详解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:执行情况的描述和说明