对于Mysql索引的理解分享

首先，对于日常的Mysql调优过程当中，索引的优化一定是不可避免的。但是，索引的底层数据结构是什么样子的，在日常的调优过程当中有可以从那些方面考虑这些优化点都是值得记录的。

索引数据结构

可用的索引数据结构大致可以分为以下三种：有序数组，Hash和树形结构。

• 其中Hash索引提供了O(1)时间复杂度的等值查询效率，但是并不支持范围查找和排序，因此适合与临时表优化大连接查询的场景。
• 有序数组则提供了良好的查询(随机访问)和范围查询的能力，但是在数组中进行增删数据的时候，会额外造成O(N)时间复杂度的元素迁移动作成本过高，因此也没有成为主流的索引数据结构。
• 树形结构首先提供了O(logn)时间复杂度的插入删除和查找的能力，并且数据有序，可以减少范围查询和排序的开销。但是InnoDB引擎并没有使用二叉树作为其索引数据结构，这是因为二叉树的数据存储能力并不高，试想如果一张表有上百万的数据，整个树的高度就有20多层，在极端情况下(整个数据文件都不在内存当中)，就需要产生20多次的磁盘io，才可以找到数据，而如果底层存储硬件采用的是机械硬盘，其随机读写的开销会很大。因此InnoDB使用了B+树(一种多叉树数据结构)作为其底层实现。并且B+树在其叶子节点上存储了数据信息，这样整个树的数据是有序的也方便了范围查询。

尽量使用自增主键

这是因为整个数据的叶子节点并不直接存储数据，而是存储的数据页(在数据页的内部可以认为是二分法定位数据)。为了保证B+树的结构，如果插入的数据不是有序的，可能会造成数据页中数据的迁移，若插入数据的位置刚好是数据页的最后一个位置，那么原数据就需要申请新页并进行迁移(页分裂)。页分裂不仅会占用CPU，还会造成页面空洞。

在Mysql的实现当中，如果我们在建表过程当中，定义了自增主键，默认会是一个4字节的无符号整数，即单表能够插入的数据量的上限是 2^32-1。当我们的主键自增至上限是再次插入数据就会报duplicate key 的错误。若在我们见表的过程当中没有定义自增主键，那么系统会默认使用系统维护的rowid作为唯一标识。Mysql内部维护了一个全局变量dict_sys.row_id，这个变量在设计时为8个字节，而真正实现时为6个字节，并且全局所有表公用这个字段。但是如果这个字段达到上线2^48-1之后会重复使用，有可能会覆盖原始数据。在一般情况下，系统的可靠性要优于可用性，因此在建表时尽量使用自定义的自增主键。

数据查询操作

例如以下这行sql:

select * from table t where id = 1;

由于我们知道，InnoDB表为主键索引表，即主键索引上存储了数据的全部信息，当能够使用id时，Mysql优化器会指定执行计划，选择主键索引为目标索引，然后在主键索引上执行树搜索找到对应的数据页和数据信息并返回。而对于普通索引来说，则并不相同。

对于普通的二级索引来说，底层的叶子节点只存储了索引字段信息和id信息，若想要获取其他的额外信息，则需要通过id回到主键索引当中获取数据。这个过程我们一般称之为回表操作。

select * from table t where a = 3;

若业务当中对应了大量的回表操作，由于id不是有序的，又会造成随机读写，这边会拉低系统的性能。若底层存储使用的是机械硬盘，则可以考虑开启MRR(Multi-Range-Read)。【注:设置optimizer-switch= “mrr=on,mrr_cost_based=on”】开启MRR之后的范围回表操作会将所有需要回表的操作缓存在一块叫做read_rnd_buffer的内存空间当中，并对id进行排序。拍好序之后尽量批量回表操作。这极大的提高了回表的效率：

1. 并行的批量操作要快于串行的单行操作。
2. 顺序读写的效率要高于随机读写的效率。