[MySQL 5.6 optimization] -- order by limit x,x optimization

Keywords: MySQL SQL

  • 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

Posted by Buddha443556 on Sun, 10 Feb 2019 05:12:19 -0800