对数据库索引的理解

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

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

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

 

Avatar_small
Ist PUC Question Pap 说:
Aug 16, 2022 10:15:45 PM

It was created for the Karnataka Secondary Education examination Board. The board administers exams for grades 1 through 11. It is one of the most well-known boards, and many public and private schools are linked with it. board on the same corporate website. After the test, students are looking for their KSEEB 11th class Question Paper 2023. Ist PUC Question Paper 2023 They may check this from the official web portal site, and we also have a direct link to check Ist PUC Important Question Paper 2023 quickly. For students, we have provided some instructions on how to check the Kar 11th Class Important Question Paper 2023, which they can get by following the links provided below.


登录 *


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