优化慢SQL：排查与解决

慢SQL已经成为开发者面临的一个重要挑战。其破坏性不仅对服务平稳运行造成最大的威胁，还常常是整个应用抖动的罪魁祸首。因此，开发者需要深入了解如何在日常工作中避免慢SQL的产生，并学会如何高效解决一旦出现的慢SQL问题。本文将通过具体案例，系统地介绍排查和解决慢SQL的步骤，并总结一些建议和优化原则。

一、解决步骤

Step 1：观察SQL

慢SQL往往源于复杂的查询语句，可能涉及多表关联、复杂函数和子查询。对于这类SQL，建议先了解业务场景，梳理关联关系，尝试将SQL拆解为几个简单的小SQL，并在内存中进行关联组合。

Step 2：分析问题

通过使用explain语句来分析慢SQL，关注type、possible_keys、key、rows、extra等指标。这些指标可以帮助初步定位SQL是否使用索引，索引选择是否正确，排序是否合理等情况。

通过 EXPLAIN 可以初步定位出 SQL 是否使用索引，使用的索引是否正确，排序是否合理、索引列区分度等情况，通过这些基本就可以定位出绝大部分问题。

Step 3：指定方案

如果从SQL本身无法解决问题，可以根据业务场景和数据分布情况制定合理的修改方案

二、案例展示 

1. 优化排序问题 

原SQL使用了非索引字段进行排序，可以改为分页查询或在内存中排序，提高效率。 

-- 修改前
SELECT ...
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;

-- 修改后
SELECT ...
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 gs.id ASC; -- 改为根据id升序排序

 2、优化子查询问题

关联查询中使用子查询可能导致全表扫描，建议将子查询改为关联查询。 

-- 修改前
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;

-- 修改后
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. 优化索引选择问题

修改SQL中的索引选择，避免MySQL优化器选择错误的索引。 

-- 修改前
SELECT id,
carrier_name,
carrier_code,
trader_name,
trader_code,
route_type_name,
...
FROM carrier_route_config
WHERE yn = 1
AND carrier_code ='C211206007386'
AND trader_code ='010K1769496'
ORDER BY update_time DESC
LIMIT 10;

-- 修改后
SELECT id,
carrier_name,
carrier_code,
trader_name,
trader_code,
route_type_name,
...
FROM carrier_route_config
FORCE INDEX (PRIMARY) -- 使用force_index指定索引
WHERE yn = 1
AND carrier_code ='C211206007386'
AND trader_code ='010K1769496'
ORDER BY update_time DESC
LIMIT 10;

 4. 优化Limit问题

在分页查询中，Limit的设置可能导致全表扫描，建议手动设置Limit参数。 

-- 修改前
SELECT ...
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;

-- 修改后
SELECT ...
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 r.verify_status, r.update_time desc
LIMIT 10;

三、总体优化原则

• 创建合适的索引

确保数据库表中存在适当的索引，这有助于加速查询过程。理解查询中涉及的列，并为这些列创建索引，特别是在经常用于过滤和排序的列上。

• 减少不必要访问的列

仅选择实际需要的列，避免检索大量不必要的数据。这可以通过显式列出所需的列来实现，而不是使用通配符 *。

• 使用覆盖索引

尽可能使用覆盖索引，以避免在查询中访问实际数据行。这可以通过确保查询中使用的列都在索引中包含来实现，从而提高查询性能。

• 语句改写

对于复杂的SQL语句，考虑重写以简化逻辑或将其拆分为多个较小的查询。这有助于数据库优化器更好地理解查询，提高执行效率。

• 数据结转

对于一些大表，可以考虑将历史数据进行归档或分区存储，以减轻查询的负担。这对于历史数据的查询不频繁的情况尤为有效。

• 选择合适的列进行排序

确保排序操作使用的列具有索引，以减少排序的开销。同时，考虑是否可以通过修改查询逻辑来避免排序，或者是否可以在应用层进行排序。

• 适当的列冗余

在某些情况下，可以通过在表中添加冗余列来提高查询性能。这样做可能会增加写入操作的复杂性，但可以大幅减少读取操作的开销。

• SQL拆分

将复杂的SQL查询拆分为多个简单的查询，然后在应用层组装结果。这有助于提高数据库的响应速度，特别是在数据量庞大时。

• 适当应用ES

对于一些查询不适合在关系型数据库中执行的场景，可以考虑使用全文搜索引擎（如Elasticsearch）进行查询。这在文本搜索和复杂查询方面可能更高效。

 

四、总结

慢SQL是数据库性能优化中的一个重要方面，通过深入了解SQL查询的执行计划和性能瓶颈，我们可以采取一系列措施来提高查询效率。总体来说，建议从创建合适的索引、减少不必要的访问、使用覆盖索引等方面入手，同时根据具体场景进行SQL语句的改写和优化。

未来，随着数据库技术的不断发展，我们还可以期待更多新的优化方法和工具的出现，帮助开发者更轻松地应对慢SQL的挑战，确保系统的稳定性和性能。

• 创建合适的索引
• 减少不必要访问的列
• 使用覆盖索引
• 语句改写
• 数据结转
• 选择合适的列进行排序
• 适当的列冗余
• SQL 拆分
• 适当应用 ES窗体顶端