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 相关资源：敏捷开发V1.0.pptx