数据库之索引

最新推荐文章于 2023-04-22 01:51:15 发布

dyjsteve

最新推荐文章于 2023-04-22 01:51:15 发布

阅读量202

点赞数

分类专栏：数据库索引文章标签：索引原理

本文链接：https://blog.csdn.net/Future_46/article/details/101058541

版权

数据库同时被 2 个专栏收录

3 篇文章 0 订阅

订阅专栏

索引

1 篇文章 0 订阅

订阅专栏

1.简介

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。

索引的一个主要目的就是加快检索表中数据，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。

特点

建立索引的目的是加快对表中记录的查找或排序。为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)

注意事项

下列情况应该创建索引：

在经常需要搜索的列上，可以加快搜索的速度；
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

不应该创建索引：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少,不利于使用索引。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改操作远远多于检索操作时，不应该创建索引。

2.案例

最普通的情况，是为出现在where子句的字段建一个索引。

CREATE TABLE mytable(
    idserial int primary key,
    category_id int default 0not null ,
    user_id int default 0not null ,
    adddate int default 0not null
);

如果在查询时常用类似以下的语句：

SELECT * FROM mytable WHERE category_id=1;

最直接的应对之道，是为category_id建立一个简单的索引：

CREATE INDEX mytable_categoryid ON mytable (category_id);

OK.如果有不止一个选择条件呢？例如：

SELECT * FROM mytable WHERE category_id=1 AND user_id=2;

第一反应可能是，再给user_id建立一个索引。不好，这不是一个最佳的方法。可以建立多重的索引。

CREATE INDEX mytable_categoryid_userid ON mytable(category_id,user_id);

索引名在命名时的习惯使用"表名_字段1名_字段2名"的方式。

大多数的数据库在使用orderby的时候，都将会从索引中受益。

3.索引原理

想要理解索引原理必须清楚一种数据结构「平衡树」(非二叉)，也就是b tree或者 b+ tree，有的数据库也使用哈希桶作用索引的数据结构，然而，主流的RDBMS都是把平衡树当做数据表默认的索引数据结构的。

3.1B+树

B＋tree 是一个n叉树，每个节点有多个叶子节点，一颗B+树包含根节点，内部节点，叶子节点。根节点可能是一个叶子节点，也可能是一个包含两个或两个以上叶子节点的节点。

B＋tree的性质：

1.n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。

2.所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

3.所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。

3.2为什么用B+树做索引

数据库索引的存储结构一般是B+树，为什么不适用红黑树等普通的二叉树？

1. 数据库文件是放在硬盘上，每次读取数据库都需要在磁盘上搜索，因此需要考虑磁盘寻道时间，我们都知道磁盘寻道开销是非常大的。同时，索引一般也是非常大的，内存不能放下，因此也会放在磁盘上。（另外，还与局部性原理与磁盘预读有关系）。

2. B+树所有的关键字都出现在叶子节点的链表（稠密索引）中，且链表中的关键字是有序的。非叶子节点只起索引作用（稀疏索引）。叶子节点相当于存储了关键字的数据层。

因此，我们得出结论，由于数据存数在磁盘上，因此应该尽量减少磁盘I/O次数。恰好，B+树的叶子节点存储了关键字的数据层（我们可以称为主键），因此我们可以直接通过主键来查询数据。若是使用二叉树，二叉树索引的只是主键的位置，我还需要根据二叉树索引主键的位置，进行一次I/O操作来获取主键。

在选择数据库索引的结构的时候，要考虑到另一个问题。索引是存在于磁盘中，当索引非常大的时候，达到几个G的时候，无法一次加载到内存中。

考虑到上面两个因素，数据库中索引使用的是树形结构。

平衡二叉树

树形结构是计算机系统里最重要的数据结构。

我们知道，二叉树的查找的时间复杂度是O(log2N)，其查找效率与深度有关，而普通的二叉树可能由于内部节点排列问题退化成链表，这样查找效率就会很低。因此平衡二叉树是更好的选择，因为它保持平衡，即通过旋转调整结构保持最小的深度。其查找的时间复杂度也是O(log2N)。

但实际上，数据库中索引的结构也并非AVL树或更优秀的红黑树，尽管它的查询的时间复杂度很低。

为什么平衡二叉树也不适合作为索引

之前说了平衡树的查找时间复杂度是O(log2N)，已经很不错了，但还是不适合作为索引结构。那么肯定是有一种更适合作为索引的数据结构。那么这个更适合作为索引的数据结构，难道是查找的时间复杂度更低吗？并不是。这种作为索引的数据结构的查找的时间复杂度也近似O(log2N)。

那为什么平衡二叉树不适合作为索引呢？

索引是存在于索引文件中，是存在于磁盘中的。因为索引通常是很大的，因此无法一次将全部索引加载到内存当中，因此每次只能从磁盘中读取一个磁盘页的数据到内存中。而这个磁盘的读取的速度较内存中的读取速度而言是差了好几个级别。

注意，我们说的平衡二叉树结构，指的是逻辑结构上的平衡二叉树，其物理实现是数组。然后由于在逻辑结构上相近的节点在物理结构上可能会差很远。因此，每次读取的磁盘页的数据中有许多是用不上的。因此，查找过程中要进行许多次的磁盘读取操作。

而适合作为索引的结构应该是尽可能少的执行磁盘IO操作，因为执行磁盘IO操作非常的耗时。因此，平衡二叉树并不适合作为索引结构。

B-Tree适合作为索引

平衡二叉树不适合作为索引。那么什么才适合作为索引——B树。

平衡二叉树没能充分利用磁盘预读功能，而B树是为了充分利用磁盘预读功能来而创建的一种数据结构，也就是说B树就是为了作为索引才被发明出来的的。

为什么说红黑树没能充分利用磁盘预读功能？

红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

也就是说，使用红黑树（平衡二叉树）结构的话，每次磁盘预读中的很多数据是用不上的数据。因此，它没能利用好磁盘预读的提供的数据。然后又由于深度大（较B树而言），所以进行的磁盘IO操作更多。

B树的每个节点可以存储多个关键字，它将节点大小设置为磁盘页的大小，充分利用了磁盘预读的功能。每次读取磁盘页时就会读取一整个节点。也正因每个节点存储着非常多个关键字，树的深度就会非常的小。进而要执行的磁盘读取操作次数就会非常少，更多的是在内存中对读取进来的数据进行查找。

B树的查询，主要发生在内存中，而平衡二叉树的查询，则是发生在磁盘读取中。因此，虽然B树查询查询的次数不比平衡二叉树的次数少，但是相比起磁盘IO速度，内存中比较的耗时就可以忽略不计了。因此，B树更适合作为索引。

比B树更适合作为索引的结构——B+树

比B树更适合作为索引的结构是B+树。MySQL中也是使用B+树作为索引。它是B树的变种，因此是基于B树来改进的。为什么B+树会比B树更加优秀呢？

B树：有序数组+平衡多叉树；

B+树：有序数组链表+平衡多叉树；

B+树的关键字全部存放在叶子节点中，非叶子节点用来做索引，而叶子节点中有一个指针指向一下个叶子节点。做这个优化的目的是为了提高区间访问的性能。而正是这个特性决定了B+树更适合用来存储外部数据。

B+树还有一个最大的好处，方便扫库，B树必须用中序遍历的方法按序扫库，而B+树直接从叶子结点挨个扫一遍就完了，B+树支持range-query非常方便，而B树不支持。这是数据库选用B+树的最主要原因。

比如要查 5-10之间的，B+树一把到5这个标记，再一把到10，然后串起来就行了，B树就非常麻烦。B树的好处，就是成功查询特别有利，因为树的高度总体要比B+树矮。不成功的情况下，B树也比B+树稍稍占一点点便宜。

4.索引类型

Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。

1. FULLTEXT

即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。

为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

2. HASH

由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。

HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

3. BTREE

BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。

4. RTREE

RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

相对于BTREE，RTREE的优势在于范围查找。

索引种类

普通索引：仅加速查询

唯一索引：加速查询 + 列值唯一（可以有null）

主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个

组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

全文索引：对文本的内容进行分词，进行搜索

5.Mysql联合索引最左匹配原则

5.1联合索引

案例：a,b,c三个字段添加上联合索引！(a,b,c)

1.查询条件为a ，即 select * from tablename where a=""

联合索引有效

2.查询条件为b, 即 select * from tablename where b=""

联合索引无效。只有c时，也无效

3.查询条件为 a and b ，select * from tablename where a="" and b=""

联合索引有效，b and a 联合索引也有效。a and c 也同样有效。

4.查询条件用 or ，条件为a or b时，select * from tablename where a="" or b=""

联合查询无效

5.查询条件为b and c时，select * from tablename where b="" and c=""

没有第一个索引字段，联合查询无效

6.所有条件一起查询，即条件为：a and b and c 。联合索引有有效

总结：缺第一个不可，顺序无关，and有效，or无效。

最左前缀原则：

顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上，

注：如果第一个字段是范围查询需要单独建一个索引

注：在创建联合索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。这样的话扩展性较好，比如 a经常需要作为查询条件，而 b不常常用，则需要把 a 放在联合索引的第一位置，即最左边

5.2单列索引

案例：在a,b,c三个字段上分别创建单列索引。

1. 条件为 a and b and c 时，可能此时只有第一个索引会生效。

这里涉及mysql优化器的优化策略！当多条件联合查询时，优化器会评估那个条件的索引效率最高，会选择最佳的索引去使用。

2. 查询条件为 a or b 时。两个查询条件都能用上索引。

5.3联合索引本质：

当创建**(a,b,c)联合索引时，相当于创建了(a)单列索引**，(a,b)联合索引以及**(a,b,c)联合索引**

想要索引生效的话,只能使用 a和a,b和a,b,c三种组合；当然，我们上面测试过，a,c组合也可以，但实际上只用到了a的索引，c并没有用到！

6.其他知识点：

1、需要加索引的字段，要在where条件中

2、数据量少的字段不需要加索引；因为建索引有一定开销，如果数据量小则没必要建索引（速度反而慢）

3、避免在where子句中使用or来连接条件,因为如果俩个字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描

4、联合索引比对每个列分别建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。另外建立多列索引时，顺序也是需要注意的，应该将严格的索引放在前面，这样筛选的力度会更大，效率更高。

最后的说明：

网上关于索引优化等文章太多了，针对各个数据库各个版本各种引擎都可能存在不一样的说法！

我们的SQL引擎自带的优化也越来越强大，说不定你的某个SQL优化认知，其SQL引擎在某次升级中早就自优化了。

所以要么跟进官方文档，要么关注数据库大牛的最新文章，要么在现有数据库环境下自己去亲手测试！

参考：

https://blog.csdn.net/Abysscarry/article/details/80792876

https://baike.baidu.com/item/数据库索引/8751686

https://www.cnblogs.com/aspwebchh/p/6652855.html

dyjsteve

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库之索引

目录1.简介2.案例3.索引原理3.1B+树3.2为什么用B+树做索引为什么平衡二叉树也不适合作为索引那为什么平衡二叉树不适合作为索引呢？4.索引类型索引种类5.Mysql联合索引最左匹配原则5.1联合索引5.2单列索引5.3联合索引本质：6.其他知识点：1.简介索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速...
复制链接

扫一扫