数据库索引

一、基础

1、概念

类似目录

实质: 平衡二叉树

从广义上讲，SQL Server检索所需数据的方法只有两种：

• 　　使用全表扫描
• 　　使用索引

B-Tree 索引  (MySQL，SQL Server，Oracle)

• 索引每块固定大小 4K   选字段 一定要考虑字段大小（int 4b 、long 8b） 
• 大多数B-tree 就3-4层的深度   比如 一层（分支因子） 为500个4K ，4层就 500*4k^3 =256TB

哈希Hash索引 (MySQL，Oracle)

• 1、Hash是k,v形式，通过一个散列函数，能够根据key快速找到value  
• 2、哈希索引就是采用一定的hash算法，把键值换成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需要一次hash算法即可立即定位到相应的位置，速度非常快。
• 缺点: 因为底层数据结构是散列的，无法进行比较大小，不能进行范围查找

2、索引类型

唯一索引：

• 唯一索引不允许两行具有相同的索引值
• 当新数据将使表中的键值重复时，数据库也拒绝接受此数据
• 不影响数据的物理存储顺序
  

主键索引：

• 为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空

聚集索引：

• 表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个。
• 页码1，2，3……就是物理顺序。

非聚集索引

• 非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个
• 按笔画排序的索引就是非聚集索引
  

聚集索引与非聚集索引区别

聚集索引的数据行按照索引键的顺序进行物理存储。每个表只能有一个聚集索引，通常是主键。聚集索引对于按照索引键进行范围查询和排序操作的性能较好。

非聚集索引使用单独的数据结构存储索引键和指向数据行的指针。数据行的物理存储顺序与索引无关。一个表可以有多个非聚集索引。非聚集索引可以快速定位到所需的数据行，但在涉及多个列的查询或包含非索引列的查询时可能需要额外的查找操作。

当一个表具有多个非聚集索引时，每个非聚集索引都是一个独立的数据结构，用于存储索引键和指向数据行的指针。

复合索引

复合索引的创建可以基于多个列的组合顺序。查询时，如果使用了复合索引中的前缀列，那么索引可以发挥作用。但是，如果查询条件中没有使用到复合索引的前缀列，那么索引的效果将会降低。

SQL  查索引

-- 查询表的所有索引
SELECT 
    i.name AS IndexName, -- 索引名称
    i.type_desc AS IndexType, -- 索引类型描述
    c.name AS ColumnName, -- 列名
    ic.index_column_id AS ColumnOrder -- 列的顺序
FROM 
    sys.indexes i
INNER JOIN 
    sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN 
    sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE 
    i.object_id = OBJECT_ID('SafeInspectionPlanRecordPointUnitItem') 
ORDER BY 
    ic.index_column_id;

SQL Server中，一个表只能创建1个聚集索引，多个非聚集索引。设置某列为主键，该列就默认为聚集索引

--Scan count 扫描计数
--logical reads 逻辑读取 
--physical reads 物理读取
--read-ahead reads 预读
--lob logical reads lob 逻辑读取
--lob physical reads lob 物理读取
--lob read-ahead reads lob 预读 
set statistics io on
SELECT * FROM dbo.Users 
set statistics io off