2023年7月27日发(作者：)

mysql数据库物理结构_MySQL数据库结构设计（物理设计）什么是物理设计根据所选择的关系型数据库的特点对逻辑模型进⾏存储结构设计。MySQL数据库物理设计涉及的内容1、定义数据库、表及字段命名规范命名要遵守可读性原则。⽐如使⽤下划线来分割不同的单词等遵守表意性原则。⼀看表名或字段名就知道是⼲什么的长名原则。尽量少使⽤缩写，但命名也不能太长，适中最好。2、选择合适的存储引擎3、为表中字段选择合适的数据类型。如何选择正确的整数类型当⼀个列可以选择多种数据类型时，应该优先考虑数字类型，其次是⽇期或⼆进制类型，最后是字符类型。对于相同级别的数据类型，应该优先选择占⽤空间⼩的类型。整数类型的存储最⼤值是2的n-1次幂，最⼩值是2的n-1次幂减1，n是存储空间，⽐如tinyint，存储空间是1字节=8bit，取值范围就是-2的8-1次幂到2的8-1次幂减1，也就是-128~127之间的整数都可以存到tinyint中。需要说明的是，有⼀个误区，⽐如很多⼈使⽤int(2)来指定int类型的宽度，认为int(2)只会存储2位整数，这样可以节省存储空间，⽽实际上，这种限制整数类型的宽度是没有任何意义的，即使做了宽度的限制，int类型也会占⽤4个字节(32个bit)的空间，对存储值的范围也不会有任何影响，所以如果只需要存储2位数的话，使⽤tinyint类型。如何选择浮点类型通过例⼦来看下什么是精确类型如上表结构，i列是int类型，d1、d2是双精度类型，对该表数据进⾏按i分组对d1、d2分表求和并且查找出d1求和与d2求和后不相等的数据，结果如下从结果可以看出，两个double类型数之间的运算结果是不⼀样的，⽐如i为1分组，d1列求和(101.4-80)不等于21.4，这就说明double类型是⾮精确类型。同样使⽤decimal类型来进⾏上⾯例⼦的操作结果只有i为6的数据是不⼀样的，其他的都是⼀样的，说明decimal类型是精确类型。但是decimal类型⽐其他两个类型占的存储空间要多。当进⾏和财务有关的数字运算时，⼀定要采⽤decimal。如何选择正确的字符串类型，VARCHAR和CHAR的选择VARCHAR类型的存储特点r⽤于存储变长字符串，只占⽤必要的存储空间2.列的最⼤长度⼩于255则只占⽤⼀个额外字节⽤于记录字符串长度3.列的最⼤长度⼤于255则要占⽤两个额外字节⽤于记录字符串长度VARCHAR长度选择问题：使⽤最⼩的符合需求的长度。这时会有个疑问，使⽤varchar(5)和varchar(200)来存储“MySQL”字符串，由于这两种列的宽度都没有超过255，所以都只需要占⽤⼀个额外的存储字节，存储"MySQL"这个字符串只需要6个字节的存储空间就够了，那么我们还有必要选择最⼩的符合需求的长度吗，难道不是说只要不超过255，像这类字符串所占的存储空间不是⼀样的吗？其实还是需要选择最⼩的符合需求的长度的，MySQL为了更有效的优化查询，在内存中对字符串使⽤的是固定的长度，所以如果我们把列的长度设置的太长，会占⽤更多的内存，这才是要求使⽤最⼩的符合需求的长度的原因。VARCHAR适⽤场景1.字符串列的最⼤长度⽐平均长度⼤很多。2.字符串列很少被更新。因为varchar列更新字符长度会变化，可能会引起存储页的分裂，造成很多的存储碎⽚。3.使⽤了多字节字符集存储字符串CHAR类型的存储特点的类型是定长的2.字符串存储在CHAR类型的列中会删除末尾的空格类型的最⼤宽度为255CHAR适⽤场景1.适合存储长度近似的值。⽐如MD5加密的数据、⾝份证等2.适合存储短字符串3.适合存储经常更新的字符串如何存储⽇期数据ME类型，占⽤8个字节的存储空间以YYYY-MM-DD HH:MM:SS[.fraction]格式存储⽇期时间默认datetime=YYYY-MM-DD HH:MM:SS 也就是默认最⼩精度是秒，如果想存到毫秒，就要定义⼀个宽度，这个宽度的最⼤值是6。⽐如datetime(6)=YYYY-MM-DD HH:MM:on，fraction代表6位的毫秒数DATETIME类型与时区⽆关，不会随着时区的变化⽽变化，如果我们在不同的时区中使⽤相同的数据，那就要注意了。DATETIME存储范围很⼤：1000-01-01 00:00:00~9999-12-31 23:59:AMP类型存储占⽤ 4个字节，以年⽉⽇⼩时分秒的⽇期型式显⽰，存储范围'1970-01-01 00:00:01' to '2038-01-19 03:14:07'，以UTC时区进⾏存储，但是以系统当前时间进⾏显⽰，可以在insert和update时把值⾃动更新为当前时间。存储了由格林尼治时间1970年1⽉1⽇到当前时间的秒数，以YYYY-MM-DD HH:SS:MM[.fraction]的格式显⽰，占⽤四个字节，实际上是以int类型存储。⽐DATETIME类型存储范围⼩很多时间范围：1970-01-01到2038-01-19。如果要存的时间范围⼩，那就选⽤TIMESTAMP类型，因为这要⽐DATETIME占⽤空间⼩的多。TIMESTAMP类型显⽰的时间依赖于时区，不同时区显⽰不同的时间。在⼀个表中指定⼀个timestamp列，那么在本⾏的任何数据修改时都可以⾃动修改timestamp列的值，这是⾮常有⽤的，可以⾃动记录最后更新数据的时间。需要注意的是，如果定义了两个timestamp列，只有⼀个会⾃动更新。类型和time类型date类型只存储⽇期部分，存储时间范围是1000-01-01~9999-12-31。⽐DATETIME、字符串、int类型占⽤的字节数要少，使⽤datetime和字符串类型存储⽇期⾄少需要8个字节，使⽤int存储需要4个字节，使⽤date类型只需要3个字节。使⽤date类型还可以利⽤⽇期时间函数进⾏⽇期之间的计算，这是其他类型做不到的。time类型存储时间部分，格式HH:MM:SS[.fraction]。存储⽇期时间类型的注意事项：①不要使⽤字符串类型来存储⽇期时间数据。⽇期时间类型⽐字符串占⽤空间⼩，⽽且⽇期时间类型在进⾏查找过滤时可以利⽤⽇期来进⾏对⽐，如果使⽤字符串类型，那么只能按照字符集按顺序过滤，这是不符合对时间过滤的要求。②使⽤int存储⽇期时间不如使⽤timestamp类型，有⼈认为把⽇期时间转换为时间戳存到int类型⾥会提⾼效率，这是不对的。timestamp存储占⽤的空间和INT类型相同，实际上timestamp类型的数据在存储时就是被保存成INT类型的数据来存储的，这和我们使⽤INT来存储⽇期时间数据可以说是完全⼀样的。由于同样是使⽤INT类型来保存数据，所以和INT类型⼀样其存储的时间范围也是有限制的，这⼀点⼤家⼀定要注意，超过了这个范围的⽇期数据建议⼤家使⽤datetime类型来保存。另外timestamp数据存储时是以UTC时区来保存的，在显⽰时MySQL会⾃动的把数据转换为当前连接所对应时间来显⽰。可见，使⽤timestamp来存储⽇期时间数据不但保证了数据类型的⼤⼩同INT类型⼀样，同时可以显⽰为⽇期时间格式，这在给我们使⽤数据带来了很多的⽅便。所以强烈建议⼤家，使⽤timestamp类型来存储⽇期数据⽽不要再使⽤INT类型了。