MySQL索引和SQL优化是提升数据库性能的核心手段，合理的优化策略可将查询速度提升几十倍甚至上百倍。

---

一、索引设计优化策略

1. 高效索引设计原则

高选择性字段优先：选择性高的字段（唯一值占比高）更适合建索引。例如，user_id比gender更适合建索引，因为前者能精确过滤，后者可能只有"男/女"两种值，区分度低。

短索引策略：对于长字段（如VARCHAR(255)），可以用前缀索引，只索引前几个字符，节省空间又不失效率。例如：

-- 索引前10个字符，适用于邮箱前缀匹配
CREATE INDEX idx_email_prefix ON users(email(10));
SELECT * FROM users WHERE email LIKE 'john.doe%';

这样可减少索引大小约70%。

覆盖查询需求：设计联合索引时，尽量覆盖常用查询的字段，避免回表。例如：

-- 创建联合索引
CREATE INDEX idx_name_age ON users(name, age);
-- 直接从索引获取数据，无需回表
SELECT name, age FROM users WHERE name = 'Tom';

在EXPLAIN输出中，Extra列显示Using index就表示用到了覆盖索引。

2. 联合索引设计技巧

最左前缀原则：联合索引遵循最左前缀匹配原则。假设你建了一个联合索引：

ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

以下查询能命中索引：

-- 命中：使用了最左列user_id
SELECT * FROM orders WHERE user_id = 1001;
-- 命中：使用了user_id + status
SELECT * FROM orders WHERE user_id = 1001 AND status = 'paid';

以下查询无法命中索引：

-- 未命中：跳过了user_id，直接查status
SELECT * FROM orders WHERE status = 'paid';

口诀：联合索引从左往右匹配，中间不能断，遇到范围查询（大于、小于、BETWEEN、LIKE）会停止匹配。

联合索引顺序优化：将选择性高的列放在前面，常用于条件查询的列放在前面，范围查询的列放在最后。例如：

-- 假设选择性：city < name < age
CREATE INDEX idx_user_name_age_city ON user(name, age, city);
-- 充分利用索引的查询
SELECT * FROM user WHERE name = 'Tom' AND age = 25;
SELECT * FROM user WHERE name = 'Tom' AND age = 25 AND city = 'Beijing';

错误示例：SELECT * FROM user WHERE age = 25 AND city = 'Beijing';（name列缺失，只能全表扫描）

3. MySQL 8.0+特色索引功能

降序索引：支持按降序存储索引，适合ORDER BY ... DESC场景。

-- 创建降序索引
CREATE INDEX idx_date_desc ON orders(order_date DESC);
-- 查询时避免额外排序
SELECT * FROM orders ORDER BY order_date DESC LIMIT 10;

性能提升20%-30%，特别适合大数据量排序查询。

不可见索引：标记索引为不可见，测试优化效果而不影响线上查询。

-- 创建不可见索引
ALTER TABLE users ADD INDEX idx_test (age) INVISIBLE;
-- 验证后启用
ALTER TABLE users ALTER INDEX idx_test VISIBLE;

此功能在高并发项目中可安全验证新索引效果，避免风险。

如果不清楚索引关系，看这里【SQL优化篇：索引（Index）】

---

二、SQL查询优化技巧

1. 避免索引失效的常见场景

1)索引字段使用函数或运算：

-- 错误：索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2026;
-- 正确：改写为范围查询，索引生效
SELECT * FROM users WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

函数或运算会改变索引字段的值，导致MySQL无法利用索引查找。

2)隐式类型转换：

-- 错误：phone是varchar类型，传入数字会触发隐式转换，索引失效
SELECT * FROM users WHERE phone = 13800138000;
-- 正确：加引号
SELECT * FROM users WHERE phone = '13800138000';

MySQL会将索引字段转换为数字类型，导致索引失效。

3)LIKE以通配符开头：

-- 错误：索引失效
SELECT * FROM users WHERE name LIKE '%张';
-- 正确：前缀匹配，索引有效
SELECT * FROM users WHERE name LIKE '张%';

%开头的模糊查询无法利用B+树索引的有序性，需全表扫描。

2. 子查询优化策略

1)JOIN改写替代IN子查询：

-- 原始慢查询（执行时间8.2秒）
SELECT o.order_id, o.total_amount, u.user_name
FROM orders o
WHERE o.user_id IN (
    SELECT user_id
    FROM users
    WHERE vip_level > 3
    AND registration_date > '2023-01-01'
);

-- 优化后（执行时间0.45秒）
SELECT o.order_id, o.total_amount, u.user_name
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE u.vip_level > 3
AND u.registration_date > '2023-01-01';

优化效果：消除了临时表创建，充分利用了users表的复合索引。

2)EXISTS改写替代IN子查询：

-- 优化前
SELECT * FROM orders WHERE user_id IN (SELECT user_id FROM vip_users);

-- 优化后
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM vip_users v WHERE v.user_id = o.user_id);

使用EXISTS比IN更高效当子查询结果集较大时，确保子查询表有索引user_id。

3. 分页查询优化

1)延迟关联优化：大数据量分页查询时，避免LIMIT offset过大导致的性能问题。

-- 慢查询：LIMIT 10000, 10（需扫描前10000条数据）
SELECT * FROM orders ORDER BY create_time DESC LIMIT 10000, 10;

-- 优化方案：利用索引定位起点
CREATE INDEX idx_create_id (create_time DESC, id);
SELECT o.* FROM orders o
WHERE o.id < (SELECT id FROM orders ORDER BY create_time DESC LIMIT 10000, 1)
ORDER BY create_time DESC LIMIT 10;

原理：子查询通过索引快速定位第10000条数据的id，主查询通过id < 目标值过滤，仅扫描10条数据。

---

三、实战优化流程

1. 优化流程与工具

四步优化法：

1. 定位问题：通过慢查询日志找出执行时间长的SQL

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

2. 分析执行计划：使用EXPLAIN查看查询计划

EXPLAIN SELECT * FROM orders WHERE user_id = 1001;

3. 优化索引与SQL：根据分析结果调整索引或重写SQL
4. 验证效果：对比优化前后的执行时间

关键分析命令：

• EXPLAIN：查看查询执行计划
• SHOW VARIABLES LIKE '%slow%'：查看慢查询日志配置
• SHOW STATUS LIKE 'Slow_queries'：查看慢查询数量
• SHOW ENGINE INNODB STATUS\G：查看InnoDB状态

2. 索引优化实践

控制索引数量：单表索引数量控制在5个以内，过多的索引会导致插入、更新、删除时需要同步维护多个B+树，写入性能暴跌，同时占用大量磁盘空间。

小表无需建索引：数据量小于1000行的表，全表扫描成本低于索引查询，无需建索引。

低区分度字段不单独建索引：性别、状态等枚举值少的字段，单独建索引无意义，可和其他字段组成联合索引。

避免冗余索引：已有联合索引idx_a_b(a,b)，无需再建idx_a(a)，联合索引已支持最左前缀匹配。

3. 电商订单查询案例

原始慢查询（执行时间3.2秒）：

SELECT * FROM orders
WHERE user_id = 1001
AND status IN ('paid', 'shipped')
ORDER BY create_time DESC
LIMIT 0, 10;

优化步骤：

1. 执行计划分析：发现type=ALL，未使用索引
2. 创建覆盖索引：

-- 依据user_id, status, create_time的顺序创建联合索引
ALTER TABLE orders ADD INDEX idx_user_status_time(user_id, status, create_time);

3. SQL改写（减少返回字段）：

SELECT id, user_id, status, amount, create_time
FROM orders
WHERE user_id = 1001
AND status IN ('paid', 'shipped')
ORDER BY create_time DESC
LIMIT 10;

结果：执行时间降至28ms，提升115倍！

---

四、总结与建议

核心原则：索引不是越多越好，每个索引都有写入和存储成本。关键是针对实际的查询模式，设计最合适的索引策略。

优化建议：

• 先分析后优化：使用EXPLAIN分析查询计划，确认索引是否生效
• 合理设计联合索引：遵循最左前缀原则，覆盖常用查询字段
• 避免索引失效：不使用函数、隐式转换、前缀通配符等
• 持续监控：开启慢查询日志，定期分析性能瓶颈