Mysql不止CRUD，聊聊索引( 五 ) 我想很多人对mysql的认知可能就

由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。
聚集索引的存储并不是物理上连续的，而是逻辑上连续的。这其中有两点：一是前面说过的页通过双向链表链接，页按照主键的顺序排序；另一点是每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。
2、二级索引（辅助索引）
对于辅助索引（Secondary Index），叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark）。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎表是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。
当通过辅助索引来寻找数据时， InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。
2.6、覆盖索引InnoDB存储引擎支持覆盖索引（covering index ，或称索引覆盖），即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。
CREATE TABLE `item` (`item_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,`store_id` bigint(20) unsigned NOT NULL COMMENT '所属店铺ID',`type` tinyint(3) unsigned NOT NULL COMMENT '商品类型 . 不同类型的商品, 保存到各自不同的表中. 参考 ',`state` tinyint(3) unsigned NOT NULL COMMENT '状态',`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,`create_user_id` bigint(20) unsigned NOT NULL,`last_modify_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,`last_modify_user_id` bigint(20) unsigned NOT NULL,`is_deleted` bigint(20) unsigned NOT NULL DEFAULT '0',PRIMARY KEY (`item_id`),KEY `fk_store_id` (`store_id`)) ENGINE=InnoDB AUTO_INCREMENT=332604631475558863 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='代表所有的物品，之前有把用户ID放进来，表示该物品所属的用户，但是考虑到如果有子账号的情况，物品难道属于这个子账号所属的用户吗？而且记录了创建人用户ID ，考虑这两个因素，因此不设置用户ID列'查看：explain select store_id,create_time from item where store_id > 332604504321036698 ;
文章插图
这里使用了全表扫描，没有走索引，然后我们把查询语句改为：explain select store_id from item where store_id > 332604504321036698 ;
文章插图
这样就变成了范围查询，走索引，因为索引中包含了需要查询的全部值，所以不需要再查询聚集索引，减少磁盘IO ，这样就可以提高速度。
2.7、Multi-Range Read优化Multi-Range Read优化的目的就是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于IO-bound类型的SQL查询语句可带来性能极大的提升。 Multi-RangeRead优化可适用于range ， ref ， eq_ref类型的查询。
Multi-Range Read的好处：

MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。
减少缓冲池中页被替换的次数。
批量处理对键值的查询操作

MRR的工作方式如下：

将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
将缓存中的键值根据RowID进行排序。
根据RowID的排序顺序来访问实际的数据文件。

实验：采用前面2.1节导入的数据：
explain select * from foundation_item.item where store_id > 332604504249736122 and store_id < 332604504249736201;

文章插图
MySQL5.6版本开始支持Multi-Range Read（MRR）优化，通过参数 optimizer_switch 的标记来控制是否使用MRR ，当设置mrr=on时，表示启用MRR优化。 mrr_cost_based 表示是否通过 cost base的方式来启用MRR.如果选择mrr=on,mrr_cost_based=off,则表示总是开启MRR优化。
例如设置：set optimizer_switch='mrr=on,mrr_cost_based=on';然后我们继续查看：