MySQL 索引的使用

发表于 更新于 阅读次数： 评论数： 本文字数： 8.7k 阅读时长 ≈ 8 分钟

大纲

索引的介绍

索引是一种特殊的文件（InnoDB 数据表上的索引是表空间的一个组成部分），包含了对数据表里所有记录的引用指针。索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。创建索引时，需要确保该索引是应用在 SQL 查询语句的条件 (一般是 WHERE、JOIN 子句的条件)。

索引的类型

  • FULLTEXT：即为全文索引，目前只有 MyISAM 引擎支持，其可以在 CREATE TABLE，ALTER TABLE，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR、TEXT 列上可以创建全文索引
  • HASH：由于 HASH 的唯一性及类似键值对的形式，很适合作为索引，HASH 索引可以一次定位，不需要像树形索引那样逐层查找，因此具有极高的效率。但是，这种高效是有条件的，即只在 = 和 in 条件下才高效，对于范围查询、排序及组合索引仍然效率不高
  • BTREE：一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口 Root 开始，依次遍历 Node，获取 Leaf，这是 MySQL 里默认和最常用的索引类型
  • RTREE：在 MySQL 很少使用，仅支持 geometry 数据类型，支持该类型的存储引擎有 MyISAM、BDb、InnoDb、NDb、Archive

索引的种类

  • 普通索引：仅加速查询（BTREE 类型）
  • 全文索引：对文本的内容进行分词和搜索
  • 唯一索引：加速查询 + 列值唯一（可以有 NULL）+ 表中可以有多个唯一索引
  • 主键索引：加速查询 + 列值唯一（不可以有 NULL） + 表中只能有一个主键索引
  • 组合索引：又叫 联合索引，多列组成一个索引，专门用于组合搜索，其效率大于索引合并（使用多个单列索引组合搜索）

索引的优缺点

  • 优点

    • 可以提高数据检索的效率，降低数据库的 IO 成本
    • 通过索引列对数据进行排序，可以降低数据排序的成本，降低 CPU 的消耗

  • 缺点

    • 高并发写入会影响性能
    • 虽然索引大大提高了查询效率，但同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE 操作。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
    • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用磁盘空间的。一般情况这个问题不太严重，但如果在一个大表上创建了多种组合索引，索引文件的体积会膨胀得很快

覆盖索引是什么

覆盖索引就是一个 SQL 在执行时，该 SQL 所要查询的字段都包含在索引中（即查询字段都建立了索引），那么就表示该 SQL 走完索引后不用回表了，所需要的字段都在当前索引的叶子节点上存在，可以直接作为结果返回。这样可以显著提高查询性能，因为从索引中读取数据通常比从数据表中读取数据要快。

索引的使用

索引的使用案例

  • 创建索引
1
2
3
4
5
6
7
8
9
10
11
--创建普通索引
CREATE INDEX index_name ON table_name(col_name);

--创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);

--创建普通组合索引
CREATE INDEX index_name ON table_name(col_name_1, col_name_2);

--创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1, col_name_2);
  • 通过修改表结构创建索引
1
ALTER TABLE table_name ADD INDEX index_name(col_name);
  • 创建表时直接指定索引
1
2
3
4
5
CREATE TABLE table_name (
    ID INT NOT NULL,
    col_name VARCHAR (16) NOT NULL,
    INDEX index_name(col_name)
);
  • 删除索引
1
2
3
4
5
--直接删除索引
DROP INDEX index_name ON table_name;

--修改表结构删除索引
ALTER TABLE table_name DROP INDEX index_name;
  • 其它相关命令
1
2
3
4
5
6
7
8
9
10
11
12
13
--查看表结构
desc table_name;

--查看创建表的SQL
show create table table_name;

--查看索引
show index from table_name;

--查看执行时间
set profiling = 1;
... EXecute SQL ...
show profiles;

索引的使用注意事项

哪些情况需要创建索引

一般来说，在 WHERE 和 JOIN 子句中出现的列需要建立索引，但也不完全如此，因为 MySQL 只对 <、<=、=、>、>=、BETWEEN、IN 以及某些时候的 LIKE 才会使用索引。例如下述的 SQL 语句，就需要对 city 和 age 列建立索引，由于 mytable_m 表的 userame 也出现在了 JOIN 子句中，因此也有对它建立索引的必要。

1
SELECT t.Name  FROM mytable_t LEFT JOIN mytable_m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' ;

特别注意

上面提到只有某些时候的 LIKE 才需建立索引，这是因为在以通配符 % 开头作查询时，MySQL 不会使用索引；只有以通配符 % 结尾做查询时，MySQL 才会使用到索引。但有一种情况例外，那就是当触发了覆盖索引（select 的数据列只从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖）的情况下，以通配符 % 开头作查询 MySQL 也会使用索引。例如：如果表里面只有 id 和 username 两个字段且都加了索引，那么 select * like '%username' 查询也是会使用索引的，前提是 select 数据列都加了索引。

哪些情况不要创建索引

  • 表记录太少
  • 增删改非常频繁的字段
  • WHERE 条件里用不到的字段
  • 唯一性太差的字段，尽管频繁作为查询条件，例如：性别字段

哪些字段应该创建索引

  • 查询中与其他表关联的字段，例如外键应该建立索引
  • WHERE 和 JOIN 子句中，较频繁作为查询条件的字段应该创建索引
  • 查询中排序（order by）、分组（group by）、统计的字段应该建立索引

哪些情况索引不会生效

  • 计算、函数导致索引失效
  • != 或者 <> 会导致索引失效
  • or 前后存在非索引的列，会导致索引失效
  • is null 可以使用索引，is not null 可能无法使用索引（不同版本和数据决定）
  • like 查询，以 % 开头的查询不会使用索引，除非 select 数据列都加了索引（触发覆盖索引）
  • 对于组合索引（联合索引），如果不满足最左前缀法则，则索引不会生效
  • 类型转换会导致索引失效，比如列类型是字符串，那一定要在查询条件中将数据使用单引号包裹起来，否则索引不生效
  • 如果数据库优化器估算使用全表扫描要比使用索引快，则不会使用索引

索引使用的注意事项是什么

  • 针对普通 SQL 查询

    • 避免使用 select *
    • 连接表时注意条件类型需要一致
    • 创建表时尽量使用 char 代替 varchar
    • 使用 count (1) 或 count (列) 代替 count (*)
    • 使用表连接（JOIN）来代替子查询（Sub-Queries）

  • 针对普通索引的使用

    • 单表的索引数量建议控制在 5 个以内
    • 单索引的字段数量不允许超过 5 个，当字段超过 5 个时，实际已经起不到有效过滤数据的作用了
    • 禁止在更新十分频繁、唯一性（区分度）不高的字段上建立索引
    • 索引散列值（重复多的值）不适合建索引，例如：性别字段
    • 索引不会包含有 NULL 值的列，只要列中包含有 NULL 值都将不会被包含在索引中，组合索引中只要有一列含有 NULL 值，那么这一列对于此组合索引就是无效的，因此在数据库设计时不要让字段的默认值为 NULL
    • 不要在列上进行运算，例如 select * from users where YEAR(adddate) < 2007，将在每个行记录上进行运算，这将导致索引失效而进行全表扫描，因此可以改成 select * from users where adddate < '2007-01-01'
    • 尽量使用短索引，对串列进行索引，如果可以应该指定一个前缀长度。例如：如果有一个 CHAR (255) 的列，如果在前 10 个或 20 个字符内，多数值是惟一的，那么就不要对整个列进行索引；短索引不仅可以提高查询速度，还可以节省磁盘空间和 I/O 操作
    • MySQL 5.0 之前，SQL 查询只能使用一个索引，因此如果 WHERE 子句中已经使用了索引的话，那么 order by、group by 中的列是不会使用索引的。因此如果数据库默认排序可以符合要求的情况下，不要使用排序操作，同时尽量使用不包含多个列的排序，如果需要最好给这些列创建组合索引

  • 针对组合索引的使用

    • 使用组合索引代替多个单列索引（经常使用多个条件查询时）
    • 建立组合索引时，必须将唯一性（区分度）高的字段放在最前面

数据库开发的三十六条军规

  • 美团内部 MySQL 数据库开发的三十六条军规，请点击 [这里](/pdf/MySQL 数据库开发的三十六条军规.pdf) 阅读。

如何查看索引的使用效果

获取执行计划

Explain + 查询 SQL，用于显示 SQL 执行信息参数，根据参考信息可以进行 SQL 优化或者判断索引是否生效

查看索引的使用情况

1
show status like '%Handler_read%';
  • handler_read_key：这个值越高越好，越高表示使用索引查询到的次数越多
  • handler_read_rnd_next：这个值越高，说明查询效率越低效

索引的最左前缀法则

使用组合索引（联合索引）时，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效 (这一列后面的字段的索引失效)。

最左前缀法则的使用

使用是案例一

案例内容：组合索引 + where 子句的使用

使用是案例二

案例内容：组合索引 + where 子句的使用

假设 MySQL 8.0 数据库中有一张表 t_user，表里面有 id、phone、name、age、status 这些字段，而且 phone、name、age 这三个字段创建了组合索引 idx_phone_name_age。

1
2
# 创建组合索引
mysql> create index idx_phone_name_age on t_user(phone, name, age);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# 索引不会失效，因为遵循最左前缀法则
mysql> explain select * from t_user where phone = '123456' and name = 'Amy' and age = 20;
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys      | key                | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_phone_name_age | idx_phone_name_age | 221     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------------------+------+----------+-------+

# 索引不会失效，最左前缀法则和过滤条件的顺序无关
mysql> explain select * from t_user where name = 'Amy' and age = 20 and phone = '123456';
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys      | key                | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_phone_name_age | idx_phone_name_age | 221     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------------------+------+----------+-------+

# 索引部分失效，因为跳跃了 name 这里一列
mysql> explain select * from t_user where phone = '123456' and age = 20;
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_phone_name_age | idx_phone_name_age | 63      | const |    4 |    25.00 | Using index condition |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+

# 索引全部失效，因为跳跃了 phone 这里一列，将不遵循最左前缀法则
mysql> explain select * from t_user where name = 'Amy' and age = 20;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

使用是案例三

案例内容：组合索引 + where 子句 + order by 子句的使用

假设 MySQL 8.0 数据库中有一张表 t_user，表里面有 id、phone、name、age、status 这些字段，而且 phone、name、age 这三个字段创建了组合索引 idx_phone_name_age。

  • 创建组合索引
1
mysql> create index idx_phone_name_age on t_user(phone, name, age);
  • 无过滤条件（如 where 条件，不包括 limit 条件），索引不会生效
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 索引全部失效，因为没有过滤条件
mysql> explain select * from t_user order by phone, name, age;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+

# 索引全部失效，因为没有过滤条件，不包括 `limit` 条件
mysql> explain select * from t_user order by phone, name, age limit 5;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+

# 索引不会失效，因为有过滤条件
mysql> explain select * from t_user where phone = '123456' order by phone, name, age;
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_phone_name_age | idx_phone_name_age | 63      | const |    4 |   100.00 | NULL  |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
  • 查询字段与过滤字段对索引的影响
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 索引部分生效
mysql> explain select * from t_user where phone = '123456' and age > 20 order by phone, name, age;
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_phone_name_age | idx_phone_name_age | 63      | const |    4 |    33.33 | Using index condition |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+

# 索引完全生效，因为触发了覆盖索引
mysql> explain select phone, age from t_user where phone = '123456' and age > 20 order by phone, name, age;
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table  | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_phone_name_age | idx_phone_name_age | 63      | const |    4 |    33.33 | Using where; Using index |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+--------------------------+
  • 排序字段的顺序和索引的顺序不一致对索引的影响
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 排序字段的顺序和索引的顺序一致时，不会触发 `filesort`
mysql> explain select phone, age from t_user where phone = '123456' order by phone, name, age;
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_phone_name_age | idx_phone_name_age | 63      | const |    4 |   100.00 | Using index |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+

# 排序字段的顺序和索引的顺序不一致时，会触发 `filesort`
mysql> explain select phone, age from t_user where phone = '123456' order by phone, age, name;
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra                       |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_phone_name_age | idx_phone_name_age | 63      | const |    4 |   100.00 | Using index; Using filesort |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------------+
  • 多个索引字段排序时，排序方向（升序、降序）不一致对索引的影响
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 多个索引字段排序时，排序方向（升序、降序）一致，不会触发 `filesort`
mysql> explain select phone, age from t_user where phone = '123456' order by phone desc, name desc, age desc;
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+----------------------------------+
| id | select_type | table  | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra                            |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+----------------------------------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_phone_name_age | idx_phone_name_age | 63      | const |    4 |   100.00 | Backward index scan; Using index |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+----------------------------------+

# 多个索引字段排序时，排序方向（升序、降序）不一致，会触发 `filesort`
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra                       |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_phone_name_age | idx_phone_name_age | 63      | const |    4 |   100.00 | Using index; Using filesort |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------------+

最左前缀法则的知识点

  • 在创建组合索引的时候，过滤性（唯一性）最好的字段在索引字段顺序中，位置越靠左边越好
  • 组合索引出现范围查询时，应该尽量把这个字段放在索引顺序的最右边
  • 在创建组合索引的时候，应该尽量包含 where 子句中的字段
  • 应该尽量避免造成索引失效的情况

最左前缀法则的关键点

  • 如果排序字段不在索引列上，MySQL 就会触发 filesort，导致查询性能降低；有两种排序算法：单路排序和双路排序
  • 无过滤不索引，即如果没有过滤条件（如 where 条件，不包括 limit 条件），索引不会生效
  • 在 SQL 语句中，如果索引字段的顺序写错了，会触发 filesort
  • 使用 order by 查询时，如果索引字段的位置非最左，会触发 filesort
  • 使用 order by 查询时，如果索引字段的排序方向（升序、降序）不一致，会触发 filesort
  • 熟练使用 explain，必要时使用 optimizer_trace

最左前缀法则的面试题

面试题目

MySQL 使用 Innodb 引擎，请简述 MysQL 索引的最左前缀法则如何优化 Order By 语句。

  • 在 SQL 语句中，一定要有过滤条件（如 where 条件），否则执行 order by 时索引会失效
  • 首先要对 SQL 进行分析，检查必要的查询字段、过滤字段、排序字段是否按顺序创建好了素引
  • 如果查询字段没有创建索引，可能会产生回表操作，导致触发 filesort，降低查询性能
  • 排序字段的顺序和索引的顺序不一致时，会触发 filesort，降低查询性能
  • 多个索引字段排序时，如果排序方向（升序、降序）不一致，会触发 filesort，降低查询性能
  • 使用 explain 观察查询类型和索引利用情况
  • 尽可能减少不必要的 filesort

聚簇索引和非聚簇索引

两者的区别是什么

聚簇索引和非聚簇索引是数据库管理系统（DBMS）中常见的两种索引类型，它们在数据存储和检索方式上有显著的区别。以下是对这两种索引的详细比较：

  • 聚簇索引（Clustered Index）：

    • 定义：又称为 “主键索引” 或者 “聚集索引”，索引和数据存储在一起。数据表中的记录按索引的键值顺序存储在磁盘上。每个表只能有一个聚簇索引。
    • 特点：
      • 数据存储顺序：数据物理上按索引键的顺序存储。
      • 主键：通常主键会自动成为聚簇索引，但也可以指定其他列为聚簇索引。
      • 索引结构：InnoDB 的聚簇索引是按照主键顺序构建 B+ 树结构的。
      • 检索速度：对索引键的查询速度较快，因为数据和索引在一起。
      • 插入和更新：插入和更新可能会比较慢，因为需要保持数据的物理顺序。
      • 适用场景：适用于经常需要范围查询的场景，比如查找某一范围内的数据。

  • 非聚簇索引（Non-Clustered Index）：

    • 定义：又称为 “辅助索引” 或者 “二级索引”，索引和数据分开存储。非聚簇索引存储的是索引键值和指向数据记录的指针。每个表可以有多个非聚簇索引。
    • 特点：
      • 数据存储顺序：数据的物理存储顺序与索引无关。
      • 索引存储：索引存储的是键值和指向数据的指针。
      • 检索速度：对索引键的检索速度较快，但访问实际数据时需要通过指针进行二次查找。
      • 插入和更新：插入和更新相对较快，因为不需要保持数据的物理顺序。
      • 适用场景：适用于查找特定值或者少量记录的场景。

  • 总结：

    • 在 InnoDB 中，根据索引的存储形式，可以分为聚簇索引和非聚簇索引，这两种索引的底层数据结构都是 B+ 树。
    • InnoDB 使用的是聚簇索引，树的叶子节点上的 data 就是数据本身。
    • MyISAM 使用的是非聚簇索引，树的叶子节点上的 data 不是数据本身，而是数据存放的地址。
    • InnoDB 的主键索引是聚簇索引，而唯一索引、组合索引、前缀索引都是非聚簇索引。
    • 在 InnoDB 中，如果定义了主键（Primary Key），那么主键就是聚簇索引；如果没有定义主键，就会找第一个非空的唯一（Unique）列作为聚簇索引；如果没有非空唯一列，InnoDB 会自动创建一个隐藏的 row-id 作为聚簇索引。

扩展阅读

回表操作是什么

回表的概述

  • 回表是指数据库根据非聚簇索引（非主键索引）找到了指定的记录所在行后，还需要根据主键再次回到聚簇索引（主键索引）中查询的过程。这个过程在 MySQL 中可能涉及两次查询：首先通过非聚簇索引扫描找到满足条件的记录的主键值，然后再通过主键值去聚簇索引中查找完整的行记录。
  • 具体来说，在 InnoDB 存储引擎中，当通过非聚簇索引查询数据时，由于非聚簇索引中不包含完整的行数据，因此需要根据非聚簇索引中找到的主键值，再去聚簇索引中查找完整的行数据，这个过程就是回表。
  • 然而，回表操作虽然提供了更全面的数据信息，但也带来了一些问题和局限性。首先，回表操作需要访问两次索引，增加了 I/O 开销和 CPU 消耗，对查询性能有一定的影响。特别是在高并发、大数据量的情况下，回表可能成为性能瓶颈。其次，由于回表操作是基于物理地址来获取数据，如果在回表过程中发生了数据修改（如 DELETE、UPDATE 操作），则可能会读取到不一致或错误的数据。
  • 因此，在设计索引时，需要结合具体的业务场景和查询需求，选择合适的索引策略，以尽量减少回表的次数和影响，提高查询效率和性能。比如，可以通过使用覆盖索引来避免回表操作，覆盖索引是指索引包含了查询所需的所有字段的值，因此可以直接通过索引获取查询结果，而无需再去访问数据表。

详细案例说明

  • 创建表，并插入数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE `employee` (
  `id` INT(11) NOT NULL,
  `name` VARCHAR(255) DEFAULT NULL,
  `age` INT(11) DEFAULT NULL,
  `date` DATETIME DEFAULT NULL,
  `sex` INT(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO employee VALUES(100,'小伦',43,'2021-01-20','0');
INSERT INTO employee VALUES(200,'俊杰',48,'2021-01-21','0');
INSERT INTO employee VALUES(300,'紫琪',36,'2020-01-21','1');
INSERT INTO employee VALUES(400,'立红',32,'2020-01-21','0');
INSERT INTO employee VALUES(500,'易迅',37,'2020-01-21','1');
INSERT INTO employee VALUES(600,'小军',49,'2021-01-21','0');
INSERT INTO employee VALUES(700,'小燕',28,'2021-01-21','1');
  • 查看 SQL 语句的执行计划
1
EXPLAIN SELECT * FROM employee WHERE age = 32;
1
2
3
4
5
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employee | NULL       | ref  | idx_age       | idx_age | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
  • 回表操作的分析

  • (1) 搜索 idx_age 索引树（非聚簇索引），将磁盘块 1 加载到内存，由于 32 < 37，搜索左路分支，加载到磁盘块 2。
  • (2) 将磁盘块 2 加载到内存中，在内存中继续遍历数据，找到 age = 32 的记录，取得 id = 400 后，回到 id 的主键索引树（聚簇索引）中获取完整的数据记录。

  • (3) 搜索 id 的主键索引树（聚簇索引），将磁盘块 1 加载内存，在内存遍历找到 id = 400 的记录，但是 B + 树索引非叶子节点是不保存数据的。因此，索引会继续搜索 id = 400 的右分支，加载到磁盘块 3。

  • (4) 将磁盘块 3 加载到内存中，在内存中继续遍历数据，找到 id = 400 的记录，拿到第 4 行 (R4) 这一行的数据，查询成功并返回结果。

  • 最终的结论

在 idx_age 二级索引树（非聚簇索引）中找到对应的主键 id 后，再根据主键 id 回到主键索引树（聚簇索引）搜索的过程，就称为 “回表”。

底层原理分析

由于 B+ 树的非叶子节点只存储键值信息和子节点的引用，不存储数据。假设每个磁盘块能存储 4 个键值及指针信息，则转换成 B+ 树后，其结构如下图所示：

在 B+ 树中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储 Key 值信息，这样可以大大加大每个节点存储的 Key 值数量，降低 B+ 树的高度（搜索深度）。

B+ 树算法的特性

  • 通过继承了 B 树的特征，B+ 树相比 B 树，新增了叶子节点与非叶子节点的关系。
  • 在根节点和枝节点（非叶子节点）中，只包含键值和子节点的引用，不包含数据。然而，叶子节点中包含了键值和数据。
  • B+ 树可以通过非叶子节点查询叶子节点来获取对应的数据。非叶子节点之间使用链表进行连接。叶子节点是顺序排序的，并且相邻节点有顺序引用的关系。

索引的底层原理

扩展阅读

索引的实现原理

索引是通过一种数据结构，将数据库表中的某一列或多列的值进行预先排序和存储，以加快对这些列的搜索和筛选操作。通过索引，数据库管理系统不需要逐行扫描整个表来查找数据，而是可以通过快速定位索引来访问特定的数据行，从而提高检索速度。常见的索引数据结构包括 B 树、B+ 树和哈希等。

    1. 以 MySQL 为例，InnoDB 存储引擎使用了 B+ 树实现索引，在索引查找时实现了 log(n) 的时间复杂度
    1. 聚族索引记录了主键 ID 与完整的数据，非聚族索引记录了主键 ID + 索引字段
    1. 在聚族索引的叶子节点中记录了完整的数据，而非聚族索引的叶子节点记录的是主键以及索引字段，如果需要完整数据的话，就需要回表操作，即通过主键去聚族素引中再次查找完整的数据
    1. 索引的叶子节点以有序链表的形式存储，方便顺序查找和排序

提示

MySOL 官方对索引的定义为：索引（Index）是帮助 MySOL 高效获取数据的数据结构。简而言之，索引是排好序、可快速查找的数据结构。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。索引是一种特殊的文件（InnoDB 数据表上的索引是表空间的一个组成部分），包含了对数据表里所有记录的引用指针。

B 树与 B+ 树的区别

B 树与 B+ 树的区别如下：

    1. 叶子节点的结构：
    • B 树：叶子节点之间没有链表结构。
    • B+ 树：叶子节点通过链表连接，便于顺序访问。
    1. 数据存储位置：
    • B 树：数据记录存储在所有节点上。
    • B+ 树：数据记录只存储在叶子节点上，非叶子节点仅用于索引。
    1. 查询效率：
    • B 树：查询时可能在非叶子节点找到数据，搜索路径较短。
    • B+ 树：所有查询都必须到达叶子节点，但叶子节点之间的有序链表结构提高了范围查询和顺序访问的效率。
    1. 范围查询：
    • B 树：范围查询不如 B+ 树高效，叶子节点之间没有链表结构。
    • B+ 树：范围查询更高效，因为叶子节点按顺序链表连接。

综上所述，B 树和 B+ 树各有优劣。B 树在查找单个键值时可能效率更高，而 B+ 树在范围查询和顺序访问方面表现更佳，因此在数据库系统中，B+ 树更为常用。

建议 InnoDB 表必须创建主键

面试题

为什么建议 InnoDB 表必须创建主键，并且推荐使用整型的自增主键？

  • 如果在创建表时不设置主键，InnoDB 会自动从第一列开始筛选一列数据不重复的列（拥有唯一索引的列）作为主键；如果找不到这样的列，就会自动创建一个隐藏的列（row-id）做为主键，这会大大增加 MySQL 的工作量，所以在创建 InnoDB 表时必须设置主键。
  • 使用整型的字段做为主键，一方面在数据比较时不需要进行转换，另一方面存储也比较节省空间。那为什么要强调主键自增呢？如果主键 ID 是无序的，那么很有可能新插入的值会导致当前节点分裂，此时 MySQL 不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过 OPTIMIZE TABLE 来重建表并优化填充页面。反之，如果每次插入有序，那就会在当前页后面连续写入，写不下就会重新分配一个节点，内存都是连续的，这样效率自然也就最高了。

MySQL 查询只能使用一个索引吗

MySQL 5.0 之前，SQL 查询只能使用一个索引，所以要合理使用组合索引，而不是单列索引。与其说是 “数据库查询只能用到一个索引”，倒不如说和全表扫描、只使用一个索引的查询速度比起来，去分析多个索引二叉树更加耗费时间，所以绝大多数情况下数据库都是用一个索引。特别注意：从 MySQL 5.1 开始，引入了索引合并优化技术，对同一个表可以使用多个索引分别进行条件扫描。

1
select count(1) from table1 where column1 = 1 and column2 = 'foo' and column3 = 'bar';

例如上面的语句，当数据库有 N 个索引并且查询中分别都要用上它们的情况下：查询优化器（用于生成执行计划）需要进行 N 次主二叉树查找（这里主二叉树的意思是最外层的索引节点），此时的查找流程大概是：查出第一条 column1 主二叉树等于 1 的值，然后去第二条 column2 主二叉树查出 foo 的值并且当前行的 coumn1 必须等于 1，最后去 column3 主二叉树查找 bar 的值并且 column1 必须等于 1 和 column2 必须等于 foo。如果这样的流程被查询优化器执行一遍，就算不死也半条命了，查询优化器可等不及把以上计划都执行一遍，贪婪算法（最近邻居算法）可不允许这种情况的发生。所以当遇到上面的语句，数据库只要用到第一个筛选列的索引（column1），就会直接去进行表扫描了。所以与其说是数据库只支持一条查询语句只使用一个索引，倒不如说 N 个独立索引同时在一条语句使用的开销比只使用一个索引还要大。最佳推荐是使用 index(column1, column2, column3） 这种组合索引，此组合索引可以把 B+Tree 结构的优势发挥得淋漓尽致。一条主二叉树（column=1），查询到（column=1）节点后基于当前节点进行二级二叉树（column2=foo）的查询，在二级二叉树查询到（column2=foo）后，去三级二叉树（column3=bar）查找，这样查询效率会高跟多。