SQL的开发建议（MySQL和Oracle）
MYSQL 开发建议
关于建表
1、尽量使用INNODB存储引擎。
2、建议使用UNSIGNED存储非负数值。
3、建议使用INT UNSIGNED存储IPV4。
4、强烈建议使用TINYINT来代替ENUM类型。
5、使用VARBINARY存储大小写敏感的变长字符串或二进制内容。
7、区分使用DATETIME和TIMESTAMP。存储年使用YEAR类型。存储日期使用DATE类型。 存储时间(精确到秒)建议使用TIMESTAMP类型。
8、将大字段、访问频率低的字段拆分到单独的表中存储,分离冷热数据。
9、禁止在数据库表中存储明文密码。
10.表必须有主键,推荐使用UNSIGNED自增列作为主键。
11、表字符集使用UTF8,必要时可申请使用UTF8MB4字符集。
a)UTF8字符集存储汉字占用3个字节,存储英文字符占用一个字节。
b)UTF8统一而且通用,不会出现转码出现乱码风险。
c)如果遇到EMOJ等表情符号的存储需求,可申请使用UTF8MB4字符集。
12、采用合适的分库分表策略。例如千库十表、十库百表等。
关于索引
1、禁止冗余索引。
2、禁止重复索引。
3、不在低基数列上建立索引,例如“性别”。
4、合理使用覆盖索引减少IO,避免排序。
关于SQL
1、 不管数据库隔离级别是什么状态或者事务大小，养成COMMIT习惯，避免事务锁的长期持有。
2、 更新(update)sql语句尽量使用主键条件
3、用IN代替OR。SQL语句中IN包含的值不应过多。
4、用UNION ALL代替UNION。UNION ALL不需要对结果集再进行排序。
5、尽量不使用order by rand()。
6、建议使用合理的分页方式以提高分页效率。
7、SELECT只获取必要的字段,尽量少使用SELECT *。
8、SQL中避免出现now()、rand()、sysdate()、current_user()等不确定结果的函数。
9、减少与数据库交互次数,尽量采用批量SQL语句。
使用下面的语句来减少和db的交互次数:
a)INSERT ... ON DUPLICATE KEY UPDATE
b)REPLACE INTO
c)INSERT IGNORE
d)INSERT INTO VALUES()
10、拆分复杂SQL为多个小SQL,避免大事务。
11、对同一个表的多次alter操作必须合并为一次操作。
Oracle 开发建议
使用索引需要注意的地方：
1、避免在索引列上使用NOT，
2、避免在索引列上使用计算．
低效:SELECT … FROM DEPT WHERE SAL * 12 > 25000;
高效:SELECT … FROM DEPT WHERE SAL > 25000/12;
3、避免在索引列上使用IS NULL和IS NOT NULL
低效:(索引失效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;
高效:(索引有效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;
4、避免改变索引列的类型.
关于SQL
1、用EXISTS替换DISTINCT：
(低效): SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E
WHERE D.DEPT_NO = E.DEPT_NO
And E.sex =man
(高效): SELECT DEPT_NO,DEPT_NAME FROM DEPT D
WHERE EXISTS
( SELECT ‘X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO
And E.sex =man
);
2、用(UNION)UNION ALL替换OR (适用于索引列)
高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION ALL
SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE”
低效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
3、用UNION-ALL 替换UNION ( 如果有可能的话)。
4、Order By语句加在索引列，最好是主键PK上。
SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE（低效）
SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_CODE （高效）
5、避免使用耗费资源的操作:
带有DISTINCT,UNION,MINUS,INTERSECT的SQL语句会启动SQL引擎 执行耗费资源的排序(SORT)功能.
6、使用Where替代Having（如果可以的话）
低效:
SELECT JOB , AVG(SAL)
FROM EMP GROUP JOB HAVING JOB = ‘PRESIDENT'AND AVG(SAL)>XXX
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER' GROUP JOB Having AND AVG(SAL)>XXX
7、通常来说，如果语句能够避免子查询的使用，就尽量不用子查询。因为子查询的开销是相当昂贵的。具体的例子在后面的案例“一条SQL的优化过程”中。
8、注意WHERE子句中的连接顺序。合理选择驱动表。
9、SELECT子句中避免使用 *。ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间 。