背景

在应用开发的早期，数据量少，开发人员开发功能时更重视功能上的实现，随着生产数据的增长，很多 SQL 语句开始暴露出性能问题，对生产的影响也越来越大，有时可能这些有问题的 SQL 就是整个系统性能的瓶颈。

SQL 优化一般步骤

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

2、explain 分析 SQL 的执行计划

需要重点关注 type、rows、filtered、extra。
type由上至下，效率越来越高(表示使用的索引类型)

1. ALL 全表扫描
2. index 索引全扫描
3. range 索引范围扫描，常用语<,<=,>=,between,in 等操作
4. ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
5. eq_ref 类似 ref，区别在于使用的是唯一索引，使用主键的关联查询
6. const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询
7. 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

补充关于 cost 模型(sql 内部优化选择模型)

成本模型的基本工作方式, 这些可配置的成本模型是这样工作的：

1. SQL 服务在启动的时候从成本模型的数据库表中读取数据，存到内存，运行期间就从内存中读取这些数据。这个表中非 NULL 的值的优先级高于编译好的默认值，这个表中的 NULL 值代表优化器使用编译好的默认值。
2. SQL 服务运行的时候可能会重新读取成本表。重新读取的场景有：存储引擎的动态加载，和执行 FLUSH OPTIMIZER_COSTS 语句。
3. 内存中的成本评估数据对于客户端的会话（session）来说是实时的，贯串了会话的开始到结束。注意，如果 SQL 服务重新读取了成本表，表中修改的信息只对后面生成的会话生效，对即将结束的会话不会起作用。
4. 成本评估表是对指定的 SQL 服务实例生效的，SQL 服务不会把成本评估表的修改同步到从库去。

Extra

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

3、show profile 分析

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

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

4、trace

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

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

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

优化索引
优化 SQL 语句：修改 SQL、IN 查询分段、时间查询分段、基于上一次数据过滤
改用其他实现方式：ES、数仓等
数据碎片处理

场景分析

案例 1、最左匹配

//索引
KEY `idx_shopid_orderno` (`shop_id`,`order_no`)
//SQL语句， 使用最左的作为索引
select * from _t where orderno=''

查询匹配从左往右匹配，要使用 order_no 走索引，必须查询条件携带 shop_id 或者索引(shop_id,order_no)调换前后顺序

案例 2、隐式转换

//索引
KEY `idx_mobile` (`mobile`)
//SQL语句
select * from _user where mobile=12345678901

隐式转换相当于在索引上做运算，会让索引失效。mobile 是字符类型，使用了数字，应该使用字符串匹配，否则 MySQL 会用到隐式替换，导致索引失效。

案例 3、大分页

//索引
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;

对于大分页的场景，可以优先让产品优化需求，如果没有优化的，有如下两种优化方式， 一种是把上一次的最后一条数据，也即上面的 c 传过来，然后做“c < xxx”处理，但是这种一般需要改接口协议，并不一定可行。

另一种是采用延迟关联的方式进行处理，减少 SQL 回表，但是要记得索引需要完全覆盖才有效果，SQL 改动如下
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;
个评: 其实这种操作是使用覆盖索引的方法，让查询语句完全覆盖到索引，查询出范围后，然后再利用 id 在全表中查询出结果来。 防止多次数据查询回流。

案例 4、in + order by

//索引
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

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 为延迟关联。

案例 5、范围查询阻断，后续字段不能走索引

//索引
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” 。个解: 如果使用了范围查询，一定注意放在查询的最后。

案例 6、不等于、不包含不能用到索引的快速搜索。（可以用到 ICP）

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 等

案例 7、优化器选择不使用索引的情况

如果要求访问的数据量很小，则优化器还是会选择辅助索引，但是当访问的数据占整个表中数据的蛮大一部分时（一般是 20%左右），优化器会选择通过聚集索引来查找数据。
select * from _order where order_status = 1
查询出所有未支付的订单，一般这种订单是很少的，即使建了索引，也没法使用索引。

案例 8、复杂查询

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 了，而是采用其他的方式进行解决，比如使用 ES 等进行解决。

案例 9、asc 和 desc 混用

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

desc 和 asc 混用时会导致索引失效

案例 10、大数据

对于推送业务的数据存储，可能数据量会很大，如果在方案的选择上，最终选择存储在 MySQL 上，并且做 7 天等有效期的保存。那么需要注意，频繁的清理数据，会照成数据碎片，需要联系 DBA 进行数据碎片处理。