MYSQL执行计划

QUOTE:原帖由 zty5984 于 2011-7-25 18:48 发表
create table index_test (id  int primary key , col1 int , col2 int);
create index t  on index_test(col1, col2)
explain select * from index_test  where col1 in(100, 200 ) and col2 = 200;
1 row in set (0.00 sec)

mysql> explain select * from t where col1 in (100,200)  col2 = 200;
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | index_test    | index | t             | t    | 10      | NULL |    1 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
1 row in set (0.00 sec)


create table index_test (id  int  , col1 int , col2 int);
create index t  on index_test(col1, col2)
explain select * from index_test  where col1 in(100, 200 ) and col2 = 200;

mysql> explain select * from t where col1 in (100,200) and col2=200;
----+-------------+-------+------+---------------+------+---------+------+------+-------------+
id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  1 | SIMPLE      | index_test     | ALL  | t             | NULL | NULL    | NULL |    1 | Using where |
----+-------------+-------+------+---------------+------+---------+------+------+-------------+
row in set (0.00 sec)


mysql> explain select * from t where col1 in (100,200)  col2 = 200;

explain select * from index_test  where col1 in(100, 200 ) and col2 = 200;

为什么加一个一个主键索引，这两个SQL的差别为什么那么大？