MYSQL 优化之延迟关联

延迟关联

• 能够建立索引的种类分为主键索引、唯一索引、普通索引三种，

• 覆盖索引只是一种查询的一种效果，利用覆盖索引来进行查询操作，避免回表。用 explain 的结果，extra 列会出现：using index。

• 利用延迟关联或者子查询优化超多分页场景。
  说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当
  offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL
  改写。
  正例：先快速定位需要获取的 id 段，然后再关联：

 SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

• 覆盖 索引 必须 要 存储 索引 列 的 值，假设 索引 覆盖 了 WHERE 条件 中的 字段， 但不 是 整个 查询 涉及 的 字段。 如果 条件 为 假（ false）， MySQL 5. 5 和 更早 的 版本 也 总是 会 回 表 获取 数据 行， 尽管 并不 需要 这 一行 且 最终 会被 过 滤掉。

使用延迟关联和覆盖索引对查询进行优化

• 测试结果来自 高性能MySQL

1. 查询 返回 了 一个 很大 的 结果 集， 因此 看不 到 优化 的 效果。 大部分 时间 都 花在 读取 和 发送 数据 上了。
2. 经过 索引 过滤， 过滤后 的 结果集 已经很少了，所以 优化效果 明显
3. 子 查询 效率 反而 下降 因为 索引 过滤 时 符合 第一个 条件 的 结果 集 已经 很小， 所以 子 查询 带来 的 成本 反而 比 从 表中 直接 提取 完整 行 更高。

• 当从一个数据集中过滤出很少的结果集时，优化效果显著。当数据集过滤出很多结果集、优化效果一般，数据集本身很少时，优化效果反而下降

SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是consts 最好。

• 摘自阿里Java开发规约

1. consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
2. ref 指的是使用普通的索引（normal index）。
3. range 对索引进行范围检索。
   反例：explain 表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index 级别比较 range还低，与全表扫描是小巫见大巫

【推荐】建组合索引的时候，区分度最高的在最左边。

正例：如果 where a=? and b=? ，如果 a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。
说明：存在非等号和等号混合时，在建索引时，请把等号条件的列前置。
如：where c>? and d=? 那么即使 c 的区分度更高，也必须把 d 放在索引的最前列，即索引 idx_d_c。

【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。

说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达
90%以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

【推荐】如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。

正例：where a=? and b=? order by c; 索引：a_b_c
反例：索引如果存在范围查询，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引 a_b 无法排序

施瓦茨(Baron Schwartz); 扎伊采夫(Peter Zaitsev); 特卡琴科(Vadim Tkachenko). 高性能MySQL(第3版)（博文视点图书） (Kindle位置4126). 电子工业出版社. Kindle 版本.