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 '',
`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 '',
`height` int(11) DEFAULT '',
`width` int(11) DEFAULT '',
`length` bigint(20) DEFAULT '',
`status` int(11) NOT NULL DEFAULT '',
`uploadServer` int(11) NOT NULL DEFAULT '',
`orgfileName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`img` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`delStatus` int(11) NOT NULL DEFAULT '',
`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:
http://blog.csdn.net/zbszhangbosen/article/details/11490479
最新文章
- c#设计模式-组合模式
- Codeforces Round #254 (Div. 2) A	 DZY Loves Chessboard
- windows Path变量优先级
- git 创建远程分支和删除 master 分支
- yii2归档安装
- java的Socket通信例子及关于java.net.SocketException: Socket is closed错误
- $(this).val()与this.value的区别?text()与html()的区别?
- HDU 1240 Asteroids!
- AIDL介绍和实例讲解
- 使用SignalR打造消息总线
- Cisco 学会使用Telnet、SSH
- SQL复习五(索引)
- Echarts 中国地图各个省市自治区自定义颜色
- Vue组件之全局组件与局部组件
- Java 使用Arrays.sort排序 从大到小排列
- 手动增加swap空间
- java实操之使用jcraft进行sftp上传下载文件
- 查询表Or列的注释信息
- IIS短文件名泄露漏洞检测
- RT/Metro商店应用如何调用SQLite数据库
热门文章
- 【Python学习】request库
- 64_n2
- html的loadrunner脚本2
- <;mvc:annotation-driven/>;都做了那些事情
- CentOS 7.1使用yum安装MySql5.6.24
- CentOS_Linux服务器系统安装之分区
- C#后台获取ajax传来的xml格式数据值
- 【JavaWeb开发】初步实现网站应用钉钉扫码登录
- ArrayBuffer对象、TypedArray视图和DataView视图
- [hdu5251]矩形面积 旋转卡壳求最小矩形覆盖