[MySQL 5.6优化] --order by limit x,x 优化

阅读原文请点击：[url]http://click.aliyun.com/m/23632/[/url]
摘要： 简介： order by limit x ,x 在实际工作中有各种业务需求要有order by的排序，有时候处理不好则会造成系统宕机！ 原理： a.通过索引来获取排序 b.通过内部算法获取排序： 案例 具体SQL： SELECT c.

简介： 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)
阅读原文请点击：[url]http://click.aliyun.com/m/23632/[/url]