一文带你深入理解MySQL的索引

1、Mysql的逻辑层次

• 如果你想理解一条查询SQL具体是怎么执行的，就需要完全彻底地理解索引，且清楚MySQL整个的层次结构
  

总体来讲，MySQL分为Server层和存储引擎两部分。

• Server层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL大多数的核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能（存储过程、触发器、视图等）都在这一层实现
• 存储引擎负责数据的存储和提取，其架构模式是插件式的，支持InnoDB、MyISAM、Memory等存储引擎，现最常用的存储引擎是InnoDB（自MySQL5.5以后，InnoDB是默认的存储引擎）

1.1、连接器

• 连接器负责和客户端建立连接、获取权限、维持和管理连接，连接命令（$后面需要分别跟上对应的ip、port、user）：

mysql -h$ip -P$port -u$user -p

• 在完成TCP握手后，连接器需要认证用户身份（通过用户名和密码），如果认证失败，则会收到用户拒绝访问的信息，客户端程序结束执行；如果认证成功，连接器会到权限列表中查询出用户所拥有的权限，此后，连接中的权限判断逻辑都将依赖于先前读取到的权限
• MySQL客户端如果太长时间没有操作，连接器就会自动断开，其时间有参数wait_timeout控制，默认是8小时（28800秒），具体操作如下：
  • 登录管理员的账号，查看等待的超时时间，可输入命令：
    show global variables like ‘wait_timeout’;
    修改超时时间：
    set global wait_timeout=36000;

• MySQL在执行过程中临时使用的内存是管理在连接对象中的，这些资源会在连接断开时才释放

MySQL一直保持连接好还是每次都新建连接好？长短连接如何抉择？

• 长连接：在一个连接上可以连续发送多个数据包，在连接保持期间，如果没有数据包发送，则需要双方链路进行检测
  • 长连接多用于操作频繁、点对点的通讯，且连接数不能太多的情况（每次TCP连接需要三次握手），如MySQL的连接就用的是长连接，如果MySQL用短连接频繁的通信会造成socket错误，且频繁的socket 创建也浪费资源
• 短连接：是相对于长连接来说的概念，指在数据传送过程中，只在发送数据时才去建立连接，数据发送完成后，则断开此连接，即每次连接只是完成一次需求中的数据传送
  • 短连接适用于并发量大，每个用户无需频繁操作的情况，如WEB网站的http服务一般都用短链接，长连接对于服务端来说会太耗费资源，WEB网站成千上万的连接用短连接会更节省资源

1.2、查询缓存

• MySQL获取到查询请求后，会先去查询缓存中查看是否执行过该语句，如果执行过，其语句及结果会以key-value键值对的形式缓存在内存中，ke是查询语句，value是查询结果，如果查询缓存中可以找到对应的key（SQL语句），则对应的value就会直接返回给客户端；如果SQL不在查询缓存，就会执行后续的操作，执行完成后，执行结果会被存入查询缓存
• 查询缓存存在很大问题，如果缓存失效地非常频繁（有对表的更新操作），表上所有的查询缓存都会被清空。对于更新操作频繁的数据库来说，缓存命中率非常低，所以，MySQL 8.0+直接将查询缓存的整块功能删掉了

1.3、分析器

• 在执行SQL语句之前，需要先对SQL进行语法分析和词法分析，如分析SQL：select * from user where Id=1;
  • 分析器会先做"词法分析"，MySQL需要识别出SQL语句中的字符串分别是什么，表示的含义是什么。通过select关键字，识别出该sql是个查询语句，字符串"user"识别成"user表"，字符串"ID"识别成"Id列"
  • 再根据词法分析的结果，分析器会根据语法规则，判断输入输入SQL语句是否满足MySQL语法，如果不满足，就会收到“You have an error in your SQL syntax”的错误提醒

1.4、优化器

• 经过分析器对SQL的分析后，在开始执行之前，还要经过优化器的处理。在表有多个索引时，优化器可以决定使用哪个索引，或在SQL有多表关联（join）时，决定各个表的连接顺序，例如，执行如下SQL语句：
  • 既可以先从表t1里面取出c=10的记录的ID值，再根据ID值关联到表t2，再判断t2里面d的值是否等于20；也可以先从表t2里面取出d=20的记录的ID值，再根据ID值关联到t1，再判断t1里面c的值是否等于10。
  • 这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同（视t1、t2表的数据量而定），而优化器的作用是决定选择使用哪个执行方案

1.5、执行器

• MySQL通过分析器知道了SQL要执行什么操作（what），通过优化器知道了该怎么做（how），其后，就需要进入执行器阶段，真正开始执行SQL语句（需要判断用户对该表有无执行操作的权限，如果没有，就会报错），如执行如下SQL：select * from user where Id=1;
  • 1）调用InnoDB引擎接口取user表的第一行数据记录（无索引状态下），判断Id值是不是1，如果不是则跳过，如果是则将这行存在结果集中；
  • 2）调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
  • 3）执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

经过如上对MySQL层次结构的讲解，你应该清楚 一条SQL语句完整的执行过程是怎样的（会经过哪些模块，各个模块又具体会做哪些事情）

2、MySQL索引

• 索引是MySQL高效获取数据的数据结构（what），可加快查询速度（不使用索引就需要全表扫描），其代价是需要额外的存储空间
  • 创建索引：CREATE <索引类型> INDEX <索引的名字> ON <table_name>(列的列表)；
  • 删除索引：DROP INDEX <index_name> ON <table_name>;
  • 查询索引：SHOW INDEX FROM <table_name>;
  • 强制指定索引：sql select <列名> from <table_name> where <查询条件> force index(${index_name})

2.1、索引类型

• 按照实现的数据结构分类：
  • B+树索引（InnoDB、MyISAM存储引擎）
  • Hash索引（Memory存储引擎）
• 按照约束类型分类：
  • 普通索引：CREATE INDEX
  • 唯一索引：CREATE UNIQUE INDEX
  • 主键索引：建表自带，（PRIMARY KEY(id) ）
• 按照索引列的数量区分：
  • 单列索引
  • 联合索引（复合索引、组合索引）【使用最左前缀匹配原则】
• 按照存储的内容区分：
  • 聚簇索引（主键索引）：存储索引列与数据（索引和数据存放在一起）
  • 非聚簇索引（辅助索引/二级索引/非主键索引）：存储索引列和主键

补充：

• 最左匹配原则：以最左边的索引字段为起点，任何连续的索引都可匹配
  • 索引：index(a, b, c )
    以下查询语句是否可以使用此索引：

where a = 1     // 可用
where a = 1 and c = 1 // 索引效果与上面一样(连续索引才可以匹配.由于没有关于索引b的条件,则索引c失效)
where b = 1   // 没有关于索引a的条件,索引b失效
where a = 1 and b = 1 // 可用
where b = 1 and a = 1  // 可用,【与条件先后顺序无关】

2.2、索引模型

• 索引是一个通用的概念，并不局限于数据库，常见的索引模型有是三种：哈希表、有序数组、搜索树

1）哈希表

• 哈希表是一种以键-值（key-value）存储数据的结构，输入待查找的key，就可以找到其对应的Value，哈希表把值放在数组中，通过哈希函数把key换算成确定的位置，然后把value放在数组的这个位置

• 哈希冲突：多个key值经过哈希函数的换算，会出现同一个值的情况，其解决方案是拉出一个链表（拉链法，java的hashmap也是如此解决冲突）
  

• 哈希表实现索引的优点是维护成本低、查询速度快，缺点是只能适用于等值查询的场景（范围查询不合适）
  

2）有序数组

• 有序数组在等值查询和范围查询场景中的性能都非常优秀，但维护成本高，执行删除、新增元素的操作效率较低，设计大量元素的移动
  

3）搜索树

• 二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子，如果要查询数据ID_card_n2，需要按照图中的搜索顺序UserA ->UserC->UserF ->User2路径得到，其时间复杂度是O(logN)，为维持O(logN)的查询复杂度，就需要保持这棵树是平衡二叉树，更新操作的时间复杂度也是O(logN)【平衡搜索二叉树上增删改查操作的时间复杂度均为O(logN)】
  
• 搜索树实现索引的优点：维护成本低、查询速度快、支持等值和范围查询，其缺点是树越高，IO次数就越多，访问就越慢
  • 如：100万节点的平衡二叉树，树高20，一次查询可能需要访问20个数据块，假设在从磁盘随机读一个数据块需要10 ms左右的寻址时间，也就是说，对于一个100万行的表，如果使用二叉树来存储，单独访问一个行可能需要20个10 ms的时间
    

2.3、InnoDB的记录格式

• 数据行（或记录）在磁盘上的存放方式，称为行格式/记录格式（what）
• 指定和修改行格式
  • 指定：CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
  • 修改：ALTER TABLE 表名 ROW_FORMAT=行格式名称
• 行格式的类型：
  • Redundant：MySQL5.0以前，冗余行格式
  • Compact：MySQL5.6默认
  • Dynamic：MySQL5.7+默认，动态行格式，和Compact区别不大，仅在数据记录溢出的情况下存在区别，后面重点介绍
  • Compressed：压缩行格式，采用压缩算法节约磁盘空间，仅在数据归档时才会使用

2.3.1、Dynamic行格式

1）记录的额外信息

• 变长字段的长度列表：变长字段（占用存储字节数不固定的字段，例如VARCHAR）的实际长度
• NULL值列表：
  • 标示的列：除了主键列、被NOT NULL修饰的列之外，其他的列都可以存储NULL值
  • 如何标示：二进制位的值为1时，代表该列的值为NULL；二进制位的值为0时，代表该列的值不为NULL
  • 占用的字节数：使用的二进制位个数必须是整数个字节的位来表示（如果不是，则在高位补0）
• 记录头信息：占用5个字节（40位）
  • delete_mask：记录是否被删除的判断标志
  • next_record：下一条记录的相对位置（按照主键值/索引列值的下一条记录）
  • record_type：记录类型，0表示普通记录，1表示B+树非叶子节点的记录，2表示最小记录，3表示最大记录
  • heap_no：当前记录所属的页号

2）记录的真实数据

• 隐藏列：
  • row_id：行的ID，非必须
  • transaction_id：事务ID
  • roll_pointer：回滚指针
• 数据列：存储的真实数据

补充：

• Dynamic与Compact处理溢出页的区别：前者不记录字节的前缀，只记录20字节的指针
• 行溢出：当记录中的数据太多，当前页无法全部存储，会把多余的数据存储到其他页中的现象

2.3.2、面试答疑

• 面试题1：delete语句之后，空间会不会自动释放？
  答：不会自动释放，会将要delete的数据记录标记为已删除（标识记录头信息的delete_mask），页内已删除的记录加入到垃圾链表中，下次向此页插入数据时，优先使用被标识为删除数据的空间
• 面试题2：表创建时无主键，会怎么办？
  答：会先选择第一个被NOT NULL修饰的唯一索引（Unique Key）作为聚簇索引（主键），若无，则会在记录中添加隐藏列的row_id作为主键
• 面试题3：为什么查询数据时不建议select *?
  答：
  • 1）会造成不必要的磁盘IO，查询操作可看做一种磁盘I/O的行为（前提是查询记录不再查询缓存中），查询的字段越多，读取的内容就越多，网络IO的开销就越大，故而加重网络延时。默认情况下，结果集占用的内存空间最大是net_buffer_length，不会因为多几个字段就占用额外的内存空间
  • 2）可能无法使用覆盖索引，造成回表查询
  • 3）回表时命中溢出页

扩展：

• 索引覆盖：是指查询操作使用联合索引，覆盖了要查询的字段，数据库不用去进行回表，从而减少IO（或者说在一棵索引树上就能获取SQL所需的所有列数据，无需回表）
• 回表：通过辅助索引查询到的数据，不包含用户查询的全量数据，需要用主键去聚簇索引中再次查询的过程（存在随机IO）

2.4、InnoDB的页格式

2.4.1、索引页/数据页

• 页：InnoDB管理 存储空间的基本单位，是MySQL中磁盘和内存交互的基本单位，一个页的大小一般是16KB
• 数据页/索引页格式的示意图：
  
  • 文件头：File Header，页的一些通用信息
    • 页号
    • 检验和：与页尾一起，校验页是否完整，在执行刷盘操作时，优先写入校验和，通过与文件尾的校验和，共同判断是否存在页没有同步完全的情况
    • 上页的页号+下页的页号：组成B+树的双向链表
  • 页头：Page Header，数据页专有的信息
    • 本页中的记录数量（包括最小和最大记录以及标记为删除的记录）
    • 第一个已经标记为删除记录的地址（便于找到垃圾链表后插入数据）
    • 还未使用的空间最小地址（为了插入数据）
  • 虚记录：最大虚记录和最小虚记录（与数据链表形成闭环）
    • 最小虚记录：记录的next_record指向本页中最小的记录（next_record表示下一条记录的相对位置，见前面Dynamic行格式中记录的额外信息）
    • 最大虚记录：本页中最大记录的next_record 指向当前的记录
  • 用户记录/记录堆：实际存储的记录内容，由多个行记录组成（既存在有效记录，即有效的数据链表，又有已删除记录，即垃圾链表）
  • 未分配空间：页中尚未使用的空间，即如果没有垃圾链表，下次需要向此页插入数据时使用到的分配空间
  • Slot区/页目录：页中某些记录的相对位置，以便快速查询
  • 文件尾：与页头共同校验页是否完整
    

2.4.2、记录的页内插入

• 页内插入数据记录的位置可以是垃圾链表和未分配的空间，垃圾链表的页头记录了第一个已经标记为删除记录的地址，未分配空间的页头记录了还未使用的空间最小地址
• 页内插入记录的策略包括物理有序和逻辑有序
  

  2.4.3、记录的页分裂

• 如果页无法存储插入的数据，就需要通过页分裂来转存数据，如下图所示，假设页10只能装下3条记录，现在要插入id=4的记录
  
• 在对页中的记录进行增删改操作的过程中，我们必须通过诸如记录移动的操作来始终保证某个状态一直成立，该状态即为下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值，该过程称为页分裂。基于此，我们就不难理解，为什么mysql主键不建议使用UUID了（MySQL主键需要有序）

2.4.4、页内记录的二分查找

• Slot区：也称为Page Directory区，页目录区，记录页中某些数据记录的相对位置

• Slot：对于页内记录（含最大记录和最小记录，不包括标记为已删除的记录）进行分组，每个组最后一条记录的地址偏移量即是槽（slot）

• 页内记录分组的依据：

  • 最小记录所在的分组只能有1条数据记录
  • 最大记录所在的分组拥有的记录条数只能在1~8条之间
  • 剩下的分组中记录的条数范围只能是在4~8条之间

• 如何二分查找：

  • 通过二分法确定该记录所在的槽，并找到该槽所在分组中主键值最小的那条记录二分过程：(high槽 + low槽) / 2 得到当前槽，判断当前槽所指记录的大小，决定high/low的变化查找主键值最小的那条记录：(high – 1)槽所指的记录的next_record
  • 通过记录的next_record属性遍历该槽所在的组中的各个记录
    

2.5、B+树索引详解

2.5.1、B+树实现聚簇索引

• 1）页与页之间存在双线链表（通过页的文件头区域的上页页号与下页页号来组织连接），此时查找数据记录的办法如下：从左向右遍历，看当前记录是否在最小记录与最大记录之间，若不在，则去下页继续判断，若在，则在页内进行二分查找
  

• 2）为每个页建立目录，并标识上页的用户记录中最小的主键值（key）和页号（page_no），此时查找数据记录的方法：在目录页中进行二分查找（距离查找记录最近的所在页），再在目标页内进行二分查找
  
  

• 3）当"目录"太多，一页无法装下，则可以分为多页来分级存储，其查找方式：先从根页查找“距离此记录最近的记录”所在的页，再到索引页“距离此记录最近的记录”，最后到叶子节点二分查找，即从根页依次向叶子节点上的页的顺序，逐步完成查找

  举例说明：如下图所示，在一个存储更高级目录项的 页33 ，这个页中的两条记录分别代表页30和页32，如果用户记录的主键值在 [1, 320) 之间，则到页30中查找更详细的目录项记录，如果主键值 不小于320 的话，就到页32中查找更详细的目录项记录（record_type：记录类型，0表示普通记录，1表示B+树非叶子节点的记录，2表示最小记录，3表示最大记录，记录的相对位置由next_record来标识，这在前面Dynamic行格式中记录的额外信息提到过）
  

B+树上各个节点的说明：

• 根节点：存放索引信息的页（目录项的页）
• 非叶子节点（内节点）：存放索引信息的页，下一数据页中用户记录的主键值必须大于上页中用户记录的主键值
• 叶子节点（外节点）：存放索引信息与实际数据的页，下一数据页中用户记录的主键值必须大于上页中用户记录的主键值
• 总的来说，叶子节点存放索引和实际数据，非叶子节点存放索引信息，且非叶子节点之间存在双向链表（便于范围查询时横向遍历元素）
  

2.5.2、B+树实现非聚簇索引

• InnoDB支持聚簇索引和非聚簇索引，MyISAM只支持非聚簇索引，我们可以InnoDB和MyISAM支持的索引类型来理解
• 下一数据页中用户记录的索引列的值，必须大于上页中用户记录的索引列的值，因此，非聚簇索引存储的索引信息一定会有主键

• InnoDB创建的索引存储在数据文件中，即索引与数据不分离（存在后缀为.idb的文件中）；InnoDB的辅助索引（非聚簇索引/非主键索引）data域存储的是相应记录 主键的值而非地址（引用主键作为datat域）；InnoDB只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录
• MyISAM的索引文件和数据文件分开存储（.sdi（描述表结构）、.MYD（数据）、.MYI（索引）），索引文件仅仅保存数据记录的地址；主键索引和辅助索引实现一致，索引文件仅仅保存数据记录所在页的指针（物理位置），通过这些物理地址来读取页，进而读取被索引的记录；在MyISAM中却需要进行一次回表操作，因为MyISAM中建立的索引都是二级索引（辅助索引）
• InnoDB要求表必须有主键 （ MyISAM可以没有 ），如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段（row_id）作为主键，这个字段长度为6个字节，类型为长整型。

2.5.3、聚簇索引和非聚簇索引的区别

InnoDB实现聚簇索引和非聚簇索引都采用B+树来完成记录的存储（一张表只能有一个聚簇索引，可有多个非聚簇索引），其区别在于：

• 聚簇索引：

  • 聚簇索引的叶子节点存储的是全量数据，聚簇索引只有在搜索条件是主键时才能发挥作用（B+树中的数据是根据主键排序的），其非叶子节点存储的是记录的主键+页号，叶子节点存储的是完整的用户记录
  • 页内的记录 是按照主键的大小顺序排成一个 单向链表，页和页之间 也是根据页中记录的主键的大小顺序排成一个 双向链表

• 非聚簇索引：

  • 非聚簇索引的叶子节点存储的是索引列的值+主键，其后需要根据主键再去聚簇索引中查找完整的数据，如果想以别的列col1作为搜索条件，则需要使用col1创建非聚簇索引（创建B+树），再根据主键值去聚簇索引中再查找 一遍完整的用户记录，其非叶子节点存储的是col1列+页号，叶子节点存储的是col1列+主键这两个列的值
  • 页内的记录 是按照从 col1列 的大小顺序排成一个 单向链表，页和页之间 也是根据页中记录的 col1列 的大小顺序排成一个 双向链表

2.6、索引的使用

2.6.1、执行计划

• 查看执行计划：explain +SQL，如：
  
  参数解释：

• id：由一组数字组成，表示一个查询语句中各个子查询的执行顺序

  • id相同执行顺序由上至下；id不同，id值越大，则优先级越高，越先被执行
  • id为null，表示一个结果集，不需要使用该SQL查询，常出现在包含union等查询语句中

• table：表示SQL语句查询的表

• partitions：表分区、表创建时指定通过哪个列进行表分区

• possible_keys：查询语句涉及字段上存在的索引，possible_keys为null时，就要考虑当前SQL是否需要优化了

• key：MySQL在查询中实际使用的索引，如果没有使用索引，则显示位NULL，如果查询操作使用了覆盖索引，则仅出现在key列表中（覆盖索引：索引列覆盖了要查询的字段/数据列）

• key_length：索引长度

• ref：表的连接匹配条件，即哪些列或常量被使用在查找索引列上的值
  

• rows：获取结果集的行数需要实际扫描的行数，往往rows越小，效率越高（大部分SQL优化，都是在减小rows值）

• extra：包含信息有Using index、Using filesort、Using temporary

  • Using index：使用覆盖索引
  • Using where：使用where字句来过滤结果集
  • Using filesort：使用文件、非索引列进行排序时出现，较消费性能
  • Using temporary：使用了临时表
    

• select_type：每个子查询的查询类型，一些常见的查询类型如下图：
  
  关于select_type的说明可参考文章：Explain执行计划详解

• type：访问类型，依次从好到差分别为：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，重要的访问类型会详细说明

  • 除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引
  • system：表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index
  • const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const
  • eq_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref
  • ref：不要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见于辅助索引的等值查找，或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现
  • fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引
  • unique_subquery：用于where中的in形式子查询，子查询返回不重复的值
  • range：索引范围扫描，常见于使用>、<、is null、in、like、between等运算符的查询中
  • index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引
  • index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、使用索引排序或者分组的查询（查询的值以及返回的结果恰好在某个辅助索引里，但是无法通过索引列查询，全部扫描此索引）
  • all：全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录
    

2.6.2、使用索引的注意事项

• 1）要只为用于搜索、排序或分组的列创建索引
• 2）要为区分度大的列创建索引，重复数据多的字段不应设为索引，例外情况，如：状态字段为有效/无效，仅根据“有效”查询时，可以建立
• 3） 更新频繁的列不应设置索引
• 4）联合索引，区分度大的列放在第一位
• 5）索引列在比较表达式中单独出现才可以适用索引 （ a > 2 可以，a + 1 > 1不适合）
• 6）为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有AUTO_INCREMENT属性
• 7）尽量使用覆盖索引进行查询，避免回表带来的性能损耗。
• 8）使用IN查询，IN中的数量不能太大（<=2000），避免mysql走错索引
• 9）Mysql会根据I/O和CPU成本去估算，选择最终执行计划

2.6.3、索引下推

• 索引下推：Index Condition Pushdown，全称为索引条件下推，MySQL5.6+推出的功能，可有效减少回表次数，提高效率
  例如：检索出表中“名字第一个字是张，而且年龄是10岁的所有男孩”，
  
• 在MySQL 5.6之前（不能索引下推），只能从ID3开始一个个回表，到主键索引上找出数据行，再依次对比其他字段值（name、age、ismale）
  
• 而使用MySQL 5.6+ 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对联合索引中包含的字段（name，age）先做判断，直接过滤掉不满足条件的记录，减少回表次数。
• 

2.7、其他

2.7.1、innodb区/段/表空间

• 区：物理位置上连续的64个页是一个区，设计目的：根据空间局部性原理，物理连续的空间发生IO的概率更高
• 段：存储相同类型记录的区算作一个段，一个段包含256个区，一个表空间中会有多个段（“叶子节点段”、“非叶子节点段"），设计目的：范围查询是对B+树 叶子节点中的记录进行顺序扫描， 而如果不区分叶子节点和非叶子节点，都把节点代表的页面放到申请到的区中的话，进行范围扫描的效果就大打折扣
• 表空间：方便更好地管理页，一个.idb结尾的表为一个独立的表空间

2.7.2、平衡二叉树，红黑树，B树和B+树

1）平衡二叉树

• 平衡二叉树（AVL树）是最基本的数据结构，可以保证较高的查询效率，其特点如下：
  • 左右两个子树的高度差的绝对值不超过1，且左右两个子树都是平衡二叉树
  • 可以避免树的某侧全部为空，退化为单链表的问题，

AVL的生成演示

• AVL树存在的问题：执行查询操作，不能将所有数据加载到内存中，只能逐个节点加载（一页数据一次IO），如果使用二叉树作为结构，则磁盘的IO次数和索引树的高度呈正相关。平衡二叉树会由于树深度过高而造成磁盘IO读写频繁，从而导致效率低下
  
• 为了提高查询效率，就需要减少磁盘IO的次数（降低树的高度），即将树的每层分叉，如将二叉树改成三叉树：

2）红黑树

• 红黑树：非严格的平衡树，树的节点被标志为红色或黑色，设计红黑树的目的：完善平衡树的维护问题，红黑树读取性能略逊于AVL，方便维护，其特点如下：
  • 根节点和每个叶节点一定为黑
  • 如果一个节点是红色的，则其子节点需要是黑色的
  • 对于任一结点，到叶结点的每一条路径都包含相同数目的黑色节点
  • 两次旋转即可重新达到平衡，红黑树的长子树不超过短子树的两倍
    
• 红黑树适用场景：
  • 存储网络地址、IO多路复用epoll的实现，采用红黑树组织管理sockfd
  • ngnix中，用红黑树管理timer
  • java中TreeMap，jdk1.8的hashmap的实现

3）B树/B+树

• B/B+树是为了磁盘或其它存储设备而设计的平衡多路查找树(相- 对于二叉， B/B+树内每个节点有多个分支)，与红黑树相比，在相同数量节点的情况下，B/B+树的高度远远小于红黑树的高度，B树的操作效率取决于访问磁盘的次数（树的高度）
• B树和B+树的差异：
  • B+树中非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都存储在叶子节点中，而B树中，非叶子节点存储索引和数据记录
  • B+树的叶子节点按照关键字的大小升序连接，对整棵树的遍历只需要一次性地遍历所有叶子节点即可（便于区间查询）；B树遍历整棵树，需要对树的每层都进行遍历（相邻的元素在内存中可能不相邻，故缓存命中性没有B+树好）

2.7.3、自适应哈希索引

• 自适应哈希索引：MySql基于InnoDB引擎，额外提供的一套索引机制（用户无法干预，系统内部完全自动的行为），当InnoDB注意到某些索引值使用的比较频繁时，会在内存中基于B-Tree之上，再建立哈希索引，使B-Tree索引具有哈希索引的优点，如：快速查找，时间复杂度O(1)，可使用命令来查看：

SHOW ENGINE INNODB STATUS \G ;

2.7.4、2-3树和2-3-4树

• 多叉树（multiway tree）通过重新组织节点，减少节点数量和树的高度，增加分叉，允许每个节点可以有更多的数据项和子节点，2-3树，2-3-4树就是多叉树
• 2-3树就是多路查找树，一个节点都具有两个孩子(称其为 2 节点)或三个孩子(称其为 3 节点)，所有叶子节点都在同一层，示意图如下：
  
• 2-3-4树：是2-3树的扩展， 另外引入了 4 节点，且任何节点都遵守 BST 二叉排序树的规则