Optimize the performance of the PHP website Mysql from the database, code, and server, and the mysql performance.

Optimize the performance of the PHP website Mysql from the database, code, and server, and the mysql performance.

Database optimization is something that almost always happens during PHP interviews. It is also something we should pay attention to at work. Of course, it doesn't matter if it's a small website, when the website traffic volume is large, the database bottleneck is exposed. This bottleneck is caused by a combination of various problems. Let's summarize the database optimization below. I. Optimization of the database 1. index creation is particularly important for the applications where queries account for the majority. In many cases, the performance problem is very simple because we forget to add an index, or we have not added a more effective index. If no index is added, a full table scan is performed to search for any specific data. If the data volume of a table is large and the results meet the requirements are few, if you do not add an index, it will cause fatal performance degradation. 2. selecting the most suitable field attribute MySQL can support access to large data volumes. However, the smaller the table in the database, the faster the query will be executed on it. Therefore, when creating a table, we can set the field width in the table as small as possible to achieve better performance. A. Use the numeric type as much as possible for the data type. The numeric type is much faster than the numeric type. B. The data type should be as small as possible. The smaller here refers to meeting the foreseeable future needs. C. Do NOT allow NULL unless necessary. Use not null + DEFAULT instead. D. Use less TEXT and IMAGE. The reading and writing of binary fields is slow, and there are not many reading methods. It is best not to use them in most cases. E. Use auto-increment fields with caution, which is not conducive to data migration. design a canonicalized table to eliminate data redundancy 4. with proper redundancy, tables that meet the paradigm of adding computing columns must be normalized, but not necessarily the best design. In many cases, in order to improve the efficiency of database operation, we often need to lower the paradigm standard: appropriately add redundancy to achieve the purpose of changing the space for time. Ii. code optimization 1. Enabling query cache most MySQL servers have enabled query cache. This is one of the most effective ways to improve performance, and it is processed by the MySQL database engine. When many identical queries are executed multiple times, these query results are stored in a cache, the cache results are directly accessed for the same query in the future without having to operate the table. The main problem here is that this is easy for programmers to ignore. Because some of our query statements will make MySQL not use cache.

/Query cache disabled

$ R = mysql_query ("SELECT username FROM user WHERE signup_date> = CURDATE ()");

// Enable query Cache

$ Today = date ("Y-m-d ");

$ R = mysql_query ("SELECT username FROM user WHERE signup_date> = '$ today '");

The difference between the preceding two SQL statements is CURDATE (). The query cache of MySQL does not work for this function. Therefore, SQL functions such as NOW (), RAND (), and other such functions do not enable the query cache, because the returned results of these functions are variable. Therefore, all you need is to use a variable to replace the MySQL function and enable the cache. 2. optimized the unified Writing of SQL statements for query statements, with spaces and Case sensitivity kept consistent throughout the site. Avoid using select * whenever possible. Returning useless fields will reduce the query efficiency. Do not write SQL statements too complex. If the statement is too long, you can take the result of a Select statement as a subset and then query it from this subset. This layer of nested statements is still quite common, however, based on experience, if there are more than three layers of nesting, the query optimizer can easily give an incorrect execution plan. When temporary tables are used to store temporary results, an important way to simplify SQL statements is to use temporary tables to store intermediate results. However, temporary tables have far more advantages than this. Temporary results are saved to temporary tables, the subsequent query is in tempdb, which can avoid scanning the master table multiple times in the program. It also greatly reduces the blocking of the "Update lock" in program execution and reduces blocking, improves concurrency performance. Avoid using in and not in as much as possible, which will cause the database engine to discard the index for full table scanning. If you try to avoid using or, the database engine will discard the index for full table scanning. Try to avoid null value determination, which will cause the database engine to discard the index for full table scan. Avoid performing expression and function operations on the left side of the where condition with a medium number. This will cause the database engine to discard the index for full table scanning. Reduce cross-database queries and large table join operations (split your table and reduce the table size ). Suitable Use of stored procedures, views, and functions. Iii. hardware optimization 1. The higher the bandwidth, the faster the access speed. 2. when the data is fast enough, there may be a bottleneck on the CPU. Increasing the number of cpu cores or the number of CPUs 3. I/O usually occurs when the data required for work far exceeds the effective memory capacity, at this time, we will replace the General disk with an SSD hard disk to increase the server memory;
My blog: optimizing the performance of Mysql on the PHP website from the database, code, and Server