数据库性能优化的核心在于索引的合理使用。本文将通过10个黄金法则，结合真实场景案例，揭示如何让百万级数据查询效率提升百倍的实战技巧。

一、索引优化十大黄金法则1. 精准索引法则

选择区分度>30%的字段创建索引，避免对性别等低区分度字段建索引

-- 错误示范CREATE INDEX idx_gender ON users(gender);-- 正确做法CREATE INDEX idx_mobile ON users(mobile);2. 最左匹配原则

联合索引(a,b,c)生效场景：

WHERE a=1 AND b=2 AND c=3WHERE a=1 AND b>2WHERE a=1 ORDER BY b

失效场景：

WHERE b=2 AND c=3WHERE a>1 AND b=23. 覆盖索引优先

当索引包含所有查询字段时，性能提升3-10倍

-- 需要回表SELECT * FROM orders WHERE user_id=100;-- 覆盖索引CREATE INDEX idx_user_status ON orders(user_id, status);SELECT user_id, status FROM orders WHERE user_id=100;4. 函数失效陷阱

索引列参与计算或函数时索引失效

-- 索引失效SELECT * FROM logs WHERE YEAR(create_time)=2023;-- 优化方案SELECT * FROM logs WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';5. 前缀索引技巧

对长文本字段使用前缀索引，节省70%存储空间

CREATE INDEX idx_comment_prefix ON comments(comment(20));6. 排序优化策略

ORDER BY字段与索引顺序一致可避免filesort

-- 需要filesortCREATE INDEX idx_age ON users(age);SELECT * FROM users ORDER BY age DESC, name ASC;-- 优化索引CREATE INDEX idx_age_name ON users(age DESC, name ASC);7. 索引下推（ICP）

MySQL5.6+自动启用，减少回表次数达90%

SET optimizer_switch='index_condition_pushdown=on';8. 索引合并优化

合理利用index_merge提升OR条件查询效率

EXPLAIN SELECT * FROM products WHERE category_id=5 OR price>1000;9. 死锁预防方案

更新操作按固定顺序执行，避免间隙锁冲突

-- 危险操作UPDATE account SET balance=balance-100 WHERE id=1;UPDATE account SET balance=balance+100 WHERE id=2;-- 安全方案BEGIN;SELECT * FROM account WHERE id IN (1,2) FOR UPDATE;UPDATE account SET balance=... WHERE id=1;UPDATE account SET balance=... WHERE id=2;COMMIT;10. 索引监控体系

定期分析索引使用率，清理冗余索引

SELECT * FROM sys.schema_unused_indexes;SELECT * FROM sys.schema_redundant_indexes;二、千万级数据优化实战案例背景

电商订单表（order）含1200万数据，查询最近3个月某用户的待发货订单耗时8.2秒

原始表结构

CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id INT, status TINYINT, amount DECIMAL(10,2), create_time DATETIME);

慢查询语句

SELECT * FROM orders WHERE user_id=12345 AND status=1 AND create_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH)ORDER BY create_time DESCLIMIT 10;优化四部曲执行计划分析EXPLAIN SELECT... -- 显示全表扫描索引重构方案ALTER TABLE orders ADD INDEX idx_user_status_time(user_id, status, create_time DESC);查询改写优化SELECT * FROM orders FORCE INDEX(idx_user_status_time)WHERE user_id=12345 AND status=1 AND create_time >= '2023-03-01'ORDER BY create_time DESC LIMIT 10;效果验证扫描行数：从1200万 → 35行执行时间：8200ms → 8ms磁盘IO：从物理读 → 内存读三、索引维护最佳实践碎片整理周期ALTER TABLE orders ENGINE=InnoDB; -- 每月执行ANALYZE TABLE orders; -- 每周更新统计信息热数据预加载SELECT SQL_CACHE * FROM hot_products WHERE id IN (...);读写分离架构# 配置MySQL组复制group_replication_group_seeds = "node1:3306,node2:3306,node3:3306"四、性能对比数据

优化阶段

数据量

查询耗时

扫描方式

无索引

1200万

8200ms

全表扫描

单列索引

1200万

450ms

索引扫描

联合索引

1200万

8ms

覆盖索引

缓存命中

热点数据

0.5ms

内存直接访问

结语： 索引优化是持续迭代的过程，需要结合EXPLAIN分析、慢查询日志和性能监控工具，建立从索引设计到维护的完整体系。通过本文的10大法则，可使90%的慢查询问题得到显著改善。