mysql 知识库数据库我爱编程

数据库设计及开发规范，sql性能优化

2018-04-02  本文已影响233人  画风故事汇

1 引言

1.1 编写目的

本文档用于规范数据库设计、开发等方面的内容。

1.2 预期读者

本文档的预期读者为本项目组全体成员，以及其他与项目有关的管理人员。

1.3 术语定义

OMP：Operation Management Platform

1.4 参考文档

《数据库编码规范.pdf》

2 数据库规范

2.1 设计规范

2.1.1 命名规范

数据库对象的命名规则的范围为管理平台设计开发所涉及的表，对于其他外部系统所创建的表不在本规范约束范围内，
数据库对象如表、列、序列、过程、函数等在命名时要遵循如下规则：

图例-1.png

2.1.2 表规范

2.1.2.1 建表的参数设计

2.1.2.2 主外键设计

2.1.2.3 列设计

2.1.2.4 临时表

2.1.3 索引规范

分区规则图

2.1.4 存储过程、函数、包规范

2.1.5 别名

2.1.6 Database Link 别名

开发规范

2.2.1 变量命名规范

变量的命名体现其作用域和数据类型，规则如下：

2.2.2 SQL开发规范

2.2.2.1 SQL书写规范

BEGIN
  FOR l_count IN 1..10 LOOP
    x_result := x_result + l_count;
  END LOOP;
END;


CONSTRAINT G_MAX_VALUE NUMBER :=10;
...
  IF(1_value > G_MAX_VALUE)
  THEN
    ...

2.2.2.2 索引与分区使用规范

2.2.2.3 SELECT列和WHERE条件规范

2.2.2.4 多表连接规范

#低效:
SELECT DISTINCT DEPT_NO,DEPT_NAME
    FROM DEPT D,EMP E
    WHERE D.DEPT_NO = E.DEPT_NO

#高效:
SELECT DEPT_NO,DEPT_NAME
    FROM DEPT D
    WHERE EXISTS ( SELECT 'X'
                    FROM EMP E
                    WHERE E.DEPT_NO = D.DEPT_NO);

2.2.2.5 嵌套查询规范

#例如:
SELECT …
FROM EMP
WHERE DEPT_NO NOT IN (SELECT DEPT_NO 
                         FROM DEPT 
                         WHERE DEPT_CAT='A');
#为了提高效率,改写成:
SELECT ….
FROM EMP E
WHERE NOT EXISTS (SELECT 'X'
                    FROM DEPT D
                    WHERE D.DEPT_NO = E.DEPT_NO
                    AND DEPT_CAT = 'A');

2.2.3 PL/SQL开发规范

2.2.3.1 包规范

2.2.3.2 游标规范

2.2.3.3 事务处理规范

2.2.3.4 数据封装规范

2.2.3.5 数据访问规范

2.2.3.6 日志书写规范

2.2.3.7 错误处理规范

2.2.3.8 书写规范

2.2.3.9 书写优化性能建议

2.2.3.10 其他经验性规则

#错误写法
SELECT ......
  FROM emp
  WHERE dept_no NOT IN ( SELECT dept_no  
            FROM dept  
            WHERE dept_cat='A');


#正确写法
SELECT ......  
    FROM emp e  
    WHERE NOT EXISTS ( SELECT 'X'  
    FROM dept  
    WHERE dept_no=e.dept_no  
    AND dept_cat='A');


#错误写法
SELECT ......  
    FROM emp  
    WHERE EXISTS ( SELECT 'X'  
    FROM dept  
    WHERE dept_no=e.dept_no  
    AND dept_cat='A');


#正确写法
SELECT ......  
    FROM emp e,dept d  
    WHERE e.dept_no=d.dept_no  
    AND dept_cat='A';


#错误写法
SELECT DISTINCT d.dept_code,d.dept_name  
    FROM dept d ,emp e  
    WHERE e.dept_code=d.dept_code;



#正确写法
SELECT dept_code,dept_name  
    FROM dept d  
    WHERE EXISTS ( SELECT 'X'  
    FROM emp e  
    WHERE e.dept_code=d.dept_code);

3 拓展

3.1 Oracle 与 Mysql之间部分函数和语法对比

3.1.1 UUID生成

SELECT sys_guid() FROM dual

SYS_GUID(),是Oracle 8i 后提供的函数。SYS_GUID产生并返回一个全球唯一的标识符(原始值)由16个字节组成,在Oracle 9i和Oracle 10g生成的是32个字节。在大多数平台,生成的标识符由主机标符，执行函数的进程或者线程标识符，和进程或线程的一个非重复的值(字节序列)组成。
可以用来生成唯一标识ID；

SELECT uuid() FROM dual

MySQL 实现了 UUID，并且提供 UUID() 函数方便用户生成 UUID。在 MySQL 的 UUID() 函数中，前三组数字从时间戳中生成，第四组数字暂时保持时间戳的唯一性，第五组数字是一个 IEEE 802 节点标点值，保证空间唯一。使用 UUID() 函数，可以生成时间、空间上都独一无二的值。据说只要是使用了 UUID，都不可能看到两个重复的 UUID 值。当然，这个只是在理论情况下。

比较：本质上都是方便用户生成随机的唯一索引，SYS_GUID()生成的是32位的字节;uuid()生成的则是带4根- 的36位的字节。

3.1.2 表之间左右连接使用

在关系型数据库中，左连接使用 LEFT JOIN ……ON……,右连接使用 RIGHT JOIN ……ON……

SELECT t1.* FROM TABLE1 t1 LEFT JOIN TABLE2 t2 ON t1.id = t2.id

SELECT t2.* FROM TABLE1 t1 RIGHT JOIN TABLE2 t2 ON t1.id = t2.id

但是在Oracle中却有一种简化写法，采用 += 、=+ 来替代左连接和右连接，写法如下

SELECT t1.* FROM TABLE1 t1 , TABLE2 t2 t1.id += t2.id

SELECT t2.* FROM TABLE1 t1 , TABLE2 t2 t1.id =+ t2.id

3.1.3 判断并替换null函数

在Mysql中判断null函数并且替换null的函数是ifnull(exp1,result)

SELECT ifnull(t1.user_name,'张三') as user_name FROM TABLE1 t1

在Oracle中判断null函数并且替换null的函数是nvl(exp1,result)

3.1.4 条件语句(函数)

在Mysql中条件判断语句可以使用 IF(exp1,exp2,exp3)函数，缺陷是只能判断单重条件

SELECT 
  IF(t1.user_name='',"zhang san",t1.user_name) as user_name
FROM TABLE1 t1

在Oracle中条件判断语句可以使用 decode(exp1,result1,exp2,result2,result3)函数,可支持多重判断

SELECT 
  decode(t1.user_name='',"zhang san",t1.user_name is null,'li si',t1.user_name) as user_name
FROM TABLE1 t1

对于比较复杂的多重条件判断，推荐使用通用的 CASE WHEN 条件 THEN 结果 WHEN 条件 THEN 结果 ELSE 结果 END 这种形式

SELECT 
  CASE WHEN t1.user_name=''THEN 
  "zhang san"
  WHEN t1.user_name is null THEN
  "li si"
  ELSE t1.user_name END as user_name
FROM TABLE1 t1

3.1.4 字符串拼接

字符串拼接是sql中常用到，在Mysql中提供了concat(str1,str2,…strn)和concat_ws(separator,str1,str2,...)

#无添加任何字符拼接
SELECT concat(column1,column2,……) as column_sttr FROM TABLE1;

#各个字符串之间以'-'拼接
SELECT concat_ws('-',column1,column2,……) as column_sttr FROM TABLE1;

在Oracle中，字符拼接是以 "||" 方式,此种方式显得更灵活点

SELECT column1 ||column2 || column3 as column_sttr FROM TABLE1;
上一篇下一篇

猜你喜欢

热点阅读