Mysql开发规范

摘自《高可用架构》

 

大家应该都很熟悉MySQL 数据库，而且随着前几年的阿里的去IOE 、MySQL 逐渐引起更多人的重视。

1. MySQL 历史

• 1979 年， Monty Widenius 写了最初的版本， 1996 年发布l.O o

• 1995-2000 年， MySQLAB 成立，引入BDB 。

• 2000 年4 月， 集成My ISAM 和replication 。

• 2001 年， Heikki Tuuri 向MySQL 建议集成InnoDB 。

• 2003 发布5.0 ，提供了视图、存储过程等功能。

• 2008 年， MySQLAB 被Sun 收购， 2009 年推出5.1 。

• 2009 年4 月， Oracle 收购S un, 2010 年12 月推出5.5 。

• 2013 年2 月推出5.6 GA, 5.7 开发中。

• 2015 年10 月， MySQL5.7 发布GA 版本。

• 2016 年9 月，发布下一个MySQL 大版本8.0 里程碑版本，引入更多更新。

 

2 . MySQL 的优点

． 使用简单。

· 开源免费。

· 扩展性“好”，在一定阶段扩展性好。

． 社区活跃。

· 性能可以满足互联网存储和性能需求，离不开硬件支持。

上面这几个因素也是大多数公司选择考虑My SQL 的原因。不过MySQL 本身存在的问题和限制也很多，有些问题点也经常被其他数据库吐槽或鄙视。

 

3. MySQL 存在的问题

·优化器对复杂SQL 支持不好。

． 对SQL 标准支持不好。

· 大规模集群方案不成熟，主要指中间件。

• ID 生成器，全局自增ID 。

· 异步逻辑复制，数据安全性问题。

• Online DDL 。

• HA 方案不完善。

． 备份和恢复方案还是比较复杂，需要依赖外部组件。

． 展现给用户的信息过少，排查问题困难。

· 众多分支，让人难以选择。

看到了刚才讲的MySQL 的优势和劣势，可以看到MySQL 面临的问题还是远大于它的

优势的，很多问题也是我们实际需要在运维中优化解决的，这也是MySQLDBA 的一方面

价值所在。而且MySQL 的不断发展也离不开杜区支持，比如Google 最早提交的半同步

patch ，后来也合并到官方主线。Fac ebook 、Tw itter 等也都开源了，内部使用MySQL 分支

版本，包含了它们内部使用的patch 和特性。

 

 

数据库开发规范

数据库开发规范定义：开发规范是针对内部开发的一系列建议或规则，由OBA 制订（如

果有OBA 的话）。

开发规范本身也包含几部分： 基本命名、约束规范、字段设计规范、索引规范、使用

规范。

 

5.3 优化和运维之道

1. 规范存在的意义

．保证线上数据库schema 规范。

．减少出问题概率。

·方便自动化管理。

·需要长期坚持规范，这对开发和DBA 来说是双赢。

试想如果没有开发规范，有的开发人员会写出各种全表扫描的SQL 语句或者各种奇商

SQL 语旬，我们之前就看过开发人员写出可以打印好几页的SQL 。这会造成业务不稳定，

也会让DBA 天天忙于各种救火情况。

 

2 . 基本命名和约束规范

·表字符集选择UTF8 ，如果需要存储emoj 表情，需要使用UTF8mb4 (MySQL 5.5 .3

以后支持）。

·存储引擎使用InnoDB 。

·变长字符串尽量使用varchar 或varbinary 。

．不在数据库中存储图片、文件等。

·单表数据量控制在l 亿以下。

·库名、表名、字段名不使用保留字。

·库名、表名、字段名、索引名使用小写字母，以下划线分隔，需要见名知意。

．库表名不要设计得过长，尽可能用最少的字符表达出表的用途。

 

3 . 字段规范

·所有字段均定义为NOT NULL ，除非你真的想存NULL 。

· 字段类型在满足需求条件下越小越好，使用UN SIG阳D 存储非负整数， 实际使用

时存储负数的场景不多。

·使用TIMESTAMP 存储时间。

·使用varchar 存储变长字符串，当然要注意varchar C M ） 里的M 指的是字符数而不

是字节数：使用UNSIGNED INT 存储1Pv4 地址而不是CHAR (15 ），这种方式只能

存储1Pv4 ，存储不了1Pv6 。

·使用DECIMAL 存储精确浮点数，用float 类型可能会存在数据误差。

·少用blob texto

关于为什么定义不使用NULL 的原因，有2 种。

·浪费存储空间，因为InnoDB 需要额外一个字节来存储。

· 表内默认值NULL 过多会影响优化器选择执行计划。

关于使用datatime 和timestamp ，现在在5.6.4 版本之后又有了变化，如下表所示。使

用二者来存储时，它们在存储空间方面的差距越来越小，而datatime 本身的存储范围就比

timestamp 大很多，且timestamp 只能存储到2038 年。

 

4 . 索引规范

· 单个索引字段数不超过5 ，单表索引数量不超过5 ，索引设计遵循B+Tree 索引最左

前缀匹配原则。

· 选择区分度高的列作为索引。

· 建立的索引能覆盖8 0% 主要的查询，不求全，解决问题的主要矛盾就好。

• DML 要和order by 、group b y 字段建立合适的索引。

· 避免索引的隐式转换。

· 避免冗余索引。

关于索引规范， 一定要记住索引这个东西是一把双刃剑，在加速读的同时也引入了很

多额外的写入和锁，降低了写入能力，这也是为什么要控制索引数的原因。之前看到过不

少人给表里每个字段都建了索引，其实这可能对查询起不到什么作用。

冗余索引示例：

• idx_abc(a,b,c ）。

• idx_a(a）冗余。

• idx_ab(a,b）冗余。

隐式转换示例：

字段： remark varchar(SO)NOT Null

MySQL>SELECT id,ift_code FROM gift WHERE deal_id =640 AND remark=l15127;1

row in set (0.14 sec)

MySQL>SELECT id, gift_code FROM pool_gift WHERE deal_id=640 AND

remark=‘ l 15127’;

l row in set (0.005 sec)

字段定义为varchar 类型， 但传入的值是int 类型， 就会导致全表扫描，这要求程序端

做好类型检查。

5. SQL 类规范

．建议尽量不使用存储过程、触发器、函数等，减少维护成本和性能隐患。

．避免使用大表的JOIN, MySQL优化器对JOIN 优化策略过于简单。

·避免在数据库中进行数学运算和其他大量计算任务。

• SQL合并，主要是指DML时多个value合并，减少和数据库交互。

．合理的分页，尤其大分页。

·如果UPDATE、DELETE 语句不使用LIMIT，在Statement 日志格式下容易造成主从不一致。