数据库索引及基本优化入门

数据库索引及基本优化入门

2013-7-26

---

 

一 前言

   经常在面试中发现很多人工作了好多年了，项目经验也不少，用过各种数据库，但大都不知道这些SQL语句背后的基本原理，更别说数据库优化了。平时做项目只知道实现功能，懒得学习，懒得思考，懒得看书（其实本人也是，不要找借口说这是China国情，项目是给boss做的，但技术和成长是你自己的）。

   本篇文章主要讲述数据库索引的基本原理，及基本的数据库优化的知识。所有知识均为本人自己学习的总结以及网络。此篇文章主要是为公司内部人员培训所用的，整理出来只是希望和大家分享、交流，因本人技术有限，若有遗漏、错误，希望多多指正、交流。

---

 

二．基础知识

2.1 页

   数据库文件存储是已页为存储单元的，一个页是8K（8192Byte），一个页就可以存放N行数据。我们常用的页类型就是数据页和索引页。一个页中除了存放基本数据之外还需要存放一些其他的数据，如页的信息、偏移量等，如下图所示。

   虽然SQLServer是以页为单位存储数据，但是其分配空间是以一个盘区为单位的（8个页=64K），这样做的目的主要是为提高I/O的性能。

 

2.2 B树

   B树即二叉搜索树，所有非叶子节点最低拥有两个子节点，基本信息如下图所示。都是小的元素放左边，大的元素放右边。比如说要查找某个元素，其时间复杂度就对应该元素的深度，如要查询9，从根节点开始，只要比较三次就找到他了，其查询效率是非常高的。

   子节点：最多两个子节点（指针分别指向Left和Right）

阶数（节点子节点个数）：2

深度：就是层数，各个叶子节点不一定一样，如节点21的深度为4，40的深度为3

 

2.2 B-树

B-树是一中多路搜索树，其阶数可以自定义（>2)，是很多数据及文件系统应用的一种索引结构，基本特征如：

1) 阶数（M）>2，即孩子数量大于2个

2) 每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）

3) 非叶子结点上的多个关键字是按照顺序排列的：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；

4) 所有叶子节点都位于同一层，因此叶子节点的深度都是一样的

5) 非叶子结点的关键字个数=指向儿子的指针个数-1；

6) 非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；

如下图是一个三阶的B-树，节点[18]有两个指针分别指向其2个子节点。

 

这时如果要插入一个值17，其处理步骤：

1) 从根节点进入，17小于22，进入左边的节点[18];

2) [18]不是叶子节点，继续向下搜索，17小于18，进入其左边的子节点[12,16];

3) [12,16]为叶子节点，插入到该节点；

4) 节点[12,16,17]元素大于2了（3阶树的节点关键字数量应>3/2-1,<3-1），因此该节点需要分裂，分裂中间的元素16到父节点18中去；

5) 12,17分裂成了两个子节点了；

分裂后的效果如下图

   以上图片效果来自一个外国大学里面的的在线版B-树的测试，网站：http://www.cs.usfca.edu/~galles/visualization/BTree.html ，大家可以去这个网站测试，效果很直观，外国人就是牛。本人以前用C#+GDI实现过类似的效果，结果还是可以的，就是当树太大的时候，布局不好处理了。

 

2.3 B+树

B+树是B-树的变体，也是一种多路搜索树，一棵m 阶的B+树和m 阶的B-树的差异在于： 

l 非叶子节点的子节点和其关键字相同，即节点有三个元素（关键字），他就肯定有三个子节点； 

l 非叶子节点的子节点P[i]，指向关键字值属于[K[i], K[i+1])的子树（B-树是开区间）； 

l 所有叶子节点增加一个链指针； 

l 所有关键字的数据都在叶子节点中；

 如下图所示，图片来自网络（http://www.cnblogs.com/chjw8016/archive/2011/03/08/1976891.html）。

 

---

 

三 索引存储

   B+树和B-树是数据库广发应用的索引存储结构，它可以极大的提高数据查找的效率。关于B-树、B+树的原理与应用的详细可以参考文档：http://blog.csdn.net/hguisu/article/details/7786014

前面2.1中我们讲了SQLServer中使用页为存储单元的，那么在建立索引时，其索引节点就是页了，然后树的键值就是存放到这些页（节点）中的。就是说表中的数据行就是存放到页上的，一个表有多个页构成，这些页以树的结构存放。

3.1 聚集索引

   如下图为聚集索引的存储结构（图片来自网络）。其中可以看出页有两种：Index Rows（索引页）、data rows（数据页），所有非叶子节点都存放着索引项，数据行是存放在叶子节点中的，只有叶子节点才真实存放着表中的每一行数据，而其他非叶子节点的页都存放着聚集索引的键值。因此查询数据的时间复杂度都是一样的，就是该树的深度。

   在4.2中有说明，聚集索引决定了表数据的存储顺序，具体可以参考4.2。若表没有创建聚集索引，则表数据时一个无序的堆结构。

3.2 非聚集索引

   与非聚集索引的存储结构唯一不一样的，就是非聚集索引中不存储真正的数据行，因为在聚集索引中已经存放了数据，非聚集索引只包含一个指向数据行的指针即可。如下图所示（图片来源：http://www.cnblogs.com/ashou706/archive/2010/06/08/1754164.html）：

 

---

 

四 数据库优化

   数据库优化的一个重要参数指标就是“逻辑读”（Logical Reads），可以使用命令SET STATISTICS IO ON来打开消息提示，如下图所示。

逻辑读（Logical Reads)：我们在查询数据时，数据时从缓冲区（内存）中读取的，而不是直接从磁盘读取数据的。数据库会预先把数据读取到数据缓冲区中，存放到8K字节的页中。逻辑读就是从缓冲区中读取页的页数，这个才是真正反映查询效率的指标，一般情况下，一个查询的逻辑读越小，其效率越高、速度就越快。同时，同样的SQL查询同样数据集，每次的逻辑读是一样的。

   物理读取（Physical Reads)：真正的从磁盘读取数据到期缓冲区，在SQLServer执行查询钱，会先检查其需要的数据是否在缓冲区中，若不在，就会从磁盘读取数据岛缓冲区。这一块是数据库本省的职责，我们在做查询优化的时候不用太关注的，只要给数据库服务器提供足够的内存就OK了。

   预读（Read-Ahead Reads)：数据库为优化查询，预先读取一部分数据，这个值在优化中可以不用关注。由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。

   数据优化的一个主要手段就是查看SQL的执行计划，通过查看具体SQL执行过程，可以看出索引的使用是否正确，了解查询中性能问题在哪，从而解决问题。

 

4.1 T-SQL优化基本常识

1.在Where条件中尽量不要在=号左边进行函数、运算符、或表达式计算，如Where DATEDIFF(DD,StartTime,GetDate())=6  ；或Where Num/2=100;

2.在Where中尽量避免出现！=或<>操作符；

3.在Where中尽量避免对字段进行null值判定；

4.使用Like关键字进行模糊查找时，不要使用前置百分号，如Like ‘%123%’；

5.数据库字段的长度尽量的小（保证应用的前提下）；

6.不要使用Selecte*，不要使用*号来查询数据；

7.尽量避免使用游标，游标的效率是很差的，可以使用While循环来代替；

8.尽量避免返回大量数据（查询数据(Select)优化，分页处理等）；

9.使用Exists代替in和not in；

4.2 聚集索引

   聚集索引决定了表数据的物理存储顺序，也就是说表的物理存储时根据聚集索引结构进行顺序存储的，因此一个表只能有一个聚集索引，SQLServer的聚集索引属性如下图。

 

该索引的的创建脚本：

/****** Object:  Index [Index_KeyId]    Script Date: 08/12/2013 15:25:59 ******/
CREATE UNIQUE CLUSTERED INDEX [Index_KeyId] ON [dbo].[User] 
(
    [KeyId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

 

   因此我们可以得出一个个结论：根据聚集索引的查找效率是比较高的。若表没有建立聚集索引，则表的数据存储是乱的，数据就是一个堆，没有任何顺序可言，对表的查询经常会扫描全表，造成性能较低。一般我们在实际使用中，大多会对主键建立聚集索引，一般这么做就足够了，但实际应用应该尊从一个原则就是“频繁使用的、排序的字段上”，如果要深究的话，可以参见文章结尾的参考目录其他同学的文章。 

   对应与聚集索引，所有其他的索引可以统称为非聚集索引，非聚集索引的创建会单独创建索引文件来存储索引结构，因此在创建其他索引的时候也要注意硬盘空间。比如一个表的容量是2000w行，大概有800Mb，创建的一个非聚集索引可能数据立马增加好几个G。具体如下4.3、4.4所述。 

4.3 覆盖索引

   覆盖索引就是在原本索引的基础上，把Select中需要的字段放到索引包含列中，这样就不需要再到数据表中读取数据了，这个就叫做覆盖索引了。 比如，我们查询User表中的字段UserName、Age，其中UserName上创建了非聚集索引，查询语句及索引脚本如下：

Select UserName,Age from [User] where UserName ='Ryan'
--UserName的索引
CREATE UNIQUE NONCLUSTERED INDEX [Index_UserName] ON [dbo].[User] 
(
    [UserName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

通过UserName条件查询到数据后，还需要Age字段的值，该聚集索引没有，还要到数据页中取其Age数据（就是图中的键查找），这样会造成额外的开销，查询计划如下图。

若把Age放到该索引的包含列中，该索引就会包含Age的值，查询的时候就可以直接返回UserName、Age的值了，UserName、Age的覆盖索引脚本及SQLServer的管理视图如下：

 

CREATE UNIQUE NONCLUSTERED INDEX [Index_UserName] ON [dbo].[User] 
(
    [UserName] ASC
)
INCLUDE ( [Age]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

创建了覆盖索引后的查询效率明显高了，执行计划如下图，其中就没有了循环键查找了：

关于覆盖索引更详细的文章，可以参考文章：SQL Server 查询性能优化——覆盖索引

 

4.4 复合索引

在上面的索引例子中都只是在一个键上建立索引，但实际情况中往往一个查询会有多个查询条件，如下的sql语句中，多了条件Password：

Select UserName,Age from [User] where UserName ='Ryan' and Password='123456'

该索引中是没有关于Password字段的任何信息，因此查询也会引发键查找，查询计划如下图

对于这种情况，复合索引的用途就来了，简单来所，复合索引就是在多个列上建立索引。Sql脚本及SqlServer的索引属性视图如下：

CREATE UNIQUE NONCLUSTERED INDEX [Index_UserName] ON [dbo].[User] 
(
    [UserName] ASC,
    [Password] ASC
)
INCLUDE ( [Age]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

创建复合索引后再次执行刚的查询，观看查询计划，查询有明显的改善：

在使用复合索引时，应注意多个索引键的顺序问题，这个是会影响查询效率的，一般的原则是唯一性高的放前面，还有就是SQl语句中Where条件的顺序应该和索引顺序一致。

4.5 页填充因子

   通过前面的了解我们知道数据时存放到树上的页中，当插入数据时，如果该页已经存储满了，就要进行页的拆分，频繁的拆分，会产生较多的索引碎片，影响修改和查询数据的效率。

   填充因子就是用来这种页中填充数据的一个比例，一般默认是100%填充的。如果我们修改填充因子为80%，那么页在存储数据时，就会剩余20%的剩余空间，这样在下次插入的时候就不会拆分页了。 那么是不是我们可以把填充因子设置第一点，留更多的剩余空间，不是很好嘛？当然不行，填充因子设置的低，会需要分配更多的存储空间，叶子节点的深度会增加，这样是会影响查询效率的，因此，这是要根据实际情况而定的。那么一般我们是怎么设置填充因子的呢，主要根据表的读写比例而定的。如果读的多，填充因子可以设置高一点，如100%，读写各一半，可以80~90%；更改多可以设置50~70%。SQlServer的的索引属性中有一个设置填充因子的项，如下图。

更详细的信息可以参考：http://www.cnblogs.com/cxd4321/archive/2010/08/16/1800677.html

4.6 索引碎片

   我们在前面了解到索引的结构就是B树，当树在增加、删除的时候，会触发页的拆分或合并，这种频繁的操作会产生索引碎片，造成索引不连续，当索引碎片曾多时，是会影响查询效率的。因此，访问使用的是随机的i/o，而不是有顺序的i/o，这样访问索引页会变得更慢。因此要定期的清理索引碎片，一般的方法就是重建索引。关于索引碎片的整理，可参考：http://www.cnblogs.com/mywebname/archive/2007/11/13/958463.html 

4.7 索引优化注意事项

Ø 建立索引的字段尽量的小，最好是数值；

Ø 尽量在唯一性高的字段上创建索引，不要在性别这种唯一性很低的字段上创建索引；

---

 

附录A：参考文档

1.一个b-树的在线演示例子，很直观很不错的额：

http://www.cs.usfca.edu/~galles/visualization/BTree.html

2. SQLSERVER聚集索引与非聚集索引的再次研究

http://www.cnblogs.com/lyhabc/p/3196479.html 

3. SQL Server 查询性能优化——覆盖索引：

http://www.cnblogs.com/chillsrc/archive/2012/09/04/2671092.html

 
出处：http://www.cnblogs.com/anding/p/3254674.html

Ps：这文章格式太难弄了，图片上传也很繁琐（本来是用word写好了的）

共享一张桌面壁纸，养下眼，文章编辑的眼睛疼。