MySQL 进阶讲解（一）：MySQL 索引原理

发表于 | 分类于 | 评论数： | 阅读次数：
本文字数统计： 8.7k | 阅读时长 ≈ 8 分钟

前言

索引对查询的速度有着至关重要的影响，理解索引也是进行数据库性能调优的起点。索引在 MySQL 中也叫做“键”，存储引擎用于快速查找记录的一种数据结构，通过合理的使用数据库索引可以大大提高系统的访问性能。关于 MySQL 索引的好处，如果正确合理设计并且使用索引的 MySQL 是一辆兰博基尼的话，那么没有设计和使用索引的 MySQL 就是一个人力三轮车。对于没有索引的表，单表查询可能几十万数据就是瓶颈，而通常大型网站单日就可能会产生几十万甚至几百万的数据，没有索引查询会变的非常缓慢。接下来主要介绍在 MySQL 数据库中索引类型，以及如何创建出更加合理且高效的索引技巧。

MySQL 索引的概念

索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

索引的优缺点

  • 优势：可以快速检索，减少 I/O 次数，加快检索速度；根据索引分组和排序，可以加快分组和排序；
  • 劣势：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的 1.5 倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表；

MySQL 索引的类型

常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引。

阿里巴巴的 Java 开发手册中提到：

【强制】主键索引名为 pk_字段名；唯一索引名为 uk_字段名；普通索引名则为 idx_字段名。 说明：pk_ 即 primary key；uk_ 即 unique key；idx_ 即 index 的简称。

1
2
3
4
5
-- 显示索引信息
SHOW INDEX FROM table_name;

-- 删除索引
DROP INDEX index_name ON table_name;

PRIMARY - 主键索引

即主索引，根据主键 pk_clolum（length）建立索引，不允许重复，不允许空值；

UNIQUE - 唯一索引

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 创建唯一索引[唯一索引名为 uk_字段名]
CREATE UNIQUE INDEX uk_column_name ON table_name (column_name(length));

-- 修改表结构
ALTER TABLE table_name ADD UNIQUE uk_column_name ON (column_name(length));

-- 创建表的时候直接指定
CREATE TABLE `table` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`title` CHAR (255) NOT NULL,
	`content` text NULL,
	`time` INT (10) NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	UNIQUE uk_title (title(length))
);

INDEX - 普通索引

这是最基本的索引，它没有任何限制，MyIASM 中默认的 BTREE 类型的索引，也是我们大多数情况下用到的索引。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 直接创建索引[如果是 CHAR,VARCHAR 类型, length 可以小于字段实际长度；如果是 BLOB 和 TEXT 类型, 必须指定 length.]
CREATE INDEX idx_column_name ON table_name (column_name(length));

-- 修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX idx_column_name ON (column_name(length));

-- 创建表的时候同时创建索引
CREATE TABLE `table` (
    `id` INT (11) NOT NULL AUTO_INCREMENT,
    `title` CHAR (255) NOT NULL,
    `content` text NULL,
    `time` INT (10) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX idx_title (title(length))
);

FULLTEXT - 全文索引

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。它有很多需要注意的，如停用词、词干、复数、布尔搜索等。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 直接创建索引
CREATE FULLTEXT INDEX ft_column_name ON table_name (content);

-- 修改表结构添加全文索引
ALTER TABLE table_name ADD FULLTEXT ft_column_name ON (column_name);

-- 创建表的适合添加全文索引
CREATE TABLE `table` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`title` CHAR (255) NOT NULL,
	`content` text NULL,
	`time` INT (10) NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	FULLTEXT (content)
);

组合索引

一个表中含有多个单列索引不代表是组合索引，通俗一点讲组合索引是：包含多个字段但是只有索引名称。　在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX idx_column_name_1_column_name_2 (column_name_1, column_name_2);

-- 直接创建索引
CREATE INDEX idx_column_name_1_column_name_2 ON table_name (column_name_1, column_name_2);

-- 创建表的时候同时创建索引
CREATE TABLE `table` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`title` CHAR (255) NOT NULL,
	`content` text NULL,
	`time` INT (10) NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	INDEX idx_title_time (`title`, `time`)
);

MySQL 索引的方法

不同的存储引擎对于索引有不同的支持：Innodb 和 MyISAM 默认的索引是 Btree 索引；而 MEMORY 默认的索引是 Hash 索引。

Hash 索引

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像 Btree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的 IO 访问，所以 Hash 索引的查询效率要远高于 Btree 索引。

可能很多人又有疑问了，既然 Hash 索引的效率要比 Btree 高很多，为什么大家不都用 Hash 索引而还要使用 Btree 索引呢？任何事物都是有两面性的，Hash 索引也一样，虽然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些：

（1）Hash 索引仅仅能满足 “=”,”IN” 和 “<=>” 查询，不能使用范围查询。

如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和 Hash 运算前完全一样。

（2）Hash 索引无法被用来避免数据的排序操作。

由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且 Hash 值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。

（3）Hash 索引不能利用部分索引键查询。

对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

（4）Hash 索引在任何时候都不能避免表扫描。

前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash 运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

（5）Hash 索引遇到大量 Hash 值相等的情况后性能并不一定就会比 Btree 索引高。

对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

Btree 索引

Btree 索引是 MySQL 数据库中使用最为频繁的索引类型，除了 ARCHIVE 存储引擎之外的其他所有的存储引擎都支持 Btree 索引。不仅仅在 MySQL 中是如此，实际上在其他的很多数据库管理系统中 Btree 索引也同样是作为最主要的索引类型，这主要是因为 Btree 索引的存储结构在数据库的数据检索中有非常优异的表现。

一般来说，MySQL 中的 Btree 索引的物理文件大多都是以 Balance Tree 的结构来存储的，也就是所有实际需要的数据都存放于 Tree 的 Leaf Node，而且到任何一个 Leaf Node 的最短路径的长度都是完全相同的，所以我们大家都称之为 Btree 索引。当然，可能各种数据库（或 MySQL 的各种存储引擎）在存放自己的 Btree 索引的时候会对存储结构稍作改造。如 Innodb 存储引擎的 Btree 索引实际使用的存储结构实际上是 B+Tree，也就是在 Btree 数据结构的基础上做了很小的改造，在每一个 Leaf Node 上面除了存放索引键的相关信息之外，还存储了指向与该 Leaf Node 相邻的后一个 Leaf Node 的指针信息，这主要是为了加快检索多个相邻 Leaf Node 的效率考虑。

MYSQL 使用 B+Tree 做索引：

（1）所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的。（只有根节点存储关键字最后树的末梢才有值)

（2）非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层。（非根节点，存储的其实是指向根节点的索引）

（3） 不可能在非叶子结点存数据。

（4）根节点横向也有链指针。(方便快速顺藤摸瓜嘛，没这个指针，就算下一个取的值是挨着的邻居，也得跑个圈才能拿到)

综合起来，B+Tree 比 B - 树优势有三个：1、IO 次数更少；2、查询性能稳定；3、范围查询简便。

聚簇索引与非聚簇索引

当数据库一条记录里包含多个字段时，一棵 B+Tree 就只能存储主键，如果检索的是非主键字段，则主键索引失去作用，又变成顺序查找了。这时应该在第二个要检索的列上建立第二套索引。这个索引由独立的 B+Tree 来组织。有两种常见的方法可以解决多个 B+Tree 访问同一套表数据的问题，一种叫做聚簇索引（clustered index），一种叫做非聚簇索引（secondary index）。这两个名字虽然都叫做索引，但这并不是一种单独的索引类型，而是一种数据存储方式。对于聚簇索引存储来说，行数据和主键 B+Tree 存储在一起，辅助键 B+Tree 只存储辅助键和主键，主键和非主键 B+Tree 几乎是两种类型的树。对于非聚簇索引存储来说，主键 B+Tree 在叶子节点存储指向真正数据行的指针，而非主键。

非聚簇索引

  • 非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的 key 都存储指向键值对应的数据的物理地址。
  • 非聚簇索引的数据表和索引表是分开存储的。
  • 非聚簇索引中的数据是根据数据的插入顺序保存。因此非聚簇索引更适合单个数据的查询。插入顺序不受键值影响。
  • 只有在 MyISAM 中才能使用 FULLTEXT 索引。

聚簇索引

  • 聚簇索引的主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。
  • 聚簇索引的数据和主键索引存储在一起。
  • 聚簇索引的数据是根据主键的顺序保存。因此适合按主键索引的区间查找，可以有更少的磁盘 I/O，加快查询速度。但是也是因为这个原因，聚簇索引的插入顺序最好按照主键单调的顺序插入，否则会频繁的引起页分裂，严重影响性能。
  • 在 InnoDB 中，如果只需要查找索引的列，就尽量不要加入其它的列，这样会提高查询效率。

MyISAM Btree 索引实现

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

MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址，读取相应数据记录。

InnoDB Btree 索引实现

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

InnoDB 中，表数据文件本身就是按 B+Tree 组织的一个索引结构，这棵树的叶结点 data 域保存了完整的数据记录。因为 InnoDB 的数据文件本身要按主键聚集，所以 InnoDB 要求表必须有主键（MyISAM 可以没有），如果没有显式指定，则 MySQL 系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，这个字段长度为 6 个字节，类型为长整形。

为了更形象说明这两种索引的区别，我们假想一个表如下图存储了 4 行数据。其中 Id 作为主索引，Name 作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。
　　


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

（1）由于行数据和叶子节点存储在一起，这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键 Id 来组织数据，获得数据更快。

（2）辅助索引使用主键作为“指针”，而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是 InnoDB 在移动行时无须更新辅助索引中的这个“指针”。也就是说行的位置会随着数据库里数据的修改而发生变化，使用聚簇索引就可以保证不管这个主键 B+Tree 的节点如何变化，辅助索引树都不受影响。

建立索引的规则

1、选择唯一性索引：唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2、为经常需要排序、分组和联合操作的字段建立索引：经常需要 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

3、为常作为查询条件的字段建立索引：如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

4、限制索引的数目：索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

5、尽量使用数据量少的索引：如果索引的值很长，那么查询的速度会受到影响。例如，对一个 CHAR(100)类型的字段进行全文检索需要的时间肯定要比对 CHAR(10)类型的字段需要的时间要多。

6、尽量使用前缀来索引：如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT 和 BLOG 类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

7、删除不再使用或者很少使用的索引：表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

8、最左前缀匹配原则，非常重要的原则：Mysql 会一直向右查找直到遇到范围操作（>，<，like、between）就停止匹配。比如 a=1 and b=2 and c>3 and d=6；此时如果建立了（a,b,c,d）索引，那么后面的 d 索引是完全没有用到，当换成了（a,b,d,c）就可以用到。

9、= 和 in 可以乱序：比如 a = 1 and b = 2 and c = 3 建立 (a,b,c) 索引可以任意顺序，mysql 的查询优化器会帮你优化成索引可以识别的形式。

10、尽量选择区分度高的列作为索引：区分度的公式是 count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是 1，而一些状态、性别字段可能在大数据面前区分度就 是 0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要 join 的字段我们都要求是 0.1 以上，即平均 1 条扫描 10 条记录。

11、尽量的扩展索引，不要新建索引：比如表中已经有 a 的索引，现在要加 (a,b) 的索引，那么只需要修改原来的索引即可。

注意：选择索引的最终目的是为了使查询的速度变快。上面给出的原则是最基本的准则，但不能拘泥于上面的准则。读者要在以后的学习和工作中进行不断的实践。根据应用的实际情况进行分析和判断，选择最合适的索引方式。

参考博文

[1]. 剖析 Mysql 的 InnoDB 索引
[2]. 数据库索引 B 树、B+Tree、Hash 索引

MySQL 进阶讲解系列

谢谢你长得那么好看，还打赏我！😘
0%