MySQL性能优化

慢查询

通过监控发现了一些慢查询日志如下:

时间上已经超出了期望,这些查询动作主要发生在模糊查询和分页查询的时候。
首先这个耗时比较长的为select *,整个表的数据大概在5000左右。
explain一下表:

explain SELECT * FROM `game_video` ORDER BY update_time DESC LIMIT 5000,10;

结果如下:

type=all,基本就是全表都查询了,以后数据多了将会是噩梦。
下面解释一下这几个字段的意思:

  • select_type

SIMPLE 最简单的select,其他的还有UNION PRIMARY

  • type

这个是比较主要的字段,称为访问类型,一般典型的是index range ref const

  • key

这个也比较重要,显示以哪个键为索引

  • rows

这个为查询到结果需要读取的行数

  • Extra

using where 表示对表的请求都是同一个索引部分
using filesort 表示无法利用索引
使用select *的时候,需要判断是否真的需要返回全部的列。而且如果用select * 还会存在表结构新增字段导致的程序不兼容。

  • 响应时间
  • 扫描行数
  • 返回的行数

这三个指标基本就是查询的开销。

优化

下面开始优化,首先这是典型的分页查询。采用了limit导致了需要遍历很长的数据才能真正查询需要的记录。比较常用的优化是索引覆盖 , 把整个查询分为两步:

首先只查id,

explain select id from game_video order by id desc LIMIT 5000,10;


因为查询的列,只涉及了索引,所以会非常的快。
然后在查询一遍这些列的全部数据即可

explain select * from game_video a join (select id from game_video order by id desc LIMIT 5000,10) b on a.id = b.id ;

误区

这里如果id为主键(聚集索引),也就是物理上是挨着的,上述查询优化会不生效,因为数据本身就在索引列上。

在排序列为聚集索引列的情况下,两者都是顺序扫描表来实现查询符合条件的数据的。后者虽然是先驱动一个子查询,然后再用子查询的结果驱动主表,但是子查询并没有改变“顺序扫描表来实现查询符合条件的数据的”做法,当前情况下,甚至改写后的做法显得画蛇添足。 后面验证其实差不多的

其中gid是非聚集索引
因此改进方案,改为非聚集索引作为查询 或者 改用下面:

 explain select * from game_video where id < (select id from game_video order by id desc LIMIT 5000,1) order by id desc limit 20

测试

首先mock了 100万的数据集

光查这句话就耗费了0.16ms

  • 正常的分页查询
select * from game_video limit 0,10

几乎不花时间,

  • 偏移向后的分页查询
select * from game_video limit 50000,10


速度也还OK

  • 偏移特别后面的分页查询
select * from game_video limit 500000,10


慢了快10倍

  • 极端分页查询
select * from game_video limit 1000000,10


接近1s了
以上是本地数据库测试,字段比较少,所以时间上还没有那么可怕。

  • 倒序分页查询
select * from game_video order by id desc limit 1000000,10

还是很慢

  • 优化分页查询

分两步,首先按PK(聚集索引)查
验证了上面的想法确实减半了

explain一下看看

  • 再次优化分页查询

首先用非聚集索引试试

效果差不多。 是不是字段不够多数据不够大。
决定再增加200万数据

  • 再次对比查询


确实有点慢了
使用索引查呢

还是要快好几倍
再想办法优化呢

如果id中间没有缺失,这样查询是最快的。那假如id中间不连号呢。
其实目的就是找到第3000000条的下标,就要想如何利用到索引. 目前还没有更好的办法。

总结

如果能利用到索引将会查询提升速度,如果利用不到,尽量用子查询减少要不必要的字段过滤。

另类分页

limit是强制数数类型的分页最快也要o(N) 很难利用索引,其实还有一种trick那就是利用cursor的思想。

第一页:select * from game_video where id > 0 limit 10  记录下cursor=最后一条记录的id
第30万页:select * from game_video where id > cursor limit 10 , 这里cursor是某个几百万的值,比如3000000

子查询都不用,分分钟查出来

Comments
Write a Comment