阅读323 返回首页    go 阿里云 go 技术社区[云栖]

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

待优化SQL：

select `t1`.`order_sn` as `orderSn`,
       `t1`.`pay_platform_order_sn` as `payPlatformOrderSn`,
       `t1`.`liquidator_order_sn` as `liquidatorOrderSn`,
       `t1`.`store_id` as `storeId`,
       `t1`.`create_time` as `createTime`,
       ROUND(`t1`.`real_money`, 2) as `realMoney`,
       ROUND(`t1`.`net_money`, 2) as `netMoney`
  from `xxxx` `t1`
 where((`t1`.`pay_day`= 20170623)
   AND(`t1`.`pay_platform_type`= 1)
   AND(`t1`.`order_type`!= 4))
 order by `t1`.`create_time` desc
 limit 13000,1000;

select `t1`.`order_sn` as `orderSn`,
       `t1`.`pay_platform_order_sn` as `payPlatformOrderSn`,
       `t1`.`liquidator_order_sn` as `liquidatorOrderSn`,
       `t1`.`store_id` as `storeId`,
       `t1`.`create_time` as `createTime`,
       ROUND(`t1`.`real_money`, 2) as `realMoney`,
       ROUND(`t1`.`net_money`, 2) as `netMoney`
  from `xxxx` `t1`
 where((`t1`.`pay_day`= 20170623)
   AND(`t1`.`pay_platform_type`= 1)
   AND(`t1`.`order_type`!= 4))
 order by `t1`.`create_time` desc
 limit 14000,1000;

.......
SQL N :
select `t1`.`order_sn` as `orderSn`,
       `t1`.`pay_platform_order_sn` as `payPlatformOrderSn`,
       `t1`.`liquidator_order_sn` as `liquidatorOrderSn`,
       `t1`.`store_id` as `storeId`,
       `t1`.`create_time` as `createTime`,
       ROUND(`t1`.`real_money`, 2) as `realMoney`,
       ROUND(`t1`.`net_money`, 2) as `netMoney`
  from `xxxx` `t1`
 where((`t1`.`pay_day`= 20170623)
   AND(`t1`.`pay_platform_type`= 2)
   AND(`t1`.`order_type`!= 4))
 order by `t1`.`create_time` desc
 limit 639000,1000；  等等
  1. - 我们来分析下这条SQL存在的问题是什么？
    每条SQL都要进行limit 分页 1000 ，每次SQL扫描的时候都会多扫描出来1000依次类似 每个SQL1+1000+SQl n 1000扫描的行数越来越多，SQL执行越来越慢
  2. 那么我们这个时候应该如何进行优化呢？
  3. 那么我们找到原因，应该如何进行优化？
    1.下面我们来查看下表结构： CREATE TABLE `xxxx` ( `order_sn` varchar(64) NOT NULL , `pay_platform_order_sn` varchar(64) DEFAULT NULL , `liquidator_order_sn` varchar(64) NOT NULL , `store_id` varchar(28) NOT NULL , `liquidator_merchant_id` varchar(28) DEFAULT NULL , `liquidator_id` varchar(28) NOT NULL , `notify_url` varchar(255) DEFAULT NULL , `order_type` int(1) NOT NULL , `pay_platform_type` int(1) NOT NULL DEFAULT '1' , `real_money` decimal(16,2) NOT NULL , `net_money` decimal(16,2) DEFAULT NULL , `pay_platform_rate` decimal(10,5) NOT NULL `pay_platform_fee` decimal(10,5) NOT NULL , `bank_commission_rate` decimal(10,5) NOT NULL , `bank_commission_fee` decimal(10,5) NOT NULL , `liquidator_commission_rate` decimal(10,5) NOT NULL , `liquidator_commission_fee` decimal(10,5) NOT NULL , `pay_status` int(1) NOT NULL DEFAULT '0' , `pay_time` bigint(20) DEFAULT NULL `create_time` bigint(20) NOT NULL `update_time` bigint(20) NOT NULL `create_day` int(8) NOT NULL , `pay_day` int(8) NOT NULL PRIMARY KEY (`order_sn`), KEY `auto_shard_key_pay_day` (`pay_day`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`pay_day`) tbpartition by hash(`pay_day`) tbpartitions 128 只有一个auto_key,按照pay_day来进行分库分表的，每天的数据将会写入到一个表中
  4. 那么接下来我们来看下具体的SQL吧。 select t1.order_sn as orderSn, t1.pay_platform_order_sn as payPlatformOrderSn, t1.liquidator_order_sn as liquidatorOrderSn, t1.store_id as storeId, t1.create_time as createTime, ROUND(t1.real_money, 2) as realMoney, ROUND(t1.net_money, 2) as netMoney from xxxx t1 where((t1.pay_day= 20170623) AND(t1.pay_platform_type= 1) AND(t1.order_type!= 4)) order by t1.create_time desc limit 13000,1000 执行时间： 1000 rows in set (17.15 sec)

**SQL2：SQL1：limit 13000,1000 SQL 2:limit 14000,1000 SQL3 15000,1000 .......
**
每次进行limit分页后面就会越来越多，我们怎么优化呢？可以进行使用create_time的时候来取，yinw create_time desc 每次取值小于 上次 最后一个分页的值，程序中可以获取到最后一个值

  • 改写： select `t1`.`order_sn` as `orderSn`, `t1`.`pay_platform_order_sn` as `payPlatformOrderSn`, `t1`.`liquidator_order_sn` as `liquidatorOrderSn`, `t1`.`store_id` as `storeId`, `t1`.`create_time` as `createTime`, ROUND(`t1`.`real_money`, 2) as `realMoney`, ROUND(`t1`.`net_money`, 2) as `netMoney` from `xxxx` `t1` where((`t1`.`pay_day`= 20170623) AND(`t1`.`pay_platform_type`= 1) AND(`t1`.`order_type`!= 4) and (t1.create_time <1498224817214 ) ) order by `t1`.`create_time` desc limit 1000
    执行这条SQL的运行结果：与limit 14000， 1000 结果对比是否正确 注意create_time必须要唯一，要不然数据会出错
  • 改写SQL的结果：
    +------------------------------+------------------------------+-----------------------------------------------------------+----------------------+---------------+-----------+----------+ | orderSn | payPlatformOrderSn | liquidatorOrderSn | storeId | createTime | realMoney | netMoney | +------------------------------+------------------------------+-----------------------------------------------------------+----------------------+---------------+-----------+----------+ | 2017062321333602874614794313 | 2017062321001004110231124716 | 20170623213336122444 | 20161102024036021056 | 1498224816273 | 3.00 | 2.98 | | 2017062321333602570370510454 | 2017062321001004400217227825 | 20170623213336691911 | 20170122091030022044 | 1498224816260 | 20.00 | 19.88 | | 2017062321333502963986026949 | 2017062321001004470292075414 | SPD1620170623000705388 | 20170606114500022732 | 1498224815909 | 6.80 | 6.77 | | 2017062321333502573421997925 | 2017062321001004740292353231 | WXAP2017062321333566468d1f19e_f274_476b_894f_4e4af336d203 | 20170622162655020787 | 1498224815750 | 44.00 | 43.80 | | 2017062321333502270167936461 | 2017062321001004170282781120 | 00001272100010283170623213334952 | 20170317200852026235 | 1498224815322 | 1000.00 | 996.00 | | 2017062321333402676833246001 | 2017062321001004490269448345 | 20170623213334759694 | 20170105140104028248 | 1498224814987 | 27.00 | 26.84 | | 2017062321333302925673783936 | 2017062321001004380202447519 | 201706232133317423606 | 20161109154109026555 | 1498224813963 | 103.20 | 102.82 | | 2017062321333302281415357316 | 2017062321001004260200016627 | 20170623213333792133 | 20161102022037029919 | 1498224813229 | 25.00 | 24.85 | ............... ............... | 2017062321271102156595350682 | 2017062321001004740292345996 | 20170623212711758269 | 20170303160046022946 | 1498224431479 | 19.90 | 19.78 | | 2017062321271002759434262863 | 2017062321001004070230038958 | WXAP20170623212710871c30ece88_7f6c_4534_aa0c_3bb7a2db46a1 | 20170622160655026739 | 1498224430965 | 49.00 | 48.78 | | 2017062321271002883316826506 | 2017062321001004590294549086 | 9149822443060084 | 20170617154758026337 | 1498224430919 | 17.00 | 16.49 | | 2017062321270902321509662572 | 2017062321001004340290839986 | SPD1620170623000701894 | 20170606114500022732 | 1498224429936 | 13.61 | 13.54 | | 2017062321270902952568474880 | 2017062321001004750236790581 | 20170623212709727878 | 20161102003309027565 | 1498224429936 | 37.99 | 37.76 | | 2017062321270902803469089016 | 2017062321001004620210763524 | 201706232127092206332069817024 | 20170616182658029807 | 1498224429884 | 2000.00 | 1988.00 | | 2017062321270902739469347172 | 2017062321001004060233894096 | 20170623212709722286 | 20161102015026021523 | 1498224429759 | 10.00 | 9.94 | | 2017062321270902700116205691 | 2017062321001004600226774684 | 20170623212708338324 | 20170406142250020040 | 1498224429070 | 5000.00 | 4970.00 | | 2017062321270902495327404572 | 2017062321001004390277865674 | 20170623212708728085 | 20170216123921028933 | 1498224429040 | 12.00 | 11.93 | +------------------------------+------------------------------+-----------------------------------------------------------+----------------------+---------------+-----------+----------+ 在没有添加索引的时候这种方法运行出来的时间是2.4
    运行limit 14000,1000 出来的结果 +------------------------------+------------------------------+-----------------------------------------------------------+----------------------+---------------+-----------+----------+ | orderSn | payPlatformOrderSn | liquidatorOrderSn | storeId | createTime | realMoney | netMoney | +------------------------------+------------------------------+-----------------------------------------------------------+----------------------+---------------+-----------+----------+ | 2017062321333602874614794313 | 2017062321001004110231124716 | 20170623213336122444 | 20161102024036021056 | 1498224816273 | 3.00 | 2.98 | | 2017062321333602570370510454 | 2017062321001004400217227825 | 20170623213336691911 | 20170122091030022044 | 1498224816260 | 20.00 | 19.88 | | 2017062321333502963986026949 | 2017062321001004470292075414 | SPD1620170623000705388 | 20170606114500022732 | 1498224815909 | 6.80 | 6.77 | | 2017062321333502573421997925 | 2017062321001004740292353231 | WXAP2017062321333566468d1f19e_f274_476b_894f_4e4af336d203 | 20170622162655020787 | 1498224815750 | 44.00 | 43.80 | | 2017062321333502270167936461 | 2017062321001004170282781120 | 00001272100010283170623213334952 | 20170317200852026235 | 1498224815322 | 1000.00 | 996.00 | | 2017062321333402676833246001 | 2017062321001004490269448345 | 20170623213334759694 | 20170105140104028248 | 1498224814987 | 27.00 | 26.84 | | 2017062321333302925673783936 | 2017062321001004380202447519 | 201706232133317423606 | 20161109154109026555 | 1498224813963 | 103.20 | 102.82 | ......... ......... | 2017062321271102827269541757 | 2017062321001004190253334130 | 20170623212711656142 | 20170601101415027115 | 1498224431553 | 21.50 | 21.37 | | 2017062321271102156595350682 | 2017062321001004740292345996 | 20170623212711758269 | 20170303160046022946 | 1498224431479 | 19.90 | 19.78 | | 2017062321271002759434262863 | 2017062321001004070230038958 | WXAP20170623212710871c30ece88_7f6c_4534_aa0c_3bb7a2db46a1 | 20170622160655026739 | 1498224430965 | 49.00 | 48.78 | | 2017062321271002883316826506 | 2017062321001004590294549086 | 9149822443060084 | 20170617154758026337 | 1498224430919 | 17.00 | 16.49 | | 2017062321270902952568474880 | 2017062321001004750236790581 | 20170623212709727878 | 20161102003309027565 | 1498224429936 | 37.99 | 37.76 | | 2017062321270902321509662572 | 2017062321001004340290839986 | SPD1620170623000701894 | 20170606114500022732 | 1498224429936 | 13.61 | 13.54 | | 2017062321270902803469089016 | 2017062321001004620210763524 | 201706232127092206332069817024 | 20170616182658029807 | 1498224429884 | 2000.00 | 1988.00 | | 2017062321270902739469347172 | 2017062321001004060233894096 | 20170623212709722286 | 20161102015026021523 | 1498224429759 | 10.00 | 9.94 | | 2017062321270902700116205691 | 2017062321001004600226774684 | 20170623212708338324 | 20170406142250020040 | 1498224429070 | 5000.00 | 4970.00 | | 2017062321270902495327404572 | 2017062321001004390277865674 | 20170623212708728085 | 20170216123921028933 | 1498224429040 | 12.00 | 11.93 | +------------------------------+------------------------------+-----------------------------------------------------------+----------------------+---------------+-----------+----------+ 1000 rows in set (4.49 sec) 和使用create_time方式的结果一样正确，那么我们可以利用这种方式进行优化此条SQL 在SQL还没有较好的索引的时候就已经快了2ms 那么这个SQL应该如何添加索引呢？
    alter table xx add index idx_a1(pay_day,pay_platform_type,create_time`);

添加执行时间：
limit 1300,1000;

jingjing_test@3306>[(none)]>select `t1`.`order_sn` as `orderSn`,
    ->         `t1`.`pay_platform_order_sn` as `payPlatformOrderSn`,
    ->         `t1`.`liquidator_order_sn` as `liquidatorOrderSn`,
    ->         `t1`.`store_id` as `storeId`,
    ->         `t1`.`create_time` as `createTime`,
    ->         ROUND(`t1`.`real_money`, 2) as `realMoney`,
    ->         ROUND(`t1`.`net_money`, 2) as `netMoney`
    ->    from `lp_finance` `t1`
    ->   where((`t1`.`pay_day`= 20170623)
    ->     AND(`t1`.`pay_platform_type`= 1)
    ->     AND(`t1`.`order_type`!= 4))
    ->   order by `t1`.`create_time` desc
    ->   limit 13000,
    ->         1000;
    .....................
    | 2017062321334102493006443532 | 2017062321001004630266975418 | 20170623213341193315                                      | 20170524113231026018 | 1498224821151 |     93.00 |    92.44 |
    | 2017062321334002753089202153 | 2017062321001004160215380831 | 20170623213340105039                                      | 20161102023756029582 | 1498224820913 |     25.00 |    24.85 |
    | 2017062321334002919036140619 | 2017062321001004270288865237 | 20170623213339933374                                      | 20161102023240024609 | 1498224820087 |     37.50 |    37.28 |
    | 2017062321333902874877808774 | 2017062321001004430270812088 | 20170623213339810251                                      | 20161226103301021799 | 1498224819729 |     28.00 |    27.83 |
    | 2017062321333902976770948844 | 2017062321001004320209582434 | 20170623213339621127                                      | 20170317151136029664 | 1498224819450 |    378.00 |   375.73 |
    | 2017062321333902556267340535 | 2017062321001004130281143423 | 20170623213339666339                                      | 20170224094339024226 | 1498224819415 |     10.00 |     9.94 |
    | 2017062321333902425882087362 | 2017062321001004220205474086 | 20170623213339991035                                      | 20170527151140020666 | 1498224819268 |    241.00 |   239.55 |
    | 2017062321333702498468772706 | 2017062321001004680288980520 | ese2017062321333749387648                                 | 20161207155611022133 | 1498224817722 |      7.00 |     6.97 |
    | 2017062321333702490339598678 | 2017062321001004940261562625 | 20170623213337588027                                      | 20161102023333020142 | 1498224817656 |     28.00 |    27.83 |
    | 2017062321333702937547154469 | 2017062321001004830288597484 | PA0032017062321333713229870                               | 20170612154757023948 | 1498224817499 |   1500.00 |  1493.25 |
    | 2017062321333702661269609478 | 2017062321001004040297362328 | pa2017062321363758402                                     | 20170523175858022655 | 1498224817464 |     42.00 |    41.87 |
    | 2017062321333702820795329640 | 2017062321001004980252193355 | 2017062365938777                                          | 20170511134520025852 | 1498224817214 |    162.00 |   160.70 |
    +------------------------------+------------------------------+-----------------------------------------------------------+----------------------+---------------+-----------+----------+
    1000 rows in set (0.28 sec) 
jingjing_test@3306>[(none)]> select `t1`.`order_sn` as `orderSn`,
    ->         `t1`.`pay_platform_order_sn` as `payPlatformOrderSn`,
    ->         `t1`.`liquidator_order_sn` as `liquidatorOrderSn`,
    ->         `t1`.`store_id` as `storeId`,
    ->         `t1`.`create_time` as `createTime`,
    ->         ROUND(`t1`.`real_money`, 2) as `realMoney`,
    ->         ROUND(`t1`.`net_money`, 2) as `netMoney`
    ->    from `lp_finance` `t1`
    ->   where((`t1`.`pay_day`= 20170623)
    ->     AND(`t1`.`pay_platform_type`= 1)
    ->     AND(`t1`.`order_type`!= 4)
    ->     and (t1.create_time <1498224817214 ) )
    ->   order by `t1`.`create_time` desc
    ->   limit 1000;
    .....................
    | 2017062321271102216678933045 | 2017062321001004190253356416 | 20170623212711554602                                      | 20161107144216020916 | 1498224431627 |    300.00 |   298.20 |
    | 2017062321271102827269541757 | 2017062321001004190253334130 | 20170623212711656142                                      | 20170601101415027115 | 1498224431553 |     21.50 |    21.37 |
    | 2017062321271102156595350682 | 2017062321001004740292345996 | 20170623212711758269                                      | 20170303160046022946 | 1498224431479 |     19.90 |    19.78 |
    | 2017062321271002759434262863 | 2017062321001004070230038958 | WXAP20170623212710871c30ece88_7f6c_4534_aa0c_3bb7a2db46a1 | 20170622160655026739 | 1498224430965 |     49.00 |    48.78 |
    | 2017062321271002883316826506 | 2017062321001004590294549086 | 9149822443060084                                          | 20170617154758026337 | 1498224430919 |     17.00 |    16.49 |
    | 2017062321270902952568474880 | 2017062321001004750236790581 | 20170623212709727878                                      | 20161102003309027565 | 1498224429936 |     37.99 |    37.76 |
    | 2017062321270902321509662572 | 2017062321001004340290839986 | SPD1620170623000701894                                    | 20170606114500022732 | 1498224429936 |     13.61 |    13.54 |
    | 2017062321270902803469089016 | 2017062321001004620210763524 | 201706232127092206332069817024                            | 20170616182658029807 | 1498224429884 |   2000.00 |  1988.00 |
    | 2017062321270902739469347172 | 2017062321001004060233894096 | 20170623212709722286                                      | 20161102015026021523 | 1498224429759 |     10.00 |     9.94 |
    | 2017062321270902700116205691 | 2017062321001004600226774684 | 20170623212708338324                                      | 20170406142250020040 | 1498224429070 |   5000.00 |  4970.00 |
    | 2017062321270902495327404572 | 2017062321001004390277865674 | 20170623212708728085                                      | 20170216123921028933 | 1498224429040 |     12.00 |    11.93 |
    +------------------------------+------------------------------+-----------------------------------------------------------+----------------------+---------------+-----------+----------+
    1000 rows in set (0.24 sec)
  • 执行计划：
    explain execute select `t1`.`order_sn` as `orderSn`, `t1`.`pay_platform_order_sn` as `payPlatformOrderSn`, `t1`.`liquidator_order_sn` as `liquidatorOrderSn`, `t1`.`store_id` as `storeId`, `t1`.`create_time` as `createTime`, ROUND(`t1`.`real_money`, 2) as `realMoney`, ROUND(`t1`.`net_money`, 2) as `netMoney` from `lp_finance` `t1` where((`t1`.`pay_day`= 20170623) AND(`t1`.`pay_platform_type`= 1) AND(`t1`.`order_type`!= 4)) order by `t1`.`create_time` desc limit 13000, 1000; +----+-------------+-------+------+---------------+--------+---------+-------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------+---------+-------------+--------+-------------+ | 1 | SIMPLE | t1 | ref | idx_a1 | idx_a1 | 8 | const,const | 470367 | Using where | +----+-------------+-------+------+---------------+--------+---------+-------------+--------+-------------+ 1 row in set (0.06 sec) explain execute select `t1`.`order_sn` as `orderSn`, `t1`.`pay_platform_order_sn` as `payPlatformOrderSn`, `t1`.`liquidator_order_sn` as `liquidatorOrderSn`, `t1`.`store_id` as `storeId`, `t1`.`create_time` as `createTime`, ROUND(`t1`.`real_money`, 2) as `realMoney`, ROUND(`t1`.`net_money`, 2) as `netMoney` from `lp_finance` `t1` where((`t1`.`pay_day`= 20170623) AND(`t1`.`pay_platform_type`= 1) AND(`t1`.`order_type`!= 4) and (t1.create_time <1498224817214 ) ) order by `t1`.`create_time` desc limit 1000; +----+-------------+-------+------+-----------------------+--------+---------+-------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-----------------------+--------+---------+-------------+--------+-------------+ | 1 | SIMPLE | t1 | ref | idx_a1,idx_createtime | idx_a1 | 8 | const,const | 470367 | Using where | +----+-------------+-------+------+-----------------------+--------+---------+-------------+--------+-------------+ 1 row in set (0.04 sec)
  • 那么这里还存在一个问题： 为什么type,为ref，而不是range 呢？hint 那么我们来强制索引测试下： mysql> explain execute select `t1`.`order_sn` as `orderSn`, `t1`.`pay_platform_order_sn` as `payPlatformOrderSn`, `t1`.`liquidator_order_sn` as `liquidatorOrderSn`, `t1`.`store_id` as `storeId`, `t1`.`create_time` as `createTime`, ROUND(`t1`.`real_money`, 2) as `realMoney`, ROUND(`t1`.`net_money`, 2) as `netMoney` from `lp_finance` `t1` force index(idx_a1) where((`t1`.`pay_day`= 20170623) AND(`t1`.`pay_platform_type`= 1) AND(`t1`.`order_type`!= 4) and (t1.create_time <1498224817214 ) ) order by `t1`.`create_time` desc limit 1000; +----+-------------+-------+-------+---------------+--------+---------+------+--------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+--------+---------+------+--------+------------------------------------+ | 1 | SIMPLE | t1 | range | idx_a1 | idx_a1 | 16 | NULL | 470367 | Using index condition; Using where | +----+-------------+-------+-------+---------------+--------+---------+------+--------+------------------------------------+ 1 row in set (0.04 sec) 这个时候需要思考一个问题，为什么不强制的时候，走的也是 idx_a1 type 为ref 呢？强制了之后就是range 呢？
  • 下节分享
  • 总结：
    • 在当优化limit x,x 。 添加索引是最好的方法，并发量大的时候还是会出现问题，我们应该从根本来解决问题。
      如何解决呢？可以利用传参的形式来优化，因为limit x,x MySQL是不知道从哪里开始，是需要从头开始扫描，直到符合lit的位置进行返回来。 解决方法： 1.create_tble 2.主键

最后更新：2017-07-07 11:32:08

  上一篇：go  中国-世界上最大的物联网市场
  下一篇：go  强势标准各占山头 物联网标准战打响