MySQL开发规范_mysql单张表多个字段在查询需求上都要单独用到索引

一.命名规范

1. 库名、表名、字段名必须使用小写字母，并采用下划线分割
    为了统一规范， 库名、表名、字段名使用小写字母，禁用关键字（index,order等）
2. 前缀命令规范
   
   • 视图以view_开头，事件以event_开头，触发器以trig_开头，存储过程以proc_开头，函数以func_开头，应用上面禁用
   • 普通索引以idx_各个列名简称，唯一索引以uk_各个列名简称命名，中间用_隔开。如 idx_col1_col2_col3(col1,col2,col3)，如果列过长，用简写
   • 临时表以tmp_实体表名，线上禁用，备份表以bak_日期_实体表名，尽可能备份至HDFS
3. 库名、表名、字段名禁止超过32个字符，需见名知意
   
   • 库名、表名、字段名支持最多32个字符，但为了统一规范、易于辨识以及减少传输量，禁止超过32个字符，例：业务名称/实体_表作用
4. 按日期时间分表须符合_YYYYMMDD格式
   
   • 按月或日生成的表，以_YYYYMM[DD]方式命名。

二.库表基础规范

1. 使用Innodb存储引擎
   
   • 所有表必须使用默认存储引擎InnoDB。
2. 表编码方式统一使用UTF8或UTF8MB4的
   
   • 创建索引时utf8比utf8mb4少一个字节，所以明确没有emoj时，尽量使用utf8
3. 所有表都要添加注释
   
   • 所有字段必需要有注释，包括表注释，并标注简单意义
4. 控制单表字段数量
   
   • 单表字段数上限50左右，再多的话考虑垂直分表，一是冷热数据分离，二是大字段分离，三是常在一起做条件和返回列的不分离
   • 表字段控制少而精，可以提高IO效率，内存缓存更多有效数据，从而提高响应速度和并发能力，后续 alter table 也更快
5. 所有表都必须要显式指定主键
   
   • 双活的表必须禁用自增主键【snowflake】，InnoDB表实际是一棵索引组织表，顺序存储可以提高存取效率，充分利用磁盘空间。还有对一些复杂查询可能需要自连接来优化时需要用到
6. 不使用外键
   
   • 外键严重影响数据库性能，增加表维护复杂度，所以线上禁用外键
7. 字段名称统一
   
   • 每张表原则上必须含有gmt_create,gmt_modify，gmt_create为创建时间。gmt_modify属性为NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，gmt_create属性为NOT NULL DEFAULT CURRENT_TIMESTAMP，统一放至字段最后
   • 所有相同意义的表采用统一字段名称，并且字段类型一致，这样应用与维护人员见字段知其含义，方便理解
8. 简单单表数据量控制在1000w以内
9. 尽量保证单表存储空间小于10GB
10. 建表时不要加入COLLATE相关内容

三. 字段规范

1. char、varchar、text等字符串类型定义
   
   • 对于长度基本固定的列，如果该列恰好更新又特别频繁，适合char
   • varchar虽然存储变长字符串，但不可太小也不可太大。UTF8最多能存21844个汉字，或65532个英文
   • TEXT类型与VARCHAR都类似，存储可变长度，最大限制也是2^16，但是它20bytes以后的内容是在数据页以外的空间存储（row_format=dynamic），对它的使用需要多一次寻址，没有默认值
   • 把text/blob拆到另一个表中，如果只存不读的禁用，建议直接存HDFS
   • BLOB可以看出varbinary的扩展版本，内容以二进制字符串存储，无字符集，区分大小写，有一种经常提但不用的场景：不要在数据库里存储图片
   • 字符集为utf8，varchar类型最大只能创建索引为前255字节；字符集为utf8mb4，varchar类型最大只能创建索引为前191字节。所以字段造型时首选int，如果varchar时字段做查询的越小越好，长度不要超过191
2. int、tinyint、decimal等数字类型定义
   
   • 使用tinyint来代替 enum和booleanENUM类型在需要修改或增加枚举值时，需要在线DDL，成本较高；ENUM列值如果含有数字类型，可能会引起默认值混淆tinyint使用1个字节，一般用于status,type,flag的列
   • 建议使用 UNSIGNED 存储非负数值相比不使用 unsigned，可以扩大一倍使用数值范围
   • int使用固定4个字节存储，int(11)与int(4)只是显示宽度的区别
   • 使用int存储IPv4地址，可节省 9%+的数据存储空间
   • 使用Decimal 代替float/double存储精确浮点数对于货币、金额这样的类型，可以使用（货币使用bigint+币种+最小单位），如果精度不高，可以使用decimal(9,2)。float默认只能能精确到6位有效数字
3. timestamp与datetime选择
   
   • datetime 和 timestamp类型所占的存储空间不同，前者8个字节，后者4个字节，这样造成的后果是两者能表示的时间范围不同。前者范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59，后者范围为 1970-01-01 00:00:00 到 2037-12-31 23:59:59。所以 TIMESTAMP 支持的范围比 DATATIME 要小
   • timestamp可以在insert/update行时，自动更新时间字段（如 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP），但一个表只能有一个这样的定义
   • timestamp显示与时区有关，内部总是以 UTC 毫秒 来存的。还受到严格模式的限制
   • 优先使用timestamp，timestamp精度可以到秒后6位，即timestamp(6)
   • where条件里不要对时间列上使用时间函数
4. 建议字段都定义为NOT NULL加上default值
5. 类型选择原则
   
   • Int->char->varchar->text，从左到右性能越差

四.索引规范

1. 索引个数限制
   
   • 索引是双刃剑，会增加维护负担，增大IO压力，索引占用空间是成倍增加的
   • 单张表的索引数量控制在5个以内，索引的大小尽量比表小。若单张表多个字段在查询需求上都要单独用到索引，需要经过DBA评估。
2. 避免冗余索引
   
   • InnoDB表是一棵索引组织表，主键是和数据放在一起的聚集索引，普通索引最终指向的是主键地址，所以把主键做最后一列是多余的。如id作为主键，联合索引(user_id,id)上的id就完全多余
   • (a,b,c)、(a,b)，后者为冗余索引。可以利用前缀索引来达到加速目的，减轻维护负担
3. 索引创建原则
   
   • 尽可能选择过滤效果好的列上创建索引
   • 索引选择性计算方法（基数 ÷ 数据行数）Selectivity = Cardinality / Total Rows = select count(distinct col1)/count(*) from tbname，越接近1说明col1上使用索引的过滤效果越好
   • 如果某列为sex，数据倾斜比较大，male占比99%，female占比1%，而每次查询都为female，则需要创建该列的索引
4. 最左前缀原则
   
   • mysql使用联合索引时，从左向右匹配，遇到断开或者范围查询时，无法用到后续的索引列比如索引idx_c1c2c3 (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列上的比较能用到索引，(c1,c2,c3)排列的索引才可能会都用上
   • where条件里面字段的顺序与索引顺序无关，mysql优化器会自动调整顺序
   • mysql的optimizer_switch，可以配置icp，mrr等特性。mrr为多个字段做合并，如已存在c1,c2,c3索引，查询时c1=’a’ and/or c2=’b’，则会使用到索引合并，如果大部分查询没有c1和c2同时存在，则建议创建单列索引
5. 前缀索引
   
   • 前缀索引尽量第一列区分度高，这样如果优化器判断时会比较准确 ,A列1000个不同值，B列有100000个不同值，这样建议索引创建时idx_B_A(B,A)
6. 合理使用覆盖索引减少IO
   
   • INNODB存储引擎中，secondary index(非主键索引，又称为辅助索引、二级索引)没有直接存储行地址，而是存储主键值。如果用户需要查询secondary index中所不包含的数据列，则需要先通过secondary index查找到主键值，然后再通过主键查询到其他数据列，因此需要查询两次。覆盖索引则可以在一个索引中获取所有需要的数据列，从而避免回表进行二次查找，节省IO因此效率较高。例如SELECT email，uid FROM user_email WHERE uid=xx，如果uid不是主键，适当时候可以将索引添加为index(uid，email)，以获得性能提升。
7. 不要在频繁更新的列上创建索引

五. SQL设计

1. 杜绝直接 SELECT * 读取全部字段
   
   • 即使需要所有字段，减少网络带宽消耗，能有效利用覆盖索引，表结构变更对程序基本无影响
2. 能确定返回结果只有一条时，使用 limit 1
   
   • 在保证数据不会有误的前提下，能确定结果集数量时，多使用limit，尽快的返回结果
3. 建议不要使用隐式类型转换
4. 禁止在where条件列上使用函数
   
   • 会导致索引失效，如lower(email)，f_qq % 4。可放到右边的常量上计算
   • 返回小结果集不是很大的情况下，可以对返回列使用函数，简化程序开发，但尽量不要使用函数索引
5. 使用like模糊匹配，%不要放首位
   
   • 会导致索引失效，有这种搜索需求是，考虑其它方案，如es全文搜索
6. 使用join时，where条件尽量使用充分利用同一表上的索引
   
   • 如 select t1.a,t2.b * from t1,t2 where 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
7. 少用或不用子查询，改用join
   
   • mysql 5.6版本只支持nest loop，让mysql自已判断需要使用的索引
8. 考虑使用union all，少使用union，注意考虑去重
   
   • union all不去重，而少了排序操作，速度相对比union要快，如果没有去重的需求，优先使用union all
   • 如果UNION结果中有使用limit，在2个子SQL可能有许多返回值的情况下，各自加上limit。如果还有order by，请找DBA
9. IN的内容尽量不超过200个
   
   • 超过200个值使用批量的方式，否则一次执行会影响数据库的并发能力，因为单SQL只能且一直占用单CPU，而且可能导致主从复制延迟
10. 拒绝大事务
    
    • 比如在一个事务里进行多个select，多个update，如果是高频事务，会严重影响MySQL并发能力，因为事务持有的锁等资源只在事务rollback/commit时才能释放。但同时也要权衡数据写入的一致性
11. 避免使用is null, is not null这样的比较
12. 杜绝危险SQL
    
    • 去掉where 1=1 这样无意义或恒真的条件，如果遇到update/delete或遭到sql注入就恐怖了
    • SQL中不允许出现DDL语句。一般也不给予create/alter这类权限
13. 编写SQL 慎用分组、聚合
    在编写SQL中，原则上不能使用以下方法，尽可能将这些逻辑交由应用实现
    
    • group by having
    • count/sum/avg etc.
    • order by 非id
    • 3张及3张以上表做嵌套
    • left join/ right join
    • select * from
    • 无where条件的查询

六. 行为规范

1. 不允许在DBA不知情的情况下导现网数据
2. 大批量更新，如修复数据，避开高峰期，并通知DBA
3. 及时处理已下线业务的SQL
4. 复杂sql上线审核
   因为目前还没有SQL审查机制，复杂sql如多表join,count,group by，主动上报DBA评估
5. 重要项目的数据库方案选型和设计必须提前通知DBA参与
6. 严格按照现有的SQL规范提交SQL审核，如果有不确认单独和DBA沟通，如SQL较多，建议以脚本文件方式提交
7. 每周定某段时间窗口内提交SQL，其他时间段需要走紧急审批流程，原则上至少提前一天提交脚本审核
8. 严格执行【在线业务，客户直接使用，保证实时性】，【客户olap应用】，【离线、运营相关】，【内部系统】 存放，核心业务与其他业务隔离，业务耦合度高的应用尽可能放在一个实例中
9. 提交SQL时，不允许出现drop table if exists
10. 重要项目涉及数据库DBA必须参加，不然影响项目进度由项目自己负责
11. 表字段名称修改如果有otter同步时，则必须要保证两边数据没写入
12. count(*)无条件，或者扫描行数较大，大于10000行，理论上都是不被允许的，计数功能尽可能交由redis处理
13. 批量操作必须由DBA审核执行
14. 杭州、上海机房双活、双向，自增长主键的使用必须禁用
15. 在线数据库的人工变更（已有表结构变更、数据导入、更新、删除等）须要提前申请，主管审批，并知会运营可能的影响面

七.使用建议

1. order by .. limit
   这种查询更多的是通过索引去优化，但order by的字段有讲究，比如主键id与gmt_create都是顺序递增，那就可以考虑order by id而非 gmt_create
2. c1 < a order by c2
   与上面不同的是，order by之前有个范围查询，由前面的内容可知，用不到类似(c1,c2)的索引，但是可以利用(c2,c1)索引。另外还可以改写成join的方式实现

3. 分页优化
   建议使用合理的分页方式以提高分页效率，大页情况下不使用跳跃式分页假如有类似下面分页语句: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

4. 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方式可能效率更高
5. delete,update语句改成select再explain
6. 涉及到复杂sql时，务必先参考已有索引设计，先explain
   
   • 简单SQL拆分，不以代码处理复杂为由
   • 比如 OR 条件： f_phone=’10000’ or f_mobile=’10000’，两个字段各自有索引，可以使用索引合并使用两个索引。不过建议拆分成2个sql，或者union all，如果非要用mysql索引实现，则需要创建两列单列索引，打开index_merge_union后会转义成union
   • 先explain的好处是可以为了利用索引，增加更多查询限制条件
7. 减少与数据库交互的次数，尽量采用批量SQL语句
   
   • INSERT … ON DUPLICATE KEY UPDATE …，插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE，如果不重复则直接插入，影响1行
   • REPLACE INTO类似，但它是冲突时删除旧行。INSERT IGNORE相反，保留旧行，丢弃要插入的新行
   • INSERT INTO VALUES(),(),()，合并插入

8. 隐式类型使用注意事项

   • 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换
   • 两个参数都是字符串，会按照字符串来比较，不做类型转换
   • 两个参数都是整数，按照整数来比较，不做类型转换
   • 十六进制的值和非数字做比较时，会被当做二进制串
   • 有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
   • 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较

   • 所有其他情况下，两个参数都会被转换为浮点数再进行比较。

     例：A列为int类型时，A=’123’隐式转换后可以使用索引；A为char类型时，A=123隐式转换后无法使用索引

9. 任何新的select,update,delete上线，建议都要先explain，看索引使用情况
    尽量避免extra列出现：Using File Sort，Using Temporary，rows超过1000的要谨慎上线。explain解读
   
   • type：ALL, index, range, ref, eq_ref, const, system（从左到右，性能从差到好）
   • 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中无法利用索引完成的排序操作称为“文件排序”

八.SQL提交规范

业务名称或者功能模块: 某个应用的用户表【越详细越好】
实例库名： asgard
建表语句：
CREATE TABLE user (
id int(11) NOT NULL AUTO_INCREMENT,
login_id varchar(200) DEFAULT NULL 登录id,
password varchar(100) DEFAULT NULL COMMENT ‘密码’,
user_name varchar(45) DEFAULT NULL COMMENT ‘姓名’,
user_type int(1) NOT NULL COMMENT ‘应用类型：1.内网应用，走域帐号；2.外网应用，只做映射关系’,
user_code varchar(45) NULL COMMENT ‘asgard的管理员：asgard_admin 内部应用 fraudmetrix 外部应用’,
status int(1) DEFAULT ‘0’ COMMENT ‘是否可用，0:停用，1:可用’,
created_by varchar(100) DEFAULT NULL COMMENT ‘新增者’,
updated_by varchar(100) DEFAULT NULL COMMENT ‘修改者’,
gmt_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
gmt_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘修改时间’,
PRIMARY KEY (id),
KEY idx_loginId (login_id(191)) ,
KEY idx_userType (user_type) ,
KEY idx_userCode (user_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’用户表’
注：时间字段放于最后
查询条件（包括DML中的条件）及每个条件占比数据量：
where login_id=1 100%不同值 算法：count(distinct login_id) / count(1)
where user_name=1 and status=1 order by id desc limit 5 80%不同值，算法：count(distinct user_name,status) / count(1)
原则上大于20%以上就要创建下索引
表的预估数据量条数： 10K条左右
表的预估并发量QPS： 50
分库分表要求： 无【原则上1000万以上需要分库分表，这个待中间件上线上正式实施】
其他说明： 无