MySQL(七)性能调优

2024 年 4 月 22 日 星期一(已编辑)
/
62
这篇文章上次修改于 2024 年 7 月 24 日 星期三,可能部分内容已经不适用,如有疑问可询问作者。

阅读此文章之前,你可能需要首先阅读以下的文章才能更好的理解上下文。

MySQL(七)性能调优

怎么查看一条语句是否走了索引

type字段就是描述了找到所需数据时使用的扫描方式是什么,常见扫描类型的执行效率从低到高的顺序为:

A‖(全表扫描);

index(全索引扫描);

range(索引范围扫描);

ref(非唯一索引扫描);

eq_ref(唯一索引扫描);

const(结果只有一条的主键或唯一索引扫描)。

可以通过explain查看SQL的执行计划,关注type字段,这个字段表明SQL扫描的方式,如果type字段不是all或者index就代表是索引扫描的方式,这种情况就代表SQL走了索引,并且我们还可以通过key字段,看这条查询用了哪个索引字段来走索引,如果key为null,也代表没有走索引。

extra字段中的using index和using where的区别

  • using index表示查询使用了索引覆盖,不会回表,这个可以提高查询效率。
  • using where表示MySQL的存储引擎返回给server层的数据并不一定满足where子句的条件,所以MySQL从存储引擎掌到的数据,还得在server层进行了where子句的条件判断,来过滤出最终sql所需要查询的数据。

怎么找到慢SQL

可以开启慢查询日志,MySQL就会自动将执行比较慢的SQL语句记录在慢查询日志中,具体多慢我们可以自己设置的,比如设置3秒,那么MySQL就会将执行超过3秒的SQL语句记录在慢查询日志中

如何优化慢SQL

  • 优化数据访问:要先确认这条查询语句是否查询了不必要的数据行,可以通过t子句来缩减查询返回的数据行数,如果查询语句用了select*,需要改进SQL语句,只返回需要查询的列。
  • 切分查询:针对一个大查询可以拆分多个小查询,每个小查询只返回一部分查询数据。比如删除一千万行数据,可以改进成分批删除,每一次只删除一批数据,然后睡眠一下,再删除下一批,这样可以将一次性的压力分散到一个很长的时间段中,不仅可以降低对服务器的性能影响,还可以大大减少删除时锁的持续时间。
  • 覆盖索引:如果没有索引字段的话,就需要考虑建立索引,或者建立联合索引,通过覆盖索引的查询,这样就避免回表查询,可以提高查询性能。
  • 避免索引失效:检查SQL语句有没有问题,比如对索引进行了计算和函数操作、联合索引没有遵循最左匹配原则等,这些场景都会导致索引失效,这时候就需要修改SQL避免索引失效的发生。
  • 分解联表查询:针对联表查询的SQL语句,可以将联表查询分解成多个单表查询的语句,然后在业务层来聚合数据,或者增加冗余字段减少联表查询。
  • 排序优化:针对order by排序操作,如果执行计划的extra显示了文件排序,这时候我们可以对排序字段和其他字段建立联合索引,因为索引数据是天然有序的,这样对索引字段进行排序操作的时候,就不需要文件排序了,提高了查询性能。

深分页场景如何优化

分页最简单的实现是使用limit字节句,比如每页显示10条内容的话,第一页就是limit10、第二页就是limit10,10、第三页20,10。

但是这种方式,在深分页的场景,存在严重的性能问题,比如limit10000,20这样的查询,这时候MySQL最左叶子节点开始向右扫描10020条记录,时间复杂度为O(n),然后只返回20条给客户端,前面10000条记录都将被抛弃。如果是使用了二级索引,这种场景性能损失会加剧,因为对于前10000个不需要的数据,MySQL每次也要回表去查找,这就导致了10000次随机IO。(LIMIT offset, count ,MySQL的offset没有下推)

select * from t_player order by score desc limit 10000,20

我能想到这两种优化方式:

  • 可以在业务上改进,将"第几页"改成"下一页",先记录上一页的最后一条记录的id,然后下次就直接从该记录的位置开始扫描,这样就避免MySQL扫描大量不需要的行然后再抛弃掉的问题。
-- 记录score为prev_score
select score from t_player order by score desc limit 20

-- 下一页
select score from t_player where score > prev_score order by score desc limit 20
  • 如果要遵循第几页的方案,可以通过覆盖索引+子查询方式改进。子查询语句主要查询分页数据对应的数据库唯一id值,因为主键在辅助索引上就有,所以子查询可以不用回表。然后主查询再根据子查询返回的id,进行索引查询完整的数据行。
select * from t_player id in (select id from t_player order by score limit 10000, 1)

如果SQL和索引都没问题,查询还是很慢怎么办

  • 分批查询:针对一个大查询可以拆分多个小查询,每个小查询只返回一部分查询数据
  • 增加缓存:针对频繁读取的热点数据,我们可以放到Redis缓存,避免每次都要请求MySQL
  • 分表:如果表的数据量很大,比如表数据千万级别了,这时候可以考虑分表了,通过减少每次查询数据总量来解决数据查询缓慢的问题
  • 主从复制:针对读多写少的场景,我们可以搭建MySQL主从模式来分摊读请求的流量
  • 分库:针对写多读少的场景,单库的性能无法抗住高并发流量,就需要进行分库,把并发请求分散到多个实例中去
  • Loading...
  • Loading...
  • Loading...
  • Loading...
  • Loading...