SQL编写建议

编写高效的SQL语句往往能够获得更好的执行性能，下面将提供一些关于编写SQL语句的启发式建议，供大家参考。

名称	用 例	说 明
建议使用AS关键字显式声明别名	SELECT * FROM t1 laft JOIN t2 ON TRUE;	在当前语句中laft是t1表的别名，可见在列或者表别名中使用AS关键字显式声明别名代替隐式别名更易懂。
不建议给列通配符'*'设置别名	SELECT a FROM (SELECT t1.* AS a FROM t1);	列通配符设置别名会存在逻辑错误，导致别名无法被使用。
别名不要与表或列的名字相同	SELECT name FROM t1 AS t1 WHERE id < 100;	表或列的别名与其真实名称相同, 这样的别名会使得查询更难去分辨。
不建议使用前项通配符查找	SELECT a,b FROM t1 WHERE name LIKE '%foo';	查询参数有一个前项通配符的情况,会遍历全部索引，影响查询性能。
LIKE查询不建议使用'列名||字符串'	SELECT a,b FROM t1 WHERE name LIKE c||'foo%';	使用‘列名||字符串’形式进行匹配会导致查询无法走索引，影戏查询性能。
不建议使用没有通配符的LIKE查询	SELECT a,b FROM t1 WHERE name LIKE 'foo';	不包含通配符的 LIKE 查询可能存在逻辑错误，因为逻辑上它与等值查询相同。
避免WHERE在子句中进行NULL值判断	SELECT a FROM t1 WHERE a IS NULL;	使用IS NULL或IS NOT NULL可能导致引擎遍历全部索引，建议将a上设置默认值，避免NULL值。
不建议使用HINT	SELECT /*+ index(t1 i1) */ a FROM t1 ORDER BY a;	HINT是用来强制SQL按照某个执行计划来执行，但随着数据量变化无法保证当初的判断是正确的。
不建议一次INSERT太多数据	INSERT INTO t1(a) VALUES (1), (2);	单条INSERT语句批量插入大量数据性能较差，甚至可能导致从库同步延迟。
不建议使用ORDER BY RANDOM	SELECT a FROM t1 WHERE id < 1000 ORDER BY RANDOM();	ORDER BY RANDOM 是从结果集中检索随机行的一种非常低效的方法，因为它会对整个结果进行排序。
不建议对常量进行GROUP BY/ORDER BY	SELECT a,SUM(b) FROM t1 WHERE id < 1000 GROUP BY 1;	如果在GROUP BY/ORDER BY子句中使用数字，而不是表达式或列名称，当查询列顺序改变时，可能会导致问题。
不建议使用 HAVING 子句	SELECT a FROM t1 GROUP BY a HAVING a <> 1 ORDER BY a;	将查询的HAVING子句改写为WHERE中的查询条件，可以在查询处理期间使用索引。
删除全表时建议使用 TRUNCATE	DELETE t1;	删除全表时建议使用TRUNCATE替代DELETE，TRUNCATE在功能上与不带WHERE的DELETE语句相同，但速度更快。
谨慎使用TRUNCATE操作	TRUNCATE t1;	TRUNCATE无法返回被删除的行数，需要返回被删除的行数建议使用DELETE语法。TRUNCATE删除的数据无法回滚
UPDATE建议指定WHERE条件	UPDATE t1 SET a = 1;	UPDATE不指定WHERE条件一般是致命的，请您三思后行。
不建议使用 SELECT * 类型查询	SELECT * FROM t1 WHERE a = 1;	当表结构变更时，使用*通配符选择所有列将导致查询的含义和行为发生更改，可能导致查询返回更多的数据。
INSERT建议指定列名	INSERT INTO t1 VALUES(1,'name');	当表结构发生变更，如果 INSERT 请求不明确指定列名，请求的结果将会与预想的不同。
谨慎使用NULL	SELECT a,b,c FROM t1 WHERE a IS NULL OR a <> 1;	在NULL上进行运算、拼接操作，会得到NULL值，AND、OR和NOT操作如果涉及NULL，结果也让很多人感到困惑。
当列不能有缺失值时才使用NOT NULL	CREATE TABLE t1(id int NOT NULL);	将一列声明为NOT NULL时，这列中的每一个值都必须存在。TEXT、BLOB和JSON字段不建议为NOT NULL。
避免在WHERE中使用函数或其他运算符	SELECT a FROM t1 WHERE SUBSTRING(c,1,3)='ABC';	函数查询无法利用表中已经建立的索引，建议列名写在运算符左侧，不建议在比较条件两侧书写多余的括号。
指定WHERE条件，不建议使用COUNT操作	SELECT a, COUNT(*) FROM t1 WHERE a<2000 GROUP BY a ORDER BY b;	WHERE条件，COUNT操作需要扫描大量的行才能获取精确的结果，性能也因此不佳。
不建议使用COUNT(列名)代替COUNT(*)	SELECT COUNT(a) FROM t1;	不建议使用COUNT(列名)或COUNT(常量)来替代COUNT(*),COUNT(*)会统计NULL行，COUNT(列名)不会
建议将COUNT(*)>0 改写成EXISTS	SELECT * FROM t1 WHERE (SELECT COUNT(*) FROM t2 WHERE t2.a = t1.a) > 0;	可改写成SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.a = t1.a);
使用SUM时需注意列值为空的情况	SELECT SUM(a) FROM t1;	某一列值全是NULL时,SUM(col)返回NULL值。可通过SELECT IF(ISNULL(SUM(col)),0,SUM(col)) FROM t1;避免
不建议对等值查询列使用GROUP BY	SELECT a FROM t1 WHERE a = 3 GROUP BY a;	GROUP BY中的列在前面的WHERE条件中使用了等值查询，对这样的列进行GROUP BY意义不大。
同一张表不建议连接两次	SELECT t1.a FROM t1,t2 AS tb2 JOIN t2 ON TRUE;	相同的表在FROM子句中至少出现两次，可以简化为对该表的单次访问。
建议减少JOIN的数量	SELECT t1.a FROM t1 JOIN t2 ON TRUE JOIN t3 ON TRUE JOIN t4 ON TRUE;	连接的表越多，消耗时间越长，严重影响性能，可把连接表拆开成较小的几个执行。
建议将嵌套查询重写为JOIN	SELECT a FROM t1 WHERE a IN (SELECT a FROM t2);	将嵌套查询重写为JOIN通常会导致更高效的执行和更有效的优化。
建议将索引属性顺序与查询对齐	CREATE INDEX idx1 ON t1(a,b);	若为列创建复合索引，请确保查询属性与索引属性的顺序相同，以便DBMS在处理查询时使用索引。
不建议为表建立太多索引	CREATE INDEX idx1 ON t1(a);...CREATE INDEX idxi ON t1(i);	在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。
主键中的列不建议过多	CREATE TABLE tb1(a INT,b INT,c INT, PRIMARY KEY(a,b,c));	当表结构变更时，主键中的列过多会影响性能
使用LIMIT时建议使用ORDER BY	SELECT a,b FROM t1 WHERE a > 1 LIMIT 10;	没有ORDER BY的LIMIT会导致非确定性的结果，这取决于查询执行计划。
UPDATE/DELETE不建议指定ORDER BY	UPDATE t1 SET a = 12 WHERE b = 'abc' ORDER BY b;	对UPDATE/DELETE的数据使用ORDER BY意义不大。
UPDATE语句可能存在逻辑错误	UPDATE t1 SET a = 1, b = 2 WHERE c='A';	在一条UPDATE语句中，如果要更新多个字段，字段间不能使用 AND ，而应该用逗号分隔。
建议注意永远不真的比较条件	SELECT * FROM t1 WHERE 1 != 1;	查询条件永远非真，如果该条件出现在WHERE中可能导致查询无匹配到的结果。
建议使用UNION ALL替代UNION	SELECT * FROM t1 WHERE a<3 UNION SELECT * FROM t2 WHERE a>5	与去除重复的UNION不同，UNION ALL允许重复元组。如果您不关心重复元组，使用UNION ALL将更快。
执行计划中嵌套连接深度不建议过深	SELECT * FROM t1 WHERE a IN (SELECT a FROM (SELECT a FROM t1));	外部查询中的每一行作为依赖子查询执行子查询,影响查询性能。
不建议在子查询中使用函数	SELECT * FROM t1 WHERE a IN (SELECT MAX(a) FROM t2);	外部查询中的每一行作为依赖子查询，如果在子查询中使用函数，即使是SEMI-JOIN也很难进行高效的查询。
LIMIT的UNION下内查询建议添加LIMIT	(SELECT * FROM t1 LIMIT 20) UNION (SELECT * FROM t2 LIMIT 20) LIMIT 20;	数据库无法将限制条件从外层“下推”到内层，使得原本可以限制返回结果条件无法应用到内层查询的优化上。