MySQL order by的一个优化思路
最近遇到一条SQL线上执行超过5s,这显然无法忍受了,必须要优化了。
首先看眼库表结构和SQL语句。
CREATE TABLE `xxxxx` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `owner` bigint(20) NOT NULL, `publicStatus` int(11) NOT NULL DEFAULT '0', `title` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `type` int(11) NOT NULL, `deviceType` int(11) NOT NULL, `deviceName` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL, `createTime` bigint(20) NOT NULL, `startTime` bigint(20) NOT NULL, `finishTime` bigint(20) NOT NULL DEFAULT '0', `height` int(11) DEFAULT '0', `width` int(11) DEFAULT '0', `length` bigint(20) DEFAULT '0', `status` int(11) NOT NULL DEFAULT '0', `uploadServer` int(11) NOT NULL DEFAULT '0', `orgfileName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `img` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `delStatus` int(11) NOT NULL DEFAULT '0', `location` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `locationText` varchar(256) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `lastModifyTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `extUrl` varchar(1024) COLLATE utf8_unicode_ci DEFAULT NULL, `oem` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL, `lat` float(10,6) NOT NULL DEFAULT '-1000.000000', `lng` float(10,6) NOT NULL DEFAULT '-1000.000000', PRIMARY KEY (`id`), KEY `index_owner` (`owner`), KEY `Index_public` (`publicStatus`), KEY `Index_status` (`status`), KEY `index_finishTime` (`finishTime`), KEY `idx_channel_oem` (`oem`), KEY `idx_dev_type` (`deviceType`), KEY `idx_delStatus` (`delStatus`), KEY `idx_loc_locText` (`location`,`locationText`(255)), KEY `idx_lat_lng` (`lat`,`lng`) ) ENGINE=InnoDB AUTO_INCREMENT=583029 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
显然这个表结构直观看上去就不是很优化的样子,先不去关心,在看眼SQL。
select * from `AAA` c left join `BBB` o on c.id = o.channelid where c.publicStatus = 2 and c.status= 30 and c.delStatus = 0 order by c.finishTime desc limit 100;
虽然有一个left join,但是仔细看where条件就可以知道其实问题并不大,只是一个简单的链接,因为所有查询条件都属于AAA表。
那么接下来就是需要看眼这个SQL的explain和profiling了。为了简单一些,我们将left join去掉。
explain结果如下: *************************** 1. row *************************** id: 1 select_type: SIMPLE table: c type: index_merge possible_keys: Index_public,Index_status,idx_delStatus key: Index_public,Index_status,idx_delStatus key_len: 4,4,4 ref: NULL rows: 72362 Extra: Using intersect(Index_public,Index_status,idx_delStatus); Using where; Using filesort 1 row in set (0.00 sec)
show profiling结果如下: +----------+------------+------------------------------------------------------------------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+------------------------------------------------------------------------------------------------------------------------------+ | 1 | 4.10154300 | select * from `channel` c where c.publicStatus = 2 and c.status= 30 and c.delStatus = 0 order by c.finishTime desc limit 100 | +----------+------------+------------------------------------------------------------------------------------------------------------------------------+ +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000026 | | Waiting for query cache lock | 0.000003 | | checking query cache for query | 0.000048 | | checking permissions | 0.000005 | | Opening tables | 0.000021 | | System lock | 0.000009 | | Waiting for query cache lock | 0.000022 | | init | 0.000038 | | optimizing | 0.000003 | | statistics | 0.000167 | | preparing | 0.000072 | | executing | 0.000004 | | Sorting result | 4.096042 | | Sending data | 0.000715 | | Waiting for query cache lock | 0.000000 | | Sending data | 0.004289 | | end | 0.000007 | | query end | 0.000005 | | closing tables | 0.000008 | | freeing items | 0.000009 | | Waiting for query cache lock | 0.000002 | | freeing items | 0.000009 | | Waiting for query cache lock | 0.000002 | | freeing items | 0.000002 | | storing result in query cache | 0.000003 | | logging slow query | 0.000002 | | logging slow query | 0.000026 | | cleaning up | 0.000004 | +--------------------------------+----------+
从上面可以很明显的看出来,sort占了最长的时间,那么这条SQL重点就是要解决sort问题。
解决sort问题就是解决order by问题,直观的看这条sql,第一反应就是需要添加一个4个字段的联合索引idx(publicstatus,status,delstatu,finishtime),通过试验结果可以接受,但是扫描行数依然不少,达到1w行以上。
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: c type: ref possible_keys: idx_test key: idx_test key_len: 12 ref: const,const,const rows: 13038 Extra: Using where 1 row in set (0.00 sec)
那么有没有其他的优化思路呢? 我们看眼第一次的explain的结果,其中比较明显的是index merge和useing intersect,这个代表什么呢?
查询MySQL的官方文档,可以得知,这是查询解析器进行index merge的交叉算法优化。索引合并交叉算法同时对所有使用的索引进行扫描,并产生一个符合条件的行的交集。这个交集一般都比较大,而真正进行排序的字段的索引并没有使用到,所以需要单独进行排序,而一旦结果集过大,就会在磁盘上生成临时文件进行排序,就出现了useing filesort的情况了。
以上可以参考:http://dev.mysql.com/doc/refman/5.5/en/index-merge-optimization.html
同时,扩展阅读一下,如果对于这种情况不打算使用index merge,可以在服务器上进行如下配置
set optimizer_switch=‘index_merge_intersection=off’
就可以将index merge的交叉优化算法关闭了。
BTW:MySQL 5.6的 Index Codiction Pushdown对这个的优化会更好一些,有兴趣的同学可以自行去看。
回到我们的主题,那么这个order by还有什么其他优化思路呢? 那么既然排序是最大的消耗,那么我们强制使用排序字段的索引会产生什么效果呢?
explain select * from `channel` c FORCE INDEX(index_finishtime) where c.publicStatus = 2 and c.status= 30 and c.delStatus = 0 order by c.finishTime desc limit 100\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: c type: index possible_keys: NULL key: index_finishTime key_len: 8 ref: NULL rows: 100 Extra: Using where +----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | 0.00427200 | select * from `channel` c FORCE INDEX(index_finishtime) where c.publicStatus = 2 and c.status= 30 and c.delStatus = 0 order by c.finishTime desc limit 100 | +----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000021 | | Waiting for query cache lock | 0.000005 | | checking query cache for query | 0.000063 | | checking permissions | 0.000007 | | Opening tables | 0.000018 | | System lock | 0.000010 | | Waiting for query cache lock | 0.000026 | | init | 0.000043 | | optimizing | 0.000015 | | statistics | 0.000013 | | preparing | 0.000020 | | executing | 0.000003 | | Sorting result | 0.000005 | | Sending data | 0.001091 | | Waiting for query cache lock | 0.000004 | | Sending data | 0.000805 | | end | 0.000007 | | query end | 0.000006 | | closing tables | 0.000009 | | freeing items | 0.000012 | | Waiting for query cache lock | 0.000002 | | freeing items | 0.002067 | | Waiting for query cache lock | 0.000006 | | freeing items | 0.000003 | | storing result in query cache | 0.000005 | | logging slow query | 0.000002 | | cleaning up | 0.000004 | +--------------------------------+----------+
可以看到排序依然有,但是耗时已经下降到非常低了,扫描行数变为100行,总执行时间变为0.004秒,是原来4.101秒的0.09%,效率提高了近1000倍。
结论:
这次调整给我们提供了一个对order by的优化思路,不要相信mysql的查询解析器,我们可以只针对排序字段建立索引,而不用去管前面的where条件,有时候会收到意想不到的效果。
还可以看@reples的同样的一片blog: