MySQL性能优化：索引、查询与架构实战

MySQL是目前最流行的关系型数据库之一，几乎是Web开发的标配。但是很多开发者只会用MySQL做基本的增删改查，对性能优化了解不多。随着数据量的增长和访问量的增加，数据库性能问题会逐渐显现，慢查询、锁等待、连接数过多等问题会严重影响网站的性能和稳定性。掌握MySQL性能优化，是后端工程师的必备技能。今天就来分享MySQL性能优化的实战经验，包括索引优化、查询优化、架构优化等方面。

性能优化概述

MySQL性能优化是一个系统工程，需要从多个层面进行：

硬件层面：CPU、内存、磁盘、网络等硬件资源的优化
配置层面：MySQL配置参数的优化，如缓冲池、连接数、日志等
架构层面：读写分离、分库分表、主从复制、集群等架构优化
索引层面：合理设计和使用索引，避免全表扫描
查询层面：优化SQL语句，避免慢查询
应用层面：连接池、缓存、批量操作等应用层优化

性能优化的原则：

先测量，再优化：不要凭感觉优化，先用工具测量，找到瓶颈，再有针对性地优化
二八定律：80%的性能问题来自20%的慢查询，优先优化这20%的慢查询
避免过度优化：优化要适度，不要为了优化而优化，增加系统复杂度
持续监控：性能优化不是一次性的，需要持续监控，发现问题及时优化

索引优化

索引是MySQL性能优化中最重要的部分，合理使用索引能大大提升查询效率。

1. 索引类型：

普通索引（INDEX）：最基本的索引，没有任何限制
唯一索引（UNIQUE）：索引列的值必须唯一，允许有空值
主键索引（PRIMARY KEY）：特殊的唯一索引，不允许有空值，一个表只能有一个主键
联合索引（复合索引）：多个字段组合成的索引，遵循最左前缀原则
全文索引（FULLTEXT）：用于全文搜索，支持CHAR、VARCHAR、TEXT类型
空间索引（SPATIAL）：用于地理空间数据类型

2. 索引的创建原则：

频繁查询的字段：WHERE、JOIN、ORDER BY、GROUP BY中频繁使用的字段应该建索引
区分度高的字段：区分度（基数/总行数）高的字段适合建索引，如用户ID、邮箱等；性别、状态等区分度低的字段不适合单独建索引
不要过度索引：索引不是越多越好，每个索引都会占用存储空间，降低写入性能。只在需要的字段上建索引
联合索引优先：多个字段经常一起查询时，优先建联合索引，而不是多个单列索引
短索引优先：对字符串字段建索引时，尽量指定索引长度，只索引前缀，节省空间

3. 联合索引的最左前缀原则：联合索引遵循最左前缀原则，查询时必须从索引的最左列开始，并且不能跳过中间的列。

例如，有联合索引idxab_c(a, b, c)：

WHERE a = 1 ✅ 使用索引
WHERE a = 1 AND b = 2 ✅ 使用索引
WHERE a = 1 AND b = 2 AND c = 3 ✅ 使用索引
WHERE b = 2 ❌ 不使用索引（没有从最左列a开始）
WHERE a = 1 AND c = 3 ⚠️ 只使用a部分索引（跳过了中间的b）
WHERE b = 2 AND c = 3 ❌ 不使用索引（没有从最左列a开始）

4. 索引失效的常见情况：

在索引列上使用函数或运算：WHERE YEAR(create_time) = 2016
隐式类型转换：字符串字段用数字查询WHERE phone = 13800138000
使用!=、<>、NOT IN、NOT EXISTS等负向查询（部分情况）
使用LIKE '%xxx'前缀模糊查询（LIKE 'xxx%'可以使用索引）
OR连接的条件中有非索引列
联合索引不满足最左前缀原则
数据量小，优化器认为全表扫描比索引扫描更快

5. 查看索引使用情况：

-- 查看表的索引
SHOW INDEX FROM table_name;

-- 查看查询执行计划
EXPLAIN SELECT * FROM table_name WHERE id = 1;

-- 查看索引使用统计
SELECT * FROM sys.schema_unused_indexes;

-- 查看索引使用情况
SELECT * FROM sys.schema_index_statistics;

EXPLAIN结果中重要的字段：

type：访问类型，从好到差依次是system > const > eq_ref > ref > range > index > ALL
key：实际使用的索引
rows：扫描的行数
Extra：额外信息，如Using index（覆盖索引）、Using where、Using filesort（文件排序）、Using temporary（临时表）

查询优化

1. 避免SELECT ：只查询需要的字段，不要用SELECT 。SELECT *会查询所有字段，增加网络传输和内存消耗，而且无法使用覆盖索引。

-- 不好
SELECT * FROM users WHERE id = 1;

-- 好
SELECT id, name, email FROM users WHERE id = 1;

2. 分页查询优化：当偏移量很大时，LIMIT offset, size会很慢，因为需要扫描前面的所有记录。

-- 普通分页，offset大时很慢
SELECT * FROM articles ORDER BY id LIMIT 100000, 10;

-- 优化1：使用子查询延迟关联
SELECT * FROM articles 
WHERE id >= (SELECT id FROM articles ORDER BY id LIMIT 100000, 1)
LIMIT 10;

-- 优化2：使用上一页的最大ID（适用于ID连续递增）
SELECT * FROM articles WHERE id > 100000 ORDER BY id LIMIT 10;

3. 避免在WHERE子句中对字段进行函数或运算：

-- 不好，索引失效
SELECT * FROM orders WHERE YEAR(create_time) = 2016;

-- 好，使用范围查询
SELECT * FROM orders WHERE create_time >= '2016-01-01' AND create_time < '2017-01-01';

4. 用EXISTS代替IN：当子查询结果集较大时，EXISTS通常比IN效率更高。

-- IN
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE status = 1);

-- EXISTS
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 1);

5. 批量操作：批量插入、更新、删除比逐条操作效率高很多，能减少网络交互和事务开销。

-- 批量插入
INSERT INTO users (name, email) VALUES 
('张三', 'zhangsan@example.com'),
('李四', 'lisi@example.com'),
('王五', 'wangwu@example.com');

-- 批量更新（使用CASE WHEN）
UPDATE users SET 
    name = CASE id 
        WHEN 1 THEN '张三新'
        WHEN 2 THEN '李四新'
        WHEN 3 THEN '王五新'
    END
WHERE id IN (1, 2, 3);

6. 避免使用临时表和文件排序： ORDER BY和GROUP BY如果不能使用索引，会产生文件排序（Using filesort）或临时表（Using temporary），性能很差。

为ORDER BY和GROUP BY的字段建合适的索引
尽量用索引排序，避免文件排序
分组时尽量用索引字段分组

7. 慢查询分析：

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过1秒的查询记录

-- 查看慢查询日志
SHOW VARIABLES LIKE 'slow_query_log%';

-- 使用mysqldumpslow分析慢查询日志
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

配置优化

1. InnoDB缓冲池（innodbbufferpool_size）：这是InnoDB最重要的配置参数，决定了InnoDB缓存数据和索引的内存大小。建议设置为物理内存的60%-80%。

[mysqld]
innodb_buffer_pool_size = 4G

2. 连接数（max_connections）：设置最大连接数，根据并发量调整。连接数不是越大越好，过大的连接数会消耗更多内存。

[mysqld]
max_connections = 500

3. 日志配置：

[mysqld]
# 慢查询日志
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

# 二进制日志（主从复制需要）
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7

# 错误日志
log_error = /var/log/mysql/error.log

4. InnoDB其他配置：

[mysqld]
# 日志文件大小
innodb_log_file_size = 256M

# 日志缓冲大小
innodb_log_buffer_size = 8M

# 刷新日志策略，2是性能和安全的平衡
innodb_flush_log_at_trx_commit = 2

# 每个表独立表空间
innodb_file_per_table = 1

# 并发线程数
innodb_thread_concurrency = 0  # 0表示不限制

# IO线程数
innodb_read_io_threads = 4
innodb_write_io_threads = 4

架构优化

1. 主从复制：主从复制是MySQL最常用的架构，主库负责写，从库负责读，实现读写分离，提升读性能。

主从复制的原理：

主库将数据变更记录到二进制日志（binlog）
从库的IO线程读取主库的binlog并写入中继日志（relay log）
从库的SQL线程读取中继日志并在从库执行

主从复制的配置：

# 主库配置
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW

# 从库配置
[mysqld]
server-id = 2
relay_log = /var/log/mysql/relay-bin.log
read_only = 1  # 从库只读

2. 读写分离：有了主从复制后，就可以实现读写分离。写操作走主库，读操作走从库，减轻主库的读压力。

读写分离可以在应用层实现（代码判断读写，选择不同的数据源），也可以用中间件实现（如MyCat、ShardingSphere、ProxySQL等）。

3. 分库分表：当单库单表数据量太大（如单表超过1000万行）时，查询性能会严重下降，这时需要考虑分库分表。

分库分表的方式：

垂直分库：按业务模块分库，如用户库、订单库、商品库
垂直分表：按字段分表，将不常用的大字段拆分到扩展表
水平分库：按某个字段（如用户ID）的哈希或范围将数据分到多个库
水平分表：按某个字段的哈希或范围将数据分到多个表

分库分表会增加系统复杂度，带来分布式事务、跨库查询、排序分页等问题，需要谨慎使用。常用的分库分表中间件有ShardingSphere、MyCat等。

4. 缓存：数据库性能优化的终极手段是缓存。将热点数据缓存到Redis等缓存系统中，减少数据库的访问压力。

缓存的常见策略：

Cache Aside：先查缓存，缓存没有再查数据库，然后写入缓存
Read Through：缓存层负责读取数据库，应用只和缓存交互
Write Through：写操作同时更新缓存和数据库
Write Behind：写操作只更新缓存，异步批量更新数据库

缓存要注意缓存穿透、缓存雪崩、缓存击穿等问题，合理设置过期时间和降级策略。

表结构优化

1. 选择合适的数据类型：

尽量使用小的数据类型，如用TINYINT代替INT，用VARCHAR(20)代替VARCHAR(255)
整数类型优先，整数比字符串比较和索引效率高
避免使用NULL，NULL会占用额外空间，索引和查询更复杂，用默认值代替
时间类型用DATETIME或TIMESTAMP，不要用字符串存储时间
金额用DECIMAL，不要用FLOAT或DOUBLE（会有精度问题）

2. 主键设计：

主键尽量用自增整数（AUTO_INCREMENT），插入性能好，索引体积小
不要用UUID作为主键，UUID无序，插入性能差，索引体积大
主键不要修改，主键修改会导致索引重建和外键问题

3. 字段设计：

不要预留太多字段，需要时再添加
大字段（如TEXT、BLOB）尽量拆分到单独的表，避免影响主表查询性能
经常一起查询的字段放在同一个表，减少JOIN

4. 字符集和排序规则：

统一使用utf8mb4字符集，支持完整的Unicode（包括emoji）
排序规则用utf8mb4unicodeci或utf8mb4generalci
数据库、表、字段的字符集要统一，避免隐式转换导致索引失效

性能监控工具

1. MySQL自带工具：

-- 查看服务器状态
SHOW STATUS;

-- 查看服务器变量
SHOW VARIABLES;

-- 查看进程列表
SHOW PROCESSLIST;

-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS;

-- 查看当前事务
SELECT * FROM information_schema.INNODB_TRX;

-- 查看锁等待
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

2. 性能schema： MySQL 5.5+提供了performanceschema，用于监控MySQL内部运行。

-- 查看是否开启
SHOW VARIABLES LIKE 'performance_schema';

-- 查看事件等待统计
SELECT * FROM performance_schema.events_waits_summary_global_by_event_name;

-- 查看语句统计
SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

3. sys库： MySQL 5.7+提供了sys库，是对performance_schema的封装，提供了更易用的视图。

-- 查看慢查询
SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;

-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;

-- 查看表的I/O统计
SELECT * FROM sys.schema_table_statistics ORDER BY rows_read DESC LIMIT 10;

-- 查看等待事件
SELECT * FROM sys.waits_global_by_latency LIMIT 10;

4. 第三方工具：

pt-query-digest：Percona Toolkit中的慢查询分析工具
mysqldumpslow：MySQL自带的慢查询日志分析工具
MySQL Workbench：MySQL官方的图形化管理工具，有性能监控功能
Prometheus + Grafana：常用的监控方案，通过mysqld_exporter采集MySQL指标

总结

MySQL性能优化是一个系统工程，需要从硬件、配置、架构、索引、查询、应用等多个层面进行。其中索引优化和查询优化是最常用、见效最快的优化手段，架构优化（主从复制、读写分离、分库分表、缓存）是应对大数据量高并发的终极方案。

性能优化要遵循"先测量，再优化"的原则，先用工具找到瓶颈，再有针对性地优化。不要凭感觉优化，也不要过度优化。性能优化不是一次性的，需要持续监控，发现问题及时优化。

希望这篇文章能帮助大家更好地理解和使用MySQL性能优化，让你的数据库跑得更快、更稳。