谈一个MySQL语句的优化

事情是这样:数据库是MySQL,有若干个连接保持每秒2~3条的速度向表同一个表里写入“日志型”记录。表的结构除了一个必要的自增主键id之外,还有一个做了索引的type(tinyint)、time(timestamp)和一个ipc(float)类型的字段。一个读进程定时会要抓取每种type的最后一条记录。数据库的读写都很热,所以要尽可能的保证性能。

多年之后,再次拾起Database engineer的活儿,有点不太适应。起初,这个问题交到我手里的时候,type的定义很少,只有区区两种,原来的做法是简单的分两次select:

SELECT * FROM log WHERE type = TYPE1 ORDER BY id DESC LIMIT 1;
SELECT * FROM log WHERE type = TYPE2 ORDER BY id DESC LIMIT 1;

几乎是教课书式的做法,由于是日志型插入数据,而且使用主键排序相比time排序更有优势。由于是简单的index查询,效率不错。但问题来了:

  1. 如果进一步引入多个type——而事实上这就是这次重构的目的。每次都会多一次查询。
  2. 由于多次查询且写入相当频繁的缘故,往往在两次查询之间,会有新的数据写入,那么两次或者说多次查询不能保证在同一时间维度之下,即多次查询的经典问题,不具备原子性。

既然说到了每次最大值,那么就是group by语句出马的时候了;说到原子性,也就是必须一个命令搞定,于是就出炉了优化版本1.

SELECT
 *
FROM
 log
WHERE
 id IN (SELECT
 MAX(id)
 FROM
  log
 GROUP BY type )

先是一个子查询,查出每个type类型的最大id,然后再做一次查询取出每种类型的最大id的记录。这样做的问题是需要先做一个range;接下来尽管是一个主键查询,但毕竟还是一个full_table。一时犯懒,在5.7下测了300w级别的性能,感觉还能满足需求,就直接交了差。

接下来没多久需求变了,原先的“最后一条记录”变成了“最后两条记录”。区区的一个小变化,由于没有让这条查询赖以生存的max(id)函数无从下手。

既然有“最后两条”的需求,那么就排序了,可MySQL自古就没有出过类似Oracle的rank()或者SQL Server的top那么好用的方式,只能用反过头来找传说中的“临时变量”。于是有了第二版

SELECT
 *
FROM
 (SELECT
 a.*,
 (CASE a.type
 WHEN @t THEN @r:=@r + 1
 ELSE @t:=a.type
 END) AS rank
 FROM
 log a, (SELECT @r:=1, @t = 0) b
 ORDER BY type , id DESC) c

where rank < 3

涉及了两次全表查询,一次file_sort。性能上跟上一次肯定有了一倍的差距,几乎到了能接受的极限。继续开动优化大法:

SELECT
 *
FROM
 (SELECT
 a.*,
 (CASE a.type
 WHEN @t THEN @r:=@r + 1
 ELSE @t:=a.type
 END) AS rank
 FROM
 (SELECT * FROM log order by id desc limit 10000) a, (SELECT @r:=1, @t = 0) b
 ORDER BY type, id desc ) c

where rank < 3

这次优化的思路在于:之前的filesort是由于大量的记录都参与到了排序的过程中,性能奇差。我们假定所有命中的记录都会出现在最后的10000条记录里,那么只要最后的10000条记录参与排序就够了。

最终,性能甚至高过了最初的方案!

各种结论:

  1. MySQL真的是快,因为它是在裸奔!
  2. 只要花点时间挤,性能总会有的。
  3. SQL语句并不是越长性能越垃圾。
  4. 数据结构真的很重要!
推荐阅读:
记得那时是2005年10月,开
在看一台服务器的SQL-Slo
Coreseek是一个基于sp
之前接触到的基于LAMP平台的

“谈一个MySQL语句的优化”的一个回复

发表评论

电子邮件地址不会被公开。 必填项已用*标注

请补全下列算式: *

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据