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

如果格式、图片有问题，欢迎查看原文（可能会有更新、也欢迎交流）：《数据库上多列索引是否可以使用其查询单一列？》

今天被问到一个关于数据库索引（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)这些列组合上的索引。

所以答案有了吧！