数据库索引的使用和查询

数据库索引建立原则

主键不能添加聚集索引

在有主键的情况下，要先接触初见的聚集，才能再在其他字段建立聚集索引。

if exists (select * from sysindexes where name = 'IX_Teacher_Name')
drop index IX_Teacher_Name
go 
create clustered index IX_Teacher_Name
on Teacher(Name）

结果：

解决方法：删除主键约束

if exists (select * from sysobjects where name = 'PK_Teacher_Name')
alter table Teacher
drop constraint PK_Teacher_Name
if exists (select * from sysindexes where name = 'IX_Teacher_Name')
drop index IX_Teacher_Name
go 
create clustered index IX_Teacher_Name
on Teacher(Name）

使用DMV（Dynamic Management View）查询索引使用次数

declare @dbid int
select @dbid = db_id()
select objectname=object_name(s.object_id), s.object_id, indexname=i.name, i.index_id
 , user_seeks, user_scans, user_lookups, user_updates
from sys.dm_db_index_usage_stats s,
 sys.indexes i
where database_id = @dbid and objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by (user_seeks + user_scans + user_lookups + user_updates) asc
--使用多的索引排在前面
SELECT objects.name ,
databases.name ,
 indexes.name ,
user_seeks ,
user_scans ,
user_lookups ,
partition_stats.row_count
FROM sys.dm_db_index_usage_stats stats
LEFT JOIN sys.objects objects ON stats.object_id = objects.object_id
 LEFT JOIN sys.databases databases ON databases.database_id = stats.database_id
LEFT JOIN sys.indexes indexes ON indexes.index_id = stats.index_id
AND stats.object_id = indexes.object_id
LEFT JOIN sys.dm_db_partition_stats partition_stats ON stats.object_id = partition_stats.object_id
AND indexes.index_id = partition_stats.index_id
WHERE 1 = 1
--AND databases.database_id = 7
 AND objects.name IS NOT NULL
 AND indexes.name IS NOT NULL
 AND user_scans>0
ORDER BY user_scans DESC ,
 stats.object_id ,
 indexes.index_id

参数解释：

user_seeks : 通过用户查询执行的搜索次数。user_scans: 通过用户查询执行的扫描次数。user_lookups: 通过用户查询执行的查找次数。user_updates: 通过用户查询执行的更新次数。

查询结果：

索引缺失情况查询

目的：

帮助你查找数据库缺失的索引，以及建议添加的索引字段。

SELECT TOP 10
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
, avg_user_impact
, TableName = statement
, [EqualityUsage] = equality_columns
, [InequalityUsage] = inequality_columns
, [Include Cloumns] = included_columns
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 [Total Cost] DESC;

SQL语句在SQL执行引擎上的执行过程

Teacher表

SQL 查询语句：

select Age，Salary from Teacher where Name = 10

执行过程：

1) Teacher表在Name列上有一个非聚集索引，因此它查找非聚集索引树找出Name=ll 的记录;2) 包含Name= ll 记录的索引页也包括所有的聚集索引键(所有的主键键值，即Id);3) 针对每一个主键(这里是10)数据库查询操作，SQL Server引擎查找聚集索引树找出真实的行在对应页面中的位置;4) SQL Server引擎从对应的行查找Age和Salary列的值。

图解：

（编辑：源码网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!