Nicander知识小站

Appearance

​

1、索引基本概念 ​

索引的作用就类似汉语字典的目录，可以按偏旁部首快速定位需要查找的汉字所在的页数。

1.1 索引定义 ​

索引是定义在存储表基础之上，有助于无需检查所有记录而快速定位所需记录的一种辅助存储结构，

一个索引项由两部分组成：

  • 索引字段：表中的某些列，如id等；类似于词典中的词条
  • 索引指针：指向表中包含索引字段值的记录在磁盘上的存储位置。类似于词典中词条对应的页码

1.2 索引分类 ​

不同数据库系统提供了不同的索引类型。

按物理存储结构划分：

  • 聚集索引：数据库表行中数据的物理顺序与键值（索引）的逻辑顺序相同。 因为一个表的物理存储顺序只有一种情况，所以一个表只能有一个聚集索引。
  • 非聚集索引：与聚集索引相对，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

按字段特性划分：

  • 唯一索引
  • 普通索引

按字段个数划分：

  • 单列索引
  • 组合索引

2 、索引文件本身的存储结构 ​

每个索引对应一个索引文件，MSSQL的索引文件存储结构是B+树。

为什么采用这种结构，需要从B+树这种数据结构的演化过程说起，二叉搜索树——平衡二叉树——B树——B+树

2.1 平衡二叉树 ​

平衡二叉树是基于二分法的策略提高数据的查找速度的一种数据结构。

上图为一个简单的二叉树模拟图，每个节点有这样的特点：

  1. 每个节点最多有两颗子树(树分支)
  2. 左子树和右子树是有顺序的，同层级相邻节点，右边的值比左边大。
  3. 即使某节点只有一颗子树，也要区分左右子树。

平衡二叉树基于二分法的策略，平衡二叉树查询性能和树的层级（h高度）成反比。当树结构层级越高，查找次数越多，磁盘IO次数就越多，查询效率也就越慢。

假设有100个数值需要储存时，会产生6层树高度，100个树节点的平衡二叉树。如果有办法让一个节点存储多个元素(数值)，就可以减少树节点，从而减少树整体层级高度，于是就出现了B树。

2.2 B 树 ​

B树和平衡二叉树不同的是B树属于多叉树又名平衡多路查找树（查找路径不只两个），单个节点可以存储多个数据。

B树规则：

  1. 排序方式：所有节点关键字是按递增次序排列，并遵循左小右大原则；
  2. 子节点数：非叶节点的子节点数>1，且<=M ，且M>=2，空树除外（注：M阶代表一个树节点最多有多少个查找路径，M=M路,当M=2则是2叉树,M=3则是3叉）；
  3. 关键字数：枝节点的关键字数量大于等于ceil(m/2)-1个且小于等于M-1个（注：ceil()是个朝正无穷方向取整的函数 如ceil(1.1)结果为2);
  4. 所有叶子节点均在同一层、叶子节点除了包含了关键字和关键字记录的指针外也有指向其子节点的指针只不过其指针地址都为null对应下图最后一层节点的空格子;

特点：

B树相对于平衡二叉树的不同是，每个节点包含的关键字增多了，特别是在B树应用到数据库中的时候，数据库充分利用了磁盘块的原理（磁盘数据存储是采用块的形式存储的，每个块的大小为4K，每次IO进行数据读取时，同一个磁盘块的数据可以一次性读取出来）把节点大小限制和充分使用在磁盘快大小范围；把树的节点关键字增多后树的层级比原来的二叉树少了，减少数据查找的次数和复杂度;

2.3 B+树 ​

B+树是B树的一个进化，与B树的主要区别在于B+树非叶子节点上是不存储数据的，仅存储键值，数据存储在同一层的叶节点。这样每个非叶节点可以存储更多的数据，从而降低树的层级高度，并且所有的叶子节像是一个链表一样，指向右边的叶子节点，从而可以有效加快检索效率，如果需要遍历所有的数据，只需要遍历叶子节点链式结构即可，方便且高效。

2.4 聚集索引的存储结构 ​

按建立聚集索引的字段建立B+树索引，表中的数据按建立聚集索引的字段在磁盘上进行排序，每张表只能有一个聚集索引(只能有一种排序方式)。

特点：

  • 聚集索引的根节点和中间节点是索引页，都只包含下一层的入口指针和入口值（位于存储位置的第一个主键值）；
  • 聚集索引的叶节点就是数据页。存放了表里所有字段的数据

聚集索引是一种对磁盘上实际数据重新组织以按指定的一列或多列值排序。由于在聚集索引下，数据在物理上是按序排列在数据页上的，重复值也排在一起，因而包含范围检查（bentween,<,><=,>=）或使用group by 或order by的查询时，一旦找到第一个键值的行，后面都将是连在一起，不必在进一步的搜索，避免大范围的扫描，可以大大提高查询速度。

2.5 非聚集索引的存储结构 ​

非聚集索引不重新组织表中的数据，一个表中可以有多个非聚集索引。

具体结构如下：

  • 非聚集索引的根节点和中间节点是索引页，都只含下一层级的入口指针和入口值（位于存储位置的第一个键值）；
  • 非聚集索引的叶节点也是索引页，也存储有聚集索引和非聚集索引的键值；
  • 非聚集索引中的每个索引行（不论是根节点、中间节点还是叶节点）都包含非聚集键值和行定位符，此定位符指向聚集索引或堆（没有聚集索引的表）中包含该键值的数据行。

非聚集索引概念关系图如下：

通过非聚集索引查询数据需首先找到聚集索引，再通过聚集索引获取实际的数据。相比聚集索引多了一倍io。

3、 如何建立索引 ​

3.1 sqlserver 中建立索引 ​

  1. 通过sql语言建立索引：
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] -- UNIQUE表示唯一索引，CLUSTERED、NONCLUSTERED表示聚集索引还是非聚集索引

    INDEX   index_name  -- 指定索引名称

     ON table_name (column_name…) -- 表名、列名
  1. 通过图形界面创建：

3.2 建立索引的一般原则 ​

索引技术应用可以使得检索效率大大提高，但同时其增加了存储空间，使维护负担加重（不仅要维护主文件，而且要维护索引文件），而且聚集索引每张表只能有一个，因此在什么字段上建立索引和建立什么样的索引尤为重要。

原则：

  • 为经常需要排序、分组和联合操作的字段建立索引；
  • 为常作为查询条件的字段建立索引；
  • 尽量使用小数据类型的列作为键的索引，以减少索引页大小。（int类型的键作为索引是速度最快的）
  • 尽量选择区分度高的列作为索引；如果字段数据都差不太多，也就没有建立索引的必要
  • 对于查询很少、增删改等操作较多的表不需建立索引；
  • 限制索引的数目，索引并不是越多越好
  • 尽量避免uuid作为索引；因为数据生成没有规律，无法做到按顺序存储，且占用空间大。

误区：

** 把主键自动设为聚集索引**。通常情况下，我们会在每张表中建立一个id作为主键，sqlserver会默认为主键建立聚集索引，但有些时候这个字段只是无意义的自动增量字段，失去了作为聚集索引的意义，浪费了聚集索引这一宝贵的资源。

4、 sql 书写的一些注意事项 ​

4.1 前置概念-查询优化器 ​

数据库系统内部有查询优化器，当我们执行一条sql时候，查询优化器去优化这条 SQL 语句， 依据IO/CPU最小原则来选择使用对应的索引。

4.2 sql 注意事项 ​

  • 索引最左匹配原则

索引可以以一定顺序引用多列，这种索引叫作联合索引。最左匹配原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下：

sql
-- user （name,city）组成联合索引
select * from user where name=xx and city=xx ; --可以命中索引
select * from user where name=xx ; --可以命中索引
select * from user where city=xx ; --无法命中索引
  • 尽量避免在 where 子句中对字段进行 null 值判断，null判断会导致数据库将放弃使用索引而进行全表扫描
  • 应尽量避免在 where 子句中使用!=、<>、in等操作符，否则数据库将放弃使用索引而进行全表扫描。
  • 避免在索引列上使用计算，否则数据库将放弃使用索引而进行全表扫描。
sql
select id from t where num/2=100 
-- 应改为:
select id from t where num=100*2
  • 小心使用or操作，and操作中任何一个子句可使用索引都会提高查询性能，但是or条件中任何一个不能使用索引，都将导致查询性能下降，

5、 sql性能优化工具-执行计划 ​

执行计划就是数据库如何执行一条Sql语句,包括Sql查询的顺序、是否使用索引、以及使用的索引信息等内容。

在SQLSERVER 中，打开执行计划图形化界面的地方：

上面是一个sql语句执行计划（执行计划是从右往左看的），从执行计划可以得到信息：

  • 哪些执行花费成本更高——开销
  • 哪些产生的数据量更大，对于每个步骤所产生的数据量，SQL Server的执行计划是用**【线条粗细】**来表示的
  • 每一步执行了什么样的动作

通过分析执行计划，可以知道哪些步骤的成本比较高，进而，可以尝试一些改进的方法。

参考 ​

平衡二叉树、B树、B+树、B*树 理解其中一种你就都明白了

SQLServer索引调优实践

详解一条 SQL 的执行过程