【学习笔记】mysql索引原理之InnoDB

InnoDB是事务安全的MySQL存储引擎，在OLTP的应用中，InnoDB应该作为核心应用表的首选存储引擎。

1、线程

后台的线程主要负责：刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据；将已修改的数据文件刷新到磁盘文件；保证在数据库发生异常情况下InnoDB能恢复到正常状态。

InnoDB存储引擎在一个叫master thread的线程上几乎完成了所有的功能。默认情况下， InnoDB存储引擎的后台线程有7个——4个IO thread、1个master thread、1个lock监控线程、1个错误监控线程。4个IO线程分别是insert buffer thread、log thread、read thread、write thread，线程数可以通过文件配置。

2、数据结构

InnoDB的所有数据的逻辑存储空间成为表空间，表空间由段组成。段主要分为数据段、索引段等，数据段其实就是B+Tree的叶子节点，而索引段就是内节点。

段里面又分为区，每个区由64个连续的页组成，每页16K，即每个区1M。对于大的数据段，最多可申请4个区，以保证数据的连续性。

页的数据结构如图所示，File Header、Page Header、File Trailer的大小是固定的，用来标示该页的一些信息，如Checksum、数据所在的索引层、该页的前一个页和后一个页的地址等。

Page Directory，页字典，存放的是记录在页中的相对地址，这是个稀疏目录，即并不是每条记录都会在目录中。因为即使在同一页中，记录的存放也是顺序存放的，比如想找到"H"，即使"H"不在目录中，那么只需找到"G"，再按"G"记录的指针顺序往下查找即可，所以无需每条记录都放入目录中。

因为页是最小存储单位，IO操作都是按页来的，所以聚簇索引的B+Tree叶子节点存放的其实并非记录本身，而是记录所在的页，然后把页读到内存中，再根据Page Directory二叉查找到记录，这个二叉查找是在内存中进行的，所以时间几乎可以忽略。

3、索引操作
对索引的插入、修改、删除操作，自然就得提到innodb的缓冲池。主键的索引是聚簇索引，所以聚簇索引的插入都是顺序读取，而非随机读取的，这样的操作无需使用缓冲池。但非聚簇索引的插入，由于B+树的特性，就需要离散的访问非聚集索引页，而且很大可能会涉及到页分裂等操作，插入的性能相对较低。缓冲池就是为了解决这类问题，非聚簇索引的插入，并非每一次都直接插入到索引页中，会先放入缓冲池，然后以一定的频率进行合并操作，再插入到索引页。

索引的添加或删除操作，会先创建一个新的临时表，把数据导入到临时表，在临时表上创建完或删除完索引，再把原表删除，最后重命名临时表。因此添加或删除索引，对大表而言，代价是比较巨大的，更有可能，会出现表丢失等现象。针对非聚簇索引，InnoDB支持一种快捷方式，对表加锁，这样就不需要临时表。

4、索引查询优化

InnoDB会监控对表索引的查找，建立hash索引，即自适应索引，这个索引并非对全表建索引，而是自动根据访问频率和模式来对某些页建立索引，不能人为干预的，用于等于查询，以提高查询效率。

多个索引的聚合查询，MYSQL会有一个统计机制来记录全表数据记录条数与各个索引的索引条数的比值，比如：全表记录10000条，city索引条数为1000条，那么其比值为10000/1000=10，status索引条数为10条，其比值为10000/10=1000，那么如果where city=1 and status=2，MYSQL会使用city索引，因为city索引的比值比status小很多，查出结果集自然会小很多。但如果两个索引的比值相差不大，MYSQL会把两个索引查出的结果集进行聚合。不过通常的查询都只会使用一个索引，毕竟两个索引查出的结果集进行聚合的代价也不小。

非聚簇索引的查询，最后一步通常是根据主键再到聚簇索引上查一次拿到记录，如果非聚簇索引查出1000条数据，就需要做1000次聚簇索引的查找。当然，MYSQL在这一步也进行了一些优化，比如先对主键排一下序再进行聚簇索引的查询，减少I/O。但如果只需要非聚簇索引上的数据，就无需再走这一步了。

涉及排序时，也是一样的，如果排序的字段与where条件的字段一起建立联合索引，那么查询速度会快很多。比如，where status=1 order by city，联合索引为status,city，索引本来就是有序的，拿到status=1条件过滤之后的结果，再到聚簇索引上查找一下就可以拿到最终结果了。但，如果联合索引是city,status，就不能用了，查询就慢啦。这就是什么左缀原则。