SQL optimization,
Optimization objectives
1. IO reduction is always the easiest bottleneck for databases. This is determined by the database's responsibilities. More than 90% of database operations are occupied by I/O operations, reducing the number of I/O operations is the first priority in SQL optimization. Of course, it is also the most effective optimization method.
2. In addition to the I/O bottleneck, the optimization of CPU computing workload should be considered in SQL optimization. Order by, group by, distinct... These are all large CPU-consuming users (these operations are basically the data comparison operations in CPU processing memory ). After a certain stage of IO optimization, reducing CPU computing becomes an important goal of SQL optimization.
•
Optimization Method
1. After changing the explain statement of the SQL Execution Plan and specifying the optimization goal, we need to determine the method to achieve our goal. For SQL statements, there is actually only one way to achieve the above two goals, that is, to change the SQL Execution Plan, so that he can "avoid detours" as much as possible ", try to find the data we need through various "shortcuts" to achieve the goal of "Reducing IO times" and "Reducing CPU computing ".
•
Common Mistakes
1. count (1) and count (primary_key) are better than count (*) distinct. Many people use count (1) and count (primary_key) instead of count (*) to count the number of records (*), they think this is a misunderstanding. In some scenarios, the performance may be worse. The database should optimize the count (*) operation.
2. The difference between count (column) and count (*) is the same. This misunderstanding even exists in many senior engineers or DBAs. Many people will take it for granted. In fact, count (column) and count (*) are completely different operations, and the meaning is completely different. Distinct count (column) indicates the number of columns in the result set that are not empty. Distinct count (*) indicates the number of records in the result set.
3. select a, B from... Than select a, B, c from... The misunderstanding that databases can access less data volumes mainly exists in a large number of developers, mainly because they do not know much about the storage principles of databases. In fact, most relational databases store data in row mode, and data access operations are based on a fixed IO unit (called block or page, usually 4 kb, 8 KB... In most cases, multiple rows are stored in each IO unit, and each row stores all fields of the row (except for special fields such as lob ). Therefore, we need to retrieve one or more fields. In fact, the amount of data that the database needs to access in the table is the same. Of course, there are exceptions, that is, our query can be completed in the index. That is to say, when only two fields a and B are obtained, the table does not need to be returned, the field c is not used in the index and needs to be retrieved from the table. Under such circumstances, the IO volume of the two will be significantly different.
4. order by must be sorted. We know that the index data is actually ordered. If the data we need is consistent with the order of an index, and our query uses this index for execution, generally, the database will omit the sorting operation and directly return the data because the database knows that the data has met our sorting requirements. In fact, using indexes to optimize SQL statements with sorting requirements is a very important optimization method.
5. In the execution plan, filesort will sort disk files. This misunderstanding does not blame us, But MySQL developers have problems in terms of terms. Filesort is the information displayed in the "Extra" column when we use the explain command to view an SQL Execution Plan. In fact, as long as an SQL statement needs to be sorted, "Using filesort" will be displayed, which does not mean that there will be File Sorting operations.
•
Basic Principles
1. Try to minimize the advantage of join into MySQL lies in simplicity, but this is also a disadvantage in some aspects. The MySQL optimizer is highly efficient. However, due to the limited amount of statistics, the Optimizer may be more prone to deviations during its operation. For complex multi-table Join, on the one hand, the optimizer is limited, and the effort to Join is not enough, so the performance is still a little far from that of Oracle and other relational databases. However, for simple single-table queries, this gap will be extremely small and even superior to those of database predecessors in some scenarios.
2. As few sorting operations as possible will consume a large amount of CPU resources, so reducing sorting will greatly affect the SQL response time when the cache hit rate is high and the IO capability is sufficient. For MySQL, there are multiple ways to reduce sorting, such:
• The index-based sorting method mentioned in the above misunderstanding is used for optimization.
• Reduce the number of records involved in sorting
• Do not sort data unless necessary
•...
3. Try to avoid select * distinct. Many people may find this difficult to understand. Isn't the above misunderstanding that the number of fields in the select clause just mentioned does not affect the data read? Yes, most of the time does not affect the IO volume, but when we still have the order by operation, the number of fields in the select clause will greatly affect our sorting efficiency. In addition, the above misunderstanding does not mean that the IO volume is not affected in most cases. When our query results only need to be located in the index, it will greatly reduce the IO volume.
4. Try to use join instead of subquery distinct. Although the Join performance is not good, it has a great performance advantage compared with MySQL subquery. MySQL's subquery Execution Plan has been a big problem. Although this problem has existed for many years, it has been common in all the stable versions that have been released and has not been significantly improved. Although the official team admitted this issue for a long time and promised to solve it as soon as possible, we have not yet seen any better version to solve this problem.
5. minimize or equal when multiple conditions exist in the where clause and coexist with "or", the MySQL optimizer does not solve the optimization problem of its execution plan very well, coupled with the unique SQL and Storage layered architecture of MySQL, the performance is relatively low. In many cases, union all or union is used (when necessary) to replace "or.
6. try to use union all to replace union distinct union and union all. The main difference is that the former must set two (or more) results and then perform the unique filter operation. This will involve sorting, A large number of CPU operations are added to increase resource consumption and latency. So when we can confirm that there is no repeated result set or we do not care about the repeated result set, we should try to use union all instead of union.
7. Filtering filters as early as possible. This optimization policy is most common in the index Optimization Design (placing the fields with better filtering at the top ). Explain can also use this principle in SQL writing to optimize some Join SQL statements. For example, when we query paging data in multiple tables, we 'd better filter data in one table and split the pages, then, we can use the result set of split pages to Join other tables. This can reduce unnecessary IO operations as much as possible, greatly saving the time consumed by IO operations.
8. Avoid type conversion. Here, "type conversion" refers to the type conversion that occurs when the column field type in the where clause is inconsistent with the input parameter type:
• Manual conversions using the transform function on column_name directly result in MySQL (in fact, there will be the same problem with other databases) Failure to use indexes. If conversion is required, the conversion should be performed on the input parameters.
• The database performs the conversion by itself. If the input data type and field type are inconsistent, we do not perform any type conversion, mySQL may convert the data type by itself, or submit the data to the storage engine for processing without processing it, the execution plan may be caused by an index failure.
9. optimize high-concurrency SQL statements with priority, rather than lower execution frequency. For some "large" SQL statements that are destructive, high-concurrency SQL statements are always greater than low-frequency ones, this is because if a high-concurrency SQL statement encounters a problem, it will not even give us any chance to breathe out, and the system will be overwhelmed. For some SQL statements that consume a large amount of IO and slow response, even if the response frequency is low, the system may slow down at most, but it may take at least a while, this gives us a buffer opportunity.
10. optimization from the global perspective, rather than one-sided adjustment of explain SQL optimization, cannot be performed for a specific one. Instead, all the SQL statements in the system should be fully considered, especially when you adjust the index to optimize the SQL Execution Plan, you must never ignore this problem because it is too small.
Try to explain and optimize each SQL statement running in the database. You need to be aware of the SQL Execution Plan to determine whether there is room for optimization and whether there is an execution plan problem. After optimizing the SQL statements running in the database for a period of time, it is obvious that there may be very few SQL statements, and most of them need to be explored, at this time, a large number of explain operations are required to collect execution plans and determine whether optimization is required.