mysql

MySQL 开发约定

基本原则

DDL 类的 SQL（CREAT、ALTER、DROP 等）必须先通过代码评审，对数据模型的合理性进行分析，并统一建表风格，方便后期维护。
ALRER 操作非常耗时，必须在业务空闲时操作。
查询类的 SQL 必须先 EXPLAIN 进行性能分析，优先使用已存在的索引，严禁出现全表查询的情况。
创建高性能索引

建表语句约定

数据表中的首个字段必须为 id，且将其设置自增长的主键
数据表中的最后两个字段必须为 ctime 和 mtime，分别存储所在记录的创建时间和最后修改时间
数据表中的字段顺序应该符合一定的内在逻辑，添加字段应该使用 AFTER 关键字
存储数值若无负数逻辑，数值类型的字段必须添加 UNSIGNED 属性
所有字段均要三设置：设置 NOT NULL（ TEXT 等特殊类型等除外）、设置默认值（数值类型的字段的默认值为 0，字符串类型的字段的默认值为 ''）、设置备注信息
数据表引擎统一使用 innodb
数据表字符集统一设置为 utf8

通用建表语句


CREATE TABLE IF NOT EXISTS db_name.table_name (
  id    INT UNSIGNED NOT NULL AUTO_INCREMENT
  COMMENT '自增主键 id',
  ctime TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '创建时间',
  mtime TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  COMMENT '最后修改时间',
  PRIMARY KEY (id)
)
  ENGINE = innodb
  DEFAULT CHARSET = utf8
  COMMENT '表简介';

Schema 与数据类型优化原则

尽量避免过度设计，例如会导致极其负责查询的 Schema 设计，或者有很多列的表的设计；
使用小而简单的适合数据类型：前提是要确保没有低估需要存储的值范围，因为它占用更少的磁盘、内存、CPU缓存，并且处理时需要的CPU周期也更少；
除非真实数据模型有确切的需要，否则应该尽可能地避免使用 NULL 值：值可为NULL的列使得索引、索引统计和值比较都更复杂化。可为NULL的列会使用更多的存储空间；
尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列；
注意可变长字符串，其在临时表中排序时可能导致悲观的按最大长度分配内存；
尽量使用整形定义标识列；
避免使用MySQL已经遗弃的特性，例如指定浮点数的精度，后者整数的显示宽度；
小心使用ENUM和SET。虽然他们用起来很方便，但是不要滥用，否则有时候会变成陷阱；

索引优化原则

索引有效的类型

全值匹配：是指和索引中的所有列进行匹配。
匹配最左前缀：即只使用索引的第一列。
匹配列前缀：即只使用索引第一列的开头部分。
匹配范围值：只使用了索引的第一列。
精确匹配某个列并范围匹配另外一列：即索引第一列全匹配，第二列范围匹配。
只访问索引的查询：又被称为“覆盖索引”，B-tree通常支持“只访问索引的查询”，即查询只需要访问索引，而无须访问数据行。如用户名与密码的匹配，手机号与验证码的匹配。

索引无效的类型

如果不是按照索引的最左列开始查找，则无法使用索引。
不能跳过索引中的列。
如果查询中有某个列的范围查询，则其右边所有列都无法使用索引。

综上所述，能否有效使用索引的关键在于索引列的顺序。在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

索引的优势

索引大大减少了服务器需要扫描的数据量；
索引可以帮助服务器避免排序和临时表；
索引可以将随机I/O变为顺序I/O；

索引评价“三星系统”（three-star system）

索引相关记录放在一起则获得一星；
索引中的数据顺序和查找中的排序顺序一致则获得二星；
索引中的列包含了查询中需要的全部列则获取三星；

高性能索引策略

独立的列
前缀索引和索引选择性
多列索引
覆盖索引

总结

选择索引和编写利用这些索引的查询的 3 原则：

单行访问是很慢的。特别是在机械硬盘存储中（SSD固态硬盘的随意IO要快很多，不过这一点仍然适用）。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引用以提升效率。
按顺序访问范围数据是很快的，这有两个原因。第一，顺序IO不需要多次磁盘寻道，所以比随机IO要快很多。第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且GROUP BY 查询也无须再做排序和将行按组进行聚合计算了。
索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查找行。这避免了大量的单行访问。

编写查询语句时应尽可能选择合适的索引避免单行查找、尽可能地使用数据原声顺序从而避免额外的排序操作，尽可能使用索引覆盖查询。

查询性能优化

链接：EverNote

EXPLAIN

链接：EverNote

MySQL 高级特性

高级特性包含：分区、视图、外键约束等。链接：EverNote

示例

我们以短信验证登录这一具体功能，来说明怎样建立一个高性能的数据模型。

具体业务逻辑：
用户点击获取验证码，客户端调用获取短信接口
获取短信接口内部调用短信服务商的接口，并向短信日志表中插入一条记录
用户收到验证码，并输入验证码进行验证，客户端调用验证短信接口
验证短信接口接收客户端传参，进行判断该用户是否通过验证
验证逻辑：手机号+验证码+是否在有效期
根据上述业务逻辑创建逻辑


CREATE TABLE sms_log (
  id            INT UNSIGNED       NOT NULL AUTO_INCREMENT
  COMMENT '自增主键',
  phone         BIGINT UNSIGNED    NOT NULL DEFAULT 0
  COMMENT '手机号码',
  code          MEDIUMINT UNSIGNED NOT NULL DEFAULT 0
  COMMENT '短信验证码',
  send_status   TINYINT UNSIGNED   NOT NULL DEFAULT 0
  COMMENT '是否发送',
  verify_status TINYINT UNSIGNED   NOT NULL DEFAULT 0
  COMMENT '是否被验证',
  ctime         TIMESTAMP          NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '创建时间',
  mtime         TIMESTAMP          NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  COMMENT '最后修改时间',
  PRIMARY KEY (id),
  KEY idx_pcc(phone, code, ctime)
)
  ENGINE = innodb
  DEFAULT CHARSET = utf8
  COMMENT '短信日志表';