Mysql里SQL调优公式及案例

2009年08月27日　作者： 大头刚　

SQL调优公式T＝S/V (此公式来源于互联网，本文只是引用)

S指sql所需处理的资源总量，V指sql单位时间所能处理的资源量，T自然就是SQL执行所需时间了。

这个公式很简单，调优的目标是减少T，T＝S/V，很简单的数学题，要减小值，那就只能是减小分子，或者是增大分母了。那么调整的对象自然就要放到这里的S和V上，所以调优的方法就是围绕减少S和增大V。

增大V，在硬件设备不变、参数不变的情况下，能控制的似乎不太多？
减少S（sql所需访问的资源总量），这通常是调优工作的重中之重，SQL调优的主要目的就是围绕着如何减少S在进行。在Mysql里，我觉得就是减少rows值，一条SQL执行所读写的rows数直接影响到SQL的执行时间。

下面举一个我在调优mysql查询的例子,需求是需要查出被点击数最大的16篇文章记录，sql如下：

mysql> select uid from test where status=1 order by today_hit desc limit 16;
+----------+
| uid      |
+----------+
| 22380047 | 
|  6264708 | 
| 17318093 | 
|  3412287 | 
| 21542034 | 
| 21542034 | 
| 16929428 | 
| 16929428 | 
| 11994313 | 
| 12234372 | 
| 21938539 | 
| 18323337 | 
|  3441092 | 
| 12234372 | 
| 16758499 | 
| 16929428 | 
+----------+
16 rows in set (15.11 sec)

一个简单的查询需要15秒，我们看下它的执行计划

explain select uid from article_topic where status=1 order by today_hit desc limit 16;
+----+-------------+---------------+------+---------------+-------------+---------+-------+---------+-----------------------------+
| id | select_type | table         | type | possible_keys | key         | key_len | ref   | rows    | Extra                       |
+----+-------------+---------------+------+---------------+-------------+---------+-------+---------+-----------------------------+
|  1 | SIMPLE      | test          | ref  | status_type   | status_type | 1       | const | 2327024 | Using where; Using filesort | 
+----+-------------+---------------+------+---------------+-------------+---------+-------+---------+-----------------------------+
1 row in set (0.00 sec)

这条sql需要扫描rows=2327024行，根据我们的公式，我们要减小S（sql所需访问的资源总量），即rows。

需要的结果是today_hit即被点击最高的16篇文章，today_hit字段的类型为int，那么实际情况中会有很多点击数是0的情况，所以在加个条件today_hit>0，把today_hit较小的值先排除，我们在看下执行计划：

mysql> explain select uid from test where status=1 and today_hit>0 order by today_hit desc limit 16;
+----+-------------+---------------+-------+-----------------------+-----------+---------+------+--------+-------------+
| id | select_type | table         | type  | possible_keys         | key       | key_len | ref  | rows   | Extra       |
+----+-------------+---------------+-------+-----------------------+-----------+---------+------+--------+-------------+
|  1 | SIMPLE      | article_topic | range | today_hit,status_type | today_hit | 5       | NULL | 253220 | Using where | 
+----+-------------+---------------+-------+-----------------------+-----------+---------+------+--------+-------------+
1 row in set (0.00 sec)

rows=253220，需要扫描的行数即我们公式里的S，比原来小了10倍，我们在来看看查询的结果。

mysql> select uid from test where status=1 and today_hit>0 order by today_hit desc limit 16;
+----------+
| uid      |
+----------+
| 22380047 | 
|  6264708 | 
| 17318093 | 
|  3412287 | 
| 21542034 | 
| 21542034 | 
| 16929428 | 
| 16929428 | 
| 11994313 | 
| 12234372 | 
| 21938539 | 
| 18323337 | 
|  3441092 | 
| 12234372 | 
| 16758499 | 
| 16929428 | 
+----------+
16 rows in set (0.01 sec)

显然扫描200W行在进行排序，和扫描20W行在排序不是一个数量级别的。
时间比原来少不止100倍，查询结果和原来一样。当然today_hit>0，这里什么数值比较合适，需要从你的实际情况定，需要保证需求的实现。

本例是通过加了一个条件，来减少S值，在实际情况中，调优的方案肯定不止一种，但是万变不离其宗，最终的目的都是为了减少S，或者增大V，不过就Mysql本身来说，S的减少是很有潜力可挖的，V相对困难一些吧。