MySQL 规范

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

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

字符集

字符集使用 utf8mb4，MySQL 的 utf8 是假的 utf8，不能保存特殊字符（如手机的emoji表情）

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

[client]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4

数据类型

数值型

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

TINYINT (1字节)

SMALLINT(2字节)

MEDIUMINT(3字节)

INT (4字节)

BIGINT(8字节)

如果需要存储小数，使用 decimal，不要使用 float 和 double（存在精度丢失问题）。如果是货币计算，推荐使用整型来进行存储，前端处理小数点。

字符串

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() 转换才能看到结果。

表名与字段名

不要使用保留字：如 desc、match 等

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

表名：一律小写，多个单词用下划线分隔，单词用单数不用复数，如：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 缓存优化工作集中在索引缓存优化上。

清空和禁用缓存需要两步：

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

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

内存占用

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

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

磁盘占用

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 的以下三个参数还可以更好控制并发：

1
2
3

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 官方解释没看懂