The optimization of slow SQL mainly considers two aspects: the optimization of SQL statements itself and the optimization of database design.
Avoid unnecessary columns
When querying SQL, you should only query the required columns, rather than include additional columns, such asselect *This writing method should be avoided as much as possible.
Pagination optimization
When the data volume is relatively large and the page is deep, the optimization of paging needs to be considered.
select * from tabel where type = 2 and level = 9 order by id asc limit 100000,10;
Delayed association
Pass firstwhereConditionally extract the primary key, and when the table is associated with the original data table, the data row is extracted by the primary key id, instead of extracting the data rows through the original secondary index
select a.* from table a, (select id from table where type = 2 and level = 9 order by id asc limit 100000,10) b where = ;
id offset
The offset is to findlimitThe primary key value corresponding to the first parameter is filtered according to this primary key value andlimit
select * from table where id > (select id from table where type = 2 and level = 9 order by id asc limit 190 );
Index optimization
Reasonable design and use of indexes is a powerful tool for optimizing slow SQL.
1.Use overlay indexes
InnoDB will return to the table when querying data using secondary indexes, but if the index's leaf node already contains the fields to be queryed, there is no need to return to the table to query again. This is called overwriting the index. There is also a simple understanding that query columns are index columns.
select b from test where a = "wanna";
alter table test add index idx_a_b (a,b);
2. Avoid using or query
Try to avoid using versions before MySQL 5.0orQuery, can be usedunionOr subqueries are used as a subquery, as earlier MySQL versions usedorQuery may cause index failure. The higher version introduces index merging, which solves this problem. However, it is recommended that you still standardize the writing method in actual use, and use it less if you can use it.
3. Avoid using the != or <> operator
In SQL, the operator does not equal to the operator will cause the query engine to abandon the query index, causing the full table to scan, and there will be indexes on the fields that are instantly compared.
Solution: Change the not equal operator toor, you can use indexes to avoid full table scanning
id <> 'aaa' ===> id > 'aaa' or id < 'aaa'
4. Use prefix index appropriately
Appropriate use of prefix indexes can reduce the control occupation of the index and improve the query efficiency of the index.
For example, the suffix of the mailbox is fixed@, then fields like the following several bits of fixed values are very suitable for definition as prefix index
alter table test add index dix_emaile_prefix (email(6));
It should be noted that prefix indexes also have disadvantages, and MySQL cannot use prefix indexes to do it.order byandgroup byOperations cannot be used as overlay index.
5. Avoid function operations on columns
Avoid arithmetic operators or other expression operations on column fields, otherwise the storage engine may not be able to use the index correctly, which affects the efficiency of the query.
select * from test where id + 1 = 50; select * from test where month(updateTime) = 7;
6. Use joint index correctly
When using joint indexes, pay attention to the leftmost matching principle.
Optimization of JOIN
Optimize subquery
Try to usejoinStatements replace subqueries because subqueries are nested queries, and nested queries will create a new temporary table. The creation and destruction of temporary tables will take up a certain system resource and take a certain amount of time. At the same time, for subqueries with larger return result sets, it will have a greater impact on query performance.
Small table drives large tables
When queries, you should use small tables to drive large tables, because when queries, MySQL will traverse the driver tables internally and then connect to the driven tables.
select name from Small table left join Big table;
Add redundant fields appropriately
Adding redundant fields can reduce a large number of connected table queries, because the performance of connected table queries for multiple tables is very low. All redundant fields can be appropriately added to reduce the associated queries for multiple tables. This is an optimization strategy for exchanging space for time.
Avoid using JOIN to associate too many tables
"Alibaba Java Development Manual" stipulates that it does notjoinMore than three tables, firstjoinToo much to reduce the speed of query, secondjoinThe buffer will take up more memory.
Sort optimization
Sorting with index scanning
MySQL has two ways to generate ordered results: one is to sort the result set, but to scan the results in index order. The index is the sorted data result, which is naturally ordered.
However, if the index cannot overwrite the columns required for the query (overwrite the index), no record will be scanned and returned to the table to query once (get one by one). This read operation is random IO, which is usually slower than sequential full-table scans. Sometimes it will directly give up using the index and convert it to full-table scans.
Therefore, when designing indexes, use the same index as much as possible to satisfy the sorting and use it to find rows.
-- index(a,b,c) select b,c from test where a like 'aa%' order by b,c;
Only when indexed column order andorder byOnly when the order of clauses is exactly the same and the sorting directions of all columns are the same can the index be used to sort the results.
UNION Optimization
Promote under conditions
MySQL's strategy for handling union is to create a temporary table first, then populate each query result into the temporary table and then do the query. Many optimization strategies areunionAll in the query will fail because it cannot utilize the index.
So we need towhere、limitWait for the clause to be pushed downunionin each subquery so that the optimizer can make full use of these conditions for optimization.
In addition, unless the server really needs to be pushed, you must try itunion all, if not addedallKeywords, MySQL will add temporary tablesdistinctOptions, which will result in unique checks on the entire temporary table, which is very expensive.
This is the article about different ways to introduce slow SQL optimization in MySQL. For more related content on slow SQL optimization of MySQL, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!