Mysql表设计需要注意的问题

database

下面探讨的数据库为MySQL 存储引擎为innodb因为这是最常见的,使用最多的数据库和引擎

什么是页分裂?
这是因为聚簇索引采用的是平衡二叉树算法,而且每个节点都保存了该主键所对应行的数据,假设插入数据的主键是自增长的,那么根据二叉树算法会很快的把该数据添加到某个节点下,而其他的节点不用动;但是如果插入的是不规则的数据,那么每次插入都会改变二叉树之前的数据状态。从而导致了页分裂。

为什么一定要设置一个主键
因为就算你不主动设置一个主键,innodb也会帮你生成一个隐藏列,作为自增主键来使用。所以,不管怎么样都会有自增主键,还不如自己指定一个,主键索引可以提高查询效率。

主键使用自增还是UUID
主键肯定是用自增。innodb种的主键是聚族索引,如果主键是自增的,那么每次插入的新数据都会顺序添加到当前索引节点的后续位置,当一页写满时,就会自动开启第二页,如果不是自增主键,那么就可能在中间插入,就会引发页的分裂,产生很多碎片,总之用自增主键性能更好。

UUID产生的索引文件更大,当数据量超过一百万行时,主键查询性能和索引文件大小都比UUID要更有效率和更小。

 

主键为什么不推荐有业务含义

1)因为任何含有业务的列都有改变的可能性,主键一旦带上业务含义,那么主键就有可能发生改变。主键发生改变,该数据在磁盘上的存储位置就会发生更改,有可能引发页分裂,产生空间碎片。

2)带有业务含义的主键不一定是顺序自增的,就有可能导致后边插入的数据主键一定比前面的大,如果出现后边插入的数据主键比前面插入的小,就肯能引发页分裂,产生空间碎片。
 

表示枚举的字段为什么不用enum类型
首先,枚举在MySQL中一般用tinyint类型,为什么不使用enum呢

1)ENUM类型,order by操作效率低,需要额外的操作

2)如果枚举是数值,有陷阱

例如:表结构如下create table test(sex enum("0","1"))此时执行插入语句insert into test values(1) 查询的结果为0

只有这样插入insert into test values("1")此时结果才是1

 

货币字段用什么类型
货币:如果货币单位是分,可以使用int类型,如果坚持用元的话,可以使用Decimal类型,不能使用float和double类型,因为这两个类型是以二进制存储的,所有有一定的误差。

例如:create table "t"("price" float(10,2) default null) engine=innodb default charset=utf8然后插入一条数据price=120.23,然后你会发现数据变为120.25,精度失准。

 

时间字段用什么类型
1)如果使用varchar,优点是显示直观,缺点是做时间比较运算的时候,需要使用str_to_date等函数把它转化为事件类型,你会发现这是无法命中索引的,数据量一大,这就是一个大坑。

2)如果使用timestamp类型,该类型为4个字节,表示的时间范围为1970-01-01 到2038-01-19 也就是说2038年以后的时间不能使用timestamp存储。该类型有一个优势,就是自带时区,如果修改了时区,该字段值会跟着改变。

3)dtatime类型,该类型为8个字节,自己维护一个时间戳,表示范围比timestamp大,因为需要自己维护,所以不太方便。


为什么不直接存储图片、音频、视频等大容量内容
在实际生产中,我们都是在文件服务器上存真实文件,数据库中保存对应的文件路劲即可,MySQL有存放大文件的类型,text和blob类型,但是基本不使用

1)MySQL内存临时表不支持text、blob这样的大数据类型,如果查询中包含这样的数据,在排序等操作时,就不能使用内存临时表,必须使用磁盘临时表,导致效率低下。

2)binlog内容太多,因为数据内容过大,会导致binlog内容较多,而MySQL主从同步靠binlog进行同步,binlog太大就会导致主从同步效率问题。


字段为什么要定义为NOT NULL
1)索引性能不好

2)查询会出现一些不可预料的结果

以上是 Mysql表设计需要注意的问题 的全部内容, 来源链接: utcz.com/z/534001.html

回到顶部