西西软件园多重安全检测下载网站、值得信赖的软件下载站!
软件
软件
文章
搜索

首页西西教程数据库教程 → SQL SERVER 索引中聚集索引分析和Transact-SQL语句优化

SQL SERVER 索引中聚集索引分析和Transact-SQL语句优化

相关软件相关文章发表评论 来源:本站整理时间:2010/9/17 23:43:18字体大小:A-A+

作者:佚名点击:599次评论:1次标签: SQL 索引

  • 类型:音频处理大小:1M语言:中文 评分:5.1
  • 标签:
立即下载

写这篇文章帮助自己更好的理解索引,也算是自己的学习笔记吧,分享给大家 ,文中如果有分析的不正确的地方,欢迎指出 。
一. 聚集索引B树分析
1.聚集索引按B树结构进行组织的,索引B树种的每一页称为一个索引节点。B树的顶端节点称为根节点。
  索引中的低层节点称为叶节点。根节点与叶节点之间的任何索引级别统称为中间级。在聚集索引中,叶节点包含基础表的数据页。
  根节点和中间级节点包含存有索引行的索引页。每个索引行包含一个键值和一个指针,该指针指向 B 树上的某一中间级页或叶级索引中的某个数据行.每级索引中的页均被连接在双向链接列表中。
 
2.索引使用的每一个分区的index_id = 1 ,默认情况下聚集索引单个分区,当使用分区表的时候,每个分区都有一个包含该特定分区相关数据的B树结构,我是这么理解的不知道对不对?
 
3.SQL Server 写入的数据,数据链内的页和行将按聚集索引键值进行排序。
 
4.SQL Server 将在索引中查找该范围的起始键值,然后用向前或向后在数据页中进行扫描。为了查找数据页链的首页,SQL Server 将从索引的根节点沿最左边的指针进行扫描。
 
 聚集索引B树图 :
 
 
二 .优化 Transact-SQL 语句经常使用的语句
  
 1.SET STATISTICS IO {ON| OFF} /*Transact-SQL 语句生成的磁盘活动量的信息*/
  
 2.SET SHOWPLAN_ALL ON {ON| OFF} /*返回有关语句执行情况的详细信息,并估计语句对资源的需求*/
 
 3.SET STATISTICS TIME {ON| OFF} /*显示分析、编译和执行各语句所需的毫秒数*/
 
  4.使用T-SQL语句创建索引的语法:
    CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] 
    INDEX   index_name
    ON table_name (column_name)
    [WITH FILLFACTOR=x]
    UNIQUE表示唯一索引,可选CLUSTERED、NONCLUSTERED表示聚集索引还是非聚集索引, 可选 FILLFACTOR表示填充因子,指定一个0到100之间的值,该值指示索引页填满的空间所占的百分比
 
 
 SET STATISTICS IO 输出信息如图
 
三 创建数据测试下上面学到的理论知识
 
--创建表
CREATE TABLE employee
(
 emp_username varchar (20),
 emp_register DATETIME
)
  
--插入测试数据
DECLARE @startid INT
DECLARE @endid INT
SELECT @startid= 1,@endid = 100
WHILE @startid <=@endid
BEGIN
 INSERT INTO employee (
    emp_username,
    emp_register
 ) VALUES (
    /* emp_username - varchar (20) */ '刘'+CAST(@startid AS NVARCHAR(20)),
    /* emp_register - DATETIME */ GETDATE() )
 SELECT @startid =@startid +1;
END
 
-- 查询employee的执行计划 和 io  信息
SET STATISTICS IO ON 
SELECT * FROM employee WHERE emp_username = '刘'

 
查看消息输出的 IO 信息
表'employee'。(1)1扫描计数1,(2)逻辑读取1 次,(3)物理读取0 次,(4)预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。
输出的信息和上面的图片讲解的是对应的
1.      执行的扫描次数 。
2.      从磁盘读取的页数。
3.      为进行查询而放入缓存的页数。
4. 预读
T_SQL transaction 语句有很多种的写法,但是决定那条语句是最优的是根据(logical reads) 逻辑读取来判断。
 
 
添加聚集索引 查询逻辑读取是否会变少
CREATE CLUSTERED INDEX Idx_emp_username ON employee (emp_username);
 
--然后再执行查询 
SET STATISTICS IO ON
SELECT * FROM employee WHERE emp_username = '刘'

 
查看消息输出的 IO 信息
表'employee'。扫描计数1,逻辑读取2 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。 
Q 这次逻辑读取是2次为什么呢 ?
A.难道查询比表扫描还要慢,答案是对的,数据量小的时候,聚集索引的优势体现不出来。
Q 为什么是2次逻辑读取
A 现在查询的时候如聚集索引图,先查询索引页 ,查找到对应的键值后,扫描数据页,如果有包含索引,直接在索引页就可以提取到需要的数据。
 
 
上面说了小数据量的时候聚集索引体现不出效果,下面我们继续填充数据测试 。
 
 
填充测试数据到1000
 
表扫描
消息:
表'employee'。扫描计数1,逻辑读取36 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。
 
聚集索引扫描
消息:
表'employee'。扫描计数1,逻辑读取2 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。
 
这个时候聚集索引的优势就先显示出来了 O(∩_∩)O
 
 
 
下面在来讲讲transaction sql 语句 ,大家在网上看到的一些人说  In like left  不使用索引 ,我们动手来测试下看他们说的对不对 ?
 
 
删除employee表的索引
DROP INDEX employee.Idx_emp_username
表 'employee'。扫描计数 1,逻辑读取 371 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
打开IO信息
SET STATISTICS IO ON
SELECT * FROM employee WHERE employee.emp_username in ('刘10000')
 

 
消息:
 
 --添加Idx_emp_username聚集索引
CREATE CLUSTERED INDEX Idx_emp_username ON employee (emp_username);
SELECT * FROM employee WHERE employee.emp_username in ('刘10000');

 
消息:
表 'employee'。扫描计数 1,逻辑读取 3 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 
 使用索引后逻辑读取3次,没有使用索引是371次,IN 很好的使用了索引!
 
 下面我们来测试下 LIKE 是否很好的使用索引
 
 删除索引
 DROP INDEX employee.Idx_emp_username
 打开IO 信息
 SET STATISTICS IO ON
 执行查询
 SELECT * FROM employee WHERE  employee.emp_username like   ('刘1000%')
 
 
 消息:
  表 'employee'。扫描计数 1,逻辑读取 371 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 
 添加索引
 CREATE CLUSTERED INDEX Idx_emp_username ON employee (emp_username);
 SET STATISTICS IO ON
 SELECT * FROM employee WHERE employee.emp_username  like  ( '刘1000%');
 
 
 
 
表 'employee'。扫描计数 1,逻辑读取 3 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 
网上很多优化的文章写到查询不要使用 in like  left ,其实自己动手测试下看看查询计划就一幕了然了 。

    相关评论

    阅读本文后您有什么感想? 已有人给出评价!

    • 8 喜欢喜欢
    • 3 顶
    • 1 难过难过
    • 5 囧
    • 3 围观围观
    • 2 无聊无聊

    热门评论

    最新评论

    发表评论 查看所有评论(1)

    昵称:
    表情: 高兴 可 汗 我不要 害羞 好 下下下 送花 屎 亲亲
    字数: 0/500 (您的评论需要经过审核才能显示)