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也能看得懂的數據庫選型指南
- 阿裏雲將增設馬來西亞數據中心 納吉布總理大讚中國技術