技术分享会（二）：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.*, a.execution_count,a.total_worker_time, ????(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.*, a.total_logical_reads,a.avg_reads,a.total_logical_writes,a.avg_writes, a.total_worker_time,‘ ‘)?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 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |