MySQL开发规范

MySQL开发规范第1页共20页MySQL开发规范1.简介持续借鉴、收集并整理一些开发规范和技巧，期望能更充分利用MySQL的特性，得到更好的性能。规范是死的，人是活的。现在定义的规范，是为以后推翻准备的。1.1目的提供给开发人员参考，方便写成更有效率的开发。1.2范围文档涉及的范围：需要基于MySQL做应用开发的人员。1.3定义、首字母缩写词和缩略语暂无2.数据库设计目标三个：功能实现，可伸缩性，可用性。关键点：平衡业务技术各个方面，做好取舍。80%的性能优化来自架构设计的优化。2.1引擎及版本选择引擎建议使用InnoDB根据目前我们业务的特点，建议使用MySQL5.1社区版和InnoDBplugin或MySQL5.5，后续MySQL5.6比较稳定后再行考量和评估。2.2架构浅谈开发大牛都擅长，这里不多提，仅标注一下。MySQL开发规范第2页共20页2.2.1非功能性需求2.2.2读写分离2.2.3分库分表2.2.4热点数据多级缓存雪崩效应与过载保护读优化写优化2.3schema设计2.3.1尽量不在数据库做运算2.3.2复杂运算移到程序端CPU2.3.3尽可能简单应用MySQL如：md5()或OrderbyRand()或计算字段等操作不在数据库表上进行。2.3.4适当的范式设计2.3.5库和表预估常见的有100库100表，1000库10表等。建议单库不超过300-400个表。总空间容量不超过100G。2.3.6单表控制考虑因素IO高效；全表遍历；表修复快；提高并发；altertable快。MySQL开发规范第3页共20页字段数量建议上限20~50个。一年内的单表数据量预估建议纯INT不超1000W，含CHAR不超500W。举例单表1G体积500W行评估：顺序读1G文件需N秒单行不超过200Byte单表不超50个纯INT字段单表不超20个CHAR(10)字段2.3.7拒绝3B大SQL(BIGSQL)大事务(BIGTransaction)大批量(BIGBatch)2.4反范式设计2.4.1概念无外键，少多表join查询。便于分布式设计，允许适度冗余，为了容量扩展允许适度开销。基于业务自由优化，基于i/o或查询设计，无须遵循范式结构设计。2.4.2典型场景a)原有展现程序涉及多个表的查询，希望精简查询程序。b)数据表拆分往往基于主键，而原有数据表往往存在非基于主键的关键查询，无法在分表结构中完成。c)存在较多数据统计需求（count,sum等），效率低下。2.4.3解决思路基于展现的冗余设计如：消息表message，存在字段from_uid,to_uid,msg,send_time四个字段，而展示程序需要显示MySQL开发规范第4页共20页发送者姓名和性别。通常在message表中增加冗余字段from_username和from_user_sex即可。基于查询的冗余设计如：用户分表，将用户库分成若干数据表。基于用户名的查询和基于uid的查询都是高并发请求。用户分表基于uid分成数据表，同时基于用户名做对应冗余表。如果允许多方式登陆，可以有如下设计方法：uid,passwd,用户信息等等，主数据表，基于uid分表ukey,ukeytype,uid基于ukey分表，便于用户登陆的查询。分解成如下两个SQL：selectuidfromulist_key_13whereukey='$username'andukeytype='$login';select*fromulist_uid_23whereuid=$uidandpasswd='$passwd';ukeytype定义用户的登陆依据，比如用户名，手机号，邮件地址，网站昵称等。Ukey+ukeytype必须唯一。此种方式需要登陆密码统一，对于第三方接入模式，可以通过引申额外字段完成。基于统计的冗余设计如：count(*)操作。需要不精准结果，可以直接showtablestatuslike…获得。需要精准结果，可以在缓存层增加key-value对，实时更新该key-value。同时异步更新到数据库中冗余字段，或冗余表中。历史数据表历史数据表对应于热点数据表。将需求较少又不能丢弃的数据，仅在少数情况下被访问存入历史数据表。2.5全文检索设计2.5.1最差的设计直接使用sql语句where条件中使用like%fulltext%直接全表扫描或全索引扫描，性能最差，无任何扩展，基本不可接受。2.5.2MySQL相关引擎支持MyISAM全文索引，使用match()函数搜索。InnoDB从MySQL5.6.4开始支持全文索引，对中文支持不好，使用MATCH()…AGAINST。并发不高，数据量不大，业务逻辑简单，可以考虑。MySQL开发规范第5页共20页2.5.3使用外部开源全文检索引擎目前常用的有sphinx和lucene等。适合并发高，数据量大，业务逻辑复杂的场景。主要关注预热、增量更新及分片功能的实现。2.6分页设计2.6.1传统分页Select*fromtablelimit10000,10;2.6.2LIMIT原理Limit10000,10偏移量越大则越慢2.6.3LIMIT方式推荐分页Select*fromtableWHEREid=23423limit11;#10+1(每页10条)select*fromtableWHEREid=23434limit11;2.6.4LIMIT的高效分页可能需按场景分析并重组索引。分页方式二Select*fromtableWHEREid=(selectidfromtablelimit10000,1)limit10;分页方式三?SELECT*FROMtableINNERJOIN(SELECTidFROMtableLIMIT10000,10)USING(id);分页方式四程序取ID：selectidfromtablelimit10000,10;Select*fromtableWHEREidin(123,456…);2.6.5LIMIT分页举例MySQLselectsql_no_cache*frompostlimit10,10;10rowinset(0.01sec)MySQL开发规范第6页共20页MySQLselectsql_no_cache*frompostlimit20000,10;10rowinset(0.13sec)MySQLselectsql_no_cache*frompostlimit80000,10;10rowsinset(0.58sec)MySQLselectsql_no_cacheidfrompostlimit80000,10;10rowsinset(0.02sec)MySQLselectsql_no_cache*frompostWHEREid=323423limit10;10rowsinset(0.01sec)MySQLselect*frompostWHEREid=(selectsql_no_cacheidfrompostlimit80000,1)limit10;10rowsinset(0.02sec)2.6.6LIMIT分页与缓存结合类似sina微博的首页，总共保留最新的500条微博，分10页。分页样式类似如下：2.7表字段设计2.7.1主键如果使用的是InnoDB并且不需要特殊的聚簇。定义一个代理键（surrogatekey）是个好的主意。意思就是这个主键并不是来自于你的应用程序的数据（与业务逻辑无关，而应用程序的数据如果有唯一的候选列可以做成唯一键），最简单的方法就是使用AUTO_INCREMENT列。这能保证数据插入保持着连续的顺序并且对于使用主键连接会获得更好的性能。最好避免使用随机的聚簇键。对每张表，最重要的就是一定要有主键。MySQL开发规范第7页共20页主键设计之UUID从性能的角度来说，使用UUID是个最不好的方法：它使聚簇索引的插入是随机的。这是最不好的场景了。并且对于数据的聚集也没有什么帮助。UUID_SHORT()UUID_SHORT()所占用的存储空间比UUID要小。（UUID_SHORT()可能要使用bigint占用8个字节，而UUID可能要使用字符串用char(32)）。另外uuid_short()是顺序的，这个也解决了随机导致的问题，但是uuid_short()也有一些限制和bug。主键设计之自增列没什么好说的，自增列简单实用。仅注意一下锁(gaplock)问题即可。主键设计之程序控制建立类似如下表：避免自增列引起的一些锁问题，统一管理，并发性更高。主键设计之使用中间件更高的并发性，可以考虑从数据库中剥离，使用自己开发或第三方中间件，如：类型溢出举例以MySQL5版本，int类型为例：MySQL开发规范第8页共20页#建表root@localhost(test2)14:46createtabletest2(aint(10)UNSIGNED);QueryOK,0rowsaffected(0.12sec)#插入数据root@localhost(test2)14:56inserttest2values(10);QueryOK,1rowaffected(0.00sec)#模拟更新溢出root@localhost(test2)14:56updatetest2seta=a-11;QueryOK,1rowaffected,1warning(0.00sec)Rowsmatched:1Changed:1Warnings:1#查看warningsroot@localhost(test2)14:57showwarnings;+---------+------+--------------------------------------------+|Level|Code|Message|+---------+------+--------------------------------------------+|Warning|1264|Outofrangevalueforcolumn'a'atrow1|+---------+------+--------------------------------------------+1rowinset(0.00sec)#确定实际得到的值已经溢出root@localhost(test2)14:57select*fromtest2;+------------+|a|+------------+|4294967295|+------------+1rowinset(0.00sec)#清理数据root@localhost(test2)14:59deletefromtest2;QueryOK,1rowaffected(0.00sec)#模拟插入溢出root@localhost(test2)14:59inserttest2values(-1);QueryOK,1rowaffected,1warning(0.00sec)#查看warningsroot@localhost(test2)14:59showwarnings;+---------+------+--------------------------------------------+|Level|Code|Message|+---------+------+--------------------------------------------+|Warning|1264|Outofrangevalueforcolumn'a'atrow1|+---------+------+--------------------------------------------+1rowinset(0.00sec)#确定实际得到的值已经溢出root@localhost(test2)14:59select*fromtest2;