表中一个列既有单列索引,又有多列索引,查询时是怎么使用索引的?
表结构: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数据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)查询
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索引?
表结构: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数据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)查询
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索引?
KEY `in1` (`col2`),
KEY `in2` (`col2`,`col3`),这两个索引针对 where col2='sbzw';几乎是等效的。
我也知道key 'in1'('col2')和key 'in2'('col2','col3')几乎等效,但我现在就是疑惑为什么根据col2的值的不同,使用索引的时候也会不同。
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)
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| 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> explain select * from test use index(in1) where col2='sbzw';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | test | ref | in1 | in1 | 92 | const | 4 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row 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.00 sec)
mysql> explain select * from test use index(in1) where col2='szuc';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | test | ref | in1 | in1 | 92 | const | 7 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)在使用use index(in1)和不使用use index(in1)的时候,rows列值是不一样的,在第一条查询语句中,rows值在使用索引in2的时候比in1小。