10 个经典场景带你玩转 SQL 优化，Java 笔试题算法题
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 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
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;
案例 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
评论