MySQL是最常用的关系型数据库几乎每个项目都会用到但是很多人对MySQL的使用只停留在建表写SQL查询的层面对性能优化了解不多结果项目上线之后随着数据量和访问量的增长数据库越来越慢慢查询越来越多甚至数据库被打挂影响业务。

我做过很多MySQL的性能优化从慢查询优化到索引优化到SQL优化到架构优化到高并发踩了很多坑也总结了很多经验今天就来分享一下MySQL性能优化的实战经验从慢查询到高并发全面分享。

一、性能优化的思路

在讲具体的优化方法之前先说说性能优化的整体思路因为思路比具体的方法更重要有了正确的思路才能高效地做优化。

MySQL性能优化我一般按照以下几个步骤来做：

  1. 发现问题： 首先要发现性能问题比如系统变慢了接口响应时间长了数据库CPU高了慢查询多了等等要通过监控告警用户反馈等方式及时发现问题。
  1. 定位问题： 发现问题之后要定位问题出在哪里是SQL慢了？还是索引没建好？还是锁等待？还是连接数不够？还是硬件瓶颈？要通过慢查询日志explainshow processlistshow statusperformance_schema等工具定位具体的问题。
  1. 分析原因： 定位问题之后要分析为什么会有这个问题比如SQL慢是因为没索引？还是索引没生效？还是数据量太大？还是SQL写得不好？要找到根本原因而不是只看表面现象。
  1. 解决问题： 找到原因之后就针对性地解决问题比如没索引就加索引SQL写得不好就优化SQL数据量太大就分库分表硬件瓶颈就升级硬件等等。
  1. 验证效果： 解决问题之后要验证优化的效果比如SQL的执行时间是不是缩短了系统的响应时间是不是变快了数据库的CPU是不是降下来了等等要确认优化确实有效没有引入新的问题。
  1. 预防问题： 最后要总结经验建立规范预防类似的问题再次发生比如制定SQL编写规范索引设计规范上线前SQL审核慢查询监控等等从流程上避免性能问题。

这个思路不仅适用于MySQL性能优化也适用于其他的性能优化甚至其他的问题排查和解决。

二、慢查询分析

慢查询是MySQL性能优化最常见的切入点大部分性能问题都和慢查询有关所以首先要学会分析慢查询。

1. 开启慢查询日志：

MySQL有慢查询日志会记录执行时间超过指定阈值的SQL默认是关闭的需要手动开启。

可以在my.cnf（Linux）或者my.ini（Windows）里配置：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

这里slowquerylog=1开启慢查询日志slowquerylogfile指定日志文件路径longquerytime=1指定执行时间超过1秒的SQL才记录logqueriesnotusing_indexes=1记录没有使用索引的SQL即使执行时间没超过阈值。

也可以在MySQL运行时动态设置：

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;

不过动态设置重启MySQL之后就失效了所以最好在配置文件里也设置一下。

2. 分析慢查询日志：

慢查询日志记录了所有的慢SQL但是日志是文本格式直接看不太方便特别是慢SQL很多的时候所以需要用工具分析。

MySQL自带了一个慢查询分析工具mysqldumpslow可以分析慢查询日志统计各种SQL的执行次数平均执行时间总执行时间等等。

常用的命令：

# 查看，执行时间最长的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

这里-s指定排序方式t按总时间c按次数r按返回的记录数-t指定返回前N条。

除了mysqldumpslow还有一些第三方工具比如pt-query-digest（Percona Toolkit里的）功能更强大分析更详细推荐使用。

3. explain分析执行计划：

找到慢SQL之后要用explain分析SQL的执行计划看看MySQL是怎么执行这条SQL的有没有用上索引扫描了多少行等等。

用法很简单在SQL前面加上explain就行：

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

explain会返回一些字段每个字段都有含义重点关注以下几个：

  • type： 访问类型性能从好到差依次是system > const > eqref > ref > range > index > ALL一般来说至少要达到range级别最好是ref或者eqref如果是ALL就是全表扫描性能很差要优化。
  • key： 实际使用的索引如果是NULL就是没有使用索引要看看为什么没用到索引是没建索引？还是索引失效了？
  • rows： 扫描的行数这个值越小越好如果扫描的行数很多但是返回的行数很少说明SQL或者索引有问题要优化。
  • Extra： 额外信息比如Using index（覆盖索引好）Using where（用了where过滤）Using temporary（用了临时表不好）Using filesort（用了文件排序不好）等等如果出现Using temporary或者Using filesort要注意可能需要优化。

通过explain就能知道SQL的执行计划找到性能瓶颈然后针对性地优化。

三、索引优化

索引是MySQL性能优化最常用也是最有效的手段很多慢查询都是因为没有索引或者索引没建好导致的所以索引优化非常重要。

1. 索引的类型：

MySQL常用的索引有以下几种：

  • 普通索引： 最基本的索引没有任何限制就是用来加速查询的。
  • 唯一索引： 和普通索引类似但是索引列的值必须唯一允许有空值如果是组合索引列值的组合必须唯一。
  • 主键索引： 特殊的唯一索引不允许有空值一般建表的时候指定主键就会自动创建主键索引一张表只能有一个主键索引。
  • 组合索引： 多个字段组成的索引比如INDEX(name, age)组合索引遵循最左前缀原则查询的时候要从索引的最左边开始才能用上索引。
  • 全文索引： 用来做全文搜索的适合大文本的内容搜索比如文章标题内容的搜索。

2. 索引设计的原则：

索引不是越多越好也不是随便建就行要遵循一些原则：

  • 适合索引的列： 经常作为WHERE条件的列经常ORDER BYGROUP BY的列经常JOIN的列适合建索引因为这些列查询频率高建索引能明显提升性能。
  • 不适合索引的列： 数据量很小的表不用建索引因为全表扫描可能比走索引还快；区分度很低的列比如性别只有男/女两个值不适合建索引因为索引过滤不了多少行效果不好；经常更新的列要谨慎建索引因为更新的时候索引也要更新会增加更新的开销；textblob等大字段不适合建索引因为索引会很大。
  • 组合索引的最左前缀： 组合索引要遵循最左前缀原则查询的时候条件要从索引的最左边开始才能用上索引比如组合索引INDEX(a, b, c)WHERE a=1WHERE a=1 AND b=2WHERE a=1 AND b=2 AND c=3都能用上索引但是WHERE b=2WHERE c=3WHERE b=2 AND c=3就用不上索引因为没有从最左边的a开始。
  • 索引列不要参与计算： WHERE条件里索引列不要参与计算或者函数比如WHERE YEAR(createtime) = 2017这样索引会失效因为MySQL要对每一行都计算YEAR(createtime)没法用索引要改成WHERE createtime >= '2017-01-01' AND createtime < '2018-01-01'这样就能用上索引了。
  • 避免索引失效： 还有一些情况会导致索引失效比如索引列用了!=或者<>可能失效；索引列用了OR两边都要有索引不然失效；索引列用了LIKE以%开头会失效比如LIKE '%abc'但是LIKE 'abc%'不会失效；字符串不加引号可能失效比如phone是字符串WHERE phone = 13800138000没加引号可能失效要加引号WHERE phone = '13800138000'。
  • 覆盖索引： 如果索引包含了查询需要的所有字段就不需要回表查询数据了这就是覆盖索引性能很好比如SELECT name, age FROM users WHERE name = 'test'如果有组合索引INDEX(name, age)那么索引里已经有name和age了直接从索引里取数据就行不用回表查询整行数据性能很好所以经常查询的字段可以考虑加到组合索引里做成覆盖索引。
  • 索引不是越多越好： 索引会占用存储空间而且插入更新删除的时候索引也要同步更新会增加写操作的开销所以索引不是越多越好要合理建索引只建需要的索引不要乱建索引。

3. 索引优化的实战：

举几个索引优化的例子：

例子1：用户表users有username字段经常用username查询但是没建索引导致全表扫描很慢优化方法给username建普通索引或者唯一索引（如果username唯一）：

ALTER TABLE users ADD INDEX idx_username(username);

例子2：订单表orders经常用userid和status查询比如WHERE userid = 123 AND status = 1这时候建组合索引比两个单独索引好：

ALTER TABLE orders ADD INDEX idx_user_status(user_id, status);

因为组合索引能同时用user_id和status过滤而两个单独索引MySQL一般只会用其中一个过滤效果不如组合索引。

例子3：文章表articles经常查询SELECT id, title FROM articles WHERE categoryid = 1 ORDER BY createtime DESC这时候可以建组合索引INDEX(categoryid, createtime, id, title)这样既能用categoryid过滤又能用createtime排序还覆盖了id和title不用回表性能很好。

当然组合索引也不要太长字段太多索引会很大更新开销也大要权衡。

四、SQL优化

除了索引优化SQL本身的优化也很重要很多时候SQL写得不好即使有索引也会很慢所以要学会写高效的SQL。

*1. 避免SELECT ：**

很多人写SQL喜欢用SELECT 查询所有字段这是一个不好的习惯因为SELECT 会查询所有字段包括不需要的大字段比如textblob会增加网络传输的数据量也可能导致无法使用覆盖索引性能不好。

所以要明确写出需要的字段比如SELECT id, title, create_time FROM articles而不是SELECT * FROM articles只查需要的字段。

2. 避免子查询尽量用JOIN：

子查询有时候性能不好特别是IN子查询里面的结果集很大的时候性能会很差因为MySQL对子查询的优化不是很好有时候会全表扫描。

所以尽量把子查询改成JOIN比如：

-- 子查询性能可能不好
SELECT * FROM articles WHERE user_id IN (SELECT id FROM users WHERE status = 1);

-- 改成JOIN性能更好
SELECT a.* FROM articles a JOIN users u ON a.user_id = u.id WHERE u.status = 1;

当然也不是所有子查询都不好有些子查询性能也不错要具体情况具体分析但是大部分情况JOIN性能更好也更清晰。

3. 避免大事务长事务：

事务太大太长会导致锁等待回滚段太大主从延迟等等问题性能不好也容易出问题。

所以要尽量避免大事务长事务把大事务拆成小事务比如批量更新不要一次更新几十万条要分批每次更新几百条几千条每批一个事务这样锁的时间短影响小。

4. 分页优化：

分页是很常见的需求但是当数据量很大的时候LIMIT offset, size性能会很差因为MySQL要先扫描offset条然后再返回size条offset越大扫描的行数越多越慢。

比如LIMIT 1000000, 10要扫描1000010行然后返回10行很慢。

分页优化有几种方法：

  • 利用覆盖索引延迟关联： 先通过覆盖索引查到需要的id然后再回表查询其他字段比如：
-- 普通分页慢
SELECT * FROM articles ORDER BY id LIMIT 1000000, 10;

-- 延迟关联快
SELECT a.* FROM articles a JOIN (SELECT id FROM articles ORDER BY id LIMIT 1000000, 10) t ON a.id = t.id;

因为子查询用了覆盖索引（id是主键索引里就有id）扫描很快然后再用id回表查询10条整体比直接分页快很多。

  • 利用WHERE条件缩小范围： 如果能知道上一页的最后一条的id或者时间可以用WHERE条件缩小范围比如：
-- 上一页最后一条id是1000000查下一页
SELECT * FROM articles WHERE id > 1000000 ORDER BY id LIMIT 10;

这样就不用扫描前面的1000000条了直接从1000000之后开始查很快这种方式适合滚动加载或者只有上一页/下一页的场景不适合跳页。

  • 业务上限制分页深度： 很多时候用户不会翻到很后面的页所以可以业务上限制最多翻到多少页比如最多100页超过就不让翻了或者提示数据太多请缩小搜索范围这样也能避免深分页的性能问题。

5. 批量操作优化：

批量插入批量更新批量删除要用批量的方式而不是循环一条条执行因为一条条执行每次都有网络开销事务开销SQL解析开销性能很差。

比如批量插入：

-- 一条条插入慢
INSERT INTO users(name, age) VALUES('a', 1);
INSERT INTO users(name, age) VALUES('b', 2);
INSERT INTO users(name, age) VALUES('c', 3);

-- 批量插入快
INSERT INTO users(name, age) VALUES('a', 1), ('b', 2), ('c', 3);

批量插入一次插入多条性能比一条条好很多但是也不要一次插入太多比如一次插入几万条可能会导致锁表或者主从延迟一般一次几百条几千条比较合适。

批量更新也可以用CASE WHEN或者临时表批量更新而不是循环一条条更新。

6. 其他SQL优化技巧：

  • ORDER BY的字段要有索引： 如果ORDER BY的字段有索引MySQL就能直接按索引顺序返回不用排序性能好如果没有索引就要filesort性能差所以经常排序的字段要建索引。
  • GROUP BY的字段要有索引： 和ORDER BY类似GROUP BY的字段有索引性能好没有索引就要临时表排序性能差。
  • 避免在WHERE里用函数计算： 前面索引优化讲过索引列不要参与计算函数会导致索引失效。
  • 用EXISTS代替IN： 有时候EXISTS比IN性能好特别是子查询结果集大的时候当然要具体情况具体分析。
  • 合理使用临时表： 复杂的查询可以考虑用临时表分步执行有时候比一个复杂的大SQL性能好也更清晰但是临时表也有开销不要滥用。

五、表结构优化

除了索引和SQL表结构的设计也很重要好的表结构能从根本上提升性能不好的表结构再怎么优化SQL和索引效果也有限。

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

数据类型要合适不要太大也不要太小要根据实际情况选择：

  • 整数类型： TINYINT（1字节-128到127）SMALLINT（2字节-32768到32767）MEDIUMINT（3字节）INT（4字节）BIGINT（8字节）要根据数据的范围选择最小的够用的类型比如状态只有几个值用TINYINT就行不要用INT更不要用BIGINT节省空间也提升性能因为数据类型越小占用空间越小索引也越小查询越快。
  • 字符串类型： CHAR固定长度适合长度固定的比如手机号身份证号MD5值等等VARCHAR可变长度适合长度不固定的比如用户名标题等等VARCHAR的长度要合理不要太大比如VARCHAR(255)如果实际最长只有50就用VARCHAR(50)节省空间。
  • 时间类型： DATETIME8字节范围大TIMESTAMP4字节范围小（1970到2038）但是占用空间小如果时间在范围内优先用TIMESTAMP节省空间当然也要考虑时区的问题。
  • 避免NULL： 尽量不要让字段为NULL因为NULL会占用额外的空间也会让索引统计比较更复杂性能不好可以用默认值比如0''等等代替NULL。

2. 范式和反范式：

表结构设计有范式和反范式两种思路：

  • 范式： 就是按数据库范式设计消除数据冗余比如用户信息存在用户表文章信息存在文章表文章里只存user_id不存username这样数据不冗余更新的时候只需要更新用户表的username但是查询文章的时候要JOIN用户表才能拿到username查询可能慢。
  • 反范式： 就是适当冗余一些字段比如文章表除了存user_id还存username这样查询文章的时候不用JOIN用户表直接就能拿到username查询快但是数据冗余了用户修改username的时候要同时更新文章表的username更新麻烦也可能不一致。

实际项目中一般是范式和反范式结合用核心的经常更新的字段按范式不冗余避免不一致经常查询的很少更新的字段可以适当冗余提升查询性能要权衡查询性能和数据一致性。

3. 分库分表：

当单表数据量太大比如几千万几亿条单库压力太大这时候就要考虑分库分表了。

分库分表有几种方式：

  • 垂直分库： 按业务模块分库比如用户相关的表放用户库订单相关的表放订单库文章相关的表放文章库这样每个库的压力小了也便于业务解耦和扩展。
  • 垂直分表： 把一张大表按字段拆成多张表比如文章表有基本信息（id, title, author, create_time）和内容（content）内容是大字段查询列表的时候不需要就可以拆成文章基本信息表和文章内容表这样基本信息表很小查询列表很快看详情的时候再查内容表。
  • 水平分库分表： 把一张表的数据按某个维度拆分到多个库多个表比如订单表按user_id取模分到8个库每个库8个表共64个表这样每个表的数据量就小了查询快并发也高了。

分库分表能解决大数据量高并发的问题但是也会带来很多复杂性比如跨库JOIN分布式事务分页排序聚合等等都会变复杂所以不要过早分库分表当单表确实扛不住了再考虑而且要选择合适的分片键和分片策略。

六、架构优化

除了数据库本身的优化架构层面的优化也很重要能大大减轻数据库的压力。

1. 读写分离：

大部分系统都是读多写少查询的压力远大于写入的压力这时候可以用读写分离主库负责写从库负责读主库把数据同步到从库这样读的压力就分散到从库了主库只负责写压力小了。

MySQL自带了主从复制很容易搭建读写分离一般一主多从主库写多个从库读负载均衡读的并发能提升很多。

当然读写分离也有主从延迟的问题主库写入之后从库同步需要时间可能刚写入马上从从库读读不到所以对于一致性要求高的读比如刚写完马上读可以强制走主库或者等从库同步完成再读。

2. 缓存：

缓存是减轻数据库压力最有效的手段之一大部分读请求都可以通过缓存挡住不用查数据库数据库只需要处理缓存没命中的和写请求压力会小很多。

常用的缓存有RedisMemcached等等Redis功能更丰富用得更多。

缓存的常见策略：

  • Cache Aside： 最常用的读的时候先查缓存缓存有直接返回缓存没有查数据库然后写入缓存返回写的时候先更新数据库然后删除缓存这种方式简单常用但是有缓存不一致的问题不过一般业务能接受。
  • Read Through： 读的时候由缓存层负责查数据库写入缓存应用层只查缓存不用管数据库。
  • Write Through： 写的时候先写缓存由缓存层同步写数据库应用层只写缓存。
  • Write Behind： 写的时候只写缓存由缓存层异步写数据库性能好但是可能丢数据一致性差。

大部分业务用Cache Aside就行简单实用。

缓存还要注意缓存穿透缓存击穿缓存雪崩等问题：

  • 缓存穿透： 查询不存在的数据缓存没有每次都查数据库导致数据库压力大解决方法缓存空值或者用布隆过滤器过滤不存在的key。
  • 缓存击穿： 某个热点key过期了同时有大量请求查这个key都打到数据库导致数据库压力大解决方法加互斥锁只让一个请求查数据库更新缓存其他等待或者热点key永不过期。
  • 缓存雪崩： 大量key同时过期或者缓存挂了导致大量请求都打到数据库数据库被打挂解决方法过期时间加随机值避免同时过期缓存高可用降级限流等等。

3. 消息队列异步化：

对于一些非核心的写操作或者耗时的操作可以用消息队列异步化不用同步写数据库比如用户注册发邮件发短信记录日志更新统计等等都可以异步处理这样主流程快数据库的写压力也小了。

常用的消息队列有RabbitMQKafkaRocketMQ等等根据业务需求选择。

4. 搜索引擎：

对于复杂的搜索查询比如全文搜索多条件组合搜索聚合等等MySQL可能性能不好这时候可以用搜索引擎比如ElasticsearchSolr等等把数据同步到搜索引擎搜索查询走搜索引擎不用查MySQL减轻MySQL的压力也提升搜索的性能和功能。

七、硬件和配置优化

最后简单说说硬件和MySQL配置的优化这部分虽然不是最核心的但是也有一定的作用。

1. 硬件优化：

  • CPU： MySQL对CPU还是有要求的特别是高并发复杂查询的时候CPU要好核心数要多主频要高。
  • 内存： 内存非常重要MySQL会把数据索引缓存到内存里内存越大缓存越多磁盘IO越少性能越好所以尽量内存大一点最好能把热数据都缓存在内存里。
  • 磁盘： 磁盘IO是MySQL的常见瓶颈特别是数据量大内存不够的时候所以要用好的磁盘比如SSDNVMe SSD比机械硬盘快很多IOPS高延迟低性能好很多有条件尽量用SSD。
  • 网络： 高并发的时候网络也可能成为瓶颈要用千兆万兆网卡保证网络带宽。

2. MySQL配置优化：

MySQL的配置也很重要几个关键的配置：

  • innodbbufferpool_size： InnoDB的缓冲池大小这是最重要的配置缓冲池用来缓存数据索引一般设置为物理内存的50%-70%比如16G内存可以设置为8-10G缓冲池越大缓存越多性能越好但是不要设置太大不然系统其他进程没内存了。
  • innodblogfile_size： InnoDB的日志文件大小影响写入性能和崩溃恢复时间一般设置为256M或者512M不要太小也不要太大。
  • innodbflushlogattrx_commit： 事务提交时日志的刷盘策略0每秒刷一次性能最好但是可能丢1秒数据1每次提交都刷盘最安全但是性能差2每次提交写到操作系统缓存每秒刷盘性能和安全折中一般对数据安全要求高用1要求不高用2或者0。
  • sync_binlog： binlog的刷盘策略和上面的类似1每次提交都刷盘安全性能差0操作系统决定性能好可能丢数据一般主库用1从库可以用0。
  • max_connections： 最大连接数根据系统的并发设置不要太小不然连接不够也不要太大不然内存不够一般几百到几千根据实际情况。
  • innodbfileper_table： 每个表独立表空间建议开启这样每个表有自己的.ibd文件便于管理和回收空间。

配置优化要根据实际情况调整没有万能的配置要结合硬件业务数据量等等综合考虑也要通过压测监控验证效果。

八、写在最后

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

MySQL性能优化是一个系统工程不是只靠加几个索引就能解决的要从SQL索引表结构架构硬件配置等多个方面综合优化而且要根据实际情况具体分析没有银弹没有万能的方法。

但是只要掌握了正确的思路和方法一步步发现问题定位问题分析原因解决问题验证效果预防问题就能把MySQL的性能优化好支撑业务的发展。

希望这篇分享能帮大家更深入地了解MySQL性能优化少踩坑多解决问题。

最后用一句话结尾：

"MySQL性能优化没有最好只有更好要持续监控持续优化根据业务的发展不断调整才能让数据库一直保持良好的性能。"

祝大家都能把MySQL优化好系统又快又稳！