数据库索引

最新推荐文章于 2023-07-14 22:32:32 发布

Coder_by

最新推荐文章于 2023-07-14 22:32:32 发布

阅读量475

点赞数

分类专栏：数据库相关

本文链接：https://blog.csdn.net/i_silence/article/details/106968571

版权

数据库相关专栏收录该内容

26 篇文章 0 订阅

订阅专栏

索引机制
前言：数据库和数据库索引这两个东西是在服务器端开发领域应用最为广泛的两个概念，熟练使用数据库和数据库索引是后端开发人员在行业内生存的必备技能。数据库索引是用来提高数据库表的数据查询速度的。一个最优的索引能够轻易的将查询性能提高好几个数量级。

索引定义

数据库索引：对数据库表中要查询的字段建立索引其实就是把该字段按照一定的方式排序的结构。

索引的作用

能够大大的提高数据的查询检索速度
通过创建唯一性索引可以保证数据库中每一行的唯一性
可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
通过使用索引，可以在查询的过程中，使用查询优化器，提高系统的性能。

索引的弊端

索引会占用一部分存储空间，尤其在数据量很大的时候占用的存储空间可是很客观的；
一旦对数据进行了插入、删除、修改等操作，要对索引进行动态的维护。
但是总的来说利还是大于弊的，所以我们设计数据库的时候需要善于利用索引，善于优化索引。

什么地方该用索引，什么地方应该避免使用索引？

使用索引:

在数据量超过几百行之后就应该考虑建立索引，在主键上建立索引，保证数据的唯一性和组织表中的数据排列结构
在经常使用到的查询列上建立索引，比如 where name = “wang” ,经常做这样的查询，那么name上就应该建立索引
在经常进行范围查询的列上建立索引(可以建立聚簇索引，让索引的顺序和数据的物理存放顺序一致，这样大大的加快的查找的速度，变随机查找为顺序查找)
在经常用在连接的列上，在连接字段列上建立索引，这些列主要是一些外键，可以加快连接的速度；
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

不该使用索引：

数据量太小不要建立索引，因为维护的代价要高于建立索引之后优化的代价
经常频繁更新的列不要建立索引，因为一旦更新，就要对索引也要随之更新，如果更新的代价比查询的代价高，那就不要建立索引
不经常被引用、查询的列不要建立索引，因为没有必要
对于那些列的取值很少（比如性别），或者text等类型的大文本字段不要建立索引，大文本字段的索引也会很长，影响查询。

索引的建立方法

普通索引

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

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

create index myDeptIndex on detail(dept_id);

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

修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

创建表的时候直接指定

CREATE TABLE mytable( 
ID INT NOT NULL, 
username VARCHAR(16) NOT NULL, 
INDEX [indexName] (username(length)) 
);

删除索引

DROP INDEX [indexName] ON mytable;

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

创建索引

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

修改表结构

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

创建表的时候直接指定

CREATE TABLE mytable( 
ID INT NOT NULL, 
username VARCHAR(16) NOT NULL, 
UNIQUE [indexName] (username(length)) 
);

使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)

该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)

这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

ALTER TABLE tbl_name ADD INDEX index_name (column_list)

添加普通索引，索引值可出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

该语句指定了索引为FULLTEXT ，用于全文索引。

以下实例为在表中添加索引。

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

使用 ALTER 命令添加和删除主键

主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

MySQL数据库索引优化策略

1、索引列上不能使用表达式或者函数

例子：

select ...... from product where to_days(out_date) - to_days(current_data)<=30 to_days

就是使用了函数，out_date就是索引列。优化后：

select ...... from product where out_date<=data_add(current_data,interval 30 day)

这样对out_date索引列就没有使用函数。
2、联合索引，如何选择索引的顺序

经常会被使用到的列优先
选择性高的列优先
宽度小的列优先

3、索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

4、索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

5、like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

eg：MySQL只对一下操作符才使用索引：<、<=、=、>、>=、between、in以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引。

索引原理

索引根据其构造结构可以分为： B-tree索引， B+tree索引， Hash索引，全文索引，空间数据索引（R-Tree
先介绍几种树的概念：

B树是二叉的排序树，也叫二叉查找树
红黑树是B树的变种，是平衡的二叉排序树
B-Tree 也是B树的改进，而不过二叉变成了多叉，而且非叶子节点中也存储了数据，搜索有可能到非叶结点就结束。
B+Tree 是B-tree的变种，所有的关键字都出现在叶子节点上，一次查找搜索必然是到叶子节点上才结束的。

B-tree 索引

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

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

B-Tree的特性

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

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

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

B+tree 索引：是B-Tree的变种

大部分的定义和B-Tree相同，但是它有独特于B-tree的地方，

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

B+Tree的特点

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

B+Tree 在 MySQL中的实现。

MySQL 中的 InnoDB和MyISAM存储引擎使用的就是B+Tree的实现方案：
在InnoDB中，数据的物理存放顺序是按照设定聚簇索引的列的顺序进行组织的（实质上是按照主键的顺序组织数据），聚簇索引（一定包含主键，但也可能是主键和其他列的复合索引），对于InnoDB中来说，它的索引文件和数据文件是同一个文件，所以读取进来的页中（也就是一个节点）包含了key和data（key 为索引，data为实际的数据）

对于聚簇索引来说， key = 索引键值；data = 实际数据本身
对于非聚簇索引，key = 索引键值， data = 聚簇索引的值

B+Tree 和B-Tree的性能比较

相对来说，B-Tree键值只在索引中出现一次，比B+Tree能节省存储空间；
B-Tree树的键值位置不定，使得在插入删除操作中复杂度明显增加，B+Tree的插入删除操作性能比B-Tree更好
B+Tree因为叶子节点的物理存放是集中在一块存储区域的而且按顺序存放，所以 B+Tree进行区间查询的速度会更加的快。