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

第 2 页 优化 Transact-SQL 语句经常使用的语句
二 .优化 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  不使用索引 ，我们动手来测试下看他们说的对不对 ？