数据库索引

数据库索引

索引是一种特殊的文件，它们包含着对数据表里所有记录的引用指针。

索引是一种数据结构。数据库索引是对数据库表中的一个或者多个列的值进行排序的结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，要占据物理空间。

索引类型

聚集索引：将数据行和相邻的索引键值紧凑的存储在一起，所以一个表只能有一个聚簇索引，因为不可能将数据存放在两个位置
可以通过CREATE CLUSTERED INDEX index_name ON table_name (column_name)创建聚集索引
非聚集索引：除聚簇索引外的其他索引都是非聚簇索引。

主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。

唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

可以通过 ALTER TABLE table_name ADD UNIQUE (column)创建唯一索引

可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2)创建唯一组合索引

普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。

可以通过ALTER TABLE table_name ADD INDEX index_name (column)创建普通索引

可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3)创建组合索引

全文索引： 是目前搜索引擎使用的一种关键技术。

可以通过ALTER TABLE table_name ADD FULLTEXT (column)创建全文索引

索引的基本原理

索引就是用来快速查询那些具有特定值的记录的，索引的原理就是将无序的数据变成有序的查询

1. 把创建了索引的列内容进行排序
2. 对排序结果生成倒排表
3. 在倒排表内容上拼上数据地址链
4. 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

索引的创建原则

1. 较频繁作为查询条件的字段才去创建索引

2. 更新频繁字段不适合创建索引

3. 不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度太低)

4. 定义有外键的数据列一定要建立索引。

5. 对于查询中很少涉及的列，重复值比较多的列不要建立索引。

索引的创建和删除

创建索引：

1. 创建表时创建索引

   CREATE TABLE user (
   	id INT auto_increment PRIMARY KEY,
   	first_name VARCHAR (16),
   	last_name VARCHAR (16),
   	id_card VARCHAR (18),
   	information text,
   	KEY name (first_name, last_name),
   	FULLTEXT KEY (information),
   	UNIQUE KEY (id_card)
   )
   

2. 使用ALTER TABLE命令去增加索引

   ALTER TABLE table_name ADD INDEX index_name (column_list);

3. 使用CREATE INDEX命令创建

   CREATE INDEX index_name ON table_name (column_list);

删除索引：

1. 根据索引名删除普通索引、唯一索引、全文索引：alter table 表名 drop KEY 索引名

2. 使用drop index 表名.索引名[,...n]命令删除索引

索引的数据结构

Hash索引，B+树索引，Ｂ树索引

B-tree 索引：
B-tree 是一种平衡的多叉排序树，，，B-Tree的特点：（M代表着阶数，代表着一个节点最多有多少个孩子节点

1. 定义任意非叶子结点最多只有M个儿子；且M>2；
2. 根结点的儿子数为[2, M]；
3. 除根结点以外的非叶子结点的儿子数为[M/2, M]；
4. 每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）
5. 非叶子节点的关键字个数 = 指向儿子的指针个数 - 1；
6. 非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；
7. 所有的叶子节点位于同一层

B-Tree的特性：

1. 关键字集合分布在整棵树中，
2. 任何关键字出现且只出现在一个节点中
3. 搜索有可能在非叶子节点中结束
4. 搜索性能等价于做二分查找，做一次查找最大的次数为 h 次，h为B-tree的深度。

在数据库中B-Tree索引的实现：

1. 根节点常驻内存
2. 根节点和非叶子节点的槽中存放了指向下一个子节点的指针，每个页中存放着一些关键字，与指针相对应，定义了子节点中值的上限与下限，存储引擎根据这些指针向下层查找， 但是叶子节点中只存放数据的物理地址，不再存放指针。
3. 将每一个节点设定为一个页的大小，这样只需要一次I/O就可以读取一个节点的内容，(这是因为页是计算机管理存储器的逻辑块，硬件和操作系统在进行内存和磁盘上的数据交换时往往以一个页作为基本单位)
4. 在叶节点和非叶子节点中，都存储了关键字（该关键字里包含了指向该索引数据本身的物理地址），在一次查找中，给定了某个关键字，如果在任何节点找到了该关键字（包括非叶结点）则就可以根据找到的关键字读取到该关键字所指向的实际数据。

B+tree 索引： 是B-Tree的变种
大部分的定义和B-Tree相同，但是它有独特于B-tree的地方，

1. 非叶结点的子树指针和关键字个数相同
2. 非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树（B-树是开区间）
3. 所有的叶子节点增加了一个指针，指针指向相邻的叶子节点。
4. 所有的关键字都在叶子节点中出现

B+Tree的特点:

1. 所有的关键字都出现在叶子节点中（稠密索引），而且叶子节点中的关键字恰好都是有序的
2. 不可能在非叶子节点查找成功，这是因为非叶子节点中存储的仍旧是索引，并没有存储实际的数据或者指向实际数据的物理地址，在叶子节点才存放的是实际的数据或者实际数据的物理地址。
3. 查找方式有两种： 一种是从根节点进行查找，另一种是可以从叶子节点的开头开始查找，因为叶子节点中存储了指向下一个叶子节点的指针，而且在数据库的实现中，叶子节点在实际的物理存储中是顺序存放的，也就是叶子节点都是集中在一块存储区域内存放的（这样的好处是大大提高了区间查询效率）。
4. 更适合文件系统