SQL-索引

转载

mob604756ea4c07 2021-09-09 10:35:00

文章标签 SQL 聚集索引 sql 非聚集索引数据库 文章分类 MySQL 数据库

# 本博大多数来自这两位大佬的博客或者问答：SQL中索引的原理、如何让你的SQL运行得更快

理解“聚集索引”和“非聚集索引”

① 聚集索引（clustered index，也称聚类索引、簇集索引）：把内容本身就是一种按照一定规则排列的目录称为“聚集索引”

我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。

② 非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）：目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”

如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。

由以上解释，就很容易理解：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。

索引使用的误区

① 主键就是聚集索引

通常，我们会在每个表中都建立一个 ID 列，以区分每条数据，并且这个 ID 列是自动增大的，步长一般为 1 。这种 ID 是自动生成，我们并不知道每条记录的ID号，所以我们很难在实践中用 ID 号来进行查询（若要查询需要提前知道要查询记录的 ID 号，这就有点本末倒置了）。

② 只要建立索引就能显著提高查询速度

并非是在任何字段上简单地建立索引就能提高查询速度，因此建立“适当”的聚合索引对于我们提高查询速度是非常重要的

③ 把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度

仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的，甚至比用上全部的复合索引列还要略快（在查询结果集数目一样的情况下）；而如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起任何作用的。

索引使用经验总结

① 用聚合索引比用不是聚合索引的主键速度快

② 用聚合索引比用一般的主键作order by时速度快，特别是在小数据量情况下

如果数据量很小的话，用聚集索引作为排序列要比使用非聚集索引速度快得明显的多；而数据量如果很大的话，如10万以上，则二者的速度差别不明显。

③ 使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚合索引使用了多少个

④ 日期列不会因为有分秒的输入而减慢查询速度

⑤ 建立一个“适当”的索引体系，特别是对聚合索引的创建

引有助于提高检索性能，但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引，数据库就要做更多的工作。过多的索引甚至会导致索引碎片。

不良的SQL

不良的 SQL 往往来自于不恰当的索引设计、不充份的连接条件和不可优化的 where 子句。在对它们进行适当的优化后，其运行速度有了明显地提高！

① 不恰当的索引设计

缺省情况下建立的索引是非聚集索引，但有时它并不是最佳的；合理的索引设计要建立在对各种查询的分析和预测上。一般来说：

• 有大量重复值、且经常有范围查询（ between , > , < , >= , <= ）和 order by 、group by 发生的列，可考虑建立聚集索引；

• 经常同时存取多列，且每列都含有重复值可考虑建立组合索引；

• 组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。

② 不充份的连接条件

eg：表 card 有 7896行，在 card_no 上有一个非聚集索引，表 account 有 191122行，在 account_no 上有一个非聚集索引。试看在不同的表连接条件下，两个 SQL 的执行情况：

1 select sum(a.amount) from account a,card b where a.card_no = b.card_no（20秒）
2 -- 将SQL改为：
3 select sum(a.amount) from account a,card b where a.card_no = b.card_no and a.account_no=b.account_no（< 1秒）
4 -- 在第一个连接条件下，最佳查询方案是将 account 作外层表，card 作内层表，利用 card 上的索引，其 I/O 次数可由以下公式估算为：
5 ---- 外层表 account 上的 22541页 +（ 外层表 account 的 191122行 * 内层表 card 上对应外层表第一行所要查找的3页 ）= 595907 次 I/O


6 -- 在第二个连接条件下，最佳查询方案是将 card 作外层表，account 作内层表，利用 account 上的索引，其 I/O 次数可由以下公式估算为：
7 ---- 外层表 card 上的 1944页 +（ 外层表 card 的 7896行 * 内层表 account 上对应外层表每一行所要查找的4页 ）= 33528 次 I/O

可见，只有充份的连接条件，真正的最佳方案才会被执行。

多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表。

内外表的选择可由公式：外层表中的匹配行数 * 内层表中每一次查找的次数确定，乘积最小为最佳方案。

③ 不可优化的 where 子句

# where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表搜索。

eg：

 1 select * from record where substring ( card_no , 1 , 4) ='5378'(13秒)
 2 --改为下面的SQL
 3 select * from record where card_no like '5378%'（ <1秒 )
 4 
 5 select * from record where amount/30 < 1000 ( 11秒 )
 6 --改为下面的SQL
 7 select * from record where amount < 1000*30（<1秒）
 8 
 9 select * from record where convert ( char(10) , date , 112 ) = '19991201'（10秒）
10 --改为下面的SQL
11 select * from record where date= '1999/12/01'（< 1秒）

# 所谓优化即where子句利用了索引，不可优化即发生了表扫描或额外开销

eg：表 stuff 有 200000 行，id_no 上有非聚集索引，请看下面这个 SQL ：

select count(*) from stuff where id_no in('0','1') （23秒）

where条件中的 'in' 在逻辑上相当于 'or' ，所以语法分析器会将 in ( '0' , '1' ) 转化为 id_no = '0' or id_no = '1' 来执行。

　　我们期望它会根据每个 or 子句分别查找，再将结果相加，这样可以利用 id_no 上的索引；但实际上（根据showplan ）,它却采用了 " OR 策略 " ，即先取出满足每个 or 子句的行，存入临时数据库的工作表中，再建立唯一索引以去掉重复行，最后从这个临时表中计算结果。因此，实际过程没有利用 id_no 上索引，并且完成时间还要受 tempdb 数据库性能的影响。

▶ 实践证明，表的行数越多，工作表的性能就越差，当stuff有620000行时，执行时间竟达到220秒！还不如将or子句分开：

select count(*) from stuff where id_no='0'
select count(*) from stuff where id_no='1'

得到两个结果，再作一次加法合算。因为每句都使用了索引，执行时间只有3秒，在620000行下，时间也只有4秒。

▶ 用更好的方法，写一个简单的存储过程：

 1 create proc count_stuff as
 2 declare @a int
 3 declare @b int
 4 declare @c int
 5 declare @d char(10)
 6 begin
 7 select @a=count(*) from stuff where id_no='0'
 8 select @b=count(*) from stuff where id_no='1'
 9 end
10 select @c=@a+@b
11 select @d=convert(char(10),@c)
12 print @d