SQL优化建议（不定时更新添加）

1.临时表和非日志表的存储方式建议和基表相同。当基表为行存（列存）表时，临时表和非日志表也推荐创建为行存（列存）表，可以避免行列混合关联带来的高计算代价。

2.索引字段的总长度不超过50字节。否则，索引大小会膨胀比较严重，带来较大的存储开销，同时索引性能也会下降，一张表的也最好不要超过6个。

3.不要使用DROP…CASCADE方式删除对象，除非已经明确对象间的依赖关系，以免误删。

4.数据加载和卸载最好在insert语句中显式给出插入的字段列表。例如：INSERT INTO test(id, name,age) VALUES ('100', 'lyg','99');

5.如果要清理表中的所有数据，建议使用truncate table方式，不要使用delete table方式。delete table方式删除性能差，且不会释放那些已经删除了的数据占用的磁盘空间。

6.类型转换最好在需要数据类型转换（不同数据类型进行比较或转换）时，使用强制类型转换，以防隐式类型转换结果与预期不符。在查询中，对常量要显式指定数据类型，不要试图依赖任何隐式的数据类型转换。

7.建议使用事务方式执行DDL和DML操作。例如，truncate table、update table、delete table、drop table等操作，一旦执行提交就无法恢复。对于这类操作，建议使用事务进行封装，必要时可以进行回滚。

8.建议在查询编写时，建议明确列出查询涉及的所有字段，不建议使用“SELECT *”这种写法。一方面基于性能考虑，尽量减少查询输出列；另一方面避免增删字段对前端业务兼容性的影响。

9.超过3张表或视图进行关联（特别是full join）时，执行代价难以估算。建议使用WITH TABLE AS语句创建中间临时表的方式增加SQL语句的可读性。

10.NULL值的比较只能使用IS NULL或者IS NOT NULL的方式判断，其他任何形式的逻辑判断都返回NULL。例如：NULL<>NULL、NULL=NULL和NULL<>1返回结果都是NULL，而不是期望的布尔值。

11.需要统计表中所有记录数时，不要使用count(col)来替代count(*)。count(*)会统计NULL值（真实行数），而count(col)不会统计。

12.在执行count(col)时，将“值为NULL”的记录行计数为0。在执行sum(col)时，当所有记录都为NULL时，最终将返回NULL；当不全为NULL时，“值为NULL”的记录行将被计数为0。

13.count(多个字段)时，多个字段名必须用圆括号括起来。例如，count( (col1,col2,col3) )。注意：通过多字段统计行数时，即使所选字段都为NULL，该行也被计数，效果与count(*)一致。

14.count(distinct col)用来计算该列不重复的非NULL的数量，NULL将不被计数，一般来说这种需求建议改写为：select count(1) from (select col from tablename group by col) tmp。

15.count(distinct (col1,col2,...))用来统计多列的唯一值数量，当所有统计字段都为NULL时，也会被计数，同时这些记录被认为是相同的。

16.使用连接操作符“||”替换concat函数进行字符串连接。因为concat函数生成的执行计划不能下推，导致查询性能严重劣化。

17.使用下面时间相关的宏替换now函数来获取当前时间。因为now函数生成的执行计划无法下推，导致查询性能严重劣化。

CURRENT_DATE：获取当前日期，不包含时分秒。

CURRENT_TIME：获取当前时间，不包含年月日。

CURRENT_TIMESTAMP(n)：获取当前日期和时间，包含年月日时分秒。n表示存储的毫秒位数。

18.尽量避免标量子查询语句的出现。标量子查询是出现在select语句输出列表中的子查询，在下面例子中，标色部分即为一个标量子查询语句：

SELECT id, (SELECT COUNT(*) FROM films f WHERE f.did = s.id) FROM staffs_p1 s;

标量子查询往往会导致查询性能急剧劣化，在应用开发过程中，应当根据业务逻辑，对标量子查询进行等价转换，将其写为表关联。

尽量使用JOIN来代替子查询，因为子查询需要嵌套查询,嵌套查询时会建立一张临时表，临时表的建立和删除都会有较大的系统开销，而连接查询不会创建临时表,因此效率比嵌套子查询高。

19.在where子句中，应当对过滤条件进行排序，把选择读较小（筛选出的记录数较少）的条件排在前面。

20.where子句中的过滤条件，尽量符合单边规则。即把字段名放在比较条件的一边，优化器在某些场景下会自动进行剪枝优化。形如col op expression，其中col为表的一个列，op为‘=’、‘>’的等比较操作符，expression为不含列名的表达式。例如：

SELECT id, from_image_id, from_person_id, from_video_id FROM face_data WHERE current_timestamp(6) - time < '1 days'::interval;

改写为：

SELECT id, from_image_id, from_person_id, from_video_id FROM face_data where time >  current_timestamp(6) - '1 days'::interval;

21.where的过滤中为了使得引擎使用索引而不全表扫描应：

①不要在where中对过滤字段进行null判断，如is null等。

②where使用like匹配的时候尽量不要使用百分号前置，例如like ‘%测试’，会导致全表扫描，后置百分号没问题。

③表达式过滤时应注意，例如age/2 > 10应改写为：age > 10 * 2。

④能用between的不使用in。

22.尽量避免不必要的排序操作。排序需要耗费大量的内存及CPU，如果业务逻辑许可，可以组合使用order by和limit，减小资源开销。一般数据库默认按照ASC & NULL LAST进行排序。

23.建议在使用ORDER BY子句进行排序时，显式指定排序方式（ASC/DESC），NULL的排序方式（NULLS FIRST/NULLS LAST）。

24.不要单独依赖limit子句返回特定顺序的结果集。如果部分特定结果集，可以将ORDER BY子句与Limit子句组合使用，必要时也可以使用offset跳过特定结果。

25.在保障业务逻辑准确的情况下，建议尽量使用UNION ALL来代替UNION。

26.如果过滤条件只有OR表达式，可以将OR表达式转化为UNION ALL以提升性能。使用OR的SQL语句经常无法优化，导致执行速度慢。例如，下面语句的改写：

SELECT * FROM scdc.pub_menu

WHERE (cdp= 300 AND inline=301) OR (cdp= 301 AND inline=302) OR (cdp= 302 ANDinline=301);

改写为：

SELECT * FROM scdc.pub_menu

WHERE (cdp= 300 AND inline=301)

union all

SELECT * FROM scdc.pub_menu

WHERE (cdp= 301 AND inline=302)

union all

SELECT * FROM tablename

WHERE (cdp= 302 AND inline=301)

27.当in(val1, val2, val3…)表达式中字段较多时，建议使用in (values (va11), (val2),(val3)…)语句进行替换。优化器会自动把in约束转换为非关联子查询，从而提升查询性能。

28.在关联字段不存在NULL值的情况下，使用(not) exist代替(not) in。例如，在下面查询语句中，当T1.C1列不存在NULL值时，可以先为T1.C1字段添加NOT NULL约束，再进行如下改写：

SELECT * FROM T1 WHERE T1.C1 NOT IN (SELECT T2.C2 FROM T2);

改写为：

SELECT * FROM T1 WHERE NOT EXISTS (SELECT * FROM T1,T2 WHERE T1.C1=T2.C2);

说明1：

如果不能保证T1.C1列的值为NOT NULL的情况下，就不能进行上述改写。

如果T1.C1为子查询的输出，要根据业务逻辑确认其输出是否为NOT NULL。

说明2：

区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

29.最好通过游标进行翻页查询，而不是使用LIMIT OFFSET语法，避免多次执行带来的资源开销。且游标必须在事务中使用，执行完后务必关闭游标并提交事务。

30.高效数据类型，主要包括以下三方面：

①尽量使用执行效率比较高的数据类型

一般来说整型数据运算(包括=、＞、＜、≧、≦、≠等常规的比较运算，以及group by)的效率比字符串、浮点数要高。比如某场景中对列存表进行点查询，filter条件在一个numeric列上，执行时间为10+s；修改numeric为int类型之后，执行时间缩短为1.8s左右。

②尽量使用短字段的数据类型

长度较短的数据类型不仅可以减小数据文件的大小，提升IO性能；同时也可以减小相关计算时的内存消耗，提升计算性能。比如对于整型数据，如果可以用smallint就尽量不用int，如果可以用int就尽量不用bigint。

③使用一致的数据类型

表关联列尽量使用相同的数据类型。如果表关联列数据类型不同，数据库必须动态地转化为相同的数据类型进行比较，这种转换会带来一定的性能开销。

31. 优化改写sql：将SQL的排序往后推：

SELECT COUNT

         ( 1 ) OVER ( ) AS DATACNT,

         IMSI AS IMSI_IMSI,

         CAST ( TRUNC( ( ( SUM ( L4_UL_THROUGHPUT ) + SUM ( L4_DW_THROUGHPUT ) ) ), 0 ) AS DECIMAL ( 20 ) ) AS TOTAL_VOLOME_KPIID

FROM

         PUBLIC.test AS test

GROUP BY

         IMSI

ORDER BY

         TOTAL_VOLOME_KPIID DESC;

改为：

SELECT COUNT

         ( 1 ) OVER ( ) AS DATACNT,

         IMSI_IMSI,

         TOTAL_VOLOME_KPIID

FROM

         (

         SELECT

                  IMSI AS IMSI_IMSI,

                 CAST ( TRUNC( ( ( SUM ( L4_UL_THROUGHPUT ) + SUM ( L4_DW_THROUGHPUT ) ) ), 0 ) AS DECIMAL ( 20 ) ) AS TOTAL_VOLOME_KPIID

         FROM

                  PUBLIC.test AS test

         GROUP BY

                  IMSI

         ORDER BY

                  TOTAL_VOLOME_KPIID DESC

         );

32. pg_advisory_lock的使用注意：

SELECT pg_advisory_lock(id) FROM foo WHERE id = 12345; -- 可以

SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100; -- 危险!

SELECT pg_advisory_lock(q.id) FROM

(

 SELECT id FROM foo WHERE id > 12345 LIMIT 100

) q; -- 可以

33.

代码1：

select

a.s_no

from student_orc_partition a

inner join

student_orc_partition_only b

on a.s_no=b.s_no and a.part=b.part and a.part>=1 and a.part<=2;

代码2：

select

a.s_no

from student_orc_partition a

inner join

student_orc_partition_only b

on a.s_no=b.s_no and a.part=b.part

where a.part>=1 and a.part<=2;

上述的两个SQL并不等价，代码1在内连接（inner join）中的连接条件（on）中加入非等值的过滤条件后，并没有将内连接的左右两个表按照过滤条件进行过滤，内连接在执行时会多读取part=0的分区数据。而在代码2中，会过滤掉不符合条件的分区。

34.左连接的过滤条件注意：

代码1：

explain dependency

select

a.s_no

from student_orc_partition a

left join

student_orc_partition_only b

on a.s_no=b.s_no and a.part=b.part and b.part>=1 and b.part<=2;

代码2：

explain dependency

select

a.s_no

from student_orc_partition a

left join

student_orc_partition_only b

on a.s_no=b.s_no and a.part=b.part and a.part>=1 and a.part<=2;

上面两个结果相同的SQL，对左外连接在连接条件中加入非等值过滤的条件，如果过滤条件是作用于右表（b表）有起到过滤的效果，则右表只要扫描两个分区即可，但是左表（a表）会进行全表扫描。如果过滤条件是针对左表，则完全没有起到过滤的作用，那么两个表将进行全表扫描。这时的情况就如同全外连接一样都需要对两个数据进行全表扫描。

35.

select id,name from tb1 a join tb2 b on(a.id = b.id);

可以优化替换为：

select id,name from tb1 where id in(select id from tb2);

 36.在一些场景下，考虑使用TIMESTAMP代替DATETIME

这两种类型的都能表达"yyyy-MM-dd HH:mm:ss"格式的时间，TIMESTAMP只需要占用4个字节的长度，可以存储的范围为(1970-2038)年，在各个时区，所展示的时间是不一样的；

而DATETIME类型占用8个字节，对时区不敏感，可以存储的范围为(1001-9999)年。

37.JOIN的表不允许超过五个。需要JOIN的字段，数据类型必须绝对一致; 多表关联查询时，保证被关联的字段需要有索引。

太多表的JOIN会让Mysql的优化器更难权衡出一个“最佳”的执行计划（可能性为表数量的阶乘），同时要注意关联字段的类型、长度、字符编码等等是否一致。

38.建立联合索引时，必须将区分度更高的字段放在左边：

区分度更高的列放在左边，能够在一开始就有效的过滤掉无用数据。提高索引的效率，相应我们在Mapper中编写SQL的WHERE条件中有多个条件时，需要先看看当前表是否有现成的联合索引直接使用，注意各个条件的顺序尽量和索引的顺序一致。

39.如果有ORDER BY的场景，请注意利用索引的有序性。ORDER BY最后的字段是联合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。

假设有查询条件为WHERE a=? and b=? ORDER BY c；存在索引：a_b_c，则此时可以利用索引排序；
反例：在查询条件中包含了范围查询，那么索引有序性无法利用，如:WHERE a>10 ORDER BY b; 索引a_b无法排序。

40.不使用ORDER BY RAND()

select id from `table_name` 

order by rand() limit 1000;

上面的sql语句，可优化为

select id from `table_name` t1 join 

(select rand() * (select max(id) from `table_name`) as nid) t2 

on t1.id > t2.nid limit 1000;

41.避免在 where 子句中对字段进行 null 值判断，因为对于Null的判断会导致引擎放弃使用索引而进行全表扫描。

42.特殊情况下，例如要这样查询：LIKE “%name”或者LIKE “%name%”，导致索引失效而进行全表扫描，这个时候我们可以选择使用全文索引：

创建全文索引的sql语法是：

ALTER TABLE `table_name` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);

使用全文索引的sql语句是：

select id,fnum,fdst from table_name 

where match(user_name) against('zhangsan' in boolean mode);

43.巧用STRAIGHT_JOIN：

inner join是由mysql选择驱动表，但是有些特殊情况需要选择另个表作为驱动表，比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序，在STRAIGHT_JOIN左边的表名就是驱动表，右边则是被驱动表。

在使用STRAIGHT_JOIN有个前提条件是该查询是内连接，也就是inner join。其他链接不推荐使用STRAIGHT_JOIN，否则可能造成查询结果不准确。