- 简介：
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=utf81、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 where3、当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 where4、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.总结： 具体场景具体分析：
本例子中
- 强制索引是索引全扫描，limit值越大性能就会越差
- 而默认走tscp 索引，是根据 where条件 token,store_id值ref 等值过滤的。效果比较强制IDX_CR_MO_TO
