mysql倒排的優化

    今天資料庫負載就直線上升，資料庫連接配接數撐爆。把語句抓出來一看，罪魁禍首是一條很簡單的語句：SELECT * FROM eload_promotion_code WHERE 1 AND exp_time<1478782591 AND cishu=0 order by id desc limit 454660,20; 二話不說先把這個語句kill了，然後慢慢看怎麼優化。

先看一下這個表的索引：

>show index from eload_promotion_code\G

*************************** 1. row ***************************

        Table: eload_promotion_code

   Non_unique: 0

     Key_name: PRIMARY

 Seq_in_index: 1

  Column_name: id

    Collation: A

  Cardinality: 921642

     Sub_part: NULL

       Packed: NULL

         Null: 

   Index_type: BTREE

      Comment: 

Index_comment: 

*************************** 2. row ***************************

   Non_unique: 1

     Key_name: idx_cishu_exp

  Column_name: cishu

  Cardinality: 15

*************************** 3. row ***************************

 Seq_in_index: 2

  Column_name: exp_time

可以看到id為主鍵，idx_cishu_exp為（cishu,exp_time）的唯一索引

看一下這個語句的執行計劃，可以看到排序沒有用到索引

explain SELECT * FROM eload_promotion_code WHERE 1 AND exp_time<1478782591 AND cishu=0 order by id desc limit 454660,20\G

           id: 1

  select_type: SIMPLE

        table: eload_promotion_code

         type: ref

possible_keys: idx_cishu_exp

          key: idx_cishu_exp

      key_len: 4

          ref: const

         rows: 460854

        Extra: Using where; Using filesort

1 row in set (0.00 sec)

将select * 換成select id後再看執行計劃，可以用索引覆寫

>explain select id FROM eload_promotion_code WHERE 1 AND exp_time<1478782591 AND cishu=0 order by id desc limit 454660,20 \G

         type: range

      key_len: 8

          ref: NULL

         rows: 460862

        Extra: Using where; Using index; Using filesort

好吧，這個語句有救了，采用延時關聯先取出id,然後根據id擷取原表所需要的行，改寫後的語句來了：select * from eload_promotion_code inner join (select id from eload_promotion_code where exp_time<1478782591 AND cishu=0 order by id desc limit 454660,20) as x on eload_promotion_code.id=x.id;

執行一下，0.3s出結果。

這樣就算完了。

本文轉自 emma_cql 51CTO部落格，原文連結:http://blog.51cto.com/chenql/1871575