閱讀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  強勢標準各占山頭 物聯網標準戰打響