用户名: 密码: 忘记密码? 注册
收藏此问题 发表新评论

索引使用的问题

表中一个列既有单列索引,又有多列索引,查询时是怎么使用索引的?
表结构:
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 15:06:54
先ANALYZE TABLE test 一下。让MYSQL分析一下索引。然后再试一下。

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

这两个索引针对 where col2='sbzw';几乎是等效的。
昵称: ACMAIN_CHM  时间: 2011-06-09 15:23:38
分析之后还是老样子。
我也知道key 'in1'('col2')和key 'in2'('col2','col3')几乎等效,但我现在就是疑惑为什么根据col2的值的不同,使用索引的时候也会不同。
昵称: zeldady  时间: 2011-06-09 15:44:28
show index from test 

看一下情况。
昵称: ACMAIN_CHM  时间: 2011-06-09 15:49:30
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 15:57:31
发表评论
昵称:
内容:
验证: