Performance optimization of PHP Web site mysql from database, code and server

Database optimization is a PHP interview almost will be asked about things, but also our work should pay attention to the things, of course, if the small site does not optimize, site access to a large number of natural will burst the bottleneck of the database, this bottleneck is a combination of various aspects of the problem, the following we do under the summary of database optimization. One: Optimization of database 1. Creating an index is especially important for queries that are the primary application. A lot of times the performance problem is simply because we forgot to add an index, or we didn't add a more efficient index. If you do not index, then find any even a specific data will be a full table scan, if a table of large amounts of data and meet the results of a few, then the non-index will cause a fatal performance degradation. 2. Select the most applicable field properties MySQL can well support large data volume access, but in general, the smaller the table in the database, the faster queries will be executed on it. Therefore, in order to achieve better performance when creating a table, we can set the width of the fields in the table as small as possible. A, the data type as far as possible with the digital type, the digital type comparison is much faster than the character type. B, the data type is as small as possible, where the minimum is to meet the foreseeable future needs of the premise. C, try not to allow NULL, unless necessary, you can use not null+default instead. D, less with the text and image, binary field read and write is relatively slow, and, read the method is not much, most of the case is best not. E, self-increment field to be used with caution, not conducive to data migration 3. Design normalization tables, eliminate data redundancy 4. Proper redundancy, the addition of a table that satisfies the normal form of a computed column must be a normalized table, but not necessarily the best design. In many cases, in order to improve the efficiency of the database, it is often necessary to reduce the paradigm standard: the appropriate increase in redundancy to achieve the purpose of space-changing time. Two: Optimization of code 1. Turn on query caching most MySQL servers have query caching turned on. This is one of the most effective ways to improve sex, and this is handled by the MySQL database engine. When many of the same queries are executed multiple times, the results of these queries are placed in a cache so that subsequent identical queries do not have to manipulate the table directly to access the cached results. The main problem here is that this is a very easy thing to ignore for programmers. Because, some of our query statements will let MySQL not use the cache.

/query cache does not open

$r = mysql_query ("Select username from user WHERE signup_date >= curdate ()");

Turn on query caching

$today = Date ("y-m-d");

$r = mysql_query ("Select username from user WHERE signup_date >= ' $today '");

The difference between the two SQL statements above is curdate (), and the MySQL query cache does not work for this function. Therefore, SQL functions such as now () and RAND () or whatever, do not turn on the query cache because the return of these functions is variable. So all you need to do is use a variable instead of the MySQL function to turn on the cache. 2. Optimization query statement The wording of the unified SQL statement, the space, the case to keep the whole station consistent. Avoiding the use of select *, returning useless fields can reduce query efficiency.     Do not write SQL statements too complex, if the statement length can be a subset of the results of a SELECT statement, and then query from that subset, this layer of nested statements is still more common, but based on experience, more than 3 layers of nesting, the query optimizer can easily give the wrong execution plan. Using temporary table staging results, an important way to simplify SQL statements is to use temporary tables to stage intermediate results, but temporary tables benefit far more than that, temporary tables are temporarily present, and subsequent queries are in tempdb, which avoids multiple scans of the main table in the program and greatly reduces the "shared lock" in program execution. "Blocking update locks" reduces blocking and improves concurrency performance. Try to avoid using in and not, which causes the database engine to discard indexes for a full table scan. Avoiding or, as much as possible, causes the database engine to discard indexes for a full table scan. Trying to avoid null values can cause the database engine to discard indexes for a full table scan. Try to avoid expressions, function actions on the left side of the equals sign in the Where condition, which causes the database engine to discard indexes for full table scans. Reduce cross-Library queries and large table join operations (splitting your tables and reducing the size of your tables). Proper use of stored procedures, views, functions. Three: Hardware optimization 1. The higher the bandwidth bandwidth, the faster the access speed. 2. When the data is fast enough, the CPU may become a bottleneck, increasing the number of cores or CPU 3. I/O bottlenecks generally occur when the data required to work far exceeds the effective memory capacity, this time we use SSD hard disk instead of ordinary disk, increase the server memory;
I blog: from the database, code and server to PHP Web site MySQL performance optimization

Performance optimization of PHP Web site mysql from database, code and server