悠悠楠杉
网站页面
MySQL索引检查与性能对比实战：从创建到优化的全流程解析
08/03
MySQL索引检查与性能对比实战：从创建到优化的全流程解析
一、索引检查：MySQL的"体检中心"
当我们发现SQL查询变慢时，第一反应往往是："索引用上了吗？" 这里介绍几种实用的检查方法：
1. EXPLAIN命令 - 执行计划透视镜
sql
EXPLAIN SELECT * FROM articles WHERE title LIKE '数据库%';
重点关注type列（ALL表示全表扫描）、key列（实际使用的索引）和rows列（预估扫描行数）。上个月排查的一个案例中，某文章表未走索引导致500万行全表扫描，加上索引后查询时间从2.1秒降至23毫秒。
2. SHOW INDEX诊断
sql
SHOW INDEX FROM articles;
这个命令能显示表的索引清单，包括Cardinality（基数）这个关键指标。去年优化过一个电商系统，发现某索引基数只有总行数的0.3%，这种低选择性索引反而拖慢了写入速度。
3. 慢查询日志分析
在my.cnf中配置：
ini
slow_query_log = 1
long_query_time = 1
曾通过慢日志发现一个被忽略的联合查询，该查询涉及三个表但只使用了一个索引，添加复合索引后响应时间从3秒降到80毫秒。
二、创建索引前后的性能蜕变
案例背景
某博客系统文章表结构：
sql
CREATE TABLE `articles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`keywords` varchar(255) DEFAULT NULL,
`description` text,
`content` longtext,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
测试环境
- 数据量：120万篇技术文章
- 测试查询：
SELECT id, title FROM articles WHERE keywords LIKE '%Java%' ORDER BY created_at DESC LIMIT 20
无索引时的表现
sql
-- 执行时间：1.87秒
-- EXPLAIN显示：
type: ALL
possible_keys: NULL
rows: 1184276
数据库工程师的噩梦场景：全表扫描+文件排序（Using filesort）
添加索引后的蜕变
sql
ALTER TABLE articles
ADD INDEX idx_keyword_created (keywords, created_at);
执行同样的查询：
sql
-- 执行时间：0.028秒
-- EXPLAIN显示：
type: range
possible_keys: idx_keyword_created
rows: 2843
性能提升约66倍！索引就像给数据库装上了GPS导航，直接定位到目标数据区。
三、索引使用的黄金法则
最左前缀原则实战：
- 有效：
WHERE keywords LIKE 'MySQL%'（能用索引) - 无效：
WHERE keywords LIKE '%SQL%'（全表扫描）
- 有效：
覆盖索引的魔法：sql
-- 使用覆盖索引（Using index）
SELECT keywords, createdat FROM articles WHERE keywords = '数据库' ORDER BY createdat;-- 需要回表查询（Using index condition）
SELECT title, keywords FROM articles
WHERE keywords = '云计算';索引选择性的平衡艺术：
- 性别字段索引（选择性差）
- 手机号字段索引（选择性优）
四、那些年我们踩过的索引坑
过早优化陷阱：曾给所有字段都加索引，导致写入性能下降40%
隐式转换事故：
WHERE mobile = 13800138000（数字比较触发全表扫描）OR条件失控：
WHERE title='A' OR content='B'导致索引失效
五、性能监控长效机制
每周使用
pt-index-usage工具分析未使用的索引建立性能基线：
sql SELECT * FROM sys.schema_unused_indexes;定期执行
ANALYZE TABLE更新统计信息
结语：索引是把双刃剑
记得去年双十一大促前夜，我们紧急撤下一个"画蛇添足"的索引，使订单处理速度恢复如常。索引不是越多越好，而要在查询速度和写入成本间找到平衡点。当你真正理解B+树的工作原理时，索引优化就会从玄学变成精确的科学。
