技术分享会（二）：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