MySQL性能优化实战，从这几个方面下手

前言

做网站开发或者运维的同学肯定都遇到过这种情况：网站越来越慢，数据库CPU占用越来越高，用户体验越来越差。这时候怎么办？很多同学一上来就说：加机器！加内存！其实这是最笨的方法。

MySQL性能优化是一个系统工程，要从多个方面下手：SQL、索引、表结构、配置参数、架构。很多时候，不用加机器，只要优化一下SQL和索引，性能就能提升好几倍。

这篇文章就把MySQL性能优化的完整思路整理出来，从定位问题到SQL优化、表结构优化、参数优化、架构优化，一步一步讲清楚。看完之后你就知道遇到性能问题应该从哪里下手了。

一、优化之前先定位问题

很多同学一上来就瞎优化，优化了半天也不知道有没有效果。正确的做法是：先定位问题，再针对性优化。

怎么定位慢SQL

1. 开启慢查询日志

MySQL的慢查询日志可以记录所有执行慢的SQL。先把慢查询日志开了，看看哪些SQL慢。

# my.cnf里配置
slow_query_log = 1
long_query_time = 1   # 超过1秒的SQL记录下来
slow_query_log_file = /var/log/mysql/slow.log

2. 用explain分析慢SQL

找到慢SQL之后，用explain看看它的执行计划，看看有没有走索引，是不是全表扫描。

3. 看数据库的状态

用下面的命令看看数据库当前的状态：

SHOW GLOBAL STATUS;

重点关注这几个指标：

• Slow_queries：慢查询的数量
• Threads_connected：当前连接数
• Innodb_row_reads：读了多少行数据
• Innodb_data_reads：读了多少数据

二、SQL和索引优化

这是最常用、也是性价比最高的优化方式。很多时候，加个索引，SQL性能就能提升几十倍。

1. 加合适的索引

哪些字段要加索引

• WHERE条件里经常用到的字段
• 表连接的字段
• ORDER BY、GROUP BY的字段
• 区分度高的字段

索引的注意点

• 不要加太多索引，索引也占空间，而且插入更新的时候要维护索引
• 联合索引要注意最左前缀原则
• 尽量不要在索引字段上做函数操作，不然索引会失效

2. 避免索引失效的情况

常见的索引失效场景：

• WHERE条件里对索引字段做函数操作
• WHERE条件里用了!=、<>、NOT IN
• LIKE以%开头
• 联合索引不满足最左前缀
• 字段类型不匹配（比如字符串字段用数字去查）

3. 优化SQL写法

不要用SELECT *

只查需要的字段，不要查所有字段。这样不仅减少数据传输，还能用到覆盖索引。

避免大分页

比如LIMIT 100000, 10，这种越往后越慢。优化方法：用上次的最大id来查。

避免在WHERE里做计算

比如WHERE age + 1 = 18，这样索引会失效。应该写成WHERE age = 17。

小表驱动大表

联表查询的时候，小表在前，大表在后，性能更好。

三、表结构优化

1. 选择合适的数据类型

• 能用数字类型就不要用字符串
• 能用短的就不要用长的，比如用TINYINT不要用INT
• 时间类型用DATETIME或者TIMESTAMP，不要用字符串存时间

2. 避免太多字段

一张表不要有太多字段，字段太多的话，数据页能放的行就少，IO次数就多。不常用的字段可以拆到另一张表里。

3. 适当加冗余字段

有些字段虽然可以联表查出来，但是如果经常用到，可以考虑加冗余字段，减少联表查询。

4. 大表做归档

如果表里有很多历史数据，但是查询的时候很少用到，可以把历史数据归档到另一张表里，主表只保留最近的数据。这样主表数据量小了，查询就快了。

四、配置参数优化

MySQL的默认配置很多都不是最优的，需要根据自己的服务器配置调一下。

1. innodb_buffer_pool_size

这个是InnoDB的缓冲池大小，是最重要的参数。一般设置成服务器内存的50%~70%。如果你的服务器内存是16G，那这个参数就设成8G~10G。

这个参数设大了，很多数据和索引都能在内存里，就不用读磁盘了，性能会好很多。

2. innodb_log_file_size

这个是redo log的大小，一般设成256M~1G。这个参数影响写性能，太小的话会频繁刷盘。

3. max_connections

最大连接数，根据你的业务量调整，一般设成500~1000就够了。不要设太大，不然每个连接都占内存，反而会拖慢性能。

4. query_cache

MySQL 8.0已经把查询缓存去掉了，因为查询缓存的命中率不高，而且维护成本很高。如果是老版本的MySQL，建议把查询缓存关了。

五、架构层面优化

如果SQL和索引都优化过了，还是不行，那就要从架构层面下手了。

1. 读写分离

主库写，从库读，把读的压力分散到多个从库上。大部分网站都是读多写少，读写分离效果很明显。

2. 加缓存

把热点数据放到Redis里，直接从Redis读，不用查数据库。这是提升性能最明显的方式。

3. 分库分表

单表数据量太大了，到了千万级甚至亿级，那就分库分表，把数据分散到多个表或者多个数据库里。

4. 用更好的硬件

如果前面的优化都做了，还是不够，那只能加硬件了。比如用SSD代替机械硬盘，数据库的性能瓶颈很多时候都是磁盘IO。SSD的IOPS比机械硬盘高几个数量级，提升非常明显。

常见坑

坑1：上来就加机器，不优化SQL

很多同学一遇到性能问题就说：加机器！加内存！其实很多时候都是SQL写的烂，没加索引，优化一下SQL性能就能提升好几倍。加机器是最后手段，不是第一选择。

坑2：索引加了很多，但是都没用到

很多同学觉得索引加的越多越好，结果加了一堆索引，真正查询的时候一个都没用到。加索引之前要先explain一下，看看是不是真的能用到。

坑3：以为加了索引就一定快

很多同学以为只要加了索引，查询就一定快。其实不是，如果索引字段的区分度很低，比如性别只有男和女，那加了索引也没用，优化器可能还是会选择全表扫描。

坑4：调参数瞎调

很多同学看了网上的优化文章，把一堆参数往自己服务器上套，结果把数据库搞出问题了。调参数一定要一点点调，调完观察效果，不要一下子改一堆参数。

坑5：不做监控

很多同学数据库出问题了才知道性能差，平时根本没监控。一定要做监控，比如慢查询数量、CPU使用率、连接数这些指标，提前发现问题。

总结

MySQL性能优化的思路和步骤：

1. 先定位问题：开慢查询日志，找到慢SQL
2. SQL和索引优化：加合适的索引，优化SQL写法，这是性价比最高的
3. 表结构优化：选合适的数据类型，大表归档，适当加冗余字段
4. 配置参数优化：重点调innodb_buffer_pool_size
5. 架构层面优化：读写分离、加缓存、分库分表
6. 最后才是加硬件：比如用SSD、加内存

记住：性能优化是一个系统工程，要从多个方面下手。不要指望一招就能解决所有问题，先从最简单、最容易见效的地方下手，比如加索引、优化SQL，然后再一步步深入。

遇到问题加QQ23979811 协助处理