陈着's Blog

最近在写一个项目，其中涉及表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

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