悠悠楠杉
MySQL索引优化全攻略:从创建到实战的最佳实践
引言:为什么你的数据库越来越慢?
最近接手了一个电商系统,商品表数据量突破500万后,搜索查询突然从毫秒级跌到3-4秒。经过排查发现,问题出在索引使用不当——有12个字段建立了独立索引,但最常用的组合查询反而没有覆盖。这让我意识到,很多开发者对MySQL索引的理解还停留在表面。本文将分享我沉淀多年的索引优化实战经验。
一、索引的本质与底层原理
1.1 索引的B+树结构
MySQL最常见的InnoDB引擎采用B+树索引结构,其特点在于:
- 非叶子节点只存储键值和指针(类似目录页)
- 所有数据记录存储在叶子节点并形成链表
- 三层结构可支撑约2000万数据(按16KB页大小计算)
我曾用EXPLAIN ANALYZE
对比过B+树与哈希索引的差异:在范围查询时,B+树的性能优势能达到哈希索引的5-8倍。
1.2 聚簇索引的妙用
InnoDB的聚簇索引(主键索引)有个特性:数据行实际上存储在叶子节点。这意味着:
sql
-- 查询效率对比示例
SELECT * FROM users WHERE id = 100; -- 只需1次IO
SELECT * FROM users WHERE name = '张三'; -- 需要2次IO(先查二级索引,再回表)
二、索引创建五大黄金法则
2.1 最左前缀原则实战
组合索引(a,b,c)的实际生效场景:
sql
WHERE a=1 AND b=2 -- √ 能用a,b
WHERE b=2 AND c=3 -- × 只能用a
WHERE a=1 AND c=3 -- √ 能用a(c不能单独用)
去年优化过一个订单系统,将(status, create_time)
的组合索引调整为(user_id, status, create_time)
后,用户订单查询速度提升12倍。
2.2 索引选择性计算
公式:选择性 = 不重复值数量 / 总记录数
sql
-- 计算性别字段的选择性
SELECT
COUNT(DISTINCT gender)/COUNT(*) AS selectivity
FROM users;
-- 结果约0.5(不适合单独建索引)
经验阈值:低于0.1的字段不建议单独建索引。
三、高性能索引使用技巧
3.1 避免索引失效的坑
这些常见写法会导致索引失效:
sql
WHERE YEAR(create_time) = 2023 -- × 函数运算
WHERE name LIKE '%伟' -- × 前导通配符
WHERE amount*2 > 100 -- × 列运算
解决方案:
sql
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31' -- √
WHERE name LIKE '张%' -- √
WHERE amount > 100/2 -- √
3.2 覆盖索引优化
当查询所需字段都包含在索引中时,性能飞跃:sql
-- 原始查询(需要回表)
SELECT id,name,age FROM users WHERE city='北京';
-- 优化方案:建立(city,name,age)组合索引
-- EXPLAIN显示"Using index"即表示使用了覆盖索引
四、实战场景索引设计
4.1 电商系统典型场景
商品表推荐索引结构:
sql
ALTER TABLE products ADD INDEX `idx_search` (
category_id,
price_range,
is_on_sale
);
ALTER TABLE products ADD INDEX `idx_title` (title(20));
-- 标题使用前缀索引,平衡存储与查询效率
4.2 社交网络关系查询
好友关系表的优化案例:sql
-- 原始设计
SELECT * FROM relations
WHERE userid=100 OR friendid=100;
-- 优化方案:使用UNION+分别索引
SELECT * FROM relations WHERE userid=100
UNION
SELECT * FROM relations WHERE friendid=100;
-- 需建立(userid)和(friendid)两个索引
五、索引监控与维护
5.1 索引使用率分析
通过performance_schema查看索引使用情况:
sql
SELECT * FROM sys.schema_index_statistics
WHERE table_schema='your_db';
-- 重点关注select_scan/select_lookup比例
5.2 定期索引重组
碎片率超过30%时应优化:sql
-- 查看碎片率
SELECT tablename, indexname,
ROUND(statvalue * 100, 2) AS fragratio
FROM mysql.innodbindexstats
WHERE stat_name='fragmentation';
-- 优化命令
ALTER TABLE orders ENGINE=InnoDB;
结语:索引优化的平衡艺术
记得前年有个200GB的数据库,删除17个冗余索引后,写入性能提升40%,磁盘空间节省35GB。但也要警惕过度优化——曾见过一个表建了20个索引,导致UPDATE操作比SELECT还慢。好的索引策略应该像中医调理:先诊断(SQL审计),再对症下药(精准建索引),最后定期复查(性能监控)。
附录:推荐工具清单
1. Percona Toolkit中的pt-index-usage
2. MySQL Shell的索引分析功能
3. 阿里云DAS的索引建议