本帖最后由 huolin 于 2010-05-20 09:56:26 编辑

解决方案 »

  1.   

    贴一下你的show index from tbl_down 直接贴文本,不要贴图。
      

  2.   

    Table       Non_unique  Key_name    Seq_in_index Collation Cardinality Sub_part Packed Null Index_type Commenttbl_down 0 PRIMARY 1 aid A 206098 BTREE
    tbl_down 1 adminid 1 adminid A 140 BTREE
    tbl_down 1 uid 1 uid A 3747 BTREE
    tbl_down 1 uid 2 u_cateid A 22899 BTREE
    tbl_down 1 iselite 1 iselite A 2 BTREE
    tbl_down 1 isms 1 isms A 2 BTREE
    tbl_down 1 isbest 1 isbest A 2 BTREE
    tbl_down 1 postnum 1 postnum A 14 BTREE
    tbl_down 1 specialid 1 specialid A 1 BTREE
    tbl_down 1 typeid 1 typeid A 3 BTREE
    tbl_down 1 comm 1 classid A 194 BTREE
    tbl_down 1 comm 2 version A 204 BTREE
    tbl_down 1 comm 3 chapterid A 2785 BTREE
    tbl_down 1 comm 4 nodeid A 7633 BTREE
    tbl_down 1 comm 5 knid A 8587 BTREE
    tbl_down 1 channelid 1 channelid A 24 BTREE
    tbl_down 1 blockid 1 blockid A 834 BTREE
    tbl_down 1 username 1 username A 1 BTREE
      

  3.   

      PRIMARY KEY (`aid`),
      KEY `adminid` (`adminid`),
      KEY `uid` (`uid`,`u_cateid`),
      KEY `iselite` (`iselite`),
      KEY `isms` (`isms`),
      KEY `isbest` (`isbest`),
      KEY `postnum` (`postnum`),
      KEY `specialid` (`specialid`),
      KEY `typeid` (`typeid`),
      KEY `comm` (`classid`,`version`,`chapterid`,`nodeid`,`knid`),
      KEY `channelid` (`channelid`),
      KEY `blockid` (`blockid`),
      KEY `username` (`username`)
      

  4.   

    你的这些索引的意义不大。最有用的索引还是 
    tbl_down 0 PRIMARY 1 aid A 206098 BTREE 这个用于排序。
      

  5.   

    您这种有范围的检索,不能同时使用一个索引检索和排序的可以先增加一个复合索引 (classid,isbest,status),执行下面的语句,就要看优化器是检索优先还是排序优先了explain SELECT aid,title,pubdate,uid,username,status,channelid,classid,typeid,isbest 
    FROM tbl_down 
    WHERE classid IN(10,89,49,50,59,79,80,87,88,81,82,83,85,86,138,139,140,141,142,143,144,145,146,147,137,389,397,398,399,400,392,401,402,403,393,404,405,406,407,394,413,414,415,416,421,424,425,395,100,101,352,353,354,355,356,396,408,409,410,422,423,411,412,426,12,55,56,57,58,95,96,97,98,99,11,51,52,53,54,90,91,92,93,94,179,427,294,304,305,306,307,308,309,295,310,311,312,313,296,314,315,316,317,318,319,320,321,297,322,323,324,325,298,326,327,328,329,330,362,363,299,331,332,333,334,300,335,336,337,338,339,340,341,301,342,343,344,345,302,346,347,348,349,303,350,358,359,360,361,1,434,13,14,15,220,364,365,366,238,156,157,158,159,160,186,187,188,189,190,2,433,19,20,21,221,290,291,292,25,26,27,222,367,368,369,240,30,28,29,191,192,193,194,195,196,197,198,32,33,223,370,371,372,241,35,36,151,152,153,154,155,39,224,373,374,375,242,40,41,42,169,170,171,172,173,43,44,45,225,376,377,378,243,46,47,48,199,200,201,61,62,63,226,379,380,381,244,64,65,66,202,431,432,69,227,382,383,384,245,70,71,72,203,204,205,206,207,208,73,74,75,228,385,386,387,246,76,77,78,209,210,211,212,213,214,215,174,175,176,430,428,429,357,351) AND isbest=0 AND status IN ('0','1') ORDER BY aid DESC LIMIT 0,20  
      

  6.   

    通过explain看到的结果(key列)只使用到了primary key
    注意,按照你说的status IN ('0','1') 三种值的情况,按照书上的理论,一般当匹配某一个值的数量大于总数的%15时就不要对此列使用索引,使用之后情况可能会还不如不使用
    只需要增加一个关于classid的索引就ok了,下面是我的测试结果,in会使用到索引[email protected]_monitor>select * from test;
    +----+-------+
    | id | value |
    +----+-------+
    |  1 |     1 | 
    |  2 |     2 | 
    |  3 |     3 | 
    +----+-------+
    3 rows in set (0.00 sec)[email protected]_monitor>insert into test(value) values(5)
        -> ;
    Query OK, 1 row affected (0.02 sec)[email protected]_monitor>insert into test(value) values(6);
    Query OK, 1 row affected (0.00 sec)[email protected]_monitor>show index from test;
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | test  |          0 | PRIMARY  |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         | 
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    1 row in set (0.01 sec)[email protected]_monitor>alter table test add index index_value(value);
    Query OK, 5 rows affected (0.07 sec)
    Records: 5  Duplicates: 0  Warnings: [email protected]_monitor>explain select * from test where value in (1,2,3,4);
    +----+-------------+-------+-------+---------------+-------------+---------+------+------+--------------------------+
    | id | select_type | table | type  | possible_keys | key         | key_len | ref  | rows | Extra                    |
    +----+-------------+-------+-------+---------------+-------------+---------+------+------+--------------------------+
    |  1 | SIMPLE      | test  | range | index_value   | index_value | 5       | NULL |    4 | Using where; Using index | 
    +----+-------------+-------+-------+---------------+-------------+---------+------+------+--------------------------+
    1 row in set (0.01 sec)[email protected]_monitor>
      

  7.   

    上面的方法不行的,已经有了classid这个索引了,但是没有使用的
      

  8.   

    现在的情况感觉着应该是最理想的了,它是根据aid来先查找出来最后的一部分记录信息的,如limit 10,20,系统可能取出来最后的多于20条的记录个数,然后再在classid里检查的,测试了一下,比起使用classid之类的索引速度要快上百倍以下了,用PRIMARY索引查询速度很快的.