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的位图索引就更快了.