跳至主要內容

Mr.Hope大约 15 分钟

上篇文章讲到使用MySQL的Explain命令可以分析SQL性能瓶颈，优化SQL查询，以及查看是否用到了索引。 我们都知道创建索引可以提高查询效率，但是具体该怎么创建索引？ 哪些字段适合创建索引？ 哪些字段又不适合创建索引？ 本文跟大家一块学习一下如何创建合适数据库索引。

1. 索引分类

在创建索引之前了解一下MySQL有哪些索引，然后我们才能选择合适的索引。 常见的索引有，普通索引、唯一索引、主键索引、联合索引、全文索引等。

普通索引

普通索引就是最基本的索引，没有任何限制。 可以使用命令创建普通索引：

ALTER TABLE `table_name` ADD INDEX index_name (`column`);

唯一索引

与普通索引不同，唯一索引的列值必须唯一，允许为null。 创建方式是这样的：

ALTER TABLE `table_name` ADD UNIQUE index_name (`column`);

主键索引

主键索引是一种特殊的唯一索引，并且一张表只有一个主键，不允许为null。 创建方式是这样的：

ALTER TABLE `table_name` ADD PRIMARY KEY (`column`);

联合索引

联合索引是同时在多个字段上创建索引，查询效率更高。 创建方式是这样的：

ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`);

全文索引

全文索引主要用来匹配字符串文本中关键字。 当需要字符串中是否包含关键字的时候，我们一般用like，如果是以%开头的时候，则无法用到索引，这时候就可以使用全文索引了。 创建方式是这样的：

ALTER TABLE `table_name` ADD FULLTEXT (`column`);

索引底层实现分类

哈希索引

MEMORY 存储引擎默认使用哈希索引，哈希索引的实现原理是：

  • 哈希表结构：哈希索引通过一个哈希函数将键值映射到一个哈希表的桶中。每个桶包含指向数据记录的指针。
  • 无序存储：哈希索引不维护数据的顺序，只是简单地将键值通过哈希函数映射到位置上。

哈希索引的适用场景：

  • 等值查询：哈希索引特别适用于等值查询（如=、!=或者IN操作），因为哈希函数可以快速定位到目标桶。
  • 不支持范围查询：由于哈希表不维护数据的顺序，无法高效地进行范围查询（如BETWEEN、>、<操作）。

B+树索引

InnoDB和MyISAM默认都是使用B+树索引。 B+树索引实现原理：

  • 树结构：B+树是一种平衡树，其每个节点包含指向子节点的指针和键值。叶子节点包含实际的数据记录或指向数据记录的指针。
  • 有序存储：B+树索引维护数据的顺序，支持范围查询和排序操作。

B+树索引适用场景：

  • 范围查询和排序：B+树索引支持高效的范围查询和排序操作，适用于需要进行大量范围扫描的应用场景。
  • 等值查询：虽然等值查询的性能不如哈希索引，但在B+树上进行等值查询的性能也非常高。
  • 查找速度适中：B+树的查找、插入和删除操作的时间复杂度为O(log n)，适中但稳定。
  • 维护成本高：由于需要维护树的平衡性，B+树索引的插入和删除操作会涉及节点的分裂和合并，维护成本较高。

索引实现原理

要知道MySQL索引底层数据结构为啥用B+树？ 为了保证数据安全性，一般都是把数据存储在磁盘里面。当我们需要查询数据的时候，需要读取磁盘，就产生了磁盘IO，相比较内存操作，磁盘IO读取速度是非常慢的。 由于所需数据可能在磁盘并不是连续的，一次数据查询就需要多次磁盘IO，所以就需要我们设计的索引数据结构尽可能的减少磁盘IO次数。

再了解一下这几种二叉树的特性，以及优缺点，就知道哪种数据结构更适合建索引。

二叉搜索树

什么是二叉搜索树：

  1. 若左子树不空，则左子树上所有结点的值均小于它的根结点的值；
  2. 若右子树不空，则右子树上所有结点的值均大于它的根结点的值；
  3. 左、右子树也分别为二叉查找树； image-1.png

二叉搜索树查找数据的时间复杂度是O(logN)，如图所示，最多查找3次就可以查到所需数据。 理想很丰满，现实很骨感。极端情况下，二叉查找树可能退化成线性链表。

链表的查找时间复杂度是O(N)，这时候最多需要7次才能查到所需数据。

该怎么办呢？于是我们就想到了给二叉树加一些限制条件，平衡一下左右子树，然后就引申出了很多平衡树：平衡二叉查找树、红黑树、B树、B+树。咱们分别说一下这几种树的优缺点，看哪种树最适合做索引。

红黑树

什么是红黑树？

  1. 结点是红色或黑色
  2. 根结点是黑色
  3. 所有叶子都是黑色（叶子是NIL结点）
  4. 每个红色结点的两个子结点都是黑色（从每个叶子到根的所有路径上不能有两个连续的红色结点）
  5. 从任一结点到其每个叶子的所有路径都包含相同数目的黑色结点 image-2.png

看蒙了没有？

这么多复杂的规则，就是为了保证从根节点到叶子节点的最长路径不超过最短路径的2倍。

当插入节点或者删除节点的时候，为了满足红黑树规则，可能需要变色和旋转，这是一个复杂且耗时的过程。

红黑树的优点：

  • 限制了左右子树的树高，不会相差过大。

缺点：

  • 规则复杂，一般人想要弄懂这玩意儿，就已经很费劲了，更别说使用了。

B树

什么是B树？

我们知道，树的高度越高，查找次数越多，也就是磁盘IO次数越多，耗时越长，我们能不能想办法降低树的高度，把二叉树变成N叉树？于是B树就来了。

对于一个m阶的B树：

  1. 根节点至少有2个子节点
  2. 每个中间节点都包含k-1个元素和k个子节点，其中 m/2 <= k <= m
  3. 每个叶子节点都包含k-1个元素，其中 m/2 <= k <= m
  4. 中间节点的元素按照升序排列
  5. 所有的叶子结点都位于同一层 image-1.png

解释说明：

  1. 根节点（8）有两个子节点，左子节点（3 5）和右子节点（11 15）。
  2. 左子节点（3 5）中有2个元素和3个子节点。
  3. 元素是3和5，按照升序排列。
  4. 子节点是（1 2）、（4）、（6 7），
  5. 而（1 2）中元素小于3，（4）中的元素在3和5中间，（6 7）的元素大于5，符合B树特征。

B树这样的设计有哪些优点呢？

高度更低，每个节点含有多个元素，查找的时候一次可以把一个节点中的所有元素加载到内存中作比较，两种改进都大大减少了磁盘IO次数。

B+树

什么是B+树？

相比较B树，B+树又做了如下约定：

  1. 有k个子节点的中间节点就有k个元素（B树中是k-1个元素），也就是子节点数量 = 元素数量。 每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
  2. 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。
  3. 非叶子节点只保存索引，不保存数据。（B树中两者都保存）
  4. 叶子结点包含了全部元素的信息，并且叶子结点按照元素大小组成有序列表。 image-1.png

B+树这样设计有什么优点呢？

  1. 每个节点存储的元素更多，看起来比B树更矮胖，导致磁盘IO次数更少。
  2. 非叶子节点不存储数据，只存储索引，叶子节点存储全部数据。 这样设计导致每次查找都会查到叶子节点，效率更稳定，便于做性能优化。
  3. 叶子节点之间使用有序链表连接。 这样设计方便范围查找，只需要遍历链表中相邻元素即可，不再需要二次遍历二叉树。

很明显，B树和B+树就是为了文件检索系统设计的，更适合做索引结构。

2. 哪些字段适合创建索引？

我总结了有以下几条：

  1. 频繁查询的字段适合创建索引 一张表的字段总会有冷热之分，很明显那些频繁使用的字段更适合为它创建索引。
  2. 在where和on条件出现的字段优先创建索引 为什么不是在select后面出现的字段优先创建索引？ 因为查询SQL会先匹配on和where条件的字段，具体的匹配顺序是这样的：

from > on > join > where > group by > having > select > distinct > order by > limit

  1. 区分度高的字段适合创建索引 比如对于一张用户表来说，生日比性别的区分度更高，更适合创建索引。 可以使用下面的方式手动统计一下，每个字段的区分度，值越大，区分度越高：
select 
    count(distinct birthday)/count(*), 
    count(distinct gender)/count(*) 
from user;

image-20220730230017044.png 对于已经创建好的索引，我们还可以使用MySQL命令查看每个索引的区分度排名： image-20220730230358758.png 图中Cardinality列表示索引的区分度排名，也被称为基数。 4. 有序的字段适合创建索引 有序的字段在插入数据库的过程中，仍能保持B+树的索引结构，不需要频繁更新索引文件，性能更好。

3. 哪些字段不合适创建索引？

说完哪些字段适合创建索引，就有不适合创建索引的的字段。

  1. 区分度低的字段不适合创建索引。 刚才说了用户表中性别的区分度较低，不如生日字段适合创建索引。
  2. 频繁更新的字段不适合创建索引 更新字段的过程中，需要维护B+树结构，会频繁更新索引文件，降低SQL性能。
  3. 过长的字段不适合创建索引 过长的字段会占用更多的空间，不适合创建索引。
  4. 无序的字段不适合创建索引 无序的字段在插入数据库的过程中，为了维护B+树索引结构，需要频繁更新索引文件，性能较差。

4. 创建索引的其他注意事项

  1. 优先使用联合索引 查询的时候，联合索引比普通索引能更精准的匹配到所需数据。 image-20220730232213888.png 图中就是在（age，name）两个字段上建立的联合索引，在B+树中的存储结构。 可以看出，是先age排序，age相等的数据，再按name排序。 对于这条查询SQL：
select age,name from user where age=18 and name='李四';

联合索引只需一次就可以查到所需数据，如果我们只在age字段上建立索引，会先匹配到age=18的三条数据，然后再逐个遍历，效率更差，所以平时应该优先使用联合索引。 2. 使用联合索引时，区分度高的字段放前面 这样可以减少查询次数，更快地匹配到所需数据。 3. 过长字符串可以使用前缀索引 比如在匹配用户地址的时候，如果乡镇已经能区分大部分用户了，就没必要精确到街道小区了。 创建普通索引的时候，指定索引长度，就可以创建前缀索引了。

ALTER TABLE `user` ADD INDEX idx_address (address(3));
  1. 值唯一的字段，使用唯一索引 使用唯一索引，可以避免程序bug导致产生重复数据。
  2. 排序和分组字段也尽量创建索引 在order by和group by中的字段也尽量创建索引，避免使用文件排序，可以使用索引排序提供性能。
  3. 避免创建过多索引 索引好用，适度即可。创建过多的索引，会占用更多存储空间，也会严重影响SQL性能，每次更新SQL，都需要更新大量索引文件，得不偿失。

索引失效场景

工作中，经常遇到这样的问题，我明明在MySQL表上面加了索引，为什么执行SQL查询的时候却没有用到索引？ 同一条SQL有时候查询用到了索引，有时候却没用到索引，这是咋回事？

原因可能是索引失效了，失效的原因有以下几种，看你有没有踩过类似的坑？

  1. 数据准备： 有这么一张用户表，在name字段上建个索引：
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB COMMENT='用户表';
  1. Explain详解： 想要查看一条SQL是否用到索引？用到了哪种类型的索引？

可以使用explain关键字，查看SQL执行计划。例如：

explain select * from user where id=1;

image-20220629230715808.png 可以看到type=const，表示使用了主键索引。 explain的所有type类型如下： image-20220630000000083.png

  1. 失效原因

1. 数据类型隐式转换

name字段是varchar类型，如果我们使用数据类型查询，就会产生数据类型转换，虽然不会报错，但是无法用到索引。

explain select * from user where name='一灯';
image-20220629231442732.png
image-20220629231442732.png
explain select * from user where name=18;
image-20220629231513592.png
image-20220629231513592.png

2. 模糊查询 like 以%开头

explain select * from user where name like '张%';
image-20220629231905411.png
image-20220629231905411.png
explain select * from user where name like '%张';
image-20220629231938893.png
image-20220629231938893.png

3. or前后没有同时使用索引

虽然name字段上加了索引，但是age字段没有索引，使用or的时候会全表扫描。

# or前后没有同时使用索引，导致全表扫描
explain select * from user where name='一灯' or age=18;
image-20220629232130791.png
image-20220629232130791.png

4. 联合索引，没有使用第一列索引

如果我们在（name，age）上，建立联合索引，但是查询条件中只用到了age字段，也是无法用到索引的。 使用联合索引，必须遵循最左匹配原则，首先使用第一列字段，然后使用第二列字段。

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB COMMENT='用户表';
image-20220630000609202.png
image-20220630000609202.png

5. 在索引字段进行计算操作

如果我们在索引列进行了计算操作，也是无法用到索引的。

# 在主键索引上进行计算操作，导致全表扫描
explain select * from user where id+1=2;
image-20220629233208133.png
image-20220629233208133.png

6. 在索引字段字段上使用函数

如果我们在索引列使用函数，也是无法用到索引的。 image-20220629233447426.png

7. 优化器选错索引

同一条SQL有时候查询用到了索引，有时候却没用到索引，这是咋回事？ 这可能是优化器选择的结果，会根据表中数据量选择是否使用索引。 image-20220629234641204.png 当表中大部分name都是一灯，这时候用name='一灯'做查询，还会不会用到索引呢？ 索引优化器会认为，用索引还不如全表扫描来得快，干脆不用索引了。 image-20220629234900354.png 当然我们认为优化器优化的不对，也可以使用force index强制使用索引。 image-20220629235137298.png

默认页脚
Copyright © 2025 Mr.Hope