mysql倒排的优化

54321333

  今天数据库负载就直线上升，数据库连接数撑爆。把语句抓出来一看，罪魁祸首是一条很简单的语句：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 ***************************
        Table: eload_promotion_code
   Non_unique: 1
     Key_name: idx_cishu_exp
Seq_in_index: 1
  Column_name: cishu
    Collation: A
  Cardinality: 15
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 3. row ***************************
        Table: eload_promotion_code
   Non_unique: 1
     Key_name: idx_cishu_exp
Seq_in_index: 2
  Column_name: exp_time
    Collation: A
  Cardinality: 921642
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:

可以看到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
*************************** 1. row ***************************
           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
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: eload_promotion_code
         type: range
possible_keys: idx_cishu_exp
          key: idx_cishu_exp
      key_len: 8
          ref: NULL
         rows: 460862
        Extra: Using where; Using index; Using filesort
1 row in set (0.00 sec)

好吧，这个语句有救了，采用延时关联先取出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出结果。
这样就算完了。