MySQL 索引和执行计划

索引的作用是优化查询。

一、索引分类（按算法）

1. B 树

家族

B-Tree：上层节点保存下层节点的最小值
B+：对范围查找优化
B*（默认）：对范围查找进一步优化

2. R 树

3. Hash

自适应 Hash 索引

4. FullText

全文索引

5. GIS

类似 MongoDB

二、索引分类（按功能）

1. 辅助索引

创建过程
- 将索引列的值取出并排序
- 将排序后的数据均匀存储到索引的叶子节点（叶子节点存储对应 id）
- 生成枝节点和根节点
细分
- 单列辅助索引
- 联合索引（覆盖索引）
- 唯一索引

2. 聚集索引

创建过程
- 自动将主键作为聚集索引列（无主键则选唯一键，均无则生成隐藏主键）
- 存储数据时按顺序存储数据行
- 将源表的数据页作为叶子节点，提取索引列向上生成枝节点和根节点

3. 区别

任何列都可创建辅助索引；聚集索引只能有一个列
辅助索引的叶子节点只存储索引列的有序值和对应 id；聚集索引的叶子节点存储有序的整行数据
MySQL 的表数据是聚集索引组织表

三、索引树高度

1. 高度要求

越低越好，3-4 层最佳

2. 影响因素

数据行数
- 分表（partition）：目前用得较少
- 分片（分布式架构）
字段长度
- 尽量选择字符长度短的列作为索引列
- 采用前缀索引
数据类型
- char/varchar：char 创建索引时占用更大空间
- enum：占用更少空间

四、索引操作

1. 创建索引

单列辅助索引

alter table user add index idx_password(password);

Note：会导致锁表。

查询 SQL：

select * from user where password = '954002';

联合索引

alter table user add index idx_user_password(user, password);

查看：

mysql> show index from user;
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY           |            1 | id          | A         |    16295990 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | idx_user_password |            1 | user        | A         |    10324053 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | idx_user_password |            2 | password    | A         |    16295990 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql> desc user;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| user     | varchar(255) | NO   | MUL | NULL    |                |
| password | varchar(255) | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

唯一索引

alter table user add unique index uidx_user(user);

查询 SQL：

select * from user where id = '145324';

前缀索引
```
alter table user add index idx_user(user(5));
```
Note：使用 user 列的前 5 个字符创建索引。

2. 查看索引

desc
key 列的值表明索引类型：

desc user;

输出结果：

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| user     | varchar(255) | NO   |     | NULL    |                |
| password | varchar(255) | NO   | MUL | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

PRI：主键索引
MUL：辅助索引
UNI：唯一索引

show

show index from user;

输出结果：

+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY      |            1 | id          | A         |    16295990 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | idx_password |            1 | password    | A         |     1002924 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

3. 删除索引

alter table user drop index idx_password;

五、索引规范

1. 创建索引规范

建表必须有主键，一般为无关列且自增长
索引列最好是经常作为 where 条件的列（包括排序条件 order by、group by、union 等）
推荐使用唯一值多的列作为联合索引的第一位
列值长度较长的列，建议使用索引前缀
降低索引数量（不建无用索引，清理不常用索引）
索引维护要避开业务高峰期
小表不建索引

2. 不走索引的情况

没有查询条件或没有建索引

mysql> desc select * from user2 limit 5;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | user2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9682283 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from user2 where qq = '234234234';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | user2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9682283 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

当查询扫描的数据超过全表的 25%，优化器会选择全表扫描（固定百分比不是唯一的判定条件）
索引失效（统计信息不准）：同一 SQL 语句突然变慢可能是此原因

索引列的查询条件上使用了函数或运算（+、-、*、/、!）

mysql> desc select * from user2 where user + 1 = '34234324324';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | user2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9682283 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from user2 where floor(user) = '34234324324';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | user2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9682283 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

索引列上有隐式转换（如字符串和数字的转换）

mysql> desc select * from user2 where user = 34234324324;
+----+-------------+-------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys     | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | user2 | NULL       | ALL  | idx_user_password | NULL | NULL    | NULL | 9682283 |    10.00 | Using where |
+----+-------------+-------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

对于辅助索引，索引列中包含 !=、<> 和 not in 时不走索引（只有主键索引的 != 会走索引）

六、压力测试

在创建索引前后进行压力测试，对比查询效率提升。

1. 模拟环境

create table user(
id int not null auto_increment primary key,
user varchar(255) not null,
password varchar(255) not null
) engine=InnoDB;

insert into user(user, password) values('张三', '123456');

insert into user(
 user
 ,password
 )
select
 md5(rand())
 ,floor(rand(100)*1000000)
from
 user;

2. 测试

mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='test' \
--query="select * from user where user = '6518617c3a54d10028287abec8db13d2' and password = '694125';" engine=innodb \
--number-of-queries=2000 -uroot -proot -verbose

Note：创建索引前后分别记录查询时长，可看到明显性能提升。

七、执行计划

1. 查看执行计划

目的是进行 SQL 优化。

mysql> explain select * from user limit 5;
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16295990 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------+
1 row in set, 1 warning (0.02 sec)

mysql> desc select * from user where user = '6518617c3a54d10028287abec8db13d2' and password = '694125';
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16295990 |     1.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

方式：desc + SQL 语句或 explain + SQL 语句

2. 分析执行计划

type（查询类型）

全表扫描（ALL）

mysql> explain select * from user limit 5;
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16295990 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------+

索引扫描

性能排序：index（全索引扫描）< range（索引范围扫描）< ref（辅助索引等值查询）< eq_ref（联表查询时，主键或唯一键的等值查询）< const（主键或唯一键的等值查询）< NULL（所查找的数据在表中不存在）

index（全索引扫描）：遍历整个索引树

mysql> desc select * from user;
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key               | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | idx_user_password | 2044    | NULL | 16295990 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+----------+----------+-------------+

range（索引范围扫描）：用于 >、<、>=、<=、between and、or、in、like 等范围查询（>、like 性能优于 or、in；in 一般改写成 union all）

mysql> desc select * from user where user like '000005561e686de93b46%';
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | user  | NULL       | range | idx_user_password | idx_user_password | 1022    | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------+----------+--------------------------+

in 与 union all 对比：

mysql> desc select * from user where user in ('3242342werwerwe','000003f8fb93a54bf1d37d2b23a5cdef');
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | user  | NULL       | range | idx_user_password | idx_user_password | 1022    | NULL |    3 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc
    -> select * from user where user = '3242342werwerwe' 
    -> union all
    -> select * from user where user = '000003f8fb93a54bf1d37d2b23a5cdef'
    -> ;
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | user  | NULL       | ref  | idx_user_password | idx_user_password | 1022    | const |    1 |   100.00 | Using index |
|  2 | UNION       | user  | NULL       | ref  | idx_user_password | idx_user_password | 1022    | const |    2 |   100.00 | Using index |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

Note：in 使用 range 索引类型，union all 使用 ref 索引类型。

ref（辅助索引等值查询）

mysql> desc select * from user where user = '000003f8fb93a54bf1d37d2b23a5cdef';
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_user_password | idx_user_password | 1022    | const |    2 |   100.00 | Using index |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+

eq_ref（联表查询时，主键或唯一键的等值查询）：多表连接时，子表使用主键列或唯一列作为连接条件（一般小表作为驱动表）

mysql> desc
    -> select * 
    -> from user t1
    -> left join user t2
    -> on t1.id = t2.id
    -> ;
+----+-------------+-------+------------+--------+---------------+-------------------+---------+------------+----------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key               | key_len | ref        | rows     | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+-------------------+---------+------------+----------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index  | NULL          | idx_user_password | 2044    | NULL       | 16295990 |   100.00 | Using index |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY           | 4       | test.t1.id |        1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+-------------------+---------+------------+----------+----------+-------------+

const（主键或唯一键的等值查询）

mysql> desc select * from user where id = '2342343';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

NULL（所查找的数据在表中不存在）：索引中无对应数据，无需到叶子节点查找

mysql> desc select * from user where id = '0';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+

possible_keys：可能用到的索引
key：实际选择的索引
key_len（索引覆盖长度）：可判断联合索引中哪些字段被使用（联合索引覆盖越全越好）

假设字符集为 utf8mb4（NULL 占用 1 个标志位，若字段允许 NULL）：
- varchar(10)：1（NULL） + 4×10 + 2 = 43
- char(10)：1（NULL） + 4×10 = 41
- int：1（NULL） + 4 = 5

3. 联合索引的执行计划

假设表结构为 t1(id, k1, k2, k3, k4)，联合索引为 idx(k1, k2, k3, k4)。

当 where 查询有多个等值条件（如 where k1 = ‘xx’ and k2 = ‘xx’ and k3 = ‘xx’）：联合索引顺序与 where 条件顺序无关，优化器会自动排序；若条件不连续（如 k1, k3, k4），仅走 k1 索引；建索引时，唯一值多的列放第一位。
若 where 条件缺少最左列（如仅 k2 = ‘xx’）：不走联合索引（例外：表中所有列均在联合索引中时，每个列的查询条件会走索引）。
若 where 条件有范围查询（>、< 等）或模糊匹配（like）：建索引时，等值条件放前，不等值条件放后。

多子句查询（如 select * from user where k1=‘xx’ order by k2）：应创建索引 idx(k1, k2)

mysql> desc select * from user2 where user = '000005561e686de93b4681562b951f2b' order by qq2;
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | user2 | NULL       | ref  | idx_user_password | idx_user_password | 1022    | const |    2 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+---------------------------------------+

Extra: Using filesort：说明排序条件未合理应用索引（关注 key_len），常见于 order by、group by、distinct、union 等场景。

八、数据库性能优化

1. 应急性的慢（突然夯住，资源耗尽）

获取导致卡住的语句

show processlist;

输出结果：

+-----+------+-----------+------+---------+------+--------------+--------------------------------------------------------------------------------------+
| Id  | User | Host      | db   | Command | Time | State        | Info                                                                                 |
+-----+------+-----------+------+---------+------+--------------+--------------------------------------------------------------------------------------+
|   6 | root | localhost | test | Query   |    0 | starting     | show processlist                                                                     |
| 413 | root | localhost | NULL | Sleep   |    4 |              | NULL                                                                                 |
| 414 | root | localhost | test | Query   |    4 | Sending data | select * from user2 where qq = '6518617c3a54d10028287abec8db13d2' and qq2 = '694125' |
| 415 | root | localhost | test | Query   |    4 | Sending data | select * from user2 where qq = '6518617c3a54d10028287abec8db13d2' and qq2 = '694125' |
| 416 | root | localhost | test | Query   |    4 | Sending data | select * from user2 where qq = '6518617c3a54d10028287abec8db13d2' and qq2 = '694125' |
| 417 | root | localhost | test | Query   |    4 | Sending data | select * from user2 where qq = '6518617c3a54d10028287abec8db13d2' and qq2 = '694125' |
| 418 | root | localhost | test | Query   |    4 | Sending data | select * from user2 where qq = '6518617c3a54d10028287abec8db13d2' and qq2 = '694125' |
| 419 | root | localhost | test | Query   |    4 | Sending data | select * from user2 where qq = '6518617c3a54d10028287abec8db13d2' and qq2 = '694125' |
| 420 | root | localhost | test | Query   |    4 | Sending data | select * from user2 where qq = '6518617c3a54d10028287abec8db13d2' and qq2 = '694125' |
| 421 | root | localhost | test | Query   |    4 | Sending data | select * from user2 where qq = '6518617c3a54d10028287abec8db13d2' and qq2 = '694125' |
| 422 | root | localhost | test | Query   |    4 | Sending data | select * from user2 where qq = '6518617c3a54d10028287abec8db13d2' and qq2 = '694125' |
| 423 | root | localhost | test | Query   |    4 | Sending data | select * from user2 where qq = '6518617c3a54d10028287abec8db13d2' and qq2 = '694125' |
| 424 | root | localhost | test | Query   |    4 | Sending data | select * from user2 where qq = '6518617c3a54d10028287abec8db13d2' and qq2 = '694125' |
+-----+------+-----------+------+---------+------+--------------+--------------------------------------------------------------------------------------+

分析 SQL 执行计划（是否走索引、索引类型）
创建索引或优化 SQL

2. 一段时间慢（持续性的）

分析慢日志
分析 SQL 执行计划
创建索引或优化 SQL

红炉点雪