MYSQL 数据库设计模式

Myth丶恋晨 2022-03-11 13:37 195阅读 0赞

第一章：数据库设计规范  
一、数据库命名规范总结  
1、所有数据库对像名称必须 小写  
2、命名要做到见名识意，禁止使用MySQL保留关键字  
3、临时表以tmp\_开头，备份表以bak\_开头并以时间戳结尾  
4、所有存储相同数据的列名和列类型必须一致  
二、数据库基本设计规范总结  
1、所有表必须使用Innodb存储引擎  
2、所有表及字段都要有备注信息，并使用UTF-8字符集  
3、要做到尽量控制单表大小（数据量在500万行以内），并且把冷热数据分离  
4、禁止使用预留字段及在表中存储大的二进制数据  
三、索引设计规范总结  
1、每个Innodb表都需要有一个主键  
2、限制表上索引的数量，避免建立重复和冗余索引  
3、注意合理选择复合索引键值的顺序  
四、数据库字段设计规范总结  
1、选择符合存储要求的最小的数据类型  
2、避免使用Blob或是Text类型及ENUM类型  
3、每个字段尽可能具有not null属性  
4、使用datetime或timestamp类型存储时间  
5、财务相关金额类数据，必须使用decimal类型  
五、数据库SQL开发规范  
1、建议使用预编译语句进行数据库操作  
2、避免数据类型隐式转换  
3、禁止使用select \*进行查询及没有字段列表的insert操作  
4、优先利用表上一金存在的索引  
六、数据库操作行为规范  
1、超100万行的批量写操作，要分批多次进行操作  
2、对于大表使用pt-online-schema-change修改表结构  
3、禁止为程序使用的账号赋予super权限--当达到最大连接数限制时，还允许1个有super权限的用户连接。  
4、对于程序连接数据库账号，遵循权限最小原则  
第二章  
第三范式(3NF)  
一、MySQL分区表  
命令：show plugins;  
特点：在逻辑上为一个表，在物理上存储在多个文件中  
1、hash分区特点：  
创建hash分区    
partition by hash(id)(   
 partitions 4 );  
根据MOD(分区键，分区数)的值把数据行存储到表的不同分区中  
数据可以平均的分布在各个分区中  
hash分区的键值必须是一个int类型的值，或是通过函数可以转换为int类型  
2、range范围分区  
创建range分区   
partition by range(id)(   
partition p0 values less than (10000), //0~9999  
partition p0 values less than (20000),  
partition p0 values less than (30000),  
partition p0 values less than maxvalue //大于30000)；  
分区场景：  
分区键为日期或是时间类型  
所有查询中都包括分区键  
定期按范围清理历史数据  
3、list分区  
创建list分区：   
partition by list(login\_type)(   
partition p0 values in (1,3,5,6,9),   
partition p1 values in (2,4,6,8) );  
list特点：  
按分区键取值的列表进行分区  
同范围分区一样，各分区的列表值不能重复  
每一行数据必须能找到对应的分区列表，否则数据插入失败  
使用分区表注意事项：  
1)结合业务场景选择分区键，避免跨分区查询  
2)对分区表进行查询最好在where从句中包含分区键  
3)具有主键或唯一索引的表，主键或唯一索引必须是分区键的一部分  
第三章  
1、复制表as 和 like区别  
as用来创建相同表结构并复制源表数据  
like用来创建完整表结构和全部索引  
例子：  
create table bak\_info\_vehicle\_info\_20190225 like info\_vehicle\_info  
insert into bak\_info\_vehicle\_info\_20190225 select \* from info\_vehicle\_info