MySQL 开发规范
一、表设计
1、库名、表名、字段名必须使用小写字母，“_”分割。
a) MySQL 有配置参数 lower_case_table_names，不可动态更改，linux 系统默认为 0，即库表名以实际情况存储，大小写敏感。如果是 1，以小写存储，大小写不敏感。如果是 2，以实际情况存储，但以小写比较。
b)如果大小写混合用，可能存在 abc,Abc,ABC 等多个表共存，容易导致混乱。
c) 字段名显示区分大小写，但实际使用不区分，即不可以建立两个名字一样但大小写不一样的字段。
d)为了统一规范， 库名、表名、字段名使用小写字母。
2、库名、表名、字段名必须不超过 12 个字符。
库名、表名、字段名支持最多 64 个字符，但为了统一规范、易于辨识以及减少传输量，必须不超过
12字符。
3、库名、表名、字段名见名知意,建议使用名词而不是动词。
a) 例如用户评论可用表名 usercomment 或者 comment。
b)库表是一种客观存在的事物，一种对象，所以建议使用名词。
4、建议使用 InnoDB 存储引擎。
a) 5.5 以后的默认引擘，支持事务，行级锁，更好的恢复性，高并发下性能更好，对多核，大内存， ssd 等硬件支持更好。
b)具体比较可见附件的官方白皮书。
5、存储精确浮点数必须使用 DECIMAL 替代 FLOAT 和 DOUBLE。
a)mysql 中的数值类型（不包括整型）：
浮点数： float （单精度） ， double 或 real （双精度）
定点数： decimal 或 numeric
单精度浮点数的有效数字二进制是 24 位，按十进制来说，是 8 位；双精度浮点数的有效数字二进制是 53 位，按十进制来说，是 16 位
一个实数的有效数字超过 8 位，用单精度浮点数来表示的话，就会产生误差！同样，如果一个实数的有效数字超过 16 位，用双精度浮点数来表示，也会产生误差
b)标准的计算机浮点数，在内部是用二进制表示的，但在将一个十进制数转换为二进制浮点数时，也会造成误差，原因是不是所有的数都能转换成有限长度的二进制数。
即一个二进制可以准确转换成十进制，但一个带小数的十进制不一定能够准确地用二进制来表示。
实例：
drop table if exists t;
create table t(value float(10,2));
insert into t values(131072.67),(131072.68);
select value from t;
+-----------+
| value |
+-----------+
| 131072.67 |
| 131072.69 |
6、建议使用 UNSIGNED 存储非负数值。
同样的字节数，存储的数值范围更大。如 smallint 有符号为–32768 -32767，无符号为 0 - 65535
7、建议使用varchar 存储 IPV4。
8、整形定义中不添加长度，比如使用 INT，而不是 INT(4)。
注意数值类型括号后面的数字只是表示宽度而跟存储范围没有关系，比如 INT(3)默认显示 3 位，空格补齐，超出时正常显示，python、java 客户端等不具备这个功能。
9、使用短数据类型，比如取值范围为 0-80 时，使用 SMALLINT UNSIGNED。
10、不建议使用 ENUM、SET 类型和TINYINT，使用 SMALLINT来代替。
a)ENUM，有三个问题：添加新的值要做 DDL，默认值问题(将一个非法值插入 ENUM(也就是说，允许的值列之外的字符串)，将插入空字符串以作为特殊错误值)，索引值问题（插入数字实际是插入索引对应的值）
b)由于mysql没有boolean类型，所以会用到TINYINT[1]类型来表示，在mysql中
boolean=tinyint[1],在别的数据库语言中也会被解读成是true/false,为了避免不必要的麻烦，在我们系
统中一律不使用tinyint,使用smallint来代替
（详解可以参阅mysql的tinyint(1).docx）
实例：
drop table if exists t;
create table t(sex enum('0','1'));
insert into t values(1);
insert into t values('3');
select * from t;
+------+
| sex |
+------+
| 0 |
| |
+------+
2 rows in set (0.00 sec)
11、尽可能不使用 TEXT、BLOB 类型。
a)索引排序问题，只能使用 max_sort_length 的长度或者手工指定 ORDER BY SUBSTRING(column, length)的长度来排序
b)Memory 引擘不支持 text,blog 类型，会在磁盘上生成临时表
c)可能浪费更多的空间
d)可能无法使用 adaptive hash index
e)导致使用 where 没有索引的语句变慢
12、VARCHAR(N)，N 表示的是字符数不是字节数，比如 VARCHAR(255)，可以最大可存
储255 个汉字，需要根据实际的宽度来选择 N。
13、VARCHAR(N)，N 尽可能小，因为 MySQL 一个表中所有的 VARCHAR 字段最大长度是 65535 个字节，进行排序和创建临时表一类的内存操作时，会使用 N 的长度申请内存。
VARCHAR 中会产生额外存储吗？
VARCHAR(M)，如果 M<256 时会使用一个字节来存储长度，如果 M>=256 则使用两个字节来存储
长度。
14、 如果表里有字段需要存储表情使用表字符集选择 Utf8mb4
a)Utf8mb4兼容utf8,且比utf8能表示更多的字符，例 存储emoji表情，utf8是无法存储的，
Utf8mb4则可以做到
b)统一，不会有转换产生乱码风险
c)其他地区的用户（美国、印度、台湾）无需安装简体中文支持，就能正常看您的文字，并且不会出现乱码
d)mysql 5.5.3版本以后支持Utf8mb4
15、存储年使用 YEAR 类型。
16、存储日期使用 DATE 类型。
17、建议字段设置不允空。
18、建议字段默认值不要为NUll。
a）如果 null 字段被索引，需要额外的 1 字节
b）使索引，索引统计，值的比较变得更复杂
c）可用 0，''代替
d）如果是索引字段，一定要定义为 not null
19、所有表、字段均应用 comment 列属性来描述此表、字段所代表的真正含义，如枚举值则建议将该字段中使用的内容都定义出来。
20、如无说明，表中的第一个id字段一定是主键且为自动增长，禁止在非事务内作为上下文作为条件进行数据传递。禁止使用varchar类型作为主键语句设计。
21、如无说明，表必须包含create_time和modify_time字段，即表必须包含记录创建时间和修改时间的字段
22、如无说明，表必须包含is_del，用来标示数据是否被删除，原则上数据库数据不允许物理删除。
23、禁止在数据库中使用 VARBINARY、BLOB 存储图片、文件等。
采用分布式文件系统更高效
24、表结构变更需要通知 DBA 审核。
二、索引
1、 为什么 MySQL 的性能依赖于索引？
MySQL 的查询速度依赖良好的索引设计，因此索引对于高性能至关重要。合理的索引会加快查询速度（包括UPDATE和DELETE的速度，MySQL会将包含该行的page加载到内存中，然后进行UPDATE或者 DELETE 操作），不合理的索引会降低速度。
MySQL 索引查找类似于新华字典的拼音和部首查找，当拼音和部首索引不存在时，只能通过一页一页的 翻页来查找。当 MySQL 查询不能使用索引时，MySQL 会进行全表扫描，会消耗大量的 IO
2、非唯一索引必须按照“idx_字段名称字段名称[字段名]”进行命名。
3、唯一索引必须按照“uniq_字段名称字段名称[字段名]”进行命名。
4、索引名称必须使用小写。
5、索引中的字段数建议不超过 5 个。
InnoDB 的 secondary index 使用 b+tree 来存储，因此在 UPDATE、DELETE、INSERT 的时候需要对 b+tree 进行调整，过多的索引会减慢更新的速度。
6、单张表的索引数量控制在 5 个以内。
7、唯一键由 3 个以下字段组成，并且字段都是整形时，使用唯一键作为主键。
8、没有唯一键或者唯一键不符合 5 中的条件时，使用自增（或者通过发号器获取）id 作为
主键。
9、唯一键不和主键重复。
10、索引字段的顺序需要考虑字段值去重之后的个数，个数多的放在前面（复合索引的最左原则）。
11、ORDER BY，GROUP BY，DISTINCT 的字段需要添加在索引的后面。
12、使用 EXPLAIN 判断 SQL 语句是否合理使用索引，尽量避免 extra 列出现：Using File Sort，Using Temporary。
EXPLAIN 语句（在 MySQL 客户端中执行）可以获得 MySQL 如何执行 SELECT 语句的信息。通过对 SELECT 语句执行 EXPLAIN，可以知晓 MySQL 执 行该 SELECT 语句时是否使用了索引、全表扫描、临时表、排序等信息。尽量避免 MySQL 进行全表扫描、使用临时表、排序等。详见官方文档。
13、UPDATE、DELETE 语句需要根据 WHERE 条件添加索引。
14、不建议使用%前缀模糊查询，例如 LIKE “%weibo”。
会导致全表扫描
15合理创建联合索引（避免冗余），(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。
从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分
索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效
16 经常join其他表，在连接字段上应该建立索引
经常出现在Where子句中的字段，特别是大表的字段，应该建立索引
复合索引的几个字段是否经常同时以AND方式出现在Where子句中,单字段查询是否极少甚至没有,如果是，则可以建立复合索引；否则考虑单字段索引
17、SQL 变更需要确认索引是否需要变更并通知 DBA。
三、 SQL 语句
1、使用 prepared statement，可以提供性能并且避免 SQL 注入。
2、SQL 语句中 IN 包含的值不应过多。
3、UPDATE语句不使用 LIMIT。
a)可能导致主从数据不一致
b)会记录到错误日志，导致日志占用大量空间
4、WHERE 条件中必须使用合适的类型，避免 MySQL 进行隐式类型转化。
因为 MySQL 进行隐式类型转化之后，可能会将索引字段类型转化成=号右边值的类型，导致使用不
到索引，原因和避免在索引字段中使用函数是类似的。（字符型应该加引号，数值型不加引号）
例 select * from user where mobile=159000000,mobile字段类型是字符型，值却给了数值型
5、SELECT、INSERT 语句必须显式的指明字段名称，不使用 SELECT *。
增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前段也需要更新。
6、WHERE 条件中的非等值条件（IN、BETWEEN、<、<=、>、>=）会导致后面的条件使用不了索引。
7、避免在 SQL 语句进行数学运算或者函数运算，容易将业务逻辑和 DB 耦合在一起。
8、INSERT 语句使用 batch 提交（INSERT INTO table VALUES(),(),()……），values 的个数不应过多。
9、多表连接时，尽量小表驱动大表，即小表 left join 大表。
10、使用合理的 SQL 语句减少与数据库的交互次数。
使用下面的语句来减少和 db 的交互次数：
INSERT ... ON DUPLICATE KEY UPDATE
REPLACE INTO
INSERT IGNORE
INSERT INTO VALUES(),(),()
UPDATE … WHERE ID IN(10,20,50,…)
11、不使用 ORDER BY RAND()，使用其他方法替换。
因为 ORDER BY rand()会将数据从磁盘中读取，进行排序，会消耗大量的 IO 和 CPU，可以在程序中获取一个 rand 值，然后通过在从数据库中获取对应的值
12、 InnoDB 存储引擎为什么避免使用 COUNT(*)?
InnoDB 表避免使用 COUNT(*)操作，计数统计实时要求较强可以使用 memcache 或者 redis，非实时统计可以使用
13 不使用负向查询，如not in/like 无法使用索引，导致全表扫描 全表扫描导致buffer pool利用率降低
14、使用EXPLAIN诊断，避免生成临时表
EXPLAIN语句（在MySQL客户端中执行）可以获得MySQL如何执行SELECT语句的信息。通过对SELECT语句执行EXPLAIN，可以知晓MySQL执行该SELECT语句时是否使用了索引、全表扫描、临时表、排序等信息。尽量避免MySQL进行全表扫描、使用临时表、排序等。详见官方文档。
15、禁止单条SQL语句同时更新多个表
16 禁止使用delete语句
四、分表
1、每张表数据量建议控制在 2000w 以下。
2、推荐使用 求余（或者类似的算术算法）进行分表，表名后缀使用数字，数字必须从 0 开始并等宽，比如分 100 张表，后缀从 00-99。
3、使用时间分表，表名后缀必须使用特定格式，比如按日散表 user_20110209、按月分表user_201102。
