数据库之索引的底层数据逻辑及应用

最新推荐文章于 2024-07-20 20:04:43 发布

sevenlob

最新推荐文章于 2024-07-20 20:04:43 发布

阅读量835

点赞数 20

文章标签：数据库

本文链接：https://blog.csdn.net/sevenlob/article/details/131108098

版权

索引（index）是帮助数据库高效获取数据的数据结构。

索引的数据结构

堆存储
一个数据表
使用二叉树存储
使用二叉树存储的
极端情况下的单链形式

大数据量下，层级越深，查询效率越低。

平衡二叉树

多路平衡查找树

B+树的结构
B+树
所有的数据都存储在叶结点中
叶结点间以单向链表链接

数据库中的B+树结构

叶结点间以双向链表链接

为什么是B+树？

一个结点存储在一个页上，一页的大小是8K(SQL Server)，页不可拆分。B+树中不存放数据，则在一页中可以存放的Key个数更多，使得树的层级减少，查找效率增加；
搜索效率稳定；
叶子节点间的双向链表便于排序。

优缺点

索引：帮助高效获取数据的数据结构。
优：

高效获取数据，减少IO成本。
通过索引对数据进行排序，降低CPU消耗

缺：

占用空间
降低了更新表的效率（insert、update、delete）

哈希索引

哈希索引的数据结构
通过内存中的哈希表来访问数据。
哈希索引的内存用量固定不变，是存储桶数量的函数。
只能进行等值匹配（=，in），不能进行范围查询（between，<，>）；
无法利用索引进行排序（哈希值是无序的）；
查询效率高，通常只需要一次检索（没有哈希冲突的情况）（效率>B树）

索引分类

物理结构

类型	物理存储	特点
聚集索引	叶节点包含基础表的数据页	可以没有但必须唯一
非聚集索引	包含索引键值和指向表数据存储位置的行定位器	可以有多个

聚集索引

聚集索引的物理存储

聚集索引特殊的方面是：聚集索引的叶级是实际的数据。

表上如果没有建立聚集索引，则是按照堆（HEAP）存放的。
在建立聚集索引时，会开创新的物理空间，将数据重新排序，按照和聚集索引排序条件声明的相同物理顺序存储。但原有的堆表并不会被释放，旧的结构只有在提交索引创建事务后才会释放。

这意味着，一旦到达索引的叶级，就到达了数据。

聚集索引的选取规则：

如果有主键，默认主键索引就是聚集索引；

如果没有主键，数据库引擎会自动向行添加一个唯一标识符RID，使每个键唯一。RID不可被用户查看及访问；

如果对具有多个现有非聚集索引的堆创建聚集索引，则必须重新生成所有非聚集索引，以使它们包含聚集键值而非行标识符 (RID)；

在聚集索引中，叶节点包含基础表的数据页；

数据链内的页和行将按聚集索引键值进行排序。所有插入操作都在所插入行中的键值与现有行中的排序顺序相匹配时执行。

非聚集索引

非聚集索引是为提高聚集索引未涵盖的常用查询的性能。

非聚集索引并不改变其所在表的物理结构，而是额外生成一个聚集索引的B树结构，但叶子节点是对于其所在表的引用，这个引用分为两种，如果其所在表上没有聚集索引，则引用行号。如果其所在表上已经有了聚集索引，则引用聚集索引的页。

非聚集索引，到达了叶级只是找到了数据的引用。

非聚集索引的回表查询

索引关键字

类型	关键字	含义	特点
主键索引	PRIMARY	针对主键建立的索引	默认自动创建、唯一
唯一索引	UNIQUE	避免字段值的重复	不唯一
全文索引	FULLTEXT	查找文本中的关键字	不唯一
常规索引	实现快速查找		不唯一

主键索引：
关键字：primary
针对主键创建的索引，默认自动创建，只能有一个

唯一索引：
关键字：unique
避免表中某列的值重复，确保索引键不包含重复的值，因此，表或视图中的每一行在某种程度上是唯一的。唯一性可以是聚集索引和非聚集索引的属性。
但唯一索引可以有多个

常规索引：快速定位特定数据

全文索引：
关键字：fulltext
查找的是文本中的关键字，而非比较索引中的值

索引语法

CREATE UNIQUE INDEX index_name ON table_name (column_name) ;---非空索引
CREATE PRIMARY KEY INDEX index_name ON table_name (column_name) ;---主键索引

使用ALTER TABLE语句创建索引

alter table table_name add index index_name (column_list) ;
alter table table_name add unique (column_list) ;
alter table table_name add primary key (column_list) ;

查看索引

exec sp_helpindex table_name;

删除索引

drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;

验证索引:
在未使用索引之前，执行SQL语句，查看SQL耗时

SELECT * FROM table_name WHERE id = 1;

针对字段建立索引

CREATE INDEX index_name on table_name;

再执行，查看耗时

建立哈希索引

ALTER TABLE MyTable_memop
ADD INDEX ix_hash_Column2 UNIQUE
HASH (Column2) WITH (BUCKET_COUNT = 64);

哈希索引桶计数在索引创建时指定，可使用ALTER TABLE...ALTER INDEX REBUILD语法进行更改。

在大多数情况下，桶计数在理想情况下应该介于索引键中非重复值数目的 1 到 2 倍之间。

单列索引与联合索引

关联一个字段——单列索引；
关联多个字段——联合索引（组合索引）（联合索引中字段的顺序——最左前缀法则）
涉及多个查询条件时，建议使用联合索引

索引失效

最左前缀法则：

查询时从索引的最左列开始，且不跳过索引中的列。如果跳过了，跳过之后的列会失效

范围查询：

在联合索引中，出现范围查询（<,>,between），范围查询之后的列失效

覆盖索引：

查询返回的列在索引中都包含

其他索引失效

在索引列里进行运算操作，索引会失效
类型转换导致索引失效——不给字符串类型加单引号，索引失效
模糊匹配：在头部进行模糊匹配，索引会失效。在尾部进行不会
Or连接：只有所有条件都有索引，索引才会执行
is null可以使用索引，is not null无法使用索引

索引分布影响

如果数据库评估全表查询比使用索引更快，则不使用索引

索引提示

如果数据库评估全表查询比使用索引更快，则不使用索引

前缀索引

scan/seek

全表扫描与索引查找的磁盘 I/O

Table Scan 的 Disk I/O
Nonclustered Index Scan 的 Disk I/O
Clustered Seek 的 Disk I/O
Nonclustered Index Seek 的 Disk I/O

index seek是查找从B树的根节点开始，一级一级找到目标行。
index scan则是从左到右，把整个B树遍历一遍

SQL有一个查询优化分析器 Query Optimizer，其在执行查询之前首先会进行分析，当查询中有可以利用的索引时，其会优先分析使用Index Seek进行查询的效率，在使用Index Seek查询效率并不好的情况下，其会使用Index Scan进行查询。
1.在要查询的表中数据不是很多的话，使用Index Seek效率不一定高，因此使用Index seek还要先从索引树开始，然后再利用叶子节点去查找相应的行。在行树比较少的情况下，还没有直接进行Index scan快。
2.在返回的数据量大的情况下，在返回的数据量占总数据量的50%或者超过50%则使用Index Seek效率不一定好，在返回的数据量占10%-15%时，利用Index Seek能获得最佳的性能。
3.在建立索引的列的取值很多是一致的情况下，建立索引不一定能获得很好的效率。

利用统计优化查询

引入关键字(MySQL)

DBCC SHOW_STATISTICS
CREATE STATISTICS / DROP STATISTICS

索引设计原则

数据量大
查询频繁
常使用where（查询）、order by（排序）、group by（分组）
尽量选择区分度高的字段作为索引，尽量建立唯一索引
尽量使用联合索引，联合索引可以实现覆盖索引，减少回表查询
索引越多，维护索引的代价越大，增删改的效率会降低——只建立必要的索引
索引不能储存NULL，因此在创建表示要使用NOT NULL约束。当优化器知道每列是否包含NULL时，可以更好地确定使用哪个索引