《开发规.docVIP

MySQL开发规范 简介 持续借鉴、收集并整理一些开发规范和技巧，期望能更充分利用MySQL的特性，得到更好的性能。 规范是死的，人是活的。 现在定义的规范，是为以后推翻准备的。 目的 提供给开发人员参考，方便写成更有效率的开发。 范围 文档涉及的范围：需要基于MySQL做应用开发的人员。 定义、首字母缩写词和缩略语 暂无 数据库设计 目标三个：功能实现，可伸缩性，可用性。 关键点：平衡业务技术各个方面，做好取舍。 80%的性能优化来自架构设计的优化。 引擎及版本选择 引擎建议使用InnoDB 根据目前我们业务的特点，建议使用MySQL5.1社区版和InnoDB plugin或MySQL5.5，后续MySQL5.6比较稳定后再行考量和评估。 架构浅谈 开发大牛都擅长，这里不多提，仅标注一下。 非功能性需求 读写分离 分库分表 热点数据 多级缓存 雪崩效应与过载保护 读优化 写优化 schema设计 尽量不在数据库做运算 复杂运算移到程序端CPU 尽可能简单应用MySQL 如：md5() 或Order by Rand()或计算字段等操作不在数据库表上进行。 适当的范式设计 库和表预估 常见的有100库100表，1000库10表等。 建议单库不超过300-400个表。 总空间容量不超过100G。 单表控制 考虑因素 IO高效；全表遍历；表修复快；提高并发；alter table快。 字段数量 建议上限20~50个。 一年内的单表数据量预估 建议纯INT不超1000W，含CHAR不超500W。 举例 单表1G体积 500W行评估： 顺序读1G文件需N秒 单行不超过200Byte 单表不超50个纯INT字段 单表不超20个CHAR(10)字段 拒绝3B 大SQL (BIG SQL) 大事务 (BIG Transaction) 大批量 (BIG Batch) 反范式设计 概念 无外键，少多表join查询。 便于分布式设计，允许适度冗余，为了容量扩展允许适度开销。 基于业务自由优化，基于i/o 或查询设计，无须遵循范式结构设计。 典型场景 原有展现程序涉及多个表的查询，希望精简查询程序。 数据表拆分往往基于主键，而原有数据表往往存在非基于主键的关键查询，无法在分表结构中完成。 存在较多数据统计需求（count, sum等），效率低下。 解决思路 基于展现的冗余设计 如： 消息表message，存在字段 from_uid,to_uid,msg,send_time 四个字段，而展示程序需要显示发送者姓名和性别。 通常在message表中增加冗余字段from_username和from_user_sex即可。 基于查询的冗余设计 如： 用户分表，将用户库分成若干数据表。基于用户名的查询和基于uid的查询都是高并发请求。用户分表基于uid分成数据表，同时基于用户名做对应冗余表。 如果允许多方式登陆，可以有如下设计方法： uid,passwd,用户信息等等，主数据表，基于uid分表 ukey,ukeytype,uid基于ukey分表，便于用户登陆的查询。分解成如下两个SQL： select uid from ulist_key_13 where ukey=$username and ukeytype=$login; select * from ulist_uid_23 where uid=$uid and passwd=$passwd; ukeytype定义用户的登陆依据，比如用户名，手机号，邮件地址，网站昵称等。 Ukey+ukeytype必须唯一。 此种方式需要登陆密码统一，对于第三方接入模式，可以通过引申额外字段完成。 基于统计的冗余设计 如： count(*)操作。 需要不精准结果，可以直接show table status like …获得。 需要精准结果，可以在缓存层增加key-value对，实时更新该key-value。同时异步更新到数据库中冗余字段，或冗余表中。 历史数据表 历史数据表对应于热点数据表。 将需求较少又不能丢弃的数据，仅在少数情况下被访问存入历史数据表。 全文检索设计 最差的设计 直接使用sql语句where条件中使用like %fulltext% 直接全表扫描或全索引扫描，性能最差，无任何扩展，基本不可接受。 MySQL相关引擎支持 MyISAM全文索引，使用match()函数搜索。InnoDB从MySQL5.6.4开始支持全文索引，对中文支持不好，使用MATCH()…AGAINST。 并发不高，数据量不大，业务逻辑简单，可以考虑。 使用外部开源全文检索引擎 目前常用的有sphinx和lucene等。 适合并发高，数据量大，业务逻辑复杂的场景。 主要关注预热、增量更新及分片功能的实现。 分页设计 传统分页 Sele