mysql orderitems_MySQL order by的一个优化思路

最新推荐文章于 2021-11-23 20:16:03 发布

王Tie塔儿

最新推荐文章于 2021-11-23 20:16:03 发布

阅读量82

点赞数

文章标签： mysql orderitems

本文链接：https://blog.csdn.net/weixin_36307713/article/details/113212198

版权

最近遇到一条SQL线上执行超过5s，这显然无法忍受了，必须要优化了。

首先看眼库表结构和SQL语句。

CREATE TABLE`xxxxx` (

`id`bigint(20) NOT NULLAUTO_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:1select_type: SIMPLEtable: c

type: index_merge

possible_keys: Index_public,Index_status,idx_delStatuskey: Index_public,Index_status,idx_delStatus

key_len:4,4,4ref:NULLrows:72362Extra: Usingintersect(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:1select_type: SIMPLEtable: c

type: ref

possible_keys: idx_testkey: idx_test

key_len:12ref: const,const,const

rows:13038Extra: Usingwhere

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:1select_type: SIMPLEtable: c

type:indexpossible_keys:NULL

key: index_finishTime

key_len:8ref:NULLrows:100Extra: Usingwhere

+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+

| 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 |