MySQL 数据库设计定义和示例

terry 2年前 (2023-09-26) 阅读数 48 #数据库

1。基本定义和目的

2。设计定义

2.1 数据库设计 2.1.1 库名称 2.1.2 表结构 2.1.3 列式数据类型改进 2.1.4 索引设计 2.1.5 表、表、分区表 2.1.6 字符集 2.1.7 程序 DAO 层设计建议 2.1.8 标准建表语句示例 2.2 SQL 脚本编写 2.2.1 DML 语句 2.2.2 多表关系 2.2.3 事务 2.2. 4 分类与分组 2.2.5 禁止在线使用的 SQL 语句

1. 基础定义和目的

MySQL数据库与Oracle、SQL Server数据库相比有主要优点和缺点。我们在使用MySQL数据库的时候，需要遵循一定的标准，扬长避短。本参考旨在帮助或指导RD、QA、OP和其他技术人员为在线业务进行适当的数据库设计。规范数据变更和处理方式、数据库表设计、SQL编写等，保证公司业务系统平稳健康运行。？

对于不满足【高风险】和【强制】两个级别的设计，DBA将强制退回设计并要求更改。

2.1.1 库名称

【必填】库名称必须限制在32个字符内，尽量体现相关模块中表名称的关系，如user表、user_login表等。
【必填】库名称格式为：业务系统名称_子系统名称。尝试使用与同一模块中使用的表名相同的前缀。
【必填】一般情况下，分支数据库名称的格式为通配符library_name，数字从0开始递增。例如基于时间的数据格式wenda_001的名称格式为“通配符名称”
【强制】创建数据时必须明确定义字符集，字符集只能是utf8或utf8mb4。创建数据库的SQL示例：create database db1 default font utf8;。

2.1.2 表结构

【必填】表名和列名必须限制在32个字符内，表名只能使用字母、数字和下划线，且全部小写。
【必填】表名需要硬链接模板的名称。例如，教师系统使用“sz”作为前缀，频道系统使用“qd”作为前缀等。
【强制】创建表时必须指定字符集为utf8或utf8mb4。
【必选】创建表时必须指定表存储引擎的类型。除非另有说明，否则始终是 InnoDB。当需要使用InnoDB/MyISAM/Memory以外的存储引擎时，必须通过DBA审核后才能用于生产环境。由于Innodb表支持关系数据库的重要特性，如事务、锁、恢复和MVCC等，因此它是业界使用最广泛的MySQL存储引擎。这是大多数其他存储引擎所不具备的，因此InnoDB是第一个提供的。
【必填】建表时需要注释 (2) 标识表中每一行的主体不要使用字段作为主键。建议设置为user_id、order_id等其他字段，并构建唯一键索引（参见cdb.teacher表设计）。因为如果设置为主键并且随机输入主键的值，会导致innodb中出现页面碎片以及大量的随机I/O，造成损坏。
【提示】主表（如用户表、币种相关表）行数据中必须有创建时间字段create_time和最后更新时间字段update_time，以方便解决问题。
【温馨提示】表中所有字段必须有NON-NULL属性，公司可以根据需要指定DEFAULT值。因为使用NULL值会带来每行占用额外存储空间等问题，数据迁移容易出错，计算操作结果有偏差。
【建议】建议将垂直表中的blob、text等大字段拆分到其他表中，如果不需要读取这些项目，则选择它们。
【建议】反范式设计：频繁添加需要查询其他表的字段。例如，user_account、user_login_log等表中缺少user_name属性，减少了登录请求。
【必选】中间表用于存储中间结果集，名称必须以tmp_开头。备份表用于对源表进行备份或拍照，名称必须以bak_开头。定期清理中间表和备份表。
【强制】100万行以上大表的换表必须经过DBA审核并在非高峰期执行。因为改表会产生表锁，在此期间所有对该表的写入都会被阻塞，这会对业务产生很大的影响。

2.1.3 列数据类型的确认

【提示】对于表中的auto_increment列（属性auto_increment），建议使用bigint类型。由于unsigned int的存储范围是-2147483648~2147483647（约21亿），所以溢出后会报错。
【提示】对于工厂中很少选择的状态、类型等字段，建议使用tinytint或smallint类型，以节省存储空间。
【建议】公司内IP地址字段建议使用int类型，不建议使用char(15)。由于int只有4个字节，所以可以使用下面的函数进行转换，但是char(15)至少有15个字节。一旦数据数量达到1亿，则需要额外的存储空间1.1G。 SQL：选择inet_aton('192.168.2.12');选择inet_ntoa(3232236044); PHP: ip2long('192.168.2.12'); long2ip(3530427185);
【建议】不建议使用 enum, set 。因为它们占用空间，而且枚举的值是硬编码的，所以改变并不烦人。最好使用tinyint或smallint。
【提示】不建议使用blob、text等类型。它们都会消耗硬盘和内存空间。加载表数据时，大字段会被读入内存，消耗内存空间，影响系统性能。如果确实需要这么广泛的领域，建议联系 PM 和 RD。在Innodb中，当文件的行超过8098字节时，将选择记录中最长的字段，并将768字节放在第一页上，溢出页将是剩余内容。不幸的是，在线性格式中，第一页和溢出页都被填满。
【提示】保存字段时，建议使用int。终端将输入乘以 100 并除以 100。由于int占用4个字节，double占用8个字节，所以浪费了空间。
【提示】文本数据尽量使用varchar存储。由于varchar是易失性存储，因此比char节省更多空间。 MySQL服务器层规定一行最多可以存储65535字节的文本，其中一个utf8字符串最多可以存储21844字节。如果超过限制，则会转换为平均字段。文本最多可以存储utf8字符集中的21844个字符，中文本最多可以存储2^24/3个字符，长文本最多可以存储2^32个字符。一般情况下，建议使用varchar类型，字符数不要超过2700。由于日期时间为 8 个字节，因此时间戳仅包含 4 个字节，但它是从 1970-01-01 00:00:01 到 2038-01-01 00:00:00。更高级的方法是使用 int 来存储时间，并使用 SQL 函数 unix_timestamp() 和 from_unixtime() 进行转换。

2.1.4 索引设计

【强制】InnoDB表的主键必须是id int/bigint auto_increment，并且主键值禁止更新。
【提示】主键名称以“pk_”开头，唯一键以“uk_”或“uq_”开头，公共索引以“idx_”开头。始终使用小写字母，并使用表名的名称或缩写作为后缀。
【强制】对于InnoDB和MyISAM存储引擎表，索引类型必须为BTREE； MEMORY表可以根据需要选择HASH或BTREE类型参数。
【强制】单个索引中每条索引记录的长度不超过64KB。
【提示】单表索引数量不要超过7个。
【提示】建索引时，可以考虑建联合索引，先把区分度最高的字段特别标出来。例如，可以通过选择的数字（唯一的userid）来计算userid列的唯一性。
【提示】多表连接的SQL中，要确保索引表的链接列上有索引，这样执行效率最高。
【提示】创建表或添加索引时，请确保表中没有多余的索引。对于MySQL来说，如果表中已经存在key(a,b)，那么key(a)就是一个冗余索引，需要删除。

2.1.5 数据库、表、分区表

【强制】分区表的分区字段（partition-key）必须有索引，或者是复合索引的第一列。
【强制】分区表中的分区（包括子分区）数量不能超过1024。
【强制】RD或DBA上线前必须指定分区表的创建和清除。
【强制】访问分区表的SQL必须包含分区键。
【提示】单个分区文件大小不超过2G，总大小不超过50G。建议分区总数不超过20个。
【必选】分区表必须连续执行换表操作。
[必选]若采用分片策略，库数不超过1024
[必选]若采用分片策略，表数不超过4096
[单推荐表] 不超过500W线。 ibd文件大小不超过2G，提高数据共享效率。
[提示] 尝试使用取模方法进行水平表分割。建议对数据和报告使用日期。

2.1.6 字符集

【强制】数据库、表、列中的所有字符集必须兼容，即utf8或utf8mb4。
【强制】前端程序的字符集或环境变量的字符必须与数据库和表的字符相匹配，并且要与utf8结合。

2.1.7 DAO 层设计技巧

【提示】不要在新代码中使用模板。推荐使用SQL+绑定变量来传递参数。虽然模型可以让db以目标为导向的方式工作，但是使用不当会让制定的SQL变得非常复杂，并且模型层的强制类型转换不成功，最终库存耗尽。表现。
【提示】前端程序连接MySQL或redis时，必然会出现连接超时、连接系统失败的情况，并且失败的复位之间必定存在间隙。
【提示】尽量在前端程序的错误报告中提示MySQL或redis的原始错误信息，以方便排查。
【提示】使用连接池进行前端编程，必须根据业务需求配置初始、最小、最大连接数、超时时间以及机制等通信处理。否则会导致数据连接资源耗尽，出现线上灾难。
[提示] 对于日志或历史类型图表，随着时间的推移很容易变得太大。因此，RD或DBA必须在上线前建立清理或备份计划。
【建议】在应用设计领域，RD必须考虑并避免数据库主从延迟对企业的影响。尽量避免公司从机数据短暂延迟（20秒以内）的影响。建议强制不断从主库读取，或者升级后一段时间后从从库读取。
【提示】当多个业务逻辑访问同一个数据库（innodb表）时，数据库端会出现行锁甚至表锁，因此并发性降低。因此，建议尽可能根据主键来更新新的SQL。
【提示】业务逻辑中的闭合顺序应尽可能保持一致，否则会导致死锁。
【Tips】对于单表读写比超过10:1的单行或单列数据，可以缓存（如mecache或redis）热数据，以加快访问速度，减轻MySQL压力。

2.1.8 标准建表语句示例

更标准的建表语句是：

CREATE TABLE user (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(11) NOT NULL COMMENT ‘用户id’
  `username` varchar(45) NOT NULL COMMENT '真实姓名',
  `email` varchar(30) NOT NULL COMMENT ‘用户邮箱’,
  `nickname` varchar(45) NOT NULL COMMENT '昵称',
  `avatar` int(11) NOT NULL COMMENT '头像',
  `birthday` date NOT NULL COMMENT '生日',
  `sex` tinyint(4) DEFAULT '0' COMMENT '性别',
  `short_introduce` varchar(150) DEFAULT NULL COMMENT '一句话介绍自己，最多50个汉字',
  `user_resume` varchar(300) NOT NULL COMMENT '用户提交的简历存放地址',
  `user_register_ip` int NOT NULL COMMENT ‘用户注册时的源ip’,
  `create_time` timestamp NOT NULL COMMENT ‘用户记录创建的时间’,
  `update_time` timestamp NOT NULL COMMENT ‘用户资料修改的时间’,
  `user_review_status` tinyint NOT NULL COMMENT ‘用户资料审核状态，1为通过，2为审核中，3为未通过，4为还未提交审核’,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_id` (`user_id`),
  KEY `idx_username`(`username`),
  KEY `idx_create_time`(`create_time`,`user_review_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='网站用户基本信息';
复制代码

2.2 SQL 脚本

2.2.1 DML 语句自定义字段名称必须为

且不能写成*。因为Select *会从MySQL中读取不该读取的数据，造成网卡压力。而当表字段更新了，但是模型层来不及更新时，系统就会报错。
[必需] 输入指定自定义字段的名称。不要在值 t1(…) 中键入 insert。原因与上面相同。
【建议】输入…值(XX),(XX),(XX)…。这里的XX值不要超过5000，即使值太大，上线也很快，但是会造成主从同步的延迟。
[提示] 不要在 SELECT 语句中使用 UNION。推荐使用UNION ALL，并且UNION子句的数量限制为5个。由于所有联合都不需要删除，因此节省了表资源并提高了性能。
【提示】列表值限制为500个。比如选择...where is the userid（...500以内...），这是为了减少基本扫描，减少点击上有库存并加快查询速度。
【建议】在批量交易中更新数据时，需要控制数量，做好必要的睡眠，并小部分、重复地进行。
【强制】事务涉及的所有表必须是innodb表。否则故障后一切都无法恢复，并且很容易导致主从库同步停止。
【必选】向主库发送写入和事务，向从库发送只读SQL。
【强制】除了静态表或小表（100行以内）外，DML语句必须具备条件并使用索引搜索。
【强制】使用建议，如sql_no_cache、强制索引、忽略key、直接direct等。生产环境中是禁止的。因为建议是用来强制SQL按照执行计划执行的，但是当数据量发生变化时，我们无法保证我们原来的预测是正确的，所以我们需要相信MySQL优化器！
[必填] 条件必须匹配的等号周围的字段，否则无法使用符号。
【建议】SELECT|UPDATE|DELETE|REPLACE必须有WHERE子句，且WHERE子句的条件必须使用索引搜索。
【强制】不强烈建议对生产数据库中的大表进行全表扫描，但可以对小于100行的静态表进行全表扫描。查询数据量不要超过表行数的25%，否则不会使用索引。
【强制】禁止在WHERE子句中使用完全不明确的KATIKA子句进行搜索。必须有相等的请求条件或者不同的区间，否则索引无法使用。
【提示】索引列中不要使用特征或表达式，否则索引无法使用。就像 length(name)='Admin' 或 user_id+2=10023 一样。
【建议】下载use or语句，将or语句组织成并集，然后为每个case创建索引。例如，如果a=1或b=2被优化为a=1...union...其中b=2，key(a),key(b)。
【提示】页面上的问题。当限制起点较高时，可以按第一个过滤条件进行过滤。例如，在t1 limit 10000,20中选择a,b,c；优化为：select a, b, c from t1 where id>10000 limit 20;。

2.2.2 多表连接

【强制】禁止跨db声明。因为它可以减少模块之间的耦合，为数据分离打下坚实的基础。
【强制】 SQL语句连中禁止使用update输入，如update t1 Enter t2…。
【建议】不建议使用子查询。建议拆分 SQL 子查询并以编程方式将其连接起来以进行多个查询，或者使用连接而不是子查询。
【提示】网络世界中，总桌数不应超过3桌。
【提示】涉及多个表的查询建议使用别名，并且别名应该用来引用SELECT列表中的字段，格式database.table，如select a from db1.table1 alias1 which... 。
【提示】多表分组时，尽量选择结果集较小的表作为驱动表来访问其他表。

2.2.3 事务

【提示】事务中INSERT|UPDATE|DELETE|REPLACE语句的操作行数限制为2000，以及事务中IN列表中传递的mason -filter的数量WHERE子句限制在500以内。
【提示】使用批量数据时，需要控制事务的间隔，并执行必要的睡眠。通常建议的值为 5-10 秒。
【温馨提示】对于具有auto_increment属性字段的表插入操作，并发数需要控制在200以内。
【强制】程序设计必须考虑“数据库隔离级别”的影响，包括脏读、不可恢复读和幻读。可以阅读互联网上推荐的商业隐私级别。
【提示】一笔交易中的SQL语句不能超过5条（支付交易除外）。因为太长的事务会导致雪崩问题，例如长时间锁定数据库、MySQL内部缓存、连接使用过多等。尽可能的，比如 update ... where id=XX;否则，会产生锁紧间隙，并且锁紧间隙会向内扩展，从而降低系统效率和停机时间。
【提示】尽量去除事务中常见的外部调用，例如Web服务调用、文件存储访问等，以防止事务耗时过长。
【提示】对于MySQL主从延迟非常敏感的select语句，请允许事务强制访问主库。

2.2.4 排序与分组

【建议】减少排序方式的使用，连接未排序的公司而不排序，或者在程序中设置排序。 order、groupeach、unique等语句消耗CPU较多，而数据库的CPU资源非常宝贵。
【提示】尝试使用索引直接检索SQL中选定的行、组、唯一等数据。例如，如果a=1，则可以使用密钥(a，b)。
【提示】如果添加了order、groupeach、unique等查询语句，按位置过滤的结果应该保持条件在1000行以内，否则SQL会很慢。
2.2.5 禁止在线使用的SQL语句【高危】停止更新|删除t1 ... where a=XX limit XX;有限的声明更新。因为这会导致与奴隶主的意见不合，造成混乱。建议将命令添加到PK中。
【高风险】禁止使用相关查询片段，如update t1 set...name contains(select name from user where...)；非常无效。
【强制】删除过程、函数、触发器、视图、事件和外键约束。因为它们会消耗数据资源并降低数据库的可扩展性等。建议以编程方式实施。
【强制】停止包含在…关键更新上…在高度并行的环境中，这会导致与slave master的冲突。
【强制】禁止公共表更新语句，如 update t1, t2 where t1.id=t2.id...。

作者：苏木力哥
链接：https://juejin.im/post/5d68dff55188255b0600538e
来源：掘金商业印刷请联系作者获得许可。非商业转载请注明来源。