详解MySQL索引

详解MySQL索引

本文详细描述MySQL运行原理和底层数据结构

输入MySQL命令发生了什么？

图片清晰的演示了查询的过程

InnDB索引的主要的数据结构是什么？

索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构

可以类比字典的拼音表，笔画表，如果没有这些内容，我们查询一个字就需要full scan全文扫描，效率低下

所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引

对于数据库海量的数据来说，最差劲的查找方式肯定是遍历查找，O(n)的算法使用起来是糟糕的，在算法发展过程中，出现了二分法，将效率提升到了O(log2n),等等衍生出很多的数据结构来提高效率，那MySQL使用的是什么呢？

二叉树？

• 如果MySQL使用二叉树作为索引，虽然二叉树查找效率很高，但从MySQL的角度来讲，我们不得不考虑一个问题就是：磁盘IO，对于这种关系型数据库，当数据量巨大的时候，索引也会变得很大，索引的存储也是放在磁盘上的，当我们查询索引时，不可能将整个索引装入内存中，只能逐一加载每个磁盘页，磁盘页对应索引树的结点。所以MySQL索引的效率就取决于要进行几次磁盘IO才能到达指定的结点。而树的查询是一层一层的（即磁盘IO数与树的高度挂钩），二叉树只能分两个叉，那就需要一直向树的深处查找，会进行很多的磁盘IO
• 如果数据是顺序插入，二叉树就会倒向一侧，这时查找就变成了full scan

所以二叉树并不是理想的数据结构，需要解决的问题就变成了减少磁盘IO->减少树的高度，让树变得矮胖同时保证数据的有序，自然就想到了B树

B树?

相对于二叉树，B树极大的减少了树的高度，并且保证了有序性，虽然查询元素时比较次数来看并不比二叉树少，但是对与磁盘IO来说就少很多，因为大部分查找消耗时间花费在一个结点内的查找,而磁盘IO只需要发生一次，然后在内存中对结点内数据的查找会非常快，所以B树的优势就是减少磁盘IO，将更多的操作放在内存中操作，但MySQL并不是用B树，而是用的B+树，这又是为什么?

B+树！

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Nc7GP0SE-1649303684136)(https://cdn.jsdelivr.net/gh/Gs1mpleL/pictures/b+Tree.png)]

由图可以看出B+树相比B树，在结点分裂时并不会将数据挪出成新节点，而是复制新的一份数据当作父节点，这样做就让所以数据都存储在了叶子节点上，非叶子节点只起到搜索的作用。并且在叶子节点处形成了单向链表。（注意此处展示叶子节点位单向链表，事实上MySQL对B+树做了优化，叶子节点之间为双向链表）

那这种结构有什么好处？

• 磁盘IO：B+树非也叶子结点只存储索引值，不存储数据，数据全部在叶子节点，这样同一个磁盘页中就可以存储更多的的非叶子节点，而B-树是每个索引节点都会有Data域。这就意味着，数据量相同的情况下，B+树的结构比B-树更加“矮胖”，因此查询是IO次数也更少，这就决定了B+树更适合用来存储外部数据，也就是所谓的磁盘数据。
• 稳定性：B树查询结果可能在树的任意位置，查询速度要么快要么慢，而B+树相对来说就很稳定，因为数据都存储在叶子结点中
• 范围查询：B树的范围查询需要，比如查询范围为3~11的元素（上图），B-树只能依靠繁琐的中序遍历，首先自顶向下查找范围的下限，然后中序遍历找到上限，而B+树形成链表，查询到下限后之间向后遍历即可

总体来说

选择数据结构时，最重要的一点是要减少磁盘IO，应为索引的结点是存储在磁盘上的！B+树高度低，磁盘IO次数少

索引的分类（从数据结构角度）

FULLTEXT

这是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值，全文索引更类似于搜索引擎做的事情，实际生产中我们一般不会使用MySQL来做类似搜索引擎的工作。

HASH

HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

BTREE

BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。默认是B+树

RTREE

RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

相对于BTREE，RTREE的优势在于范围查找。

索引的分类 （从存储方式角度)

聚簇索引

聚簇索引也叫聚集索引，它实际上并不是一种单独的索引类型，而是一种数据存储方式，聚簇索引的叶子节点保存了一行记录的所有列信息。也就是说，聚簇索引的叶子节点中，包含了一个完整的记录行。由图可见叶子节点包含完整的一行记录

InnoDB中，每个表必须有一个聚簇索引，默认是根据主键建立的。如果表中没有主键，InnoDB会选择一个合适的列作为聚簇索引，如果找不到合适的列，会使用一列隐藏的列**DB_ROW_ID作为聚簇索引**。这个字段长度为6个字节，类型为长整形。

非聚簇索引

它的叶子节点中，不包含整个记录的完整信息，除了age字段本身以外，只包含当前记录的主键id。如果想要获取整行记录数据还需要再通过id号到聚簇索引中回表查询。

非聚簇索引也叫辅助索引、普通索引，它的叶子节点只包含一个主键值，通过非聚簇索引查找记录要先找到主键，然后通过主键再到聚簇索引中找到对应的记录行，这个过程被称为回表

索引的分类（从类型的角度）

主键索引

设定为主键后数据库会自动建立索引，innodb为聚簇索引

单值索引

即一个索引只包含单个列，一个表可以有多个单列索引，根据一列的数据建立的一个普通索引

唯一索引

索引列的值必须唯一，但允许有空值

复合索引

即一个索引包含多个列

在数据库操作期间，复合索引比单值索引所需要的开销更小(对于相同的多个列建索引)

当表的行数远大于索引列的数目时可以使用复合索引

索引常见面试题

为什么我们推荐使用自增id而不推荐使用uuid或者身份证号等呢?

使用自增ID：

自增的主键的值是顺序的，所以Innodb把每一条记录都存储在一条记录的后面。当达到页面的最大填充因子时候(innodb默认的最大填充因子是页大小的15/16，会留出1/16的空间留作以后的修改)下一条记录就会写入新的页中，一旦数据按照这种顺序的方式加载，主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费新插入的行一定会在原有的最大数据行下一行，mysql定位和寻址很快，不会为计算新行的位置而做出额外的消耗,减少了页分裂和碎片的产生

使用UUID：

因为uuid相对顺序的自增id来说是毫无规律可言的，新行的值不一定要比之前的主键的值要大，所以innodb无法做到总是把新行插入到索引的最后，而是需要为新行寻找新的合适的位置从而来分配新的空间。因为写入是乱序的，innodb不得不频繁的做页分裂操作，以便为新的行分配空间，页分裂导致移动大量的数据，一次插入最少需要修改三个页以上，写入的目标页很可能已经刷新到磁盘上并且从缓存上移除，或者还没有被加载到缓存中，innodb在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机IO，在把随机值（uuid和雪花id）载入到聚簇索引(innodb默认的索引类型)以后，有时候会需要做一次OPTIMEIZE TABLE来重建表并优化页的填充，这将又需要一定的时间消耗。

MySIAM和InnoDB索引的区别

• InnoDB数据和索引在一起（索引即数据），MySIAM是分开存储的
• InnoDB索引有主次之分，区分聚簇和非聚簇索引，MySIAM不区分
• InnoDB叶子节点存放数据，MySIAM索引叶子节点存放数据地址

B+树和HASH索引的区别

HASH索引适合等值查询，无法进行范围查询，无法根据索引进行排序，大量重复键值对情况下，存在hash碰撞的问题

索引有没有缺点

索引虽然提高了查询速度，但是随着数据的增加会增加维护和新建索引的耗时，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要更新保存索引文件
建立索引会占用磁盘物理空间。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件会变得非常大
如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果
对于非常小的表，大部分情况下简单的全表扫描更高效

如何查看SQL语句执行情况？

mysql> explain select * from student;
±—±------------±--------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±--------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL |
±—±------------±--------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+

id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明