4.4.1. 基本规范
-
规则1: 避免使用存储过程、触发器、函数等，容易将业务逻辑和DB耦合在一起，并且MySQL的存储过程、触发器、函数中存在一定的BUG；
规则2: 禁止进行字段数据类型的隐式转换，所有转换必须进行明确的数据类型转换；
说明：隐式转换会导致字段上的索引失效， 最为常见的隐式类型转换常见于时间类型与字符串类型之间，建议所有时间类型字段在myBatis中均以时间类型传入，或者以字符串传入然后通过时间函数转换字符串为合法的时间格式 ，如下： - 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值