mysql的慢查询优化是个老生常谈的话题.本文结生产数据库中遇到的实际问题,举例说明.
开启慢查询支持
首先要开通慢查询日志,修改my.cnf配置文件,添加如下选项:
log-slow-queries = slow.log
long_query_time = 1
----如果打了patch,可以指定更小的值
log-queries-not-using-indexes
然
后,把slow.log按天来切割,例如
slow.log.20100405
分析慢查询日志
分析慢查询语句的最重要的工具,当然是mysql官方提供的mysqldumpslow了.它的详细用法,请参考我之前的文章
mysqldumpslow -s t -t 5
slow.log.20100405
>analyse.txt
上面的语句将慢查询语句按照总时间排序,不是mysqldumpslow默认的按照语句执行的平均时间排序.原因是,总时间最长的语句,才是真正需要优化的慢查询语句,而且这种语句的优化往往能带来较好的效率提升.
在top5的语句中,我找到了如下的一条sql语句:
Count:
48 Time=206.79s (9926s) Lock=0.00s (0s) Rows=1.0 (48),
work[work]@[10.81.6.106]
SELECT MAX(img_id) as max_img_id FROM t_mis_pic
WHERE N AND img_id%N='S'
(每天执行了48次,平均每次206s,绝对是慢的需要优化了)
根据上述的sql语句模型,去slow.log中找到真实的sql语句中的一条:
SELECT MAX(img_id) as max_img_id FROM t_mis_pic WHERE 1 AND
img_id%2='0';
好了,现在来看看我们有没有可能优化它.
定位问题,优化
分析慢查询语句的工具,首先是explain,如果不能解决,再考虑用profile.
来
show一下表的结构:
show create table t_mis_pic /G
Table: t_mis_pic
Create
Table: CREATE TABLE `t_mis_pic` (
`img_id` bigint(20) unsigned NOT
NULL,
`add_time` int(10) unsigned NOT NULL,
`is_del` tinyint(3) unsigned
NOT NULL default '0',
PRIMARY KEY (`img_id`),
KEY `add_time`
(`add_time`,`is_del`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set
(0.00 sec)
可以看到,img_id列上是有主键的.
再explain一下:
Explain SELECT
MAX(img_id) as max_img_id FROM t_mis_pic WHERE 1 AND img_id%2='0'
/G
*************************** 1. row ***************************
id:
1
select_type: SIMPLE
table: t_mis_pic
type: index ----对整个索引的扫描
possible_keys: NULL
key: add_time
----利用了索引,但是不是主键索引,只是
add_time索引本身包含了主键列,比表的数据小,所以mysql选择了它
key_len: 5
ref:
NULL ----无法根据条件来实现过滤和快速查找
rows:
89964741 -----快要1亿的数据,全索引扫描,导致了慢查询
Extra:
Using where; Using index ---利用到了索引和where条件语句
上面的分析已经很清楚了,mysql无法有效的利用主键索引,而且由于数据量太大,导致了慢查询语句.再来看看表的状态:
show
table status like 't_mis_pic' /G
…
Rows: 85532594
Avg_row_length:
55
Data_length: 4771020800
Max_data_length: 0
Index_length:
2581594112
…
从上面的统计中,看到索引的量占到了数据的一半以上,都很巨大.所以mysql
即使扫描索引也需要很长的时间.现在我们已经知道问题所在了,那么我们应该怎么优化它呢?
从 SELECT MAX(img_id) as
max_img_id FROM t_mis_pic WHERE 1 AND
img_id%2='0';
这句话来分析,是想要找到最大的为偶数的img_id.那么我们正好可以利用img_id上的主键索引,先直接找到img_id的最大值,然后找到小于等于这个最大值的并且为偶数的
img_id值,就是我们想要的结果.
先explain一下:
explain select MAX(img_id) as
max_img_id FROM t_mis_pic /G:
…
rows: NULL
Extra: Select tables
optimized away
…
结果表明,mysql不用查询表就可以得到最大的img_id值.那么我们继续改写
SELECT
MAX(img_id) as max_img_id FROM t_mis_pic WHERE 1 AND img_id%2='0'
这条语句:
select img_id as max_img_id
FROM t_mis_pic
where
img_id<=(select MAX(img_id) FROM t_mis_pic) -----wehre条件已经成为where img_id<=常数
and img_id%2=0 ----限制 img_id为偶数
order by img_id desc ----利用 img_id上的索引,从最大的img_id开始,往前寻找为偶数的img_id
limit 1
---返回符合条件的最大的img_id
根据上面的分析,再来explain一下看看是
否符合我们的预期:
explain select img_id as max_img_id FROM t_mis_pic where
img_id<=(select MAX(img_id) FROM t_mis_pic) and img_id%2=0 order by img_id
desc limit 1 /G
*************************** 1. row
***************************
id: 1
select_type: PRIMARY
table:
t_mis_pic
type: range ----mysql仍然进行索引扫描,但是此处是
range扫描,不需要扫描整个index.
possible_keys: PRIMARY
key:
PRIMARY
key_len: 8
ref: NULL
rows: 23548910 -----sql语句本身只需 要返回一行,并不需要检查23548910
这么多行,此处的数字可以忽略.实际上,mysql最多只需要扫描2行.
Extra: Using where; Using index
-----mysql扫描的是索引,而不是表本身
*************************** 2.
row ***************************
…
rows: NULL
Extra: Select tables
optimized away ----优化的不能再优化了
在备机上执行这条语句,瞬间即可完成.这个慢查询语句可以完全消除.
回头看看slow.log的每天统计结果中,
SELECT MAX(img_id) as max_img_id FROM t_mis_pic WHERE N AND img_id%N='S'
语句每天执行48次,每次执行平均时间为200s左右,而优化后,执行的时间可以忽略不计,效果还是非常明显的.
其他方法
既然所有的查询都用的是img_id%2这个条件,那么我们可以给该表增加一列,存储的值就是img_id%2后的结果.然后再给这一列建立索引.但是速度仍然比不上上面改写后的sql语句,究其原因,因为img_id%2的结果不是奇数就是偶数,区分度太小,给这样的列建立索引的效果就不明显了.事实上,在其他商业数据库中,比如oracle,它的函数索引就是这个原理.针对这个例子,oracle的位图索引就更快了.