联合索引的问题

Question

案例1
root@localhost : test1 Tue Jan 11 16:03:45 2011>show index from gene;
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardi
nality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+
| gene  |          1 | ix_3     |            1 | gene_chr    | A         |
     7 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
root@localhost : test1 Tue Jan 11 16:03:36 2011>explain select query.*,source,st
yle,gene_start,gene_end,strand,frame,attributes,gene_name from gene,query where
gene_chr=chr and gene_start<=end and gene_end>=start;
+----+-------------+-------+------+---------------+------+---------+------------
-----+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref
     | rows  | Extra       |
+----+-------------+-------+------+---------------+------+---------+------------
-----+-------+-------------+
|  1 | SIMPLE      | query | ALL  | NULL          | NULL | NULL    | NULL
     | 16533 |             |
|  1 | SIMPLE      | gene  | ref  | ix_3          | ix_3 | 258     | test1.query
.chr | 78588 | Using where |
+----+-------------+-------+------+---------------+------+---------+------------
-----+-------+-------------+
2 rows in set (0.00 sec)
案例2
root@localhost : test2 Tue Jan 11 16:02:54 2011>show index from gene;
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardi
nality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+
| gene  |          1 | ix_3     |            1 | gene_chr    | A         |
     7 |     NULL | NULL   | YES  | BTREE      |         |
| gene  |          1 | ix_3     |            2 | gene_start  | A         |
314351 |     NULL | NULL   | YES  | BTREE      |         |
| gene  |          1 | ix_3     |            3 | gene_end    | A         |
628703 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)
root@localhost : test2 Tue Jan 11 16:02:36 2011>explain select query.*,source,st
yle,gene_start,gene_end,strand,frame,attributes,gene_name from gene,query where
gene_chr=chr and gene_start<=end and gene_end>=start;
+----+-------------+-------+------+---------------+------+---------+------------
-----+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref
     | rows  | Extra       |
+----+-------------+-------+------+---------------+------+---------+------------
-----+-------+-------------+
|  1 | SIMPLE      | query | ALL  | NULL          | NULL | NULL    | NULL
     | 16533 |             |
|  1 | SIMPLE      | gene  | ref  | ix_3          | ix_3 | 258     | test2.query
.chr | 78588 | Using where |
+----+-------------+-------+------+---------------+------+---------+------------
-----+-------+-------------+
2 rows in set (0.00 sec)
--------------------------------------------------------------------------
我很不解，为什么我建立了联合索引，mysql好像没有使用。。
从explain的结果来看，案例2的联合索引与案例1的单列索引扫描表的次数都是一样？show create table gene的结果为：
------------+
| gene  | CREATE TABLE `gene` (
  `gene_chr` varchar(255) DEFAULT NULL,
  `source` varchar(255) DEFAULT NULL,
  `style` varchar(50) DEFAULT NULL,
  `gene_start` int(11) DEFAULT NULL,
  `gene_end` int(11) DEFAULT NULL,
  `gene_score` char(2) DEFAULT NULL,
  `strand` char(5) DEFAULT NULL,
  `frame` char(5) DEFAULT NULL,
  `attributes` varchar(255) DEFAULT NULL,
  `gene_name` varchar(255) DEFAULT NULL,
  KEY `ix_3` (`gene_chr`,`gene_start`,`gene_end`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+------------------------------------------
请问怎样提高查询效率？为什么联合索引没有用上呢？

Answer 1

show create table query的结果为：
--------------------------------------------------
| query | CREATE TABLE `query` (
  `name` varchar(255) DEFAULT NULL,
  `chr` varchar(255) DEFAULT NULL,
  `start` int(11) DEFAULT NULL,
  `end` int(11) DEFAULT NULL,
  `antibody` varchar(255) DEFAULT NULL,
  `maxLevel` double DEFAULT NULL,
  `score` double DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------

Answer 2

|  1 | SIMPLE      | gene  | ref  | ix_3          | ix_3 | 258     | test2.query.chr | 78588 | Using where |索引被使用了，这个行数的多少并不代码索引没用啊。另外你的索引中最后一项没有意义，基本上只有前两项起作用
+----------+--------------+-------------+
| Key_name | Seq_in_index | Column_name |
+----------+--------------+-------------+
| ix_3     |            1 | gene_chr    |
| ix_3     |            2 | gene_start  |
| ix_3     |            3 | gene_end    |
+----------+--------------+-------------+

Answer 3

在复合索引里，仅仅只能保存一个range类型的查询字段，并且要放到复合索引的末尾，否则，range类型查询字段后面的索引无效。没有必要再加,`ix_3` (`gene_chr`,`gene_start`)就可以了,不过楼主的第2个列子,不是有在用ix_3这个索引吗;
或则楼主就建立2个索引一个是`ix_1` (`gene_chr`),`ix_2` (`gene_chr`,`gene_start`) 看mysql走哪个索引

Answer 4

为什么呢？复合索引只能有两列？还是我的select语句没有写对？

Answer 5

使用的是`ix_2`
但是
+----+-------------+-------+------+---------------+------+---------+------------
-----+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------------
-----+-------+-------------+
| 1 | SIMPLE | query | ALL | NULL | NULL | NULL | NULL
  | 16533 | |
| 1 | SIMPLE | gene | ref | ix_3 | ix_3 | 258 | test2.query
.chr | 78588 | Using where |
+----+-------------+-------+------+---------------+------+---------+------------
-----+-------+-------------+
还是没变 啊
explain的结果和单列索引一样

Answer 6

你把ix_3先删除掉,建立ix_1` (`gene_chr`),`ix_2` (`gene_chr`,`gene_start`) 来看结果