• 简介：
  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=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: 1  select_type: SIMPLE        table: c         type: refpossible_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_refpossible_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: indexpossible_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_refpossible_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: indexpossible_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_refpossible_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: indexpossible_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_refpossible_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: refpossible_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_refpossible_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值越大性能就会越差
2. 而默认走tscp 索引，是根据 where条件 token,store_id值ref 等值过滤的。效果比较强制IDX_CR_MO_TO