SQL总复习四：SQL语句性能优化、以及正确的使用索引

SQL语句优化

SQL优化基本出发点，即本质

其实不只是数据库和 SQL，计算机世界里容易成为性能瓶颈的也是对硬盘，也就是文件系统的访问（个人计算机还可以通过增加内存，或者使用访问速度更快的硬盘等方法来提升性能）。不管是减少排序还是使用索引，抑或是避免中间表的使用，都是为了减少对硬盘的访问。

SQL语句的优化，主要有三方面：

• 避免排序
• 减少中间表
• 正确使用索引

---

参数是子查询时，使用 EXISTS 代替 IN

例如：

-- 慢
SELECT *
 FROM Class_A
 WHERE id IN (SELECT id FROM Class_B);
 
 -- 快
SELECT *
 FROM Class_A A
 WHERE EXISTS (SELECT 1 FROM Class_B B WHERE A.id = B.id);

参数是子查询时，使用连接代替 IN

-- 使用连接代替 IN
SELECT A.id, A.name
FROM Class_A A 
INNER JOIN Class_B B ON A.id = B.id;

避免排序

会进行排序的代表性的运算有下面这些。

GROUP BY 子句

ORDER BY 子句

聚合函数（SUM、COUNT、AVG、MAX、MIN）

DISTINCT

集合运算符（UNION、INTERSECT、EXCEPT）

窗口函数（RANK、ROW_NUMBER 等）

排序如果只在内存中进行，那么还好；但是如果内存不足因而需要在硬盘上排序，那么伴随着“呲啦呲啦”的硬盘访问声，排序的性能也会急剧恶化（下面的数据可能不太准确……据说硬盘的访问速度比内存的要慢上 100 万倍）。因此，尽量避免（或减少）无谓的排序是我们的目标。

灵活使用having子句

对聚合结果指定筛选条件时，使用 HAVING 子句是基本原则。

--使用中间表
SELECT *
 FROM (SELECT sale_date, MAX(quantity) AS max_qty
 FROM SalesHistory
 GROUP BY sale_date) TMP 没用的中间表
 WHERE max_qty >= 10;
 
 --更优的查询。HAVING 子句和聚合操作是同时执行的，所以比起生成中间表后再执行的 WHERE 子句，效率会更高一些，而且代码看起来也更简洁。
 SELECT sale_date, MAX(quantity) 
 FROM SalesHistory
 GROUP BY sale_date
 HAVING MAX(quantity) >= 10;
 

合理使用视图

特别是视图的定义语句中包含以下运算的时候，SQL 会非常低效，执行速度也会变得非常慢。

• 聚合函数（AVG、COUNT、SUM、MIN、MAX）
• 集合运算符（UNION、INTERSECT、EXCEPT 等）

减少中间表

在 SQL 中，子查询的结果会被看成一张新表，这张新表与原始表一样，可以通过代码进行操作。这种高度的相似性使得 SQL 编程具有非常强的灵活性，但是如果不加限制地大量使用中间表，会导致查询性能下降。

频繁使用中间表会带来两个问题，一是展开数据需要耗费内存资源，二是原始表中的索引不容易使用到（特别是聚合时）。因此，尽量减少中间表的使用也是提升性能的一个重要方法。

SQL语句中正确的索引使用方式

在极值函数（MAX/MIN）中使用索引

即max，或min 函数的参数列，在该列上建立索引。

这种方法并不是去掉了排序这一过程，而是优化了排序前的查找速度，从而减弱排序对整体性能的影响

能写在 WHERE 子句里的条件不要写在 HAVING 子句里

原因通常有两个。

第一个是在使用 GROUP BY 子句聚合时会进行排序，如果事先通过 WHERE 子句筛选出一部分行，就能够减轻排序的负担。

第二个是在 WHERE 子句的条件里可以使用索引。HAVING 子句是针对聚合后生成的视图进行筛选的，但是很多时候聚合后的视图都没有继承原表的索引结构。

在 GROUP BY 子句和 ORDER BY 子句中使用索引

一般来说，GROUP BY 子句和 ORDER BY 子句都会进行排序，来对行进行排列和替换。不过，通过指定带索引的列作为 GROUP BY 和 ORDER BY 的列，可以实现高速查询。特别是，在一些数据库中，如果操作对象的列上建立的是唯一索引，那么排序过程本身都会被省略掉。

避免索引不起作用

使用索引时，条件表达式的左侧应该是原始字段。如果左边不是原始字段，则SQL 语句本来是想使用索引，但实际上执行时却进行了全表扫描。

比如为col_1 建立了索引：

WHERE col_1 * 1.1 > 100; 不如 WHERE col_1 > 100 / 1.1;

同样的，在查询条件左侧使用函数时，也不能用到索引，比如下面这句：

WHERE SUBSTR(col_1, 1, 1) = 'a';

下面几种否定形式也不能用到索引，会全表扫描

• <>
• !=
• not in

使用 or 的时候，两边的条件中用到的列如果是有索引的，也是达不到使用索引的理想效果的

使用 LIKE 谓词时，只有前方一致的匹配才能用到索引。避免使用 LIKE 谓词进行后方一致或中间一致的匹配，如下面示例：

× SELECT * FROM SomeTable WHERE col_1 LIKE '%a'; 
× SELECT * FROM SomeTable WHERE col_1 LIKE '%a%'; 
√ SELECT * FROM SomeTable WHERE col_1 LIKE 'a%';

应避免进行默认的类型转换

对 char 类型的“col_1”列指定条件的示例

× SELECT * FROM SomeTable WHERE col_1 = 10;
√ SELECT * FROM SomeTable WHERE col_1 = '10'; 
√ SELECT * FROM SomeTable WHERE col_1 = CAST(10, AS CHAR(2));

默认的类型转换不仅会增加额外的性能开销，还会导致索引不可用，可以说是有百害而无一利。虽然这样写还不至于出错，但还是不要嫌麻烦，在需要类型转换时显式地进行类型转换吧（别忘了转换要写在条件表达式的右边）

ToUpper 代码将在 TSQL SELECT 语句的 WHERE 子句中放置一个函数。 这将阻止优化器使用索引。

---

参考书籍：图灵社区的《SQL进价教程》

---

数据库中的索引

以下以SQL Server 数据库为例。

相关链接：

索引 - SQL Server | Microsoft Learn

详细索引创建语法参考：CREATE INDEX (Transact-SQL) - SQL Server | Microsoft Learn

SQL Server 和 Azure SQL 索引体系结构和设计指南 - SQL Server | Microsoft Learn

SQL索引一步到位 - 老Key - 博客园 (cnblogs.com)

什么是索引

索引是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。 索引包含由表或视图中的一列或多列生成的键。 这些键存储在一个结构（B 树）中，使 SQL Server 可以快速有效地查找与键值关联的行。

索引就是数据表中指定的列数据和相应的存储位置（所在行）的关系列表，利用索引可以提高在表或视图中查找数据的速度。这就像通过书的目录快速找到你想看的内容，而不需要读完整本书。

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

但是索引对于提高查询性能也不是万能的，也不是建立越多的索引就越好。索引建少了，用 WHERE 子句找数据效率低，不利于查找数据。索引建多了，不利于新增、修改和删除等操作，因为做这些操作时，SQL SERVER 除了要更新数据表本身，还要连带立即更新所有的相关索引，而且过多的索引也会浪费硬盘空间。

索引设计不佳和缺少索引是提高数据库和应用程序性能的主要障碍。 设计高效的索引对于获得良好的数据库和应用程序性能极为重要。有关索引设计指南的信息，请参阅 SQL Server 索引设计指南。

注意：

• 每当修改了表数据后，都会自动维护表或视图的索引。
• 聚集索引和非聚集索引都可以是唯一的。 这意味着任何两行数据都不能有相同的索引键值。 另外，索引也可以不是唯一的，即多行可以共享同一键值。 有关详细信息，请参阅创建唯一索引。

索引分类

索引就类似于中文字典前面的目录，按照拼音或部首都可以很快的定位到所要查找的字。

索引主要的分类如下：

• 聚集索引（clustered index）：聚集索引就相当于使用字典的拼音查找，因为聚集索引存储记录是物理上连续存在的，即拼音 a 过了后面肯定是 b 一样。

• 非聚集索引（nonclustered index）：非聚集索引就相当于使用字典的部首查找，非聚集索引是逻辑上的连续，物理存储并不连续。

当然还有其他类型索引，这两种索引是数据库引擎中索引的基本类型，是理解其他类型索引的基础。

聚集索引和非聚集索引的区别：

聚集索引	非聚集索引
每个表只允许创建一个聚集索引	最多可以有249个非聚集索引
物理的重排表中的数据以符合索引约束	创建一个键值列表，键值指向数据在数据页中的位置
用于经常查找数据的列	用于从表中查找单个值的列

索引是针对表或视图中的列来创建的，聚集和非聚集索引，都可以用表中的一个列或多个列来建立。

聚集和非聚集索引适合的列

动作描述	使用聚集索引	使用非聚集索引
外键列	应	应
主键列	应	应
列经常被分组排序(order by)	应	应
返回某范围内的数据，如时间	应	不应
小数目的不同值	应	不应
大数目的不同值	不应	应
频繁更新的列	不应	应
频繁修改索引列	不应	应
一个或极少不同值	不应	不应

索引的创建

索引设计不佳和缺少索引是提高数据库和应用程序性能的主要障碍。 设计高效的索引对于获得良好的数据库和应用程序性能极为重要。有关索引设计指南的信息，请参阅 SQL Server 索引设计指南。

适合的创建索引的列

• 除主键外，定义有外键（或有关联其它表作用）的数据列一定要建立索引。
• 被频繁的用于where查询的列，最好建立索引。
• 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

不适合创建索引的列

• 索引键列的最大长度为 900 字节。即如果一个列的类型比如是 nvarchar（2000），则无法为其创建索引。

• 如果列中有几个不同的值，或者表中仅包含几行值，则不推荐为其创建索引。因为索引在搜索数据所花的时间比在表中逐行搜索话的时间更长。总之，表数据量很少就别花功夫创建索引。

索引定义原则

• 避免对经常更新的表进行过多的索引（超过5个就有些多了），并且索引中的列尽可能少。

• 在条件表达式中经常用到的、不同值较多的列上建立索引，在不同值少的列上不要建立索引。

• 在频繁进行排序或分组（即进行 GROUP BY 或 ORDER BY 操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

• 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

创建索引相关SQL示例

--查看指定表 Student 中的索引
exec sp_helpindex Student    
 
--删除指定表 Student 中名为 Index_StuNo_SName 的索引
drop index Student.Index_StuNo_SName
 
--创建索引语法
create [unique] [clustered | noclustered]
index index_name
on table_name (column_name ...)
[with fillfactor=x]
--unique 是指创建唯一约束的索引，即索引相关列的值，在各数据行中不能重复
   
--示例：在student表为name列创建索引
create index idx_stu_name on student(name) with(drop_existing=on);
--with(drop_existing=on) 表示如果索引在表上已存在则删除掉重新创建（语法检查可能会报错但能执行），默认为off。
--PS：当 create index 时，如果未指定 clustered 和 nonclustered，那么默认为 nonclustered。
 
--聚集索引
if (exists (select * from sys.indexes where name = 'idx_uqe_clu_stu_name_age'))
    drop index student.idx_uqe_clu_stu_name_age
go
create unique clustered index idx_uqe_clu_stu_name_age
on student(name, age);
 
--非聚集索引
if (exists (select * from sys.indexes where name = 'idx_name'))
    drop index student.idx_name
go
create  index idx_name
on
student(name);
 
 

SQL Server 中的DMV (dynamic management view)

首先我们来认识一下DMV，DMV (dynamic management view)动态管理视图和函数返回特定于实现的内部状态数据。这些被称为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获得的信息是准确和完整的，你才能变更数据库或者应用程序代码。

有了这个东西，你就可以查看：

• 最占用CPU、执行时间最长的命令
• 执行时间最长的命令
• SQL查询时索引被使用到的次数

检验索引的使用情况

我们创建的索引到底效率执行的怎么样？好不好？我们创建的对不对？

就是利用SQL Server 中的DMV去查看相关信息，具体可网上搜下现成代码。

---

更新于：2023-4-6