• 簡介：
  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) payStatusFROM t_order cLEFT JOIN t_user u ON c.user_id= u.idWHERE c.token= '1392044'and c.pay_status in (1, 3)and c.refund_status= 0and c.store_id= 36574order by c.create_time desclimit 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=utf8CREATE 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: 1select_type: SIMPLEtable: ctype: ref possible_keys: idx_tscc,IDX_CR_MO_TOkey: idx_tscpkey_len: 68ref: const,constrows: 26980Extra: Using index condition; Using where; Using filesort *************************** 2. row ***************************id: 1select_type: SIMPLEtable: utype: eq_ref possible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: youdian_life_sewsq.c.user_idrows: 1Extra: Using where共返回 2 行記錄,花費 5 ms.

執行時間：共返回 15 行記錄,花費 128 ms.

2、當使用IDX_CR_MO_TO (create_time,token,user_id)索引時，避免Using filesortl臨時表，減少rows
執行計劃為：

*************************** 1. row ***************************id: 1select_type: SIMPLEtable: ctype: index possible_keys: key: IDX_CR_MO_TOkey_len: 73ref: rows: 15Extra: Using where *************************** 2. row ***************************id: 1select_type: SIMPLEtable: utype: eq_ref possible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: youdian_life_sewsq.c.user_idrows: 1Extra: 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) payStatusFROM tp_order c force index(IDX_CR_MO_TO)LEFT JOIN tp_user u ON c.user_id= u.idWHERE c.token= '1392044'and c.pay_status in (1, 3)and c.refund_status= 0and c.store_id= 36574order by c.create_time desclimit 100\G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: ctype: index possible_keys: key: IDX_CR_MO_TOkey_len: 73ref: rows: 100Extra: Using where *************************** 2. row ***************************id: 1select_type: SIMPLEtable: utype: eq_ref possible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: youdian_life_sewsq.c.user_idrows: 1Extra: 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) payStatusFROM tp_order c force index(IDX_CR_MO_TO)LEFT JOIN tp_user u ON c.user_id= u.idWHERE c.token= '1392044'and c.pay_status in (1, 3)and c.refund_status= 0and c.store_id= 36574order by c.create_time desclimit 1000,10\G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: ctype: index possible_keys: key: IDX_CR_MO_TOkey_len: 73ref: rows: 1010Extra: Using where *************************** 2. row ***************************id: 1select_type: SIMPLEtable: utype: eq_ref possible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: youdian_life_sewsq.c.user_idrows: 1Extra: Using where 默認執行計劃： ************************** 1. row ***************************id: 1select_type: SIMPLEtable: ctype: ref possible_keys: idx_tscc,IDX_CR_MO_TOkey: idx_tscpkey_len: 68ref: const,constrows: 27002Extra: Using index condition; Using where; Using filesort *************************** 2. row ***************************id: 1select_type: SIMPLEtable: utype: eq_ref possible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: youdian_life_sewsq.c.user_idrows: 1Extra: 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) payStatusFROM tp_order c LEFT JOIN tp_user u ON c.user_id= u.idWHERE c.token= '1392044'and c.pay_status in (1, 3)and c.refund_status= 0and c.store_id= 36574order by c.create_time desclimit 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) payStatusFROM tp_order c force index(IDX_CR_MO_TO)LEFT JOIN tp_user u ON c.user_id= u.idWHERE c.token= '1392044'and c.pay_status in (1, 3)and c.refund_status= 0and c.store_id= 36574order by c.create_time desclimit 1000,10\G 共返回 10 行記錄,花費 17444 ms.

總結： 具體場景具體分析：
本例子中

強制索引是索引全掃描，limit值越大性能就會越差

而默認走tscp 索引，是根據 where條件 token,store_id值ref 等值過濾的。效果比較強制IDX_CR_MO_TO

總結

以上是生活随笔為你收集整理的[MySQL 5.6优化] --order by limit x,x 优化的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。