数据库索引

数据库索引简介

数据库索引的定义

数据库索引是一种数据结构。通过增加额外的写操作和存储空间来维护数据库索引，可以提高从数据库中读取数据的速度。通过索引，不需要搜索数据库的每一条记录，就可以快速地定位到特定的数据。索引可以建在在表中某一个字段或多个字段之上。总而言之：数据库索引是一种数据结构

数据库索引的作用

1. 用于支持快速地查找到数据

   • 若没有索引，通常需要遍历所有记录才能找到相应地数据（O(N)）;而通过索引，一般只需要O(log(N))次就可以定位到数据，提高了查找效率

2. 管理数据库约束

   • 索引通常还会被用于管理数据库约束，例如UNIQUE, EXCLUSION, PRIMARY KEY 和 FOREIGN KEY。当一个索引被定义为UNIQUE时，数据库同时创建一个隐式的约束。

Clustered Index & Non-clustered Index

Clustered Index（聚集索引/聚簇索引）

聚集索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。一个表只能有一个聚集索引，因为一个表的物理顺序只有一种情况，所以，对应的聚集索引只能有一个。如果某索引不是聚集索引，则表中的行物理顺序与索引顺序不匹配，与非聚集索引相比，聚集索引有着更快的检索速度。如下图，叶节点中直接包含了具体数据。

Non-clustered Index（非聚集索引/非聚簇索引）

与聚集索引不同，非聚集索引的逻辑顺序与磁盘上行的物理存储顺序不同。磁盘上的数据可以随意分布，而通过非聚集索引，可以在逻辑上为数据排序。如下图，叶节点没有包含具体的数据，而是包含了一个指向具体数据的指针。

区别

当索引通过二叉树的形式进行描述时，我们可以这样区分聚集与非聚集索引的区别：聚集索引的叶节点就是最终的数据节点，而非聚集索引的叶节仍然是索引节点，但它有一个指向最终数据的指针。

数据库索引的实现

常见的数据库索引实现有

1. 平衡树（B树）
2. B+树
3. Hashes（哈希）

B树，B+树

参考B-tree wiki，B+ tree wiki

Mysq中的索引

MySQL数据库支持多种索引类型，如B+ Tree索引，哈希索引，全文索引等等。下面只分析B+ Tree索引。

MyISAM索引实现

MyISAM引擎使用B+Tree作为索引实现，并且所有的索引都是非聚集索引。
下图是主键索引：

若在Col2上建立辅助索引，其依然是一个非聚集索引，与主键索引类似：

MyISAM引擎中，使用索引查找数据时，先通过索引获取到数据的物理地址，然后通过物理地址读取数据。

InnoDB索引实现

InnoDB引擎同样使用B+Tree作为索引实现，但与MyISAM不同，在InnoDB引擎中，主键索引是聚集索引，而辅助索引则是非聚集索引。下图是主键索引：

若在Col2上建立辅助索引，则是一个非聚集索引，叶节点的值为数据的主键：

在InnoDB中，通过主键索引，可以直接获取到具体的数据；而通过辅助索引，在叶节点获取到的是数据的主键，然后再通过主键索引最终获取到数据。

联合索引/多列索引

在上面的介绍中，我们主要是针对一个字段建立索引，而实际上，可以建立一个基于多个字段的索引。假设某张表中有a，b，c，d四个字段。现在在a，b，c上建立索引(a,b,c)（注意: a,b,c顺序不同建立的是不同的索引）。则索引首先会按a字段排序；在a字段相同的情况下按照b字段排序；在a，b字段相同的情况下按照c字段排序，以此类推。。。

最左前缀匹配原则

当建立联合索引时，该索引的所有最左前缀匹配可以用于优化查找。以上面建立的(a,b,c)索引为例，其所有最左前缀匹配为(a),(a,b),(a,b,c)。即涉及到(a),(a,b),(a,b,c)的查找都可以利用索引(a,b,c)，但涉及(a,c)的查找无法利用索引(a,b,c)，因为(a,c)不满足最左前缀匹配原则。

前缀索引

前缀索引就是针对字段的“前特定个字符”建立索引，而非对整个字段的值建立索引。显然，因为没有对完整的字段值建立索引，所以这样建立的索引更小，查询更快。MySQL的前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。
可以通过下面的预发建立前缀索引：

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

覆盖索引

覆盖索引（covering index）指一个查询语句的执行只需要从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。也可以称之为实现了索引覆盖。辅助索引不包含一整行的记录，因此可以大大减少IO操作。覆盖索引是mysql dba常用的一种SQL优化手段。

Mysql中高性能的索引策略

独立的列

如果查询中的列不是独立的，则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。因此应该简化WHERE条件，始终将索引列单独放在比较符号的一侧

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;(无法使用actor_id列的索引)
SELECT actor_id FROM sakila.actor WHERE actor_id = 4;(可以使用actor_id列的索引)

---

前缀索引和索引选择性

有时候需要索引很长的字符列，这会使索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值（也称为基数）和数据的记录总数（#T）的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

一般情况下某个列的前缀的选择性也是足够高的，足以满足查询性能。对于BLOB，TEXT或很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。通常情况，我们应该尽量使前缀的“基数”接近于完整列的“基数”。

前缀索引的缺点 : MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

---

多列索引

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。此时应该考虑建立多列索引。

---

选择合适的索引列顺序（针对B-Tree索引）

当不需要考虑排序和分组时，通常将选择性最高的列放到索引最前列。
有时可能需要根据那些运行频率最高的查询来调整索引列的顺序。

---

覆盖索引

如果一个索引包含（或者说是覆盖）所有需要查询的字段的值，我们就称为“覆盖索引”，使用覆盖索引能够极大地提高性能。

---

使用索引来做排序

MySQL可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，涉及索引时应该尽可能地同时满足这两种任务。只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且索引列的排序方向（倒序或正序）都一样时，MySQL才能使用索引来对结果排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀需求；否则，MySQL都需要执行排序操作，而无法利用索引排序。

---

冗余和重复索引

重复索引是指在相同的列上按照相同的顺序创建相同类型的索引，应该避免这样创建的重复索引，发现后也应该立即移除。MySQL允许在相同列上创建多个索引。MySQL需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。

冗余索引和重复索引又一些不同。如果创建了索引（A,B）,再创建索引（A）就是冗余索引，因为这只是前一个索引的前缀索引。大多数情况下，都不需要冗余索引，应该尽可能扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。

---

未使用的索引

若一个索引不再被使用，则应该考虑删除。可以通过一些工具找到未使用的索引，如Percona Toolkit中的pt-index-usage