关系型数据库的单列索引与联合索引

数据库只做两件事情：存储数据、检索数据。而索引是在你存储的数据之外，额外保存一些路标（一般是B+树），以减少检索数据的时间。所以索引是主数据衍生的附加结构。一张表可以建立任意多个索引，每个索引可以是任意多个字段的组合。索引可能会提高查询速度（如果查询时使用了索引），但一定会减慢写入速度，因为每次写入时都需要更新索引，所以索引只应该加在经常需要搜索的列上，不要加在写多读少的列上。

使用单列索引和组合索引的时机#

在关系数据库中，索引是一种提升检索速度的数据结构，但是它会带来写入速度的损失，以及更多的存储空间占用。

通过一个字段而不是主键查询一张n条记录的数据表，需要扫描O(n)条记录（从技术上讲，n意味着该表使用的磁盘块的数量，但是为了便于理解我们简单的假设它是记录的条数）。举例来说，假设ssn是一个唯一字段，下面这条SQL查询平均需要读取n/2条记录才能找到匹配的记录。

SELECT \* FROM users WHERE users.ssn = '1234';

因为ssn是唯一的，所以在第一次找到这条记录时就会停止；然而如果查询字段不是唯一的，类似下面这条SQL语句会扫描n条数据（全表扫描）来找到所有的匹配记录：

SELECT \* FROM users WHERE users.first\_name = 'Teemo';

全表扫描是缓慢的，尤其当表的记录非常多的时候，这个时候可以创建索引来提升查询性能。

索引，或者更具体一些，单列索引是通过一种额外的数据结构B-Tree来对指定的特定的列进行排序。索引中的每一条记录包含一个指向数据表的指针，所以在索引中查找数据等价于在原始数据表中查询。举例，假设现在有下面这张users表：

ID	first_name	last_name	Class	Position	ssn
1	Teemo	Shroomer	Specialist	Top	2345
2	Cecil	Heimerdinger	Specialist	Mid	5461
3	Annie	Hastur	Mage	Mid	8784
4	Fiora	Laurent	Slayer	Top	7867
5	Garen	Crownguard	Fighter	Top	4579
6	Malcolm	Graves	Specialist	ADC	4578
7	Irelia	Lito	Figher	Top	5689
8	Janna	Windforce	Controller	Support	4580
9	Jarvan	Lightshield	Figher	Top	4579
10	Katarina	DuCouteau	Assassin	Mid	5608
11	Kayle	Hex	Specialist	Top	4794
12	Emilia	LeBlanc	Mage	Mid	3468
13	Lee	Sin	Fighter	Jungle	8085
14	Lux	Crownguard	Mage	Mid	4567
15	Sarah	Fortune	Marksman	ADC	6560
16	Morgana	Hex	Controller	Support	3457
17	Orianna	Reveck	Mage	Mid	9282
18	Sona	Buvelle	Controller	Support	4722
19	Jericho	Swain	Mage	Mid	5489
20	Shauna	Vayne	Marksman	ADC	2352
21	Xin	Zhao	Fighter	Jungle	6902
22	Yorick	Mori	Tank	Top	4840
23	Wu	Kong	Fighter	Jungle	4933

我们在users.first_name字段创建一个普通索引：

CREATE INDEX first_name_index ON users (first_name) USING BTREE;

这时会创建一个基于first_name字段的排序，并使用指针指向users表的主键，类似于下面这样：

first_name	Primary Key
Annie	3
Cecil	2
Emilia	12
Fiora	4
Garen	5
Irelia	7
Janna	8
Jarvan	9
Jericho	19
Katarina	10
Kayle	11
Lee	13
Lux	14
Malcolm	6
Morgana	16
Orianna	17
Sarah	15
Shauna	20
Sona	18
Teemo	1
Wu	23
Xin	21
Yorick	22

执行下面这条查询：

SELECT * FROM users WHERE first_name = 'Teemo';

此时，first_name字段已建立有顺序的索引，数据库在执行查询时，通过二分法查找将算法复杂度降到O(log_2(n))。

唯一索引#

除了性能方面的收益，索引也会被用于优化具有唯一性的字段。举例，假设我们不希望多个用户使用同一个手机号码，这是就可以在创建索引时添加UNIQUE修饰符。

CREATE UNIQUE INDEX ssn\_index ON users (ssn);

创建上述唯一索引后，如果users表中已经存在了一条对应字段值相同记录，则会引起一个错误。

避免必要的索引#

正如有一句谚语所说：“天下没有免费的午餐”，索引能提高性能但也是有成本的：

1. 额外的空间用于存储索引
2. 执行CREATE、UPDATE、DELETE等数据修改操作时，索引也会被更新

因此，事实上不必要的索引会导致性能整体性的降低，接下来是使用索引的几条准则：

• 不要在读少写多的表上创建索引，正如上面所说，索引提升了读性能但是降低了写性能
• 不要在大多数值都相同字段上使用索引，查询复杂度能达到O(log_2(n))的原因是二分法查找，但这只有在多数字段值都不同的情况下才有效
• 不要在确定大小的小表上使用索引，因为这并不会明显的提升性能。但要注意，有些表（例如users）虽然现在很小，但它未来可能会不断增长；但也有的表一直很小，冰激凌的口味毕竟是有限的。

单列索引和组合索引也是对字段排序的数据结构，但是与单列索引不同的是，联合索引中组合了多个字段。举例，再看下这张users表：

ID	first_name	last_name	class	position
1	Teemo	Shroomer	Specialist	Top
2	Cecil	Heimerdinger	Specialist	Mid
3	Annie	Hastur	Mage	Mid
4	Fiora	Laurent	Slayer	Top
5	Garen	Crownguard	Fighter	Top
6	Malcolm	Graves	Specialist	ADC
7	Irelia	Lito	Figher	Top
8	Janna	Windforce	Controller	Support
9	Jarvan	Lightshield	Figher	Top
10	Katarina	DuCouteau	Assassin	Mid
11	Kayle	Hex	Specialist	Top
12	Emilia	LeBlanc	Mage	Mid
13	Lee	Sin	Fighter	Jungle
14	Lux	Crownguard	Mage	Mid
15	Sarah	Fortune	Marksman	ADC
16	Morgana	Hex	Controller	Support
17	Orianna	Reveck	Mage	Mid
18	Sona	Buvelle	Controller	Support
19	Jericho	Swain	Mage	Mid
20	Shauna	Vayne	Marksman	ADC
21	Xin	Zhao	Fighter	Jungle
22	Yorick	Mori	Tank	Top
23	Wu	Kong	Fighter	Jungle

在class和position两列上创建一个联合索引：

CREATE INDEX class_pos_index ON users (class, position);

这时候创建了一个组合索引，对两个字段拼接进行排序，如下所示：

class-position	Primary Key
AssassinMid	10
ControllerSupport	16
ControllerSupport	18
ControllerSupport	8
FigherTop	7
FigherTop	9
FighterJungle	13
FighterJungle	21
FighterJungle	23
FighterTop	5
MageMid	12
MageMid	14
MageMid	17
MageMid	19
MageMid	3
MarksmanADC	15
MarksmanADC	20
SlayerTop	4
SpecialistADC	6
SpecialistMid	2
SpecialistTop	1
SpecialistTop	11
TankTop	22

下面是一个对联合索引的查询：

SELECT * FROM users WHERE  class = 'Specialist' AND position = 'Top';

经过上面一通操作，我们减少了检索时间，因为联合索引基于class-position排序，数据库可以在时间复杂度O(log_2(n))下查找到值SpecialistTop，而不是全表扫描。

受益于上述组合索引将class作为联合索引的第一个字段，基于class列的查询性能也会提升。因为基于class字段的索引基本上等同于联合索引class-position，所以我们不需要再单独为class建立索引。

SELECT * FROM users WHERE class = 'Specialist';

然而，对字段position的查询性能并不会有所变化，因为它是联合索引中的第二个字段。基于class-position排序的联合索引，不能用于快速检索position列的值

SELECT \* FROM users WHERE position = 'Top';

基于以上，联合索引组成字段的顺序是非常重要的，一个对column1 , column2 , column3 ,…, columnN若干字段的组合索引，对以下SQL语句是有效的：

SELECT * FROM table WHERE column1 = 'value';
SELECT * FROM table WHERE column1 = 'value1' AND column2 = 'value2';
SELECT * FROM table WHERE column1 = 'value1' AND column2 = 'value2' AND column3 = 'value3'...
SELECT * FROM table WHERE column1 = 'value1' AND column2 = 'value2' AND column3 = 'value3'... AND columnN = 'valueN'

联合索引建立指南#

和单列索引一样，联合索引也会降低写入速度，同时增加存储空间的占用量。建立联合索引时，选择字段以及字段顺序排列应当考虑以下几个原则：

• 如果一些字段倾向于在查询中同时出现，这时，为他们创建一个联合是一个不错的主意。例如，在上述users表中，建立一个(last_name, first_name)组合的联合主键是不错的选择
• 如果需要对字段field1创建索引，同时也需要创建联合索引(field1, field2)，此时只需要创建后者即可
• 与单列索引类似，组合字段值的重复度会影响联合索引的查询效率。很显然，如果两个字段重复度不高，联合索引组成的索引值重复度也不高，但我们仍然可以将高重复度的字段和低重复度的几个字段组合起来建立联合索引。

唯一组合索引和组合索引#

联合索引也可以将字段值组合设置为强制唯一。

通常，单个字段值不是唯一的，但组合后的字段是唯一的。例如，addresses表中有street,address_number,city，3个字段。我们不需要将street或house_number或city设置为唯一，因为不同的地址可能使用相同的上述值，但我们可能会希望street-house_number-city这个组合是唯一的。这时，我们可以使用联合索引，并添加UNIQUE修饰符：

CREATE UNIQUE INDEX index_st_no_city ON addresses (street, house_number, city);

原文地址：https://user3141592.medium.com/single-vs-composite-indexes-in-relational-databases-58d0eb045cbe