MySQL 索引使用规则与 SQL 优化

五、索引的使用规则（续）

1. SQL 提示

多个索引到底用哪个？可以人为指定。

# 优先级
# force index > use index > 优化器自动选择
# use index 仅建议，优化器可能忽略
# force index 强制使用，若索引不存在则报错


# ignore index 使用频率极低，生产环境几乎不用

# 原因：
# 1. 索引创建是为了加速查询，主动忽略索引违背设计初衷
# 2. 若某个索引确实干扰优化器决策，更合理的做法是直接删除该索引
# 3. ignore index 仅对单条 sql 生效，维护成本高，容易遗漏

# sql 提示：在 sql 语句中加入人为提示，影响优化器选择索引

# 1. use index（建议使用指定索引，优化器不一定采纳）
explain select * from tb_user use index(idx_user_pro_age_sta) where profession = '软件工程';

# 2. ignore index（忽略指定索引）
explain select * from tb_user ignore index(idx_user_pro_age_sta) where profession = '软件工程';

# 3. force index（强制使用指定索引，优化器必须采纳）
explain select * from tb_user force index(idx_user_pro_age_sta) where profession = '软件工程';


# 使用场景
# 场景1：优化器选错索引（统计信息偏差），使用 force index 强制纠正
explain select * from tb_user force index(idx_profession) where profession = '软件工程' and age = 23;

# 场景2：测试某个索引是否有效，使用 use index 观察执行计划变化
explain select * from tb_user use index(idx_user_pro_age_sta) where age = 23;

# 场景3：验证去掉某个索引后的执行计划变化，使用 ignore index
explain select * from tb_user ignore index(idx_user_pro_age_sta) where profession = '软件工程';

2. 覆盖索引和回表

回表就是二级索引查完了需要去聚集索引再查一次。要搞清楚回表和扫表的区别。

# 尽量使用覆盖索引，减少 select *
# 覆盖索引 = 查询所需字段全部在索引中，无需回表

# 索引定义：idx_user_pro_age_sta (profession, age, status)

# 场景1：全部字段在索引中，覆盖索引生效，不回表
explain select id, profession, age, status from tb_user 
where profession = '软件工程' and age = 31 and status = '1';
# 查询字段：id（主键，二级索引叶子存储）、profession、age、status（索引列）
# extra：using index

# 场景2：部分字段不在索引中，需要回表
explain select id, profession, age, status, name from tb_user 
where profession = '软件工程' and age = 31 and status = '0';
# 查询字段：name 不在索引中
# extra：using index condition（回表取 name）

# 场景3：select * 全字段，回表
explain select * from tb_user 
where profession = '软件工程' and age = 31 and status = '1';
# 查询所有字段，索引无法覆盖
# extra：using where（回表后过滤）

# extra 字段含义
# using index          → 覆盖索引，无需回表（最优）
# using index condition → 索引条件下推（icp），部分过滤在索引层，仍需回表
# using where          → 回表后过滤（最差）

# 优化建议
# 高频查询语句中，避免 select *，只查询索引中包含的字段
# 若业务必须查 name、phone 等非索引字段，考虑建覆盖索引或调整索引字段顺序

二级索引的叶子节点存储了主键值和索引字段数据。第三个 sql 查了二级索引没有的字段，而且使用了二级索引需要回表：

3. 前缀索引

值很大的字段（比如存了 txt、长 varchar）会让索引变得很大、浪费磁盘 IO、查询效率下降，这种就用前缀索引，只取字符串前 n 个字符来建。

# 前缀索引：只取字符串前 n 个字符建立索引，减少索引体积

# 语法比起之前仅仅多了 截取长度：指定字符串的前 n 个字符建立索引
create index 索引名 on 表名(字段名(截取长度));

# 选择性（selectivity）= 不重复的索引值数量（cardinality）/ 总行数
# 值越接近 1，索引区分度越高，查询效率越好

# 示例：tb_user 表共 24 行数据
# 字段 email 全列唯一值 24 个
select count(distinct email) / count(*) from tb_user;    # 24/24 = 1.0000（唯一索引级别）

# 字段 profession 取值较少（软件工程、工程造价、通讯工程等）
select count(distinct profession) / count(*) from tb_user;  # 6/24 = 0.25（区分度低）


# 前缀索引的选择性计算
# 取 email 前 5 个字符作为前缀
select 
    count(distinct email) / count(*) as full,              # 1.0000
    count(distinct substring(email, 1, 3)) / count(*) as prefix_3,  # 0.5833
    count(distinct substring(email, 1, 5)) / count(*) as prefix_5,  # 0.8333
    count(distinct substring(email, 1, 7)) / count(*) as prefix_7,  # 0.9583
from tb_user;

# 结果解读
# prefix_3 = 0.58 → 前3个字符重复率高，区分度差
# prefix_5 = 0.83 → 接近完整字段，区分度可接受
# prefix_7 = 0.95 → 更接近完整字段，但索引体积比 prefix_5 大
# 选择 prefix_5 作为平衡点（区分度足够，体积较小）


# 唯一索引选择性 = 1
# 原因：唯一索引保证字段值全局不重复
# 每个值只匹配一行，查询时 b+tree 定位到唯一值即停止，效率最高

前缀索引的结构和查找流程：

4. 单列索引和联合索引

相比单列索引（普通、唯一索引），更推荐使用联合索引，因为联合索引回表次数少。

联合索引也属于二级索引，可能需要回表：

联合索引 (phone, name) 的结构，先按 phone 再按 name 排序

根据最左前缀法则，最左一列（phone）必须存在才能使用联合索引，所以创建联合索引时要注意顺序，一般按业务调用查询顺序来即可。

# 联合索引 (phone, age, status)
# 最左列为 phone

# 生效（phone 存在）
where phone = '17799990000'
where phone = '17799990000' and age = 23
where phone = '17799990000' and age = 23 and status = 1

# 失效（phone 不存在）
where age = 23
where age = 23 and status = 1
where status = 1

5. 设计原则

针对数据量较大，且查询比较频繁的表建立索引
针对常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，索引的效率越高
如果是字符串类型的字段，字段的长度较长，可以针对字段的特点建立前缀索引
尽量使用联合索引，减少单列索引。查询时联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
要控制索引的数量，索引并不是多多益善，索引越多维护索引结构的代价也就越大，会影响增删改的效率
如果索引列不能存储 NULL 值，请在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含 NULL 值时，它可以更好地确定哪个索引最有效地用于查询

六、SQL 优化

这部分要求能看懂、能听懂，重点了解 update。

1. 插入数据

能批量插入就批量，最好不超过 1000 条。

# insert 优化

# 批量插入
insert into tb_test values(1,'tom'),(2,'cat'),(3,'jerry');

# 手动提交事务
start transaction;
insert into tb_test values(1,'tom'),(2,'cat'),(3,'jerry');
insert into tb_test values(4,'tom'),(5,'cat'),(6,'jerry');
insert into tb_test values(7,'tom'),(8,'cat'),(9,'jerry');
commit;

# 主键顺序插入
# 主键乱序插入
# 8, 1, 9, 21, 88, 2, 4, 15, 89, 5, 7, 3

# 主键顺序插入
# 1, 2, 3, 4, 5, 7, 8, 9, 15, 21, 88, 89

大批量数据还可以用 load，它直接把符合一定规则的本地文件加载进数据库：

# 客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u root -p

# 设置全局参数 local_infile 为 1，开启从本地加载文件导入数据的开关
set global local_infile = 1;

# 然后建好表，字段数量和文件的对应
# 执行 load 指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table `tb_user` fields terminated by ',' lines terminated by '\n';
# fields terminated by 表示被加载文件 每个字段 用什么分隔的
# lines terminated by 每行用什么分隔

# 格式化
load data local infile '/root/sql1.log' into table `tb_user` 
fields terminated by ',' lines terminated by '\n';

主键顺序插入的性能高于乱序插入，这就要求原始文件的主键格式最好是顺序的。

2. 主键优化

页分裂：上面提到的乱序插入会导致性能降低，这是因为叶子节点存储的主键和对应的行数据是按照主键顺序连续存储的，乱序插入会导致引擎不断地调整这个顺序。

页合并：当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flagged）为删除，并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到 merge_threshold（默认为页的 50%），InnoDB 会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

删除只是把记录标记为 flagged，页内删除达到 MERGE_THRESHOLD 时寻找相邻页合并

merge_threshold 可以自己设置（在建表、建索引时指定）。

主键设计原则：

二级索引下面存放了主键，如果主键太长，索引就会翻好几倍大小，很消耗磁盘 IO（数据在磁盘和内存之间传输的操作）。生成的 uid 之类的不能做主键，这是无序的（页分裂），而且太长。另外尽量不修改主键。

3. order by 优化

先说 explain 的 extra 列：

using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作（所有不是通过索引直接返回排序结果的排序都叫 filesort 排序）
using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index（不需要额外排序，操作效率高）

所以要优化 order by 语句，尽量优化成 using index。下面的 sql 要满足覆盖索引，否则一定是 using filesort：

# 没有创建索引时，根据 age, phone 进行排序
explain select id, age, phone from tb_user order by age, phone;
# 没有给age、phone建立索引时为using filesort


# 创建索引
create index idx_user_age_phone_aa on tb_user(age, phone);

# 创建索引后，根据 age, phone 进行升序排序
explain select id, age, phone from tb_user order by age, phone;
# 建立索引后都是 using index

# 如果是order by phone,age;
# 违背最左前缀法则，出现 using filesort
# age升序phone降序也是相同的情况
# 因为asc才会用到索引，创建索引的值是从小到大的
# 优化：可以针对phone创建索引，指定顺序满足业务查询
create index idx_user_age_pho_ad on tb_user(age asc, phone desc);


# 创建索引后，根据 age, phone 进行降序排序
explain select id, age, phone from tb_user 
order by age desc, phone desc;
# 只有反向扫描索引

和索引方向一致就走 using index，不一致但是有索引就索引反向扫描（符合最左前缀法则、覆盖索引的情况）。

联合索引升降序不同的结构：

联合索引 age asc / phone asc 和 age asc / phone desc 两种结构的对比

4. group by 优化

核心是最左前缀 + 索引覆盖。

# 删除掉目前的联合索引 idx_user_pro_age_sta
# 为了看到有无索引的差别，先删了索引
drop index idx_user_pro_age_sta on tb_user;

# 执行分组操作，根据 profession 字段分组
explain select profession, count(*) from tb_user group by profession;
# 此时发现extra列：使用临时表，性能较低

# 创建索引（能联合就联合）
create index idx_user_pro_age_sta on tb_user(profession, age, status);
# 此时是 using index

# 执行分组操作，根据 profession 字段分组
explain select profession, count(*) from tb_user group by profession;

# 执行分组操作，根据 profession, age 字段分组
explain select profession, age, count(*) from tb_user group by profession, age;
# 上述都是 using index

# 先对profession过滤再按age排序也满足最左前缀法则
explain select age,count(*) from tb_user where profession = '软件工程' group by age;
# using index

5. limit 优化

limit n,m 可以理解为跳过前 n 条，查 m 条。当分页查询的数据过多，n 越大查得越慢。所以数据量很大（百万级）就需要优化 limit。

覆盖索引 + 子查询：

# 先使用覆盖索引查目标数据的主键
select id from tb_sku order by id limit 9000000,10;
# 返回id列，此时列子查询是用不了的：mysql不支持这个语法
select * from tb_sku where id in 
(select id from tb_sku order by id limit 9000000,10);

# 用 表子查询：把id列看作表
select s.* from 
tb_sku s,
(select id from tb_sku order by id limit 9000000,10) a1
where s.id = a1.id

limit 不能直接写在 in 的子查询里（MySQL 不支持这个语法），所以要改成把子查询当表来 join，也就是最后那个写法。

6. count 函数优化

**暂时无法优化，count(*) 性能是最高的。**

select count(*) from tb_sku;
# myisam 引擎把一个表的总行数存在了磁盘上（没有where等sql选项）
# 因此执行 count(*) 的时候会直接返回这个数，效率很高
# innodb 引擎执行 count(*) 的时候
# 需要把数据一行一行地从引擎里面读出来，然后累积计数

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加，最后返回累计值。

count 的用法和含义：count(*)、count(主键)、count(字段)、count(1)

四种写法的执行方式：

count(主键)、count(字段)、count(1)、count(*) 在 InnoDB 里的差别

count(主键)：InnoDB 引擎会遍历整张表，把每一行的主键 id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为 null）
count(字段)：没有 not null 约束的话，InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为 null，不为 null 才计数累加；有 not null 约束的话，InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加
count(1)：InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字"1"进去，直接按行进行累加
**count(*)**：InnoDB 引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加

按效率排序的话，**count(字段) < count(主键 id) < count(1) ≈ count(\*)**，所以尽量使用 count(*)。

7. update 优化（避免行锁升级为表锁）

这里用到了 锁 的内容，先理解大概意思。

update 的 where 条件必须有索引，否则锁全表，降低并发性能。

索引失效也会锁表，因为 InnoDB 是针对索引来看的。

假设有下面这样一个表：

# 开启两个会话执行
begin;
begin;
# 在其中一个会话 update
update course set name = 'javaEE' where id = 1;
# 此时第一行会被锁住，直到事务被提交

# 在另一个会话执行
update course set name = 'Kafka' where id = 4;
# 执行成功

# 两个会话都 commit

此时的表是这样的：

# 再次开启两个事务
begin;
begin;
# 其中一个会话执行
update course set name = 'SpringBoot' where name = 'PHP';
# 此时锁住了第二行？

# 在另一会话执行
update course set name = 'Kafka2' where id = 4;
# 此时会执行失败
# 因为另一会话的 update 使用了没有索引的字段 name，行锁变为表锁

# 另一会话 commit 之后才能执行成功


# 为 name 建索引重复操作
create index idx_course_name on course(name);
begin;
begin;
update course set name = 'Spring' where name = 'SpringBoot';
update course set name = 'Cloud' where id = 4;
# 执行成功，两边再 commit

8. 小结

对于 SQL 的优化绝大部分都是对索引的优化，所以索引很重要。

SQL 优化小结：插入数据、主键、order by、group by、limit、count、update

#MySQL #索引 #SQL优化 #慢查询

← 上一页MySQL 索引结构与执行计划分析下一页 →MySQL 锁机制与加锁实操