一、建表规约
1、is_xxx表达是与否，unsigned tinyint (1 表示是，0 表示否)。
2、表名不使用复数名词、保留字，如desc、range、match、delayed
3、主键索引为pk_字段名; 唯一索为uk_字段名; 普通idx_字段名
4、小数类型为decimal，禁用float和double(精度损失),超范围，拆成整数和小数并分存
5、如存字符串长度几乎相等，用char定长字符串类型
6、varchar是可变长字符串，不预先分配存储空间，长度不要超过5000，如长度大于此值，定义为 text，独立出一张表，主键来对应，避免影响其它字段索引效率。
7、表必备三字段:id(bigint unsigned)，create_time，update_time(datetime)
8、表名遵循“业务名称_表的作用”，alipay_task / trade_config 库名与应用名称尽量一致
9、冗余字段应遵循：1) 不是频繁修改的字段。2) 不是 varchar 超长字段，更不能是 text 字段。 3) 不是唯一索引的字段。
10、单表>500 万行 或 容量>2GB，才分库分表
11、无符号值避免存负数，扩大了表示范围
二、索引规约
1、唯一特性字段，必须建成唯一索引（即使多个字段组合）
2、禁止三个表join。要join字段，数据类型必须一致，被关联字段要有索引
3、varchar上建立索引时，必须指定索引长度，没必要对全字段建立索引
4、索引长度与区分度是矛盾体，一般字符串类型，长20，区分度高达90%以上，用 count(distinct left(列名, 索引长度))/count(*)的区分度确定？
5、页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决？用es？
6、order by 要用索引有序性，避免出现 file_sort 。where a=? and b=? order by c; 索引:a_b_c
7、延迟关联或子查询优化超多分页场景
MySQL不是跳过 offset 行，而是取 offset+N 行，放弃前 offset 行，返回 N 行，offset 特别大，效率低下，要么控制返回总页数，要么对超过阈值页数进行 SQL改写
8、SQL 性能优化目标:至少 range ，要求 ref ，consts 最好
1) consts 单表只有一个匹配行(主键或唯一索引)，优化阶段即可读 2) ref 用普通索引(normal index)。3) range 对索引进行范围检索
ps：explain 表结果，type=index，全扫描，慢，index 级别比 range还低
9、组合索引，区分度最高在最左边
10、防止因字段类型不同造成隐式转换，导致索引失效
网友评论