MySQL开发规范与实用技术交流(转)

原文地址：http://www.nuxnu.com/2011/07/2/MySQL%E5%BC%80%E5%8F%91%E8%A7%84%E8%8C%83%E4%B8%8E%E5%AE%9E%E7%94%A8%E6%8A%80%E6%9C%AF%E4%BA%A4%E6%B5%81.html

内 容

1.开发实用技术
2.MySQL开发规范
3.项目支持
4.变更管理
5.SQL REVIEW
6.开发测试服务器说明
7.MySQL相关项目建议
8.简述InnoDB引擎锁与索引

1.开发实用技术
1.1 自增字段定义
1.2 CHAR(N)或VARCHAR(N)中的N解释
1.3 字符串函数
1.4 日期操作函数
1.5 类型转换函数

1.1 自增字段定义

自增字段类型必须是整型，推荐类型为INT或者BIGINT类型。并且自增字段必须是主键或主键的一部分。

1.2 CHAR(N)或VARCHAR(N)中的N解释

MySQL中此两类字符串定义时候填写的长度N，不是字节数的意思 ，而是字符数的意思。
我们MySQL所有数据库的字符集都为UTF8，字符集校对规则为UTF8_general_ci。对于中文汉字，实际存储的时候占三个字节，而数据或字母，则只占一个字节。例如：

CREATE TABEL gl_user(username VARCHAR(40));

则username最多能存储40个字符 。

1.3 字符串函数
MySQL中字符串连接方法，使用CONCAT() 或CONCAT_ WS()函数，语法如下：

CONCAT(string1,string2,...)
CONCAT_ WS(separator,string1,string2,..)

字符串长度统计：

LENGTH(string)                    #返回string所占的字节数
CHAR_LENGTH(string)               #返回string中的字符个数

统计字符个数，就不区分是汉字还是字母或数字，也跟字符集没有关系，若统计的是字节数，则由字符是汉字、字母或数字类型，以及字符集共同决定。
请各位牢记：我们所有的MySQL数据库都将会采用UTF8编码，所以一个汉字占3个字节，一个字母或数字占一个字节。

1.4 日期操作函数

获取当前时间：NOW()，CURDATE()、CURTIME()
其中
NOW() 函数精确到秒，格式：YYYY-MM-DD HH:MM:SS
CURDATE() 函数精确到天，格式：YYYY-MM-DD
CURTIME() 函数精确到秒，格式：HH:MM:SS

日期数值的加减函数：

DATE_ADD(date,INTERVAL expr type)
DATE_ SUB(date,INTERVAL expr type)

常用的几种type类型：YEAR、MONTH、DAY、HOUR、MINUTE，其中expr可以为正数或负数，我们在开过程中，一般使用DATE_ADD()函数，若要作日期减去一个数字的方式，就使用负数。

DATEDIFF(expr1,expr2)，是返回 开始日期expr1与 结束日期expr2之间，相差的天数 ，返回值为正数或负数。

返回日期某部分信息的函数：
YEAR(expr1) 返回日期expr1部分的年份； MONTH(expr1) 返回日期expr1部分的月份；DAY(expr1)返回expr1部分的天数；
WEEKDAY(expr1) 返回expr1对应的星期数字

1.5 类型转换函数
字符串转换成日期方式，DATE_FORMAT()或STR_TO_DATE()，
两个函数的格式如下：

DATE_FORMAT(expr1,format)
STR_TO_DATE(expr1, format)

常用的日期格式YYYY-MM-DD HH:MM:SS 对应的format为

%Y-%m-%d %H:%i:%S

通用的类型转换函数：

CAST(expr AS type)
CONVERT(expr,type)
CONVERT(expr USING transcoding_name)

2.MySQL开发规范
2.1 字段定义规范
2.2 绑定变量和替代变量使用规范
2.3 数据类型转换规范
2.4 SELECT * 的使用规范
2.5 字段上添加函数使用规范
2.6 表连接规范
2.7 分页查询规范
2.8 特殊操作符使用规范
2.9 特殊函数使用规范

2.1 字段定义规范
MySQL中用到的相关列数据类型存储需求与范围描述信息如下表

列类型	表达的范围	存储需求
TINYINT[(M)] [UNSIGNED] [ZEROFILL]	-128 到 127 或 0 到 255	1 个字节
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]	-32768 到 32767 或 0 到 65535	2 个字节
INT[(M)] [UNSIGNED] [ZEROFILL]	-2147483648 到 2147483647 或 0 到 4294967295	4 个字节
BIGINT[(M)] [UNSIGNED] [ZEROFILL]	-9223372036854775808 到 9223372036854775807  或 0 到 18446744073709551615	8 个字节
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]	整数最大位数（ M ）为 65 ，小数位数最大（ D ）为 30	变长
DATE	YYYY-MM-DD	3 个字节
DATETIME	YYYY-MM-DD HH:MM:SS(1001 年到 9999 年的范围 )	8 个字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS （ 1970 年到 2037 年的范围）	4 个字节
CHAR(M)	0<M<=255（ 建议 CHAR(1) 外，超过此长度的用 VARCHAR）	M 个字符（所占空间跟字符集等有关系）
VARCHAR(M)	0<M<65532/N	M 个字符（ N 大小由字符集，以及是否为中文还是字母数字等 有关系）
TEXT	64K 个字符	所占空间跟字符集等有关系

详细说明：
1. 所有动态长度字符串全部使用 VARCHAR 类型，类似于状态，有限类别的字段， 也使用可以比较明显表示出实际意义的字符串，而不应该使用INT之类的数字来代替；
2. 固定长度的字符串使用 CHAR 类型，所有单个字符的全部使用 CHAR 类型，而不应该使用VARCHAR 类型；
3. 仅仅当字符数量可能超过 20000 个的时候，可以使用 TEXT 类型来存放字符类数据。所有使用 TEXT 类型的字段必须和原表进行分拆，与原表主键单独组成另外一个表进行存放；
4. 需要精确到时间（年月日时分秒）的字段可以使用DATETIME 或TIMESTAMP，但请注意各自能表达的范围，以及是否需要用到TIMESTAMP的特性；
5. 所有只需要精确到天的字段全部使用 DATE 类型，而不应该使用 TIMESTAMP或者DATETIME 类型；
6. 自增序列类型的字段只能使用 INT 或者 BIGINT，且明确标识出为无符号型(UNSIGNED)，除非确实会出现负数，仅当该字段数字取值会超过42亿，才使用 BIGINT 类型；

2.2 绑定变量和替代变量使用规范

基本原则：
1. 所有 Query 的 Where 条件中的变量，都需要使用绑定变量来实现，此要求并不完全是基于性能的考虑，更多是基于安全方面的考虑，如若有任何不使用绑定变量的需求，都必须通过安全部门的审核并征得同意。详细说明：
2. 在 iBatis 的 SqlMap 文件中绑定变量使用 "#var_name#"表示，替代变量使用"$var_name$"；所有需要动态 Order By 条件的 Query，在使用替代变量过程中，需要将可能传入的内容以枚举类写死在代码中，禁止接受任何外部传入内容；对于不变的常量条件，请使用常量而不是变量；
3. IN子句，使用"Iterate + 数组类型变量"的方式实现绑定变量而不是通过代码拼接 Query 语句，例如：

<isNotEmpty prepend="and" property="userIds">

<iterate property="userIds" open="t.user_id in (" close=")" conjunction=",">

#userIds[]#

</iterate>

</isNotEmpty>

iBatis会生成t.user_id in (1,2,3,4,5 ...)的语句

2.3 数据类型转换规范

基本原则：
在所有 Query 的 Where 条件中必须使用和过滤字段完全一致的数据类型，杜绝任何隐式类型转换，避免造成因为数据类型不匹配而导致 Query 执行计划的出错，造成性能问题.

详细说明：
1) 所有 Where 条件的字段上不允许使用函数做类型转换，如有需要转换类型，只能转换过滤值,而不是转换字段.
2) 最为常见的隐式类型转换常见于时间类型与字符串类型之间，建议所有时间类型字段在iBatis中均以时间类型传入，或者以字符串传入然后通过时间函数转换字符串为合法的时间格式 ，如下：

SELECT * FROM member WHERE gmt_create=DATE_FORMATE('2009010101:02:03','%Y-%m-%d %H:%i:%s');

3) 在表连接 Query 中，如果连接条件两端的数据类型不一致，必须保证将驱动表的连接条件数据类型转换为与被驱动表一致的数据类型.

2.4 SELECT * 的使用规范
基本原则：
在不必要查询中使用"*"列出所有字段，且需存在GROUP BY或ORDER BY的时候，禁止使用SELECT * 一次取出所有的字段。对于表连接的 JOIN 语句，禁止使用 SELECT * 来进行查询，除非明确获得 DBA 允许。含有 text 字段的表，当不需要取出 TEXT 字段的时候，也禁止使用SELECT * 进行查询.

详细说明：
1) 进行GROUP BY或ORDER BY的时候不允许使用 SELECT * 是为了确保 MySQL能够使用最新的优化排序算法.
2) JOIN 语句不允许使用 SELECT * 是为了防止仅仅只需要索引即可完成的查询需要回表取数.
3) 存有 TEXT 字段表,在不需要取出TEXT字段的时候，不允许使用 SELECT * ,因为TEXT 字段是存放在和普通记录不一样的物理位置，会造成大量的io操作.
4) 避免因增删字段而没有修改相关SQL及相关程序代码导致程序BUG，而禁用SELECT *.

2.5 字段上添加函数使用规范

基本原则：
禁止在 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');

2.6 表连接规范
基本原则：
所有非外连接SQL（即INNER JOIN），请把关联表统一写到 FROM字句中，关联条件与过滤条件统一写到WHERE字句中。出于代码的可读性原因，所有外连接SQL语句中，请一律使用LEFT JOIN，禁用RIGHT JOIN。

另外，请注意LEFT JOIN字句中，右边位置表的条件书写位置不同的影响：

SELECT A.rolename,A.gmt_create,B.nickname FROM gl_role A LEFT JOIN gl_roledetail B ON A.ID=B.roleid AND B.roleID=2;

+-------------+---------------------+----------+
| rolename    | gmt_create          | nickname |
+-------------+---------------------+----------+
| 163.com     | 0000-00-00 00:00:00 | test2    |
| sina.com    | 0000-00-00 00:00:00 | NULL     |
| hotmail.com | 0000-00-00 00:00:00 | NULL     |
| 126.com     | 2009-08-20 18:20:18 | NULL     |
+-------------+---------------------+----------+

SELECT A.rolename,A.gmt_create,B.nickname FROM gl_role A LEFT JOIN gl_roledetail B ON A.ID=B.roleid WHERE B.roleID=2;

+----------+---------------------+----------+
| rolename | gmt_create          | nickname |
+----------+---------------------+----------+
| 163.com  | 0000-00-00 00:00:00 | test2    |
+----------+---------------------+----------+

2.7 分页查询规范
基本原则：
分页查询语句全部都需要带有排序条件，除非商业方明确要求不要使用任何排序来随机展示数据。详细说明：

1) 常规分页语句写法(start：起始记录数，page_offset：每页记录数)：

SELECT ID,username FROM gl_user WHERE username like '[e-mail]'  ORDER BY M.gmt_create LIMIT start, page_offset;

2) 多表 Join 的分页语句，如果过滤条件在单个表上，需要先分页，再 Join：

低性能写法：

SELECT M.username,P.rolename FROM gl_user M INNER JOIN gl_role P ON M.ID=P.userid WHERE username like '[e-mail]' ORDER BY M.gmt_create LIMIT start, page_offset;

高性能写法：

SELECT M.username,P.rolename FROM (SELECT ID,username FROM gl_user WHERE username like '[e-mail]' ORDER BY M.gmt_create LIMIT start, page_offset) M,gl_role P WHERE M.ID=P.userid;

这样写的前提是关联的表之间记录一一对应，否则可能会返回的记录数目少于或多于page_offset的值。

3.项目支持
3.1 重设计，轻需求：从设计阶段开始参与，不会过多干涉需求
3.2 针对重点部分详细 Review，非重点部分仅针对性检查是否符合规范
3.3 线上的MySQL产品库依然由MySQL团队的DBA负责实施与维护。各个站点的MySQL 项目，在开发测试阶段，以各个站点的DBA Team接口人为主要负责人：
国际站DBA Team方接口人：
中文站DBA Team方接口人：
CRM DBA Team方接口人：

4.变更管理
4.1 结构变更先进入数据库变更系统记，访问地址：
http://dba.hz.alibaba-inc.com:8080/dbadmin/default.jsp
4.2 有非核心小表（不超过10W条记录）结构变更的发布至少提前1星期通知发布具体时间，超过10w条记录的结构变更必须提前2星期通知发布时间
4.3 每天的09:00 – 12:00 与 14:00 – 16:00之间一般不对产品数据库做任何变更（备注：数据库结构变更）操作

5.SQL REVIEW
每个项目都会在Confluence上创建相关页面，用于提交与审核SQL。

编号	变化 Sql 语句	变化类型	开发	功能描述	执行频率	前台	是否 cache	审核	审核	修改意见	是否修改完成
( 新增 / 修改 )	负责人	( 数量级 / 天 )	/ 后台	人员	结果
1	SELECT relation_type FROM brmms_contact	新增 090510	张三	通过   memberId friend_id   查询两者 关系类型	100 万 / 天	后台	否
WHERE member_id = # memberId # AND   friend_id = # friendId #

6.开发测试服务器说明
1) 开发测试服务器，不保证其能做性能测试，而是大家公用.
2) 开发测试各有一套数据库，开发人员库名称一般为项目名称，测试人员的库：开发库_test
3) 项目开发人员帐号密码规则：库名称或项目名称或即为帐号密码.
4) 测试人员帐号密码规则：库名称或项目名称或即为帐号，密码单独发给各个站点的接口人.
5) 开发测试人员的权限一般为仅有四种权限：SELECT,INSERT,UPDATE,DELETE.
6) 开发库的结构变更必须先提交到变更系统，然后通知下DBA（目前还不能自动提醒我们）
7) 开发库结构发生变更，是否一起变更测试数据库，需要项目中约定，目前主要采用两种方式：
    第一种，约定测试库等待测试人员发送变更信息（邮件，旺旺为主）;
    第二种，约定开发人员变动数据库结构，同时修改测试库;
8) 关于数据库性能测试，DBA，开发人员，测试人员三方共同协调与借调短期内专用且配置相当的性能测试数据库服务器，DBA负责搭建.

7.MySQL使用建议
1) 进行数据库结构设计的时候，考虑适当的冗余，尽量确保应用读写数据的SQL简洁.
2) 所有字符集为utf8，校对规则为utf8_general_ci ，默认是不区分英文字母大小写，若有需求区分大小写，请跟DBA特别声明，或者表定义语句指定 COLLATE ‘utf8_bin’.
3) 尽量不需要使用子查询，特别是IN的方式，可考虑转化为EXISTS

   SELECT  * FROM A WHRE A.ColName1 IN (SELECT DISTINCT ID FROM B WHERE ..);

建议改写为：

   SELECT  * FROM A WHRE EXISTS (SELECT 1 FROM B WHERE B.ID= A.ColName1...);

4) 要返回MySQL自增序列的ID值，可以考虑使用函数LAST_INSERT_ID()，此函数只能返回同一个SESSION最近一次对有AUTO_INCREMENT属性表INSERT的ID值.
5) 所有的时间字段值，请以MySQL数据库的时钟为准，除用户输入的时间值外.
6) 对于项目的数据量、PV等合理评估，我们DBA TEAM相关人员，会给大家推荐合理成熟的数据存取架构，增强系统的扩展性与用户体验，以及高可用性等.