mysql规范

命名规范

库名、表名、字段名必须使用小写字母，并采用下划线分割

• MySQL有配置参数lower_case_table_names=1，即库表名以小写存储，大小写不敏感。如果是0，则库表名以实际情况存储，大小写敏感；如果是2，以实际情况存储，但以小写比较
• 如果大小写混合使用，可能存在abc，Abc，ABC等多个表共存，容易导致混乱
• 字段名显示区分大小写，但实际使⽤时不区分，即不可以建立两个名字一样但大小写不一样的字段
• 为了统一规范， 库名、表名、字段名使用小写字母

库名、表名、字段名禁止超过32个字符，需见名知意

• 库名、表名、字段名支持最多64个字符，但为了统一规范、易于辨识以及减少传输量，禁止超过32个字符

临时库、表名须以tmp加日期为后缀

• 如 t_crm_relation_tmp0425。备份表也类似，形如 _bak20160425

按日期时间分表须符合_YYYY[MM][DD]格式

• 这也是为将来有可能分表做准备的，如t_crm_ec_record_201403，但像 t_crm_contact_at201506就打破了这种规范,不具有时间特性的，直接以 t_tbname_001 这样的方式命名

库表基础规范

使用Innodb存储引擎

• 5.5版本开始mysql默认存储引擎就是InnoDB，5.7版本开始，系统表都放弃MyISAM了

表字符集统一使用UTF8

• UTF8字符集存储汉字占用3个字节，存储英文字符占用一个字节
• 校对字符集使用默认的 utf8_general_ci
• 如果遇到EMOJ等表情符号的存储需求，可申请使用UTF8MB4字符集

所有表都要添加注释

• 所有的字段添加注释
• 类status型需指明主要值的含义，如”0-离线，1-在线”

控制单表字段数量

• 单表字段数上限30左右，再多的话考虑垂直分表，一是冷热数据分离，二是大字段分离，三是常在一起做条件和返回列的不分离
• 表字段控制少而精，可以提高IO效率，内存缓存更多有效数据，从而提高响应速度和并发能力，后续 alter table 也更快

所有表都必须要显式指定主键

• 主键尽量采用自增方式，InnoDB表实际是一棵索引组织表，顺序存储可以提高存取效率，充分利用磁盘空间。还有对一些复杂查询可能需要自连接来优化时需要用到
• 自增都定义为id bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
• 需要全局唯一主键时，使用外部发号器ticket server,或者分布式id算法
• 如果没有主键或唯一索引，update/delete是通过所有字段来定位操作的行，相当于每行就是一次全表扫描

避免使用存储过程、视图，禁止使用触发器、事件

• 将业务放到代码中，mysql只做简单的存储

单表数据量控制在5000w以内

字段规范

char、varchar、text等字符串类型定义

• 对于长度基本固定的列，如果该列恰好更新又特别频繁，适合char
• varchar虽然存储变长字符串，但不可太小也不可太大。UTF8最多能存21844个汉字，或65532个英文
• TEXT类型与VARCHAR都类似，存储可变长度，最大限制也是2^16，但是它20bytes以后的内容是在数据页以外的空间存储（row_format=dynamic），对它的使用需要多一次寻址，没有默认值。一般用于存放容量平均都很大、操作没有其它字段那样频繁的值
  【对于列数量大的单独和主键建立一张表】
• 不用blob

int、tinyint、decimal等数字类型定义

• 使用tinyint来代替 enum和boolean 【对存储不关心用int也可以】
• 使用 UNSIGNED 存储非负数值
• int使用固定4个字节存储，int(11)与int(4)只是显示宽度的区别
• 使用Decimal 代替float/double存储精确浮点数
  对于货币、金额这样的类型，使用decimal，如 decimal(9,2)

timestamp与date time, DATE选择

• datetime 和 timestamp类型所占的存储空间不同，前者8个字节，后者4个字节，这样造成的后果是两者能表示的时间范围不同。前者范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59，后者范围为 1970-01-01 08:00:01 到 2038-01-19 11:14:07 。所以 TIMESTAMP 支持的范围比 DATATIME 要小
• timestamp可以在insert/update行时，自动更新时间字段（如 f_set_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP），但一个表只能有一个这样的定义
• timestamp显示与时区有关，内部总是以 UTC 毫秒 来存的。还受到严格模式的限制
• 优先使用timestamp，date time也没问题
• 只有日期使用DATE
• where条件里不要对时间列上使用时间函数, 索引无效

建议字段都定义为NOT NULL

• 如果是索引字段，一定要定义为not null 。因为null值会影响cordinate统计，影响优化器对索引的选择
• null 字段联合索引 无效
• 都必须加default

同一意义的字段定义必须相同

• 不同表中都有 user_id 字段，那么它的类型、字段长度要设计成一样

索引规范

任何新的select,update,delete上线，都要先explain，看索引使用情况

• 尽量避免extra列出现：Using File Sort，Using Temporary，rows超过1000的要谨慎上线。

explain解读

• type：ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）
• possible_keys：指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
• key：表示MySQL实际决定使用的键（索引），如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX
• ref：表示选择 key 列上的索引，哪些列或常量被用于查找索引列上的值
• ows：根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
• Extra
  1. Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
  2. Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”

索引个数限制

• 索引是双刃剑，会增加维护负担，增大IO压力，索引占用空间是成倍增加的，还影响更新, 删除效率
• 单张表的索引数量控制在5个以内，或不超过表字段个数的20%。若单张表多个字段在查询需求上都要单独用到索引，需要经过DBA评估

避免冗余索引

索引尽量建在选择性高的列上

• 不在低基数列上建立索引，例如性别、类型。但有一种情况，idx_feedbackid_type (f_feedback_id,f_type)，如果经常用 f_type=1 比较，而且能过滤掉90%行，那这个组合索引就值得创建。有时候同样的查询语句，由于条件取值不同导致使用不同的索引，也是这个道理
• 索引选择性计算方法（基数 ÷ 数据行数）
  Selectivity = Cardinality / Total Rows = select count(distinct col1)/count(*) from tbname，越接近1说明col1上使用索引的过滤效果越好
• 走索引扫描行数超过30%时，改全表扫描

最左前缀原则

• mysql使用联合索引时，从左向右匹配，遇到断开或者范围查询时，无法用到后续的索引列
  比如索引idx_c1_c2_c3 (c1,c2,c3)，相当于创建了(c1)、(c1,c2)、(c1,c2,c3)三个索引，where条件包含上面三种情况的字段比较则可以用到索引，但像 where c1=a and c3=c 只能用到c1列的索引，像 c2=b and c3=c等情况就完全用不到这个索引
• 遇到范围查询(>、<、between、like)也会停止索引匹配，比如 c1=a and c2 > 2 and c3=c，只有c1,c2列上的比较能用到索引
• where条件里面字段的顺序与索引顺序无关，mysql优化器会自动调整顺序

合理使用覆盖索引减少IO

• INNODB存储引擎中，secondary index(非主键索引，又称为辅助索引、二级索引)没有直接存储行地址，而是存储主键值
• 如果用户需要查询secondary index中所不包含的数据列，则需要先通过secondary index查找到主键值，然后再通过主键查询到其他数据列，因此需要查询两次。覆盖索引则可以在一个索引中获取所有需要的数据列，从而避免回表进行二次查找，节省IO因此效率较高
• 例如SELECT email，uid FROM user_email WHERE uid=xx，如果uid不是主键，适当时候可以将索引添加为index(uid，email)，以获得性能提升

尽量不要在频繁更新的列上创建索引

• 如不在定义了 ON UPDATE CURRENT_STAMP 的列上创建索引，维护成本太高（好在mysql有insert buffer，会合并索引的插入）

SQL设计

SELECT * 读取全部字段 [读什么取什么特别有大数据量的字段时候]

• 即使需要所有字段，减少网络带宽消耗，能有效利用覆盖索引，表结构变更对程序基本无影响

能确定返回结果只有一条时，使用 limit 1

• 在保证数据不会有误的前提下，能确定结果集数量时，多使用limit，尽快的返回结果

禁止在where条件列上使用函数

• 会导致索引失效，如lower(email)，f_qq % 4。可放到右边的常量上计算
• 返回小结果集不是很大的情况下，可以对返回列使用函数，简化程序开发

使用like模糊匹配，%不要放首位

• 会导致索引失效，有这种搜索需求是，考虑搜索方案

涉及到复杂sql时，务必先参考已有索引设计，先explain

• 简单SQL拆分，不以代码处理复杂为由
• 比如 OR 条件： f_phone=’10000’ or f_mobile=’10000’，两个字段各自有索引，但只能用到其中一个。可以拆分成2个sql，或者union all
• 先explain的好处是可以为了利用索引，增加更多查询限制条件

使用join时，where条件尽量使用充分利用同一表上的索引

• 如 select t1.a,t2.b * from t1,t2 and t1.a=t2.a and t1.b=123 and t2.c= 4 ，如果t1.c与t2.c字段相同，那么t1上的索引(b,c)就只用到b了。此时如果把where条件中的t2.c=4改成t1.c=4，那么可以用到完整的索引
• 这种情况可能会在字段冗余设计（反范式）时出现
• 正确选取inner join和left join

少用子查询，改用join

• 小于5.6版本时，子查询效率很低，不像Oracle那样先计算子查询后外层查询。5.6版本开始得到优化

考虑使用union all，少使用union，注意考虑去重

• union all不去重，而少了排序操作，速度相对比union要快，如果没有去重的需求，优先使用union all
• 如果UNION结果中有使用limit，在2个子SQL可能有许多返回值的情况下，各自加上limit。如果还有order by

IN的内容尽量不超过200个

• 超过500个值使用批量的方式，否则一次执行会影响数据库的并发能力，因为单SQL只能且一直占用单CPU，而且可能导致主从复制延迟

拒绝大事务

• 比如在一个事务里进行多个select，多个update，如果是高频事务，会严重影响MySQL并发能力，因为事务持有的锁等资源只在事务rollback/commit时才能释放。但同时也要权衡数据写入的一致性

避免使用is null, is not null这样的比较

order by .. limit

• 这种查询更多的是通过索引去优化，但order by的字段有讲究，比如主键id与f_time都是顺序递增，那就可以考虑order by id而非 f_time

c1 < a order by c2

• 与上面不同的是，order by之前有个范围查询，由前面的内容可知，用不到类似(c1,c2)的索引，但是可以利用(c2,c1)索引。另外还可以改写成join的方式实现。

分页优化

• 建议使用合理的分页方式以提高分页效率，大页情况下不使用跳跃式分页
• 假如有类似下面分页语句:
  SELECT FROM table1 ORDER BY ftime DESC LIMIT 10000,10;
  这种分页方式会导致大量的io，因为MySQL使用的是提前读取策略
• 推荐分页方式：
  SELECT FROM table1 WHERE ftime < last_time ORDER BY ftime DESC LIMIT 10
  即传入上一次分页的界值
  SELECT * FROM table as t1 inner JOIN (SELECT id FROM table ORDER BY time LIMIT 10000，10) as t2 ON t1.id=t2.id

count计数

• 首先count()、count(1)、count(col1)是有区别的，count()表示整个结果集有多少条记录，count(1)表示结果集里以primary key统计数量，绝大多数情况下count()与count(1)效果一样的，但count(col1)表示的是结果集里 col1 列 NOT null 的记录数。优先采用count()
• 大数据量count是消耗资源的操作，甚至会拖慢整个库，查询性能问题无法解决的，应从产品设计上进行重构。例如当频繁需要count的查询，考虑使用汇总表
• 遇到distinct的情况，group by方式可能效率更高

delete,update语句改成select再explain

• select最多导致数据库慢，写操作才是锁表的罪魁祸首

减少与数据库交互的次数，尽量采用批量SQL语句

• INSERT ... ON DUPLICATE KEY UPDATE ...，插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE，如果不重复则直接插入，影响1行
• REPLACE INTO类似，但它是冲突时删除旧行。INSERT IGNORE相反，保留旧行，丢弃要插入的新行【replace into 删掉在新建 少用】
• INSERT INTO VALUES(),(),()，合并插入