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

1、MySQL开发规范与实用技术交流姓名：金官丁 旺旺：Eugene198312内内 容容1.开发实用技术2.MySQL开发规范3.项目支持4.变更管理5.SQLREVIEW6.开发测试服务器说明7.MySQL相关项目建议8.简述InnoDB引擎锁与索引1.开发实用技术1.1自增字段定义1.2CHAR(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。对于中文汉字，实际存储的时候占三个字节，而数据或字母，则只占一个字节。例如：CREATETABELgl_user(usernameVARCHAR(40);则username最多能存储40个字符。1.3 字符串函数字符串函数MySQL中字符串连接方法，使用CONCAT()或CONCAT_WS()函数，语法如下：CONCAT(string1,string2,.)CONCAT_WS(separator,string1,string2,.)字符

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

4、M-DDCURTIME函数精确到秒，格式：HH:MM:SS日期数值的加减函数：DATE_ADD(date,INTERVALexprtype)DATE_SUB(date,INTERVALexprtype)常用的几种type类型：YEAR、MONTH、DAY、HOUR、MINUTE，其中expr可以为正数或负数，我们在开过程中，一般使用DATE_ADD()函数，若要作日期减去一个数字的方式，就使用负数。DATEDIFF(expr1,expr2)，是返回开始日期expr1与结束日期expr2之间，相差的天数，返回值为正数或负数。返回日期某部分信息的函数：YEAR(expr1)返回日期expr1部分的

5、年份；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-DDHH:MM:SS对应的format为%Y-%m-%d%H:%i:%S通用的类型转换函数：CAST(exprAStype)CONVERT(expr,type)CONVERT(

6、exprUSINGtranscoding_name)2.MySQL开发规范2.1字段定义规范2.2绑定变量和替代变量使用规范2.3数据类型转换规范2.4SELECT*的使用规范2.5字段上添加函数使用规范2.6表连接规范2.7分页查询规范2.8特殊操作符使用规范2.9特殊函数使用规范2.1 字段定义规范字段定义规范 MySQL中用到的相关列数据类型存储需求与范围描述信息如下表：列类型表达的范围存储需求TINYINT(M)UNSIGNED ZEROFILL-128到127 或 0到2551个字节SMALLINT(M)UNSIGNED ZEROFILL-32768到32767 或 0到655352

7、个字节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个字节DATETIMEYYYY-MM-DD HH:MM:SS(1001年到9999年的范围)8个字节TIMESTAMPYYYY-MM

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

9、；3.仅仅当字符数量可能超过20000个的时候，可以使用TEXT类型来存放字符类数据。所有使用TEXT类型的字段必须和原表进行分拆，与原表主键单独组成另外一个表进行存放；4.需要精确到时间（年月日时分秒）的字段可以使用DATETIME或TIMESTAMP，但请注意各自能表达的范围，以及是否需要用到TIMESTAMP的特性；5.所有只需要精确到天的字段全部使用DATE类型，而不应该使用TIMESTAMP或者DATETIME类型；6.自增序列类型的字段只能使用INT或者BIGINT，且明确标识出为无符号型(UNSIGNED)，除非确实会出现负数，仅当该字段数字取值会超过42亿，才使用BIGINT类

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

11、数组类型变量的方式实现绑定变量而不是通过代码拼接Query语句，例如：#userIds#iBatis会生成t.user_idin(1,2,3,4,5.)的语句2.3 数据类型转换规范数据类型转换规范基本原则：基本原则：在所有Query的Where条件中必须使用和过滤字段完全一致的数据类型，杜绝任何隐式类型转换，避免造成因为数据类型不匹配而导致Query执行计划的出错，造成性能问题.详细说明：详细说明：1所有Where条件的字段上不允许使用函数做类型转换，如有需要转换类型，只能转换过滤值,而不是转换字段.2最为常见的隐式类型转换常见于时间类型与字符串类型之间，建议所有时间类型字段在iBatis中

12、均以时间类型传入，或者以字符串传入然后通过时间函数转换字符串为合法的时间格式，如下：SELECT*FROMmemberWHEREgmt_create=DATE_FORMATE(2009010101:02:03,%Y-%m-%d%H:%i:%s);3在表连接Query中，如果连接条件两端的数据类型不一致，必须保证将驱动表的连接条件数据类型转换为与被驱动表一致的数据类型.2.4 SELECT*的使用规范的使用规范基本原则：基本原则：在不必要查询中使用“*”列出所有字段，且需存在GROUPBY或ORDERBY的时候，禁止使用SELECT*一次取出所有的字段。对于表连接的JOIN语句，禁止使用SELE

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

14、5 字段上添加函数使用规范字段上添加函数使用规范基本原则：基本原则：禁止在WHERE条件中出现的过滤字段上，使用任何函数进行类型或格式的转换；正确的做法是把传入比较的值转换为列类型所需要的。错误的写法：错误的写法：SELECTusernameFROMgl_userWHEREDATE_FORMAT(gmt_create,%Y%m%d%H%i%s)=20090501022300;正确的写法：正确的写法：SELECTusernameFROMgl_userWHEREgmt_create=DATE_FORMAT(20090501022300,%Y-%m-%d%H:%i:s);2.6 表连接规范表连接规范

15、基本原则：基本原则：所有非外连接SQL（即即INNER JOIN），请把关联表统一写到FROM字句中，关联条件与过滤条件统一写到WHERE字句中.出于代码的可读性原因，所有外连接SQL语句中，请一律使用LEFTJOIN，禁用RIGHTJOIN。另外，请注意LEFTJOIN字句中，右边位置表的条件书写位置不同的影响：SELECTA.rolename,A.gmt_create,B.nicknameFROMgl_roleALEFTJOINgl_roledetailBONA.ID=B.roleidAND B.roleID=2;+-+-+-+|rolename|gmt_create|nickname|+

16、-+-+-+|163|0000-00-0000:00:00|test2|sina|0000-00-0000:00:00|NULL|hotmail|0000-00-0000:00:00|NULL|126|2009-08-2018:20:18|NULL|+-+-+-+SELECTA.rolename,A.gmt_create,B.nicknameFROMgl_roleALEFTJOINgl_roledetailBONA.ID=B.roleidWHERE B.roleID=2;+-+-+-+|rolename|gmt_create|nickname|+-+-+-+|163|0000-00-0000:

17、00:00|test2|+-+-+-+2.7 分页查询规范分页查询规范基本原则：基本原则：分页查询语句全部都需要带有排序条件，除非商业方明确要求不要使用任何排序来随机展示数据。详细说明：1常规分页语句写法(start：起始记录数，page_offset：每页记录数)：SELECTID,usernameFROMgl_userWHEREusernamelike%163ORDERBYM.gmt_createLIMITstart,page_offset;2多表Join的分页语句，如果过滤条件在单个表上，需要先分页，再Join：低性能写法：SELECTM.username,P.rolenameFROMg

18、l_userMINNERJOINgl_rolePONM.ID=P.useridWHEREusernamelike%163ORDERBYM.gmt_createLIMITstart,page_offset;高性能写法：SELECTM.username,P.rolenameFROM(SELECTID,usernameFROMgl_userWHEREusernamelike%163ORDERBYM.gmt_createLIMITstart,page_offset)M,gl_rolePWHEREM.ID=P.userid;这样写的前提是关联的表之间记录一一对应，否则可能会返回的记录数目少于或多于pag

19、e_offset的值。3.项目支持3.1重设计，轻需求：从设计阶段开始参与，不会过多干涉需求3.2针对重点部分详细Review，非重点部分仅针对性检查是否符合规范3.3线上的MySQL产品库依然由MySQL团队的DBA负责实施与维护。各个站点的MySQL项目，在开发测试阶段，以各个站点的DBATeam接口人为主要负责人：国际站DBATeam方接口人：中文站DBATeam方接口人：CRMDBATeam方接口人：4.变更管理4.1结构变更先进入数据库变更系统记，访问地址：dba.hz.alibaba-inc:8080/dbadmin/default.jsp4.2有非核心小表（不超过10W条记录）结

20、构变更的发布至少提前1星期通知发布具体时间，超过10w条记录的结构变更必须提前2星期通知发布时间4.3每天的09:0012:00与14:0016:00之间一般不对产品数据库做任何变更（备注：数据库结构变更）操作5.SQLREVIEW每个项目都会在Confluence上创建相关页面，用于提交与审核SQL。编编号号变化变化Sql语句语句 变化类型变化类型开发开发功能描述功能描述执行频率执行频率前台前台是否是否cache审审核核审审核核修改意见修改意见是否修改完成是否修改完成(新增新增/修改修改)负责负责人人(数量级数量级/天天)/后后台台人人员员结结果果1SELECT relation_type

21、FROM brmms_contact新增090510张三通过memberId,friend_id查询两者关系类型100万/天后台否WHERE member_id=#memberId#AND friend_id=#friendId#6.开发测试服务器说明1开发测试服务器，不保证其能做性能测试，而是大家公用.2开发测试各有一套数据库，开发人员库名称一般为项目名称，测试人员的库：开发库_test3项目开发人员帐号密码规则：库名称或项目名称或即为帐号密码.4测试人员帐号密码规则：库名称或项目名称或即为帐号，密码单独发给各个站点的接口人.5开发测试人员的权限一般为仅有四种权限：SELECT,INSERT

22、,UPDATE,DELETE.6开发库的结构变更必须先提交到变更系统，然后通知下DBA（目前还不能自动提醒我们目前还不能自动提醒我们）7开发库结构发生变更，是否一起变更测试数据库，需要项目中约定，目前主要采用两种方式：第一种，约定测试库等待测试人员发送变更信息（邮件，旺旺为主）;第二种，约定开发人员变动数据库结构，同时修改测试库;8关于数据库性能测试，DBA，开发人员，测试人员三方共同协调与借调短期内专用且配置相当的性能测试数据库服务器，DBA负责搭建.7.MySQL使用建议1.进行数据库结构设计的时候，考虑适当的冗余，尽量确保应用读写数据的SQL简洁.2.所有字符集为utf8，校对规则为ut

23、f8_general_ci，默认是不区分英文字母大小写，若有需求区分大小写，请跟DBA特别声明，或者表定义语句指定COLLATEutf8_bin.3.尽量不需要使用子查询，特别是IN的方式，可考虑转化为EXISTS.SELECT*FROMAWHREA.ColName1IN(SELECTDISTINCTIDFROMBWHERE.);建议改写为：SELECT*FROMAWHREEXISTS(SELECT1FROMBWHEREB.ID=A.ColName1.);4要返回MySQL自增序列的ID值，可以考虑使用函数LAST_INSERT_ID()，此函数只能返回同一个SESSION最近一次对有AUTO_INCREMENT属性表INSERT的ID值.5所有的时间字段值，请以MySQL数据库的时钟为准，除用户输入的时间值外.6对于项目的数据量、PV等合理评估，我们DBATEAM相关人员，会给大家推荐合理成熟的数据存取架构，增强系统的扩展性与用户体验，以及高可用性等.您们的满意也是我们的满意；您们的满意也是我们的满意；您们的成功也是我们的成功；您们的成功也是我们的成功；让我们大家携手共创美好未来让我们大家携手共创美好未来.谢谢各位的聆听谢谢各位的聆听！