MySQL 开发人员规范

对于Web线上业务推荐使用 InnoDB,这也是现在 MySQL 默认的数据引擎。

虽然 MyISAM 非常适用于读远大于写的场景,但是请不要在同一个库中混用这两种引擎,因为目前大数据量情况下的备份和容灾还没有好的方案。

字符集

字符集使用 utf8,如果表需要存储文本字段(如评论,内容等),现在最好使用:utf8mb4

utf8mb4 比 utf8 多占了一个字节,但是除非你现在能确定你的业务场景绝对不会延伸到移动端。(如手机的emoji表情)

我们更应当在编译或配置文件my.cnf中强制指定服务器级别的字符集,这样应用层不再需要 SET NAMES:

[client]
default-character-set = utf8
[mysqld]
character-set-server = utf8

数据类型

数值型

尽量选择小的数据类型,小的数据长度,但要留有一定的冗余。

TINYINT (1字节)

SMALLINT(2字节)

MEDIUMINT(3字节)

INT (4字节)

BIGINT(8字节)

字符串

varchar 最大的长度是255,声明更大的长度会转换为Text,在InnoDB引擎上,应当使用varchar代替char

日期时间

应当优先使用 DATE(3字节)、DATETIME(8字节),他们比字符串占用空间更小

TIMESTAMP(3字节) 特别适合存储记录类的时间(1970-01-01 ~ 2038-01-19),如创建时间、更新时间,并且可以设置默认值与更新默认值。并且这个类型存储为UTC时间,存入和读取的时候会自动进行时区转换

DATETIME 适合存储任意范围的时间(1000-01-01 ~ 9999-12-31),可以使用 NOW() 自动插入当前时间,比较适合存储超出范围、以及相对不变化的时间

还可以使用 INT(10) 来直接存储时间戳,时间范围限制同 TIMESTAMP,主要是查询速度快,适合大量范围查询(网上这么说),缺点是不直观,需要使用 from_unixtime() 转换才能看到结果。

表名与字段名

表名、字段名尽量能够表达意思,字段最好增加注释。

表名:一律小写,多个单词用下划线分隔,单词用单数不用复数,如:member, member_detail

字段名:一律小写,多个单词用下划线分隔,字段名不加表名,如:id, name, start_time

关联表:关联表的主键id,可以不用下划线,如 memberid (有争议,我觉得member_id有个下划线好难看)

常用字段命名(供参考,约定后保持统一):

添加时间:created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
添加人:createby
更新时间:updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
更新人:updateby
删除时间:deleted timestamp
删除人:deleteby
删除标志:delete tinyint
状态标志:status  如果状态值较多、较复杂,建议使用 enum 类型

引擎对比

主要是 MyISAM 与 InnoDB 的对比

缓存

MyISAM 仅仅缓存索引,不会缓存实际数据信息,他会将这一工作交给操作系统级别的文件系统缓存。所以 MyISAM 缓存优化工作集中在索引缓存优化上,而禁用缓存需要两步:

echo 3 > /proc/sys/vm/drop_caches
SELECT SQL_NO_CACHE * FROM ...

InnoDB 有自己的缓存,不仅仅缓存索引,还缓存表数据,只要你内存足够大。比如在做数据迁移的时候,有时候可以采用倒读的方法。

内存占用

缓存机制基本上决定了内存占用情况。

InnoDB 内存占用较大,并且由 innodb_buffer_pool_size 参数控制,对于独立的 InnoDB 数据库服务器一般要配置为物理内存大小的70%-80%,原因就是它的缓存机制。

磁盘占用

MyISAM 存储索引与数据是独立存储的。

InnoDB索引与数据整体存储在.ibd文件,一定要开启 innodb_file_per_table 选项,否则 InnoDB 会将所有表存在共享空间中。

整体上 MyISAM 磁盘占用约比 InnoDB 小20%左右。

安全性

InnoDB 安全系数很高,innodb_flush_log_at_trx_commit 参数会控制刷写机制,表基本上不会坏,目前我只遇到过因为文件系统崩溃导致数据无法恢复的情况。

MyISAM 在频繁写和更新的时候,经常容易坏,虽然很容易修复(有时也无法修复),但在坏表期间会影响线上业务。

锁定

MyISAM 是表级锁定,写入的时候阻塞读取,还会在读取的时候阻塞写入,尤其是你使用 mysqldump 的时候

InnoDB 是行级锁定,但也不绝对,比如SQL无法确定表的行范围时也会进行表锁

事务支持、外键

InnoDB 支持事务,对稍微大一点规模的Web业务,基本都会需要这个功能,会员注册你要写 member, member_detail 两张表,用户下订单,每个订单可能是一条记录,这样的插入、更新操作都要保证原子性。

注意:需要非常谨慎的使用外键、触发器

并发

MyISAM 的表级锁定决定了在写场景多的情况下多用户并发很差,而 InnoDB 的以下三个参数还可以更好控制并发:

innodb_thread_concurrency  # 独立数据库一般为CPU核数
innodb_read_io_threads
innodb_write_io_threads

写入与更新

并发性能好,并不能说明写入效率高,在千万级别的单条插入我没感觉到两者差异,但MyISAM的批量插入性能非常高。

另外 MyISAM 还可以调整 bulk_insert_buffer_size 参数来提高批量插入的速度。

LOAD DATA 也非常快,底层应该也是采用的批量插入。

很明显索引过多会影响插入性能。

其他

MyISAM 不带 Where 条件的 COUNT(*) 非常快,而 InnoDB 需要全表扫描。

SQL MODE


5.6.6 开始 MySQL 的默认sql_mode为 `NO_ENGINE_SUBSTITUTION`,之前的版本为空。

5.7.5 开始增加了ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES

5.7.7 开始增加了NO_AUTO_CREATE_USER

5.7.8 开始增加了ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, NO_ZERO_IN_DATE

所以,5.7 最新默认sql_mode为 ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

参数说明

NO_ENGINE_SUBSTITUTION  禁用时,当表引擎不存在的时候会自动转换,否则会报错
ERROR_FOR_DIVISION_BY_ZERO 数据库对于除以 0 情况的处理,比如  MOD(N, 0)
NO_AUTO_CREATE_USER  指定 GRANT 语句必须完善认证信息,比如设置密码
NO_ZERO_DATE  指定是否 0000-00-00 是合法的日期格式
NO_ZERO_IN_DATE  更严格的日期格式限制,比如 2010-00-01 或 2010-01-00
ONLY_FULL_GROUP_BY 官方解释没看懂

(以上为个人理解和整理,如有错误请指教)