深入理解MySQL篇二

• • MySQL索引
  • • 索引的基本概念
    • 常见的索引类型
    • • B-Tree索引(平衡多路查找树)
      • B+Tree索引
      • 覆盖索引
      • 哈希索引
      • 空间数据索引（R-Tree）
      • 全文索引
    • 索引优化策略

MySQL索引

索引的基本概念

什么是索引？

索引是存储引擎用于快速找到记录的一种数据结构。这是索引的基本功能。

索引对于良好的性能是至关重要的，尤其是当表中的数据越来越大的时候，索引对性能的影响非常大。

要理解索引，举一个形象的例子：一本书的目录<索引>部分，如果我们要找到一本书的某一个特定的章节内容，一般都会先看书的目录<索引>部分，找到对应的页码。

MySQL中，存储引擎就用类似的方法使用索引：先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。总结的说：索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

常见的索引类型

索引具有多种类型，不同的存储引擎的索引的工作方式并不相同，也不是所有的存储引擎都支持所有类型的索引，即使多个存储引擎支持同一种索引，其底层实现也可能不同。

B-Tree索引(平衡多路查找树)

B-Tree索引是使用B-Tree数据结构实现存储的。在B-Tree之前先了解下磁盘的相关知识。因为B-Tree索引使用的过程中涉及页和页分裂的相关概念。

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K，在MySQL中可通过如下命令查看页的大小：

mysql> show variables like 'innodb_page_size';

​系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。这就是B-Tree索引高效率的本质。

B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块，如下图为一个三层结构的B-Tree:

B-Tree节点的存储信息的大小根据实际情况而定。例如把int类型的数据作为索引和varchar类型的数据作为索引大小是不同的。上图中，每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子节点的指针，指针存储的是子节点所在磁盘块的地址。

查找过程：例如查找关键字29：

• 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
• 比较关键字29在区间（17,35），找到磁盘块1的指针P2。
• 根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
• 比较关键字29在区间（26,30），找到磁盘块3的指针P2。
• 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
• 在磁盘块8中的关键字列表中找到关键字29。

上述过程经历了3次磁盘I/O操作，和3次内存查找操作。对于实际查找B-Tree索引，I/O操作是影响效率的主要因素。

B+Tree索引

B+Tree是在B-Tree基础上的一种优化，InnoDB存储引擎就是用B+Tree实现其索引结构。

问题：为什么对B-Tree进行优化？

上面B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。实际中每块磁盘的空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量就会减少，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。总结的说就是，增大每个磁盘存储的索引数量，降低树的高度，提高查询效率。

从上述中可以知道，B-Tree和B+Tree的几点不同：

• 非叶子节点只存储键值信息。
• 所有叶子节点之间都有一个相互指向的指针。
• 数据记录都存放在叶子节点中

如下图为B+Tree的结构图：

B+Tree的优点如何具体的体现呢？

例如：InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），
指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K
个键值（因为是估值，为方便计算，这里的K取值为〖10〗^3）。也就是说一个深度为3的B+Tree索引
可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。

也就是说，查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。效率非常高！

B+Tree的索引分类

在InnoDB中。索引类型分为主键索引也称为聚簇索引和非主键索引也称为二级索引、非聚簇索引。

主键索引也称为聚簇索引：

顾名思义就是把主键直接作为索引。结构如下图：

其中树结构中的15,、56、77…都是表的主键。整个B-Tree就是以主键所谓key值进行构建的。叶子结点存储的是表对应数据。

优点：搜索速度快

缺点：使用非自增列主键时，插入麻烦。存在“页分裂问题”，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中， 整体空间利用率降低大约50%。

为了解决上述缺点，官方建议使用自增长主键作为索引，在B+Tree结构中，因为自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。

插入连续的数据：

插入非连续的数据：

从上可以看出，树在进行自我调整过程中，分裂次数明显变少！

非主键索引也称为二级索引、非聚簇索引：

该索引类型是以非主键字段作为索引。例如表主键为ID的用户表中：以表中的名称为索引，以表中的身份证号作为索引。这样的索引都是非聚簇索引。结构如下图：

上图就是一名称为索引构建的B+Tree树，叶子结点存储的并不是该索引对应的数据。也是索引列对应的主键值。因此在实际查询的时候，使用非聚簇索引需要先搜索非聚簇索引树，得到主键值后，在使用主键索引树再查询一次得到数据。这个过程就是回表。

覆盖索引

上述我们说明了什么是回表。回表操作使得我们的查询效率变低了！由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有可能经过索引优化，避免回表过程呢？

如果执行的语句是select ID from T where k between 3 and 5，其中ID为主键，k为二级索引，这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面， 索引k已经“覆盖了”我们的查询需求，我们称为覆盖索引。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用 由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

B-Tree索引支持的查询原则

• 全值匹配：全值匹配指的是和索引中的所有列进行匹配，即精确匹配。
• 最左前缀原则：用（name，age）这个联合索引来分析。在B+Tree结构中，当查找到name字段的人时，树结构中有多个张三字段，age不同的人。当定位到第一个name=“张三”后，剩下的就向后遍历就能的到所有需要的结果。能直接遍历是因为相邻节点之间有指针连接。
• 匹配列前缀：也可以只匹配某一列的值的开头部分。例如前面提到的索引可用于查找所有以J开头的姓的人。这里也只用到了索引的第一列。
• 匹配范围值：例如前边提到的索引可用于查找姓在Allen和Barrymore之间的人。这里也只使用了索引的第一列。
• 精确匹配某一列并范围匹配另外一列：前边提到的索引也可用于查找所有姓为Allen，并且名字是字母K开头(比如Kim,Karl等)的人。即第一列last_name全匹配，第二列first_name范围匹配。

因为索引树中的节点是有序的，所以除了安置查找外，索引还可以用于查询中的order by操作（按顺序查找）。

索引下推：

上面说了满足最左前缀原则的时候，最左前缀可以用于在索引中定位记录，但是那些不满足最左前缀的部分呢？索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索 引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

无索引下推执行过程：

有索引下推的执行过程：

减少了回表的次数。在(name,age)索引里面，特意去掉了age的值，这个过程InnoDB并不会去看age的值， 只是按顺序把“name第一个字是’张’”的记录一条条取出来回表。因此，需要回表4次。
有索引下推的时候， InnoDB在(name,age)索引内部就判断了age是否等于10，对于不等于10的 记录，直接判断并跳过。在我们的这个例子中，只需要对ID4、ID5这两条记录回表取数据判 断，就只需要回表2次。

哈希索引

哈希索引是(hash index)基于哈希表实现的，只有精确匹配索引所有列【索引可能不止一列，可能是组合索引】的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code）,哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

例如：

select name from record where id=3; 

MySQL的查询过程：MySQL先计算id=3的哈希值，并使用该值寻找对应的记录指针，从图可以知道对应的索引值为3036，对应的address为0X3C—>在磁盘中找到对应的地址数据“Paul”。

主要的限制：

• 哈希索引无序存储。不能进行排序
• 哈希不支持部分人索引列匹配查找。例如：索引列(A,B)上建立哈希索引，就不能只有索引列A进行查询。
• 哈希只支持等值查找，不支持范围查询；例如：id>4;
• 哈希冲突的存在，导致维护索引的代价比较高。

使用场合：多表关联查询；

支持的存储引擎：Memory引擎（唯一哈希索引）、NDB集群引擎（唯一哈希索引）、InnoDB引擎（自适应哈希索引）

InnoDB引擎（自适应哈希索引）,当InnoDB注意到某些索引值被使用的非常频繁的时候，它就会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也拥有一些哈希索引的优点。比如快速查找。该功能是自动配置的，用户可以关闭该功能。

空间数据索引（R-Tree）

MyISAM表支持空间索引，可以用作地理数据的存储。和B-Tree索引不同，这类索引无须前缀查询。空间索引会从所有维度来索引数据。

全文索引

全文索引是一种特殊类型的索引。它查找的是稳中的关键词，而不是直接比较索引中的值，全文搜索和其他几类索引的匹配方式完全不一样。全文索引更类似于搜索引擎，而不是简单的where条件匹配。

索引优化策略

正确的创建和使用索引是实现高性能查询的基础。

1.独立的列：

通常我们会看到一些查询语句的不恰当，导致MySQL不使用已有的索引。“独立的列“就是指索引的列不能是表达式的一部分，也不能是函数的参数，例如，下列查询语句：

select actor_id from actor where actor_id +1 = 5;

actor_id +1 = 5式子，mysql是不能解析的，也就无法使用索引。

2.前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得大且慢，通常在这种情况下索引开始的部分字符。节约索引空间，提高索引效率。前缀索引是一种能够是索引更小更快的方法，但是缺点就是不能使用order by和group by，也无法使用前缀索引做覆盖扫描。

3.多列索引并选择合适的索引列顺序

4.使用聚簇索引和覆盖索引也是一种优化的索引创建方式

5.使用索引扫描来做排序

6.压缩（前缀压缩）索引：MyISAM使用前缀压缩来减少索引的大小。

7.剔除冗余和重复索引以及长时间不使用的索引

8.索引可以让查询锁定更少的行：InnoDB的行锁的效率很高，但是锁定行必然会带来额外的开销。因此使用索引减少访问的行数可以减少锁的数量。

参考：

《高性能MySQL》《MySQL实战45讲(林晓斌)》

https://www.cnblogs.com/vianzhang/p/7922426.html

https://segmentfault.com/a/1190000015821650

https://www.cnblogs.com/liqiangchn/p/9060521.html