MySQL数据库使用规范
一.建表规约
1.【强制】表达是与否概念的字段，必须使用is_xxx的方式命名，数据类型是unsigned tinyint
(1表示是，0表示否)
说明：任何字段如果为非负数，必须是unsigned
正例：表达逻辑删除的字段名is_deleted,，1表示删除，0表示未删除
2.【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
正例：getter_admin，task_config，level3_name
反例：GetterAdmin，taskConfig，level_3_name
3.【强制】表名不使复数名词。
说明：表名应该仅仅表示表里面的实体内容，不应该表示实体数量。
4.【强制】禁用保留字，如desc、range、match、delayed等，请参考MySQL官方保留字。
5.【强制】主键索引名为pk_字段名；唯一索引名为uk_字段名；普通索引名为idx_字段名。
说明：pk_即primary key；uk_即unique key；idx_即index的简称。
6.【强制】小数类型为decimal，禁止使用float和double。
说明：float和double在存储的时候，存在精度损失的问题，很可能在值比较的时候得到不正确的结果。如果存储的数据范围超过decimal的范围，建议将数据拆成整数和小数分开存储。
7.【强制】varchar是可变长字符串，不预先分配存储空间，长度不要超过5000，如果存储长度大于此值，定义字段类型为text，独立出来一张表，用主键来对应，避免影响其他字段索引效率。
8.【强制】如果存储的字符串长度几乎相等，使用char定长字符串类型。
9.【强制】表必备三字段：id，create_time，update_time,且类型均为datetime类型
10.【推荐】表的名称最好是加上“业务名称_表的作用”。
11.【推荐】库名与应用名称尽量保持一致。
12.【推荐】如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。
13.【推荐】字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：
1）不是频繁修改的字段。
2）不是varchar超长字段，更不能是text字段。
正例：商品类目名称使用频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存储类目名称，避免关联查询。
14.【推荐】单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。
说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。
15.【参考】合适的存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。
二.索引规约
1.【强制】业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
说明：不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查询速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。
2.【强制】超过三个表禁止join。需要join的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引。
说明：即使双表join也要注意表索引、SQL性能。
3.【强制】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索
引，根据实际文本区分度决定索引长度即可。
说明：索引长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引区分度会高达90%以上，可以使用count(distinct left(列名，索引长度))/count(*)的区分度来确定。
4.【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
说明：索引文件具有B-tree的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
5.【推荐】如果有order by的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。
正例：where a=? and b=? order by c; 索引：a_b_c
反例：索引中有范围查找，那么索引有序性无法利用，如 where a>10 order by b; 索引
a_b无法排序。
6.【推荐】利用覆盖索引来进行查询操作，避免回表。
说明：如果一本书需要知道第11章是什么标题，会翻开第11章对应的那一页吗？目录浏览一下就好，这个目录就是起覆盖索引的作用。
正例：能够建立索引的种类：主键索引、唯一索引、普通索引，而覆盖索引是一种查询的效果，利用explain的结果，extra列会出现：using index。
7.【推荐】利用延迟关联或者子查询优化超多分页场景。
说明：MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。
正例：先快速定位需要获取的id段，然后在关联：
select a.* from 表 1 a，（select id from 表 1 where 条件 limit 100000，20）b where a.id=b.id
8.【推荐】SQL性能优化的目标：至少要达到range级别，要求是ref级别，如果可以是consts最好。
说明：
1）consts单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
2）ref指的是使用普通的索引（normal index）。
3）range对索引进行范围检索。
反例：explain表的结果，type=index，索引物理文件全扫描，速度非常慢，这个index级别比较range还低，与全表扫描是小巫见大巫。
9.【推荐】建组合索引的时候，区分度最高的在左边。
正例：如果where a=? and b=?, 通过a=？查询几乎接近唯一值，那么只需要单建idx_a索引即可。
说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如
where a>? and b=? 那么及时a的区分度更高，也必须把b放在索引的最前列。
10.【推荐】防止因为字段类型不同造成的隐式转换，导致索引失效。
11.【参考】创建索引避免有如下极端误解：
1）宁滥勿缺。误认为一个查询就需要建一个索引。
2）宁缺勿滥。误认为索引会消耗空间、严重拖慢更新和新增速度。
3）抵制唯一索引。误认为业务的唯一性一律需要在应用层通过“先查后插”方式能解决。
三.SQL语句
1.【强制】不要使用count(列名)或count(常量)来替代count(*),count(*)是SQL92定义的
标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关。
说明：count(*)会统计值为NULL的行，而count(列名)不会统计此列值为NULL值的行。
2.【强制】count(distinct col)计算该列除NULL之外的不重复行数，注意count(distict
col1,col2)如果其中一列全为NULL，那么即使另一列有不同的值，也返回为0。
3.【强制】当某一列的值全是NULL时，count(col)的返回结果为0，但sum(col)的返回结
果为NULL，因此使用sum()时需要注意NPE问题。
正例：可以使用如下方式来避免sum的NPE问题：select if (isnull(sum(g)), 0, sum(g)) from table；
4.【强制】使用ISNULL()来判断是否为NULL值。注意：NULL与任何值的直接比较都为NULL。
说明：
1）NULL<>NULL的返回结果是NULL，而不是false。
2）NULL=NULL的返回结果是NULL，而不是true。
3）NULL<>1的返回结果是NULL，而不是true。
5.【强制】在代码中写分页查询逻辑时，若count为0应直接返回，避免执行后面的分页语句。
6.【强制】不得使用外键与级联，一切外键概念必须在应用层解决。
说明：（概念解释）学生表中的student_id是主键，那么成绩表中的student_id则为外键。如果更新学生表中的student_id,同时触发成绩表中的student_id更新，则为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。
7.【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
8.【强制】数据订正时，删除和修改记录时，要先select，避免出现误删除，确认无误才能执行更新语句。
9.【推荐】in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，控制在1000个之内。
10.【参考】如果有全球化需要，所有的字符存储与表示，均以utf-8编码，注意字符统计函数的区别。
说明：
select length(“轻松工作”)；返回为12
select character_length(“轻松工作”)；返回为4
如果要使用表情，那么使用utfmb4来进行存储，注意它与utf-8编码的区别。
11.【参考】 TRUNCATE TABLE 比 DELETE速度快，且使用的系统和是去日志资源少，但TRUNCATE无事务且不触发trigger，有可能造成事故，故不建议在开发代码中使用此语句。
说明：TRUNCATE TABLE 在功能上与不带WHERE子句的DELETE语句相同。
四.值得推荐的一些建议：
库表规范：
1. 库名、表名、字段名必须使用小写字母，库名须以有业务相关开头，如pt_，ns_等，并采用下划线分割
2. 所有mysql表必须为Innodb引擎，加表语句禁止出现DROP TABLE IF EXISTS 判断字句
3. 表字符集统一使用UTF8或更长支持的的utf8mb4，表和字段需要有注释
4. 表字段必须有默认值+ NOT NULL，int等数字类型默认值推荐给0，varchar等字符类型默认值给''
5. 控制单表字段数量，字段上限30左右
6. 表字段不得使用text blob等大字段，如有特殊需要，需要负责人进行沟通
7. 表单行总宽度不得超过8000字节，单表数据量控制在5000W行以内
8. 表主键需要设置为 无业务相关的自增长的int，如写入频繁则设置成bigint
9. 不使用强制外键约束
10. 不建议使用存储过程，视图，禁止使用触发器，事件
11. 数据库中不允许存储明文密码
12. 表一般不做分区表，如有需求需要提前与DBA沟通
13. 数据库服务器一般只做数据库使用，不允许跑其他应用
索引规范：
1. 任何新的select,update,delete上线，都要先explain，看索引使用情况
2. 尽量避免extra列出现：Using File Sort，Using Temporary，rows超过1000的要谨慎上线。
3. 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 列上的索引，哪些列或常量被用于查找索引列上的值
rows：根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
Extra：
Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”
Using index：表示使用索引，如果只有 Using index，说明他没有查询到数据表，只用索引表就完成了这个查询，这个叫覆盖索引。如果同时出现Using where，代表使用索引来查找读取记录， 也是可以用到索引的，但是需要查询到数据表。
Using where：表示条件查询，如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有需要的数据，则会出现 Using where。如果type列是ALL或index，而没有出现该信息，则你有可能在执行错误的查询：返回所有数据。
4. 索引个数限制
索引是双刃剑，会增加维护负担，增大IO压力，索引占用空间是成倍增加的
单张表的索引数量控制在5个以内，或不超过表字段个数的20%。若单张表多个字段在查询需求上都要单独用到索引，需要经过DBA评估。
避免冗余索引
索引尽量建在选择性高的列上
最左前缀原则
合理使用覆盖索引减少IO
尽量不要在频繁更新的列上创建索引
业务查询需要有索引，需要提前规划好索引的覆盖程度，可以找DBA协助相关策略
索引命名需以ix_ 开头，下划线后跟上字段，以_分隔，唯一索引以ux_ 开头
数据库权限规范
1. 一个帐号对应一个数据库，帐号有增删改查权限，如有DDL需求，再做权限增加
2. 生产环境 禁止直接从办公网访问，如有查询需求，可以提供有只读权限的phpmyadmin使用
SQL使用规范
1. 杜绝直接 SELECT * 读取全部字段，即使需要所有字段。减少网络带宽消耗，能有效利用覆盖索引，表结构变更对程序基本无影响
2. 能确定返回结果只有一条时，使用 limit 1。在保证数据不会有误的前提下，能确定结果集数量时，多使用limit，尽快的返回结果。
3. 小心隐式类型转换
转换规则
1. 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换
2. 两个参数都是字符串，会按照字符串来比较，不做类型转换
3. 两个参数都是整数，按照整数来比较，不做类型转换
4. 十六进制的值和非数字做比较时，会被当做二进制串
5. 有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
6. 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
7. 所有其他情况下，两个参数都会被转换为浮点数再进行比较。
8. 如果一个索引建立在string类型上，如果这个字段和一个int类型的值比较，符合第 g 条。如f_phone定义的类型是varchar，但where使用f_phone in (098890)，两个参数都会被当成成浮点型。发生这个隐式转换并不是最糟的，最糟的是string转换后的float，mysql无法使用索引，这才导致了性能问题。如果是 f_user_id = ‘1234567’ 的情况，符合第 b 条,直接把数字当字符串比较。
4. 禁止在where条件列上使用函数。会导致索引失效，如lower(email)，f_qq % 4。可放到右边的常量上计算。返回小结果集不是很大的情况下，可以对返回列使用函数，简化程序开发
5. 使用like模糊匹配，%不要放首位。会导致索引失效，有这种搜索需求是，考虑其它方案，如sphinx全文搜索
6. 涉及到复杂sql时，务必先参考已有索引设计，先explain。先explain的好处是可以为了利用索引，增加更多查询限制条件。
7. 简单SQL拆分，不以代码处理复杂为由。比如 OR 条件： f_phone=’10000’ or f_mobile=’10000’，两个字段各自有索引，但只能用到其中一个。可以拆分成2个sql，或者union all。
8. 使用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，那么可以用到完整的索引。这种情况可能会在字段冗余设计（反范式）时出现
9. 正确选取inner join和left join。少用子查询，改用join。小于5.6版本时，子查询效率很低，不像Oracle那样先计算子查询后外层查询。5.6版本开始得到优化
10. 考虑使用union all，少使用union，注意考虑去重。union all不去重，而少了排序操作，速度相对比union要快，如果没有去重的需求，优先使用union all。如果UNION结果中有使用limit，在2个子SQL可能有许多返回值的情况下，各自加上limit。如果还有order by，请找DBA。
11. IN的内容尽量不超过200个。超过500个值使用批量的方式，否则一次执行会影响数据库的并发能力，因为单SQL只能且一直占用单CPU，而且可能导致主从复制延迟
12. 拒绝大事务。比如在一个事务里进行多个select，多个update，如果是高频事务，会严重影响MySQL并发能力，因为事务持有的锁等资源只在事务rollback/commit时才能释放。但同时也要权衡数据写入的一致性。
13. 避免使用is null, is not null这样的比较
14. order by .. limit。这种查询更多的是通过索引去优化，但order by的字段有讲究，比如主键id与f_time都是顺序递增，那就可以考虑order by id而非 f_time 。
15. c1 < a order by c2。与上面不同的是，order by之前有个范围查询，由前面的内容可知，用不到类似(c1,c2)的索引，但是可以利用(c2,c1)索引。另外还可以改写成join的方式实现。
16. 分页优化
- 建议使用合理的分页方式以提高分页效率，大页情况下不使用跳跃式分页
- 假如有类似下面分页语句:
`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`
17. count计数。首先count(*)、count(1)、count(col1)是有区别的，count(*)表示整个结果集有多少条记录，count(1)表示结果集里以primary key统计数量，绝大多数情况下count(*)与count(1)效果一样的，但count(col1)表示的是结果集里 col1 列 NOT null 的记录数。优先采用count(*)。大数据量count是消耗资源的操作，甚至会拖慢整个库，查询性能问题无法解决的，应从产品设计上进行重构。例如当频繁需要count的查询，考虑使用汇总表
18. 遇到distinct的情况，group by方式可能效率更高。
19. delete,update语句改成select再explain。select最多导致数据库慢，写操作才是锁表的罪魁祸首
20. 减少与数据库交互的次数，尽量采用批量SQL语句。
- INSERT ... ON DUPLICATE KEY UPDATE ...，插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE，如果不重复则直接插入，影响1行。
- REPLACE INTO类似，但它是冲突时删除旧行。
- INSERT IGNORE相反，保留旧行，丢弃要插入的新行。
- INSERT INTO VALUES(),(),()，合并插入。
21. 杜绝危险SQL
22. 去掉where 1=1 这样无意义或恒真的条件，对SQL进行参数化使用。
23. SQL中不允许出现DDL语句。一般也不给予create/alter这类权限。