(MySQL开发规范.docVIP

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等。 适合并发高，数据量大，业务逻辑复杂的场景。 主要关注预热、增量更新及分片功能的实现。 分页设计 传统分页 Select * from table limit 10000,10; LIMIT原理 Limit 10000,10 偏移量越大则越慢 LIMIT方式推荐分页 Select * from table WHERE id=23423 limit 11; #10+1 (每页10条) select * from table WHERE id=23434 limit 11; LIMIT的高效分页 可能需按场景分析并重组索引。 分页方式二 Select * from table WHERE id = ( select id from table limit 10000,1 ) limit 10; 分页方式三 ?SELECT * FROM table INNER JOIN (SELECT id FROM table LIMIT 10000,10) USING (id) ; 分页方式四 程序取ID：select id from table limit 10000,10;