器 | 一文理解Mysql 索引的前世今生

---

主题使用方法：https://github.com/xitu/juejin-markdown-themes

theme: juejin

highlight: juejin

一、引言

对于大多数业务场景来说，数据库的瓶颈在于查询的效率。为了优化这个瓶颈，第一无非就是合理地设置数据库各方面的参数；第二当然是为必要的字段设置索引，这个是提高查询效率最有效的手段，没有之一。总结，优化千万条，索引第一条。

二、索引概念

数据库索引，是数据库管理系统(DBMS)中一个排序的数据结构，以协助快速查询、更新数据库表中的数据。- 官方概念。

1.索引到底是什么

我们把数据集比喻成一本字典，如果我们想查询某个字，最坏的情况是我们需要遍历这本字典所有的数据，才能查询到目标字。索引通俗的讲其实就是这本字典的目录，通过翻阅目录，帮助查询者更高效地获取数据。所以说索引出现的目的是帮助MySQL高效获取数据。

2.索引的优势

• 提高数据检索的效率，降低对数据库的IO成本 - 检索
• 通过索引列对数据排序，减低数据排序的成本，降低CPU的消耗 - 排序

3.索引的劣势

索引不是银弹，为了提高查询效率，肯定在其它方便有所牺牲。

• 索引会占据磁盘的空间 - 以空间换时间
• 索引虽然会提高查询效率，但是会降低更新表的效率 - 维护索引的成本
  • 每次对表的增删改才做，MYSQL不仅要保存数据，还要保存或者更新对应的索引文件

4.索引数据结构模型

索引的数据结构不单单只能用B树，B+树，还有有很多数据结构可以用来做索引。

4.1哈希表

HashMap，哈希表它的优点是等值查询比较优秀；缺点是做区间查询的速度很慢，原因是Hash索引它不是有序的。它适用于一些等值查询的场景，比如 Memcached 及其它一些NoSQL 引擎。

4.2有序数组

Array,有序数组它的优点是等值查询和范围查询场景中的性能就都非常优秀，如果仅仅看查询效率，有序数组就是最好的数据结构。缺点是更新数据，需要挪动后面所有的记录，成本很高。适用于静态数据存储引擎。

4.3搜索树

搜索树有很多，二叉查找树，平衡二叉树，多路平衡二叉树(B树，B+树)等，下面内容详细阐述。

三、Mysql中的索引

1.索引概述

Mysql服务层不支持索引，索引是存储层提供的功能，一般存储在磁盘中。索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构。正确的创建合适的索引是提升数据库查询性能的基础。

2.为啥需要索引

• 索引能极大的减少存储引擎需要扫描的数据量索引可以把随机IO变成顺序IO

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

3.索引底层数据结构的演进

3.1二叉查找树

缺陷

虽然满足评价查询效率为O(logn)，但是在最坏的情况下，数据结构会变成一张链表，属于不稳定的查询

3.2平衡二叉查找树

缺陷

它太深了，数据处的(高)深度决定着他的IO操作次数，IO操作耗时大；并且太小了，每一个磁盘块(节点/页)保存的数据量太小了，没有很好的利用操作磁盘IO的数据交换特性，也没有利用好磁盘IO的预读能力(空间局部性原理)，从而带来频繁的IO操作。

3.3多路平衡查找树

多路平衡查找树是为了磁盘或其它存储设备而设计的一种多叉平衡查找树，它的高度一般都是2-4这个高度，树的高度直接影响IO读写的次数。

3.1Btree-

3.2Btree+

3.3Btree- 和Btree+的区别

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

3.4优胜者Btree+

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

4.Mysql常用两大存储引擎的索引实现

4.1MyISAM

它使用的是Btree-的数据结构实现索引，并且索引和数据分开保存，索引存储的是数据存储的地址引用，同时主键索引和辅助索引并无直接关联。

模型

4.2Innodb

它使用Btree+的数据结构实现索引，分为主键索引和非主键索引(普通索引，二次索引)，基于主键为索引来组织数据的存储，即为聚簇索引，数据库表行中数据的物理顺序与键值的逻辑(索引) 顺序相同，基于非主键的索引查询，先进行普通索引，找到对应的主键ID值，再进行主键索引，最终找到查询值，这个过程称为回表。

模型

5.索引的指标

5.1列的离散型

列的离散型好坏，直接决定Mysql服务层的优化器是否做出决策去走索引。那么离散度如何判断呢？列中的数据差异化越大，离散度越高。如下图，所以离散度最高的列是name列。

那么如果选择sex列建立索引，最终优化器大概率会判断执行效率约等于全盘扫描，会拒绝执行索引搜索。

5.2最左匹配原则

对索引中关键字进行计算(对比)，一定是从左往右依次进行， 且不可跳过。主要是用在联合索引中，往往可以通过调整联合索引中索引的先后顺序，达到少维护索引的目的.

5.3索引下推(待补充)

5.4联合索引

创建索引的一种方式，创建时候可使用关键字[c1,c2]。使用原则为经常用的列优先 【最左匹配原则】；选择性(离散度)高的列优先【离散度高原则】；宽度小的列优先【最少空间原则】。

5.5覆盖索引

如果查询列可通过索引节点中的关键字直接返回，则该索引称之为覆盖索引，覆盖索引可减少数据库IO，将随机IO变为顺序IO，可提高查询性能。

6.Mysql索引使用总结

• 索引列的数据长度能少则少
• 索引一定不是越多越好，越全越好，一定是建合适的
• 匹配列前缀可用到索引like 9999%，like %9999%、like %9999用不到索引
• Where 条件中 not in 和 <>操作无法使用索引； 匹配范围值，order by 也可用到索引
• 多用指定列查询，只返回自己想到的数据列，少用select *； 联合索引中如果不是按照索引最左列开始查找，无法使用索引
• 联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引
• 联合索引中如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引