索引使用的问题

表中一个列既有单列索引,又有多列索引,查询时是怎么使用索引的?
表结构:
SQL code

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col2` varchar(30) NOT NULL,
  `col3` varchar(30) NOT NULL,
  `col4` varchar(30) NOT NULL,
  `col5` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `in1` (`col2`),
  KEY `in2` (`col2`,`col3`),
  KEY `in3` (`col4`,`col5`)
) ENGINE=MyISAM AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


表内使用以下存储过程插入100000数据
SQL code

mysql> show create procedure ins_t2\G
*************************** 1. row ***************************
           Procedure: ins_t2
            sql_mode:
    Create Procedure: CREATE DEFINER=`aiml`@`localhost` PROCEDURE `ins_t2`()
begin
declare i int default 0;
while i<100000 do
insert into test(col2,col3,col4,col5) values(
concat(char(round(rand()*25+97)),char(round(rand()*25+97)),char(round(rand()*25+97)),char(round(rand()*25+97))),
concat(char(round(rand()*25+97)),char(round(rand()*25+97)),char(round(rand()*25+97)),char(round(rand()*25+97))),
concat(char(round(rand()*25+97)),char(round(rand()*25+97)),char(round(rand()*25+97)),char(round(rand()*25+97))),
concat(char(round(rand()*25+97)),char(round(rand()*25+97)),char(round(rand()*25+97)),char(round(rand()*25+97))));
set i:=i+1;
end while;
end
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8_general_ci
1 row in set (0.02 sec)


查询
SQL code
mysql> select * from test where col2='sbzw';
+-------+------+------+------+------+
| id    | col2 | col3 | col4 | col5 |
+-------+------+------+------+------+
| 17295 | sbzw | jdqs | trab | dpnu |
| 76974 | sbzw | jfyf | fgsx | ixpf |
| 81760 | sbzw | jgea | pyya | esfu |
+-------+------+------+------+------+
3 rows in set (0.00 sec)

mysql> explain select * from test where col2='sbzw';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | test  | ref  | in1,in2       | in2  | 92      | const |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> select * from test where col2='szuc';
+-------+------+------+------+------+
| id    | col2 | col3 | col4 | col5 |
+-------+------+------+------+------+
| 15812 | szuc | xmne | gufr | tshg |
| 24351 | szuc | yqhk | gybj | tpty |
| 35319 | szuc | ypdv | tiju | yhqh |
| 45451 | szuc | xjcj | mjlb | vdbx |
| 83420 | szuc | bvfk | ksnl | rzxs |
| 91216 | szuc | ynuk | qacl | xhrm |
| 92326 | szuc | auxf | htbx | momq |
+-------+------+------+------+------+
7 rows in set (0.00 sec)
mysql> explain select * from test where col2='szuc';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | test  | ref  | in1,in2       | in1  | 92      | const |    7 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.03 sec)


疑问:为什么第一条查询语句使用in2索引而第二条使用in1索引?

作者: zeldady   发布时间: 2011-06-09

先ANALYZE TABLE test 一下。让MYSQL分析一下索引。然后再试一下。

理论上 
  KEY `in1` (`col2`),
  KEY `in2` (`col2`,`col3`),

这两个索引针对 where col2='sbzw';几乎是等效的。

作者: ACMAIN_CHM   发布时间: 2011-06-09

分析之后还是老样子。
我也知道key 'in1'('col2')和key 'in2'('col2','col3')几乎等效,但我现在就是疑惑为什么根据col2的值的不同,使用索引的时候也会不同。

作者: zeldady   发布时间: 2011-06-09

show index from test 

看一下情况。

作者: ACMAIN_CHM   发布时间: 2011-06-09

SQL code

mysql> show index from test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-----
-------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Inde
x_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-----
-------+---------+
| test  |          0 | PRIMARY  |            1 | id          | A         |      100000 |     NULL | NULL   |      | BTRE
E      |         |
| test  |          1 | in1      |            1 | col2        | A         |       25000 |     NULL | NULL   |      | BTRE
E      |         |
| test  |          1 | in2      |            1 | col2        | A         |       25000 |     NULL | NULL   |      | BTRE
E      |         |
| test  |          1 | in2      |            2 | col3        | A         |      100000 |     NULL | NULL   |      | BTRE
E      |         |
| test  |          1 | in3      |            1 | col4        | A         |       25000 |     NULL | NULL   |      | BTRE
E      |         |
| test  |          1 | in3      |            2 | col5        | A         |      100000 |     NULL | NULL   |      | BTRE
E      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-----
-------+---------+
6 rows in set (0.00 sec)

作者: zeldady   发布时间: 2011-06-09