SQL优化

目录

一、避免使用LIKE、OR、NOT查询条件

二、禁止SELECT套用子查询

三、不要给底基字段加索引

四、表不使用外键

五、尽量避免使用SELECT *

六、关键字大写，表名和字段名全小写

七、数据库对象的命名不要以数字开头

八、表必须有主键

九、数据库表字段命名使用英文单词

十、创建复合主键或索引时要把高基数列放在前面

十一、可以的话使用IN代替OR

十二、使用EXISTS代替IN子查询

十三、对索引字段尽量不要使用函数

十五、注意NULL值的判断不要使用等号，只能使用IS NULL或IS NOT NULL

十六、查询记录数使用SELECT COUNT(1)/COUNT(*)，而不要使用COUNT(字段)这样的写法

十七、尽量使用UNION ALL而不是UNION

十九、不允许事务嵌套

二十、不允许事务内调用外部服务

二十一、不允许对事务内部发生的异常进行吞并

二十二、每张表都应该加入创建时间和更新时间字段

二十三、Mysql的表不允许使用自增主键

二十四、所有涉及到数据字典的SQL语句，不允许将数据字典写死固定

二十五、WHERE条件里要把能过滤掉最多数据的条件放在最前面（注，ORACLE放在最后面）

-------------------------------------------2022-11-16------------------------------------------------

二十六、where条件后尽量避免使用函数，影响效率（如：dateformdata）

二十七、join 时，主表where尽量在前

-------------------------------------------2024-01-17------------------------------------------------

二十八、更新表中某个字段状态时，应带着原状态更新

二十九、这是为了避免union产生的虚拟表进行筛选时，导致索引失效

---

---

一、避免使用LIKE、OR、NOT查询条件

• 这会使得查询SQL进行全表扫描。同理还有<>、!=

二、禁止SELECT套用子查询

• 主查询的每一条记录都会去执行一次SELECT字段上的子查询，这样的SQL性能可想而知，是慢得多么恐怖

  /* 不要这样使用子查询 */
  ​
  SELECT a.cust_no, a.cust_name, 
  ​
  •    (SELECT b.orgname FROM sys_org b WHERE a.orgno=b.orgno) orgname
  ​
  FROM cust_info a
  ​
  WHERE ......
   
  ​
  **/\* 应该使用关联查询代替*/
  ​
  SELECT a.cust_no, a.cust_name, 
  ​
  •    b.orgname
  ​
  FROM cust_info a
  ​
  •    LEFT JOIN sys_org b ON a.orgno=b.orgno
  ​
  WHERE ......

三、不要给底基字段加索引

• 不要给“性别”、“状态”这样的底基数字段添加索引

四、表不使用外键

• 表不使用外键，通过业务程序对表关联数据进行处理

五、尽量避免使用SELECT *

• SELECT * 会使得SQL进行全字段扫描，影响查询效率。但在某些特定场景，例如oracle或db2分页包装的最外层SELECT可以）

六、关键字大写，表名和字段名全小写

SELECT a.col1, a.col2, a.col3, a.....,
​
•    b.col....,
​
•    c.col....
​
FROM table1 a, table2 b
​
•    LEFT JOIN table3 c ON a....=c....
​
WHERE ......
​
GROUP BY ...
​
ORDER BY ...

七、数据库对象的命名不要以数字开头

• 所有数据库对象（包括表、字段、索引、序列等）都使用全小写字母命名，可以有数字，但不可以用数字开头

八、表必须有主键

九、数据库表字段命名使用英文单词

• 数据库表字段命名使用英文单词，超长（超过5-6位）的单词可取单词的头4-6个字母形成缩写，或取约定俗成大家基本可以理解的缩写

十、创建复合主键或索引时要把高基数列放在前面

• 例如客户份额表，主键是【客户代码】+【产品代码】，因为客户数据绝对比产品数据多，创建主键时必须把【客户代码】放在前面

  CREATE TABLE cust_vol 
  ​
  (
  ​
  ... ...
  ​
  PRIMARY KEY (cust_no, prod_code) /* 不要写成 (prod_code, cust_no)*/
  ​
  )

十一、可以的话使用IN代替OR

/* 可以使用IN的条件示例 */
​
SELECT ......
​
FROM table1
​
WHERE code='001' OR code='002' OR code='003'
​
/* 如上，即使code字段创建了索引，数据库会因为使用了or而导致全表扫描 */
 
​
/* 应该改为使用IN */
​
SELECT ......
​
FROM table1
​
WHERE code IN ('001', '002', '003')
​
/*只要IN的括号里使用的都是常量值（没有使用表达式和函数），那么这种情况下还可以使用code的索引进行检索*/

十二、使用EXISTS代替IN子查询

• 对于IN子查询结果的情况，通常使用EXISTS比IN快

• /* 可以使用EXISTS的条件示例 */
  ​
  

  SELECT ......
  ​
  FROM table1
  ​
  WHERE code IN (
  ​
  SELECT code FROM table2 WHERE ...
  ​
  )
  ​
   
  ​
  /* 应该使用EXISTS代替 */
  ​
  SELECT ......
  ​
  FROM table1 a
  ​
  WHERE EXISTS (
  ​
  •    SELECT 1 FROM tabl2 b WHERE a.code=b.code AND ...
  ​
  )

十三、对索引字段尽量不要使用函数

• 对索引字段尽量不要使用函数，尽量将函数用在常量上面，如果条件表达式的一边要使用函数，请把它放在等号的右边

  对索引字段使用函数，会使索引不起作用

  SELECT ......
  FROM table1
  WHERE TO_CHAR(date_col,'yyyymmdd')='20150930'
  /* 如果date_col字段创建了索引，数据库会因为使用了函数而不使用索引检索的 */
  ​
  /* 应该改为这样使用 */
  SELECT ......
  FROM table1
  WHERE date_col=TO_DATE('20150930','yyyymmdd')

  索引字段参与表达式运算，也会使索引不起作用

  SELECT ......
  FROM table1
  WHERE amt * nav = vol
  /* 如果amt字段创建了索引，数据库会因为其参与表达式运算而不使用索引检索的 */
  ​
  /* 应该改为这样使用 */
  SELECT ......
  FROM table1
  WHERE amt = vol / nav
  /*注意，这里有一个前提是amt有索引，那么这样改就有作用*/

十五、注意NULL值的判断不要使用等号，只能使用IS NULL或IS NOT NULL

• 一定要记住NULL不等于任何值，也不会不等于任何值，所以使用NULL做等于或不等于运算永远都会是false

  /*这样是查询不到记录的*/
  ​
  SELECT ......
  ​
  FROM table1
  ​
  WHERE col1=NULL /* 这样的条件永远都是false，不会成立的 */
  ​
  /*甚至于这样，都不会有任何记录返回*/
  ​
  SELECT ......
  ​
  FROM table1
  ​
  WHERE col1<>NULL /* 这样的条件永远都是false，不会成立的 */
   
  ​
  /* 查询字段为NULL的记录，应该这样使用 */
  ​
  SELECT ......
  ​
  FROM table1
  ​
  WHERE col1 IS NULL
  ​
  ​
  /*查询不为NULL的记录，应该这样使用*/
  ​
  SELECT ......
  ​
  FROM table1
  ​
  WHERE col1 IS NOT NULL

  • 还有一点需要注意的是在某些数据库（如ORACLE）版本里空字符串（'' IS NULL）是返回true的

十六、查询记录数使用SELECT COUNT(1)/COUNT(*)，而不要使用COUNT(字段)这样的写法

• 注意：已知Informix数据库不支持COUNT(1)，只能使用COUNT(*)

十七、尽量使用UNION ALL而不是UNION

十八、使用UNION ALL的多条SQL语句，应该将WHERE条件写在每一条SQL里，而不是在UNION ALL的最外层

/正确的写法/
​
SELECT col1, col2, col3
​
FROM table1 
​
WHERE ... ...
​
UNION ALL
​
SELECT col1, col2, col3
​
FROM table2
​
WHERE ... ...
​
 
​
/* 错误的写法 */
​
SELECT * 
​
FROM (
​
SELECT col1, col2, col3
​
FROM table1 
​
UNION ALL
​
SELECT col1, col2, col3
​
FROM table2
​
) t
​
WHERE ... ...

十九、不允许事务嵌套

二十、不允许事务内调用外部服务

二十一、不允许对事务内部发生的异常进行吞并

• 不允许对事务内部发生的异常进行吞并，应该让异常抛出，事务回滚，除非有其他特殊的业务需求（例如业务需要对主键冲突的情况进行其他特殊业务处理）

二十二、每张表都应该加入创建时间和更新时间字段

• 此种情况下，需要在各自模块constant新增数据字典对象

  /Sql错误写法：/
  ​
  Select cust_no from tb_cash_acct_info where status = ‘0‘
  ​
  /Sql正确写法：/
  ​
  Select cust_no from tb_cash_acct_info where status = :status

二十三、Mysql的表不允许使用自增主键

二十四、所有涉及到数据字典的SQL语句，不允许将数据字典写死固定

二十五、WHERE条件里要把能过滤掉最多数据的条件放在最前面（注，ORACLE放在最后面）

• 例如在交易流水表中查询某客户的购买交易信息，应该把客户代码的条件放在最优先执行的地方，可以让数据库先筛选出最少的数据来

/* MYSQL数据库示例，WHERE执行顺序为从前到后 */
​
SELECT trans_serno, cust_name, trans_date, trans_amt,......
​
FROM cust_trans_log 
​
WHERE cust_no='xxx' and busi_code='130'
​
​
/* ORACLE数据库示例，WHERE执行顺序为从后到前 */
​
SELECT trans_serno, cust_name, trans_date, trans_amt,......
​
FROM cust_trans_log 
​
WHERE busi_code='130' AND cust_no='xxx'

二十九、这是为了避免union产生的虚拟表进行筛选时，导致索引失效