开发者社区> 小麦苗> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

SQL的开发建议(MySQL和Oracle)

简介: MYSQL 开发建议 关于建表 1、尽量使用INNODB存储引擎。 2、建议使用UNSIGNED存储非负数值。 3、建议使用INT UNSIGNED存储IPV4。
+关注继续查看

MYSQL 开发建议

关于建表

1、尽量使用INNODB存储引擎。

2、建议使用UNSIGNED存储非负数值。

3、建议使用INT UNSIGNED存储IPV4

4、强烈建议使用TINYINT来代替ENUM类型。

5、使用VARBINARY存储大小写敏感的变长字符串或二进制内容。

7、区分使用DATETIMETIMESTAMP。存储年使用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代替ORSQL语句中IN包含的值不应过多。

4、用UNION ALL代替UNIONUNION 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 NULLIS 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 ( 如果有可能的话)

4Order By语句加在索引列,最好是主键PK上。

SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE(低效)

SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_CODE (高效)

5、避免使用耗费资源的操作:

带有DISTINCT,UNION,MINUS,INTERSECTSQL语句会启动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子句中的连接顺序。合理选择驱动表。

9SELECT子句中避免使用 *ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间 。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Oracle SQL优化之多表连接
Oracle SQL优化之多表连接
102 0
ORACLE SQL优化之ORA-03150&ORA-02055&ORA-02063
                                                                                                             >   
4494 0
好书推荐—《基于Oracle的SQL优化》
这是一本全书都在讲SQL性能优化的书。 这是国内目前SQL优化最详尽的一本书。 读了这本书,你会发现,SQL优化其实不是感觉的那么难。 这本书挺厚,800多页,对比它的价格,100多人民币,物有所值。
822 0
看了此文,Oracle SQL优化文章不必再看!
  第一章 看了此文,Oracle SQL优化文章不必再看! DBAplus社群 | 2015-11-17 23:44 目录SQL优化的本质 SQL优化Road Map 2.
1257 0
读书笔记-《基于Oracle的SQL优化》-第一章-3
优化器: 1、优化器的模式: 用于决定在Oracle中解析目标SQL时所用优化器的类型,以及决定当使用CBO时计算成本值的侧重点。这里的“侧重点”是指当使用CBO来计算目标SQL各条执行路径的成本值时,计算成本值的方法会随着优化器模式的不同而不同。
839 0
读书笔记-《基于Oracle的SQL优化》-第一章-2
CBO优化器的基本概念: 可传递性: 1、简单谓词传递 t1.c1=t2.c1 and t1.c1=10,Oracle会自动将t2.c1=10的条件添加。
679 0
读书笔记-《基于Oracle的SQL优化》-第一章-1
开始学习崔老师的《基于Oracle的SQL优化》,七百多页,虽然可能会比较痛苦,但想必是一个痛并快乐的过程,尽情享受了。。。 第一章:Oracle里的优化器 优化器是Oracle数据库中内置的一个核心子系统,可以理解为一个核心模块或者一个核心功能组件。
823 0
ORACLE-SQL优化
Oracle的优化器共有两种的优化方式,即: 基于规则的优化方式(Rule-Based Optimization,RBO) 基于代价的优化方式(Cost-Based Optimization,CBO)      RBO方式:优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。
1182 0
Oracle 数据库性能优化3日实战(企业培训)
课程名称一: Oracle性能优化及调整 课程时长 1天 课程深度: 高级 上机实验: 10%-30% 授课对象: Oracle开发人员、Oracle数据库管理人员,应用程序开发人员 课程描述: 本课程讲述Oracle数据库物理层规划,系统性能的监控,数据库性能参数调整,统计信息的收集,使用自动化调试工具优化数据库,I/O子系统的配置与设计以及性能优化方法论等。
1888 0
+关注
小麦苗
小麦苗,专注于数据库,Oracle OCM,PostgreSQL PGCM,PostgreSQL ACE,中国PG分会官方认证讲师,PGfans签约作者,PGfans年度MVP;微信公众号: DB宝,个人网站:www.xmmup.com
文章
问答
文章排行榜
最热
最新
相关电子书
更多
Oracle 至PostgreSQL案例分享
立即下载
PostgresChina2018_刘成伟_oracle到Postgres数据库迁移工具
立即下载
Oracle 和 MySQL 性能优化感悟
立即下载