34,593
社区成员
发帖
与我相关
我的任务
分享十八年老程员重磅分享 SQL优化经验(欢迎来喷)
一、SQL优化上不得不谈的SQL灾难
SQL灾难初期不显: 项目初期效果与灾难不可触及: 项目初期，业务数据量少， SQL执行效率的影响不明显，开发和运维以及业务人员无法体悟出个中好坏。
SQL灾难的后果是瘫痪性的:SQL的灾难影响通常是一大片,涉及到的业务项目、功能模块乃致整个数据库。这在我们公司的物流出仓，生产订单，销售订单等及时性高的业务部门项目上可能会给公司达来直接的经济损失，会成为被追责的对象。
SQL灾难的良方是未雨绸缪: SQL的灾难相当于新冠病毒,一旦有体症表现时,对于我们现在系统的宠大与复杂发生ＳＱＬ灾难时犹于新冠病毒发生在千万级别的武汉城，在查症确认上都已不是容易的事。SQL灾难以预防为主，结合个人与团队的经验未雨绸缪、疑事多问多学、防微杜渐。
二、SQL优化的一些方法
1. 索引的一些原则:
1). 索引的宽度
索引宽度，即索引的键占用了多少个字节。影响索引宽度有2个因素：一是引用的列，二是索引的数据类型。原则上，索引应保持较窄，就是说，列要尽可能少，列的数据类型要尽可能精简。
不能将 ntext、text、image、varchar(max)、nvarchar(max) 和 varbinary(max) 数据类型的列指定为索引键列。不过，varchar(max)、nvarchar(max)、varbinary(max) 和 xml 数据类型的列可以作为非键索引列参与非聚集索引,但不建议用。
设计时注意以下几类数据类型：
（1）日期型 vs. 日期时间型
旧版本的SQL Server只有日期时间型datetime（1753-01-01 00:00:00.000到 9999-12-31 23:59:59.999，8个字节）和smalldatetime（范围从1900-01-01 00:00到 2079-06-06 23:59，4个字节）。SQL Server 2008 新增了多种日期类型，其中包括date（范围从0001-01-01 到 9999-12-31，3个字节）和time(time数据类型支持从0到7不同的精度，就像DATETIME2格式，它的磁盘开销是3到5个字节)。如果某个列经常只需要查询日期，建议将实际业务的Datetime拆分为date和time类型的2个列，并在date类型的列上建立索引。
（2）整型 vs. 字符型
有些编号，例如客户ID，如果业务上没有特别要求，那么使用整型是最佳选择。因为，整型的范围从 -2,147,483,648 到 2,147,483,647 ，占4个字节，而同样范围的字符型却需要10个字节。此外，对于小范围的编号，smallint也是不错的选择，它的范围从 -32,768 到 32,767 ，只占2个字节。
（3）Uniqueidentifier列（GUID）vs. IDENTITY列
有些程序员希望每行有一个唯一标识，于是将GUID作为标识。如果在 表定义中将列类型指定为 uniqueidentifier，则列的值就为 GUID 类型，占16个字节。
CREATE TABLE Table1( MyID uniqueidentifier, MyName varchar(10) )
insert into Table1 values (newid(),'noname')
select * from Table1
从程序设计的角度来看，上述设计并无不妥。但如果在这个列上建立索引（尤其是聚集索引），对性能可能有很大的影响。建议改用IDENTITY列。
首先，GUID占用16个字节；而IDENTITY列为int类型，仅占用4个字节。对比之下，后者的索引宽度可以缩减12个字节。
其次，GUID是随机的，导致索引的分页现象非常严重；而IDENTITY列的值一般是连续增长，因此不会造成过多索引分页。
（4）主数据聚集主键: IDENTITY列 vs. varChar(10)编号列
有些程序员希望主数据以编号作为主键,然后在单据表里以主数据的编号字段写入记录.相对于用 IDENTITY列作为主数据表的主键并写入单据表记录的方法而言,记录相对看起来直观,大多程序员都乐意这样处理. 这可能在以往他们所经历的小公司或软件公司看起来没什么影响. 这样的主键索引 varchar(10)编号列 比 IDENTITY列 慢近20倍,对于我们公司大多时候都是百万级,千万级的数据,这样的主键带来20倍的效率损失将可能引发灾难性后果.
(5) T_Code_info的灾难:现在部份库的这个表已达5000条数据,而这个表的索引效率非常低.引发了很多问题,后续需要改为T_Code_head 与 T_Code_item二个主从表以分类方式存储优化这个表.
2.特殊情况下,建索引的一些经验:
1). 对于频繁插入更新的超大数据表, 聚集索引的正确创建犹其重要,否则后续数据量大的时候影响非常严重. 根据经验应以自增量字段或恒定顺序的唯一字段列作为聚集索引/聚集主键,可以大大提高数据插入时的时效,从而表被锁定的时间减小,能提高整个涉及此表查询的所有语句的执行效率. 这在公司的条码扫码表里已存在过负面例子.
约定:一年数据量在500W条记录以上的表, 必须以自增量字段为聚集索引/聚集主键,绝对不能以多个字段的复合索引做为聚集主键/聚集索引.
注: 超大数据表用自增量字段做为聚集索引,多个字段组合成非聚集主键做为唯一主键约束,这是对频繁插入与更新的大数据表的最优方案.
约定: 所有主数据表必须是int/bigint类型的NID自增量作为主键聚集索引,主从关系的单据表,主表必须是int/bigint类型的NID自增量作为聚集索引,并且从表以主表的NID字段作为关联字段.
(这里再提一句计量系统按秒写入数据表建表与建索引的经验)
2).主键: 是所有索引里除聚集索引外最快的, 所有表我们都需要建一个单索引/复合索引的主键(注意涉及主键的字段不能存在NULL值).
3). 设计索引的一个重要原则就是能用单索引不用复合索引,因为单索引往往比复合索引更有效.
如非必要不建议建复合索引(主键除外),因为复合索引的只有在单向顺序符合才生效. 如果不知道或记错顺序,项目上线后,到后期数据量上来后,才发现这复合索引实际在你的查询中不生效,很可能就会引发SQL灾难.
4). 索引列尽量不要参与计算，保持查询的索引列“干净”。比如， cast(create_time as date)='2020-03-06' 就不能使用索引，原因很简单，B+树中存储的都是数据表中的字段值，但是进行检索时，需要把所有元素都应用函数才能比较，显然这样的代价太大。所以语句要写成 ： create_time >=cast('2020-03-06 00:00:00' as datetime) And create_time< cast('2020-03-07 00:00:00' as datetime)
5).再次讲到应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描.
这里拿出来讲主要是上次有一个老同事有一条语句他优化几次都不得要领,问题发生在关联的子查询里有null值, 在主查询中直接通对子表里的null值作为查询条件对效率影响很大. 为了避免这情况应把在子查询里把这有null值的字段转化成其他特殊值. 如: nvl(ParentNO,'ISNULL', ParentNO) as ParentNO, 主查询里用 ParentNO<>'ISNULL'
6).应尽量避免在 where 子句的中间使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描,一定要用时,这类查询条件尽量别用(布尔型的改为= ),一定要用时尽量放到where 子句的最后。
这里引申出一个问题: 就是我们很多时候为了省事,把状态字段定义为char(2),这对查询效率的影响也是非常大的,这情况目前在我们的表里很常见.
解决方法: 改成布尔型或smallint或int, 且把状态表的说明维护在系统配置表里,并为方便查询可以在系统配置表里对状态分组标识以提高查询效率,减小 <>或IN查询的出现.
4.应尽量避免在 where 子句中使用 or 来连接条件，否则有可能导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
5.in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
6.下面的查询也将导致全表扫描：
select id from t where name like '%abc%'
7.应尽量避免在 where 子句中对字段进行计算表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2
8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3)='abc'--name以abc开头的id
应改为:
select id from t where name like 'abc%'
9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
11.不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(...)
12.很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)
13.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
14.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，
因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。
一个表的索引数最好不要超过12个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
15.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
16.字段值定长的字段使用char代替varchar,不定长的字段尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
17.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
18.在循环中为避免频繁创建和删除临时表，以减少系统表资源的消耗,应尽量在循环外创建和删除临时表。
19.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
20.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
21.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
22.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
23.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
24.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。
在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
25.尽量避免大事务操作，提高系统并发能力。
26.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
-- [完]-- 2020年03月04日 易登科(电话: 688336/13929928336)
————————————————
版权声明：本文为CSDN博主「moqing」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/moqing/article/details/104657457
SQL灾难初期不显: 项目初期效果与灾难不可触及: 项目初期，业务数据量少， SQL执行效率的影响不明显，开发和运维以及业务人员无法体悟出个中好坏。
SQL灾难的后果是瘫痪性的:SQL的灾难影响通常是一大片,涉及到的业务项目、功能模块乃致整个数据库。这在我们公司的物流出仓，生产订单，销售订单等及时性高的业务部门项目上可能会给公司达来直接的经济损失，会成为被追责的对象。
SQL灾难的良方是未雨绸缪: SQL的灾难相当于新冠病毒,一旦有体症表现时,对于我们现在系统的宠大与复杂发生ＳＱＬ灾难时犹于新冠病毒发生在千万级别的武汉城，在查症确认上都已不是容易的事。SQL灾难以预防为主，结合个人与团队的经验未雨绸缪、疑事多问多学、防微杜渐。
二、SQL优化的一些方法
1. 索引的一些原则:
1). 索引的宽度
索引宽度，即索引的键占用了多少个字节。影响索引宽度有2个因素：一是引用的列，二是索引的数据类型。原则上，索引应保持较窄，就是说，列要尽可能少，列的数据类型要尽可能精简。
不能将 ntext、text、image、varchar(max)、nvarchar(max) 和 varbinary(max) 数据类型的列指定为索引键列。不过，varchar(max)、nvarchar(max)、varbinary(max) 和 xml 数据类型的列可以作为非键索引列参与非聚集索引,但不建议用。
设计时注意以下几类数据类型：
（1）日期型 vs. 日期时间型
旧版本的SQL Server只有日期时间型datetime（1753-01-01 00:00:00.000到 9999-12-31 23:59:59.999，8个字节）和smalldatetime（范围从1900-01-01 00:00到 2079-06-06 23:59，4个字节）。SQL Server 2008 新增了多种日期类型，其中包括date（范围从0001-01-01 到 9999-12-31，3个字节）和time(time数据类型支持从0到7不同的精度，就像DATETIME2格式，它的磁盘开销是3到5个字节)。如果某个列经常只需要查询日期，建议将实际业务的Datetime拆分为date和time类型的2个列，并在date类型的列上建立索引。
（2）整型 vs. 字符型
有些编号，例如客户ID，如果业务上没有特别要求，那么使用整型是最佳选择。因为，整型的范围从 -2,147,483,648 到 2,147,483,647 ，占4个字节，而同样范围的字符型却需要10个字节。此外，对于小范围的编号，smallint也是不错的选择，它的范围从 -32,768 到 32,767 ，只占2个字节。
（3）Uniqueidentifier列（GUID）vs. IDENTITY列
有些程序员希望每行有一个唯一标识，于是将GUID作为标识。如果在 表定义中将列类型指定为 uniqueidentifier，则列的值就为 GUID 类型，占16个字节。
CREATE TABLE Table1( MyID uniqueidentifier, MyName varchar(10) )
insert into Table1 values (newid(),'noname')
select * from Table1
从程序设计的角度来看，上述设计并无不妥。但如果在这个列上建立索引（尤其是聚集索引），对性能可能有很大的影响。建议改用IDENTITY列。
首先，GUID占用16个字节；而IDENTITY列为int类型，仅占用4个字节。对比之下，后者的索引宽度可以缩减12个字节。
其次，GUID是随机的，导致索引的分页现象非常严重；而IDENTITY列的值一般是连续增长，因此不会造成过多索引分页。
（4）主数据聚集主键: IDENTITY列 vs. varChar(10)编号列
有些程序员希望主数据以编号作为主键,然后在单据表里以主数据的编号字段写入记录.相对于用 IDENTITY列作为主数据表的主键并写入单据表记录的方法而言,记录相对看起来直观,大多程序员都乐意这样处理. 这可能在以往他们所经历的小公司或软件公司看起来没什么影响. 这样的主键索引 varchar(10)编号列 比 IDENTITY列 慢近20倍,对于我们公司大多时候都是百万级,千万级的数据,这样的主键带来20倍的效率损失将可能引发灾难性后果.
(5) T_Code_info的灾难:现在部份库的这个表已达5000条数据,而这个表的索引效率非常低.引发了很多问题,后续需要改为T_Code_head 与 T_Code_item二个主从表以分类方式存储优化这个表.
2.特殊情况下,建索引的一些经验:
1). 对于频繁插入更新的超大数据表, 聚集索引的正确创建犹其重要,否则后续数据量大的时候影响非常严重. 根据经验应以自增量字段或恒定顺序的唯一字段列作为聚集索引/聚集主键,可以大大提高数据插入时的时效,从而表被锁定的时间减小,能提高整个涉及此表查询的所有语句的执行效率. 这在公司的条码扫码表里已存在过负面例子.
约定:一年数据量在500W条记录以上的表, 必须以自增量字段为聚集索引/聚集主键,绝对不能以多个字段的复合索引做为聚集主键/聚集索引.
注: 超大数据表用自增量字段做为聚集索引,多个字段组合成非聚集主键做为唯一主键约束,这是对频繁插入与更新的大数据表的最优方案.
约定: 所有主数据表必须是int/bigint类型的NID自增量作为主键聚集索引,主从关系的单据表,主表必须是int/bigint类型的NID自增量作为聚集索引,并且从表以主表的NID字段作为关联字段.
(这里再提一句计量系统按秒写入数据表建表与建索引的经验)
2).主键: 是所有索引里除聚集索引外最快的, 所有表我们都需要建一个单索引/复合索引的主键(注意涉及主键的字段不能存在NULL值).
3). 设计索引的一个重要原则就是能用单索引不用复合索引,因为单索引往往比复合索引更有效.
如非必要不建议建复合索引(主键除外),因为复合索引的只有在单向顺序符合才生效. 如果不知道或记错顺序,项目上线后,到后期数据量上来后,才发现这复合索引实际在你的查询中不生效,很可能就会引发SQL灾难.
4). 索引列尽量不要参与计算，保持查询的索引列“干净”。比如， cast(create_time as date)='2020-03-06' 就不能使用索引，原因很简单，B+树中存储的都是数据表中的字段值，但是进行检索时，需要把所有元素都应用函数才能比较，显然这样的代价太大。所以语句要写成 ： create_time >=cast('2020-03-06 00:00:00' as datetime) And create_time< cast('2020-03-07 00:00:00' as datetime)
5).再次讲到应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描.
这里拿出来讲主要是上次有一个老同事有一条语句他优化几次都不得要领,问题发生在关联的子查询里有null值, 在主查询中直接通对子表里的null值作为查询条件对效率影响很大. 为了避免这情况应把在子查询里把这有null值的字段转化成其他特殊值. 如: nvl(ParentNO,'ISNULL', ParentNO) as ParentNO, 主查询里用 ParentNO<>'ISNULL'
6).应尽量避免在 where 子句的中间使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描,一定要用时,这类查询条件尽量别用(布尔型的改为= ),一定要用时尽量放到where 子句的最后。
这里引申出一个问题: 就是我们很多时候为了省事,把状态字段定义为char(2),这对查询效率的影响也是非常大的,这情况目前在我们的表里很常见.
解决方法: 改成布尔型或smallint或int, 且把状态表的说明维护在系统配置表里,并为方便查询可以在系统配置表里对状态分组标识以提高查询效率,减小 <>或IN查询的出现.
4.应尽量避免在 where 子句中使用 or 来连接条件，否则有可能导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
5.in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
6.下面的查询也将导致全表扫描：
select id from t where name like '%abc%'
7.应尽量避免在 where 子句中对字段进行计算表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2
8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3)='abc'--name以abc开头的id
应改为:
select id from t where name like 'abc%'
9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
11.不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(...)
12.很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)
13.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
14.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，
因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。
一个表的索引数最好不要超过12个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
15.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
16.字段值定长的字段使用char代替varchar,不定长的字段尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
17.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
18.在循环中为避免频繁创建和删除临时表，以减少系统表资源的消耗,应尽量在循环外创建和删除临时表。
19.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
20.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
21.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
22.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
23.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
24.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。
在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
25.尽量避免大事务操作，提高系统并发能力。
26.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
-- [完]-- 2020年03月04日 易登科(电话: 688336/13929928336)
————————————————
版权声明：本文为CSDN博主「moqing」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/moqing/article/details/104657457
...全文
请发表友善的回复…
发表回复
吉普赛的歌 版主 2020-04-03
- 打赏
- 举报
绝大部分还是值得借鉴的， 感谢分享
