技术分享会（二）：SQLSERVER索引介绍
SQLSERVER索引介绍
一、SQLSERVER索引类型？
1、聚集索引；
2、非聚集索引；
3、包含索引；
4、列存储索引；
5、无索引（堆表）；
二、如何创建索引？
索引示例：
建表
create table t_test
(
id int identity(1,1),
name nvarchar(50),
[no] varchar(50),
[score] int,
created datetime
)
数据初始化
declare @i int = 1
while(@i <= 10000)
begin
insert into t_test(name,no,created,score)
select 'name_' + CAST(@i as varchar),'20190101-' + CAST(@i as varchar),DATEADD(day,@i,'2019-01-01'),CAST( rand() * 100 as int)
set @i = @i + 1
End
堆表
sp_helpindex t_test
select * from sysindexes where id = OBJECT_ID('t_test') -- indid = 0 堆表，1 聚集索引，2 列存储索引，大于等于3 常规索引；
查看执行计划
select * from t_test where id = 5000
添加主键（聚集索引）
alter table t_test add constraint PK_t_test primary key(id)
查看执行计划
select * from t_test where id = 10
非聚集索引
create index ix_created on t_test(created)
select * from t_test where created between '2019-01-08' and '2019-01-15'
包含索引
create unique index uix_no on t_test(no) include(name)
查看和对比执行计划
select * from t_test where no = '20190101-100'
select name,no from t_test where no = '20190101-100'
排序字段加入索引
查看执行计划
select * from t_test where created between '2019-01-08' and '2019-02-01'
select * from t_test where created between '2019-01-08' and '2019-02-01'
order by score desc
创建索引
create index ix_created_score on t_test(created,score)
三、如何检查索引是否被用到？是否还有索引未创建？
1、当前指定表的索引使用情况
declare @table as nvarchar(100) = 'crm_customer';
SELECT
(
select name
from sys.indexes
where object_id = stats.object_id and index_id = stats.index_id
) as index_name
,*
FROM sys.dm_db_index_usage_stats as stats
where object_id = object_id(@table)
order by user_seeks desc, user_scans desc, user_lookups desc
2、当前表可能缺失的索引
select d.*
, s.avg_total_user_cost
, s.avg_user_impact
, s.last_user_seek
,s.unique_compiles
from sys.dm_db_missing_index_group_stats s
,sys.dm_db_missing_index_groups g
,sys.dm_db_missing_index_details d
where s.group_handle = g.index_group_handle
and d.index_handle = g.index_handle
and object_id = object_id('SCM_Loan_Loan')
order by s.avg_user_impact desc
字段说明：
avg_total_user_cost：可通过组中的索引减少的用户查询的平均成本
avg_user_impact：该值表示如果实现此缺失索引组，则查询成本将按此百分比平均下降。
unique_compiles：将从该缺失索引组受益的编译和重新编译数
四、SQL Trace查看实时数据
五、扩展一Profiler的常用功能
列筛选：ClientProcessID，Duration，Reads，TextData
六、扩展二通过DMV分别找出最耗时、最耗CPU、调用最频繁的语句
-- 最耗时的sql
declare @n int
set @n=500 ;
with cte1 as
(
select a.*,t.*
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(a.plan_handle) t
where t.dbid >= 5
)
select
t.dbid,db_name(t.dbid) as dbname, a.total_worker_time,a.avg_time_ms,a.execution_count,a.cache_count,
replace(replace(t.text,CHAR(10),' '),CHAR(13),' ') as text
from
(
select top(@n)
plan_handle,
sum(total_worker_time) / 1000 as total_worker_time ,
sum(execution_count) as execution_count ,
count(1) as cache_count,
(sum(total_worker_time) / sum(execution_count) ) / 1000 as avg_time_ms
from cte1
group by plan_handle
order by avg_time_ms desc
) a
cross apply sys.dm_exec_sql_text(a.plan_handle) t
where avg_time_ms > 200
order by avg_time_ms desc
Go
-- 调用最频繁的sql
declare @n int
set @n=500 ;
with cte1 as
(
select a.*,t.*
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(a.plan_handle) t
where t.dbid >= 5
)
select
t.dbid,db_name(t.dbid) as dbname,
a.execution_count,a.total_worker_time,a.avg_time_ms,a.cache_count,
replace(replace(t.text,CHAR(10),' '),CHAR(13),' ') as text
from
(
select top(@n)
plan_handle,
sum(total_worker_time) / 1000 as total_worker_time ,
sum(execution_count) as execution_count ,
count(1) as cache_count,
(sum(total_worker_time) / sum(execution_count) ) / 1000 as avg_time_ms
from cte1
group by plan_handle
order by avg_time_ms desc
) a
cross apply sys.dm_exec_sql_text(a.plan_handle) t
order by execution_count desc
go
-- 最耗cpu的sql
declare @n int
set @n=500 ;
with cte1 as
(
select a.*,t.*
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(a.plan_handle) t
where t.dbid >= 5
)
select
t.dbid,db_name(t.dbid) as dbname,
a.total_logical_reads,a.avg_reads,a.total_logical_writes,a.avg_writes,a.execution_count,
a.total_worker_time,a.avg_time_ms,a.cache_count,
replace(replace(t.text,CHAR(10),' '),CHAR(13),' ') as text
from
(
select top(@n)
plan_handle,
sum(total_logical_reads) as total_logical_reads,
(sum(total_logical_reads) / sum(execution_count) ) as avg_reads,
sum(total_logical_writes) as total_logical_writes,
(sum(total_logical_writes) / sum(execution_count) ) as avg_writes,
sum(execution_count) as execution_count,
count(1) as cache_count,
sum(total_worker_time) as total_worker_time ,
(sum(total_worker_time) / sum(execution_count) ) / 1000 as avg_time_ms
from cte1
group by plan_handle
order by ( (sum(total_logical_reads) / sum(execution_count) ) + (sum(total_logical_writes) / sum(execution_count) ) ) desc
) a
cross apply sys.dm_exec_sql_text(a.plan_handle) t
order by (avg_reads + avg_writes) desc
go
