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 100G;
*************************** 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：

http://blog.csdn.net/zbszhangbosen/article/details/11490479 

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/billyxp/p/3433629.html