案例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 |
+-------+------------------------------------------
请问怎样提高查询效率?为什么联合索引没有用上呢?
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 |
+-------+------------------------------------------
请问怎样提高查询效率?为什么联合索引没有用上呢?
解决方案 »
- mysql增删改太慢,求高手知道配置文件·
- 问个非常简单的问题
- 帮忙看看这SQL语句错在哪里?
- 对有auto_increment的表插入数据的问题
- 什么版本的JDBC支持MySQL5.1.41,可以将Java连接MySQL的代码贴出来吗
- 小女子想学mysql,请问大家哪里有好的中文教程,推一下
- 关于innodb类型表的count准确性
- mysql里怎么用一条sql语句判断某个字段是否存在 ?
- 关于文件检索,高手帮忙!!!!!!!!!
- 我在一台有静态地址的机器上(Linux)安装了MySql,在其它机器上用PHP或C怎样远程访问MySql?
- 关于使用变量值修改auto_increment的问题
- MySql 数据优化 奇怪问题.!
--------------------------------------------------
| 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 |
+-------+-----------------------------------------
+----------+--------------+-------------+
| Key_name | Seq_in_index | Column_name |
+----------+--------------+-------------+
| ix_3 | 1 | gene_chr |
| ix_3 | 2 | gene_start |
| ix_3 | 3 | gene_end |
+----------+--------------+-------------+
或则楼主就建立2个索引一个是`ix_1` (`gene_chr`),`ix_2` (`gene_chr`,`gene_start`) 看mysql走哪个索引
为什么呢?复合索引只能有两列?还是我的select语句没有写对?
使用的是`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的结果和单列索引一样