MySQL是最常用的关系型数据库很多网站应用都在用MySQL但是随着数据量和访问量的增加MySQL的性能问题就会显现出来慢查询连接数不够CPU占用高内存不足等等这些问题都会影响网站的性能和用户体验所以MySQL性能优化是每个后端开发者都必须掌握的技能。

我做MySQL性能优化很多年了优化过很多项目踩了很多坑也总结了很多经验今天就来分享一下MySQL性能优化的实战经验从慢查询分析到索引优化SQL优化配置优化架构优化等等全面分享希望能帮大家更好地优化MySQL提升性能。

一、性能优化的思路

首先说说MySQL性能优化的整体思路。

MySQL性能优化不是一蹴而就的也不是只优化某一个方面就能解决所有问题的它是一个系统工程需要从多个方面入手综合优化。

一般MySQL性能优化的思路是这样的：

  1. 先定位问题： 首先要找到性能瓶颈在哪里是慢查询？还是连接数不够？还是CPU内存不足？还是IO瓶颈？只有定位了问题才能针对性地优化。
  2. 优化SQL和索引： 大部分MySQL性能问题都是SQL写得不好或者索引没建好导致的所以首先要优化SQL语句和索引这是最有效成本最低的优化方式。
  3. 优化MySQL配置： 然后优化MySQL的配置比如缓存连接数线程等等让MySQL更充分地利用服务器的资源。
  4. 优化数据库结构： 然后优化数据库的表结构字段类型范式分表分库等等让数据存储更合理查询更高效。
  5. 优化架构： 最后如果以上优化都做了还是不能满足需求就需要优化架构比如读写分离主从复制分库分表引入缓存等等从架构层面提升性能。

这个顺序很重要要先从成本最低效果最明显的SQL和索引优化开始然后再配置结构架构不要一上来就搞分库分表读写分离那样成本很高而且可能SQL优化一下就解决了问题没必要搞那么复杂。

二、慢查询分析

定位MySQL性能问题最常用的方法就是分析慢查询慢查询就是执行时间超过指定阈值的SQL语句通过分析慢查询我们能找到哪些SQL执行慢然后针对性地优化。

首先要开启慢查询日志在my.cnf里配置：

[mysqld]
# 开启慢查询日志
slow_query_log = ON
# 慢查询日志文件路径
slow_query_log_file = /var/log/mysql/slow.log
# 慢查询阈值，单位秒，执行时间超过1秒的SQL，都会被记录
long_query_time = 1
# 记录没有使用索引的查询
log_queries_not_using_indexes = ON

这样开启之后执行时间超过1秒的SQL和没有使用索引的SQL都会被记录到慢查询日志里。

然后我们就能分析慢查询日志了最常用的工具是mysqldumpslow这是MySQL自带的慢查询分析工具：

# 查看执行时间最长的10条SQL
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 查看访问次数最多的10条SQL
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 查看返回记录集最多的10条SQL
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

mysqldumpslow会把相似的SQL归类统计执行次数总时间平均时间等等很方便我们找到最需要优化的SQL。

除了mysqldumpslow还有一个更强大的工具叫pt-query-digest是Percona Toolkit里的一个工具能更详细地分析慢查询生成报告很好用推荐大家使用。

找到慢查询之后我们就能用EXPLAIN分析这条SQL的执行计划看看为什么慢是全表扫描？还是索引没用上？还是临时表？还是文件排序？等等然后针对性地优化。

EXPLAIN的用法：

EXPLAIN SELECT * FROM users WHERE username = 'admin';

EXPLAIN会返回SQL的执行计划包括idselecttypetabletypepossiblekeyskeykey_lenrefrowsExtra等等字段我们重点看这几个：

  • type： 访问类型最好的是consteq_refrefrange最差的是ALL全表扫描如果是ALL就要优化加索引。
  • key： 实际使用的索引如果是NULL说明没有使用索引要优化。
  • rows： 扫描的行数越少越好如果扫描行数很多说明索引没建好或者SQL写得不好。
  • Extra： 额外信息如果出现Using filesort文件排序或者Using temporary临时表就要优化因为这两个很影响性能。

通过EXPLAIN我们能清楚地知道SQL为什么慢然后针对性地优化。

三、索引优化

索引是MySQL性能优化最重要的部分大部分慢查询都是因为没有合适的索引导致的所以索引优化是重中之重。

先说说索引的类型：

  1. 普通索引： 最基本的索引没有任何限制。
  2. 唯一索引： 索引列的值必须唯一允许空值。
  3. 主键索引： 特殊的唯一索引不允许空值一张表只能有一个主键。
  4. 组合索引： 多个字段组成的索引遵循最左前缀原则。
  5. 全文索引： 用于全文搜索只能在CHARVARCHARTEXT类型的字段上使用。

索引优化的原则和经验：

1. 适合建索引的字段

  • 经常作为WHERE查询条件的字段
  • 经常作为ORDER BY排序的字段
  • 经常作为GROUP BY分组的字段
  • 经常作为JOIN连接条件的字段
  • 区分度高的字段比如用户名手机号等等不要在性别这种区分度低的字段上建索引

2. 不适合建索引的字段

  • 数据量小的表不需要建索引
  • 经常更新的字段不要建太多索引因为更新的时候也要更新索引影响性能
  • 区分度低的字段不要建索引比如性别状态等等
  • TEXTBLOB类型的字段不要建索引或者只建前缀索引

3. 组合索引和最左前缀原则

组合索引就是多个字段组成的索引比如INDEX(name, age, gender)组合索引遵循最左前缀原则就是查询的时候必须从索引的最左边的字段开始才能用上索引。

比如组合索引INDEX(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 c = 3不能用上索引
  • WHERE a = 1 AND c = 3只能用上a的索引c用不上

所以建组合索引的时候要把最常用的查询字段放在最左边区分度高的字段放在前面。

4. 索引不是越多越好

很多人以为索引越多越好其实不是索引太多会有这些问题：

  • 占用更多的磁盘空间
  • 插入更新删除的时候要维护索引影响性能
  • 优化器选择索引的时候可能会选错影响性能

所以索引要适量只在需要的字段上建索引不要乱建索引。

5. 避免索引失效

有些情况会导致索引失效要注意避免：

  • 在索引列上使用函数运算比如WHERE YEAR(createtime) = 2017索引会失效应该改成WHERE createtime >= '2017-01-01' AND create_time < '2018-01-01'
  • 隐式类型转换比如字段是字符串查询的时候用数字WHERE phone = 13800138000索引会失效应该用字符串WHERE phone = '13800138000'
  • 使用LIKE以通配符开头比如WHERE name LIKE '%admin'索引会失效WHERE name LIKE 'admin%'能用上索引
  • 使用OR连接条件其中一个条件没有索引整个索引都会失效要注意OR两边的条件都要有索引或者用UNION代替
  • 使用!=<>NOT IN等等可能会导致索引失效要注意

四、SQL优化

除了索引SQL语句本身的优化也很重要很多SQL写得不好即使有索引也会慢下面分享一些SQL优化的经验：

1. 避免SELECT *

不要用SELECT 查询所有字段要只查询需要的字段因为SELECT 会读取所有字段浪费IO和内存而且不能利用覆盖索引性能差所以要明确写出需要的字段比如SELECT id, name, age FROM users不要SELECT * FROM users。

2. 避免子查询尽量用JOIN

子查询性能比较差特别是IN子查询很容易慢尽量用JOIN代替子查询性能更好比如：

-- 子查询性能差
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE status = 1);

-- 用JOIN代替性能更好
SELECT u.* FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE o.status = 1;

3. 避免大事务

大事务会占用锁时间长影响并发还可能导致主从延迟所以要避免大事务尽量把大事务拆成小事务比如批量插入更新的时候分批处理每批1000条不要一次处理几万条。

4. 分页优化

LIMIT分页当偏移量很大的时候比如LIMIT 100000, 10会很慢因为要扫描100000条然后丢弃只返回10条优化方法：

  • 用覆盖索引先查出id再根据id查数据
  • 用WHERE条件限制比如WHERE id > 100000 LIMIT 10代替LIMIT 100000, 10

比如：

-- 慢的分页
SELECT * FROM users ORDER BY id LIMIT 100000, 10;

-- 优化后的分页
SELECT * FROM users WHERE id > 100000 ORDER BY id LIMIT 10;

-- 或者用覆盖索引
SELECT * FROM users u INNER JOIN (SELECT id FROM users ORDER BY id LIMIT 100000, 10) t ON u.id = t.id;

5. 避免在WHERE里对字段做运算和函数

前面索引部分也提到了在索引列上使用函数运算会导致索引失效所以要避免比如：

-- 索引失效
SELECT * FROM users WHERE YEAR(create_time) = 2017;

-- 优化索引生效
SELECT * FROM users WHERE create_time >= '2017-01-01' AND create_time < '2018-01-01';

6. 批量操作优化

批量插入更新删除的时候要注意优化比如批量插入用INSERT INTO table VALUES (...), (...), (...)一条SQL插入多条比一条条插入性能好很多批量更新也可以用CASE WHEN或者临时表优化不要一条条更新。

7. 避免ORDER BY和GROUP BY产生临时表和文件排序

ORDER BY和GROUP BY如果没有合适的索引会产生临时表和文件排序很影响性能要给ORDER BY和GROUP BY的字段建合适的索引避免临时表和文件排序。

五、配置优化

SQL和索引优化之后我们还能优化MySQL的配置让MySQL更充分地利用服务器的资源下面分享一些常用的配置优化：

1. 内存相关配置

  • innodbbufferpool_size： 这是InnoDB最重要的配置InnoDB的数据和索引缓存大小一般设置为服务器内存的60%-70%比如服务器8G内存设置为5-6G这个越大越好能缓存更多数据减少磁盘IO但是不要设置太大导致系统内存不够。
  • innodblogbuffer_size： InnoDB的日志缓存大小一般设置为8M-16M大事务多的话可以调大。
  • keybuffersize： MyISAM的索引缓存大小如果用InnoDB这个不用设置太大一般64M就够了。
  • querycachesize： 查询缓存大小但是MySQL 5.7之后已经不推荐用查询缓存了因为并发高的时候锁竞争大反而影响性能建议关闭设置为0。

2. 连接相关配置

  • max_connections： 最大连接数根据服务器配置和应用需求设置一般500-1000不要设置太大不然内存不够连接太多也会影响性能。
  • wait_timeout： 非交互连接超时时间一般设置为600秒不要设置太长不然空闲连接占用资源。
  • interactivetimeout： 交互连接超时时间和waittimeout保持一致。

3. InnoDB相关配置

  • innodbfileper_table： 每个表单独一个表空间开启ON这样删除表的时候能回收空间不然所有表都在一个表空间删除表空间不回收。
  • innodbflushlogattrx_commit： 事务日志刷盘策略0：每秒刷一次性能最好但是崩溃会丢1秒数据1：每次事务提交都刷最安全但是性能差2：每次事务提交写到系统缓存每秒刷一次性能和安全折中一般生产环境设置为1保证数据安全性能要求高的话可以设置为2。
  • innodbflushmethod： InnoDB数据刷盘方法Linux下设置为O_DIRECT避免双重缓存提高性能。
  • innodblockwait_timeout： 行锁等待超时时间一般设置为50秒避免长事务占用锁时间长。

4. 其他配置

  • charactersetserver： 服务器默认字符集设置为utf8mb4支持emoji和所有中文。
  • collationserver： 排序规则设置为utf8mb4general_ci。
  • maxallowedpacket： 最大包大小一般设置为16M-64M避免大SQL或者大数据插入失败。
  • tmptablesize： 临时表大小一般设置为64M-128M避免临时表太大写到磁盘。
  • maxheaptablesize： 内存表大小和tmptable_size保持一致。

配置优化要根据服务器的配置和应用的特点来调整不是一成不变的要慢慢调观察性能找到最合适的配置。

六、表结构优化

除了SQL索引配置表结构的优化也很重要合理的表结构能提高查询效率减少存储空间下面分享一些表结构优化的经验：

1. 选择合适的字段类型

字段类型要尽量小够用就行不要用大的类型比如年龄用TINYINT就行不要用INT状态用TINYINT就行不要用VARCHAR小的字段类型能节省存储空间也能提高查询效率因为IO更少缓存能缓存更多数据。

常用的字段类型选择：

  • 整数： TINYINT（1字节）SMALLINT（2字节）MEDIUMINT（3字节）INT（4字节）BIGINT（8字节）根据数据范围选择最小的够用的。
  • 字符串： CHAR固定长度适合长度固定的比如手机号身份证号VARCHAR可变长度适合长度不固定的要设置合适的长度不要太大。
  • 时间： DATETIME8字节范围大TIMESTAMP4字节范围小但是占用小根据需求选择一般用DATETIME更方便。
  • 小数： DECIMAL精确小数适合金额等等不要用FLOATDOUBLE因为有精度问题。

2. 避免NULL值

尽量不要让字段为NULLNULL会占用额外的空间也会让索引查询更复杂性能差所以尽量给字段设置默认值比如字符串默认空字符串整数默认0等等不要用NULL。

3. 适当反范式

范式能减少数据冗余但是有时候适当反范式加一些冗余字段能减少JOIN提高查询效率比如订单表冗余用户名用户头像等等这样查询订单的时候不用JOIN用户表性能更好当然反范式要注意数据一致性更新的时候要同时更新冗余字段。

4. 大表拆分

如果表数据量很大比如几百万上千万查询就会慢这时候可以考虑分表比如按时间分表按id范围分表按hash分表等等把大表拆成小表提高查询效率当然分表会增加开发复杂度要根据实际情况选择。

七、架构优化

如果以上优化都做了还是不能满足高并发的需求就需要从架构层面优化了下面分享一些常用的架构优化方案：

1. 读写分离

大部分应用都是读多写少所以可以用主从复制读写分离主库负责写从库负责读把读请求分散到从库减轻主库的压力提高并发能力MySQL主从复制很成熟配置也不复杂推荐使用。

2. 引入缓存

对于热点数据经常查询但是不经常修改的数据可以引入缓存比如RedisMemcached把数据缓存到内存查询的时候先查缓存缓存有就直接返回没有再查数据库然后缓存起来这样能大大减轻数据库的压力提高性能缓存是高并发必备的优化手段。

3. 分库分表

如果数据量特别大单库单表扛不住就需要分库分表把数据分散到多个库多个表提高并发能力和查询效率分库分表有很多中间件比如Sharding-JDBCMyCat等等能帮我们实现分库分表当然分库分表复杂度很高要谨慎使用不到万不得已不要用。

4. 搜索引擎

对于复杂的全文搜索多条件组合查询MySQL可能性能不好这时候可以引入搜索引擎比如ElasticsearchSolr把数据同步到搜索引擎复杂的查询走搜索引擎MySQL只负责简单的查询和写入这样能大大提高查询性能。

八、踩坑经验

最后分享一些我做MySQL性能优化踩过的坑和经验：

1. 不要盲目加索引

很多人遇到慢查询就给字段加索引但是有时候加了索引也没用或者反而更慢要先用EXPLAIN分析执行计划看看为什么慢再针对性地加索引不要盲目加。

2. 索引要注意区分度

建索引要注意字段的区分度区分度低的字段比如性别状态建了索引也没什么用优化器可能不会用因为扫描索引还不如全表扫描快所以要在区分度高的字段上建索引。

3. 注意隐式类型转换

这个前面也提到了隐式类型转换会导致索引失效很常见比如字段是字符串查询用数字就会隐式转换索引失效要特别注意查询的时候类型要一致。

4. 不要用SELECT *

这个前面也提到了SELECT *性能差还不能利用覆盖索引要只查询需要的字段养成好习惯。

5. 注意MySQL版本

不同的MySQL版本性能特性都不一样比如MySQL 5.7比5.6性能好很多MySQL 8.0又比5.7好很多所以尽量用新的稳定版本能获得更好的性能和更多的特性当然升级版本要注意兼容性测试好了再升级。

6. 定期维护

MySQL要定期维护比如定期优化表OPTIMIZE TABLE整理碎片定期检查慢查询优化SQL定期备份数据等等不要装好了就不管了定期维护能让MySQL保持好的性能。

九、写在最后

MySQL性能优化实战：从慢查询到高并发的经验总结。

MySQL性能优化是一个系统工程需要从SQL索引配置表结构架构等多个方面综合优化不是一蹴而就的也没有银弹要根据实际情况慢慢调慢慢优化。

但是大部分MySQL性能问题都是SQL写得不好或者索引没建好导致的所以我们首先要把SQL和索引优化做好这是成本最低效果最明显的优化方式然后再考虑配置结构架构的优化。

希望这篇分享能帮大家更好地优化MySQL提升性能也希望大家都能写出高效的SQL建好合适的索引让MySQL跑得更快更稳。

最后用一句话结尾：

"MySQL性能优化没有最好只有更好要不断学习不断实践不断优化才能让MySQL保持最佳状态。"

祝大家优化顺利MySQL跑得又快又稳！