数据库性能优化的核心在于索引的合理使用。本文将通过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=3
• WHERE a=1 AND b>2
• WHERE a=1 ORDER BY b

失效场景：

• WHERE b=2 AND c=3
• WHERE a>1 AND b=2

3. 覆盖索引优先

当索引包含所有查询字段时，性能提升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

-- 需要filesort
CREATE 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 DESC
LIMIT 10;

优化四部曲

1. 执行计划分析

EXPLAIN SELECT... -- 显示全表扫描

2. 索引重构方案

ALTER TABLE orders ADD INDEX idx_user_status_time
(user_id, status, create_time DESC);

3. 查询改写优化

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;

4. 效果验证

• 扫描行数：从1200万 → 35行
• 执行时间：8200ms → 8ms
• 磁盘IO：从物理读 → 内存读

三、索引维护最佳实践

1. 碎片整理周期

ALTER TABLE orders ENGINE=InnoDB; -- 每月执行
ANALYZE TABLE orders; -- 每周更新统计信息

2. 热数据预加载

SELECT SQL_CACHE * FROM hot_products WHERE id IN (...);

3. 读写分离架构

# 配置MySQL组复制
group_replication_group_seeds = "node1:3306,node2:3306,node3:3306"

四、性能对比数据

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