mysql数据库设计原则(数据库设计基本原则)

mysql数据库设计原则(数据库设计基本原则)本页面通过数据整理汇集了mysql数据库设计原则(数据库设计基本原则)相关信息，和科新教育小编一起了解一下这个问题。

mysql数据库设计原则

与Oracle、SQL Server等数据库相比，MySQL数据库在内核方面有其优势和不足。我们在使用MySQL数据库时，需要遵循一定的规范，扬长避短。偶然看到一个很大的MySQL数据库设计规范，顺便分享到这里。

写的东西都很实用，建议收藏看看。

1.数据库设计

以下所有规范将按照【高风险】、【强制】和【推荐】三个等级进行标注，合规优先级由高到低。对于不符合[高风险]和[强制性]级别的设计，DBA将强制他们召回进行修改。

1.库名

【强制】库的名称必须控制在32个字符以内，相关模块的表名要尽可能的表现出联接关系，比如user表和user_login表。

【强制】库名格式:业务系统名_子系统名，同一模块中使用的表名尽量使用统一前缀。

【必填】一般情况下，子数据库名称的命名格式为库通用名称_编号，编号从0开始递增。比如wenda_001子数据库按时间的名称格式是 通用名称_时间

[强制]创建数据库时，必须明确指定字符集，字符集只能是utf8或utf8mb4。创建数据库SQL的例子:创建数据库db1默认字符集utf8。

2.表结构

【强制】表名和列名必须控制在32个字符以内，表名只能使用字母、数字和下划线，均为小写。

[强制]表名要求模块名之间有很强的相关性。比如教师制采用 sz 作为前缀，渠道系统采用 qd 作为前缀等。

[Force]创建表格时，必须明确指定字符集为utf8或utf8mb4。

[强制]创建表时，必须显式指定表存储引擎类型。如果没有特殊要求，永远是InnoDB。当需要使用InnoDB/MyISAM/Memory以外的存储引擎时，必须经过DBA的批准，才能在生产环境中使用。Innodb table是业内使用最广泛的MySQL存储引擎，因为它支持关系数据库的重要特性，如事务、行锁、停机恢复、MVCC等。这是大多数其他存储引擎所不具备的，所以InnoDB是首选。

[强制]创建表时必须有注释。

【建议】创建表时，关于主键:(1)强制主键为id，类型为int或bigint，且为auto _ increment。(2)标识表中每行主题的字段不应设置为主键。建议设置其他字段，如user_id、order_id等。，并建立唯一的关键索引(参考cdb.teacher表设计)。如果设置为主键，随机插入主键值，会导致innodb中的页面拆分和大量的随机I/O，降低性能。

[建议]核心表(如用户表和货币相关表)必须有创建时间字段create_time和上次更新时间字段updat # 101_时间，方便查问题。

[建议]表中所有字段必须不为空，业务可以根据需要定义默认值。因为使用NULL值会导致一些问题，比如每行会占用额外的存储空，数据迁移容易出错，聚合函数的计算结果有偏差。

【建议】建议将表格中的blob、text等大字段垂直拆分到其他表格中，仅在需要读取这些对象时选择。

【建议】反范式设计:冗余一个在其他表中经常需要join查询的字段。例如，user_name属性在user_account、user_login_log和其他表中是冗余的，以减少连接查询。

[强制]中间表用于保存中间结果集，名称必须以tmp_开头。Backup table用于备份或捕获源表的快照，其名称必须以bak_开头。定期清理中间表和备份表。

【强制】对于超过100W行的大表，alter table必须经过DBA的批准，并在业务低峰期执行。因为alter table会产生表锁，所以在此期间阻止对表的所有写入可能会对业务产生很大影响。

3.列数据类型优化

[建议]表中自增列(auto_increment属性)推荐使用bigint类型。因为无符号int的存储范围是-2147483648~2147483647(约21亿)，溢出会导致错误。

【建议】对于业务中选择性不大的status、type type等字段，建议使用tinytint或smallint节省存储空。

【建议】业务中IP地址字段推荐int类型，不推荐Cha。(15)。因为int只占4个字节，所以可以用下面的函数相互转换，而cha # 114(15)占用至少15个字节。一旦表数据行数达到1亿，就应该使用1.1G千兆字节的存储空。SQL:select inet _ aton(' 192 . 168 . 2 . 12 ')；选择inet _ ntoa(3232236044)；PHP:IP 2 long( lsquo；192 . 168 . 2 . 12 rsquo；);long 2 IP(3530427185)；

[建议]不建议使用enum，set。因为它们浪费空，而且枚举值写死了，不方便更改。建议使用Tinyint或smallint。

【建议】不推荐blob、text等类型。都是浪费硬盘和内存之间的空间空。加载表数据时，大字段会被读入内存，从而浪费内存空，影响系统性能。建议与PM和RD沟通是否真的需要这么大的场地。在Innodb中，当一行记录超过8098个字节时，将选择记录中最长的字段，其768个字节将放在原始页中，其余的字段将放在溢出页中。不幸的是，在紧凑行格式中，原始页和溢出页都将被加载。

【建议】建议用int存储钱字段，在程序端乘以100除以100进行存取。因为int占4个字节，double占8个字节，所以空浪费了。

【建议】文本数据尽量存储在varchar中。因为varchar是变长存储，所以比char节省空时间。MySQL层规定一行中的所有文本最多存储65，535字节，所以utf8字符集存储的字符数最多为21，844，会自动转换为mediumtext字段。但是，存储在utf8字符文本中的最大字符数是21，844，存储在mediumtext中的最大字符数是2.24/3，存储在longtext中的最大字符数是2.32。一般建议使用varchar类型，字符数不要超过2700。

[建议]时间类型应选择时间戳。因为datetime占8个字节，timestamp只占4个字节，但是范围是1970-01-01 00:00:01到2038-01-01 00:00:00。对于高阶方法，选择int存储时间，使用SQL函数unix_timestamp()和from_unixtime()进行转换。

详细存储大小见下图:4。索引设计。

【强制】InnoDB表必须有id int/bigint auto_increment作为主键，主键值禁止更新。

[建议]主键的名称应该是 。pk _ 开始时，唯一的键以 开头。uk _ 或者 uq _ 开始时，一般索引以 idx _ 开始时，始终使用小写格式，以表名/字段的名称或缩写作为后缀。

[必填] InnoDB和MyISAM存储引擎表，索引类型必须是BTREE；内存表可以根据需要选择HASH或BTREE类型的索引。

[强制]单个索引中每个索引记录的长度不能超过64KB。

[建议]单个表上的索引数量不应超过7个。

【建议】建立索引时，考虑建立联合索引，将区分度最高的字段放在最前面。列userid的区分可以通过selec # 116计算计数(不同的用户id)。

【建议】在多表join的SQL中，确保被驱动表的连接列有索引，这样join的执行效率最高。

[建议]在构建表或添加索引时，确保表内和表内没有多余的索引。对于MySQL，如果表中已经存在键(a，b)，那么键(a)就是冗余索引，需要删除。

5.子数据库、子表和子表

[强制]分区表的分区字段(分区键)必须有索引，或者组合索引的第一列。

[强制]单个分区表中的分区(包括子分区)数量不能超过1024。

【强制】上线前，RD或DBA必须指定分区表的创建和清理策略。

[强制]访问分区表的SQL必须包含分区键。

【建议】单个分区文件不要超过2G，总大小不要超过50G。建议分区总数不要超过20。

[Force]对分区表的alter table操作必须在业务的低峰期执行。

[强制]如果采用子数据库策略，数据库数量不能超过1024个。

【强制】如果采用分表策略，表数不能超过4096。

【建议】单个子表不要超过500W行，ibd文件大小不要超过2G，使数据分布更好。

【建议】横向表格尽量按模式划分，日志和报表的数据按日期划分。

6.字符集

【强制】库的所有字符集，数据库本身的表和列必须一致，是utf8或者utf8mb4。

【强制】前端程序的字符集或环境变量中的字符集必须与数据库和表的字符集一致，统一为utf8。

2.SQL写作规范1。DML语句

【强制】SELECT语句必须指定具体的字段名，禁止写成*。因为select *也会从MySQL中读出不该读取的数据，造成网卡压力。而且一旦更新了表字段，但是模型层没有及时更新，系统会报错。

[Force]insert语句指定具体的字段名，不应该写成insert into t1 values( hellip；)，同上。

【建议】插入到 hellip值(XX)，(XX)，(XX) hellip；。这里XX的值不应该超过5000。值太大会造成主从同步的延迟，虽然会很快上线。

[建议]不要在SELECT语句中使用UNIO。UNIO # 110推荐使用。ALL和UNIO # 110子句的数量限制为5个。因为unio # 110不需要对所有数据进行重复数据消除，从而节省数据库资源并提高性能。

[建议]in值列表应限制为500。例如，选择 hellipwher # 101( hellip中的用户id. 500以内 hellip)，这样做是为了减少底层扫描，减轻数据库压力，加快查询速度。

【建议】要控制交易中批量更新数据的数量，进行必要的睡眠，做到次数少。

[强制]事务中涉及的所有表必须是innodb表。否则如果失败了，就不能完全回滚，容易造成主从库同步终端。

[强制]写入和事务被发送到主库，只读SQL被发送到从库。

[强制]除了静态表或小表(100行以内)，DML语句必须有wher # 101条件，并使用索引查找。

【强制】生产环境中禁止使用提示，如sql_no_cache、force index、ignore key、straight join等。Hint是用来强制SQL按照一定的执行计划执行的，但是随着数据量的变化，我们无法保证自己当初的预测是正确的，只好相信MySQL优化器了！

[被迫]wher # 101；条件中等号两边的字段必须是同一类型，否则无法使用索引。

[建议]选择|更新|删除|替换应有WHER # 101子句，以及何时 # 101；子句必须使用索引查找。

[强制]强烈建议不要对生产数据库中的大型表进行全表扫描，但它可以用于少于100行的静态表。查询量不应超过表行的25%，否则索引将不会被利用。

[被迫]WHER # 101；禁止在子句中仅使用完全模糊的LIKE条件进行搜索。必须有其他等价或范围查询条件，否则索引不能使用。

[建议]不要在索引列中使用函数或表达式，否则无法使用索引。比如什么时候 # 101；Length(name)='Admin '或when # 101user_id+2=10023 .

【建议】减少or语句的使用，将or语句优化为unio # 110然后在每个wher # 101根据条件创建索引。比如什么时候 # 101；A=1或b=2被优化为wher # 101a = 1 hellipunio # 110 hellipwher # 101b=2，关键(a)，关键(b).

【建议】对于分页查询，当限制起点较高时，可以先用过滤条件过滤。从t1限值10000，20中选择a，b，c；优化为:selec # 116从t1开始的a、b、c wher # 101；id gt10000限制20；。

2.多表连接

[Force]禁止跨db join语句。这样可以减少模块之间的耦合，为数据库拆分打下坚实的基础。

[强制]禁止在业务更新SQL语句中使用join，如updat # 101t1加入t2 hellip。

[建议]不建议使用子查询。建议反汇编子查询SQL，并将其与程序结合起来进行多次查询，或者使用join代替子查询。

【建议】在线环境下，多表连接不要超过3个表。

【建议】多表连接查询推荐使用别名，字段、数据库、表的格式要在选择列表中用别名引用，比如从db1 . table 1 Alias 1 wher # 101； hellip。

【建议】在多表连接中，尽量选择结果集较小的表作为连接其他表的驱动表。

3.事务

[建议]在交易中插入| updat # 101；|DELETE|REPLACE语句操作的行数应控制在2000以内，当 # 101；传递给IN list in子句的参数个数应控制在500以内。

【建议】批量操作数据时，要控制好交易间隔，进行必要的睡眠。一般推荐值为5-10秒。

【建议】对于带有auto_increment属性字段的表的插入操作，并发应该控制在200以内。

【强制】编程必须考虑 数据库的事务隔离级别:影响，包括脏读、不可重复读和幻影读。推荐的在线事务隔离级别是可重复读取。

【建议】交易中包含的SQL不超过5条(支付业务除外)。因为过长的事务会导致过长的数据锁、MySQL内部缓存、过多的连接消耗等雪崩问题。

【建议】事务中的Update语句应尽可能基于主键或唯一键，如update hellipwher # 101id = XX否则会产生间隙锁，内部会放大锁定范围，导致系统性能下降和死锁。

【建议】尽量将一些典型的外部调用移出事务，比如调用webservice、访问文件存储等，避免事务过长。

【建议】对于MySQL主从延迟严格敏感的select语句，请打开事务强制访问主库。

4.排序和分组

【建议】减少order by的使用，与业务沟通不进行排序，或者将排序放在程序端。order by、group by和distinct语句消耗CPU，数据库的CPU资源极其宝贵。

【建议】order by、group by、distinct等SQL应尽量使用索引直接检索排序后的数据。比如什么时候 # 101；A=1 order by可以利用key(a，b)。

[建议]包含order by、group by和distinct查询的语句，when # 101请将过滤后的结果集保持在1000行以内，否则SQL会很慢。

5.在线禁止的SQL语句

[高风险]禁用更新|删除t1 hellipwher # 101a=XX极限XX；这个update语句有限制。因为会导致主的不一致，造成数据混乱。按主键添加订单。

【高风险】禁止使用关联子查询，如update t1 set hellipwher # 101姓名(从用户名中选择姓名，wher # 101 hellip);效率极低。

[强制]禁用过程、函数、触发器、视图、事件和外键约束。因为它们消耗数据库资源并降低数据库实例的可伸缩性。所有建议都在程序端实现。

[force]禁用插入 hellip关于重复密钥更新 hellip在高并发环境下，会造成主的不一致。

【强制】禁止联表更新语句，如update t1,t2 wher#101; t1.id=t2.idhellip;。

mysql数据库设计原则(数据库设计基本原则)相关信息请关注本文章，了解更多关于PLC编程信息信息请持续关注山东科新教育网站，本站内容仅作为做为展示。