Sql优化笔记-基础概念

基数：某列的唯一键的数量。基数与总行数的比值再乘以100%就是选择性。

什么样的列必须创建索引呢？

当一个列出现在where条件中，该列没有创建索引，并且选择性大于20%，那么就必须创建索引，从而提升sql性能。当然了如果表只有数白条数据，就不用创建索引了。

第一个观点：只有大表才会有性能问题

回表：当对一个列创建索引后，索引包含该列的键值以及键值对应所在的rowid，通过索引的rowid访问表中的数据叫回表。回表一般是单块读，回表次数太多会严重影响性能。如果回表次数太多，就不应该走索引扫描，应该直接走全表扫描。特别要注意回表的物理I/O次数

**************************************************************************************

抓出必须创建索引的列

一、sys.dm_db_missing_index_details

sys.dm_db_missing_index_details 记录自sqlserver服务重启前所有运行sql脚本中缺失索引的情况。

mssql_sqlserver_sys.dm_db_missing_index_details视图内容说明

—–返回值列说明—-
index_handle:缺失索引标识信息，唯一标识列;
database_id :缺失索引涉及数据库;
object_id :缺失索引涉及表;
equality_columns:where 条件采用等式例 where tableName.[列名] =’值’
equality_columns 存储”列名”信息
inequality_columns :采用非等式的其它条件的列名信息
where tableName.[列名] < > ‘值’
where tableName.[列名] > ‘值’
where tableName.[列名] < '值' where tableName.[列名] like '%值%' inequality_columns 中存储此种操作模式涉及的"列名"信息 included_columns:查询中涉及的返回列(select ...) statement:索引涉及的表对象
例:
针对此表中的内容创建索引信息
create index ix_****** on [表名] (equality_columns/inequality_columns) include(included_columns)
—注意事项:
缺失索引表，只保留sqlserver服务器重启后的sql脚本的缺失情况。
即:需经常对此视图进行关注，获取更多的数据库优化信息

二、sys.dm_db_missing_index_groups

返回特定索引组中的缺失索引信息

三、sys.dm_db_missing_index_group_stats

2.mssql_sqlserver_sys.dm_db_missing_index_group_stats返回信息说明

返回索引缺失索引建立后，对性能的预估提升
返回集合中:
avg_user_impact:新建此索引后,sql脚本查询提升的百分比

四、sys.dm_db_missing_index_columns(index_handle)

返回索引索引列信息，接收参数index_handle来源于缺失索引系统视图 sys.dm_db_missing_index_details

五、获取缺失索引信息-举例说明

  /*
获取前20条缺失索引的情况
*/
SELECT TOP 20 
         DB_NAME() AS [数据库名称] ,
         d.[statement] AS [表名] ,
         equality_columns  as [列名1],
         inequality_columns  as [列名2],
         included_columns as [包含列]
 FROM    sys.dm_db_missing_index_groups g
         INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle
         INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
order by s.avg_total_user_cost desc

——————

 SELECT
QUOTENAME(SCHEMA_NAME(t.schema_id)) AS [架构名称],
QUOTENAME(t.name) AS [数据表名称],
QUOTENAME(i.name) AS [索引名称],
i.type_desc as [索引类型],
i.is_primary_key as [是否主键],
i.is_unique as [是否唯一],
i.is_unique_constraint as [是否外键],
STUFF(REPLACE(REPLACE((
SELECT QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END AS [data()] 
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
ORDER BY ic.key_ordinal
FOR XML PATH
), '<row>', ', '), '</row>', ''), 1, 2, '') AS [索引键列表],
STUFF(REPLACE(REPLACE((
SELECT QUOTENAME(c.name) AS [data()]
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
ORDER BY ic.index_column_id
FOR XML PATH
), '<row>', ', '), '</row>', ''), 1, 2, '') AS [包含列信息],
u.user_seeks,
u.user_scans,
u.user_lookups,
u.user_updates
FROM sys.tables AS t
INNER JOIN sys.indexes AS i ON t.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats AS u ON i.object_id = u.object_id AND i.index_id = u.index_id
WHERE t.is_ms_shipped = 0
AND i.type <> 0
——————————————————————

--缺失的索引
SELECT avg_total_user_cost * avg_user_impact * ( user_scans + user_seeks ) AS a ,
last_user_seek ,
last_user_scan ,
[statement] AS [对象名称] ,
'CREATE INDEX [IDX_' + CONVERT(VARCHAR(32), GS.group_handle) + '_'
+ CONVERT(VARCHAR(32), D.index_handle) + '_'
+ REPLACE(REPLACE(REPLACE([STATEMENT], ']', ''), '[', ''), '.', '')
+ ']' + ' ON ' + [statement] + ' (' + ISNULL(equality_columns, '')
+ CASE WHEN equality_columns IS NOT NULL
AND inequality_columns IS NOT NULL THEN ','
ELSE ''
END + ISNULL(inequality_columns, '') + ')' + ISNULL(' INCLUDE ('
+ included_columns
+ ')', '') AS [生成创建索引脚本]
FROM sys.dm_db_missing_index_groups AS G
INNER JOIN sys.dm_db_missing_index_group_stats AS GS ON G.index_group_handle = GS.group_handle
INNER JOIN sys.dm_db_missing_index_details AS D ON G.index_handle = D.index_handle
ORDER BY statement,a desc


--无用的索引
SELECT ind.index_id ,
obj.name AS [表名] ,
ind.name AS [索引名称] ,
ind.type_desc ,
indUsage.user_seeks ,
indUsage.user_scans ,
indUsage.user_lookups ,
indUsage.user_updates ,
indUsage.last_system_seek ,
indUsage.last_user_scan ,
'drop index [' + ind.name + '] ON [' + obj.name + ']' AS DropIndexCommand
FROM sys.indexes AS ind
INNER JOIN sys.objects AS obj ON ind.object_id = obj.object_id
LEFT JOIN sys.dm_db_index_usage_stats indUsage ON ind.object_id = indUsage.object_id
AND ind.index_id = indUsage.index_id
WHERE ind.type_desc <> 'HEAP'
AND obj.type <> 'S'
AND OBJECTPROPERTY(obj.object_id, 'isusertable') = 1
AND ( ISNULL(indUsage.user_seeks, 0) = 0
AND ISNULL(indUsage.user_scans, 0) = 0
AND ISNULL(indUsage.user_lookups, 0) = 0
)
ORDER BY obj.name ,
ind.name
_________________________________