In preparation for major promotions, one of the biggest hidden dangers is slow SQL, which is the most destructive to the smooth operation of services. It is also the biggest hidden danger that often causes jitter to the entire application in daily work. How to avoid slow SQL in daily development? We must know what ideas should be used to solve slow SQL problems. This article mainly introduces the troubleshooting and solution ideas for slow SQL, and provides in-depth analysis and summary through practical examples, so as to locate and solve the problem faster and more accurately.
1. Solution steps
1.1 step1, observe SQL
For some historical reasons, some SQL queries may be very complex, requiring simultaneous association of many tables and the use of some complex functions and subqueries. Such SQL will not cause a large impact on the database due to the relatively small amount of data in the early stages of the project. Pressure, but with the accumulation of time and business development, these SQL will gradually transform into slow SQL, which will have a certain impact on the performance of the database.
For such SQL, it is recommended to first understand the business scenario, sort out the relationships, try to disassemble the SQL into several simple small SQLs, and combine them in memory.
1.2 step2, analyze the problem
The most commonly used tool when analyzing slow SQL is definitely the explain statement. The following is the execution output of the explain statement.
Generally speaking, the indicators we need to pay most attention to include type, possible_keys, key, rows, and extra.
type is the connection type, which has the following values. The performance is sorted from best to worst as follows:
- system: This table has only one row (equivalent to the system table). System is a special case of const type.
- const: Equivalent query scan for primary key or unique index, only returns one row of data at most. const query is very fast, because it only reads once
- eq_ref: This type will only be used when all components of the index are used, and the index is PRIMARY KEY or UNIQUE NOT NULL. Its performance is second only to system and const.
- ref: Occurs when the leftmost prefix rule of the index is met, or the index is not a primary key or a unique index. If the index used only matches a small number of rows, the performance is good.
TIPS
The leftmost prefix principle means that the index matches the index in a leftmost first manner. For example, if a combined index (column1, column2, column3) is created, then if the query condition is:
- WHERE column1 = 1, WHERE column1= 1 AND column2 = 2, WHERE column1= 1 AND column2 = 2 AND column3 = 3 can all use this index;
- WHERE column1 = 2, WHERE column1 = 1 AND column3 = 3 cannot match the index.
- fulltext: full text index
- ref_or_null: This type is similar to ref, but MySQL will additionally search for rows that contain NULL. This type is common in parsing subqueries
- index_merge: This type indicates the use of index merge optimization, indicating that multiple indexes are used in a query.
- unique_subquery: This type is similar to eq_ref, but uses an IN query and the subquery is the primary key or unique index. For example:
index_subquery: similar to unique_subquery, except that the subquery uses a non-unique index
range: Range scan, which means that rows in the specified range have been retrieved. It is mainly used for limited index scans. A more common range scan is with a BETWEEN clause or a WHERE clause with operators such as >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, IN(), etc.
- Index: Full index scan, similar to ALL, except that index scans the entire index data. This type can be used when the query uses only a subset of the columns in the index. There are two scenarios that will trigger:
- The index tree is only scanned if the index is a covering index for the query and all the data required in the query is satisfied by the index. At this time, the result of the Extra column of explain is Using index. index is usually faster than ALL because the size of the index is usually smaller than the table data.
- To find data rows in index order, a full table scan is performed. At this time, the Uses index will not appear in the result of the Extra column of explain.
- ALL: Full table scan, worst performance.
possible_keys
Shows which indexes can be used by the current query. The data in this column is created early in the optimization process, so some indexes may not be useful for subsequent optimization processes.
key
Indicates the index actually selected by MySQL. It is important to pay attention to Using filesort and Using temporary. The former means that the index cannot be used to complete the sorting operation. When the data is small, it is sorted from the memory, otherwise it is sorted from the disk. The latter MySQL needs to create a temporary table to save the results.
Through EXPLAIN, you can initially locate whether SQL uses indexes, whether the indexes used are correct, whether the sorting is reasonable, and the index column differentiation. Through these, most problems can be basically located.
1.3 step3, specify the plan
If it cannot be solved from SQL itself, a modification plan can be reasonably formulated based on business scenarios, data distribution and other factors.
Case presentation
1. There are two main problems in this SQL. One is that the query result has a large amount of data, about 20,000 pieces of data. The second is that it is sorted according to the non-index field oil_gun_price, resulting in filesort. There are two modification options. One is to transform it into a paging query, sort according to the ID in ascending order, and avoid the problem of deep paging according to the ID offset. The other is to directly obtain the full amount of data that meets the conditions without specifying the sorting method, and then sort in the memory. Can. In scenarios like this, try not to use the database for sorting. Unless you can directly use the index for sorting, try to load all the data into memory at once or in paging and then sort.
SELECT gs.id,
gs.gas_code,
gs.tpl_gas_code,
gs.gas_name,
gs.province_id,
gs.province_name,
gs.city_id,
gs.city_name,
gs.county_id,
gs.county_name,
gs.town_id,
gs.town_name,
gs.detail_address,
gs.banner_image,
gs.logo_image,
gs.longitude,
gs.latitude,
gs.oil_gun_serials,
gs.gas_labels,
gs.status,
gs.source,
gp.oil_number,
gp.oil_gun_price
FROM fi_club_oil_gas gs
LEFT JOIN fi_club_oil_gas_price gp ON gs.gas_code = gp.gas_code
WHERE oil_number = 95
AND status = 1
AND gs.yn = 1
AND gp.yn=1
ORDER BY gp.oil_gun_price ASC;
2. The main problem of this SQL is that subqueries are used for splicing in the related query. There are few conditions in the subquery, which is equivalent to performing a full table scan first, loading the results of the first query into the memory and then executing it. Correlation, the query time is 2.63 seconds, which is a common cause of slow SQL and should be avoided as much as possible. Here, the subquery is changed to a correlation query, and the final execution time is 0.71 seconds.
SELECT count(0)
FROM trans_scheduler_base tsb
INNER JOIN
(SELECT scheduler_code,
vehicle_number,
vehicle_type_code
FROM trans_scheduler_calendar
WHERE yn = 1
GROUP BY scheduler_code) tsc ON tsb.scheduler_code = tsc.scheduler_code
WHERE tsb.type = 3
AND tsb.yn = 1;
----------After modification--------------
SELECT count(distinct(tsc.scheduler_code))
FROM trans_scheduler_base tsb
LEFT JOIN trans_scheduler_calendar tsc ON tsb.scheduler_code = tsc.scheduler_code
WHERE tsb.type = 3
AND tsb.yn = 1
AND tsc.yn=1
3. This SQL is relatively typical. It is a slow SQL that is easily overlooked but often appears. Both carrier_code and trader_code in SQL have indexes, but the update_time index is used in the end. This is due to the optimization results of the MYSQL optimizer, which may cause the index used in actual execution to be different from expected. This kind of SQL is common when using a shared In fact, query SQL is not fully applicable in many cases, such as sorting method, query field, number of items returned, etc. Therefore, it is recommended that different business logic use its own separately defined SQL. The solution can be to use force_index to specify the index or modify the sorting method according to the situation.
SELECT id,
carrier_name,
carrier_code,
trader_name,
trader_code,
route_type_name,
begin_province_name,
begin_city_name,
begin_county_name,
end_province_name,
end_city_name,
end_county_name
FROM carrier_route_config
WHERE yn = 1
AND carrier_code ='C211206007386'
AND trader_code ='010K1769496'
ORDER BY update_time DESC
LIMIT 10;
For limit N SQL statements with group by and order by (the fields of order by and group by have indexes that can be used), the MySQL optimizer will try to use the orderliness of the existing index to reduce sorting – this seems It is the optimal solution to the SQL execution plan, but in fact the effect may be completely different. I believe everyone has encountered many cases where the SQL execution plan selects the index of order by id, which leads to a full table scan instead of using the index in the where condition. Search and filter data, which may cause the query to be very inefficient (of course the query may also be very efficient, this is related to the specific distribution of the data in the table)
The premise that order by limit optimization can play a positive role is to first assume that ordered indexes and unordered indexes are irrelevant, and secondly, assume that the data is evenly distributed.
These two assumptions are the premise for estimating the cost of accessing through sorted indexes (but in real production environments, these two assumptions are not true in most scenarios, so they lead to incorrect index selection in most scenarios), and you may encounter The execution time of conditional index filtering is tens of milliseconds, but the index sort scan takes an hour, which can be considered a bug in MySQL.
4. Limit in SQL is also one of the reasons that often leads to slow SQL. When limit is used to limit SQL, if the limit used by SQL is greater than the total number of remaining entries, and the index conditions used cannot be well utilized. Ordered characteristics, then MYSQL is likely to perform a full table scan. For example, in the following SQL, the SQL uses the create_time index during execution, but there is no create_time as a condition in the condition. The total number of SQL results is 6, which is less than the limit result of 10 at this time. Therefore, MYSQL performs a full table scan, which is time-consuming. 2.19 seconds, and when the limit is changed to 6, the SQL execution time is 0.01 seconds, because MYSQL returns directly when 6 results that meet the conditions are queried, and no full table scan is performed. Therefore, when the paging query data is no longer full of one page, it is best to set the limit parameter manually.
SELECT cva.id,
cva.carrier_vehicle_approval_code,
dsi.driver_erp,
d.driver_name,
cva.vehicle_number,
cva.vehicle_type,
cva.vehicle_kind,
cva.fuel_type,
cva.audit_user_code,
dsi.driver_id,
cva.operate_type,
dsi.org_code,
dsi.org_name,
dsi.prov_code,
dsi.prov_name,
dsi.area_code,
dsi.area_name,
dsi.node_code,
dsi.node_name,
dsi.position_name,
cva.create_user_code,
cva.audit_status,
cva.create_time,
cva.audit_time,
cva.audit_reason,
d.jd_pin,
d.call_source,
cv.valid_status
FROM driver_staff_info dsi
INNER JOIN carrier_vehicle_approval cva ON cva.driver_id = dsi.driver_id
INNER JOIN driver d ON dsi.driver_id = d.driver_id
INNER JOIN carrier_vehicle_info cv ON cv.vehicle_number = cva.vehicle_number
WHERE dsi.yn = 1
AND d.yn = 1
AND cva.yn = 1
AND cv.yn = 1
AND dsi.org_code = '3'
AND dsi.prov_code = '021S002'
AND cva.carrier_code = 'C230425013337'
AND cva.yn = 1
AND cva.audit_status = 0
AND d.call_source IN ('kuaidi',
'kuaiyun')
ORDER BY cva.create_time DESC
LIMIT 10
5. The following SQL tables have too many associations, resulting in a relatively large amount of data loaded into the database. According to the actual situation, you can choose to first find out the data of one table as the basic data, and then fill in the remaining fields according to the table connection conditions. It is not recommended to associate too many tables with tables with large amounts of data. They can be replaced by appropriate redundant fields or processing of wide tables.
SELECT blsw.bid_line_code,
blsw.bid_bill_code,
blsw.bid_line_name,
blsw.step_code,
blsw.step_type,
blsw.step_type_name,
blsw.step_weight,
blsw.step_weight_scale,
blsw.block_price,
blsw.max_weight_flag,
blsw.id,
blsw.need_quote_price,
bbs.step_item_code,
bbs.step_item_name,
bbs.step_seq,
bl.bid_line_seq
FROM bid_line_step_weight blsw
LEFT JOIN bid_bill_step bbs
ON blsw.bid_bill_code = bbs.bid_bill_code
AND blsw.step_code = bbs.step_code
AND blsw.step_type = bbs.step_type
LEFT JOIN bid_line bl
ON blsw.bid_line_code = bl.bid_line_code
AND blsw.bid_bill_code = bl.bid_bill_code
WHERE blsw.yn = 1
AND bbs.yn = 1
ANDbl.yn=1
AND blsw.bid_bill_code = 'BL230423051192';
6. This SQL uses update_time as the time range index. You need to pay attention to whether there is a problem of excessive concentration of hot data, resulting in a very large amount of query data and complex sorting conditions that cannot be solved directly through SQL optimization. On the one hand, the problem of over-concentration of hot data needs to be solved first, and on the other hand, it needs to be optimized according to business scenarios, such as adding some default conditions to reduce the amount of data.
SELECT r.id,
r.carrier_code,
r.carrier_name,
r.personal_name,
r.status,
r.register_org_name,
r.register_org_code,
r.register_city_name,
r.verify_status,
r.cancel_time,
r.reenter_time,
r.verify_user_code,
r.data_source,
r.sign_contract_flag,
r.register_time,
r.update_time,
r.promotion_erp,
r.promotion_name,
r.promotion_pin,
r.board_time,
r.sync_basic_status,
r.personal_verify_result,
r.cert_verify_result,
r.qualify_verify_result,
r.photo_verify_result,
d.jd_pin,
d.driver_id,
v.vehicle_number,
v.vehicle_type,
v.vehicle_length,
r.cancellation_code ,
r.cancellation_remarks
FROM carrier_resource r
LEFT JOIN carrier_driver d
ON r.carrier_code = d.carrier_code
LEFT JOIN carrier_vehicle v
ON r.carrier_code = v.carrier_code
WHERE r.update_time >= '2023-03-26 00:00:00'
AND r.update_time <= '2023-04-02 00:00:00'
AND r.yn = 1
AND v.yn = 1
AND d.yn = 1
AND d.status != -1
AND IFNULL(r.carrier_individual_type,'') != '2'
ORDER BY (case r.verify_status
WHEN 30 THEN
1
WHEN 20 THEN
2
WHEN 25 THEN
3
WHEN 35 THEN
4
WHEN 1 THEN
5
ELSE 6 end), r.update_time desc, if((v.driving_license_time IS null
AND d.driver_license_time IS null), 0, 1) desc, if(((v.driving_license_time IS NOT null
AND v.driving_license_time < NOW())
OR (d.driver_license_time IS NOT null
AND d.driver_license_time < NOW())), 2, 0) DESC LIMIT 10;
In the actual development process, there are many scenarios that are difficult to optimize from SQL itself, such as excessive query data loading, excessive table data volume, serious data skew, etc. Try to implement some necessary protection measures and restrictions based on the business scenario, without affecting In business situations, we can look for alternatives, such as using ES for query, but we still need to choose different solutions based on the actual scenario.
7. For some tables with a large amount of data, the results can be returned quickly when performing paging queries, but it is often very slow when performing paging count of the total number of items. This is because there is a pageSize limit during paging queries. , when MYSQL queries the data that meets the number of items, it will be returned directly. When counting, it will query the entire table according to the conditions. When the amount of data contained in the conditions is too large, it will limit the performance of SQL. In this case, it is recommended to rewrite the paging logic on the one hand and separate count and selectList. You can consider applying ES as the count data source, or under certain conditions, if the total number of items already exists, you will no longer count and reduce the number of paging counts; On the other hand, limit the paging depth to avoid deep paging.
2. Overall optimization principles
- Create appropriate indexes
- Reduce unnecessary access to columns
- Use covering index
- Statement rewriting
- Data carry forward
- Select the appropriate column to sort
- Proper column redundancy
- SQL split
- Appropriate application of ES