1. 索引优化为何能带来10倍性能提升当数据库表数据量超过百万级时没有索引的查询就像在图书馆里逐页翻找特定内容。我最近优化过一个电商平台的订单查询接口原本需要8秒的查询在优化后仅需0.7秒。这种量级的性能飞跃主要来自三个机制索引的B树结构使得查找时间复杂度从O(n)降到O(log n)。以包含1000万记录的用户表为例全表扫描需要检查1000万个数据页而通过索引通常只需3-4次磁盘I/O假设树高为4。覆盖索引Covering Index能避免回表操作。当我们创建包含(user_id, username, email)的联合索引时SELECT username FROM users WHERE user_id?的查询可以直接从索引获取数据无需访问主表。某社交平台应用此策略后其核心接口的IOPS降低了72%。索引条件下推ICP是MySQL5.6引入的重要特性。它允许在存储引擎层提前过滤数据减少向上层传输的数据量。在某物流系统中对WHERE status1 AND create_time2023-01-01的查询使用ICP后传输数据量从230MB降至15MB。关键提示索引不是银弹不当使用反而会降低性能。我曾遇到一个每张表创建10个索引的案例导致写入性能下降60%因为每次INSERT都需要更新所有相关索引。2. 索引类型选型的黄金法则2.1 B-Tree索引的适用场景B-Tree索引适合等值查询和范围查询95%的OLTP场景都应优先考虑。某金融系统在账户表的account_number字段添加B-Tree索引后查询耗时从1200ms降至8ms。但要注意最左前缀原则对于(A,B,C)的联合索引WHERE A1 AND B2能使用索引但WHERE B2无法使用索引列顺序应该将区分度高的字段放前面。用户表的(gender, age)索引效果远差于(age, gender)2.2 哈希索引的精准定位哈希索引适合等值查询且不排序的场景。某缓存系统用哈希索引实现用户session查找QPS从2000提升到15000。但要注意不支持范围查询存在哈希冲突可能InnoDB的自适应哈希索引是自动管理的2.3 全文索引的文本搜索优化对于商品描述等文本字段全文索引比LIKE %keyword%高效得多。某内容平台改用全文索引后搜索延迟从2s降到200ms。关键配置ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title, body); SELECT * FROM articles WHERE MATCH(title, body) AGAINST(数据库优化);3. 实战中的索引策略设计3.1 联合索引的排列组合设计联合索引时要考虑查询模式。电商平台典型场景-- 查询模式按分类状态时间筛选商品 ALTER TABLE products ADD INDEX idx_category_status_time (category_id, status, create_time); -- 好的查询能充分利用索引 SELECT * FROM products WHERE category_id5 AND status1 ORDER BY create_time DESC LIMIT 10; -- 差的查询无法使用status之后的索引列 SELECT * FROM products WHERE status1;3.2 前缀索引的存储优化对于长字符串字段前缀索引能显著减少索引大小。某日志系统对request_uri字段采用前20字符作为索引索引大小减少80%ALTER TABLE access_log ADD INDEX idx_uri_prefix (request_uri(20));通过计算选择性确定最佳长度SELECT COUNT(DISTINCT LEFT(request_uri, 10))/COUNT(*) AS sel10, COUNT(DISTINCT LEFT(request_uri, 20))/COUNT(*) AS sel20, COUNT(DISTINCT LEFT(request_uri, 30))/COUNT(*) AS sel30 FROM access_log;3.3 函数索引的巧妙应用MySQL 8.0支持函数索引某国际化应用对用户邮箱统一小写处理ALTER TABLE users ADD INDEX idx_lower_email ((LOWER(email)));4. 索引优化诊断工具箱4.1 EXPLAIN的深度解读分析这个执行计划EXPLAIN SELECT * FROM orders WHERE user_id100 AND statuspaid ORDER BY create_time DESC;重点关注type列const ref range index ALLkey_len确认实际使用的索引长度ExtraUsing filesort表示需要额外排序4.2 慢查询日志分析配置my.cnf捕获慢查询slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1使用pt-query-digest分析pt-query-digest /var/log/mysql/mysql-slow.log slow_report.txt4.3 索引效率评估通过sys库分析索引使用情况SELECT * FROM sys.schema_unused_indexes; SELECT * FROM sys.schema_redundant_indexes;5. 高级优化技巧与避坑指南5.1 索引跳跃扫描MySQL 8.0的索引跳跃扫描特性即使不满足最左前缀也能利用索引-- 索引 (gender, age) SELECT * FROM users WHERE age 20; -- 8.0可以转化为类似 WHERE gender IN(M,F) AND age 205.2 不可见索引的灰度发布先设置索引不可用验证无性能影响再删除ALTER TABLE orders ALTER INDEX idx_test INVISIBLE; -- 观察期后 ALTER TABLE orders DROP INDEX idx_test;5.3 索引合并的陷阱优化器可能合并多个单列索引但效率通常不如联合索引-- 有index(a)和index(b) SELECT * FROM tbl WHERE a1 AND b2; -- 可能使用Index Merge而非更优的联合索引6. 真实案例电商平台优化实录某电商平台商品搜索接口优化过程原始查询耗时1200msSELECT * FROM products WHERE category_id5 AND price BETWEEN 100 AND 500 AND stock 0 ORDER BY sales_volume DESC LIMIT 20;优化方案创建(category_id, stock, price, sales_volume)联合索引改写查询确保索引生效最终效果查询时间降至85ms服务器CPU负载从70%降到15%血泪教训曾因未考虑索引维护成本在高峰时段添加大表索引导致主从延迟30分钟。现在都在业务低峰期执行ALTER TABLE ... ALGORITHMINPLACE, LOCKNONE;