解决方案 »
- 如果数据库中a表的字段field是int类型,则改为float
- winxp下,c#连接mysql,除了安装MySQLDriverCS,有没有更好的方法?
- 大家看看我这个存储过程中如何加入事务控制?
- mysql更改默认数据库路径的问题
- 关于一条SQL语句效率的问题
- linux s5下mysql的默认安装路径在那里?
- 请问高手--在freeBSD下遇到编译时,找不到mysql库的问题怎么解决(高分求)
- 关于MySQL异常问题
- MYSQL数据库迁移用什么工具
- mysql批量插入忽略错误
- 创建表时如果有两列都是datetime类型,并且都设置默认值,要怎么解决呢?
- MySQL ODBC 3.15 driver安装了,odbc中找不到。
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
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`)
tbl_down 0 PRIMARY 1 aid A 206098 BTREE 这个用于排序。
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
注意,按照你说的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>