Mysql索引(究极无敌细节版)

标签：究极 name 记录 无敌 索引 Mysql 排序 数据 主键

参考了：
https://www.jianshu.com/p/ace3cd6526c4
推荐up主https://space.bilibili.com/377905911
推荐书籍《mysql是怎样运行的》
推荐极客时间《MySQL实战45讲》——林晓斌

系列文章目录和关于我

一丶什么是索引

索引是存储引擎快速找到记录的一种数据结构。数据库中的数据可以理解成字典中的单词，而索引就是目录，显而易见这是一种空间换时间的做法，目录占用了空间，但是加快了我们找到单词的速度，正如索引需要空间存储，但是利用索引我们可以快速的找到想要的数据。

InnoDB存储引擎存在几种常见的索引：

• B+树索引
• 全文索引
• 哈希索引

本文主要讨论B+树索引

二丶索引的数据结构

可以加快查找速度的数据结构很多，为什么mysql使用B+树来实现昵，换句话说哈希表，有序数组，跳表，平衡二叉搜索树，B-树等都可以优化搜索效率，为什么偏偏使用B+树

1.哈希表

哈希表，可以联想Java中的HashMap，在HashMap源码学习中，我们了解到Hash表的数据结构。如下图

哈希表通过hash算法将key映射到数组对应的下标进行存储，不可避免的会产生hash冲突（多个不同的key散列到相同的数组下标中），解决hash冲突常用拉链法，顾名思义，就是把相同hash值的节点组成链表串起来。在根据key查找value的过程中，只需要再次使用相同的hash算法那么就能拿到对应的数组下表，然后遍历链表找到目标值即可,查找的效率是o(1)。

明明存在链表需要遍历为什么说时间复杂度o(1)
首先hash算法计算是常数时间，
hash表会在需要的时候进行扩容，
维持链表长度尽量在一个常数范围，从而保证遍历常数个链表节点

mysql中存在自适应哈希索引，由innodb存储引擎自己控制，利用查找O(1)的性质优化等值查询。我们可以看出，hash表并不适合范围查询，对于Id<10这种范围查询只能遍历hash表中每一个数据，相当于要进行一次全表扫描。我还有一个想法：从扩容的角度看，每次扩大数组大小后都需要移动元素到新的数组空间中，这部分的复制移动的开销也许也是hash表不合适的原因（redis为了解决这个问题，使用渐进式hash的方式，在扩容的时候生成更大的数组，但不是一次移动所以数据，而是插入的新元素都放到新数组，老数组使用到的数据才会慢慢移动到新数组）redis基于内存的数据库都需要通过渐进式hash优化扩容操作，基于磁盘的mysql若使用hash将惨不忍睹

2.有序数组

有序数组就是数据中元素有序，正因为有序，所以其在范围查找上非常优秀，正因为要维持有序，在更改数据的时候，也许需要移动大量数组元素（比如插入一个较小的值，大于此值的数据都需要后移动），所以有序数据只适用于静态数据（比如2020年人口信息，这种不会改变的数据）

3.跳表

为了解决有序数组需要移动元素的问题，我们可以使用链表来维护元素，从而使更改元素效率为o(1)，但是链表的查找非常慢。由于链表整体是有序的，那么我们可以使用二分查找优化查找效率，如上我们建立多级的节点，在查找的时候我们首先通过多级的索引依次找到最下层。对于范围查找，由于底层数据是有序的，查找id<7的数组，首先我们找到id=7然后向左遍历集合（可以把跳表最下面一层优化为双向链表，从而让范围查找速度也很快）

哪为什么不使用跳表来做索引昵？

跳表是链表结构，一条数据一个结点，如果最底层要存放2kw数据，且每次查询都要能达到二分查找的效果，2kw大概在2的24次方左右，所以，跳表大概高度在24层左右。最坏情况下，这24层数据会分散在不同的数据页里，也即是查一次数据会经历24次磁盘IO。

4.平衡二叉搜索树

二叉搜索树，即左子树小于根，根小于右子树，这种结构在查找的时候可以进行二分，根据根节点的值就可以确定期望的数据在左树还是右树。

但是二叉搜索树在插入，删除节点的时候可能出现树极度不平衡的情况，出现树退化成链表。

这个时候就需要维持树的平衡——AVL:在满足二叉搜索树的条件下，要求任何节点的两个子树高度差不超过1。平衡二叉树的查找是趋近于O(log(N))，但是需要维护一颗树为AVL需要进行左旋，右旋的操作，更新的时间复杂度也是 O(log(N))。

为什么不使用AVL做索引：节点存储的数据内容太少。因为操作系统和磁盘之间一次数据交换是以页为单位的，一页 = 4K，即每次IO操作系统会将4K数据加载进内存。但是，在二叉树每个节点的结构只保存一个关键字，一个数据区，两个子节点的引用，并不能够填满4K的内容。幸幸苦苦做了一次的IO操作，却只加载了一个关键字，在树的高度很高，恰好又搜索的关键字位于叶子节点或者支节点的时候，取一个关键字要做很多次的IO。

5.B-树，B+树

B-树就是B树，英文是B-Tree，所以国内有许多人称之为B-树。B树和B+树是多路平衡查找树，之所以多路，是为了契合磁盘的io操作——操作系统和磁盘之间一次数据交换是以页为单位的，多路能让读取一页能获取更多的数据，让树的高度更低。

上面两图，我们可以看出B树和B+树的区别

1. B+树叶子节点使用双向指针串联起来，这让B+树相比于B树更加适合范围查找
2. B+树非叶子节点并不存数据，所以每次查找数据都必须遍历到叶子节点，时间复杂度稳定为O(logN)，B-树在运气好的时候可以在根节点直接拿到数据。但是正是因为非叶子节点不存储数据，可以让一次磁盘读取一页中包含的索引数据更多，每个节点能索引的范围更大更精确，让我们可以更改定位到期望的数据。由于B+树的叶子节点的数据都是使用链表连接起来的，而且他们在磁盘里是顺序存储的，所以当读到某个值的时候，磁盘预读原理就会提前把这些数据都读进内存，使得范围查询和排序都很快

B+树在更改数据的时候，为了保证树的平衡可能存在节点的分裂和合并，所以我们一般建议使用自增主键，在插入的时候，不会频繁的发生节点的分裂。

三丶InnoDB索引方案

1.InnoDB行结构

InnoDB存储引擎存储一行数据使用的数据结构称为行结构。

• COMPACT

  

  • 变长字段列表：如varchar(m),Text，Blob类型的列，称为变长字段，由于其字节数量不固定，需要在变成字段列表中存储这些字段的长度，在记录的真实数据中存储内容
  • null值列表：如果表中没用允许为null的列，那么null值列表就不存在，否则把每一个允许为null的列使用一个二进制位来表示，二进制为1的时候表示值为null
  • 记录头信息：占用五个字节，其中包含delete_flag(标记记录是否被删除)，next_record(下一条记录的相对位置)等信息
  • 记录的真实信息：如果表没用定义主键，也没有唯一不可重复不可为null的列，那么innodb为我们生成一个隐藏列row_id,如果定义了主键那么此列不存在，并且还有trx_id,roll_pointer两个隐藏列，后续便是每一个列的真实数据。（char(M)类型的列，如果使用定长字符编码，那么字节数不会加到变长字段列表中，如果使用变长编码，占用长度会加入到变成字段列表中（变长编码那么必须占用M个字节，varchar(M）则没用这个要求）

• REDUNDANT

  

  • 字段长度偏移列表：此种行格式会把记录所有列长度的偏移信息存储
  • null值的处理方式：先看偏移量的null比特位是否为1，如果为了那么表示为null

• 溢出列

  如果一个列太长，并不会傻乎乎的存储所有数据在行记录中，而是使用溢出列，COMPACT和REDUNDANT只会存储该列的前768字节然后存储指向其他页的地址，剩下的数据存在其他页中。

• DYNAMIC和COMPRESSED

  和COMPACT类似，但是二者不会存储过长列的前768字节，而是把真实数据都存储到溢出中，记录只存储溢出页的地址。COMPRESSED 还会使用压缩算法对页面进行压缩

2.InnoDB页结构

页是InnoDB管理存储空间的基本单位，其默认大小为16k,InnoDB设计了很多不同的页结构：存放Change Buffer的页，存储undo log 日志的页等等。对于表中数据，也存在在页中

最开始的时候UserRecords并不存在，随着数据的插入，会从FreeSpace中申请一个记录大小的空间，将其划分到UserRecords部分，当FreeSpace用完只会继续插入就需要申请新的页。

2.1行结构中记录头信息的作用

• deleted_flag:标记是否被删除，1表示被删除，被删除的列表通过next_record串联起来，并且会记录被删除记录的空火箭，这部分空间可以重复使用

• min_rec_flag: B+树每层非叶子节点，最小的目录项记录会被添加此标记

• n_owned:

• heap_no:UserRecords中存储的用户记录是紧凑如同堆一样排布的,heap_no是堆中记录的编号，从2开始（0和1 被infimum+supremum占用，infimum虚拟的最小记录，supremum虚拟最大记录）

• next_record:表示当前记录的真实数据，到下一条记录的距离

  

  next_record左边是变长字段列表和null值列表（二者都是逆序存放信息，也就是说距离next_record最近的是第一个字段是否为null，第一个变长字段的长度）右边是记录的真实数据（顺序存放），且可以使记录中靠前字段和对应的字段信息在内存中更近，提高高速缓存的命中率。这里我们可以看到被删除的记录没用立即被清除，只是不会被next_record串联起来

  记录按照主键从小到大形成单向链表

2.2页目录

上面我们知道了记录在页中按照主键从小到大的顺序串联成单向链表，那么怎么在一个页中根据主键找到目标记录昵——通过页目录进行二分查找。

1. 页目录生成过程

   1. 将infimum，supermum以及所有未被删除的记录，分成多个组
   2. 每一个组中最大的记录的n_owned存储组中记录条数
   3. 将每一个组中最后的记录在页面中的地址偏移量，存储到页面尾部——Page Directory中，这些地址偏移量称为槽

   

2. 根据查询页面记录的过程

   通过二分法找到目标记录中的槽，然后遍历槽所在组的所有记录

3.InnoDB索引方案

3.1为页建立目录项

InnoDB使用也作为管理和存储空间的基本单位，最多只能保证16k的连续存储。

目录项记录的只是主键值和页号两个列，最下方是我们刚刚讲到的innoDB存储用户记录的页。如果页面数据量很大，可以继续为目录项建立目录项

3.2 根据目录项定位数据行的过程

例如查找主键为10记录

1. 根据目录项中的内容，确定目标记录所在的页

   如上图页33 存在记录(1,30)，(320 32)，可以判断主键位于1~320范围的记录在页30，大于320的记录在页32

2. 找到页30后还要继续在页30中，通过目录项记录的页确定目标记录真正所在的页

3. 在真正存储用户记录的页（页28）中通过槽定位到组，然后遍历槽所在组的所有记录

三丶聚集索引和非聚集索引

InnoDB存储引擎是索引组织表——表中的数据按照主键顺序存放。非聚集索引也称做辅助索引，无论是聚集还是非聚集，其原理都是一颗B+树，叶子节点都存储数据，不同的是聚集索引叶子节点存储的是一整行的数据，非聚集索引叶子节点存储的是聚集索引值（主键值）。

如果数据表定义了主键，那么这个主键索引就是聚集索引，如果没有定义主键，mysql会选择该表的第一个非空唯一的索引构建聚集索引，如果都没有那么mysql会生成一个隐藏的列（6字节的列，并且插入自增）

自增主键会把数据自动向后插入，避免了插入过程中聚集索引节点分裂的问题。节点分裂会带来大范围的数据物理移动，带来磁盘IO的性能损耗，并且我们一般建议尽量不要改动主键，主键的更改也会带来page分裂，产生碎片。

四丶回表查询

如上图，假如我们有一张表存在三个字段id,age,name我们在id上建立了主键索引，这时候id主键索引也是聚集索引，在age上建立了普通索引，这时候age索引就是非聚集索引。如果我们执行select * from table where age=1这时候先走age索引（如果数据量较大，数据量少直接全表扫描了）那么会找到对应的主键id，继续到主键id索引中找到目标数据，这个操作叫做回表。

这就是为什么根据主键查找快于根据其他索引列查找，因为如果其他索引列没有包含我们select语句中需要的列（如果是select id from table where age<10,那么age索引是可以覆盖到需要的数据的(叶子节点存储了id)，那么也不会回表），那么会走主键索引拿到需要的数据，多了一步回表操作。

这里我们也可以看到为什么建议使用select * ,这意味着查找所有列，如果配合上普通索引，那么大概率这个普通索引不会覆盖到索引列，导致需要回表查询。并且select*这种"我全都要"大概率会查询到我们不需要的列，造成不必要的网络资源消耗，增加不必要的io，增加不必要的内存消耗。

五丶联合索引

联合索引是指对表上的多个列建立索引，如上图表存在四个字段id,address,name,age，我们在name和age上建立索引，上图我们粗略的展示了联合索引的B+树结构。我们可以观察到在叶子节点中name是有序的，但是age无序，联合索引是按照索引定义的顺序排序的，这就导致select xxx from table where name='b'是可以根据上面定义的联合索引查找数据的，但是``select xxx from table where age=12是无法走上面定义的联合索引的。这就是常说的最左前缀匹配原则`的原理。

• 联合索引可以减少回表

  如果我们执行select age,id from table where name='a' and age=10,这个时候由于我们定义的聚集索引一级包含了需要的数据就不需要进行回表操作了（这其实也被称为覆盖索引，即非聚集索引中可以查询到全部需要的列，那么就不需要走聚集索引回表查询数据）

• 联合索引可以优化排序

  上图中的联合索引，我们可以看到，名称相同的节点，其年龄是有序的

  也就是说select * from table where name='a' order by age这个语句将避免多一次的排序操作（select* from table where id=1 order by age会走主键索引拿到所有符合数据进行排序，这里说的避免一次排序操作指拿到的数据本身就是有序的 所有不需要再次排序）

• 索引下推ICP

  全称Index Condition PushDown,mysql 5.6后支持的一种根据索引进行查询优化的操作。mysql数据库会在取出所有数据的同时判断是否进行where条件的过滤，将where的部分过滤放在存储引擎层。

  

  mysql5.6之前如果执行select * from table where name like '张%' and age=10这时候会先从name age的联合索引中拿到name满足张开头的数据，然后回表，mysql支持ICP后，效果图如下

  

  mysql会根据联合索引中记录的age对数据进行过滤，这时候age不等于10的数据将不会回表，将回表次数从4优化到了2，这就是索引下推。

• 如何安排联合索引的顺序

  第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的，比如业务中存在两个高频查询，根据name，以及根据name查询后根据age排序，这个时候我们应该建立name age的联合索引，上面我们说过name,age的所有其中name是有序的，age只在name相同的情况下才是有序的，这样可以减少建立name的普通索引，并且优化排序，甚至利用索引下推减少回表。如果还存在根据age进行的查询，那么需要单独维护一个age的普通索引

六丶索引与排序和分组

1.索引用于排序

假设我们有一张表存储id，姓名，年龄以及城市，我们在城市字段上建立索引

执行select city,name,age from t where city='杭州' order by name limit 1000 ;city上具备索引，那么可以通过city字段拿到符合要求的数据

拿到城市和主键的信息之后，还需要回表，来到主键索引上查询到需要的列，接下来需要排序

• 如果sort_buffer(MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer)可以容纳下目标记录，那么mysql会使用sort_buffer进行快速排序，这个过程叫做全字段排序(全部的字段都在sort_buffer中)

  

• 如果sort_buffer无法容纳下这么多记录，将使用外部文件排序，mysql把需要排序的数据分为多个文件，分别快排然后合并

• 如果mysql认为行太长，那么会使用row_id排序——从city索引找到一条数据，回表拿到索引需要排序的字段以及主键id，在sort_buffer中只存储需要排序的字段和主键，然后排序后，再次回表查询全部需要的列，组成结构集返回

  

• 如果直到的limit 比较小，比如limit 3，也许mysql会维护一个大小为3的堆，进行排序获得前3条

上面讲了mysql是如何排序的，可以看到上述的排序方式，都需要利用内存或者利用磁盘文件进行排序，总体来说是浪费空间以及效率不高的，那么如何可以让order by更快昵——创建一个 city 和 name 的联合索引

有了这个联合索引，mysql可以找到城市为杭州的数据，然后回标查询需要的字段，然后向右取下一条，并不需要排序，因为city=杭州的数据name自然是有序的。这就是索引对排序的优化

• 联合索引排序顺序需要符合最左前缀原则

• 联合索引排序，不能将ACS和DESC混合使用（mysql8降序索引似乎可以解决这个问题）

• 如果形成扫描区间的列 和排序的列不是同一个索引，可能也不能使用到索引优化排序

  select * from key1 = a oder by key2 key1，key2不是联合索引，各自包含一个索引，那么mysql选择key1索引数据。

• 排序列如何使用了函数，那么不能排序，函数也许会改变索引的单调性

2.索引用于分组

select key1,key2,key3 ,count(*) from table group by key1,key2,key3 如果key1，key2，key3没有建立联合索引，那么需要建立用于统计的临时表，将扫描的数据加入到临时表进行统计，但是如果我们按照 key1,key2,key3的顺序建立了联合索引，那么索引中的主键自然就是分好组的。索引用于分组的注意事项基本上和排序相同，这里不做过多赘述

七丶索引建立和使用原则

1.为搜索，排序，分组的列建立索引

一般只为出现在where后面的列，连接子句中的列，出现在order by,或者group by的列进创建索引。不要无脑建立索引，索引是需要存储在磁盘上的，占用空间，并且在新增，删除，修改的时候还需要维护索引，是需要时间的。

比如select xxx from table where name= 'a' order by user_no,这条查询语句可以选择在name上建立索引，也可以选择在user_no 上建立索引，后者可以优化排序。

2.考虑列中不重复的个数建立索引

select xxxx from table where sex=1 这里不要为sex性别建立索引，性别通常只有男和女，为其建立索引，b+树只有两个节点，查找之后还要对一半的进行回表，不如直接走全表扫描

3.索引列尽可能小

mysql基本数据类型十分丰富，整数类型有tinyint,mediumint,int，bigint，我们应该尽量使用占用字节数小的数据类型，这样可以让每次读取磁盘获取一页的数据，可以获得更多的范围信息

4.为列前缀进行索引

比如说有英文名可能很长，每次都是根据FirstName 进行like查找，这时候可以选择为列的前10个字符建立索引(alter table user add index idx_name(name(10)))。但是十个字符之后将无法使用索引。且前缀索引会无法使用到覆盖索引减少回表的功能，比如select name id,where name=abc123，加入为name前三个字符建立了索引，会在前缀索引中找到符合的数据比如abc111,abc121等等这个时候name的前缀索引还是需要获取主键回表然后判断name是否符合要求。

5.合理的建立覆盖索引

在联合索引小节中，我们总结了联合索引的好处，减少回表，优化排序和分组，索引下推。

6.不要在uuid上建立索引

首先uuid占用字节大，导致每一页范围信息少，并且uuid无序，这会导致插入数据的时候节点的分裂。这里也说明了自增主键优秀的点，不会频繁的节点分裂，并且不要修改主键，避免不必要的节点分裂。相比于uuid作为主键，不如使用分布式自增主键生成的方案

7.存在联合索引的情况下，不要重复建立索引

存在name,age的联合索引，那么不需要再为name单独建立索引了，但是可以为age建立索引，原理在联合索引中进行了讲解。

8.尽量使用自增主键

自增主键能减少聚簇索引的页分裂，如果插入的主键一会儿天一会儿底，会造成页面的分裂，同样更新主键也会导致移动复制

9.普通索引和唯一索引如何做出抉择

如果业务逻辑可以保证索引列的唯一，不需要依赖唯一索引保证唯一性的话，尽量使用普通索引。

9.1普通索引唯一索引等值查询的性能差异微乎其微，唯一索引略胜一筹

对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB 中，每个数据页的大小默认是 16KB，也就是说只有唯一索引满足等值条件的数据跨页的时候，才需要再一次io，这个概率是比较小的

9.2插入和更新的效率，普通索引由于唯一索引

对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行束；对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内
存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change
buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的
时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方
式就能保证这个数据逻辑的正确性.
change buffer 在内存中有拷贝，也会被写入到磁盘上。
将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问
这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关（shutdown）的过程中，也会执行 merge 操作

八丶索引失效

上图是mysql的基本架构，其中存在优化器，其作用是不改变sql执行j结果的情况下，让sql更加简单，并且根据成本分析，制定执行计划。是否走索引，走什么索引也是优化器来决定的（sql中可以提示使用什么索引，强制使用某一个索引）。

常见索引失效的原因有

1.不满足最左前缀原则

如果存在a,b,c的联合索引，select * from table where b=2 and a=1这种时候还是可能走联合索引的，mysql会优化语句,但是select * from table where b=1 and c=2是无法走联合索引的，因为b，c在b+树中整体无序

2.使用了select*

使用select*需要回表，也许mysql优化器评估后觉得走非聚集索引，不如直接全表扫描

3.like查询左边有%

以xxx开头是可以走索引的，因为是有序的，但是以xxx结尾和包含xxx是无法走索引的。因为字符串的比较是从最左的字符开始比较的

4.order by 使用了联合索引中不存在的列，或者顺序不符合最左前缀匹配

5.group by 使用了联合索引中不存在的列，或者顺序不符合最左前缀匹配

6.不要在条件字段函数操作，注意隐式类型转换，小心隐式字符编码转换

例如在A表的key1上建立索引，key1是int类型

6.1条件字段函数操作

select xxx from A where key1+1<10 理论上说mysql可以进行优化，但是最好不要这么做，更不要select xxx from key1 where abs(key1)<10，mysql任何索引列上进行这些操作是会影响单调性的，直接无脑不走索引，分组排序也一样 ，select * from B left join A on B.key2 = A.key1+1 这个语句也一样（连表查询的原理见Mysql单表访问方法，索引合并，多表连接原理，基于规则的优化，子查询优化）

6.2 注意隐式类型转换

select * from A where key1>'10' 这个语句中key1是int类型，通样无法走索引，因为是将key1转化为字符串比较，还是将'10' 转化为数字比较昵，如果是前者那么key=9符合要求，如果是后者key1=9不符合要求。

6.3 小心隐式字符编码转换

如果两个表的字符集不同，那么做表连接查询的时候用不上关联字段的索引，比如字符集 utf8mb4 是 utf8 的超集，所以当这两个类型的字符串在做比较的时候，MySQL 内部的操作是，先把 utf8 字符串转成 utf8mb4 字符集，再做比较，相当于其中一列需要使用convert函数导致索引失效

标签：究极,name,记录,无敌,索引,Mysql,排序,数据,主键
From： https://www.cnblogs.com/cuzzz/p/16812054.html