[MySQL 5.6优化] --order by limit x,x 优化

2021-12-17 03:57:23

简介：
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=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、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 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: 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 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.

总结： 具体场景具体分析：
本例子中

强制索引是索引全扫描，limit值越大性能就会越差
而默认走tscp 索引，是根据 where条件 token,store_id值ref 等值过滤的。效果比较强制IDX_CR_MO_TO

码农公寓

相关文章