13.mysql架构方案,MySQL数据架构设计

所有数据库对象名称：小写加下花线分割

MySQL对象名称在默认情况下是大小写敏感

MySQL的对像其实都是⼀一个文件，而linux文件名是大小写敏感

Dbname / dbname，MyTable / mytable

开发非常麻烦

所有MySQL数据库对象名称禁止使用MySQL保留关键字

一定要提前准备一份对应版本的关键字表

建表的时候没问题，但SQL查询就挂了了

所有的数据库对象名称：见名知义，但最长不要超过32个字符(不要中英文混合) 所有临时表命名：tmp_tablename_20191215

所有的备份表：bak_tablename_20191215

索引：idxpk

所有存储类型相同的列名以及长度必须保持一致

order：product_title 50

erp_instock: product_title 50

#####1.2.数据库设计规范

正常情况下建议使⽤用innoDB，v5.6版本后默认都是innoDB

字符集

UTF-8

统一字符集避免乱码

UTF-8的字符集是一个汉字3个字节：varchar(255) UTF-8 255*3=765字节

加入注释

控制单表的数据量大小：行

对于日志数据，进行归档

对于业务数据进行分库分表

分区表谨慎使用

控制表宽度

虽然表没有⾏行行限制，但列列最多4096

如果列多了，占用内存和I/O会非常大

禁止在表中建立预留字段：varchar_column，order_second_no，remark，memo

varchar类型

违背上面的命名规则

时间久了，不看业务代码，完全是魔鬼字段

禁止在数据库里存放图片、文件、二进制文件

如果要用blob、Text存大文件，select colums....

如何避免select * ，外键表单独放单文件

禁止对线上环境进行压力测试

会产生大量的垃圾数据和日志文件

禁止从开发环境、测试环境连接生产数据库

#####1.3.数据库索引设计规范

单张表索引数量量建议不不超过5个，如果列列多可以适当增加

索引过多：SQL在进行优化器评估的时候会有更大的开销

绝对不允许给表的每一列都建立索引

每个innodb表都必须有一个主键，innoDB表就是一个索引组织表

表数据的实际存储顺序只能有一种，innoDB是按照主键进行存放的

如果没有主键，mysql会优先选择第一个非空唯一索引来做主键

如果上面这个没有，mysql会自动⽣生成一个36个字节的主键，但性能不好

不能使用更新频繁的列和联合索引做主键，主键不断变，数据的存放顺序就会不断变化

不要使用UUID、MD5、HASH等做主键，不能保证这些值是按顺序增长的。如果⽣生成较小的字符串就会导致不断变化数据存储的位置，影响I/O性能

要在哪些列上建立索引：没有最好只有最适合

explain

where 后

join的连接lie

筛选项最大的放在索引做左侧

避免建立冗余和重复索引

对于频繁查询的数据列列，优先考虑使用覆盖索引

尽量避免加入外键约束

因为外键写入的时候会降低存储效率

但要给这些关联字段加索引