Mysql数据库索引详解

一、前言

随着我们业务量不断的增大，会发现查询数据库的时间变的越来越长，其实就是数据库到达了性能的瓶颈，这时候需要我们对其进行优化操作，可能我们第一时间就会想到：加索引。没错，这是优化的一个方向，但是我们不能只知道加索引，还应该知道索引都有哪些类型啊，索引是怎样的一种结构啊，聚合索引和非聚合索引的区别啊等等一系列相关的问题。

下面我就来分别介绍下这些知识点

二、正文

1.Mysql索引都有哪几种类型，分别有什么特点？

主要有两种类型：

• 哈希索引
• B+ Tree索引

哈希索引：

哈希索引能以 O(1) 时间进行查找，但是失去了有序性：

• 无法用于排序与分组。
• 只支持精确查找，无法用于部分查找和范围查找。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

B+ Tree索引：

是大多数 MySQL 存储引擎的默认索引类型。

• 因为不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。
• 因为 B+ Tree 的有序性，所以除了用于查找，还可以用于排序和分组。
• 可以指定多个列作为索引列，多个索引列共同组成键。
• 适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。

InnoDB 的 B+Tree 索引分为主索引和辅助索引。

主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找，这个过程也被称作回表。

2.各种树常见的特性

• AVL树

平衡二叉树，一般是用平衡因子差值决定并通过旋转来实现，左右子树树高差不超过1，那么和红黑树比较它是严格的平衡二叉树，平衡条件非常严格（树高差只有1），只要插入或删除不满足上面的条件就要通过旋转来保持平衡。由于旋转是非常耗费时间的。所以 AVL 树适用于插入/删除次数比较少，但查找多的场景。

二叉树是有序的，所以是支持范围查询的。

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

那我们用平衡二叉树作为索引可以嘛？

不可以，索引也不只是在内存里面存储的，还是要落盘持久化的，如果在一个千万级的数据中查询一个数据，会发现树很高，查询的成本就会随着树高的增加而增加。为了节约成本很多公司的磁盘还是采用的机械硬盘，这样一次千万级别的查询差不多就要10秒了，这谁顶得住啊。

• 红黑树

通过对从根节点到叶子节点路径上各个节点的颜色进行约束，确保没有一条路径会比其他路径长2倍，因而是近似平衡的。所以相对于严格要求平衡的AVL树来说，它的旋转保持平衡次数较少。适合，查找少，插入/删除次数多的场景。（现在部分场景使用跳表来替换红黑树，可搜索“为啥 redis 使用跳表(skiplist)而不是使用 red-black？”）

• B树

B树也称B-树(这个读B树，不要读B减树啊，中间的“-”是连接符),它是一颗多路平衡查找树。我们描述一颗B树时需要指定它的阶数，阶数表示了一个结点最多有多少个孩子结点，一般用字母m表示阶数。当m取2时，就是我们常见的二叉搜索树。

一颗m阶的B树定义如下：

1）每个结点最多有m-1个关键字。

2）根结点最少可以只有1个关键字。

3）非根结点至少有Math.ceil(m/2)-1个关键字。

4）每个结点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。

5）所有叶子结点都位于同一层，或者说根结点到每个叶子结点的长度都相同。

想要了解更加详细的，建议看下小灰漫画算法，通俗易懂：
漫画：什么是B-树？

从上面的结构可以看出B树要比完全平衡二叉树“矮”，原因在于B树中的一个节点可以存储多个元素，但是mysql索引为什么没用B树，而用的B+树呢？

我们可以发现同样的元素，B+树的表示要比B树要“胖”，原因在于B+树中的非叶子节点会冗余一份在叶子节点中，并且叶子节点之间用指针相连。

• B+树

1）B+树包含2种类型的结点：内部结点（也称索引结点）和叶子结点。根结点本身即可以是内部结点，也可以是叶子结点。根结点的关键字个数最少可以只有1个。

2）B+树与B树最大的不同是内部结点不保存数据，只用于索引，所有数据（或者说记录）都保存在叶子结点中。

3） m阶B+树表示了内部结点最多有m-1个关键字（或者说内部结点最多有m个子树），阶数m同时限制了叶子结点最多存储m-1个记录。

4）内部结点中的key都按照从小到大的顺序排列，对于内部结点中的一个key，左树中的所有key都小于它，右子树中的key都大于等于它。叶子结点中的记录也按照key的大小排列。

5）每个叶子结点都存有相邻叶子结点的指针，叶子结点本身依关键字的大小自小而大顺序链接。

想要了解更加详细的，建议看下小灰漫画算法，通俗易懂：
漫画：什么是B+树？

从上图可以看出，最开始的Hash不支持范围查询，二叉树树高很高，只有B树跟B+有的一比。

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

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

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

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

2.1 B + 树与红黑树的比较

红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B+ Tree 作为索引结构，主要有以下两个原因：

（一）磁盘 IO 次数

B+ 树一个节点可以存储多个元素，相对于红黑树的树高更低，磁盘 IO 次数更少。

（二）磁盘预读特性

为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道。每次会读取页的整数倍。

操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。

2.2 B + 树与 B 树的比较

B+ 树的磁盘 IO 更低

B+ 树的内部节点并没有指向关键字具体信息的指针。因此其内部节点相对 B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

B+ 树的查询效率更加稳定

由于非叶子结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

B+ 树元素遍历效率高

B 树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而 B 树不支持这样的操作（或者说效率太低）。

3.一个B+树的节点中到底存多少个元素最合适?(B+树中一个节点到底多大合适?)

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

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

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

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

4.Mysql的基本存储结构—页的概念？

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

• 各个数据页可以组成一个双向链表
• 而每个数据页中的记录又可以组成一个单向链表
• 每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录
• 以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

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

• 定位到记录所在的页
• 需要遍历双向链表，找到所在的页
• 从所在的页内中查找相应的记录
• 由于不是根据主键查询，只能遍历所在页的单链表了

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

下面我们来讲解下一些常用的索引概念和区别。

5. 聚簇索引、非聚簇索引(二级索引)特点和区别？

• 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
• 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因

澄清一个概念：innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值。

聚簇索引具有唯一性

由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引

一个误区：把主键自动设为聚簇索引

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻键值的页面可能相距甚远。如果你已经设置了主键为聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

此时其他索引只能被定义为非聚簇索引。这个是最大的误区。有的主键还是无意义的自动增量字段，那样的话Clustered index对效率的帮助，完全被浪费了。

刚才说到了，聚簇索引性能最好而且具有唯一性，所以非常珍贵，必须慎重设置。一般要根据这个表最常用的SQL查询方式来进行选择，某个字段作为聚簇索引，或组合聚簇索引，这个要看实际情况。

记住我们的最终目的就是在相同结果集情况下，尽可能减少逻辑IO。

结合图再仔细点看

1. InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
2. 若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（这个通过Name进行条件搜索的过程叫做回表。）

MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

聚簇索引的优势

看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次B+树查找，这不是多此一举吗？聚簇索引的优势在哪？

1. 由于行数据和叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。
2. 辅助索引使用主键作为"指针"而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置（实现中通过16K的Page来定位）会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。
3. 聚簇索引适合用在排序的场合，非聚簇索引不适合
4. 取出一定范围数据的时候，使用用聚簇索引
5. 二级索引需要两次索引查找，而不是一次才能取到数据，因为存储引擎第一次需要通过二级索引找到索引的叶子节点，从而找到数据的主键，然后在聚簇索引中用主键再次查找索引，再找到数据
6. 可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户 ID 来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘 I/O。

聚簇索引的劣势

1. 维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(page split)的时候。建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片
2. 表因为使用UUId（随机ID）作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面更慢，

所以建议使用int的auto_increment作为主键。

主键的值是顺序的，所以 InnoDB 把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB 默认的最大填充因子是页大小的 15/16，留出部分空间用于以后修改），下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满（二级索引页可能是不一样的），如果主键比较大的话，那辅助索引将会变的更大，因为辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点占用占用更多的物理空间

为什么主键通常建议使用自增id

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想 象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

因为MyISAM的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行I/O读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。（强烈的对比）

不过，如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。

mysql中聚簇索引的设定

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻健值的页面可能相距甚远。

6. 覆盖索引

覆盖索引（covering index ，或称为索引覆盖）即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。

比如建立一个学生表：

那么现在出现的业务需求中要求根据名称获取学生的年龄，并且该搜索场景非常频繁，以name和age两个字段建立联合索引，sql命令与建立后的索引树结构如下：

ALTER TABLE student ADD INDEX I_name_age(name, age);

那在我们再次执行如下sql后

SELECT age FROM student WHERE name = '小李'；

流程为：

1. 在name,age联合索引树上找到名称为小李的节点
2. 此时节点索引里包含信息age 直接返回 12

那如何确定数据库成功的使用了覆盖索引呢？

当发起一个索引覆盖查询时，在explain的extra列可以看到using index的信息

这里我们很清楚的看到Extra中Using index表明我们成功使用了覆盖索引。

小结：覆盖索引避免了回表现象的产生，从而减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是性能优化的一种手段。

7. 联合索引

这个和覆盖索引很像，还是一个上面的学生表为例，我们建立了name和age的联合索引，通过name可以直接查询到age，不需要回表操作。

要说联合索引和覆盖索引的区别呢？可以说覆盖索引是联合索引的最优解。

联合索引遵循最左侧匹配原则，比如(name,age)，索引可以是name或者name和age的组合，看，后面这种情况就是覆盖索引，所以说，覆盖索引是联合索引的最优解。

8. 最左侧匹配原则

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，示例：
对列col1、列col2和列col3建一个联合索引

KEY test_col1_col2_col3 on test(col1,col2,col3);

联合索引 test_col1_col2_col3 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。

SELECT * FROM test WHERE col1=“1” AND clo2=“2” AND clo4=“4”

上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引(col1,col2)进行数据匹配。

注意

索引的字段可以是任意顺序的，如：

SELECT * FROM test WHERE col1=“1” AND clo2=“2”
SELECT * FROM test WHERE col2=“2” AND clo1=“1”

这两个查询语句都会用到索引(col1,col2)，mysql创建联合索引的规则是首先会对联合合索引的最左边的，也就是第一个字段col1的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个字段col2进行排序。其实就相当于实现了类似 order by col1 col2这样一种排序规则。

有人会疑惑第二个查询语句不符合最左前缀匹配：首先可以肯定是两个查询语句都包含索引(col1,col2)中的col1、col2两个字段，只是顺序不一样，查询条件一样，最后所查询的结果肯定是一样的。既然结果是一样的，到底以何种顺序的查询方式最好呢？此时我们可以借助mysql查询优化器explain，explain会纠正sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。

为什么要使用联合索引

• 减少开销。建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

• 覆盖索引。对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

• 效率高。索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select * from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w**10% *10% *10%=1w，效率提升可想而知！

  9. 索引下推

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。

在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。

在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

我们来举个栗子：

在开始之前先先准备一张用户表(user)，其中主要几个字段有：id、name、age、address。建立联合索引（name，age）。

假设有一个需求，要求匹配姓名第一个为陈的所有用户，sql语句如下：

SELECT * from user where  name like '陈%'

根据 “最佳左前缀” 的原则，这里使用了联合索引（name，age）进行了查询，性能要比全表扫描肯定要高。

问题来了，如果有其他的条件呢？假设又有一个需求，要求匹配姓名第一个字为陈，年龄为20岁的用户，此时的sql语句如下：

SELECT * from user where  name like '陈%' and age=20

这条sql语句应该如何执行呢？下面对Mysql5.6之前版本和之后版本进行分析。

Mysql5.6之前的版本

5.6之前的版本是没有索引下推这个优化的，因此执行的过程如下图：

会忽略age这个字段，直接通过name进行查询，在(name,age)这课树上查找到了两个结果，id分别为2,1，然后拿着取到的id值一次次的回表查询，因此这个过程需要回表两次。

Mysql5.6及之后版本

5.6版本添加了索引下推这个优化，执行的过程如下图：

InnoDB并没有忽略age这个字段，而是在索引内部就判断了age是否等于20，对于不等于20的记录直接跳过，因此在(name,age)这棵索引树中只匹配到了一个记录，此时拿着这个id去主键索引树中回表查询全部数据，这个过程只需要回表一次。

小结

• 索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。
• 关闭索引下推可以使用如下命令，配置文件的修改不再讲述了，毕竟这么优秀的功能干嘛关闭呢：

set optimizer_switch='index_condition_pushdown=off';

10. 前缀索引

一.定义

前缀索引顾名思义，定义字符串的一部分当做索引，而不是把整个字符串当做索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

二.创建前缀索引即其检索原理

假设一张表有id,name,email2个字段
1.创建email列的普通索引应该是：

alter table T add index idx_email1(email)

2.前缀索引的创建规则为：

alter table table T add index idx_email2(email(6))

当然第一索引包含是的整个字符串，第二个是该字段前6个字节（注意是字节）
对于这2中索引，B+树怎么存储呢？

 INSERT INTO T (email) VALUES ('瞎子','zhangsh1234@163.com'), ('剑圣','lisi1998883@163.com'), ('露娜','zhangssxyz@163.com'), ('李白','zhangsy1998@163.com'), ('韩信','zhaq5481993@163.com'), ('百里玄策','hhaq5481993@163.com');

普通索引存储为：

前缀索引：

是的你没看错，前缀索引那颗树上的存储的是email的前6位字节，也就是你创建前缀索引时指定的前缀字节长度。2种树相比，前缀索引存储了更少的数据，那么他所耗费的空间也就相比较少，这正是他的一个优点。同样的也就相对的增加了扫描行数。
什么增加了扫描行数？？？？ 这是为什么呢？

那么我们来看下下面这个操作：
假设SQL如此这般：

select id,name,email from T where email = 'zhangsh1234@163.com'

那么这2个SQL，应该怎么操作呢?

(1)对于普通索引

1.从索引数上找到满足索引值为zhangsh1234@163.com的该记录，取得 ID1的值。

2.到主键上查到主键为ID1的，判断email值是否正确【为什么判断呢，其实我理解是为了二次判断保证数据一致性吧，比较官方的解释尚未找到】，正确放入结果集。

3.取 idx_email1 索引树上刚刚查到的位置的下一条记录，如此往复。 循环过程中，需要回主键取1次数据，所以系统可以认为只扫描了一行【1次是数第一棵树数出来的】

(2)对于前缀索引

1.从 索引数上找到满足索引值为 'zhangs’的该记录，取得 ID1的值

2.到主键上查到主键值是 ID1 的行，判断出 email 的值是zhangsh1234@xxx.com，这行记录放入结果集【不是要的值，丢弃，进行下一步】

3.取 idx_email2 上刚刚查到的位置的下一条记录,重复以上步骤

在这个过程中，要回主键索引取 3 次数据，也就是扫描了 3 行。通过这个对比，你很容易就可以发现，使用前缀索引后，可能会导致查询语句读数据的次数变多。

注：上面的为什么要回表3次？因为和“zhangs”匹配的值在idx_email2表中有3个，所以要回表3次去检测是否为“zhangsh1234@xxx.com”

但是，对于这个查询语句来说，如果你定义的 idx_email2 不是 email(6) 而是 email(8），也就是说取 email 字段的前 8 个字节来构建索引的话，即满足前缀’zhangsh’的记录只有一个，也能够直接查到 ID1，只扫描一行就结束了。也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

那么问题来了，到底定义多长才算是合理呢？

首先我们先了解一个概念，叫做索引选择性。

索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高。

具体的公式是：

select count(distinct column_name)/count() from table_name

我们选择前缀索引的长度要遵循一个原则，就是这个索引的选择性要接近上面算出的值，值越接近越好。当有多个前缀字节都一样且都等于这个值时怎么选择呢，当然是字节越少越好了哈，字节越少越省空间。索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。

前缀索引的选择性值算法：

// length是你想要创建列的前缀字节长度
select count(distinct column_name(length))/count() from table_name

三.前缀索引对覆盖索引的影响

前面我们说了使用前缀索引可能会增加扫描行数，这会影响到性能。其实，前缀索引的影响不止如此，我们再看一下另外一个场景。
来呀，上SQL：

select id,email from T where email='zhangsh1234@163.com'

如果按照email全字段索引，那么此SQL 是不需要回表的【为什么不需要回表？因为这个相当于覆盖索引了哈】
那么如果按照前缀索引是否需要回表呢？答案是的。
因为当判断前6个字节相等后，需要拿到id 回表拿到email的全部内容进行比较，如果不相同，丢弃这行，否则加入结果集。

那么有人会问了，我把长度放大点，包含所有字节不就好了吗？
那么此时会有如下问题。
1.当你此时的长度是囊括了全字段，但是系统是不知道的，他还是需要回表再次判断的，去确定前缀索引的定义是否截断了完整信息。
2.此时长度是够了，那么能肯定因为业务日后不会增加长度吗？
3.尽可能的加长长度，还不如直接建立全字段索引呢

综上，使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

四.倒叙存储

前面说到的是，可以根据字段前面几个字节进行查询的，那么对于身份证这种，一共 18 位，其中前 6 位是地址码，所以同一个县的人的身份证号前 6 位一般会是相同的。
或许你会说，多弄几个字节不就好吗？那么请问下自己为什么使用前缀索引呢，不就是为了节省空间吗？
那么这么做合适吗？ 不合适对吗？ 乖~，快去反省下吧
那么采用前缀索引显示是不行的，那么如果用前缀索引怎么办呢，聪明的你应该已经猜到了，采用倒叙存储，然后建立前缀索引。
放到SQL 中就应该是这样的：

select field_list from t where id_card = reverse('id_card_string');

当然了，这种逻辑建议放到业务逻辑中实现，而不是放到SQL 中。

五.hash

查询时：

select field_list from t where id_card_crc=crc32('id_card_string') and id_card='id_card_string'

不过有个问题相信你也想到了，不管是hash存储值还是hash索引都是不支持范围查询的。

六. hash字段 VS 倒叙存储

1.从占用空间来看呢，倒叙索引不需要额外开辟存储空间，而hash字段需要额外的一个字段，所以从这点上看倒叙索引更胜一筹，NO!并不准确,如果前缀长度过长，那么这2个情况额外的空间也就相差无几了

2.CPU方面，倒叙索引每次写和读的时候，都需要额外调用一次 reverse 函数，而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。

3.从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

七.总结

1.全字段完整索引比较占空间，但是会走覆盖索引

2.前缀索引，节省空间，但会增加扫描次数 并且不能使用覆盖索引【每次都需回表校验】

3.倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题。【倒叙方法建立放到业务逻辑中】

4.hash字段索引，相比前缀索引性能较为稳定，但是有额外的存储空间和计算消耗，同时也不支持范围查询。

11. 普通索引和唯一索引的区别？

一、概念区分

• 普通索引和唯一索引
  普通索引可以重复，唯一索引和主键一样不能重复。

唯一索引可以作为数据的一个合法验证手段，例如学生表的身份证号码字段，我们人为规定该字段不得重复，那么就使用唯一索引。（一般设置学号字段为主键）

• 主键和唯一索引
  主键保证数据库里面的每一行都是唯一的，比如身份证，学号等，在表中要求唯一，不重复。唯一索引的作用跟主键的作用一样。

不同的是，在一张表里面只能有一个主键，主键不能为空，唯一索引可以有多个，唯一索引可以有一条记录为空，即保证跟别人不一样就行。
比如学生表，在学校里面一般用学号做主键，身份证则弄成唯一索引；而到了教育局，他们就把身份证号弄成主键，学号换成了唯一索引。
选谁做表的主键，要看实际应用，主键不能为空。

二、栗子

一个市民系统，每个人都有个唯一身份证号；
业务代码已保证不会写入两个重复的身份证号；
如果市民系统需要按照身份证号查姓名，就会执行类似SQL：

select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';

相信你一定会在id_card字段上建索引。

由于身份证号字段比较大，不建推荐把身份证号做主键。
因此现在有两个选择

1. 给id_card字段创建唯一索引
2. 创建一个普通索引

如果业务代码已保证不会写入重复的身份证号，那这两个选择逻辑上都正确。

但从性能角度考虑，唯一索引还是普通索引呢？
假设字段 k 上的值都不重复。

• InnoDB的索引组织结构

接下来从这两种索引对查询语句和更新语句的性能影响来进行分析。

1.查询过程

查询语句

select id from T where k=5

该语句在索引树查找的过程：
先通过B+树从树根开始，按层搜索到叶节点，即图中右下角的数据页，然后可认为数据页内部是通过二分法定位记录。

• 对普通索引，查找到满足条件的第一个记录(5,500)后，需查找下个记录，直到碰到第一个不满足k=5条件的记录
• 对唯一索引，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止检索。

该不同点带来的性能差距会有多少呢？
微乎其微！

InnoDB数据是按数据页为单位读写。即当需读一条记录时，并非将该记录本身从磁盘读出，而是以页为单位，将其整体读入内存。

InnoDB中，每个数据页的大小默认是16KB。

因引擎按页读写，所以，当找到k=5记录时，它所在数据页就都在内存了。
对普通索引，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。
如果k=5记录刚好是该数据页的最后一个记录，那么要取下个记录，必须读取下个数据页，操作会稍微复杂。
对于整型字段，一个数据页可存近千个key，因此这种情况概率很低。所以，计算平均性能差异时，仍可认为该操作成本对现在的CPU可忽略不计。

2.更新过程

2.1 change buffer

需更新一个数据页时

• 若数据页在内存，直接更新
• 若该数据页不在内存，在不影响数据一致性前提下，InooDB会将这些更新操作缓存在change buffer，无需从磁盘读入该数据页。
  在下次查询需要访问该数据页时，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过该方式就能保证这个数据逻辑的正确性。

虽然叫change buffer，实际上是可持久化的数据。
即change buffer在内存中有拷贝，也会被写进磁盘。

2.2 merge

将change buffer中的操作应用到原数据页，得到最新结果的过程。

那何时触发呢？

• 访问该数据页会触发merge
• 系统有后台线程会定期merge
• 在数据库正常关闭（shutdown）的过程中，也会执行merge

大概的过程如下：

若能将更新操作先记录在change buffer，减少读盘，语句执行速度会明显提升。
且数据读入内存需要占用buffer pool，所以该方式还能避免占用内存，提高内存利用率。

2.3 何时用change buffer ？

对于唯一索引，所有更新操作要先判断该操作是否违反唯一性约束。

比如，要插入(4,400)记录，要先判断表中是否已存k=4记录，而这必须要将数据页读入内存才能判断。
如果都已经读入到内存，那直接更新内存会更快，就没必要使用change buffer。
因此，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可使用。

change buffer用的是buffer pool里的内存，因此不能无限增大。
change buffer的大小，可通过参数innodb_change_buffer_max_size动态设置。
参数设置为50时，表示change buffer的大小最多只能占用buffer pool的50%。

理解了change buffer机制，看看要在这张表中插入一个新记录(4,400)，InnoDB处理流程。

分情况讨论该记录要更新的目标页是否在内存中：

在内存

• 唯一索引
  找到3和5之间位置，判断到没有冲突，插入值，语句执行结束。
• 普通索引
  找到3和5之间位置，插入值，语句执行结束。

普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小CPU时间。

不在内存

• 唯一索引
  需要将数据页读入内存，判断到没有冲突，插入值，语句执行结束
• 普通索引
  将更新记录在change buffer，语句执行结束

将数据从磁盘读入内存涉及随机IO访问，是数据库里面成本最高操作之一。
change buffer因减少随机磁盘访问，所以对更新性能提升明显。

2.4 change buffer的使用场景

普通索引的所有场景，使用change buffer都可加速吗？

因为merge才是真正进行数据更新时刻；
change buffer主要目的是将记录的变更动作缓存下来；
所以在一个数据页做merge前，change buffer记录变更越多（即该数据页上要更新的次数越多），收益越大。

对写多读少业务，页面在写完后马上被访问到的概率较小，change buffer使用效果最好。该类业务模型常见为账单、日志类的系统。

反之，假设一业务的更新模式是写后马上查询，那么即使满足条件，将更新先记录在change buffer，但之后由于马上要访问该数据页，立即触发merge。
这样随机访问IO的次数不会减少，反而增加change buffer维护代价。
所以，对于这种业务模式，change buffer起副作用。

三、总结

本篇文章对mysql数据库所用的索引数据结构，以及与其相关其他的数据结构进行了讲解。而且还对常用各种索引概念、作用做了详细的分析，相信大家认真阅读后，会对这一些列索引有更加深刻的理解。

最后引用我很佩服的一个人经常说的话：你知道的越多，你不知道的越多！

文章引用：

https://mp.weixin.qq.com/s/_9rDde9wRYoZeh07EASNQQ

https://mp.weixin.qq.com/s/J3kCOJwyv2nzvI0_X0tlnA

https://mp.weixin.qq.com/s/e0CqJG2-PCDgKLjQfh02tw

https://www.cnblogs.com/nullzx/p/8729425.html

https://cloud.tencent.com/developer/article/1541265

https://segmentfault.com/a/1190000015416513

https://juejin.cn/post/6844903967365791752

https://www.jianshu.com/p/084dcea422fa

https://developer.aliyun.com/article/768845