数据库索引

Darkgel

数据库索引简介

数据库索引的定义

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

数据库索引的作用

  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 wikiB+ tree wiki

Mysq中的索引

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

MyISAM索引实现

MyISAM引擎使用B+Tree作为索引实现,并且所有的索引都是非聚集索引
下图是主键索引:
MyIASAM主键索引
若在Col2上建立辅助索引,其依然是一个非聚集索引,与主键索引类似:
MyIASAM辅助索引
MyISAM引擎中,使用索引查找数据时,先通过索引获取到数据的物理地址,然后通过物理地址读取数据。

InnoDB索引实现

InnoDB引擎同样使用B+Tree作为索引实现,但与MyISAM不同,在InnoDB引擎中,主键索引是聚集索引,而辅助索引则是非聚集索引。下图是主键索引:
InnoDDB主键索引
若在Col2上建立辅助索引,则是一个非聚集索引,叶节点的值为数据的主键:
InnoDB辅助索引
在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

阅读 4.1k

Darkgel
Darkgel的专栏,记录程序人生。

Hello,World!

325 声望
29 粉丝
0 条评论

Hello,World!

325 声望
29 粉丝
文章目录
宣传栏