270
[MySQL 5.6优化] --order by limit x,x 优化
- 简介： order by limit x ,x 在实际工作中有各种业务需求要有order by的排序，有时候处理不好则会造成系统宕机！
原理：
a.通过索引来获取排序
b.通过内部算法获取排序：案例
具体SQL：
SELECT c.order_price orderPrice,
c.preferential_amount preferentialAmount,
c.order_sumprice orderSumprice,
cast(c.mode as SIGNED) rechargeType,
cast(c.pay_type as SIGNED) payType,
cast(c.type as SIGNED) appType,
c.order_sn orderSn,
c.create_time payTime,
u.nickname nickName,
u.headimgurl headImg,
u.real_name memberName,
cast(c.pay_status as SIGNED) payStatus
FROM t_order c
LEFT JOIN t_user u ON c.user_id= u.id
WHERE c.token= '1392044'
and c.pay_status in (1, 3)
and c.refund_status= 0
and c.store_id= 36574
order by c.create_time desc
limit 0,15
表结构：
CREATE TABLE `t_order ` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`order_sn` varchar(30) DEFAULT NULL COMMENT ',
`preferential_amount` decimal(10,2) DEFAULT '0.00' COMMENT,
`order_sumprice` decimal(10,2) DEFAULT '0.00' COMMENT ,
`mode` tinyint(3) unsigned DEFAULT '1' COMMENT '',
`pay_type` tinyint(1) DEFAULT '1' COMMENT '',
`type` tinyint(4) DEFAULT '1' COMMENT '',
`create_time` int(10) unsigned DEFAULT '0' COMMENT '',
PRIMARY KEY (`id`),
UNIQUE KEY `order_sn` (`order_sn`),
KEY `IDX_CR_MO_TO` (`create_time`,`token`,`user_id`),
KEY `idx_store_token_createtime` (`store_id`,`token`,`create_time`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=53925518 DEFAULT CHARSET=utf8
CREATE TABLE `t_user ` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`nickname` varchar(20) DEFAULT NULL COMMENT '',
`headimgurl` varchar(255) DEFAULT NULL,
`real_name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `openid` (`openid`),
KEY `IDX_NICKNAME` (`nickname')
) ENGINE=InnoDB AUTO_INCREMENT=13974852 DEFAULT CHARSET=utf8
1、SQL优化器默认选择索引执行计划为：
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: c
type: ref
possible_keys: idx_tscc,IDX_CR_MO_TO
key: idx_tscp
key_len: 68
ref: const,const
rows: 26980
Extra: Using index condition; Using where; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: u
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: youdian_life_sewsq.c.user_id
rows: 1
Extra: Using where
共返回 2 行记录,花费 5 ms.
执行时间：共返回 15 行记录,花费 128 ms.
2、当使用IDX_CR_MO_TO (create_time,token,user_id)索引时，避免Using filesortl临时表，减少rows
执行计划为：
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: c
type: index
possible_keys:
key: IDX_CR_MO_TO
key_len: 73
ref:
rows: 15
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: u
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: youdian_life_sewsq.c.user_id
rows: 1
Extra: Using where
执行时间：共返回 15 行记录,花费 234 ms
3、当使用limit 100时强制索引效果：
mysql>explain SELECT c.order_price orderPrice,
c.preferential_amount preferentialAmount,
c.order_sumprice orderSumprice,
cast(c.mode as SIGNED) rechargeType,
cast(c.pay_type as SIGNED) payType,
cast(c.type as SIGNED) appType,
c.order_sn orderSn,
c.create_time payTime,
u.nickname nickName,
u.headimgurl headImg,
u.real_name memberName,
cast(c.pay_status as SIGNED) payStatus
FROM tp_order c force index(IDX_CR_MO_TO)
LEFT JOIN tp_user u ON c.user_id= u.id
WHERE c.token= '1392044'
and c.pay_status in (1, 3)
and c.refund_status= 0
and c.store_id= 36574
order by c.create_time desc
limit 100\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: c
type: index
possible_keys:
key: IDX_CR_MO_TO
key_len: 73
ref:
rows: 100
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: u
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: youdian_life_sewsq.c.user_id
rows: 1
Extra: Using where
3、当limit 为1000,10时候的效果：
强制索引：
mysql>explain SELECT c.order_price orderPrice,
c.preferential_amount preferentialAmount,
c.order_sumprice orderSumprice,
cast(c.mode as SIGNED) rechargeType,
cast(c.pay_type as SIGNED) payType,
cast(c.type as SIGNED) appType,
c.order_sn orderSn,
c.create_time payTime,
u.nickname nickName,
u.headimgurl headImg,
u.real_name memberName,
cast(c.pay_status as SIGNED) payStatus
FROM tp_order c force index(IDX_CR_MO_TO)
LEFT JOIN tp_user u ON c.user_id= u.id
WHERE c.token= '1392044'
and c.pay_status in (1, 3)
and c.refund_status= 0
and c.store_id= 36574
order by c.create_time desc
limit 1000,10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: c
type: index
possible_keys:
key: IDX_CR_MO_TO
key_len: 73
ref:
rows: 1010
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: u
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: youdian_life_sewsq.c.user_id
rows: 1
Extra: Using where
默认执行计划：
************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: c
type: ref
possible_keys: idx_tscc,IDX_CR_MO_TO
key: idx_tscp
key_len: 68
ref: const,const
rows: 27002
Extra: Using index condition; Using where; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: u
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: youdian_life_sewsq.c.user_id
rows: 1
Extra: Using where
4、limit 1000,10执行时间对比
使用idx_tscc索引执行时间：
mysql>SELECT c.order_price orderPrice,
c.preferential_amount preferentialAmount,
c.order_sumprice orderSumprice,
cast(c.mode as SIGNED) rechargeType,
cast(c.pay_type as SIGNED) payType,
cast(c.type as SIGNED) appType,
c.order_sn orderSn,
c.create_time payTime,
u.nickname nickName,
u.headimgurl headImg,
u.real_name memberName,
cast(c.pay_status as SIGNED) payStatus
FROM tp_order c
LEFT JOIN tp_user u ON c.user_id= u.id
WHERE c.token= '1392044'
and c.pay_status in (1, 3)
and c.refund_status= 0
and c.store_id= 36574
order by c.create_time desc
limit 1000,10\G
共返回 10 行记录,花费 220 ms.
使用强制索引执行时间：
mysql>SELECT c.order_price orderPrice,
c.preferential_amount preferentialAmount,
c.order_sumprice orderSumprice,
cast(c.mode as SIGNED) rechargeType,
cast(c.pay_type as SIGNED) payType,
cast(c.type as SIGNED) appType,
c.order_sn orderSn,
c.create_time payTime,
u.nickname nickName,
u.headimgurl headImg,
u.real_name memberName,
cast(c.pay_status as SIGNED) payStatus
FROM tp_order c force index(IDX_CR_MO_TO)
LEFT JOIN tp_user u ON c.user_id= u.id
WHERE c.token= '1392044'
and c.pay_status in (1, 3)
and c.refund_status= 0
and c.store_id= 36574
order by c.create_time desc
limit 1000,10\G
共返回 10 行记录,花费 17444 ms.
总结： 具体场景具体分析：
本例子中
1. 强制索引是索引全扫描，limit值越大性能就会越差
1. 而默认走tscp 索引，是根据 where条件 token,store_id值ref 等值过滤的。效果比较强制IDX_CR_MO_TO
最后更新：2017-06-19 22:32:00
上一篇：
SpringBoot笔记——1
下一篇： 入门篇：函数计算的基本概念和通用场景概述
- 理解Java机制最受欢迎的8幅图
- android 加载图片轻松避免OOM(out of memory) 支持设置缓存大小,不再强制catch OOM
- 《数据分析实战:基于EXCEL和SPSS系列工具的实践》一3.1 数据采集的几条重要原则
- 《Linux From Scratch》第三部分：构建LFS系统 第六章：安装基本的系统软件- 6.1. 简介
- 商业派微软的另一面
- 如何使用ARMS配置tengine的日志监控
- 软件下载站如何应对迅雷的P2SP流量
- MySQL · 引擎特性 · InnoDB 崩溃恢复过程
- CEO也能看得懂的数据库选型指南
- 阿里云将增设马来西亚数据中心 纳吉布总理大赞中国技术