MySQL 优化详解：从基础到高级全面指南

MySQL 优化是一个系统工程，涵盖查询优化、索引设计、配置调优、架构优化等多个层面。以下是详细的优化策略和方法：

一、SQL 语句优化

1. 查询优化基础

• 避免 SELECT *：只查询需要的列

-- 不推荐
SELECT * FROM users;

-- 推荐
SELECT id, name, email FROM users;

• 使用 LIMIT 分页：大数据量时分页优化

-- 低效
SELECT * FROM articles LIMIT 10000, 20;

-- 高效 (使用索引列)
SELECT * FROM articles WHERE id > 10000 LIMIT 20;

2. JOIN 优化

• 确保关联字段有索引
• 小表驱动大表原则

-- 假设 departments 是小表
SELECT * FROM departments d 
JOIN employees e ON d.id = e.dept_id;

3. 子查询优化

• 将子查询转为 JOIN

-- 不推荐
SELECT * FROM products 
WHERE category_id IN (SELECT id FROM categories WHERE type = 'electronics');

-- 推荐
SELECT p.* FROM products p
JOIN categories c ON p.category_id = c.id 
WHERE c.type = 'electronics';

二、索引优化

1. 索引设计原则

• 最左前缀原则：联合索引 (a,b,c) 可支持 WHERE a=?、WHERE a=? AND b=? 等条件
• 区分度高的列建索引（如用户ID而非性别）

索引是MySQL性能优化的核心环节，合理的索引设计可以提升查询效率几个数量级。以下从索引原理、设计策略、优化技巧到实战案例进行全面解析。

一、索引底层原理

1. B+Tree索引结构

• 存储方式：平衡多路搜索树，InnoDB中每个索引都是一棵B+Tree
• 节点组成：

• • 非叶子节点：存储键值和指向子节点的指针
  • 叶子节点：存储键值和数据行指针（聚簇索引存储完整数据行）

• 特点：

• • 所有叶子节点通过指针相连形成链表
  • 非叶子节点不存储数据，只存储索引
  • 通常3-4层即可存储千万级数据

2. 聚簇索引 vs 二级索引

二、索引类型详解

1. 普通索引

CREATE INDEX idx_name ON users(name);

• 最基本的索引类型
• 允许重复值和NULL值

2. 唯一索引

CREATE UNIQUE INDEX idx_email ON users(email);

• 保证列值的唯一性
• 允许NULL值（但只能有一个NULL）

3. 主键索引

ALTER TABLE users ADD PRIMARY KEY (id);

• 特殊的唯一索引
• 不允许NULL值
• 自动创建聚簇索引（InnoDB）

4. 联合索引

CREATE INDEX idx_name_age ON users(name, age);

• 多列组合索引
• 遵循最左前缀原则

5. 全文索引

ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);

• 专用于文本搜索
• 支持MATCH AGAINST语法

6. 空间索引

CREATE SPATIAL INDEX sp_location ON maps(coordinates);

• 用于地理空间数据
• 支持GIS函数计算

三、索引设计策略

1. 索引选择原则

• 高选择性：区分度=不重复值/总行数 > 10%

-- 计算列的选择性
SELECT COUNT(DISTINCT status)/COUNT(*) FROM orders; -- 区分度低
SELECT COUNT(DISTINCT user_id)/COUNT(*) FROM orders; -- 区分度高

• 高频查询：WHERE/JOIN/ORDER BY/GROUP BY涉及的列
• 短字段优先：整型效率 > 字符串

2. 联合索引设计

• 最左前缀原则：INDEX(a,b,c) 支持 a=?、a=? AND b=?、a=? AND b=? AND c=?，但不支持 b=?
• 列顺序策略：

1. 1. 区分度高的列在前
   2. 等值查询列在前，范围查询列在后
   3. 常用列在前

示例：

-- 良好设计
CREATE INDEX idx_region_age_gender ON users(region, age, gender);

-- 可优化为
CREATE INDEX idx_region_gender_age ON users(region, gender, age);
-- 理由：gender的等值查询通常比age的范围查询更常用

3. 覆盖索引优化

• 定义：索引包含查询需要的所有字段，避免回表
• 实现方法：

-- 原始查询（需要回表）
SELECT id, name, age FROM users WHERE name LIKE '张%';

-- 优化为覆盖索引
CREATE INDEX idx_name_covering ON users(name, age);
-- 现在索引包含所有查询字段

四、索引失效场景分析

1. 函数操作导致失效

-- 失效（对列使用函数）
SELECT * FROM users WHERE DATE_FORMAT(create_time,'%Y-%m') = '2023-01';

-- 优化方案
SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-01-31';

2. 隐式类型转换

-- user_id是INT类型，但传入字符串（导致索引失效）
SELECT * FROM users WHERE user_id = '1001';

-- 应改为
SELECT * FROM users WHERE user_id = 1001;

3. 模糊查询通配符开头

-- 无法使用索引
SELECT * FROM users WHERE name LIKE '%张';

-- 可以使用索引
SELECT * FROM users WHERE name LIKE '张%';

4. OR条件部分无索引

-- 只有name有索引，age无索引（全表扫描）
SELECT * FROM users WHERE name = '张三' OR age = 30;

-- 优化方案1：为age添加索引
-- 优化方案2：改写为UNION
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE age = 30;

5. 使用!=或NOT IN

-- 通常无法使用索引
SELECT * FROM users WHERE status != 1;
SELECT * FROM orders WHERE id NOT IN (1001, 1002);

-- 优化方案：考虑业务逻辑改写
SELECT * FROM users WHERE status IN (0, 2, 3);

更多索引失效的场景：

https://zhuanlan.zhihu.com/p/22362255750

• 常见原因: 使用 OR、函数、范围查询、LIKE 开头、NOT、IS NULL、DISTINCT 等。
• 避免策略: 尽量避免在查询条件中使用这些情况，或者使用合适的索引（如组合索引、覆盖索引）来优化查询。

五、索引优化实战案例

案例1：分页查询优化

问题SQL：

SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

优化方案：

-- 方案1：使用索引覆盖+延迟关联
SELECT * FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) t
ON o.id = t.id;

-- 方案2：记录上次查询的最大ID
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

案例2：订单统计优化

问题SQL：

SELECT user_id, COUNT(*) 
FROM orders 
WHERE create_time > '2023-01-01'
GROUP BY user_id;

优化方案：

-- 创建联合索引
CREATE INDEX idx_user_create ON orders(user_id, create_time);

-- 使用覆盖索引
SELECT user_id, COUNT(*) 
FROM orders 
WHERE create_time > '2023-01-01'
GROUP BY user_id;
-- 执行计划应显示"Using index"

六、索引维护与管理

1. 索引监控

-- 查看索引使用情况
SELECT * FROM sys.schema_index_statistics 
WHERE table_schema = 'your_db';

-- 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes;

2. 索引碎片整理

-- 查看碎片率
SELECT table_name, index_name, 
       ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) size_mb,
       stat_description
FROM mysql.innodb_index_stats 
WHERE stat_name = 'size' AND database_name = 'your_db';

-- 重建索引
ALTER TABLE orders ENGINE=InnoDB;
-- 或
OPTIMIZE TABLE orders;

3. 索引生命周期管理

1. 新建索引：上线前在测试环境验证效果
2. 监控调整：通过性能监控评估索引效果
3. 下线清理：定期清理使用率低的冗余索引

七、高级索引技术（MySQL 8.0+）

1. 降序索引

CREATE INDEX idx_desc ON orders(create_time DESC, amount ASC);
-- 优化 ORDER BY create_time DESC, amount ASC

2. 函数索引

-- 创建基于函数的索引
CREATE INDEX idx_name_lower ON users((LOWER(name)));

-- 查询使用
SELECT * FROM users WHERE LOWER(name) = '张三';

3. 隐藏索引

-- 测试删除索引影响
ALTER TABLE users ALTER INDEX idx_name INVISIBLE;

-- 恢复可见
ALTER TABLE users ALTER INDEX idx_name VISIBLE;

索引优化检查清单

1. 所有高频查询条件都有合适索引
2. 联合索引列顺序遵循最左前缀原则
3. 避免出现索引失效场景
4. 大表查询尽量使用覆盖索引
5. 定期监控和清理冗余索引
6. 为排序和分组操作建立适当索引

通过深入理解索引原理并结合实际业务场景进行优化，可以显著提升MySQL查询性能。建议结合EXPLAIN分析工具持续优化索引策略。

使用Explain查询sql执行过程进一步优化

参见下一篇博客