浅述SQL Server的聚焦强制索引查询条件和Columnstore Index

前言

  SQL Server
2008中SQL应用系列–目录索引

本节我们再来穿插讲讲索引知识,后续再讲数据类型中的日期类型,简短的内容,深入的理解。

  SQL
Server的系统查询过程负责在SELECT查询执行时候产生查询执行计划。SQL
Server会“智能”地选择一个高效计划来取代低效的一个。大多数时候,SQL
Server会把这份工作干得很棒。但正如有些人所担忧的,SQL
Server并不是万能的,有时候,我们通过查询执行计划、表统计信息、支撑的索引及其他因素,研究性能之后,发现查询优化器选择的执行计划没有达到预期的效果,或者说,查询优化器做出了错误的选择。此时我们可能需要使用Hints(提示)来覆盖SQL
Server查询优化器决定的过程。

强制索引查询条件

  看MSDN(http://msdn.microsoft.com/en-us/library/ms187713%28v=sql.105%29.aspx),Hints(提示)是指定的强制选项或策略,由
SQL Server 查询处理器针对 SELECT、INSERT、UPDATE 或 DELETE
语句执行。提示将覆盖查询优化器可能为查询选择的任何执行计划。

前面我们也讲了一点强制索引查询的知识,本节我们再来完整的讲述下

  使用Hints之前,我有义务提醒一下各位读者:

SQL Server使用默认索引

  ■SQL
Server绝大多数情况下会做出正确的选择,即便使用的Hints短期内有效,但随着数据库内容的更改,使用的查询计划反而可能更高效,但此时因为Hints更霸道,SQL
Server并不会“自作主张”地使用优化器。

USE TSQL2012GOSELECT * FROM Sales.Orders

  ■SQL Server发布补丁后,有效的Hints也可能会改变。

上述就不用我再啰嗦了,使用默认主键创建的聚集索引来执行查询执行计划。

  本文将要的介绍的内容包括:SQL
Server三种不同类型的Hints:联接提示(Join Hints)、查询提示(Query
Hints)、表提示(Table Hints),包括SQL
Server2008中引入的提示ForceSeek,可以用它将索引查找来替换索引扫描。

SQL Server使用强制索引

  1、使用联接提示(Join Hints)

USE TSQL2012GOSELECT custid FROM Sales.Orders WITH(INDEX(idx_nc_custid))

  官方解释:联接提示用于指定查询优化器在两个表之间强制执行联接策略。(http://msdn.microsoft.com/zh-cn/library/ms173815%28v=sql.105%29.aspx

SQL Server使用内联查询条件

  用法:<join_hint> ::= { LOOP | HASH | MERGE | REMOTE }

USE TSQL2012GOSELECT custid FROM Sales.Orders WITH (INDEX(idx_nc_custid)) INNER JOIN Sales.OrderDetails WITH (INDEX(PK_OrderDetails)) ON Sales.OrderDetails.orderid = Sales.Orders.orderidGO

  联结提示会强制查询优化器来使用你命令的方式联结表,通过内部JOIN操作来实现。可用的联结提示:

SQL Server使用OPTION

463.com 1

上述我们第三个使用内联查询是比较好的,同时我们可以在表连接中末尾使用OPTION来强制使用索引,因为涉及到表连接,我们会用到另外一个函数Hints。该Hints用在Join
Hints、Query Hints、Table
Hints。如果我们指定用Hints很明显将覆盖查询计划,因为将SQL
Server默认在查询上会使用最优查询,此时通过Hints将可能会覆盖最优查询,所以一般不推荐使用,只对于明确知道这样做会比默认查询计划会更好的有丰富经验的SQL
Server使用者可能会是好的解决方案。但是对于OPTION我们可以使用,OPTION才SQL
Server
2008+上才有,上述第三个强制使用内联查询提示可以通过OPTION结合Hints来完成。

  代码演示:

USE TSQL2012GOSELECT custid FROM Sales.Orders AS SO INNER JOIN Sales.OrderDetails AS SOD ON SOD.orderid = SO.orderid OPTION (TABLE HINT(SO,INDEX(idx_nc_custid)),TABLE HINT(SOD, INDEX(PK_OrderDetails)))GO
/********* 使用Hints联结 ***************/
/********* 3w@live.cn 邀月 ************/

USE TestDb2
GO
IF NOT OBJECT_ID('HintsDemo','U') IS NULL
DROP TABLE HintsDemo
GO
IF NOT OBJECT_ID('HintsDemo2','U') IS NULL
DROP TABLE HintsDemo2
GO
----创建测试数据表
CREATE TABLE dbo.HintsDemo (HID int ,HTitle Nvarchar(50))
GO
CREATE TABLE dbo.HintsDemo2 (HID2 int ,HID int)
GO
----插入20条数据
INSERT HintsDemo(HID,HTitle)
VALUES ( cast(rand()*10 AS INT),replicate('X',cast(rand()*25 AS INT)) )
GO 20--重复该语句20次,邀月注

INSERT HintsDemo2
SELECT TOP 10 cast(rand()*10 AS INT), HID from HintsDemo
GO 2--重复该语句2次,邀月注

--此时两个表各有20条记录
SELECT * FROM HintsDemo
SELECT * FROM HintsDemo2

SET SHOWPLAN_XML ON
GO

SELECT h.HID,h.HTitle,d.HID2
FROM HintsDemo h
INNER JOIN HintsDemo2 d ON
h.HID = d.HID
GO

SET SHOWPLAN_XML OFF
GO

上述不推荐使用,除非你明确这样做比默认使用最优执行查询计划更好。

 

Columnstore Index

463.com 2

列存储索引出现在SQL Server
2012+上,我们本节简单说说这个内容,对于大数据方面合理使用列存储索引能够提高查询性能,但是通过查找大量资料发现在SQL
Server 2012中使用列存储索引有诸多限制并且还会出现许多问题,而在SQL
Server 2014+上这样的问题得到了很大的改善,所以不建议在SQL Server
2012中使用列存储索引,要使用可以在 SQL Server 2014+上使用。

下面使用Hash Join

当我们在SQL Server 2012中创建索引会提示有Columnstore Index和NONCLUSTERED
COLUMNSTORE
INDEX。在数据库中存储数据分为两种,一种是行存储,另外一种则是列存储,行存储是在页上存储所有行数据,而列存储则是在相同页上存储一列所有数据,所以利用列存储查找数据会更加快而不是查找整行上的所有数据,当然这也就意味着查找速度的增加而导致的则是硬件驱动配置需要有更高的要求,列存储中的索引会进行压缩,所以要使用列存储需要更大的内存。列存储索引存储每一列数据在每一组单独的磁盘页中,而不是在每页上存储多行,它和行存储存储数据区别在于如下图

SET SHOWPLAN_XML ON
GO

SELECT h.HID,h.HTitle,d.HID2
FROM HintsDemo h
Inner HASH JOIN HintsDemo2 d ON
h.HID = d.HID
GO

SET SHOWPLAN_XML OFF
GO

这里关于例子就不再给出,算是做一个基本了解吧。

463.com 3

总结

   2、使用查询提示(Query Hints)

463.com,本节我们主要讲了强制使用索引条件来进行查询,当对于使用默认创建索引进行查询计划时觉得不是最优解,可以尝试使用强制索引来进行对比找出更好得解决方案。简短的内容,深入的理解,我们下节再会。

  官方解释:在查询语句的持续时间内,查询提示优先于查询优化器的默认行为。您可以使用查询提示指定受影响的表的锁定方法、一个或多个索引、一个查询处理操作(如表扫描或索引查找)或其他选项。查询提示应用于整个查询。(http://msdn.microsoft.com/zh-cn/library/ms181714%28v=sql.105%29.aspx

以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,同时也希望多多支持脚本之家!

  它的参数比较复杂:

<query_hint > ::= 
{ { HASH | ORDER } GROUP   | { CONCAT | HASH | MERGE } UNION   | { LOOP | MERGE | HASH } JOIN   | EXPAND VIEWS   | FAST number_rows   | FORCE ORDER   | IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX   | KEEP PLAN   | KEEPFIXED PLAN   | MAXDOP number_of_processors   | MAXRECURSION number 
  | OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )
  | OPTIMIZE FOR UNKNOWN
  | PARAMETERIZATION { SIMPLE | FORCED }
  | RECOMPILE   | ROBUST PLAN   | USE PLAN N'xml_plan'   | TABLE HINT ( exposed_object_name [ , <table_hint> [ [, ]...n ] ] )
}

<table_hint> ::=
[ NOEXPAND ] {     INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value )   | FORCESEEK [( index_value ( index_column_name [,... ] ) ) ]   | FORCESCAN   | HOLDLOCK   | NOLOCK 
  | NOWAIT   | PAGLOCK   | READCOMMITTED   | READCOMMITTEDLOCK   | READPAST   | READUNCOMMITTED   | REPEATABLEREAD   | ROWLOCK   | SERIALIZABLE 
  | SPATIAL_WINDOW_MAX_CELLS = integer   | TABLOCK   | TABLOCKX   | UPDLOCK   | XLOCK
}

  一般情况下,
我们可以在存储过程中使用ReCompile查询提示,这样可以实现SQL语句级的重编译,而不是整个存储过程(批处理语句)的重编译。我们通过一个示例来说明。

/********* 使用查询提示 ***************/
/********* 3w@live.cn 邀月 ************/
DECLARE @HintsTitleDemo nvarchar(50) = '0E2FAB59-9A22-4E14-B7BE-33AB500E3B9E'

SELECT HID,HTitle
FROM HintsDemo
WHERE HTitle = @HintsTitleDemo
ORDER BY HID
/*
HID HTitle
6 0E2FAB59-9A22-4E14-B7BE-33AB500E3B9E
*/

--使用DMV查询查看内存中的统计计划是否可以重用
SELECT cacheobjtype, objtype, usecounts
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text LIKE 'DECLARE @HintsTitleDemo%'

 

463.com 4

相关文章