【SQL Server】性能优化-索引_sqlserver 优化现有的索引

1 索引

---

SQL索引在数据库优化中占有一个非常大的比例， 一个好的索引的设计，可以让你的效率提高几十甚至几百倍，在这里将带你一步步揭开他的神秘面纱。

1.1 什么是索引

---

SQL索引有两种：聚集索引和非聚集索引，索引主要目的是提高了SQL Server系统的性能，加快数据的查询速度与减少系统的响应时间。

下面举两个简单的例子：

---

1. 图书馆的例子：一个图书馆那么多书，怎么管理呢?    建立一个字母开头的目录，例如：a开头的书，在第一排，b开头的在第二排，这样在找什么书就好说了，这个就是一个聚集索引，可是很多人借书找某某作者的，不知道书名怎么办？图书管理员在写一个目录，某某作者的书分别在第几排，第几排，这就是一个非聚集索引

2. 字典的例子：字典前面的目录，可以按照拼音和部首去查询，我们想查询一个字，只需要根据拼音或者部首去查询，就可以快速的定位到这个汉字了，这个就是索引的好处，拼音查询法就是聚集索引，部首查询就是一个非聚集索引.

---

      看了上面的例子，下面的一句话大家就很容易理解了：聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。就像字段，聚集索引是连续的，a后面肯定是b，非聚集索引就不连续了，就像图书馆的某个作者的书，有可能在第1个货架上和第10个货架上。还有一个小知识点就是：聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。

1.2 索引的存储机制

---

      首先，无索引的表，查询时，是按照顺序存续的方法扫描每个记录来查找符合条件的记录，这样效率十分低下,举个例子，如果我们将字典的汉字随即打乱，没有前面的按照拼音或者部首查询，那么我们想找一个字，按照顺序的方式去一页页的找，这样效率有多低，大家可以想象。
      聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致，其实理解起来非常简单，还是举字典的例子：如果按照拼音查询，那么都是从a-z的，是具有连续性的，a后面就是b，b后面就是c， 聚集索引就是这样的，他是和表的物理排列顺序是一样的，例如有id为聚集索引，那么1后面肯定是2,2后面肯定是3，所以说这样的搜索顺序的就是聚集索引。非聚集索引就和按照部首查询是一样是，可能按照偏房查询的时候，根据偏旁‘弓’字旁，索引出两个汉字，张和弘，但是这两个其实一个在100页，一个在1000页，（这里只是举个例子），他们的索引顺序和数据库表的排列顺序是不一样的，这个样的就是非聚集索引。
      原理明白了，那他们是怎么存储的呢？在这里简单的说一下，聚集索引就是在数据库被开辟一个物理空间存放他的排列的值，例如1-100，所以当插入数据时，他会重新排列整个整个物理空间，而非聚集索引其实可以看作是一个含有聚集索引的表，他只仅包含原表中非聚集索引的列和指向实际物理表的指针。他只记录一个指针，其实就有点和堆栈差不多的感觉了

1.3 创建索引原则

1. 定义主键的数据列一定要建立索引。
2. 定义有外键的数据列一定要建立索引。
3. 对于经常查询的数据列最好建立索引。
4. 对于需要在指定范围内的快速或频繁查询的数据列。
5. 经常用在WHERE子句中的数据列。
6. 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。
7. 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
8. 对于定义为text、image和bit的数据类型的列不要建立索引。
9. 对于经常存取的列避免建立索引
10. 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。
11. 对于复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

---

若对上面的描述仍然存在疑问没有关系，我们搞个直接点的，比如常用字段是否需要创建索引，如下表所示：

1.4 如何创建索引

---

在这里我们不讲述索引的详细内容，主要是以性能优化方向为主，若想深入了解到SQL Server索引的原理及应用，请点击这里

1.4.1 创建索引

1. 语法
   （1）添加PRIMARY KEY（主键索引）：
   ALTER TABLE 表名 ADD PRIMARY KEY (列名 )
   主键索引一般在建表时就会创建，这个一般对实际项目的性能优化不会用到，所以不用关心
   （2）添加UNIQUE(唯一索引)：
   ALTER TABLE 表名 ADD UNIQUE (列名 )
   唯一索引一般也没啥用，必须保证唯一索引在表中没有重复值才可以创建使用
   （3）普通索引
CREATE INDEX index_name ON table_name (column_name)
   注释：“column_name” 规定需要索引的列。
   （4）组合索引
CREATE INDEX index_name ON table_name (column_name1，column_name2，…)
   注释：“column_name1” “column_name2” 规定需要索引的列。
   （5）添加FULLTEXT(全文索引)：
   ALTER TABLE 表名 ADD FULLTEXT (列名)
   
   从上面描述中我们只需要关心第（3）、（4）条即可
2. 示例
   本例会创建一个简单的索引，名为 “PersonIndex”，在Person 表的LastName列：

CREATE INDEX PersonIndex ON Person (LastName) 
1

        如果您希望以降序索引某个列中的值，您可以在列名称之后添加保留字 DESC：

CREATE INDEX PersonIndex ON Person (LastName DESC) 
1

        假如您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：

CREATE INDEX PersonIndex ON Person (LastName, FirstName)
1

1.4.1 删除索引

1. 语法
   DROP INDEX 命令删除表格中的索引。

drop index index_name on table_name ;
1

3. 示例

1.4.1 显示索引

1. 语法
   使用sp_helpindex存储过程来查询表中的相关的索引信息。
   exec sp_helpindex 表名
2. 示例

exec sp_helpindex 'Student'
1

1.5 索引使用次数、索引效率、占用CPU检测、索引缺失

       当我们明白了什么是索引，什么时间创建索引以后，我们就会想，我们创建的索引到底效率执行的怎么样？好不好？我们创建的对不对？
　　首先我们来认识一下DMV，DMV (dynamic management view)动态管理视图和函数返回特定于实现的内部状态数据。推出SQL Server 2005时，微软介绍了许多被称为dmvs的系统视图，让您可以探测SQL Server 的健康状况，诊断问题，或查看SQL Server实例的运行信息。统计数据是在SQL Server运行的时候开始收集的，并且在SQL Server每次启动的时候，统计数据将会被重置。当你删除或者重新创建其组件时，某些dmv的统计数据也可以被重置，例如存储过程和表，而其它的dmv信息在运行dbcc命令时也可以被重置。

       当你使用一个dmv时，你需要紧记SQL Server收集这些信息有多长时间了，以确定这些从dmv返回的数据到底有多少可用性。如果SQL Server只运行了很短的一段时间，你可能不想去使用一些dmv统计数据，因为他们并不是一个能够代表SQL Server实例可能遇到的真实工作负载的样本。另一方面，SQL Server只能维持一定量的信息，有些信息在进行SQL Server性能管理活动的时候可能丢失，所以如果SQL Server已经运行了相当长的一段时间，一些统计数据就有可能已被覆盖。

       因此，任何时候你使用dmv，当你查看从SQL Server 2005的dmvs返回的相关资料时，请务必将以上的观点装在脑海中。只有当你确信从dmvs获得的信息是准确和完整的，你才能变更数据库或者应用程序代码。

下面就看一下dmv到底能带给我们那些好的功能呢？

1.5.1 索引使用次数

我们下面看一下两种查询方式返回的结果（这两种查询的查询用途一致，只不过查看角度不一样）

1. 第一种方式

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
1
2
3
4
5
6
7
8

查询返回的结果

2. 第二种方式：使用多的索引排在前面

--使用多的索引排在前面
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
1
2
3
4
5
6
7
8
9
10
11
12
13

查询返回的结果

上述查询结果的字段说明
user_seeks : 通过用户查询执行的搜索次数。个人理解： 此统计索引搜索的次数
user_scans: 通过用户查询执行的扫描次数。个人理解：此统计表扫描的次数，无索引配合
user_lookups: 通过用户查询执行的查找次数。个人理解：用户通过索引查找，在使用RID或聚集索引查找数据的次数，对于堆表或聚集表数据而言和索引配合使用次数
user_updates: 通过用户查询执行的更新次数。个人理解：索引或表的更新次数

我们可以清晰的看到，那些索引用的多，那些索引没用过，大家可以根据查询出来的东西去分析自己的数据索引和表。

1.5.2 索引提高了多少性能

        新建了索引到底增加了多少数据的效率呢？到底提高了多少性能呢？运行如下SQL可以返回连接缺失索引动态管理视图，发现最有用的索引和创建索引的方法：

SELECT avg_user_impact AS average_improvement_percentage, avg_total_user_cost AS average_cost_of_query_without_missing_index, 
'CREATE INDEX ix_' + [statement] + ISNULL(equality_columns, '_') + ISNULL(inequality_columns, '_') + ' ON ' + [statement] + 
' (' + ISNULL(equality_columns, ' ') + ISNULL(inequality_columns, ' ') + ')' + ISNULL(' INCLUDE (' + included_columns + ')', '') 
AS create_missing_index_command
FROM sys.dm_db_missing_index_details a INNER JOIN 
sys.dm_db_missing_index_groups b ON a.index_handle = b.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats c ON b.index_group_handle = c.group_handle
WHERE avg_user_impact > = 40
1
2
3
4
5
6
7
8

查询返回的结果
上述查询结果的字段说明
avg_total_user_cost / average_cost_of_query_without_missing_index：减少的用户查询的平均成本
avg_user_impact / average_improvement_percentage：用户户查询可能获得的平均百分比收益

虽然用户能够修改性能提高的百分比，但以上查询返回所有能够将性能提高40%或更高的索引。你可以清晰的看到每个索引提高的性能和效率了

1.5.3 最占用CPU、执行时间最长命令

这个和索引无关，但是还是在这里提出来，因为他也属于DMV带给我们的功能吗，他可以让你轻松查询出，那些sql语句占用你的cpu最高

1. 占用cpu

SELECT TOP 100 execution_count,total_logical_reads /execution_count AS [Avg Logical Reads],
total_elapsed_time /execution_count AS [Avg Elapsed Time],db_name(st.dbid) as [database name],object_name(st.dbid) as [object name],
object_name(st.objectid) as [object name 1],SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
((CASE statement_end_offset WHEN - 1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/ 2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE execution_count > 100
ORDER BY 1 DESC;
1
2
3
4
5
6
7

查询返回的结果

2. 执行时间长

SELECT TOP 10 COALESCE(DB_NAME(st.dbid),DB_NAME(CAST(pa.value as int))+'*','Resource') AS DBNAME,
SUBSTRING(text,-- starting value for substring
CASE WHEN statement_start_offset = 0 OR statement_start_offset IS NULL
	THEN 1
ELSE 
	statement_start_offset/2 + 1 
END,
-- ending value for substring
CASE WHEN statement_end_offset = 0 OR statement_end_offset = -1 OR statement_end_offset IS NULL
THEN LEN(text)
ELSE statement_end_offset/2 
END 
-
CASE WHEN statement_start_offset = 0 OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2  END + 1)  AS TSQL,
total_logical_reads/execution_count AS AVG_LOGICAL_READS
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa
WHERE attribute = 'dbid'
ORDER BY AVG_LOGICAL_READS DESC ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

查询返回的结果

1.5.4 缺失索引

缺失索引就是帮你查找你的数据库缺少什么索引，告诉你哪些字段需要加上索引，这样你就可以根据提示添加你数据库缺少的索引了。

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;
1
2
3
4
5
6
7
8
9
10
11
12

查询返回的结果

1.5.5 适当创建索引覆盖

        假设你在Sales表(SelesID,SalesDate,SalesPersonID,ProductID,Qty)的外键列(ProductID)上创建了一个索引，假设ProductID列是一个高选中性列，那么任何在where子句中使用索引列(ProductID)的select查询都会更快，如果在外键上没有创建索引，将会发生全部扫描，但还有办法可以进一步提升查询性能。
假设Sales表有10,000行记录，下面的SQL语句选中400行(总行数的4%)：

SELECT SalesDate, SalesPersonID FROM Sales WHERE ProductID = 112
1

我们来看看这条SQL语句在SQL执行引擎中是如何执行的：

1)Sales表在ProductID列上有一个非聚集索引，因此它查找非聚集索引树找出ProductID=112的记录;

2)包含ProductID = 112记录的索引页也包括所有的聚集索引键(所有的主键键值，即SalesID);

3)针对每一个主键(这里是400)，SQL Server引擎查找聚集索引树找出真实的行在对应页面中的位置;

SQL Server引擎从对应的行查找SalesDate和SalesPersonID列的值。

在上面的步骤中，对ProductID = 112的每个主键记录(这里是400)，SQL Server引擎要搜索400次聚集索引树以检索查询中指定的其它列(SalesDate，SalesPersonID)。

如果非聚集索引页中包括了聚集索引键和其它两列(SalesDate,，SalesPersonID)的值，SQL Server引擎可能不会执行上面的第3和4步，直接从非聚集索引树查找ProductID列速度还会快一些，直接从索引页读取这三列的数值。

幸运的是，有一种方法实现了这个功能，它被称为“覆盖索引”，在表列上创建覆盖索引时，需要指定哪些额外的列值需要和聚集索引键值(主键)一起存储在索引页中。下面是在Sales 表ProductID列上创建覆盖索引的例子：

CREATE INDEX NCLIX_Sales_ProductID--Index name
　　ON dbo.Sales(ProductID)--Column on which index is to be created 　　INCLUDE(SalesDate, SalesPersonID)--Additional column values to include
1
2

应该在那些select查询中常使用到的列上创建覆盖索引，但覆盖索引中包括过多的列也不行，因为覆盖索引列的值是存储在内存中的，这样会消耗过多内存，引发性能下降。

1.5.6 索引碎片

在数据库性能优化一：数据库自身优化一文中已经讲到了这个问题，再次就不做过多的重复地址：索引碎片

2 案例分析

2.1 参考案例

参考案例：
https://blog.csdn.net/gprime/article/details/1687930
https://blog.csdn.net/shi_hong_fei_hei/article/details/81022161

2.2 案例实战

在上一节【SQL Server】性能优化-数据准备：使用存储过程生成百万数据我们创建了4张表并生成了数据。

• Student表：3000条数据
• Class表：30条数据
• Course表：10条数据
• Score表：600,0000条数据
  我们在原来的数据基础上再给学生表Student增加100,0000万数据

exec sp_Create_AssignStudent '张清宇',1000000
1

2.1 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

查询语句

select Id from Student where ClassId = '3DCEA8D9-92D8-484B-BAC1-DB13F0CB347F'
1

1. 创建索引前
   查看执行计划 选中要执行的SQL语句，鼠标右键点击可以看到“显示估计的执行计划”
   
   从图中可以看出，告诉我们缺少索引

   我们在不创建ClassId索引的前提下，执行3次时间分别为：31秒 23秒 25秒

2. 创建索引后

--创建索引
create index classId_index on Student (ClassId) 
1
2

查看执行计划
从上图可以看出，执行语句将通过索引classId_index查找,这就说明我们创建的索引查找生效了
同样的执行3次，执行时间分别为：24秒 15秒 14秒

根据数据对比，很明显索引查找的方式显得会更快一些

2.2 导致全表查询，索引不会生效的情况

1. like本身效率就比较低，应该尽量避免查询条件使用like；对于like ‘%...%’（全模糊）这样的条件，是无法使用索引的，也就是说会进行全表扫描。另外，由于匹配算法的关系，模糊查询的字段长度越大，模糊查询效率越低。
   解决方案：首先尽量避免模糊查询，如果因为业务需要一定要使用模糊查询，则至少保证不要使用全模糊查询，对于右模糊查询，即like ‘…%’，是会使用索引的； 左模糊:'%...'无法直接使用索引，但可以利用 like reverse('%321')的形式，变化成 like ‘123%’；

2. 查询条件中含有is null的select语句执行慢;

3. 查询条件中使用了不等于操作符（<>、!=）的select语句执行慢;
   SQL中，不等于操作符会限制索引，引起全表扫描，即使比较的字段上有索引
   解决方案：通过把不等于操作符改成or，可以使用索引，避免全表扫描。
   例如，把column <> ’aaa’，改成column < ’aaa’ or column > ’aaa’，就可以使用索引了。

4. or语句使用不当会引起全表扫描
   where子句中比较的两个条件，一个有索引，一个没索引，使用or则会引起全表扫描。例如：where A==1 or B==2，A上有索引，B上没索引，则比较B=：2时会重新开始全表扫描。

5. 组合索引，排序时应按照组合索引中各列的顺序进行排序，即使索引中只有一个列是要排序的，否则排序性能会比较差。解决方案：两个都加索引。
   错误做法：例如：create index skip1 on emp5(job,empno，date); select job，empno from emp5 where job=’manager’and empno=’10’ order by date desc;解决方案：例如：create index skip1 on emp5(job,empno，date);select job，empno from emp5 where job=’manager’and empno=’10’ order by job,empno,date desc;
   实际上只是查询出符合job=’manager’and empno=’10’条件的记录并按date降序排列。

6. Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。

7. 对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。

8. select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。