对MySQL索引的认识

MySQL索引结构的认识

前言

通过学习《高性能MySQL》和各路大神的博客，写下自己对于MySQL索引的认识

• 索引是存储引擎用于快速查询的一种数据结构，提高数据库的性能

索引的分类

索引是在不同存储引擎而不是服务器层面面实现的，不同的存储引擎索引的工作方式也不同

B-Tree索引

大多数MySQL引擎都支持这种索引，使用B-Tree数据结构来存储数据

B-Tree和B±Tree

• B-Tree

B-Tree，平衡多路查找树，如果每个节点，最多有N个孩子，那么这样的树就叫N阶B-Tree，每个节点中主要包含关键字和指向孩子的指针，最多能有几个孩子，取决于节点的容量和数据库的相关配置，通常情况下这个N是很大的。
B-Tree作为一种数据结构，有如下特征：

1.根节点至少包含两个孩子
2.树中每个节点至多含有N个孩子（N>=2)
3.除根节点和叶节点外，其它每个节点至少有ceil(N/2)个孩子。（ceil表示取上限，例如1.2的上限为2，1.1的上限也为2，非四舍五入）
4.所有叶子节点都位于同一层，即叶子节点的高度都是一样的
5.假设每个非终端节点包含n个关键字信息（P0,P1…Pn,k1…kn）

遵守上述规则，其目的就是尽量使每个索引块都尽可能多的存储数据，尽可能减少查找次数以提升效率。

• B±Tree

B+ -Tree是B-Tree的一个变体，其定义基本与B树相同，除了:

1.非叶子节点的子树指针与关键字个数相同，其表明B+树能存储更多的关键字
2.非叶子节点的子树指针P[i]，指向关键字值[K[i],K[i+1])的子树。
3.非叶子节点仅用来做索引，数据保存在叶子节点中。（B+树的所有检索都是从根部开始，直到搜索到叶子节点结束。)
4.所有叶子节点均有一个链指针，指向下一个叶子节点。（方便直接在叶子节点直接做范围统计）

• B+树相较于B树的优势：
  1.B+树的磁盘读写代价更低。
  2.B+树的查询效率更加稳定。
  3.B+树更有利于对数据库的扫描。

Hash索引

• Hash索引基于Hash表实现，存储引擎会对所有的索引列计算一个hashcode
• MySQL中，只有Memory引擎显式支持哈希索引，且是默认索引类型
• 哈希索引查询速度非常快，但是也有限制：
  1. 哈希索引只包含哈希值和指向行的指针，需要回表查询
  2. 哈希索引内部数据没有排序，无法用于排序
  3. 哈希索引只支持全索引查询，因为一个哈希值对应行的所有索引
  4. 哈希索引只支持等值比较查询（=， IN等）不支持范围查询
  5. 存在哈希冲突，和HashMap类似，当不同的索引列存在相同的hashcode，会出现链表结构，当这个链表很长的时候，查询速度会受影响，并且哈希冲突会影响 增删改的性能

小结：索引的优点

• 索引大大减少了服务器扫描的数据量
• 索引可以帮助服务器避免建立临时表和排序的资源消耗
• 索引可以将所及I/O变为顺序I/O

索引高性能策略

书中提到很多策略，我们重点来聊一下聚簇索引,联合索引和覆盖索引

聚簇索引

聚簇索引不是一种单独的索引类型，而是一种数据存储方式

• InnoDb引擎支持聚簇索引，MyISAM中都是非聚簇索引
• 由于聚簇索引在一个结构中保存了BTree索引和数据行，所以InnoDB中一个表亦只能支持一个聚簇索引，通过主键聚集数据（若不存在主键，会选择一个唯一的非空索引）
• 在提高查询速度的同时，也会存在相应的问题：
  1. 插入速度严重依赖于插入的顺序，这取决于BTree结构的建立
  2. 更新索引和插入索引后的“页分裂”的代价很高
  3. 二级索引（非聚簇索引）查询时需要回表查询
• InnoDB和MyISAM区别
  • InnoDB中的聚簇索引实际上就是整张表，叶子上存储着行的所有信息，还有事务id和MVCC相关的回滚指针
  • InnoDB中的二级索引和MyISAM中的非聚簇索引也有所不同，InnoDB中使用主键来作为指向行数据的指针，而MyISAM只是一个指针
    
• 由于插入顺序十分重要，我们可以使用一个auto increment的id作为主键

联合索引（多列索引）

• 在多个列上建立独立的单列索引很多情况下并不能提高MySQL的查询性能
• 可以建立联合索引，联合索引存在最左前缀匹配原则的概念

#假如有这样一张表：people，包含id(主键),name，age，height，sex
#我们建立一个联合索引
create index idx_obj on people(age asc, height asc)

• 这个索引不包含主键id，自然而然就是一个非聚簇索引
• 排序的原则是依据创建索引时，字段出现的顺序依次排序
  • 在这里就是先按照age排序，若age相同，再按照height排序
• 在查询时，最左匹配原则十分重要，这关乎于查询是否会走这条索引
  • 我是这么理解的，当查询条件中不包含左侧的字段，则该字段右侧的字段无法通过该索引来进行查询

  1.最左前缀匹配原则，MySQL会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如 a=3 and b=4 and c>5 and d=6,如果建立(a,b,c,d)顺序的索引，d是无法使用索引的，如果建立(a,b,d,c) 的索引则都可以使用到，a、b、d的顺序可以任意调整。
  2.=和in可以乱序，比如 a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式。

覆盖索引

如果一个索引包含所有需要查询的字段的值，我们称之为覆盖索引

• 优点：
  • 索引的条目通常远小于数据行的大小
  • 在InnoDB中存在聚簇索引，二级索引在叶子中保存了主键值，二级索引实现覆盖查询，可以避免回表查询
• MySQL中（5.5以前）不能再索引中执行like操作，5.6以后引入了索引下推优化，原本like查询需要有服务器来筛选数据，有了索引下推，like查询可以由MySQL根据索引来判断

关于查询优化器的补充

一条sql语句有不同的执行方案，在一条查询执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。
大致流程：

1. 根据搜索条件，找出所有可能使用的索引
2. 计算全表扫描的代价
3. 计算不同索引下执行查询的代价
4. 找出成本最低的的方案并执行

结语

MySQL的索引机制博大精深，我只是写出了我的一些认识，只是冰山一角，还是需要继续学习。