设为首页收藏本站
查看: 35|回复: 0

数据库设计规范(数据库设计三大规范)

[复制链接]

中级会员

Rank: 3Rank: 3

积分
1066
主题
530
UID
1548
M币
3
贡献
533

  • 发表于 2019-10-12 00:01:00 | 显示全部楼层 |阅读模式
    数据库计划规范(数据库计划三大规范)原创波波说运维2019-10-12 00:01:00
    概述MySQL数据库与 Oracle八、 SQL Server 等数据库比拟,有其内核上的上风与劣势。咱们在使用MySQL数据库的时刻必要遵循肯定规范,取长补短。无心中从github上看到一个大佬的M猫先森收集资本站ySQL数据库计划规范,趁便在这里分享一下。
    https://github.com/jly8866/archer/blob/master/src/docs/mysql_db_design_guide.md
    写的都很适用,建议珍藏浏览。


    1八、数据库计划如下全部规范会根据【高危】八、【逼迫】八、【建议】三个级别进行标注,服从优先级从高到低。对付不知足【高危】以及【逼迫】两个级其余计划,DBA会逼迫打回要求修改。
    1八、库名
  • 【逼迫】库的名称必需节制在32个字符之内,相干模块的表名与表名之间只管提现join的瓜葛,如user表以及user_login表。
  • 【逼迫】库的名称格局:营业体系名称_子体系名,统一模块使用的表名只管使用同一前缀。
  • 【逼迫】一样平常分库名称定名格局是库通配名_编号,编号从0最先递增,好比wenda_001以时间进行分库的名称格局是“库通配名_时间”
  • 【逼迫】创立数据库时必需显式指定字符集,而且字符集只能是utf8或者者utf8mb4。创立数据库SQL举例:create database db1 default character set utf8;。
    [/ol]2八、 表布局
  • 【逼迫】表以及列的名称必需节制在32个字符之内,表名只能使用字母八、数字以及下划线,一概小写。
  • 【逼迫】表名要求模块名强相干,如师资体系采纳”sz”作为前缀,渠道体系采纳”qd”作为前缀等。
  • 【逼迫】创立表时必需显式指定字符集为utf8或者utf8mb4。
  • 【逼迫】创立表时必需显式指定表存储引擎范例,如无特殊需求,一概为InnoDB。当必要使用除了InnoDB/MyISAM/Memory之外的存储引擎时,必需经由过程DBA考核才能在出产环境中使用。由于Innodb表支撑事务八、行锁八、宕机规复八、MVCC等瓜葛型数据库主要特征,为业界使用至多的MySQL存储引擎。而这是其他大大都存储引擎不具有的,是以首推InnoDB。
  • 【逼迫】建表必需有co妹妹ent
  • 【建议】建表时关于主键:(1)逼迫要求主键为id,范例为int或者bigint,且为auto_increment(2)标识内外每一一行主体的字段不要设为主键,建议设为其他字段如user_id,order_id等,并确立unique key索引(可参考cdb.teacher表计划)。由于若是设为主键且主键值为随机插入,则会致使innodb内部page盘据以及大量随机I/O,机能降落。
  • 【建议】焦点表(如用户表,金钱相干的表)必需有行数据的创立时间字段create_time以及末了更新时间字段update_time,便于盘问题。
  • 【建议】表中全部字段必需都是NOT NULL属性,营业可以按照必要界说DEFAULT值。由于使用NULL值会存在每一一行城市占用分外存储空间八、数据迁徙轻易失足八、聚合函数计较效果误差等题目。
  • 【建议】建议对内外的blob八、text等大字段,垂直拆分到其他内外,仅在必要读这些工具的时刻才去select。
  • 【建议】反范式计划:把常常必要join查询的字段,在其他内外冗余一份。如user_name属性在user_account,user_login_log等内外冗余一份,削减join查询。
  • 【逼迫】中间表用于保存中间效果集,名称必需以tmp_开首。备份表用于备份或者抓取源表快照,名称必需以bak_开首。中间表以及备份表按期清算。
  • 【逼迫】对付跨越100W行的大表进行alter table,必需经由DBA考核,并在营业低峰期实行。由于alter table会发生表锁,时代壅闭对付该表的全部写入,对付营业可能会发生极大影响。
    [/ol]3八、 列数据范例优化
  • 【建议】表中的自增列(auto_increment属性),保举使用bigint范例。由于无符号int存储局限为-2147483648~2147483647(约莫21亿摆布),溢出后会致使报错。
  • 【建议】营业中选择性很少的状况status八、范例type等字段保举使用tinytint或者者smallint范例节约存储空间。
  • 【建议】营业中IP地址字段保举使用int范例,不保举用char(15)。由于int只占4字节,可以用以下函数互相转换,而char(15)占用至少15字节。一旦表数据行数到了1亿,那末要多用1.1G存储空间。 SQL:select inet_aton('192.168.2.12'); select inet_ntoa(3232236044); PHP: ip2long(‘192.168.2.12’); long2ip(3530427185);
  • 【建议】不保举使用enum,s猫先森收集资本站et。 由于它们虚耗空间,且罗列值写去世了,调动不利便。保举使用tinyint或者smallint。
  • 【建议】不保举使用blob,text等范例。它们都对照虚耗硬盘以及内存空间。在加载表数据时,会读取大字段到内存里从而虚耗内存空间,影响体系机能。建媾和PM八、RD沟通,是否真的必要这么大字段。Innodb中当一行纪录跨越8098字节时,会将该纪录中拔取最长的一个字段将其768字节放在原始page里,该字段余下内容放在overflow-page里。不幸的是在compact行格局下,原始page以及overflow-page城市加载。
  • 【建议】存储金钱的字段,建议用int,法式端乘以100以及除了以100进行存取。由于int占用4字节,而double占用8字节,空间虚耗。
  • 【建议】文本数据只管用varchar存储。由于varchar是变长存储,比char更省空间。MySQL server层划定一行全部文本至多存65535字节,是以在utf8字符集下至多存21844个字符,跨越会主动转换为mediumtext字段。而text在utf8字符集下至多存21844个字符,mediumtext至多存2^24/3个字符,longtext至多存2^32个字符。一样平常建议用varchar范例,字符数不要跨越2700。
  • 【建议】时间范例只管拔取timestamp。由于datetime占用8字节,timestamp仅占用4字节,然则局限为1970-01-01 00:00:01到2038-01-01 00:00:00。更为高阶的法子,选用int来存储时间,使用SQL函数unix_timestamp()以及from_unixtime()来进行转换。
    [/ol]具体存储巨细加入下图:


    4八、 索引计划
  • 【逼迫】InnoDB表必需主键为id int/bigint auto_increment,且主键值禁止被更新。
  • 【建议】主键的名称以“pk_”开首,独一键以“uk_”或者“uq_”开首,通俗索引以“idx_”开首,一概使用小写格局,以表名/字段的名称或者缩写作为后缀。
  • 【逼迫】InnoDB以及MyISAM存储引擎表,索引范例必需为BTREE;MEMORY表可以按照必要选择HASH或者者BTREE范例索引。
  • 【逼迫】单个索引中每一个索引纪录的长度不克不及跨越64KB。
  • 【建议】单个表上的索引个数不克不及跨越7个。
  • 【建议】在确立索引时,多思量确立联合索引,并把区别度最高的字段放在最前面。如列userid的区别度可由select count(distinct userid)计较出来。
  • 【建议】在多表join的SQL里,保证被驱动表的毗邻列上有索引,如许join实行效率最高。
  • 【建议】建表或者加索引时,保证内外相互不存在冗余索引。对付MySQL来讲,若是内外已经经存在key(a,b),则key(a)为冗余索引,必要删除了。
    [/ol]5八、 分库分表八、分区表
  • 【逼迫】分区表的分区字段(partition-key)必需有索引,或者者是组合索引的首列。
  • 【逼迫】单个分区表中的分区(包罗子分区)个数不克不及跨越1024。
  • 【逼迫】上线前RD或者者DBA必需指定分区表的创立八、清算策略。
  • 【逼迫】接见分区表的SQL必需包孕分区键。
  • 【建议】单个分区文件不跨越2G,总巨细不跨越50G。建议总分区数不跨越20个。
  • 【逼迫】对付分区表实行alter table操纵,必需在营业低峰期实行。
  • 【逼迫】采纳分库策略的,库的数目不克不及跨越1024
  • 【逼迫】采纳分表策略的,表的数目不克不及跨越4096
  • 【建议】单个分表不跨越500W行,ibd文件巨细不跨越2G,如许才能让数据散布式变患上机能更佳。
  • 【建议】程度分表只管用取模体式格局,日记八、报表类数据建议采纳日期进行分表。
    [/ol]6八、 字符集
  • 【逼迫】数据库自己库八、表八、列全部字符集必需连结同等,为utf8或者utf8mb4。
  • 【逼迫】前端法式字符集或者者环境变量中的字符集,与数据库八、表的字符集必需同等,同一为utf8。
    [/ol]2八、 SQL编写规范


    1八、 DML语句
  • 【逼迫】SELECT语句必需指定详细字段名称,禁止写成*。由于select *会将不应读的数据也从MySQL里读出来,造成网卡压力。且表字段一旦更新,但model层没有来患上及更新的话,体系会报错。
  • 【逼迫】insert语句指定详细字段名称,不要写成insert into t1 values(…),原理同上。
  • 【建议】insert into…values(XX),(XX),(XX)…。这里XX的值不要跨越5000个。值过量固然上线很很快,但会引起主从同步耽误。
  • 【建议】SELECT语句不要使用UNION,保举使用UNION ALL,而且UNION子句个数限定在5个之内。由于union all不必要去重,节约数据库资本,进步机能。
  • 【建议】in值列表限定在500之内。例如select… where userid in(….500个之内…),这么做是为了削减底层扫描,减轻数据库压力从而加快查询。
  • 【建议】事务里批量更新数据必要节制数目,进行需要的sleep,做到少许屡次。
  • 【逼迫】事务触及的表必需所有是innodb表。不然一旦掉败不会所有回滚,且易造成主从库同步终端。
  • 【逼迫】写入以及事务发往主库,只读SQL发往从库。
  • 【逼迫】除了静态表或者小表(100行之内),DML语句必需有where前提,且使用索引查找。
  • 【逼迫】出产环境禁止使用hint,如sql_no_cache,force index,ignore key,straight join等。由于hint是用来逼迫SQL根据某个实行设计来实行,但跟着数据量转变咱们没法保证本身当初的预判是准确的,是以咱们要信赖MySQL优化器!
  • 【逼迫】where前提里等号摆布字段范例必需同等,不然没法行使索引。
  • 【建议】SELECT|UPDATE|DELETE|REPLACE要有WHERE子句,且WHERE子句的前提必须使用索引查找。
  • 【逼迫】出产数据库中强烈不保举大表上产生全表扫描,但对付100行如下的静态表可以全表扫描。查询数据量不要跨越表行数的25%,不然不会行使索引。
  • 【逼迫】WHERE 子句中禁止只使用全含糊的LIKE前提进行查找,必需有其他等值或者局限查询前提,不然没法行使索引。
  • 【建议】索引列不要使用函数或者表达式,不然没法行使索引。如where length(name)='Admin'或者where user_id+2=10023。
  • 【建议】削减使用or语句,可将or语句优化为union,然后在各个where前提上确立索引。如where a=1 or b=2优化为where a=1… union …where b=2, key(a),key(b)。
  • 【建议】分页查询,当limit出发点较高时,可先用过滤前提进行过滤。如select a,b,c from t1 limit 10000,20;优化为:select a,b,c from t1 where id>10000 limit 20;。
    [/ol]2八、 多表毗邻
  • 【逼迫】禁止跨db的join语句。由于如许可以削减模块间耦合,为数据库拆分奠基坚实底子。
  • 【逼迫】禁止在营业的更新类SQL语句中使用join,好比update t1 join t2…。
  • 【建议】不建议使用子查询,建议将子查询SQL拆开连系法式屡次查询,或者使用join来取代子查询。
  • 【建议】线上环境,多表join不要跨越3个表。
  • 【建议】多表毗邻查询保举使用别号,且SELECT列表中要用别号援用字段,数据库.表格局,如select a from db1.table1 alias1 where …。
  • 【建议】在多表join中,只管拔取效果集较小的表作为驱动表,来join其他表。
    [/ol]3八、 事务
  • 【建议】事务中INSERT|UPDATE|DELETE|REPLACE语句操纵的行数节制在2000之内,和WHERE子句中IN列表的传参个数节制在500之内。
  • 【建议】批量操纵数据时,必要节制事务处置距离时间,进行需要的sleep,一样平常建议值5-10秒。
  • 【建议】对付有auto_increment属性字段的表的插入操纵,并发必要节制在200之内。
  • 【逼迫】法式计划必需思量“数据库事务断绝级别”带来的影响,包罗脏读八、不行反复读以及幻读。线上建议事务断绝级别为repeatable-read。
  • 【建议】事务里包孕SQL不跨越5个(付出营业除了外)。由于太长的事务会致使锁数据较久,MySQL内部缓存八、毗邻斲丧过量等雪崩题目。
  • 【建议】事务里更新语句只管基于主键或者unique key,如update … where id=XX; 不然会发生间隙锁,内部扩展锁定局限,致使体系机能降落,发生去世锁。
  • 【建议】只管把一些典范外部挪用移失事务,如挪用webservice,接见文件存储等,从而防止事务太长。
  • 【建议】对付MySQL主从耽误严酷敏感的select语句,请开缘由务逼迫接见主库。
    [/ol]4八、 排序以及分组
  • 【建议】削减使用order by,以及营业沟通能不排序就不排序,或者将排序放到法式端去做。order by八、group by八、distinct这些语句较为花消CPU,数据库的CPU资本是极为名贵的。
  • 【建议】order by八、group by八、distinct这些SQL只管行使索引直接检索出排序好的数猫先森收集资本站据。如where a=1 order by可以行使key(a,b)。
  • 【建议】包孕了order by八、group by八、distinct这些查询的语句,where前提过滤出来的效果集请连结在1000行之内,不然SQL会很慢。
    [/ol]5八、 线上禁止使用的SQL语句
  • 【高危】禁用update|delete t1 … where a=XX limit XX; 这类带limit的更新语句。由于会致使主从纷歧致,致使数据错乱。建议加之order by PK。
  • 【高危】禁止使用联系关系子查询,如update t1 set … where name in(select name from user where…);效率极为低下。
  • 【逼迫】禁用procedure八、function八、trigger八、views八、event八、外键束缚。由于他们斲丧数据库资本,低落数据库实例可扩大性。保举都在法式端实现。
  • 【逼迫】禁用insert into …on duplicate key update…在高并发环境下,会造成主从纷歧致。
  • 【逼迫】禁止联表更新语句,如update t1,t2 where t1.id=t2.id…。
    [/ol]觉患上有效的同伙多协助转发哦!后面会分享更多devops以及DBA方面的内容,感爱好的同伙可以存眷下~



    泉源:https://mxswl.net/zdmzt/90519.html
  • 回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    在我站开通SVIP可同时获得17个站点VIP资源 立即登录 立即注册
    快速回复 返回顶部 返回列表