1.3 MySQL SQL Tuning

1. Single-Instance MySQL Bottleneck

3 million records, 2,000 concurrent requests

2. Overall Approach

• Database Optimization.md

3. Steps

3.1. Locate Slow Queries

• MySQL Slow Query Log.md
• MySQL Indexes.md

3.2. Analyze SQL

• MySQL explain.md
• MySQL show profile.md

3.3. Rewrite SQL

1. Limit the data query range: for example, query only order data from within one month.
2. Optimize paginated queries.
   • At the database level
     • For example, change select * from table where age > 20 limit 1000000,10 to select * from table where id in (select id from table where age > 20 limit 1000000,10). The core idea is the same: reduce the amount of data loaded.
   • Reduce this kind of request from the requirements side
     • Do not provide this kind of requirement (jumping directly to a specific page millions of pages later. Only allow viewing page by page or following a given path).

4. Examples

4.1. Paginated Query Optimization

• If there are 100W records, retrieve the last 10W.

4.1.1. limit offset

• Traditional form: select * from xxx limit 10W offset 90W
• It cannot use an index and needs to scan the whole table before taking the last 10W.

4.1.2. limit id

• select * from xxx where id in (select id from xxx limit 10W offset 90W)
• It can use the ID index, but it still needs to scan the entire ID index.

4.1.3. Join Query

• select * from xxx INNER JOIN( select id from xxx limit 10W, 90W) as a USING(id)

4.1.4. BETWEEN … AND

• select * from xxx where id BETWEEN 90W AND 100W
• It can use the ID index. If IDs are not continuous and auto-incrementing, this method is not feasible.

4.1.5. Maximum-ID Query Method

• select * from xxx where id > 90W limit 10W
• It can use the ID index.

4.2. Batch Update

Six Ways to Batch Update Data in MySQL - Juejin

4.3. Join Optimization

• If the index of the driven table can be used, the join statement still has its advantages.
• If the index of the driven table cannot be used, only the Block Nested-Loop Join algorithm can be used, so this kind of statement should be avoided as much as possible.
• When using a join, the smaller table should be used as the driving table.
• MySQL Table Access Methods.md

5. References

• MySql Large-Table Pagination (with Exclusive Technique) | ifeve.com
• If Anyone Asks You Again “How to Optimize MySQL Pagination Queries,” Send Them This Article - Motianlun