MySQL order by的一个优化思路

来源：互联网发布：app交友软件编辑：程序博客网时间：2024/06/10 04:13

　　最近遇到一条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_mergepossible_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 filesort1 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: refpossible_keys: idx_test          key: idx_test      key_len: 12          ref: const,const,const         rows: 13038        Extra: Using where1 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: indexpossible_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条件，有时候会收到意想不到的效果。

0 0