我要评论mysql索引优化是提升数据库性能的关键手段，一个合理的索引设计和使用策略，往往能将查询速度提升几十倍甚至上百倍。
然而，索引优化并不简单，既需要扎实的理论基础，也需要丰富的实战经验。
本文总结了21个mysql索引优化的实战技巧，从索引选择、设计到维护、监控的全生命周期，帮助你解决日常开发中的索引性能问题。
基础知识回顾
在具体介绍前，让我们先简单回顾索引的基础知识：
mysql常用的索引类型包括：主键索引、唯一索引、普通索引、联合索引、全文索引等。
其中最常用的b+树索引，具有以下特点：
- 非叶子节点只存储键值信息
- 所有叶子节点包含了完整的数据记录
- 叶子节点通过指针连接，方便范围查询
- 所有节点按键值大小排序
理解这些基础对于后续优化至关重要。接下来，让我们进入正题。
一、索引设计优化
1. 遵循最左匹配原则，合理设计联合索引顺序
联合索引的顺序直接影响其使用效率。mysql会从左到右依次使用索引列，如果中间某列没有使用，则后面的列也无法使用索引。
错误示例：
-- 创建索引(name, age, city) create index idx_user_name_age_city on user(name, age, city); -- 以下查询无法充分利用索引 select * from user where age = 25 and city = 'beijing'; -- name列缺失，只能全表扫描 select * from user where name = 'tom' and city = 'beijing'; -- 中间age列缺失，city无法使用索引
优化方法：
1. 将选择性高的列放在前面（选择性 = 不重复值 / 总记录数）
2. 将常用于条件查询的列放在前面
3. 考虑范围查询的列放在最后
-- 假设选择性：city < name < age create index idx_user_name_age_city on user(name, age, city); -- 充分利用索引的查询 select * from user where name = 'tom' and age = 25; select * from user where name = 'tom' and age = 25 and city = 'beijing';
2. 利用覆盖索引避免回表查询
回表操作是指通过索引找到对应的行记录指针，再通过指针去查询完整记录的过程。
如果查询只需要返回索引包含的列，则可以避免回表，这称为覆盖索引。
优化前：
-- 创建普通索引 create index idx_user_name on user(name); -- 需要回表查询 select id, name, age, city from user where name = 'tom';
优化后：
-- 创建包含所需字段的索引 create index idx_user_name_age_city on user(name, age, city); -- 使用覆盖索引，无需回表 select name, age, city from user where name = 'tom';
3. 针对字符串列使用前缀索引
对于char和varchar类型的列，如果整列长度较大，可以只索引开头的部分字符，这样可以大幅减少索引占用空间，提高索引效率。
优化方法：
-- 假设product_desc是较长的产品描述文本 create index idx_product_desc on product(product_desc(50));
如何确定前缀长度？可以通过计算选择性来确定：
-- 计算不同前缀长度的选择性
select
count(distinct left(product_desc, 10)) / count(*) as sel_10,
count(distinct left(product_desc, 20)) / count(*) as sel_20,
count(distinct left(product_desc, 30)) / count(*) as sel_30,
count(distinct left(product_desc, 40)) / count(*) as sel_40,
count(distinct left(product_desc, 50)) / count(*) as sel_50,
count(distinct product_desc) / count(*) as sel_full
from product;选择一个接近完整列选择性的前缀长度即可。
注意事项： 使用前缀索引后，无法使用该索引做order by或group by，也无法使用覆盖索引。
4. 合理使用复合索引替代多个单列索引
多个单列索引在多条件查询时，mysql只会选择一个索引。而复合索引可以同时满足多个条件的查询需求。
优化前：
-- 单独创建两个索引 create index idx_user_age on user(age); create index idx_user_city on user(city); -- mysql通常只会选择一个索引 select * from user where age = 25 and city = 'beijing';
优化后：
-- 创建一个复合索引 create index idx_user_age_city on user(age, city); -- 可以同时使用age和city条件 select * from user where age = 25 and city = 'beijing';
5. 使用前缀索引优化模糊查询的左匹配
like语句使用通配符前缀（如'%abc'）会导致索引失效。但对于右匹配模式（如'abc%'），索引仍然有效。
可以使用索引的查询：
-- 可以使用索引 select * from products where product_name like 'iphone%';
无法使用索引的查询：
-- 无法使用索引 select * from products where product_name like '%iphone%';
优化方法：对于需要搜索包含某个关键词的记录，可以考虑全文索引或搜索引擎。对于简单场景，也可以通过字段冗余解决：
-- 添加一个反转字段
alter table products add product_name_reversed varchar(255);
-- 触发器维护反转值, 此处为了简单表示整体实现思路, 实际通常在代码中进行反转值赋值
delimiter //
create trigger product_insert before insert on products
for each row
begin
set new.product_name_reversed = reverse(new.product_name);
end; //
delimiter ;
-- 创建反转字段的索引
create index idx_product_name_rev on products(product_name_reversed);
-- 搜索以'phone'结尾的产品
select * from products
where product_name_reversed like concat(reverse('phone'), '%');二、索引使用优化
6. 避免在where子句中对字段进行函数运算
在字段上使用函数会导致索引失效，应该把运算转移到值上。
错误用法：
-- 索引失效 select * from orders where year(create_time) = 2023;
优化方法：
-- 可以使用索引 select * from orders where create_time >= '2023-01-01' and create_time < '2024-01-01';
7. 避免隐式类型转换导致索引失效
mysql在进行查询时，如果字段类型与条件值类型不匹配，会进行隐式类型转换，可能导致索引失效。
错误用法：
-- user_id是varchar类型，但使用了整数条件 create index idx_user_id on users(user_id); select * from users where user_id = 12345; -- 索引可能失效
优化方法：
-- 确保条件值类型与字段类型一致 select * from users where user_id = '12345'; -- 使用字符串类型
8. 小心使用not、!=、<>、!<、!>、not in、not like等否定操作符
否定条件通常会导致索引失效，因为数据库需要检查所有不满足条件的记录。
优化方法：尽量用肯定表达式替代否定表达式：
-- 优化前：无法充分利用索引 select * from products where category_id != 5; -- 优化后：可以使用索引 select * from products where category_id < 5 or category_id > 5;
9. 合理使用limit优化分页查询
大偏移量的limit分页查询效率较低，因为mysql需要检索前n条记录然后丢弃。
优化前：
-- 性能较差的分页查询 select * from products order by id limit 100000, 10;
优化方法1 - 使用索引覆盖扫描：
-- 先获取id，再关联查询完整数据
select p.* from products p
join (
select id from products order by id limit 100000, 10
) tmp on p.id = tmp.id;优化方法2 - 使用上次查询的最大id：
-- 假设已知上一页的最大id是100233 select * from products where id > 100233 order by id limit 10;
10. 避免使用select *，只查询需要的列
使用select *会返回所有列，可能破坏覆盖索引的效果，并增加网络和内存开销。
优化前：
-- 可能导致不必要的开销 select * from users where name = 'tom';
优化后：
-- 只返回需要的列，可能利用覆盖索引 select id, name, email from users where name = 'tom';
11. 使用explain分析查询执行计划
在优化前，先使用explain分析sql语句的执行计划，了解索引使用情况。
explain select * from users where name = 'tom' and age > 20;
重点关注以下字段：
type: 从好到差依次是：system > const > eq_ref > ref > range > index > allkey: 实际使用的索引rows: 预计需要扫描的行数extra: 额外信息，如"using index"表示使用了覆盖索引
三、特殊场景索引优化
12. 使用索引排序优化order by操作
如果order by的列与where使用的列不一致，排序无法使用索引，会导致文件排序。
优化前：
-- where和order by使用不同的列，可能导致文件排序 create index idx_user_name on users(name); select * from users where name = 'tom' order by age;
优化后：
-- 创建联合索引同时包含where和order by的列 create index idx_user_name_age on users(name, age); select * from users where name = 'tom' order by age;
注意事项： order by的多个字段需要与索引顺序一致，且排序方向需一致(全asc或全desc)。
13. 在大表上创建索引的最佳实践
在大表上直接创建索引可能会导致长时间锁表。可以使用以下方法优化：
方法1 - 使用低峰期操
-- 在低峰期执行索引创建 create index idx_order_status on orders(status);
方法2 - 使用在线ddl(mysql 8.0+)：
-- 使用algorithm和lock选项 create index idx_order_status on orders(status) algorithm=inplace, lock=none;
方法3 - 使用pt-online-schema-change工具：
pt-online-schema-change --alter "add index idx_order_status (status)" \ --host=localhost --user=root --ask-pass --database=mydb --table=orders \ --execute
14. 使用虚拟列为计算结果创建索引
对于经常需要计算后过滤的场景，可以使用虚拟列并在其上创建索引。
-- 添加虚拟列存储计算结果 alter table products add total_value decimal(10,2) as (price * quantity) virtual; -- 在虚拟列上创建索引 create index idx_total_value on products(total_value); -- 使用计算列进行查询 select * from products where total_value > 10000;
15. 使用哈希索引优化等值查询
innodb不支持显式的哈希索引，但我们可以自己实现：
-- 添加哈希列
alter table users add name_hash int unsigned
generated always as (crc32(name)) stored;
-- 在哈希列上创建索引
create index idx_name_hash on users(name_hash);
-- 使用哈希索引查询
select * from users
where name_hash = crc32('tom') and name = 'tom';注意最后还需要验证原始值，因为哈希可能冲突。
四、索引维护优化
16. 定期优化和重建索引
随着数据变化，索引可能变得碎片化，影响性能。定期优化表和重建索引可以改善性能。
-- 分析表 analyze table orders; -- 优化表 optimize table orders; -- 或者重建索引 alter table orders drop index idx_status, add index idx_status(status);
建议： 设置一个低峰期的定时任务，对重要表执行优化操作。
17. 控制单表上的索引数量
索引数量过多会影响写性能，建议每个表的索引数量控制在5个以内。
优化方法：
1. 删除重复和未使用的索引
2. 合并功能类似的索引
-- 查找未使用的索引 select * from schema_unused_indexes; -- performance schema -- 查找重复的索引 select * from sys.schema_redundant_indexes; -- sys schema
18. 使用降序索引优化排序
mysql 8.0+支持降序索引，可以优化混合排序方向的查询。
-- 创建混合排序方向的索引(mysql 8.0+) create index idx_user_age_score on users(age asc, score desc); -- 可以高效执行的查询 select * from users order by age asc, score desc;
19. 使用部分索引优化高选择性数据
mysql 8.0+支持在where条件满足时才为行创建索引记录，减少索引大小。
-- 只为活跃用户创建索引(mysql 8.0+) create index idx_active_users on users(name, email) where status = 'active';
五、索引监控与进阶技巧
20. 利用索引统计信息进行调优
mysql维护了索引统计信息，可以帮助优化器选择合适的索引。有时统计信息不准确会导致次优的执行计划。
-- 查看表的统计信息 show table status like 'users'; -- 查看索引的基数 show index from users; -- 刷新统计信息 analyze table users;
21. 使用索引提示(index hints)解决优化器选择问题
有时mysql优化器的选择不是最优的，可以使用索引提示强制使用特定索引。
-- 强制使用特定索引 select * from users force index(idx_name_age) where name = 'tom' and age > 20; -- 忽略特定索引 select * from users ignore index(idx_status) where status = 'active' and age > 20;
建议： 索引提示应该是最后的手段，通常先尝试优化表结构和索引设计。
总结
索引优化是一个持续的过程，需要结合业务特点、数据分布和查询模式来综合考虑。
优秀的索引设计需要理论知识和实践经验的结合。
以上就是21个mysql索引优化实战技巧分享的详细内容，更多关于mysql索引的资料请关注代码网其它相关文章！
发表评论