数据库索引最全详解(类型优缺及原理设计)

什么是数据库索引？

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询，更新数据库中表的数据。

 

数据库索引的优缺点

索引的目的是提高查找效率，对数据表的值集合进行了排序，并按照一定数据结构进行了存储。

1.数据索引的优点

• 通过创建索引，可以在查询的过程中，提高系统的性能；
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；
• 在使用分组和排序子句进行数据检索时，可以减少查询中分组和排序的时间；

2.数据库索引的缺点

• 索引的存储需要占用磁盘空间；
• 当数据的量非常巨大时，索引的创建和维护所耗费的时间也是相当大的；
• 当每次执行CRU操作时，索引也需要动态维护，降低了数据的维护速度。

 

数据库索引类型有哪些

数据库的索引类型主要分为4类。

1.普通索引

最基本的索引，它没有任何限制，它有以下几种创建方式:

1)直接创建

CREATE INDEX indexName ON mytable(username(length)); 

注意：如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

2)修改表结构创建

ALTER mytable ADD INDEX [indexName] ON (username(length)) 

3)创建表的时候直接指定索引

CREATE TABLE mytable(  

ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  

INDEX [indexName] (username(length))  

); 

4)删除索引

DROP INDEX [indexName] ON mytable; 

2.唯一索引

唯一索引它与普通索引类似，不同之处：索引列的值必须唯一，但允许有空值，创建时加上关键字UNIQUE。

如果是组合索引，则列值的组合必须唯一，它有以下几种创建方式：

1)直接创建（关键字 UNIQUE）

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

2)修改表结构时创建（UNIQUE）

ALTER mytable ADD UNIQUE [indexName] ON (username(length)) 

创建表的时候直接指定唯一索引（UNIQUE）：

CREATE TABLE mytable(  

ID INT NOT NULL,   

username VARCHAR(16) NOT NULL,  

UNIQUE [indexName] (username(length))  

);

3.主键索引

它是一种特殊的唯一索引，不允许有空值，一般是在建表的时候同时创建主键索引。

创建表时创建索引：

CREATE TABLE C(  

ID INT UNSIGNED NOT NULL auto_increment,   
 
username VARCHAR(16) NOT NULL,  
 
PRIMARY KEY(ID)  

);

也可后期追加创建：

alter table mytable add primary key(列名)

4.组合索引

索引按物理存储角度分可分为：

1)聚集索引

在聚集索引中，叶结点也即数据结点，所有数据行的存储顺序与索引的存储顺序一致。

表记录的排列顺序和索引的排列顺序一致，所以查询效率快,只要找到第一个索引值记录，其余连续性的记录在物理上一样连续存放。

聚集索引的缺点就是修改慢，因为为了使表记录和索引的排列顺序一致，所以在插入记录的时候会对数据页重新排序。

 

2)非聚集索引

非聚集索引的叶子层并不和实际数据页相重叠，而采用叶子层包含一个指向表记录的指针。

聚集索引是一种稀疏索引，数据页上一级的索引页存储的是页指针，而不是行指针。

而对于非聚集索引，则是密集索引，在数据页的上一级索引页它为每一个数据行存储一条索引记录。

 

数据库索引原理

想要理解索引原理必须清楚一种数据结构就是b tree或者 b tree。

B树是外部存储器的数据结构的一个很好的例子，它通常用于数据库和文件系统。

上图展示了一种可能的索引方式：左边是数据表，一共有两列七条记录。

最左边的是数据记录的物理地址，注意：逻辑上相邻的记录在磁盘上也并不是一定物理相邻的。

为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针。

这样就可以运用二叉查找在 O(log2n) 的复杂度内获取到相应数据。

B 的特性：
1.所有数据都存储在叶子结点的链表中，且链表中的数据都是有序存放的；
2.叶子节点间用指针相连。
3.不可能在非叶子结点命中；
4.非叶子结点相当于是叶子结点的索引，叶子结点相当于是存储数据的数据层；
5.更适合文件索引系统；

B 的性能：等于在数据集合做一次二分查找。

B 树索引被广泛应用于数据库、文件系统等场景。

B Tree与B Tree的区别：

• B 树只有达到叶子结点才命中；
• B-树可以在非叶子结点命中；
• B 树是可变的 n 元树，通常每个节点具有大量子节点；
• B 树由根，内部节点和叶组成。根可以是叶或具有两个或更多个子节点的节点；
• B 树可以被视为B树，其中每个（内部）节点仅包含键（不是键值对），并在其底部附加一层链式的叶子节点；

B 树的主要价值在于存储数据，以便在面向块的存储上下文，特别是文件系统中进行高效检索。

作者简介

陈睿|mikechen，10年+大厂架构经验，BAT资深面试官，就职于阿里巴巴、淘宝、百度等一线互联网大厂。

👇阅读更多mikechen架构文章👇

阿里架构 |双11秒杀 |分布式架构 |负载均衡 |单点登录 |微服务 |云原生 |高并发 |架构师

以上

关注作者「mikechen」公众号，获取更多技术干货！

后台回复【架构】，即可获取《阿里架构师进阶专题全部合集》，后台回复【面试】即可获取《史上最全阿里Java面试题总结》