美文网首页数据库
(24)表设计原则(待补充,不断更新)

(24)表设计原则(待补充,不断更新)

作者: hedgehog1112 | 来源:发表于2020-11-23 14:31 被阅读0次

    一、建表规约

    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、防止因字段类型不同造成隐式转换,导致索引失效

    相关文章

      网友评论

        本文标题:(24)表设计原则(待补充,不断更新)

        本文链接:https://www.haomeiwen.com/subject/jebmiktx.html