MYSQL's 10 classic optimization cases and scenarios

1. General steps for SQL optimization

Locate SQL statements with low execution efficiency by slow-checking logs, etc.

1. Explain analysis of SQL execution plan

You need to pay special attention type , rows , filtered , and extra .

Type from top to bottom, efficiency is getting higher and higher

• ALL Full table scan
• index full scan
• Range index range scan, commonly used terms <, <=, >=, between, in and other operations
• ref uses a non-unique index scan or a unique index prefix scan to return a single record, often appearing in a relational query
• eq_ref is similar to ref, except that it uses a unique index and a primary key for associated queries.
• const/system Single record, the system will treat other columns in the matching row as constants, such as primary key or unique index query
• null MySQL does not access any table or index and returns the result directly

Although the efficiency is getting higher and higher from top to bottom, according to the cost model, suppose there are two indexes idx1(a, b, c) and idx2(a, c), the SQL is "select * from t where a = 1 and b in (1, 2) order by c"; if idx1 is used, the type is range, if idx2 is used, the type is ref; when the number of rows to be scanned is about 5 times more than idx1, idx1 will be used, otherwise idx2 will be used

Extra

• Using filesort: MySQL needs an extra pass to figure out how to retrieve the rows in sorted order. The sort is done by going through all the rows according to the join type and saving the sort key and row pointers for all rows that match the WHERE clause. The keys are then sorted, and the rows are retrieved in sorted order.
• Using temporary: Temporary tables are used to store intermediate results. The performance is particularly poor and needs to be optimized.
• Using index: Indicates that Coveing Index is used in the corresponding select operation, avoiding access to the data rows of the table, which is quite efficient! If using where appears at the same time, it means that the data that meets the conditions cannot be directly queried through index search.
• Using index condition: ICP was added after MySQL5.6 . using index condtion means using ICP (index push down) to filter data at the storage engine layer instead of filtering at the service layer, and using the existing data in the index to reduce the data returned to the table.

2. Show profile analysis

Understand the status of SQL execution threads and the time consumed.
The default is off, turn on the statement "set profiling = 1;"

SHOW PROFILES ;
SHOW PROFILE FOR QUERY #{id};

3. trace

The trace analyzer uses the trace file to analyze how the optimizer selects an execution plan. The trace file can be used to further understand why the coupon selects execution plan A instead of execution plan B.

set optimizer_trace="enabled=on";
set optimizer_trace_max_mem_size=1000000;
select * from information_schema.optimizer_trace;

4. Identify the problem and take appropriate action

• Optimize indexes
• Optimize SQL statements: modify SQL, IN query segmentation, time query segmentation, filter based on the last data
• Use other implementation methods: ES, data warehouse, etc.
• Data fragmentation processing

2. Scenario Analysis (Case Study)

1. Leftmost match

index

KEY `idx_shopid_orderno` (`shop_id`,`order_no`)

SQL Statements

select * from _t where orderno=''

The query matches from left to right. To use the order_no index, the query condition must carry shop_id or the index (shop_id, order_no) must be swapped.

2. Implicit conversion

index

KEY `idx_mobile` (`mobile`)

SQL Statements

select * from _user where mobile=12345678901

Implicit conversion is equivalent to performing operations on the index, which will make the index invalid. Mobile is a character type. If a number is used, string matching should be used. Otherwise, MySQL will use implicit replacement, causing the index to fail.

3. Large paging

index

KEY `idx_a_b_c` (`a`, `b`, `c`)

SQL Statements

select * from _t where a = 1 and b = 2 order by c desc limit 10000, 10;

For large paging scenarios, you can prioritize product optimization requirements. If there is no optimization, there are two optimization methods:

One method is to pass the last piece of data, that is, c above, and then perform " c < xxx " processing, but this generally requires changing the interface protocol and may not be feasible.

Another method is to use delayed association to reduce SQL table return, but remember that the index needs to be fully covered to be effective. The SQL changes are as follows

select t1.* from _t t1, (select id from _t where a = 1 and b = 2 order by c desc limit 10000, 10) t2 where t1.id = t2.id;

4. in + order by

index

KEY `idx_shopid_status_created` (`shop_id`, `order_status`, `created_at`)

SQL Statements

select * from _order where shop_id = 1 and order_status in (1, 2, 3) order by created_at desc limit 10

In the underlying MySQL, the in query searches in an n*m manner, which is similar to union , but more efficient than union .
When the in query calculates cost (cost = number of tuples * average IO value), the number of tuples is obtained by querying the values ​​contained in in one by one. Therefore, this calculation process is relatively slow. Therefore, MySQL sets a critical value ( eq_range_index_dive_limit ). After 5.6, the cost of the column will not be calculated if this critical value is exceeded. This can lead to inaccurate execution plan selection. The default value is 200. That is, if the in condition contains more than 200 data, problems may occur in the cost calculation of in, and the index selected by MySQL may be inaccurate.

Solution: You can互換前后順序( order_status, created_at ) and adjust the SQL to delayed association.

5. Range query is blocked, and subsequent fields cannot be indexed

index

KEY `idx_shopid_created_status` (`shop_id`, `created_at`, `order_status`)

SQL Statements

select * from _order where shop_id = 1 and created_at > '2021-01-01 00:00:00' and order_status = 10

Range queries also include " IN、between "

6. Not equal to, not containing, fast search that cannot use indexes

ICP can be used

select * from _order where shop_id=1 and order_status not in (1,2)
select * from _order where shop_id=1 and order_status != 1

Avoid using NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE etc. in indexes.

7. When the optimizer chooses not to use an index

If the amount of data required to be accessed is small, the optimizer will still choose the auxiliary index. However, when the accessed data accounts for a large part of the data in the entire table (usually around 20% ), the optimizer will choose to find the data through the clustered index.

select * from _order where order_status = 1

Query all unpaid orders. Generally, there are very few such orders, so even if an index is created, it cannot be used.

8. Complex queries

select sum(amt) from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01';
select * from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01' limit 10;

If you need to count certain data, you may use a data warehouse to solve the problem;

If the business query is so complex, it may not be recommended to continue using SQL, but to use other methods to solve it, such as using ES.

9. Mixing asc and desc

select * from _t where a=1 order by b desc, c asc

Mixing desc and asc will cause index failure

10. Big Data

For data storage of push services, the amount of data may be very large. If you choose a solution, you may eventually choose to store it on MySQL and save it for a validity period of 7 days.

Then you need to pay attention that frequent data cleaning will cause data fragmentation, and you need to contact the DBA to handle data fragmentation.

This concludes this article about the top 10 classic MYSQL optimization case scenarios. For more relevant MYSQL optimization case scenarios, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!