MySQL索引原理

最新推荐文章于 2023-07-21 01:33:48 发布

原创最新推荐文章于 2023-07-21 01:33:48 发布 · 4.8k 阅读

2 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

18 篇文章

订阅专栏

本文深入解析数据库索引的概念，包括其工作原理、优缺点及如何高效使用。涵盖BTree、B+Tree、聚簇和非聚簇索引，并探讨索引在MySQL存储引擎中的实现。

什么是索引？

“索引”是存储引擎用于快速查找记录的一种数据结构。比如一本书的目录，就是这本书的内容的索引，读者可以通过在目录中快速查找自己想要的内容，然后根据页码去找到具体的章节。

数据库也是一样，如果查询语句使用到了索引，会先去索引里面查询，取得数据所在行的物理地址，进而访问数据。

索引的优缺点

优势：
- 索引大大减少了服务器需要扫描的数据量
- 索引帮助服务器避免排序和临时表
- 索引可以将随机I/O变为顺序I/O
缺点：
- 索引本身也是表，因此会占用存储空间。索引的维护和创建需要时间成本，这个成本随着数据量增大而增大
- 构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表

索引的使用原则

对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引，
数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。
在一同值少的列上(字段上)不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可以建立索引。

索引的分类

在MySQL中，常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引。创建语法分别为：

ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col');
ALTER TABLE 'table_name' ADD UNIQUE index_name('col');
ALTER TABLE 'table_name' ADD INDEX index_name('col');
ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col');
ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3');

其中，组合索引又称为多列索引，上述代码中最后一个例子就是建立了3列的索引。MySQL在根据索引查询时，会遵循“最左匹配”原则，即先根据col1的条件查寻，再根据col2的条件查，然后再根据col3的条件去查。

最左匹配原则：InnoDB是基于B-Tree数据结构来实现的，B-Tree会建立从左到右的树形结构。假设索引index(name, age)，会先匹配name字段，如果name相同，然后再去匹配age字段，最后找到数据。如果有这么一条SQL: SELECT * FROM TABLE WHERE AGE = 20;这种情况下B-Tree不知道下一步该查询那个节点，从而导致索引失效。

如果跳过了一个列直接查后面的列，比如下面的语句，就不能使用上面创建的索引了：

SELECT * FROM table_name WHERE clo2=2;

对于某列如果是字符串且比较长（比如UUID），推荐使用前缀索引，即匹配前n个字符。具体这个n取值多少是根据你的数据来的，《高性能MySQL》里提供了一个技巧：通过使用LEFT函数查询，从1开始，不断增加n的值，直到查询结果的行数接近完整列的查询结果的行数，就是合适的n的值。
在这里插入图片描述

索引的实现原理

MySQL的索引是由存储引擎来实现的。由于存储引擎不同，所以具有不同的索引类型，如BTree索引、B+Tree索引、哈希索引等。这里由于主要介绍BTree索引和B+Tree索引，我们平时使用最多的InnoDB引擎就是基于B+Tree索引的。

从二叉搜索树聊起
- 了解过数据结构的朋友应该知道一种叫二叉树的数据结构。二叉树根据用途不同，衍生了不同的变种，比如堆，比如二叉搜索树。
- 而二叉搜索树中，为了防止极端情况树的高度过大影响查询效率，所以衍生出了一些平衡二叉查找树，最典型的就是AVL和红黑树。
- 但二叉树在数据量较大时，深度过深，不太适合数据库的查询，所以数据库使用了多叉树。
BTree

BTree（又称为B-Tree）是一个平衡搜索多叉树。BTree的结构如下图：
在这里插入图片描述
设树的度为2d（d>1），高度为h，那么BTree有以下性质：

1.每个叶子结点的高度一样，等于h；
2.每个非叶子结点由n-1个key和n个指针组成，key和指针相互隔离，结点两端一定是key
3.叶子结点指针为null；
4.非叶子结点的key都是[key,data]二元组，其中key表示作为索引的键，data为键值所在行的其它列的数据；

在BTree中，对索引列是顺序存储的，所以很适合查找范围数据和ORDER BY操作。

B+Tree

B+Tree是BTree的一种变种。B+Tree和BTree的不同主要在于：

1.B+Tree中的非叶子结点不存储数据，只存储键值；
2.B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址；
3.B+Tree的每个非叶子节点由n个键值key和n个指针point组成；

结构图：
在这里插入图片描述

B+Tree对比BTree的优点

一般来说B+Tree比BTree更适合实现外存的索引结构，因为存储引擎的设计专家巧妙的利用了外存（磁盘）的存储结构。

磁盘的最小存储单位是扇区（sector），而操作系统的块（block）通常是整数倍的sector，操作系统以页（page）为单位管理内存，一页（page）通常默认为4K，数据库的页通常设置为操作系统页的整数倍，因此索引结构的节点被设计为一个页的大小，然后利用外存的“预读取”原则，每次读取的时候，把整个节点的数据读取到内存中，然后在内存中查找。

已知内存的读取速度是外存读取I/O速度的几百倍，那么提升查找速度的关键就在于尽可能少的磁盘I/O，那么可以知道，每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的key。