免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 2852 | 回复: 0

Mysql里SQL调优公式及案例 [复制链接]

论坛徽章:
0
发表于 2010-02-23 12:59 |显示全部楼层

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相对困难一些吧。


本文来自ChinaUnix博客,如果查看原文请点:http://blog.chinaunix.net/u3/111930/showart_2184941.html
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP