业务系统如何优化慢Sql？

优化慢SQL是一个复杂且需要经验的过程，因为SQL的性能受到多种因素的影响，包括数据库设计、表结构、索引、查询逻辑等。

MySql 业务系统查询过程中如何优化慢SQL？

 以下重点讨论explain 分析慢sql的过程。   

1、通过慢查日志等定位那些执行效率较低的 SQL 语句

2、 explain 分析SQL的执行计划

执行计划需要重点关注的内容 ：type、rows、filtered、extra.

2.1、 type 由上至下，效率越来越高：

ALL 全表扫描  
index 索引全扫描  
range 索引范围扫描，常用语<,<=,>=,between,in 等操作  
ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
eq_ref 类似 ref，区别在于使用的是唯一索引，使用主键的关联查询
const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询  
null MySQL 不访问任何表或索引，直接返回结果 

/虽然上至下/，效率越来越高，但是根据 cost 模型，假设有两个索引 idx1(a, b, c),idx2(a, c)，SQL 为”select * from t where a = 1 and b in (1, 2) order by c”;如果走 idx1，那么是 type 为 range，如果走 idx2，那么 type 是 ref；当需要扫描的行数，使用 idx2 大约是 idx1 的 5 倍以上时，会用 idx1，否则会用 idx2

2.2、Extra

Using filesort：MySQL 需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配 WHERE 子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行；  
Using temporary：使用了临时表保存中间结果，性能特别差，需要重点优化；    
Using index：表示相应的 select 操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！如果同时出现 using where，意味着无法直接通过索引查找来查询到符合条件的数据；    
Using index condition：MySQL5.6 之后新增的 ICP，using index condtion 就是使用了 ICP（索引下推），在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。

3、show profile 分析

了解 SQL 执行的线程的状态及消耗的时间。
默认是关闭的，开启语句“set profiling = 1;”

1 2	SHOW PROFILES ; SHOW PROFILE FOR QUERY #{id}; SQL

4、trace

分析优化器如何选择执行计划，通过 trace 文件能够进一步了解为什么又会选择 A 执行计划而不选择 B 执行计划。

1 2 3

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

5、确定问题并采用相应的措施

如下：

优化索引

优化 SQL 语句：修改 SQL、IN 查询分段、时间查询分段、基于上一次数据过滤

改用其他实现方式：ES、数仓等

数据碎片处理

6、场景分析

索引生效原则分析

6.1 最左匹配

1 2 3 4 5 6 7

-- 最左匹配 -- 索引 KEY `idx_shopid_orderno` (`shop_id`,`order_no`) -- sql 语句 select * from _t where orderno='' -- 查询匹配从左往右匹配，要使用 order_no 走索引，必须查询条件携带 shop_id 或者索引（shop_id，order_no）调换前后顺序。 SQL

6.2 隐式转换

1 2 3 4 5 6

-- 索引 KEY `idx_mobile` (`mobile`) -- sql 语句 select * from _user where mobile=12345678901 -- 隐式转换相当于在索引上做运算，会让索引失效。mobile 是字符类型，使用了数字，应该使用字符串匹配，否则 MySQL 会用到隐式替换，导致索引失效。 SQL

6.3 大分页

1 2 3 4 5 6 7 8 9 10 11 12

-- 索引 KEY `idx_a_b_c` (`a`, `b`, `c`) -- sql 语句 select * from _t where a = 1 and b = 2 order by c desc limit 10000, 10; -- 对于大分页的场景，可以优先让产品优化需求，如果没有优化的，有如下两种优化方式： --（1）一种是把上一次的最后一条数据，也即上面的 c 传过来，然后做“c < xxx”处理，但是这种一般需要改接口协议，并不一定可行 --（2）另一种是采用延迟关联的方式进行处理，减少 SQL 回表，但是要记得索引需要完全覆盖才有效果。 -- 修改如下： select * from _order where shop_id = 1 and order_status in (1, 2, 3) order by created_at desc limit 10 SQL

in 查询在 MySQL 底层是通过 n*m 的方式去搜索，类似 union，但是效率比 union 高。
in 查询在进行 cost 代价计算时（代价 = 元组数 * IO 平均值），是通过将 in 包含的数值，一条条去查询获取元组数的，因此这个计算过程会比较的慢。
所以 MySQL 设置了个临界值（eq_range_index_dive_limit），5.6 之后超过这个临界值后该列的 cost 就不参与计算了。因此会导致执行计划选择不准确。
默认是 200，即 in 条件超过了 200 个数据，会导致 in 的代价计算存在问题，可能会导致 MySQL 选择的索引不准确。
处理方式：可以（order_status，created_at）互换前后顺序，并且调整 SQL 为延迟关联。

6.4 范围查询阻断，后续字段不能走索引

1 2 3 4 5 6 7

-- 索引 KEY `idx_shopid_created_status` (`shop_id`, `created_at`, `order_status`) -- sql 语句 select * from _order where shop_id = 1 and created_at > '2021-01-01 00:00:00' and order_status = 10 -- 范围查询还有“IN、between”。 SQL

6.5 in + order by 查询

1 2 3 4 5 6

-- 索引 KEY `idx_shopid_status_created` (`shop_id`, `order_status`, `created_at`) -- sql 语句 select * from _order where shop_id = 1 and order_status in (1, 2, 3) order by created_at desc limit 10 SQL

in 查询在 MySQL 底层是通过 n*m 的方式去搜索，类似 union，但是效率比 union 高。
in 查询在进行 cost 代价计算时（代价 = 元组数 * IO 平均值），是通过将 in 包含的数值，一条条去查询获取元组数的，因此这个计算过程会比较的慢。
所以 MySQL 设置了个临界值（eq_range_index_dive_limit），5.6 之后超过这个临界值后该列的 cost 就不参与计算了。因此会导致执行计划选择不准确。
默认是 200，即 in 条件超过了 200 个数据，会导致 in 的代价计算存在问题，可能会导致 MySQL 选择的索引不准确。
处理方式：可以（order_status，created_at）互换前后顺序，并且调整 SQL 为延迟关联。

6.6 不等于、不包含不能用到索引的快速检索

1 2 3

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 -- 在索引上，避免使用 NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等。 SQL

6.7 优化器选择不使用索引的情况

1 2	-- sql 语句 select * from _order where order_status = 1 SQL

如果要求访问的数据量很小，则优化器还是会选择辅助索引，但是当访问的数据占整个表中数据的蛮大一部分时（一般是 20% 左右），优化器会选择通过聚集索引来查找数据。

6.8 复杂查询

1 2	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; SQL

如果是统计某些数据，可能改用数仓进行解决；如果是业务上就有那么复杂的查询，可能就不建议继续走 SQL 了，而是采用其他的方式进行解决，比如使用 ES 等进行解决。

6.9 asc 和 desc 混用

1 2 3

select * from _t where a=1 order by b desc, c asc -- desc 和 asc 混用时会导致索引失效。 SQL

总之，SQL的优化方法有很多种，可以根据实际情况选择合适的优化方法进行优化、调整，以提高数据库的性能和稳定性，提高系统使用的体验。优化SQL查询是一个持续的过程，需要不断地分析、调整和监控。通过应用上述优化技巧，可以显著提高查询性能并提升数据库的整体吞吐量。然而，需要注意的是，每个数据库和应用场景都有其独特性，因此在实际优化过程中需要根据具体情况灵活应用这些技巧。