mysql 军规_Mysql使用军规

库表设计规范

建库原则就是同一类业务的表放一个库，不同业务的表尽量避免公用同一个库，尽量避免在程序中执行跨库的关联操作，此操作对后续的快速回档也会产生一定的影响；

每张表必须要有主键，即使选不出合适的列做主键，亦必须添加一个无意义的列做主键 MySQL 第一范式标准 InnoDB 辅助索引叶子节点会保存一份主键值，推荐用自增短列作为主键，降低索引所占磁盘空间提升效率， binlog_format 为 row 的场景下，批量删数据没主键会导致严重的主从延迟；

认准 InnoDB 引擎 线上 CDB 产品，5.6 版本开始即不支持 MyISAM 引擎(官方 MySQL 8.0 开始也不支持)，有 memory 引擎需求的，建议使用 redis 或者 memcached 自建数据库迁移到 CDB 会 MyISAM 自动转 InnoDB，故需满足以下两点否则会任务失败；

存在自增列的表，自增列上必须存在一个单独的索引，若在复合索引中，自增列必须置于第一位；

row_format 必须保证为非 fixed；

字符集统一使用 utf8mb4 降低乱码风险，部分复杂汉字和 emoji 表情必须使用 utf8mb4 方可正常显示，修改字符集只对修改后创建的表生效，故建议新购 CDB 初始化实例时即选择 utf8mb4；

小数字段推荐使用 decimal 类型，float 和 double 精度不够，特别是涉及金钱的业务，必须使用 decimal；

尽量避免据库中使用 text/blob 来存储大段文本、二进制数据、图片、文件等内容，而是将这些数据保存成本地磁盘文件，数据库中只保存其索引信息；

尽量不使用外键，建议在应用层实现外键的逻辑， 外键与级联更新不适合高并发场景，降低插入性能，大并发下容易产生死锁；

字段尽量定义为 NOT NULL 并加上默认值，NULL 会给 SQL 开发带来很多坑导致走不了索引，对 NULL 计算时只能用 IS NULL 和 IS NOT NULL 来判断；

降低业务逻辑和数据存储的耦合度，数据库存储数据为主，业务逻辑尽量通过应用层实现，尽可能减少对存储过程、触发器、函数、event、视图等高级功能的使用，这些功能移植性、可扩展性较差，若实例中存在此类对象，建议默认不要设置 definer，避免因迁移账号和 definer 不一致导致的迁移失败；

短期内业务达不到一个比较大的量级，禁止使用分区表分区表主要用作归档管理，多用于快递行业和电商行业订单表莫迷信分区表提升性能， 除非业务中 80% 以上的查询走分区字段；

对读压力较大，且一致性要求较低(接受数据秒级延时)的业务场景，建议购买只读实例从库来实现读写分离策略。

索引设计规范

单表的索引数建议不超过 5 个，单个索引中的字段数建议不超过 5 个，太多就起不到过滤作用了，索引也占空间，管理起来也耗资源；

选择业务中 SQL 过滤走的最多的并且 cardinality 值比较高的列建索引，业务 SQL 不走的列建索引是无意义的，字段的唯一性越高即代表 cardinality 值越高，索引过滤效果也越好，一般索引列的 cardinality 记录数小于 10% 我们可认为这是一个低效索引，例如性别字段；

varchar 字段上建索引时，建议指定索引长度，不要直接将整个列建索引， 一般 varchar 列比较长，指定一定长度作索引已经区分度够高，没必要整列建索引，整列建索引会显得比较重，增大了索引维护的代价，可以用 count(distinct left(列名, 索引长度))/count(*)来看索引区分度；

避免冗余索引，两个索引(a,b) (a)同时存在，则(a)属于冗余索引——redundant index，若查询过滤条件为 a 列，(a,b)索引就够了，不用单独建(a)索引；

建复合索引的时候，区分度最高的列放索引的在最左边， 例如 select xxx where a = x and b = x; ，a 和 b 一起建组合索引，a 的区分度更高，则建 idx_ab(a,b) 存在非等号和等号混合判断条件时，必须把等号条件的列前置。如：where a xxx and b = xxx 那么即使 a 的区分度更高，也必须把 b放在索引的最前列，因为走不到索引 a；

禁止在更新十分频繁、区分度不高的列上建立索引，记录更新会变更 B+ 树，更新频繁的字段建立索引会大大降低数据库性能；

合理利用覆盖索引来降低 io 开销，在 InnoDB 中二级索引的叶子节点保存的只保存本身的键值和主键值，若一个 SQL 查询的不是索引列或者主键，走这个索引就会先找到对应主键然后根据主键去找需要找的列，这就是回表，这样会带来额外的 io 开销，此时我们可以利用覆盖索引来解决这个问题，比如select a,b from xxx where a = xxx，若 a 不是主键，这时候我们可以创建 a，b 两个列的复合索引，这样就不会回表。

SQL编写规范

**按需索取，拒绝 select *，**规避以下问题：

无法索引覆盖，回表操作，增加 io；

额外的内存负担，大量冷数据灌入innodb_buufer_pool_size，降低查询命中率；

额外的网络传输开销；

UPDATE、 DELETE 操作不使用 LIMIT，必须走 WHERE 精准匹配，limit 是随机的，此类操作会导致数据出错；

禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性，避免表结构变动导致数据出错；

尽量避免使用大事务，建议大事务拆小事务，规避主从延迟；

业务代码中事务及时提交，避免产生没必要的锁等待；

少用多表 join，大表禁止 join，两张表 join 必须让小表做驱动表，join 列必须字符集一致并且都建有索引；

LIMIT 分页优化，LIMIT 80000，10 这种操作是取出 80010 条记录，再返回后 10 条，数据库压力很大，推荐先确首记录位置再分页，如：SELECT * FROM test WHERE id = ( SELECT sql_no_cache id FROM test order by id LIMIT 80000,1 ) LIMIT 10 ;；

避免多层子查询嵌套的 SQL 语句 MySQL5.5 之前的查询优化器把会把 in 改成 exists，走不到索引性，若外表很大则性能会很差；

SQL 语句中最常见的导致索引失效的情况需注意：

隐式类型转换，如索引 a 的类型是 varchar，SQL 语句写成 where a = 1;; varchar 变成了int ；

对索引列进行数学计算和函数等操作，例如，使用函数对日期列进行格式化处理；

join 列字符集不统一；

多列排序顺序不一致问题，如索引是 (a,b)，SQL 语句是 order by a b desclike；

模糊查询使用的时候对于字符型xxx%形式可以走到一些索引，其他情况都走不到索引；

使用了负方向查询(not，!=，not in 等)。