mysql 学习---索引优化学习

     正确的创建合适的索引 是提升数据库查询性能的基础。

 

一、前言

写数据库，我第一时间就想到了MySQL、Oracle、索引、存储过程、查询优化等等。

不知道大家是不是跟我想得一样，我最想写的是索引，为啥呢？

以下这个面试场景，不知道大家熟悉不熟悉：

面试官：数据库有几千万的数据，查询又很慢我们怎么办？

面试者：加索引。

面试官：那索引有哪些数据类型？索引是怎么样的一种结构？哪些字段又适合索引呢？B+的优点？聚合索引和非聚合索引的区别？为什么说索引会降低插入、删除、修改等维护任务的速度？……..

二、索引简介

  一句话简单来说，索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。一本 500 页的书，如果你想快速找到其中的某一个知识点，在不借助目录的情况下，那我估计你可得找一会儿。同样，对于数据库的表而言，索引其实就是它的“目录”。

2.1、索引是什么？

索引是为了加速对表中数据行的检索而创建的一种分散存储的 数据结构。

如下图：索引和表数据的关系。

2.2、为什么用索引

索引能极大的减少存储引擎需要扫描的数据量

索引可以把随机IO变成顺序IO

索引可以帮助我们在进行分组、排序等操作时，避免使 用临时表

三、索引的常见模型

3.1、哈希表

     哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的值即 key，就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。

    不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。

图中，User2 和 User4 根据身份证号算出来的值都是 N，但没关系，后面还跟了一个链表。假设，这时候你要查 ID_card_n2 对应的名字是什么，处理步骤就是：首先，将 ID_card_n2 通过哈希函数算出 N；然后，按顺序遍历，找到 User2。

需要注意的是，图中四个 ID_card_n 的值并不是递增的，这样做的好处是增加新的 User 时速度会很快，只需要往后追加。但缺点是，因为不是有序的，所以哈希索引做区间查询的速度是很慢的。

你可以设想下，如果你现在要找身份证号在 [ID_card_X, ID_card_Y] 这个区间的所有用户，就必须全部扫描一遍了。

所以，哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎。

3.2、有序数组

有序数组，它就比较优秀了呀，它在等值查询的和范围查询的时候都很Nice。

那它完全没有缺点么？

不是的，有序的适合静态数据，因为如果我们新增、删除、修改数据的时候就会改变他的结构。

比如你新增一个，那在你新增的位置后面所有的节点都会后移，成本很高。

那照你这么说他根本就不优秀啊，特点也没地方放。

此言差矣，可以用来做静态存储引擎啊，用来保存静态数据，例如你2019年的支付宝账单，2019年的淘宝购物记录等等都是很合适的，都是不会变动的历史数据。

3.3、平衡二叉树

其结构图如下：

他的时间复杂度是O(log(N))，为了维持这个时间复杂度，更新的时间复杂度也得是O(log(N))，那就得保持这棵树是完全平衡二叉树了。

缺点：

  它太深了

      数据处的（高）深度决定着他的IO操作次数，IO操作耗时大

   它太小了

       每一个磁盘块（节点/页）保存的数据量太小了 没有很好的利用操作磁盘IO的数据交换特性， 也没有利用好磁盘IO的预读能力（空间局部性原理），从而带来频繁的IO操作。

3.4 、B-Tree 多路平衡查找书

这里的多路是只在一个节点上可以存储多个值，根据值的范围查找对应的叶子节点。

可以发现同样的元素，B树的表示要比完全平衡二叉树要“矮”，原因在于B树中的一个节点可以存储多个元素。

B树其实就已经是一个不错的数据结构，用来做索引效果还是不错的。

3.5、B+Tree索引

      是B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。

    

其实很简单，我们看一下上面的数据结构，最开始的Hash不支持范围查询，二叉树树高很高，只有B树跟B+有的一比。

B树一个节点可以存储多个元素，相对于完全平衡二叉树整体的树高降低了，磁盘IO效率提高了。

而B+树是B树的升级版，只是把非叶子节点冗余一下，这么做的好处是为了提高范围查找的效率。

提高了的原因也无非是会有指针指向下一个节点的叶子节点。

小结：到这里可以总结出来，Mysql选用B+树这种数据结构作为索引，可以提高查询索引时的磁盘IO效率，并且可以提高范围查询的效率，并且B+树里的元素也是有序的。

3.5.1 B+Tree索引 和B-Tree  区别

• 1，B+节点关键字搜索采用闭合区间
• 2，B+非叶节点不保存数据相关信息，只保存关键字和子节点的引用
• 3，B+关键字对应的数据保存在叶子节点中
• 4，B+叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系

3.5.2、为什么选用B+Tree索引

• B+树是B-树的变种（PLUS版）多路绝对平衡查找树，他拥有B-树的优势
• B+树扫库、表能力更强
• B+树的磁盘读写能力更强
• B+树的排序能力更强
• B+树的查询效率更加稳定（仁者见仁、智者见智）

那么，一个B+树的节点中到底存多少个元素最合适你有了解过么？

额这个这个？卧*有点懵逼呀。

过了一会还是没想出，只能老实交代：这个不是很了解咳咳。

你可以换个角度来思考B+树中一个节点到底多大合适？

B+树中一个节点为一页或页的倍数最为合适。

为啥？

因为如果一个节点的大小小于1页，那么读取这个节点的时候其实也会读出1页，造成资源的浪费。

如果一个节点的大小大于1页，比如1.2页，那么读取这个节点的时候会读出2页，也会造成资源的浪费。

所以为了不造成浪费，所以最后把一个节点的大小控制在1页、2页、3页、4页等倍数页大小最为合适。

你提到了页的概念，能跟我简单说一下么？

首先Mysql的基本存储结构是页(记录都存在页里边)：

• 各个数据页可以组成一个双向链表

• 而每个数据页中的记录又可以组成一个单向链表

• - 每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录

• 以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

所以说，如果我们写 select * from user where username='名字' 这样没有进行任何优化的sql语句，默认会这样做：

• 定位到记录所在的页

• - 需要遍历双向链表，找到所在的页

• 从所在的页内中查找相应的记录

• - 由于不是根据主键查询，只能遍历所在页的单链表了

很明显，在数据量很大的情况下这样查找会很慢！看起来跟回表有点点像。

四 、Mysql 的索引模型

4.1、MyIsam 的索引模型

mylsam 主键索引和二级索引 b+树叶子节点存储的都是数据物理地址，并不存储具体数据。

主键索引如下“”

 二级索引和主键索引存储结构一样：

4.2、InnoDB 的索引模型

       据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。

      主键索引（也称 聚集索引）结构图如下：

   

二级索引  如下：

 

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

根据上面的索引结构说明，我们来讨论一个问题：基于主键索引和普通索引的查询有什么区别？

• 如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
• 如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

五、索引优化

5.1 、索引维护

     优先考虑上一段提到的“尽量使用主键查询”原则

      B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面这个图为例，如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。

      而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。

除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。

    当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

基于上面的索引维护过程说明，我们来讨论一个案例：

你可能在一些建表规范里面见到过类似的描述，要求建表语句里一定要有自增主键。当然事无绝对，我们来分析一下哪些场景下应该使用自增主键，而哪些场景下不应该。

自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。

插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。

也就是说，自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。

除了考虑性能外，我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？

由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。

显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

有没有什么场景适合用业务字段直接做主键的呢？还是有的。比如，有些业务的场景需求是这样的：

1. 只有一个索引；

2. 该索引必须是唯一索引。

你一定看出来了，这就是典型的 KV 场景。

由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。

这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

5.2、回表

     回表大概就是我们有个主键为ID的索引，和一个普通name字段的索引，我们在普通字段上搜索：

      sql select * from table where name = '张三'

       执行的流程是先查询到name索引上的“张三”，然后找到他的id是2，最后去主键索引，找到id为2对应的值。

       回到主键索引树搜索的过程，就是回表。不过也有方法避免回表，那就是覆盖索引。

5.3、覆盖索引

    如果查询列可通过索引节点中的关键字直接返回，则该索引称之为 覆盖索引。

     覆盖索引可减少数据库IO，将随机IO变为顺序IO，可提高查询性能

     这个其实比较好理解，刚才我们是 select * ，查询所有的，我们如果只查询ID那，其实在Name字段的索引上就已经有了，那就不需要回表了。

   覆盖索引可以减少树的搜索次数，提升性能，他也是我们在实际开发过程中经常用来优化查询效率的手段。

   很多联合索引的建立，就是为了支持覆盖索引，特定的业务能极大的提升效率。

5.4、最左前缀原则

  B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

为了直观地说明这个概念，我们用（name，age）这个联合索引来分析。

可以看到，索引项是按照索引定义里面出现的字段顺序排序的。

当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。

如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是"where name like ‘张 %’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。

可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

基于上面对最左前缀索引的说明，我们来讨论一个问题：在建立联合索引的时候，如何安排索引内的字段顺序。

这里我们的评估标准是，索引的复用能力。因为可以支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

5.5 索引下推

上一段我们说到满足最左前缀原则的时候，最左前缀可以用于在索引中定位记录。这时，你可能要问，那些不符合最左前缀的部分，会怎么样呢？

我们还是以市民表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写的：

mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;

你已经道了前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录 ID3。当然，这还不错，总比全表扫描要好。

然后呢？

当然是判断其他条件是否满足。

在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

图 3 和图 4，是这两个过程的执行流程图。

图 3 无索引下推执行流程

图 4 索引下推执行流程

在图 3 和 4 这两个图里面，每一个虚线箭头表示回表一次。

图 3 中，在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。

图 4 跟图 3 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

5.6、列的离散性

离散性是指 这个列中重复字段情况。

在创建索引的时候 优先选择离散型好的列

5.7 复合索引

单列索引 节点中关键字[name]

 联合索引 节点中关键字[name,phoneNum]

单列索引是特殊的联合索引

联合索引列选择原则

• 1，经常用的列优先 【最左匹配原则】
• 2，选择性（离散度）高的列优先【离散度高原则】
• 3，宽度小的列优先【最少空间原则】

总结

索引在数据库中是一个非常重要的知识点！

上面谈的其实就是索引最基本的东西，N叉树，跳表、LSM我都没讲，同时要创建出好的索引要顾及到很多的方面：

• 最左前缀匹配原则。这是非常重要、非常重要、非常重要（重要的事情说三遍）的原则，MySQL会一直向右匹配直到遇到范围查询 （>,<,BETWEEN,LIKE）就停止匹配。

• 尽量选择离散性高的列作为索引，区分度的公式是 COUNT(DISTINCT col)/COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少。

• 索引列不能参与计算，尽量保持列“干净”。比如， FROM_UNIXTIME(create_time)='2016-06-06' 就不能使用索引，原因很简单，B+树中存储的都是数据表中的字段值，但是进行检索时，需要把所有元素都应用函数才能比较，显然这样的代价太大。所以语句要写成 ：create_time=UNIX_TIMESTAMP('2016-06-06')。

• 尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。

• 单个多列组合索引和多个单列索引的检索查询效果不同，因为在执行SQL时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格的索引(经指正，在MySQL5.0以后的版本中，有“合并索引”的策略，翻看了《高性能MySQL 第三版》，书作者认为：还是应该建立起比较好的索引，而不应该依赖于“合并索引”这么一个策略)。

• “合并索引”策略简单来讲，就是使用多个单列索引，然后将这些结果用“union或者and”来合并起来。

索引列的数据长度能少则少。

索引一定不是越多越好，越全越好，一定是建合适的。

匹配列前缀可用到索引 like 9999%，like %9999%、like %9999用不到索引；

Where 条件中 not in 和 <>操作无法使用索引；

匹配范围值，order by 也可用到索引；

多用指定列查询，只返回自己想到的数据列，少用select *；

联合索引中如果不是按照索引最左列开始查找，无法使用索引；

联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引；

联合索引中如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引；