MySQL索引实现原理剖析及使用策略优化

• 索引是什么

  有些人对索引的解释说索引就像是书的目录，用来提高查询的效率，我觉得是废话，说了和没说一样。不如直接看看MySQL官方给出的解释：“With the exception of spatial indexes, InnoDB indexes are B-tree data structures. ”。因此，从这段描述中，可以知道，索引就是一种数据结构，通过这种特定的数据结构以及查找算法来实现对数据库中数据记录查找的快速性。
  现在来想一想假如没有索引在数据库中查询一条记录会是怎么样的流程，假如我们执行如下的SQL语句：

select * from user where user_id=123

在没有索引的情况下使用顺序查找的算法，一条记录一条记录地遍历知道找到user_id等于123的记录。假如数据库中user表中的记录非常多，假如几千万条记录，想想都觉得慢。因此，我们就想在需要查找的记录上建立一种数据结构，例如AVL树或者红黑树，然后利用它们的结构特点来完成查找，我们知道这两种数据结构能在O(logn)的时间复杂度完成查找任务，但是从MySQL的官方文档中我们可以看到说，它使用的是一种叫做B-Tree的结构，那为什么不用上面我们说的两种结构而要使用B-Tree这种结构呢？这个得从磁盘的原理和I/O说起。

• 磁盘存取原理

  传统的机械硬盘由磁头，磁道，扇区，柱面组成。磁盘的存取和主存的存取原理是不一样的，磁盘存取需要耗费机械运动的时间，当需要从磁盘读取数据时，系统会将数据逻辑地址传给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点，磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间，然后磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间。
  可以看到磁盘本身读取时间就很慢，因此在进行磁盘I/O的时候，一般并不是按需读取，而是预读，这个预读的的大小一般是一个page的整数倍。page是计算机操作系统对内存和磁盘管理的最小单元，有时也称为一个块。内存和磁盘交换数据的单元一般就是一个page，一个page通常是4KB。当程序需要在内存中读取某个数据时，会先检查这个数据的逻辑地址是否在内存中，如果找不到就会触发一个缺页异常，这个时候操作系统就会向磁盘发出读取数据的I/O信号，然后磁盘会找到数据的起始地址然后往后读取一个或者多个page的大小存入内存，将缺页异常返回。

• B-Tree和二叉树在I/O上的性能

  这里需要申明一点，在这篇博客中默认你已经知道了二叉树和B-Tree的定义和特点，假如你不知道，你可以在任何数据结构和算法的书籍中找到。我们知道当数据量非常大的时候，二叉树的高度是比较大的，而索引本身就是一个很大的数据文件，因此索引会以索引文件的方式保存在磁盘上。每次查找检索时，我们都会读取磁盘上的数据，我们知道二叉树的查找效率是O(logn)，因此在最坏的情况下，我们需要进行大概logn的I/O操作，也就是二叉树的高度h，由于二叉树的结点的度只能小于等于2，因此这个高度就比较高，相应所做的I/O操作也就越多，因而时间效率就很低下。
  我们知道在B-Tree或者B+ Tree中，结点的度一般都很大，因此树的高度就很矮。在数据库的设计时可以根据预读的原理，将一个结点设计为4KB，而在Innodb中，每个page的大小默认为16KB。因此每次读取就会读取16KB的磁盘大小，这样就完全可以装下一个结点或者多个结点。B-Tree的高度一般在2~4层，也就意味着，I/O的次数在2~4次，这个次数是可以接受的。所以，B-Tree的磁盘存取效率上要比二叉树高很多。

• Innodb B+Tree 索引

  在MySQL中，索引是在存储引擎层面来实现的，因此不同的存储引擎可能支持不同的索引，在Innodb中索引是通过B-Tree的变种结构B+Tree来实现的。Innodb的索引又分为聚集索引和辅助索引。
  所谓聚集索引就是根据主键创建一棵B+ Tree，聚集索引的叶子结点存放了表中的所有记录。也就是说在Innodb中，数据文件本身就是一个索引文件，它就是按照B+ Tree结构组织的文件。这一点与MyISAM存储引擎中的索引是不一样的，

我们看到Innodb的数据文件是按照主键来索引的，因此在创建表的时候，必须指定主键，如果没有显示指定主键，Innodb会自动创建一个6字节的列作为主键。
  辅助索引就是根据索引的键来创建的一棵B+ Tree ，与聚集索引不同的是，辅助索引的叶子结点存放的仅仅是索引键的值以及该索引键值所指向的主键。也就是说，如果通过辅助索引来查找数据，那么在查找到辅助索引的叶子结点后，最终依然可能通过主键的聚集索引来查找最终的记录。由于辅助索引不包含行记录的所有数据，因此每个page可以存放更多的键值，所以它的高度要小于聚集索引。
  在MyISAM中，没有聚集索引，它的所有索引都是辅助索引，它的叶子结点中存放的是真正数据记录的物理位置。因此可以看到它和聚集索引的方式是完全不同的，当然除了它们都是使用的B+Tree结构。在MyISAM中，索引文件和数据文件是分离的，而在Innodb中，索引文件就是数据文件，数据文件就是索引文件。在MyISAM中，辅助索引存放在MYI文件中，而真正的数据记录存放在MYD文件中。

• 联合索引

  联合索引是指，对表中的多个列进行索引，它的创建方式

create table t {
	a int,
	b int,
	primary key (a),
	key idx_a_b (a, b)
}engine=innodb

联合索引本质上还是一棵B+ Tree,不同的是联合索引的键值数量不是1，而是大于等于2，我们看看下面的结构

  我们看到叶子结点中的键是按照（a,b）的顺序进行存放的。如果对于 select * from t where a = xxx and b = xxx，对于这条语句可以用（a,b）索引查询。对于 select * from t where a = xxx这条语句也可以使用联合索引，但是对于select * from t where b = xxx，对于这条语句是不能使用（a,b）索引的。因为叶子结点上b的值并不是按照顺序存放的。

• 索引的使用以及优化

  现在我们知道了索引的结构，那么我们如何正确地使用索引呢？什么样的数据适合用索引呢？我们从结构的角度谈谈优化。
（1）低修改
  我们知道，索引虽然可以提高查询的效率，但是也并不是就没有缺点，例如索引本身非常大，因此它也需要一定的空间。再者，当我们在插入或者删除更新记录的时候，索引为了保持结构的正确性，需要维护符合B+ Tree的性质。因此需要旋转或分裂，然后重新调整整个结构，当数据量大的时候这就是一个开销很大的操作。所以当数据的更新操作很频繁的时候并不适合建立索引。
（2）高选择性
  那么什么样的列适合建立索引呢？试想假如某一个列的取值域范围很小，给这样的列建立索引合适吗？肯定是不合适的，对于具有这样性质的列我们称为低选择性列，例如性别（一般情况只有男，女两个取值）。对于这样的列是不适合建立索引的。我们可以通过show index命令中的cardinality列查看索引是否具有高选择性。
（3）属性上计算中是不能命中索引的
  看看这条语句：

select * from order where YEAR(date) < = '2020'
# 使date上建立了索引，也会全表扫描，可优化为值计算：
select * from order where date < = CURDATE()
# 或者：
select * from order where date < = '2020-01-01'

（4）最左前缀
  复合索引最左前缀，并不是指SQL语句的where顺序要和复合索引一致。假如在（login_name, password）列建立联合索引，下面这两种方式都是会命中索引的

select * from user where login_name=? and passwd=?

select * from user where passwd=? and login_name=?

# 这种情况是不行的
select * from user where passwd=?

（5）索引提示（INDEX HINT）
  MySQL支持索引提示，可以显示告诉SQL优化器使用哪个索引。当某条SQL语句可以选择的索引非常多，这时优化器选择执行计划的时间开销就比较大，可能大于SQL语句本身，因此这时可以显示指定索引不让优化器进行执行分析。显示指定有来年高中方式，use index 和 force index。前者我们虽然指定了使用某个索引，但也只是该苏优化器可以使用这个索引，但是优化器可能还是使用自己的判断。后者可以强制指定使用某个索引。
除此之外，还有MRR优化，ICP优化，都是从查询层面进行优化的方式。