Mysql数据库开发规范

4.4.1. 基本规范

  1.  
    规则1:   避免使用存储过程、触发器、函数等,容易将业务逻辑和DB耦合在一起,并且MySQL的存储过程、触发器、函数中存在一定的BUG;
    规则2:   禁止进行字段数据类型的隐式转换,所有转换必须进行明确的数据类型转换;
    说明:隐式转换会导致字段上的索引失效, 最为常见的隐式类型转换常见于时间类型与字符串类型之间,建议所有时间类型字段在myBatis中均以时间类型传入,或者以字符串传入然后通过时间函数转换字符串为合法的时间格式 ,如下:    
  2. SELECT name
    FROM member
    WHERE vgmt_create=DATE_FORMATE('2009010101:02:03','%Y-%m-%d %H:%i:%s');  
    规则3:   禁止在多表关联的时候,在非索引字段上的关联;
    规则4:   进行模糊查询时,禁止条件中字符串直接以"%"开头;
    规则5:   在使用for update子句时一定注意限制条件,避免锁定全表或者不需要被锁定的行记录。如无必要锁定数据则应避免使用for update;
    规则6:   在进行结果集合并(union或union all)时, 如不需要进行结果去重,则必须使用union all,而不能使用union;且尽量减少进行数据集的去重;
    规则7:   除非必要,避免使用 != 等非等值操作符,会导致用不到索引;
    规则8:   禁止在 WHERE 条件中出现的过滤字段上使用任何函数进行类型或格式的转换;正确的做法是把传入的值转换为列类型所需要的;
    错误的写法:   
    SELECT username
    FROM gl_user
    WHERE DATE_FORMAT(gmt_create, %Y%m%d%H%i%s')='20090501022300';   
     
    正确的写法:   
    SELECT username
    FROM gl_user
    WHERE gmt_create=DATE_FORMAT('20090501022300', '%Y-%m-%d %H:%i:s');   
     
    规则9:   禁止使用order by rand();
    order by rand() 会将数据从磁盘中读取,进行排序,会消耗大量的IO和CPU。
    规则10:避免大使用大SQL,可以拆分成多个小SQL来替代;

4.4.2. 绑定变量使用规范

  • 规则1: 应用端所有查询的 where 条件中的变量,都需要使用绑定变量来实现,以防SQL注入,同时性能也会更优;   
    规则2: 在 myBatis  SqlMap 文件中绑定变量使用 "#{var_name}"表示,替代变量使用"${var_name}";所有需要动态 Order By 条件的查询,在使用替代变量过程中,需要将可能传入的内容以枚举类写死在代码中,禁止接受任何外部传入内容;对于不变的常量条件,请使用常量而不是变量;    
    规则3: IN子句,使用"Iterate + 数组类型变量"的方式实现绑定变量而不是通过代码拼接 Query 语句;
    例如:    
        myBatis会生成user_level in (1,2,3,4,5 ...)的语句      
     

4.4.3. 分页规范

  • 假如有类似下面分页语句:
    SELECT * FROM table ORDER BY create_time DESC LIMIT 10000,10;
    这种分页方式会导致大量的IO,因为MySQL使用的是提前读取策略。
    推荐分页方式:
    SELECT * FROM table WEHRE create_time < last_time ORDER BY create_time DESC LIMIT 10;
    SELECT * FROM table INNER JOIN (SELECT id FROM table ORDER BY create_time  LIMIT 10000,10) AS t USING(id);
     

4.4.4. 建议

  • 建议1:   尽量使用if来简化SQL访问数据库的次数;
    示例:
    如下两个语句实现的功能
    SELECT  COUNT , SUM(salary)
    FROM employees
    WHERE department_id = 20
    AND first_name LIKE 'SMITH%';
       
    SELECT COUNT , SUM(salary)
    FROM employees
    WHERE  department_id = 30
    AND first_name LIKE 'SMITH%';
    可以使用IF改写为如下语句
    SELECT COUNT(IF(department_id=20, '*', NULL)) d20_count,
                  COUNT(IF(department_id=30, '*', NULL)) d30_count,
                  SUM(IF(department_id=20, salary, NULL)) d20_sal,
                 SUM(IF(department_id=30, salary, NULL)) d30_sal
    FROM employees
    WHERE first_name LIKE 'SMITH%';
    建议2:   避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销;
    示例
    如下语句使用的是HAVING子句
    SELECT last_name, avg(salary)
    FROM employees
    GROUP BY last_name
    HAVING last_name != 'Grant'
    AND last_name !=  'Fay'
     
    改写使用WHERE的语句如下
    SELECT last_name, avg(salary)
    FROM employees
    WHERE last_name != 'Grant'
    AND last_name != 'Fay'
    GROUP BY last_name
    建议3:   尽量少用not exist/no in等反向写法。如果一定要用时,尽量选择not exist;
    建议4:   尽量少用is null/is not null等null的处理;
    建议5:   SQL语句中IN子句里的值不应超过300;
    建议6:   对于大表查询中的列项应尽量避免进行诸如CAST()或CONVERT()的转换;
    建议7:   尽量避免进行全表扫描,限制条件尽可能多,以便更快搜索到要查询的数据;
    建议8:   SELECT查询语句建议增加limit 1000 限定返回的行数;
    建议9:   进行数据库结构设计的时候,考虑适当的冗余,尽量确保应用读写数据的SQL简洁;
    建议10:  要返回MySQL自增序列的ID值,可以考虑使用函数LAST_INSERT_ID(),此函数只能返回同
    一个SESSION最近一次对有AUTO_INCREMENT属性表INSERT的ID值

版权属于: sbboke版权所有。

转载时必须以链接形式注明作者和原始出处及本声明。

张贴在数据库相关标签:

相关文章

0 0 投票数
文章评分
订阅评论
提醒
0 评论
内联反馈
查看所有评论