对数据库索引的理解

陈着 posted @ Mar 09, 2011 03:57:46 PM in mysql , 1874 阅读

最近在写一个项目,其中涉及表test的访问,我想设计复合索引以提高查询效率。

表test如下:

id: INT primary key
x1: INT 
x2: INT 
x3: INT 

我用存储过程生成200w行数据

建立索引:

create index x1_x2_x3 on test(x1, x2, x3);

查询语句:

select * from test where x1 > xx and x2 < xx and x3 > xx;

explain之后发现type为all,也就是扫描全表,索引x1_x2_x3并没有用到,这让我百思不得其解;经过不断的纠结与实验后,基本找到了答案:索引需要对每一列有一个范围约束,比如:select *  from test where x1 > xx and x1 < xx...;这样type才能为range,ps:range不能小于总行数的四分之一。所以查询应该是类似:

select * from test where x1 > xx and x1 < xx and x2 < xx and x2 > xx and x3 > xx;

本来到此结束了,我又蛋疼的想分析下单独索引与复合索引的效率:

测试一组数据:

x1,x2,x3分别索引                             0.01s    扫描了48686行
X1,X2分别索引                                 0.10s    
x1索引                                         0.18s
x1_x2_x3复合索引                              0.20s   扫描了40117行
无索引                                         0.34s

我个人感觉复合索引虽然扫描行数少,但是有一部分资源消耗,导致没有单独索引快。

 


登录 *


loading captcha image...
(输入验证码)
or Ctrl+Enter