【原创】为什么使用数据索引能提高效率？（本文针对mysql进行概述）（更新）

 

本文只针对mysql进行相关方面的说明。

首先我要问，“数据索引是有序无序的？”。答：“当然是有序的了。”

再问，一个SQL语句可以有几个索引？你先别往下看结果，你这想下,可以使用几个索引呢？

正确的是：“1个”。有人肯定会疑惑。对，当初我接受这个概念也是挺纳闷的，后来在实际中得到了解答。那么，你需自己找答案了。

 

在有序的情况下，通过索引查询一个数据是无需遍历索引记录的；

极端情况下，数据索引的查询效率为二分法查询效率，趋近于 log2(N)；

在进行索引分析和SQL优化时，可以将数据索引字段想象为单一有序序列，并以此作为分析的基础。

如以下的SQL，如何进行优化呢？

select * from user where area=’$area’ and sex=’$sex’ order by lastlogin desc limit 0,30;

假设，这是在高访问量的情况下，高频率的情况下如何进行优化？

答：建立复合索引.

area+sex+lastlogin 三个字段的复合索引,如何理解？

1 如果只使用area会怎样？搜索会把符合area的结果全部找出来，然后在这里面遍历，选择命中sex的并排序。 遍历所有 area=’$area’数据。

2 如果使用了area+sex，略好，仍然要遍历所有area=’$area’ and sex=’$sex’数据，然后在这个基础上排序。

3 Area+sex+lastlogin复合索引时（切记lastlogin在最后），该索引基于area+sex+lastlogin 三个字段合并的结果排序。

　　

不知道到这你是否看明白没有？？这个不妨你在本地打个环境。自己动手试试看。

下面我用我自己的工作中的案例说下，这索引前后的区别：

 

我们有个业务需求是这样的：

 

SELECT * FROM `cu_banuser` WHERE uid =386264 AND fid =2 ORDER BY `endtime`

 

1 这是一张表结构 如图。此表只有一个主键作为索引。

那么我们用explain看下吧: 关于explain，强烈建议：请大家自己baidu，有必要去了解认识它。

EXPLAIN SELECT * FROM `cu_banuser` WHERE uid =386264 AND fid =2 ORDER BY `endtime`

下面的是其explain的结果：

看到其中的 type possible_keys 尤其是rows了吗?影响的结果集是2751行。

 

2 我们现在将UID做为索引：

 

再explain下：

看划线的，你们会惊喜的发现 rows已经是2了。证明我们的努力没有白费。

 

 

3 我们继续添加剩下的索引fid；

 

好，我们再explain下，看结果：

你会发现什么呢？呵呵，对了 rows=1了。

 

4 继续给endtime加索引

explain看下：

 

我们的rows=1了，很明显的如果在大数据量的情况下，我们的获得的成果是很可喜的！我们一切从大数量出发。

 

好，不知道你注意到开篇就提到的2个问题吧？第二个问题是什么？一个sql用几个索引？答案是1个。

 

看到这，不知道你有没有感悟出来呢？我们在实际工作中用到最多的就是复合索引！

 

 

这就是你为什么看到上图的框中的索引是这么建的!

 

补充下 从最好到最差的连接类型为 ：const、eq_reg、ref、range、index和ALL  

综上所述：

1 一个sql只用一个索引；

2在实际工作中用到最多的是复合索引；

3 复合索引有严格的顺序之分；

4 遵循偏左原则；