• 幕客老师召集小伙伴
  • python自动化运维项目实战
  • nginx从入门到实战
  • 阿里云与Centos7实战

数据库编程规范

请开发人员重点关注前四部分内容

一、数据库设计

  • 表中字段尽量不要出现一个字段两种用处的字段,如:不要像wagerstore VARCHAR(8192)字段,单式时代表文件路径,复式时代表具体号码;
  • 任何表的设计均要考虑删除策略,表中数据不能无止境增长而不删除;
  • 表的设计一定要满足第一范式,尽量满足第二范式,基于性能提升可以考虑使用适当的小字段来增加冗余;
  • 选择数据类型时用VARCHAR类型,而不要用CHAR固定类型。用CHAR类型,如果有一个值小于固定长度,则可能潜在地出现问题。
    如:电话号码类型 telno VARCHAR(11), 如果一个号码是95555,只有5位,即使存在该值,必须额外的将剩下的6位填充成空格,才能获取该值。
  • 定义VARCHAR类型时,处于性能考虑,VARCHAR的长度定义应该尽量在业务合理范围内最短;
    如名字,可以定义VARCHAR(80),就不要定义成VARCHAR(255);
  • 数据库表中字段不要太多,不能超过20个;
  • 设计表时需要高度注意自增长字段,要评估是否值得,如果Mysql版本为5.0以下,插入字段会锁全表,会严重影响并发性;
  • 表中的主键不要太长,Mysql在非主键索引上面会附加主键字段,所以主键太长会导致非主键索引显得臃肿,占用很大空间,影响效率;
  • 创建表时考虑字段的默认值,VARCHAR类型可用NA表示;
  • 字段定义要明确指定是否可以为空,因为有些数据库中不指定表示可以为空,而另一些中不指定表示为空;如果不能确定是否为空,则默认指定NOT NULL;
  • 在表定义中将可能为NULL值的字段放在定义的最后,一条记录最后几列的数据都为空的话不会为这些列分配空间;
  • 添加表的时候要考虑表中记录的清理策略,一张表不能没有清除策略无限制地增长下去;
  • 设计完成,数据库定稿后,需要生成RTF文件保存和更新WIKI上的数据库文档。(使用我们统一的rtf模板生成);
  • 主键避免使用复合键;
  • 创建组合索引时,要注意组合索引的顺序和字段的选择性,把经常出现在WHERE条件中同时选择性比较好的字段放在复合索引的第一个位置。
  • 创建组合索引时,尽量将散列度最大的列放在最前面;
  • 设计要充分考虑效率和并发性,表结构确定下来,性能和并发性的数量级就确定了;
  • 为了提高系统的并发性,尽量不要在事务进行锁表或包含有锁表的操作;
  • 为了提高系统的并发性,尽可能使事务的时间最短;

二、效率以及并发性

  • 对于所有的SQL语句均要查看执行计划,在执行计划中一定要使用索引,若没有使用索引需要由DBA同意(这一点一定要注意);
  • 对于varchar长度超过255的,尽量避免建立索引,这样的索引比较臃肿,效率很差。如果不可避免建立索引,考虑为该列的前几位索引;
    如:

    c1 VARCHAR(255),如果该表中绝大部分长度是50,则
    CREATE INDEX idx_c1 ON t ( c1(50) );
  • 能确定建立唯一索引的时候不要建普通索引;
  • 复合索引中不要包含主键字段,因为主键字段本身就包含在复合索引中,没有必要重复包含;
  • 对于数据量比较大的表写SQL语句时,注意查看执行计划,避免全表扫描;
  • 尽量避免在视图中连接视图的情况,容易产生性能隐患;
  • 视图的连接尽量不要超过3个表,如果超过四个表要仔细分析执行计划;
  • 表间的连接用inner join时,要注意连接表和驱动表的顺序,原则是:小表驱动大表;
    示例一:

    表a有1万条记录,表b有100万条记录,
    不要
    FROM INNER JOIN ON a.id = b.id;
    而应该用
    FROM INNER JOIN ON a.id = b.id;
    来减少表间的连接次数,提高性能;
  • 在一个数据库表上如果要同时建立多个索引,用ALTER TABLE来一起建,而非逐个用CREATE INDEX单个建索引;
    示例一:

    CREATE INDEX idx_c1 ON t(c1);
    CREATE INDEX idx_c2 ON t(c2);
    CREATE INDEX idx_c3 ON t(c3);

    如下这条语句比上面三条效率要高:

    ALTER TABLE ADD INDEX (c1), ADD INDEX (c2), ADD INDEX (c3);
  • 建立表的SQL中一定要指定存储引擎,否则数据库会自己去选择默认存储引擎,而默认选择的有可能不是想要的存储引擎;
  • 有外键关联的表上,一定要在子表的连接键上面建立索引,以免发生数据库死锁;
  • 对于确定在一段时间内不会重复查询的结果,在SELECT中加SQL_NO_CACHE来保证不会加入查询缓存中;
  • 对于聚合函数,需要注意不要对值可能包含NULL的字段进行聚合,如t表中c1字段可以为空:

    SELECT COUNT(c1) FROM t;   这条SQL得不到正确结果,因为聚合函数不会处理NULL值。
    应该用如下语句:
    SELECT COUNT(*) FROM t; 或者
    SELECT COUNT(1) FROM t;
  • 建立触发器要慎重,除非出于严格的业务约束,不要轻易决定建立触发器;
  • 对于@变量,在使用前先给赋与初值,否则若使用脏值可能会出现怪异的错误;
  • 在where条件中涉及到OR操作的,需要将所有OR连接的条件都用一个括号括起来,以避免操作失误,
    如:

    FROM userinfo WHERE username = 'imoocc' AND city ='ShangHai' OR city='BeiJing';
    应该改成
     FROM userinfo WHERE username = 'imoocc' AND (city ='ShangHai' OR city='BeiJing');
  • 同时向一张表中插入多条数据时要拼凑成一条SQL来做,提高效率;
    如:

    INSERT INTO VALUES(...),(...),(...) ...;
  • 查询所有字段不要用星号*,应该将所有字段都列举出来,有*或未列出的列的SQL语句是导致系统升级失败的很大一个隐患;
  • 表的关联操作尽量不要操作4个,四个以上的关联表操作需要仔细查看执行计划和执行所需的时间;
  • 并发性高的事务关联操作不要超过两个,过多则容易产生性能隐患;
  • 一个存储过程的代码量行数不要超过100行,太长不易问题定位;
  • 存储过程中如果有动态SQL的情况一定要在数据库层面添加日志机制来记录数据库运行时的临时结果;
  • 存储过程中一定要考虑异常处理;
  • 在不使用DISTINCT、UNION、ORDER BY、GROUP BY情况下,也能实现业务功能的情况,
    一定不要使用这些功能。使用这些功能会导致对应的SQL语句排序,增加系统的开销;
    示例:

    错误的用法:
    SELECT COUNT(*)
      FROM (
            SELECT partid
                 FROM t_pub a
             WHERE a.partid >= '0127'
                 AND a.partID <= '1227'
             ORDER BY bgtime DESC   -- 没有用的ORDER BY
           )
  • 为了提高系统的并发性,尽可能的使事务的时间缩短。
  • 不要对VARCHAR(2000)之类的大字段值进行ORDER BY、DISTINCT、GROUP BY、UNION之类的操作;
  • 不要在varchar类型索引字段的判断开头用模糊匹配变量,导致索引失效;
    示例一:

    name上建立索引,但如下两条SQL均不能使用该索引
    WHERE name LIKE '_oko';
    WHERE name LIKE '%oko';
  • 禁止在生产系统,直接用navicate或SQL Manager之类的工具中直接调试存储过程;
  • 要选出该系统中的高并发表和大数据量表,对这些表上的所有操作进行自检和评估;
  • 当查询条件选择性很低时使用索引反而降低效率,这种情况下,应该用特殊的方法屏蔽该索引,如果字段为数值型的就在表达式的字段名后+ 0,为字符型的就并上空串;
    示例一:

    SELECT num_field
      FROM tablename
     WHERE num_field + 0 > 30
  • 在执行频度高,而又有排序的SQL上,应该加索引来避免排序;
  • SQL中如果有排序,而且不能再添加新索引的情况下,一定要保证查出来的记录数不能多于1万条;
  • 用case语句合并多次扫描:
    示例一:

    不要用如下两条
    SELECT COUNT(CASE c1<1000 THEN ELSE NULL END)
      FROM T; 
    SELECT COUNT(CASE c1 BETWEEN 1000 AND 2000 THEN NULL END)
      FROM T; 
    而要用
    SELECT COUNT(CASE c1<1000 THEN ELSE NULL END),
           COUNT(CASE c1 BETWEEN 1000 AND 2000 THEN NULL END)
      FROM T;
  • 业务允许逻辑下,尽量用UNION ALL 来代替UNION,以避免排序;
  • 对大批量(如几百万数据)的数据进行删除,要考虑使用特殊方式,如TRUNCATE,或
    CREATE TABLE AS ,然后DROP原表,然后重命名新表为原表的方式,
    不要用DELETE。因为事务太大,会导致性能急剧下降,而且很可能将日志撑满最后导致事务执行失败;
  • 不要在WHERE条件的左边用数学表达式或函数,会引起索引失效;

    WHERE column1/3 < 3000;
    WHERE ABS(column1) > 100;

    除非有意避免使用索引

  • 不要索引blob/text类型的字段,不要索引大型字段,这样作会让索引占用太多的存储空间;
  • 涉及到text类型字段的表,尽量不要使用text字段,如果必须使用,需要特别注意该SQL的写法
    如WagerInfo表中Remark字段为Text,

    SELECT `LotteryType` ,`LotteryNo`  FROM `WagerInfo`
    FORCE INDEX (Status)
    WHERE AND `Status` = 1
    AND Remark <> '' AND UserID > 0
    AND LotteryType= 'ToTo' LIMIT 1  ;

由于Where条件中涉及到对Remark字段的读操作,所以该SQL效率特别差,改为如下:

SELECT b.LotteryType,b.LotteryNo,Remark from
(
   SELECT WagerID , `LotteryType` ,`LotteryNo`  FROM `WagerInfo`
   Force INDEX (Status)
   WHERE `Status` = 1
   AND  UserID > 0 AND LotteryType= 'ToTo'
   GROUP BY `LotteryType` ,`LotteryNo`
) t
INNER Join WagerInfo b
ON t.WagerID = b.WagerID
WHERE b.Remark <> '' LIMIT 1;

该操作效率大大提高。

  • 不要索引常用的小型表。不要为小型数据表设置任何键,假如它们经常有插入和删除操作就更别这样作了。
    对这些插入和删除操作的索引维护可能比扫描表空间消耗更多的时间。

三、开发人员需要让DBA重点检查的SQL,包含以下任意一点均需要检查

  • 涉及到两个表以上(包括两表)的关联操作的SQL
  • 在执行计划中,key一列值为null
  • 执行计划中,rows一列超过200行
  • 在所使用的表中包含text,blob等大字段,如TP.Ticket TP.WagerInfo;
  • 涉及到大表的操作,对于关键型事务表,200万行以上的为大表。对于非关键表,800万以上为大表;
  • 要求性能和并发性特别高的SQL操作,如NewOkCom.NewProject NewOkCom.ProjectShare等;
  • 涉及到数据量有可能变化很大的表的SQL操作,如TP.Ticket表等;

四、数据库命名规范

  • 采用26个大小写英文字母和0-9这十个自然数,加上下划线'_'组成,共89个字符。不能出现其他字符(注释除外)。
  • 所有数据库保留字和系统函数都大写,除此之外,所有自定义的对象和变量均小写,附录中有一个UltraEdit中的wordfile文件,在*.sql文件中输入变蓝的均要大写;
  • 数据对象、变量的命名都采用英文字符,禁止使用中文命名。
  • 所有的命名不允许使用拼音,必须使用英文命名。
  • 较短的单词可通过去掉"元音"形成缩写;较长的单词可取单词的头几个字母形成缩写;一些单词有大家公认的缩写,请参考附录中的常用词缩写表。
    示例一:

    计数

    Count

    Cnt

    级别/层次

    Level

    Lvl

    金额

    Amount

    Amnt

    客户

    Customer

    Cust

    控制

    Control

    Ctrl

    联系

    Contact

    Cont

    漫游

    Roam

    Roam

    密码

    Password

    Pwd

    描述

    Description

    Desc

  • 绝对不要在对象名的字符之间留空格。

    `first namevarchar(50)  -- 不允许
  • 出于与其他数据库的命名限制兼容考虑,数据库名不要超过8个字符,表,索引,用户,存储过程,函数,触发器,表字段,存储过程局部变量,会话变量(@变量)名不要超过18个字符;
  • 注释中不要出现正斜线,反斜线特殊字段,在作为脚本导入数据库时可能会报错,出现问题很难定位;
  • 保持字段名和类型在多个表之间的一致性。假如某个字段在一个表里是整数,那在另一个表里可就别变成字符型了;
  • 小心保留词,要保证你的字段名没有和保留词、数据库系统或者常用访问方法冲突,参考附录保留字表;
  • 同一个数据库中不要为所有的表名统一前缀和后缀,表中字段不要添加代表该字段数据类型的前缀或后缀;

    示例一:
    如在Ok数据库中,不要在所有数据表前没有任何区分的加一样的前缀和后缀
    ok_ticket    -- 不允许
    ok_project   -- 不允许
     
    示例二:在字段前不要加表示数据类型的前缀或后缀
    create table project
    (
        dbl_money double,    -- 不允许
        var_name varchar(20) -- 不允许
        ...
    )
  • 索引以idx_为前缀,后接字段名,如果字段名过长,则用字段名的缩写表示;

    idx_projct_typ 表示在方案类型上建一个索引
  • 唯一索引以uidx_为前缀,后接字段名,如果表名或字段名过长,则用字段名的缩写表示;

    uidx_name_dptid 表示在名字和部门ID上建立一个复合的唯一索引
  • 主键以pk_为前缀,后接表名,如果表名过长,则用表名的缩写表示;

    pk_projctid 表示在方案ID上建主键索引
  • 外键以fk_为前缀,后接连接的字段名再加两个表名,如果表名和字段名过长,则用缩写表示;
    示例一:

    fk_dptid_emp_dpt 
    表示在employee员工表的departid上建立一个外键,该外键连接department部门表的departid
  • 触发器以tr_为前缀,后接表名,最后以_i(插入insert触发器), _u(修改update触发器),_d(删除delete触发器);
    示例一:

    tr_user_i          (当用户表发生insert操作时的触发)
    tr_user_u          (当用户表发生update操作时的触发)
    tr_user_d          (当用户表发生delete操作时的触发)
  • 存储过程以p_为前缀,后接一组动名词,以下划线连接, 表示该存储过程的意图;
    示例一:

    p_ins_projct 表示,该存储过程插入一条project记录
  • 函数以f_为前缀,后接要做的运算,注意函数只做逻辑运算,不要对表中数据做修改操作,存储过程可以对表做修改操作;
    示例一:

    f_sin            该函数表示求正弦sin
    f_emp_salry_sum  表示获取employee表中所有员工的工资总和
  • 视图以v_为前缀,后接连接的表,如果表名过长,则用表名的缩写表示;

    v_emp_dpt 表示该视图涉及到employee员工表和department部门表
  • 游标以c_为前缀;
  • 存储过程传入参数以i_为前缀,传出参数以o_为前缀,既传入又传出参数以io_为前缀;
  • 本地变量以l_为前缀,用SELECT ..INTO给变量赋值时,本地变量以l_加对应的字段名命名;
    示例一:

    SELECT name, age INTO l_name,l_age FROM ...;
  • CONTINUE异常句柄变量Handler,应该以ch_为前缀,UNDO HANDLER以uh_为前缀,EXIT HANDLER以eh为前缀;
    示例一:

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET ch_done = 1;
  • 用正确的反义词组命名具有互斥意义的变量或相反动作的函数等
    示例一:

    start

    finish

    启动

    完成

    first

    last

    第一个

    最后一个

    prior

    next

    前一个

    后一个

    add

    remove

    增加

    删除

    create

    destroy

    创建

    析构

    get

    set

    获取

    设置

    increment

    decrement

    增量

    减量

    lock

    unlock

    加锁

    解锁

    open

    close

    打开

    关闭

  • 对于变量命名,禁止取单个字符(如i、j、k...),建议除了要有具体含义外,还能表明其变量类型、数据类型等。形如i、j、k的变量只允许作为局部循环变量。

说明:变量,尤其是局部变量,如果用单个字符表示,很容易敲错(如i写成j),而编译时又检查不出来,有可能为了这个小小的错误而花费大量的查错时间。

  • 在编写数据库脚本时,将创建表的脚本和创建存储过程、函数的脚本分开,在脚本的开始集中对该脚本中创建的对象做清理工作。
    示例一:

    01_crt_table.sql --创建数据库表的脚本
    02_crt_procs.sql --创建存储过程的脚本
    03_init_data.sql --添加初始数据的脚本

五、注释以及代码风格

  • 程序块要采用缩进风格编写,缩进的空格数为4个。
  • 对齐只使用空格键,不使用TAB键。
  • 较长SQL语句(>80字符)要分成多行书写
  • 不允许把多个短语句写在一行中,即一行只写一条语句。
  • 存储过程中参数属性的选择,对于OUT和INOUT参数,需要做特殊说明用意;
  • 要对函数头、存储过程头,脚本头,表头进行注释,注释格式如下,在附录中会有UltraEdit中生成相应注释的工具;
    /**********************************************************************
  • FUNCTION : 函数、存储过程、脚本、表的功能注释
  • Params : 对其中参数的说明,没有参数的省略这一步
  • Author : 修改人
  • CREATE : 创建时间
    **********************************************************************/
  • 对于表中的每个字段,要说明其意义,使用单行注释;
    示例一:

    CREATE TABLE employee
    (
        id    INT PRIMARY KEY,                 -- 员工ID,主键
        name  VARCHAR(50) NOT NULL DEFAULT 'NA'-- 员工姓名
        ...
     ) ENGINE = INNODB;
  • 命名中若使用特殊约定或缩写,则要有注释说明;
  • 边写代码边注释,修改代码同时修改相应的注释,以保证注释与代码的一致性。不再有用的注释要删除。
  • 注释的内容要清楚、明了,含义准确,防止注释二义性。
  • 注释要注明修改人和修改时间,这样别人看代码的时候有不懂的可以直接找到开发人员;

数据库编程规范

Pingbacks已打开。

引用地址

暂无评论

发表评论