- Introduction: Order by limit x, X has various business needs in the actual work to have order by ordering, sometimes it will cause system downtime if it is not handled properly! ___________
Principle:
a. Get sorting by index
b. Get ranking by internal algorithm:case
Specific 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
Table structure:
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. The default index execution plan selected by the SQL optimizer is:
*************************** 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 //A total of 2 rows of records were returned at a cost of 5 ms.
Execution time: A total of 15 rows of records were returned, which cost 128 ms.
2. When using IDX_CR_MO_TO (create_time,token,user_id) index, avoid using temporary tables of file esortl and reduce rows
The implementation plan is as follows:
*************************** 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
Execution time: A total of 15 rows of records were returned, which took 234 ms
3. Force the indexing effect when using 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. When limit is 1000,10:
Mandatory Index: 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 //Default execution plan: ************************** 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 execution time comparison
Use idx_tscc Index execution time: 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 //A total of 10 rows of records were returned at a cost of 220 ms. //Use mandatory index execution time: 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 //A total of 10 rows of records were returned at a cost of 17444 ms.
Summary: Specific scenario analysis:
In this example
1. Mandatory index is full scan of index, and the larger limit value, the worse performance will be.
1. The default tscp index is filtered according to where condition token,store_id value ref equivalent. Effectiveness comparison forced IDX_CR_MO_TO