数据库上多列索引是否可以使用其查询单一列?

今天被问到一个关于数据库索引(index)的问题,一下子被问住了。于是网上找了资料整理学习了下。问题如下:

如果在一个表中的A和B上建立了联合索引(多列索引),那么如果我查询B列的时候,是否会使用这个索引。

答案在整理知识点之后就很简单了。

什么是索引?

索引是存储的表中一个特定列的值数据结构,一般是B-Tree,还有HashTable Index和R-Tree,我们这里只讨论B-Tree。索引是在表的列上创建,索引只包含包含一个表中列的值,并且这些值存储在一个数据结构中。索引是一种数据结构 。

当我们查询没有索引的列的时候,需要对比所有记录,而索引已经排序,所以查询的时候可以快速找到所有对应的值。比如我们在姓名中寻找“Chris”,没有索引的情况下,需要核对整个列表的数据,以免最最后一列有chris,但是有索引之后,因为已经排序,所以所有chris都在一起,所以非常高效。

索引存储了指向表中某一行的指针

如果我们在索引里找到某一条记录作为索引的列的值,如何才能找到这一条记录的其它值呢?这是很简单 – 数据库索引同时存储了指向表中的相应行的指针。指针是指一块内存区域, 该内存区域记录的是对硬盘上记录的相应行的数据的引用。因此,索引中除了存储列的值,还存储着一个指向在行数据的索引。

数据库怎么知道什么时候使用索引?

SELECT * FROM Employee WHERE Employee_Name = ‘Chris’

当这个SQL 运行时,数据库会检查在查询的列上是否有索引。假设Employee_Name列上确实创建了索引,数据库会接着检查使用这个索引做查询是否合理 – 因为有些场景下,使用索引比起全表扫描会更加低效。如何合理就使用索引。

如何创建索引

CREATE INDEX name_index
ON Employee (Employee_Name)

如果是联合索引(多列索引)

CREATE INDEX name_index
ON Employee (Employee_Name, Employee_Age)

索引的代价?

那么,使用数据库索引有什么缺点呢?其一,索引会占用空间 – 你的表越大,索引占用的空间越大。其二,性能损失(主要值更新操作),当你在表中添加、删除或者更新行数据的时候, 在索引中也会有相同的操作。

单列索引VS多列索引

假设我们要在用户表分别查询firstname、lastname、age,我们如何建立索引呢?

Select id  
FROM people  
Where firstname='Mike'   
      AND lastname='Sullivan' AND age=17;

我们可以考虑在单个列上创建索引,比如firstname、lastname或者age列。如果我们创建firstname列的索引,MySQL将通过这个索引迅速把搜索范围限制到那些firstname=’Mike’的记录,然后再在这个“中间结果集”上进行他条件的搜索:它首先排除那些lastname不等于“Sullivan”的记录,然后排除那些age不等于17的记录。当记录满足所有搜索条件之后,MySQL就返回最终的搜索结果。

由于建立了firstname列的索引,与执行表的完全扫描相比,MySQL的效率提高了很多,但我们要求MySQL扫描的记录数量仍旧远远超过了实际所需要的。虽然我们可以删除firstname列上的索引,再创建lastname或者age列的索引,但总地看来,不论在哪个列上创建索引搜索效率仍旧相似。

这时候我们要建立firstname、lastname、age的多列索引。

如果在firstname、lastname、age这三个列上分别创建单列索引,效果是否和创建一个firstname、lastname、age的多列索引一样呢?

答案是否定的,两者完全不同。当我们执行查询的时候,MySQL只能使用一个索引。如果你有三个单列的索引,MySQL会试图选择一个限制最严格的索引。但是,即使是限制最严格的单列索引,它的限制能力也肯定远远低于firstname、lastname、age这三个列上的多列索引。

多列索引中最左前缀

多列索引还有另外一个优点,它通过称为最左前缀(Leftmost Prefixing)的概念体现出来。继续考虑前面的例子,现在我们有一个firstname、lastname、age列上的多列索引,我们称这个索引为fname_lname_age。当搜索条件是以下各种列的组合时,MySQL将使用fname_lname_age索引:

  • firstname,lastname,age
  • firstname,lastname
  • firstname

也就是,它相当于我们创建了(firstname,lastname,age)、(firstname,lastname)以及(firstname)这些列组合上的索引。

所以答案有了吧!

如果喜欢本文,给个好评呗!
[参与人数: 0 平均分: 0/5]

订阅可以第一时间收到更新通知哟~

发表评论

电子邮件地址不会被公开。