MySQL开发规范与实用技术交流-精品课件

1、MySQL开发规范与实用技术交流姓名：金官丁 旺旺：Eugene198312内 容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

2、中此两类字符串定义时候填写的长度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,

3、string2,.) 字符串长度统计： LENGTH(string) #返回string所占的字节数 CHAR_LENGTH(string) #返回string中的字符个数 统计字符个数，就不区分是汉字还是字母或数字，也跟字符集没有关系，若统计的是字节数，则由字符是汉字、字母或数字类型，以及字符集共同决定。 请各位牢记：我们所有的MySQL数据库都将会采用UTF8编码，所以一个汉字占3个字节，一个字母或数字占一个字节。1.4 日期操作函数 获取当前时间：NOW()，CURDATE()、CURTIME() 其中，NOW()函数精确到秒，格式：YYYY-MM-DD HH:MM:SS CURDATE

4、函数精确到天，格式：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之间，相差的天数 ，返回值为正数或负数。返回日期某部分

5、信息的函数：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(

6、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 或

7、0到2551个字节SMALLINT(M) UNSIGNED ZEROFILL-32768到32767 或 0到655352个字节INT(M) UNSIGNED ZEROFILL-2147483648到2147483647 或 0到42949672954个字节BIGINT(M) UNSIGNED ZEROFILL-9223372036854775808到9223372036854775807 或 0到18446744073709551615 8个字节DECIMAL(M,D) UNSIGNED ZEROFILL整数最大位数（M）为65，小数位数最大（D）为30变长DATEYYYY-MM-DD3个字

8、节DATETIMEYYYY-MM-DD HH:MM:SS(1001年到9999年的范围)8个字节TIMESTAMPYYYY-MM-DD HH:MM:SS（1970年到2037年的范围）4个字节CHAR(M)0M=255（建议CHAR(1)外，超过此长度的用VARCHAR）M个字符（所占空间跟字符集等有关系）VARCHAR(M)0M65532/NM个字符（N大小由字符集，以及是否为中文还是字母数字等有关系）TEXT64K个字符所占空间跟字符集等有关系详细说明：所有动态长度字符串全部使用 VARCHAR 类型，类似于状态，有限类别的字段， 也使用可以比较明显表示出实际意义的字符串，而不应该使用IN

9、T之类的数字来代替；2. 固定长度的字符串使用 CHAR 类型，所有单个字符的全部使用 CHAR 类型， 而不应该使用VARCHAR 类型；3. 仅仅当字符数量可能超过 20000 个的时候，可以使用 TEXT 类型来存放字符 类数据。所有使用 TEXT 类型的字段必须和原表进行分拆，与原表主键单独组 成另外一个表进行存放；4. 需要精确到时间（年月日时分秒）的字段可以使用DATETIME 或TIMESTAMP， 但请注意各自能表达的范围，以及是否需要用到TIMESTAMP的特性；5. 所有只需要精确到天的字段全部使用 DATE 类型，而不应该使用 TIMESTAMP 或者DATETIME 类

10、型；6. 自增序列类型的字段只能使用 INT 或者 BIGINT，且明确标识出为无符号型 (UNSIGNED)，除非确实会出现负数，仅当该字段数字取值会超过42亿，才使 用 BIGINT 类型； 2.2 绑定变量和替代变量使用规范基本原则： 所有 Query 的 Where 条件中的变量，都需要使用绑定变量来实现，此要求并不完全是基于性能的考虑，更多是基于安全方面的考虑，如若有任何不使用绑定变量的需求，都必须通过安全部门的审核并征得同意。详细说明：. 在 iBatis 的 SqlMap 文件中绑定变量使用 “#var_name#”表示，替代变量使用$var_name$”；所有需要动态 Orde

11、r By 条件的 Query，在使用替代变量过程中，需要将可能传入的内容以枚举类写死在代码中，禁止接受任何外部传入内容；. 对于不变的常量条件，请使用常量而不是变量；. IN子句，使用Iterate + 数组类型变量的方式实现绑定变量而不是通过代码拼接 Query 语句，例如：#userIds#iBatis会生成t.user_id in (1,2,3,4,5 .)的语句2.3 数据类型转换规范基本原则： 在所有 Query 的 Where 条件中必须使用和过滤字段完全一致的数据类型，杜绝任何隐式类型转换，避免造成因为数据类型不匹配而导致 Query 执行计划的出错，造成性能问题.详细说明：1

12、所有 Where 条件的字段上不允许使用函数做类型转换，如有需要转换类型，只能转换过滤值,而不是转换字段.2 最为常见的隐式类型转换常见于时间类型与字符串类型之间，建议所有时间类型字段在iBatis中均以时间类型传入，或者以字符串传入然后通过时间函数转换字符串为合法的时间格式 ，如下：SELECT * FROM member WHERE gmt_create=DATE_FORMATE(2009010101:02:03,%Y-%m-%d %H:%i:%s);3 在表连接 Query 中，如果连接条件两端的数据类型不一致，必须保证将驱动表的连接条件数据类型转换为与被驱动表一致的数据类型. 2.4

13、SELECT * 的使用规范基本原则： 在不必要查询中使用“*”列出所有字段，且需存在GROUP BY或ORDER BY的时候，禁止使用SELECT * 一次取出所有的字段。对于表连接的 JOIN 语句，禁止使用 SELECT * 来进行查询，除非明确获得 DBA 允许。含有 text 字段的表，当不需要取出 TEXT 字段的时候，也禁止使用SELECT * 进行查询.详细说明：1 进行GROUP BY或ORDER BY的时候不允许使用 SELECT * 是为了确保 MySQL 能够使用最新的优化排序算法.2 JOIN 语句不允许使用 SELECT * 是为了防止仅仅只需要索引即可完成的查询需

14、 要回表取数.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

15、)=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.rolenam

16、e,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 | 0000-00-00 00:00:00 | test2 | | sina | 0000-00-00 00:00:00 | NULL | | hotmail | 0000-00-00 00:00:00 | NULL | | 126 | 2009-08-20 18:20:18 | NULL | +SELECT A

17、.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 | 0000-00-00 00:00:00 | test2 | + 2.7 分页查询规范基本原则： 分页查询语句全部都需要带有排序条件，除非商业方明确要求不要使用任何排序来随机展示数据。详细说明：1 常规分页语句写法(start：起始记录数，page_offset：每页记录数)：SELECT

18、ID,username FROM gl_user WHERE username like %163 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 %163 ORDER BY M.gmt_create LIMIT start, page_offset;高性

19、能写法：SELECT M.username,P.rolenameFROM (SELECT ID,username FROM gl_user WHERE username like %163 ORDER BY M.gmt_create LIMIT start, page_offset)M,gl_role PWHERE M.ID=P.userid;这样写的前提是关联的表之间记录一一对应，否则可能会返回的记录数目少于或多于page_offset的值。 3.项目支持 3.1 重设计，轻需求：从设计阶段开始参与，不会过 多干涉需求 3.2 针对重点部分详细 Review，非重点部分仅针对性 检查是否符合

20、规范 3.3 线上的MySQL产品库依然由MySQL团队的DBA负责实施与 维护。各个站点的MySQL 项目，在开发测试阶段，以各个 站点的DBA Team接口人为主要负责 人： 国际站DBA Team方接口人： 中文站DBA Team方接口人： CRM DBA Team方接口人：4.变更管理 4.1 结构变更先进入数据库变更系统记，访问地址： dba.hz.alibaba-inc:8080/dbadmin/default.jsp 4.2 有非核心小表（不超过10W条记录）结构变更的发 布至少提前1星期通知发布具体时间，超过10w条 记录的结构变更必须提前2星期通知发布时间 4.3 每天的09

21、:00 12:00 与 14:00 16:00 之间一般不对产品数据库做任何变更（备注：数据库结 构变更）操作5.SQL REVIEW每个项目都会在Confluence上创建相关页面，用于提交与审核SQL。编号变化Sql语句变化类型开发功能描述执行频率前台是否cache审核审核修改意见是否修改完成(新增/修改)负责人(数量级/天)/后台人员结果1SELECT 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 开发库结构发生变更，是否一起变更测试数据库，需要项目中