设计数据库表时数据类型的选择
设计数据库表时数据类型的选择
1. 整数类型
整数类型有:tinyint、smallint、mediumint、int、bigint,分别使⽤ 8、16、24、32、64 位存储空间。
它们可以存储的值范围从 -2 的 (n-1) 次⽅到 2 的 (n-1) 次⽅ -1,n 是存储空间的位数。
整数有可选的 unsigned 属性(⽆符号类型),表⽰不允许有负值,因此可以使正数上限提⾼⼀倍。
有符号和⽆符号类型使⽤相同的存储空间,并具有相同的性能,因此可以根据实际情况选择合适的类型。
2.  实数类型
实数类型有:FLOAT、DOUBLE ,分别占⽤ 4,8 字节。
如果插⼊值的精度(即:数字总位数)⾼于实际定义的精度,系统会⾃动进⾏四舍五⼊处理,使值的精度达到要求。
其中 DECIMAL 也可以⽤来指定精度,并且它⽐ FLOAT 和 DOUBLE 更适合做精确计算。在本⽂就不做详细介绍了,如果有⼈想了解的话可以给我留⾔,我下次再写。
3. 字符串类型
字符串类型有:VARCHAR、CHAR、BLOB、TEXT
对⽐内容        VARCHAR CHAR
是否固定长度        否    是
存储上限字节        65535      255
保存或检索值时,是否删除字符串末尾空格    否    是
超过设置的范围后,字符串是否会被截断        否    是
除了以上不同之外,VARCHAR 还需要额外使⽤ 1 个或 2 个字节来记录字符串长度。如果列的最⼤长度⼩于或等于 255 字节,则使⽤ 1个字节,否则使⽤ 2 个字节。
由于 VARCHAR 是变长的,所以在 update 时,可能使⾏变得⽐原来更长,这就导致需要进⾏额外的
⼯作。如果⼀个⾏占⽤的空间增加,并且在页内没有更多空间可以存储,在这种情况下,不同存储引擎的处理⽅式不⼀样的。例如:MyISAM 会将⾏拆分为不同的⽚段存储,InnoDB 则需要分裂页来使⾏可以放进页内。
在选择使⽤场景上,重点要抓住 VARCHAR 是变长,CHAR 是定长的特点。
⽐如在这些情况更适合使⽤ VARCHAR:
字符串的最⼤长度⽐平均长度⼤很多;
字段更新次数少(所以碎⽚不是问题);
使⽤了像 UTF-8 这样复杂的字符集,每个字符都使⽤不同的字节数进⾏存储。
⽽在这些情况则更适合使⽤ CHAR:
存储很短的字符串(⽽ VARCHAR 还要多⼀个字节来记录长度,本来打算节约存储的现在反⽽得不偿失)
定长的字符串(如 MD5、uuid);
需要频繁修改的字段。因为 VARCHAR 每次存储都要有额外的计算,得到长度等⼯作;
使⽤ VARCHAR(5) 和 VARCHAR(200) 来存储 ‘hello’ 的空间开销是⼀样的。那么使⽤更短的列有什么好处呢?因为更长的列会消耗更多的内存。MySQL 通常会分配固定⼤⼩的内存块来保存内部值。尤其是使⽤内存临时表进⾏排序或操作时会特别糟糕。在利⽤磁盘临时进⾏排序时也同样糟糕。
所以最好的策略是只分配真正需要的空间。
4. ⽇期和时间类型
对⽐内容        TIMESTAMP    DATETIME
占⽤字节        4      8
时间范围        1970-01-01 08:00:01 ~ 2038-01-19 11:14:07    1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
存储的数据是否随时区变化        是    否
如果在插⼊数据时,没有指定第⼀个 TIMESTAMP 列的值,MySQL 则将这个列设置为当前时间,同时 TIMESTAMP ⽐ DATETIME 的空间效率更⾼。
5. 设计合理的数据类型
三点设计原则:
更⼩的通常更好decimal是整数数据类型
简单就好
尽量避免 NULL
注意:
⼀般情况下,应该选择可以正确存储数据的最⼩数据类型,因为它们占⽤更少的磁盘、内存和 CPU 缓存,并且处理时需要的 CPU 周期也更少。
简单数据类型的操作需要更少的 CPU 周期。例如,整型⽐字符操作代价更低,因为字符集和校对规则(排序规则)使字符⽐较⽐整型⽐较更复杂。
通常情况下,最好指定列为 NOT NULL,除⾮真的需要存储 NULL 值。因为可为 NULL 的列会使索引、索引统计和值⽐较都更复杂。可为 NULL 的列会使⽤更多的存储空间,在 MySQL ⾥也需要特殊处理。
当可为 NULL 的列被索引时,每个索引需要⼀个额外的字节,在 MyISAM ⾥甚⾄还可能导致固定⼤⼩的索引变成可变⼤⼩的索引。通常把可为 NULL 的列改为 NOT NULL 带来的性能⽐较⼩,所以在优化时没有必要先在现有表⾥修改这种情况。
这是我所学到的⼀些知识,在此分享给⼤家,希望可以帮助到你们。
以上就是我的分享,新⼿上道,请多多指教。如果有更好的⽅法或不懂得地⽅欢迎在评论区教导和提问喔!

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。