MySQL 8.0优化最佳实践：我总结了这些经验

MySQL 8.0是目前最主流的关系型数据库版本。我做了多年的MySQL开发和运维。积累了很多优化经验。本文是我总结的MySQL 8.0优化最佳实践。包括索引优化、SQL优化、配置优化、架构优化、监控等各个方面。这些都是我在实际项目中验证过的经验。希望能帮到正在做MySQL优化的你。让你的数据库跑得更快更稳。

一、写在前面

先说说我和MySQL的故事。

我做后端开发很多年了。MySQL是我用得最多的数据库。从最开始的5.5版本。到5.6、5.7。再到现在的8.0。我经历了MySQL的很多版本变化。也踩了很多坑。积累了很多优化经验。

MySQL 8.0是一个里程碑式的版本。它在性能、功能、安全性上都有很大的提升。比如窗口函数、CTE、不可见索引、降序索引、直方图等新功能。还有默认字符集改成了utf8mb4。默认认证插件改成了cachingsha2password。这些变化都需要我们去适应和学习。

这篇文章是我总结的MySQL 8.0优化最佳实践。大部分内容也适用于5.7版本。但是有些是8.0特有的。我会标注出来。

二、索引优化

索引是MySQL优化中最重要的部分。用对了索引。查询速度能提升几百倍。用错了索引。不仅没效果。还会影响写入性能。

1. 最左前缀原则

联合索引要遵循最左前缀原则。查询的时候要从索引的最左边开始匹配。不能跳过中间的列。

比如有一个联合索引(a, b, c)。查询条件where a=1 and b=2 and c=3可以用到索引。where a=1 and b=2也可以用到。where a=1也可以。但是where b=2 and c=3就用不到索引。因为跳过了a列。

很多人建联合索引的时候不注意顺序。导致查询用不到索引。建索引之前要分析查询语句。把经常用在where条件里的列放在前面。

2. 避免在索引列上做运算

不要在索引列上做函数运算或者类型转换。否则索引会失效。

比如where year(createtime) = 2021。这个查询用不到createtime上的索引。因为对列做了函数运算。应该改成where createtime >= '2021-01-01' and createtime < '2022-01-01'。

还有类型转换的问题。比如列是字符串类型。查询的时候用数字。where phone = 13800138000。这也会导致索引失效。应该用字符串。where phone = '13800138000'。

3. 覆盖索引

如果查询的列都包含在索引里。就不需要回表查询。这就是覆盖索引。覆盖索引能大大提升查询性能。

比如有一个联合索引(a, b)。查询select a, b from table where a = 1。这个查询的列a和b都在索引里。不需要回表。速度很快。

但是如果查询select a, b, c from table where a = 1。c不在索引里。就需要回表查询c的值。性能就差一些。

所以对于经常查询的列。可以考虑把它们加到联合索引里。做成覆盖索引。但是也要注意索引不能太宽。否则会影响写入性能。

4. 前缀索引

对于字符串比较长的列。比如URL、文章标题等。可以用前缀索引。只索引字符串的前几个字符。这样索引更小。查询更快。

比如alter table table add index(title(20))。这样只索引title的前20个字符。

但是前缀索引有一个缺点。不能用于order by和group by。也不能用于覆盖索引。所以要根据实际情况选择。

5. 不可见索引（8.0新特性）

MySQL 8.0支持不可见索引。可以把索引设置为不可见。优化器就不会使用这个索引。但是索引还是会正常维护。

这个功能在优化的时候非常有用。比如你想删除一个索引。但是不确定删除之后会不会影响性能。可以先把索引设置为不可见。观察一段时间。如果没有影响再真正删除。如果有影响。再把它设置回可见。

用法很简单。alter table table alter index idx_name invisible。或者visible。

6. 降序索引（8.0新特性）

MySQL 8.0支持降序索引。之前的版本虽然语法上支持desc。但是实际上还是升序存储的。8.0真正支持了降序存储。

降序索引在多列排序的时候很有用。比如order by a asc, b desc。如果索引是(a asc, b desc)。就可以直接用索引排序。不需要filesort。

三、SQL优化

索引是基础。SQL写得不好。有索引也白搭。

1. 避免select *

不要用select 。只查询需要的列。select 会读取所有列。浪费IO和内存。也不能用到覆盖索引。

特别是表中有大字段的时候。比如text、blob等。select *会把这些大字段也读出来。性能很差。

2. 分页优化

深分页是MySQL的一个常见性能问题。比如limit 100000, 10。MySQL需要扫描100010行。然后丢弃前100000行。只返回10行。非常浪费。

优化方法是用延迟关联。先查出需要的id。然后用id关联查询。比如select * from table where id in (select id from table where condition limit 100000, 10)。

或者用游标分页。用上一次查询的最后一个id作为条件。where id > last_id limit 10。这样就不需要扫描前面的行。

3. 避免大事务

大事务会占用很多资源。导致锁等待时间长。主从延迟大。甚至会导致主库宕机。

尽量把大事务拆分成小事务。比如批量更新的时候。每次更新1000条。提交一次。而不是一次更新100万条。

4. 用exists代替in

对于子查询。exists通常比in性能好。特别是子查询结果集很大的时候。

比如select from a where a.id in (select id from b where condition)。可以改成select from a where exists (select 1 from b where b.id = a.id and condition)。

但是这也不是绝对的。要看具体的数据量和索引情况。最好用explain分析一下。

5. 避免在where中使用or

or会导致索引失效。如果or两边的列都有索引。MySQL可能会用index merge。但是性能也不如分开查询好。

可以把or改成union。比如select from table where a = 1 or b = 2。改成select from table where a = 1 union select * from table where b = 2。

6. 批量插入

批量插入比单条插入性能好很多。因为减少了网络往返和事务开销。

比如insert into table values(1), (2), (3)。一次插入多条。比三次单条插入快很多。

但是也要注意批量插入的数量。不要一次插入太多。否则会导致大事务。一般一次插入1000到10000条比较合适。

四、配置优化

合理的配置能让MySQL发挥更好的性能。

1. InnoDB缓冲池大小

innodbbufferpool_size是最重要的配置参数。它决定了InnoDB能缓存多少数据和索引。一般设置为物理内存的50%到70%。

如果是专用的数据库服务器。可以设置为物理内存的70%到80%。但是要给操作系统和其他进程留足够的内存。

缓冲池越大。缓存命中率越高。磁盘IO越少。查询越快。但是也不是越大越好。太大了可能会导致系统交换内存。反而影响性能。

2. 日志缓冲区

innodblogbuffer_size是事务日志的缓冲区大小。一般设置为16M到64M。

如果有很多大事务。可以适当调大。减少日志写入磁盘的次数。

3. 连接数

max_connections是最大连接数。默认是151。对于高并发的应用可能不够。但是也不是越大越好。连接太多会消耗很多内存。

一般设置为500到2000。根据实际的并发量来调整。同时要注意每个连接的内存占用。MySQL每个连接会占用一定的内存。连接太多可能会OOM。

4. 临时表大小

tmptablesize和maxheaptable_size控制内存临时表的大小。如果临时表超过这个大小。就会转成磁盘临时表。性能会下降很多。

一般设置为64M到256M。如果有很多group by和order by的查询。可以适当调大。

5. 慢查询日志

开启慢查询日志。记录执行时间超过阈值的SQL。这样可以发现有问题的SQL。进行优化。

longquerytime一般设置为1秒。或者更严格的0.5秒。logqueriesnotusingindexes可以记录没有用到索引的查询。

但是要注意慢查询日志会占用一定的磁盘IO。不要在高负载的时候开启太多日志。

五、架构优化

当单库的性能达到瓶颈的时候。就需要考虑架构优化了。

1. 读写分离

读写分离是最常用的架构优化。主库负责写。从库负责读。这样可以把读压力分散到多个从库上。

MySQL 8.0的主从复制已经很成熟了。可以用原生的主从复制。也可以用中间件比如ProxySQL、MyCat等。

读写分离要注意主从延迟的问题。写之后立刻读可能会读不到最新的数据。对于一致性要求高的查询。可以走主库。

2. 分库分表

当单表的数据量太大的时候。比如超过几千万或者几亿。查询性能会下降。这时候就需要分库分表。

分库分表有垂直拆分和水平拆分。垂直拆分是把不同的业务表分到不同的库。水平拆分是把同一个表的数据按照某个维度分到多个表或者多个库。

分库分表会增加开发和运维的复杂度。比如跨库join、分布式事务、全局ID等问题。所以在数据量还没到那个程度的时候。不要过早分库分表。

3. 缓存

对于热点数据。可以用Redis等缓存来减轻数据库的压力。缓存能大大提升读取性能。

但是要注意缓存的一致性问题。更新数据库的时候要同步更新缓存。或者设置合理的过期时间。还要注意缓存穿透、缓存击穿、缓存雪崩等问题。

4. 归档历史数据

很多业务表会积累大量的历史数据。这些数据很少查询。但是会影响表的性能。可以把历史数据归档到其他表或者其他库。

比如订单表。只保留最近一年的订单在主表。更早的订单归档到历史表。这样主表的数据量就不会太大。查询性能也能保持。

六、监控和诊断

优化不是一次性的。需要持续监控和诊断。

1. 用explain分析执行计划

对于慢查询。首先要用explain看执行计划。看有没有用到索引。扫描了多少行。有没有filesort或者temporary table。

explain的输出有很多列。重点看type、key、rows、Extra这几列。type最好是ref或者range。不要是all（全表扫描）。key要看实际用到了哪个索引。rows是估计扫描的行数。Extra要看有没有Using filesort或者Using temporary。

2. 用performance_schema

MySQL 8.0的performance_schema功能很强大。可以监控各种性能指标。比如等待事件、内存使用、语句统计等。

可以用performance_schema来定位性能瓶颈。比如哪些SQL执行次数最多。哪些等待事件最耗时。哪些表的IO最多。

3. 用sys schema

sys schema是基于performanceschema的一组视图和存储过程。它把performanceschema的复杂数据整理成了更容易理解的视图。

比如sys.statementanalysis可以看SQL的统计信息。sys.schemaunusedindexes可以看没有用到的索引。sys.schemaredundant_indexes可以看冗余的索引。

4. 定期检查索引使用情况

索引不是建得越多越好。没用的索引会影响写入性能。浪费存储空间。要定期检查索引的使用情况。删除没用的索引和冗余的索引。

可以用sys.schemaunusedindexes查看没有用到的索引。用sys.schemaredundantindexes查看冗余的索引。

七、MySQL 8.0的新特性

最后说说MySQL 8.0的一些对优化有用的新特性。

1. 窗口函数

窗口函数可以很方便地实现分组排名、累计求和等功能。之前需要用复杂的子查询或者变量来实现。现在用窗口函数就可以了。而且性能更好。

比如row_number() over (partition by category order by score desc)。可以给每个分类的分数排名。

2. CTE（公共表表达式）

CTE可以让复杂的查询更清晰。用with子句定义临时结果集。然后在主查询中引用。比嵌套子查询更易读。也更容易优化。

3. 直方图

MySQL 8.0支持直方图统计。对于数据分布不均匀的列。直方图能让优化器更准确地估计行数。选择更好的执行计划。

可以用analyze table table update histogram on column来创建直方图。

4. 不可见索引

前面提到过。不可见索引在优化的时候非常有用。可以安全地测试删除索引的影响。

八、给新手的建议

如果你是MySQL优化新手。我有几个建议。

1. 先学会用explain

explain是MySQL优化最基本也是最重要的工具。一定要学会看explain的输出。能看懂执行计划。知道哪里有问题。

2. 索引是基础

大部分性能问题都是索引的问题。先把索引搞懂。知道什么时候建索引。建什么样的索引。索引什么时候会失效。

3. 不要过早优化

不要在系统还没上线的时候就做各种优化。先让系统跑起来。有了真实的数据和流量。再根据监控和慢查询来优化。

4. 优化是持续的

优化不是一次性的。业务在变。数据在增长。性能问题会不断出现。要建立持续的监控和优化机制。

九、写在最后

MySQL优化是一个系统工程。需要从索引、SQL、配置、架构等多个层面入手。没有银弹。也没有万能的优化方案。要根据具体的业务和数据情况来选择合适的优化方法。

这篇文章总结了我多年的MySQL优化经验。希望能帮到大家。但是MySQL的知识很多。我不可能在一篇文章里讲完。还有很多细节需要大家在实践中去学习和体会。

最后用一句话结束本文："MySQL优化没有捷径。只有不断地监控、分析、实践。才能让数据库跑得更快更稳。"希望每一个开发者都能掌握MySQL优化的技能。写出高性能的应用。