In this article, let's talk about MySQL optimization methods commonly used in projects, a total of 19, as follows:
To do MySQL optimization, we have to make good use of EXPLAIN to view the SQL execution plan.Here's a simple example, labeling (1, 2, 3, 4, 5) the data we want to focus on:
- type column, connection type. A good SQL statement should at least reach the range level. Prevent all levels from appearing.
- key column, the index name used. If no index is selected, the value is NULL. Forced indexing can be used.
- key_len column, index length.
- The rows column, the number of rows to scan. This value is an estimate.
- extra column, detailed description. Note that common less friendly values are as follows: Using filesort, Using temporary.
2. The value contained in IN in the SQL statement should not be too muchMySQL has made corresponding optimizations for IN, that is, all the constants in IN are stored in an array, and the array is sorted. However, if the value is large, the consumption is also relatively large. Another example: select id from t where num in(1,2,3) For continuous values, if you can use between, don't use in; or use connection instead.3. The SELECT statement must specify the field nameSELECT* increases a lot of unnecessary consumption (CPU, IO, memory, network bandwidth); increases the possibility of using covering indexes; when the table structure changes, the previous interrupt also needs to be updated. So it is required to connect the field name directly after the select.4. When only one piece of data is needed, use limit 1This is to make the type column in EXPLAIN reach the const type5. If the sorting field does not use an index, sort as little as possible6. If other fields in the restriction conditions do not have indexes, use or as little as possibleIf one of the fields on both sides of or is not an index field, and the other conditions are not index fields, it will cause the query not to go through the index. In many cases, using union all or union (if necessary) instead of "or" will get better results.7. Try to use union all instead of unionThe main difference between union and union all is that the former needs to merge the result set and then perform the unique filtering operation, which will involve sorting, increase a large number of CPU operations, increase resource consumption and delay. Of course, the prerequisite for union all is that the two result sets do not have duplicate data.8. Do not use ORDER BY RAND()10. Use a reasonable paging method to improve the efficiency of paging
select id from `dynamic` order by rand() limit 1000;The above SQL statement can be optimized as:
select id from `dynamic` t1 join (select rand() * (select max(id) from `dynamic`) as nid) t2 on t1.id > t2.nidlimit 1000;9. Distinguish between in and exists, not in and not exists
select * from 表A where id in (select id from 表B)The above SQL statement is equivalent to
select * from tableA where exists(select * from tableB where tableB.id=tableA.id)Distinguishing between in and exists mainly results in a change in the driving order (this is the key to performance change). If it is exists, then the outer table is the driving table and is accessed first. If it is IN, the subquery is executed first. Therefore, IN is suitable for the case where the exterior is large and the interior is small; EXISTS is suitable for the case where the exterior is small and the interior is large.Regarding not in and not exists, it is recommended to use not exists, not only for efficiency issues, not in may have logical problems. How to efficiently write an SQL statement that replaces not exists?
select colname … from A表 where a.id not in (select b.id from B表)
select colname … from A表 Left join B表 on where a.id = b.id where b.id is nullThe result set retrieved is shown in the following figure, the data in table A is not in table B:
LEFT JOIN A table is the driving table, INNER JOIN MySQL will automatically find the table with less data to act as the driving table, and RIGHT JOIN B table is the driving table.
select id,name from product limit 866613, 20When using the above SQL statement for paging, some people may find that with the increase of the amount of table data, directly using the limit paging query will become slower and slower.The optimization method is as follows: you can take the id of the maximum number of rows of the previous page, and then limit the starting point of the next page according to the maximum id. For example, in this column, the largest id of the previous page is 866612. SQL can be written as follows:
select id,name from product where id> 866612 limit 20In some user selection pages, the time range selected by some users may be too large, resulting in slow query. The main reason is too many lines to scan. At this time, you can query by program, segment, loop through, and combine the results for display.As shown in the following SQL statement, segmented queries can be used when the number of scanned rows is more than one million:12. Avoid null value judgment for fields in the where clauseThe judgment of null will cause the engine to give up the use of the index and perform a full table scan.13. It is not recommended to use % prefix fuzzy queryFor example, LIKE "%name" or LIKE "%name%", this kind of query will cause the index to fail and perform a full table scan. But LIKE "name%" can be used.As shown in the figure below, although an index is added to the secret field, it is not used in the explain result:So how to solve this problem, the answer: use full-text indexing.Select id,fnum,fdst from dynamic_201606 where user_name like '%zhangsan%'; is often used in our queries. For such a statement, ordinary indexes cannot meet the query requirements. Fortunately, in MySQL, there are full-text indexes to help us.The SQL syntax for creating a full-text index is:
ALTER TABLE `dynamic_201606` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);The SQL statement to use the full text index is:
select id,fnum,fdst from dynamic_201606 where match(user_name) against('zhangsan' in boolean mode);Note: Before you need to create a full-text index, please contact your DBA to determine if it can be created. At the same time, it is necessary to pay attention to the difference between the writing method of the query statement and the ordinary index.14. Avoid expression operations on fields in where clauses
select user_id,user_project from user_base where age*2=36;The arithmetic operation is performed on the field in the middle, which will cause the engine to abandon the use of the index. It is recommended to change it to:
select user_id,user_project from user_base where age=36/2;15. Avoid implicit type conversionsType conversion occurs when the type of the column field in the where clause is inconsistent with the incoming parameter type. It is recommended to determine the parameter type in where first.16. For the joint index, obey the leftmost prefix ruleFor example, the index contains the fields id, name, and school. The id field can be used directly, or the order of id and name can be used, but neither name; school can use this index. Therefore, when creating a joint index, you must pay attention to the order of the index fields, and the commonly used query fields are placed at the top.17. If necessary, you can use the force index to force the query to go through an indexSometimes the MySQL optimizer uses the index it deems appropriate to retrieve the SQL statement, but maybe the index it uses is not what we want. At this time, forceindex can be used to force the optimizer to use the index we made.18. Pay attention to the range query statementFor a joint index, if there is a range query, such as between, >, < and other conditions, the subsequent index fields will be invalid.19. About JOIN optimization
1) There is no full join in MySQL, which can be solved in the following ways:It can be intuitively seen from the schematic diagram that if the drive table can be reduced, the number of loops in the nested loop can be reduced to reduce the total amount of IO and the number of CPU operations.
select * from A left join B on B.name = A.namewhere B.name is nullunion allselect * from B;2) Try to use inner join and avoid left join:The tables involved in the joint query are at least two tables, which generally differ in size. If the connection method is inner join, MySQL will automatically select the small table as the driving table in the absence of other filtering conditions, but the left join follows the principle of left driving the right in the selection of the driving table, that is, the table name on the left side of the left join for the drive table.3) Reasonable use of indexes:The index field of the driven table is used as the limit field of on.4) Use a small table to drive a large table:
5) Use STRAIGHT_JOIN skillfully:Inner join is selected by MySQL to drive the table, but in some special cases, another table needs to be selected as the driving table, such as when there are "Using filesort" and "Using temporary" such as group by and order by. STRAIGHT_JOIN to force the join order, the table name on the left of STRAIGHT_JOIN is the driving table, and the right side is the driven table. A prerequisite for using STRAIGHT_JOIN is that the query is an inner join, that is, an inner join. STRAIGHT_JOIN is not recommended for other links, otherwise the query result may be inaccurate.