目录
十五、注意NULL值的判断不要使用等号,只能使用IS NULL或IS NOT NULL
十六、查询记录数使用SELECT COUNT(1)/COUNT(*),而不要使用COUNT(字段)这样的写法
二十四、所有涉及到数据字典的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产生的虚拟表进行筛选时,导致索引失效