优化器如何避免离散读?你是如何避免的?
MySQL 5.6之前， 优化器在进行离散读决策的时候， 如果数据量比较大， 会选择使用聚集索引， 全表扫描。
MySQL5.6版本开始支持Multi-Range Read （MRR） 优化 。 Multi-Range Read优化的目的就是为了减少磁盘的 随机访问， 并且将随机访问转化为较为顺序的数据访问， 这对于IO-bound类型的SQL查询语句可带来性能极 大的提升 。 Multi-Range Read优化可适用于range， ref， eq_ref类型的查询。
MRR优化有以下几个好处：
❑MRR使数据访问变得较为顺序 。在查询辅助索引时， 首先根据得到的查询结果， 按照主键进行排序， 并按 照主键排序的顺序进行书签查找。
❑减少缓冲池中页被替换的次数 。 （顺序查找可以对一个页进行顺序查找， 无需离散加载数据页）
❑批量处理对键值的查询操作。
对于InnoDB和MyISAM存储引擎的范围查询和JOIN查询操作， MRR的工作方式如下：
❑将查询得到的辅助索引键值存放于一个缓存中， 这时缓存中的数据是根据辅助索引键值排序的。 ❑将缓存中的键值根据RowID进行排序。
❑根据RowID的排序顺序来访问实际的数据文件。
举例说明： SELECT * FROM salaries WHERE salary＞10000 AND salary＜40000;
salary上有一个辅助索引idx_s， 因此除了通过辅助索引查找键值外， 还需要通过书签查找来进行对整行数据的 查询 。 当不启用Multi-Range Read特性时， 看到的执行计划如图：
若启用Mulit-Range Read特性， 则除了会在列Extra看到Using index condition外， 还会看见Using MRR选项
Multi-Range Read还可以将某些范围查询， 拆分为键值对， 以此来进行批量的数据查询 。 这样做的好处是可以在 拆分过程中， 直接过滤一些不符合查询条件的数据， 例如：
SELECT*FROM t
WHERE key_part1＞=1000 AND key_part1＜2000
AND key_part2=10000;
表t有 （key_part1， key_part2） 的联合索引， 因此索引根据key_part1， key_part2的位置关系进行排序 。若没有 Multi-Read Range， 此时查询类型为Range， SQL优化器会先将key_part1大于1000且小于2000的数据都取出， 即 使key_part2不等于1000 。待取出行数据后再根据key_part2的条件进行过滤 。 这会导致无用数据被取出 。 如果有 大量的数据且其key_part2不等于1000， 则启用Mulit-Range Read优化会使性能有巨大的提升。
倘若启用了Multi-Range Read优化， 优化器会先将查询条件进行拆分， 然后再进行数据查询 。就上述查询语句而 言， 优化器会将查询条件拆分为 （1000， 10000） ， （1001， 10000） ， （1002， 10000） ， …， （1999 ， 10000） ， 最后再根据这些拆分出的条件进行数据的查询。
我是如何优化的： 在非必要的情况下， 拒绝使用 select *； 在必须 select * 的情况下， 尽量使用MySQL 5.6 + 的 版本开启MRR； 在必须 select * 的情况下且MySQL 小于 5.6 版本下， 可以根据数据量进行离散读和聚集索引两 种情况下的性能进行对比， 必要时采用force index语句强制指定索引。
