SQL中的Hints.docx
- 文档编号:7348726
- 上传时间:2023-01-23
- 格式:DOCX
- 页数:9
- 大小:215.28KB
SQL中的Hints.docx
《SQL中的Hints.docx》由会员分享,可在线阅读,更多相关《SQL中的Hints.docx(9页珍藏版)》请在冰豆网上搜索。
SQL中的Hints
浅谈SQLServer2008中的Hints(提示)-三种不同类型的联接提示(JoinHints)、查询提示(QueryHints)、表提示(TableHints)
SQLServer2008中SQL应用系列--目录索引
SQLServer的系统查询过程负责在SELECT查询执行时候产生查询执行计划。
SQLServer会“智能”地选择一个高效计划来取代低效的一个。
大多数时候,SQLServer会把这份工作干得很棒。
但正如有些人所担忧的,SQLServer并不是万能的,有时候,我们通过查询执行计划、表统计信息、支撑的索引及其他因素,研究性能之后,发现查询优化器选择的执行计划没有达到预期的效果,或者说,查询优化器做出了错误的选择。
此时我们可能需要使用Hints(提示)来覆盖SQLServer查询优化器决定的过程。
看MSDN(SQLServer查询处理器针对SELECT、INSERT、UPDATE或DELETE语句执行。
提示将覆盖查询优化器可能为查询选择的任何执行计划。
使用Hints之前,我有义务提醒一下各位读者:
■SQLServer绝大多数情况下会做出正确的选择,即便使用的Hints短期内有效,但随着数据库内容的更改,使用的查询计划反而可能更高效,但此时因为Hints更霸道,SQLServer并不会“自作主张”地使用优化器。
■SQLServer发布补丁后,有效的Hints也可能会改变。
本文将要的介绍的内容包括:
SQLServer三种不同类型的Hints:
联接提示(JoinHints)、查询提示(QueryHints)、表提示(TableHints),包括SQLServer2008中引入的提示ForceSeek,可以用它将索引查找来替换索引扫描。
1、使用联接提示(JoinHints)
官方解释:
联接提示用于指定查询优化器在两个表之间强制执行联接策略。
(
用法:
:
={LOOP|HASH|MERGE|REMOTE}
联结提示会强制查询优化器来使用你命令的方式联结表,通过内部JOIN操作来实现。
可用的联结提示:
代码演示:
/*********使用Hints联结***************/
/*********3w@邀月************/
USETestDb2
GO
IFNOTOBJECT_ID('HintsDemo','U')ISNULL
DROPTABLEHintsDemo
GO
IFNOTOBJECT_ID('HintsDemo2','U')ISNULL
DROPTABLEHintsDemo2
GO
----创建测试数据表
CREATETABLEdbo.HintsDemo(HIDint,HTitleNvarchar(50))
GO
CREATETABLEdbo.HintsDemo2(HID2int,HIDint)
GO
----插入20条数据
INSERTHintsDemo(HID,HTitle)
VALUES(cast(rand()*10ASINT),replicate('X',cast(rand()*25ASINT)))
GO20--重复该语句20次,邀月注
INSERTHintsDemo2
SELECTTOP10cast(rand()*10ASINT),HIDfromHintsDemo
GO2--重复该语句2次,邀月注
--此时两个表各有20条记录
SELECT*FROMHintsDemo
SELECT*FROMHintsDemo2
SETSHOWPLAN_XMLON
GO
SELECTh.HID,h.HTitle,d.HID2
FROMHintsDemoh
INNERJOINHintsDemo2dON
h.HID=d.HID
GO
SETSHOWPLAN_XMLOFF
GO
下面使用HashJoin
SETSHOWPLAN_XMLON
GO
SELECTh.HID,h.HTitle,d.HID2
FROMHintsDemoh
InnerHASHJOINHintsDemo2dON
h.HID=d.HID
GO
SETSHOWPLAN_XMLOFF
GO
2、使用查询提示(QueryHints)
官方解释:
在查询语句的持续时间内,查询提示优先于查询优化器的默认行为。
您可以使用查询提示指定受影响的表的锁定方法、一个或多个索引、一个查询处理操作(如表扫描或索引查找)或其他选项。
查询提示应用于整个查询。
(
它的参数比较复杂:
:
=
{{HASH|ORDER}GROUP|{CONCAT|HASH|MERGE}UNION|{LOOP|MERGE|HASH}JOIN|EXPANDVIEWS|FASTnumber_rows|FORCEORDER|IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX|KEEPPLAN|KEEPFIXEDPLAN|MAXDOPnumber_of_processors|MAXRECURSIONnumber
|OPTIMIZEFOR(@variable_name{UNKNOWN|=literal_constant}[,...n])
|OPTIMIZEFORUNKNOWN
|PARAMETERIZATION{SIMPLE|FORCED}
|RECOMPILE|ROBUSTPLAN|USEPLANN'xml_plan'|TABLEHINT(exposed_object_name[,
}
:
=
[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@邀月************/
DECLARE@HintsTitleDemonvarchar(50)='0E2FAB59-9A22-4E14-B7BE-33AB500E3B9E'
SELECTHID,HTitle
FROMHintsDemo
WHEREHTitle=@HintsTitleDemo
ORDERBYHID
/*
HIDHTitle
60E2FAB59-9A22-4E14-B7BE-33AB500E3B9E
*/
--使用DMV查询查看内存中的统计计划是否可以重用
SELECTcacheobjtype,objtype,usecounts
FROMsys.dm_exec_cached_plans
CROSSAPPLYsys.dm_exec_sql_text(plan_handle)
WHEREtextLIKE'DECLARE@HintsTitleDemo%'
--先清除过程缓存,!
!
!
请不要在生产环境中使用下句
DBCCFREEPROCCACHE
/*DBCCexecutioncompleted.IfDBCCprintederrormessages,contactyoursystemadministrator.*/
DECLARE@HintsTitleDemonvarchar(50)='0E2FAB59-9A22-4E14-B7BE-33AB500E3B9E'
SELECTHID,HTitle
FROMHintsDemo
WHEREHTitle=@HintsTitleDemo
ORDERBYHID
OPTION(RECOMPILE)--强制重新编译
再次友情提醒,绝大多数情况下,SQLServer会做出较优的选择,极端情况下,我们才需要干预它的查询计划,以覆盖SQLServer的选择。
3、表提示(TableHints)
官方解释:
通过指定锁定方法、一个或多个索引、查询处理操作(如表扫描或索引查找)或其他选项,表提示可在数据操作语言(DML)语句执行期间覆盖查询优化器的默认行为。
表提示在DML语句的FROM子句中指定,仅影响在该子句中引用的表或视图。
(
表提示与查询提示类似,用于覆盖Select、INSERT、UPDATE和DELETE的默认行为。
可以为一个表设置多个表提示,并使用逗号分开,只要它们不改属于同个分组即可。
WITH(
:
=[NOEXPAND]{INDEX(index_value[,...n])|INDEX=(index_value)|FORCESEEK[(index_value(index_column_name[,...]))]|FORCESCAN|FORCESEEK|HOLDLOCK|NOLOCK
|NOWAIT|PAGLOCK|READCOMMITTED|READCOMMITTEDLOCK|READPAST|READUNCOMMITTED|REPEATABLEREAD|ROWLOCK|SERIALIZABLE
|SPATIAL_WINDOW_MAX_CELLS=integer|TABLOCK|TABLOCKX|UPDLOCK|XLOCK
}
:
=
{KEEPIDENTITY|KEEPDEFAULTS|HOLDLOCK|IGNORE_CONSTRAINTS|IGNORE_TRIGGERS|NOLOCK|NOWAIT|PAGLOCK|READCOMMITTED|READCOMMITTEDLOCK|READPAST|REPEATABLEREAD|ROWLOCK|SERIALIZABLE|TABLOCK|TABLOCKX|UPDLOCK|XLOCK
}
这里就不举例了,看MSDN(
值得注意的是NOlOCK选项,如下语句:
--不锁定执行查询
SELECTHID,HTitle
FROMHintsDemo
WITH(NOLOCK)
WHEREHID=4
/*
HIDHTitle
4E6DA3DB2-3D41-47B4-B4E3-DDA90918434C
41C4C9211-EB1C-42B5-A08A-558DC73462B4
4667C9985-3B0A-4767-AED9-82FEE623433D
*/
NOLOCK表提示让查询在不在被影响的行或数据是放置共享锁--允许你在不被阻塞或不阻塞其他查询的情况下读取(但会遇到“脏读”问题)。
最后,我们来介绍一个SQLServer2008引入的FORCESEEK表提示,它可以用来将索引扫描替换为索引查找。
会有一些原因导致SQLServer产生不良的查询计划。
例如表数据经常变化并且信息忆不再准确,或带有拙劣where子句的查询没有为查询优化器过程提供有用的或足够的信息。
如果为了单独查找指定数据,而在检索一行之前对整个非常大的表进行了整表扫描,由此带来的I/O开销当然是不能接受的。
假定上面的测试表非常大。
/*********使用ForceSeek强制覆盖SCAN***************/
/*********3w@邀月************/
--创建一个测试主键
ALTERtabledbo.HintsDemo
addPKIDintPrimarykeyIdentity(101,1)
GO
--此时,SQLServer为我们自动创建一个聚集索引[PK__HintsDem__5E0282723D61619B]
SELECT*FROMdbo.HintsDemo
--创建一个非聚集索引
CREATENONClusteredIndexidx_ForceSeekDemo
ONTestdb2.dbo.HintsDemo(HTitle)
----select*,Row_Number()over(PartitionBy[HID]Orderby[HID]Asc)AsRowID
----fromHintsDemo;
SETSHOWPLAN_XMLON
GO
SELECTDISTINCTHTitlefromHintsDemo
WHEREHIDBETWEEN8AND10andHTitle='141466E4-E8CC-4219-A9AF-7C0D2B86A668'
GO
SETSHOWPLAN_XMLOFF
注意本例使用With(ForceSeek)未必最优,只是提供了一个修改系统访问数据的方式,邀月注
SETSHOWPLAN_XMLON
GO
--注意本例使用With(ForceSeek)未必最优,只是提供了一个修改系统访问数据的方式,邀月注
SELECTDISTINCTHTitlefromHintsDemoWITH(FORCESEEK)
WHEREHIDBETWEEN8AND10andHTitle='141466E4-E8CC-4219-A9AF-7C0D2B86A668'
GO
SETSHOWPLAN_XMLOFF
你也可以更进一步指定使用哪个索引
SELECTDISTINCTHTitlefromHintsDemoWITH(FORCESEEK,INDEX(idx_ForceSeekDemo))
WHEREHIDBETWEEN8AND10andHTitle='141466E4-E8CC-4219-A9AF-7C0D2B86A668'
GO
对于本例,如果你需要得更好的性能,可以考虑使用SQLServer引入的指定行集索引功能,比如你只关注某房价表中均价在5000-6000元的楼盘信息,那么可以专门为这个区间建立索引。
这个属于题外话,不在本文讨论之列。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 中的 Hints