请教mysql索引优化问题,百思不解,请大家帮忙

Question

一个表50多万数据id是主键.status和addtime做复合索引,STATUS只有三种值0,1,2,addtime是时间戳，整型的SELECT id FROM buy WHERE STATUS=1 order by addtime desc limit 292409,20
desc上面语句的时候好象提示只用到where这样执行下来也要2秒左右.请问怎么优化呢?

Answer 1

alter table tbname add index(status,addtime);

Answer 2

谢谢楼上回复，先前已经做了status,addtime的复合索引的。执行时间还是2秒左右。

Answer 3

贴出
show index from ...
explain select ...以供分析。

Answer 4

好的.谢谢楼上两位大侠mysql> mysql> show index from buy\G;
*************************** 1. row ***************************
        Table: buy
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: itemid
    Collation: A
  Cardinality: 568714
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: buy
   Non_unique: 1
     Key_name: editdate
 Seq_in_index: 1
  Column_name: editdate
    Collation: A
  Cardinality: 1834
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 3. row ***************************
        Table: buy
   Non_unique: 1
     Key_name: editdate
 Seq_in_index: 2
  Column_name: vip
    Collation: A
  Cardinality: 2154
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 4. row ***************************
        Table: buy
   Non_unique: 1
     Key_name: editdate
 Seq_in_index: 3
  Column_name: edittime
    Collation: A
  Cardinality: 284357
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 5. row ***************************
        Table: buy
   Non_unique: 1
     Key_name: status
 Seq_in_index: 1
  Column_name: status
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 6. row ***************************
        Table: buy
   Non_unique: 1
     Key_name: status
 Seq_in_index: 2
  Column_name: thumb
    Collation: A
  Cardinality: 568714
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 7. row ***************************
        Table: buy
   Non_unique: 1
     Key_name: username
 Seq_in_index: 1
  Column_name: username
    Collation: A
  Cardinality: 31595
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 8. row ***************************
        Table: buy
   Non_unique: 1
     Key_name: username
 Seq_in_index: 2
  Column_name: keyword
    Collation: A
  Cardinality: 568714
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 9. row ***************************
        Table: buy
   Non_unique: 1
     Key_name: time1
 Seq_in_index: 1
  Column_name: vip
    Collation: A
  Cardinality: 9
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 10. row ***************************
        Table: buy
   Non_unique: 1
     Key_name: time1
 Seq_in_index: 2
  Column_name: edittime
    Collation: A
  Cardinality: 284357
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 11. row ***************************
        Table: buy
   Non_unique: 1
     Key_name: time1
 Seq_in_index: 3
  Column_name: itemid
    Collation: A
  Cardinality: 568714
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 12. row ***************************
        Table: buy
   Non_unique: 1
     Key_name: title
 Seq_in_index: 1
  Column_name: status
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 13. row ***************************
        Table: buy
   Non_unique: 1
     Key_name: title
 Seq_in_index: 2
  Column_name: title
    Collation: A
  Cardinality: 568714
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 14. row ***************************
        Table: buy
   Non_unique: 1
     Key_name: company
 Seq_in_index: 1
  Column_name: company
    Collation: A
  Cardinality: 31595
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 15. row ***************************
        Table: buy
   Non_unique: 1
     Key_name: catid
 Seq_in_index: 1
  Column_name: catid
    Collation: A
  Cardinality: 274
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 16. row ***************************
        Table: buy
   Non_unique: 1
     Key_name: mycatid
 Seq_in_index: 1
  Column_name: mycatid
    Collation: A
  Cardinality: 19610
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 17. row ***************************
        Table: buy
   Non_unique: 1
     Key_name: areaid
 Seq_in_index: 1
  Column_name: areaid
    Collation: A
  Cardinality: 306
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 18. row ***************************
        Table: buy
   Non_unique: 1
     Key_name: tag
 Seq_in_index: 1
  Column_name: tag
    Collation: A
  Cardinality: 284357
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 19. row ***************************
        Table: buy
   Non_unique: 1
     Key_name: addtime
 Seq_in_index: 1
  Column_name: addtime
    Collation: A
  Cardinality: 284357
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 20. row ***************************
        Table: buy
   Non_unique: 1
     Key_name: uid
 Seq_in_index: 1
  Column_name: uid
    Collation: A
  Cardinality: 31595
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 21. row ***************************
        Table: buy
   Non_unique: 1
     Key_name: statusaddtime
 Seq_in_index: 1
  Column_name: status
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 22. row ***************************
        Table: buy
   Non_unique: 1
     Key_name: statusaddtime
 Seq_in_index: 2
  Column_name: addtime
    Collation: A
  Cardinality: 284357
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
22 rows in set (0.00 sec)ERROR:
No query specified
第二条mysql> desc SELECT itemid FROM buy WHERE STATUS=3 order by addtime desc limit 292409,20\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: buy
         type: ref
possible_keys: status,title,statusaddtime
          key: statusedittime
      key_len: 1
          ref: const
         rows: 277980
        Extra: Using where
1 row in set (0.00 sec)ERROR:
No query specified执行 SELECT itemid FROM destoon_sell WHERE STATUS=3 order by edittime desc limit 292409,20 这条SQL的时候就慢了.每次都要2秒左右.

Answer 5

重发一格式.mysql> mysql> show index from destoon_sell\G;
*************************** 1. row ***************************
        Table: destoon_sell
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: itemid
    Collation: A
  Cardinality: 568714
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: destoon_sell
   Non_unique: 1
     Key_name: editdate
 Seq_in_index: 1
  Column_name: editdate
    Collation: A
  Cardinality: 1834
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 3. row ***************************
        Table: destoon_sell
   Non_unique: 1
     Key_name: editdate
 Seq_in_index: 2
  Column_name: vip
    Collation: A
  Cardinality: 2154
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 4. row ***************************
        Table: destoon_sell
   Non_unique: 1
     Key_name: editdate
 Seq_in_index: 3
  Column_name: edittime
    Collation: A
  Cardinality: 284357
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 5. row ***************************
        Table: destoon_sell
   Non_unique: 1
     Key_name: status
 Seq_in_index: 1
  Column_name: status
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 6. row ***************************
        Table: destoon_sell
   Non_unique: 1
     Key_name: status
 Seq_in_index: 2
  Column_name: thumb
    Collation: A
  Cardinality: 568714
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 7. row ***************************
        Table: destoon_sell
   Non_unique: 1
     Key_name: username
 Seq_in_index: 1
  Column_name: username
    Collation: A
  Cardinality: 31595
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 8. row ***************************
        Table: destoon_sell
   Non_unique: 1
     Key_name: username
 Seq_in_index: 2
  Column_name: keyword
    Collation: A
  Cardinality: 568714
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 9. row ***************************
        Table: destoon_sell
   Non_unique: 1
     Key_name: time1
 Seq_in_index: 1
  Column_name: vip
    Collation: A
  Cardinality: 9
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 10. row ***************************
        Table: destoon_sell
   Non_unique: 1
     Key_name: time1
 Seq_in_index: 2
  Column_name: edittime
    Collation: A
  Cardinality: 284357
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 11. row ***************************
        Table: destoon_sell
   Non_unique: 1
     Key_name: time1
 Seq_in_index: 3
  Column_name: itemid
    Collation: A
  Cardinality: 568714
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 12. row ***************************
        Table: destoon_sell
   Non_unique: 1
     Key_name: title
 Seq_in_index: 1
  Column_name: status
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 13. row ***************************
        Table: destoon_sell
   Non_unique: 1
     Key_name: title
 Seq_in_index: 2
  Column_name: title
    Collation: A
  Cardinality: 568714
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 14. row ***************************
        Table: destoon_sell
   Non_unique: 1
     Key_name: company
 Seq_in_index: 1
  Column_name: company
    Collation: A
  Cardinality: 31595
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 15. row ***************************
        Table: destoon_sell
   Non_unique: 1
     Key_name: catid
 Seq_in_index: 1
  Column_name: catid
    Collation: A
  Cardinality: 274
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 16. row ***************************
        Table: destoon_sell
   Non_unique: 1
     Key_name: mycatid
 Seq_in_index: 1
  Column_name: mycatid
    Collation: A
  Cardinality: 19610
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 17. row ***************************
        Table: destoon_sell
   Non_unique: 1
     Key_name: areaid
 Seq_in_index: 1
  Column_name: areaid
    Collation: A
  Cardinality: 306
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 18. row ***************************
        Table: destoon_sell
   Non_unique: 1
     Key_name: tag
 Seq_in_index: 1
  Column_name: tag
    Collation: A
  Cardinality: 284357
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 19. row ***************************
        Table: destoon_sell
   Non_unique: 1
     Key_name: edittime
 Seq_in_index: 1
  Column_name: edittime
    Collation: A
  Cardinality: 284357
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 20. row ***************************
        Table: destoon_sell
   Non_unique: 1
     Key_name: uid
 Seq_in_index: 1
  Column_name: uid
    Collation: A
  Cardinality: 31595
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 21. row ***************************
        Table: destoon_sell
   Non_unique: 1
     Key_name: statusedittime
 Seq_in_index: 1
  Column_name: status
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 22. row ***************************
        Table: destoon_sell
   Non_unique: 1
     Key_name: statusedittime
 Seq_in_index: 2
  Column_name: edittime
    Collation: A
  Cardinality: 284357
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
22 rows in set (0.00 sec)ERROR:
No query specified第二条SQL
mysql> desc SELECT itemid FROM destoon_sell WHERE STATUS=3 order by edittime desc limit 292409,20\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: destoon_sell
         type: ref
possible_keys: status,title,statusedittime
          key: statusedittime
      key_len: 1
          ref: const
         rows: 277982
        Extra: Using where
1 row in set (0.00 sec)ERROR:
No query specified

Answer 6

好的.重发格式.mysql> show index from destoon_sell;
+--------------+------------+----------------+--------------+-------------+-----                                                                                                 ------+-------------+----------+--------+------+------------+---------+---------                                                                                                 ------+
| Table        | Non_unique | Key_name       | Seq_in_index | Column_name | Coll                                                                                                 ation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_co                                                                                                 mment |
+--------------+------------+----------------+--------------+-------------+-----                                                                                                 ------+-------------+----------+--------+------+------------+---------+---------                                                                                                 ------+
| destoon_sell |          0 | PRIMARY        |            1 | itemid      | A                                                                                                          |      569013 |     NULL | NULL   |      | BTREE      |         |                                                                                                                |
| destoon_sell |          1 | editdate       |            1 | editdate    | A                                                                                                          |        1835 |     NULL | NULL   |      | BTREE      |         |                                                                                                                |
| destoon_sell |          1 | editdate       |            2 | vip         | A                                                                                                          |        2155 |     NULL | NULL   |      | BTREE      |         |                                                                                                                |
| destoon_sell |          1 | editdate       |            3 | edittime    | A                                                                                                          |      284506 |     NULL | NULL   |      | BTREE      |         |                                                                                                                |
| destoon_sell |          1 | status         |            1 | status      | A                                                                                                          |           5 |     NULL | NULL   |      | BTREE      |         |                                                                                                                |
| destoon_sell |          1 | status         |            2 | thumb       | A                                                                                                          |      569013 |     NULL | NULL   |      | BTREE      |         |                                                                                                                |
| destoon_sell |          1 | username       |            1 | username    | A                                                                                                          |       31611 |     NULL | NULL   |      | BTREE      |         |                                                                                                                |
| destoon_sell |          1 | username       |            2 | keyword     | A                                                                                                          |      569013 |     NULL | NULL   |      | BTREE      |         |                                                                                                                |
| destoon_sell |          1 | time1          |            1 | vip         | A                                                                                                          |           9 |     NULL | NULL   |      | BTREE      |         |                                                                                                                |
| destoon_sell |          1 | time1          |            2 | edittime    | A                                                                                                          |      284506 |     NULL | NULL   |      | BTREE      |         |                                                                                                                |
| destoon_sell |          1 | time1          |            3 | itemid      | A                                                                                                          |      569013 |     NULL | NULL   |      | BTREE      |         |                                                                                                                |
| destoon_sell |          1 | title          |            1 | status      | A                                                                                                          |           5 |     NULL | NULL   |      | BTREE      |         |                                                                                                                |
| destoon_sell |          1 | title          |            2 | title       | A                                                                                                          |      569013 |     NULL | NULL   |      | BTREE      |         |                                                                                                                |
| destoon_sell |          1 | company        |            1 | company     | A                                                                                                          |       31611 |     NULL | NULL   |      | BTREE      |         |                                                                                                                |
| destoon_sell |          1 | catid          |            1 | catid       | A                                                                                                          |         274 |     NULL | NULL   |      | BTREE      |         |                                                                                                                |
| destoon_sell |          1 | mycatid        |            1 | mycatid     | A                                                                                                          |       19621 |     NULL | NULL   |      | BTREE      |         |                                                                                                                |
| destoon_sell |          1 | areaid         |            1 | areaid      | A                                                                                                          |         306 |     NULL | NULL   |      | BTREE      |         |                                                                                                                |
| destoon_sell |          1 | tag            |            1 | tag         | A                                                                                                          |      284506 |     NULL | NULL   |      | BTREE      |         |                                                                                                                |
| destoon_sell |          1 | edittime       |            1 | edittime    | A                                                                                                          |      284506 |     NULL | NULL   |      | BTREE      |         |                                                                                                                |
| destoon_sell |          1 | uid            |            1 | uid         | A                                                                                                          |       31611 |     NULL | NULL   |      | BTREE      |         |                                                                                                                |
| destoon_sell |          1 | statusedittime |            1 | status      | A                                                                                                          |           5 |     NULL | NULL   |      | BTREE      |         |                                                                                                                |
| destoon_sell |          1 | statusedittime |            2 | edittime    | A                                                                                                          |      284506 |     NULL | NULL   |      | BTREE      |         |                                                                                                                |
+--------------+------------+----------------+--------------+-------------+-----                                                                                                 ------+-------------+----------+--------+------+------------+---------+---------                                                                                                 ------+
22 rows in set (0.00 sec)

Answer 7

内容太多了.超过1000字符限制.desc结果!!!mysql>
mysql> desc SELECT itemid FROM destoon_sell WHERE STATUS=3 order by edittime desc limit 292409,20;
+----+-------------+--------------+------+-----------------------------+----------------+---------+-------+--------+-------------+
| id | select_type | table        | type | possible_keys               | key            | key_len | ref   | rows   | Extra       |
+----+-------------+--------------+------+-----------------------------+----------------+---------+-------+--------+-------------+
|  1 | SIMPLE      | destoon_sell | ref  | status,title,statusedittime | statusedittime | 1       | const | 282852 | Using where |
+----+-------------+--------------+------+-----------------------------+----------------+---------+-------+--------+-------------+
1 row in set (0.16 sec)

Answer 8

你的索引在哪儿？
create index xxx on destoon_sell(STATUS,edittime,itemid);

Answer 9

你的索引在哪儿？
create index xxx on destoon_sell(STATUS,edittime,itemid);不太明白联合索引上为何要加上 itemid...
如果status 只有三个值，那么是否可以考虑对status 做聚合索引呢...
然后单独在addtime 上做个索引。

Answer 10

是limit慢，先查出20条ID再关联

Answer 11

楼主好像没有对addtime添加索引，索引只有edittime
建议对itemid，status，addtime添加联合索引还有执行如下操作1. 因为你的数据有点多，尝试增加sort_buffer_size变量的大小和增加read_rnd_buffer_size变量的大小。
2. 考虑到你的表有可能删除过数据可以使用mysqlcheck -B youdb --tables youtable -o 进行表优化。

Answer 12

还可以考虑强制使用索引force indexSELECT itemid FROM destoon_sell FORCE INDEX(STATUS，addtime) WHERE STATUS=3 order by addtime desc limit 292409,20;