当前位置 博文首页 > Alan-Yin:MySQL 数据库设计的“奥秘”
【主题】逻辑设计:数据类型与 Schema
所谓“万丈高楼平地起”,一个稳固的建筑离不开扎实的基础。同样,良好的的「逻辑设计」和「物理设计」是数据库高性能的基石。
本文的主要内容总结了一张图,你可以点击查看。
我们需要根据系统需要执行的 SQL 语句设计 schema,这往往权衡各种因素。
[idea]瞧!权衡和取舍不仅仅在系统设计、方案设计时处处体现其价值,在数据库表设计也同样如此。
举个例子,反范式设计可以加快某些类型查询,但可能会使另一些类型查询变慢。如增加汇总表可以优化查询,但这些表维护成本可能很高。
首先,让我们一起来看下 MySQL 的数据类型,这是我们设计数据表前需要事先掌握的。
MySQL 支持的数据类型很多,为了做出更好的选择,我们来看几个简单的原则:
尽量使用可以正确存储数据的最小数据类型。
优点:
缺点:
简单数据类型需要更少的 CPU 周期。
比如整型比字符串代价低(字符串有字符集和校对规则)。
NULL 列对 MySQL 更难优化,因为为 NULL 的列使得索引、索引统计和值比较都更复杂;并且占用更多存储空间。
todo 为什么占用更多存储空间?
如数字、字符串、时间等。
同类型下只是存储的长度、范围、允许的精度、需要的物理空间(磁盘、内存)不同。
有两种类型的数字:「整数」和「实数」。
整数有很多种,如:TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT,分别使用 8,16,24,32,64 位存储空间。
有可选的 UNSIGNED 属性,表示不允许负值。
还可以指定宽度
例如 INT(11),它不会限制值的合法范围,只是规定了 MySQL 的一些客户端显示字符的个数。
INT(1) 和 INT(11) 是相同的。
即带有小数部分的数字。MySQL 既支持精确类型,也支持不精确类型。
FLOAT 和 DOUBLE 类型使用浮点运算进行近似计算。
DECIMAL 类型用于存储精确的小数。使用场景:尽量只有对小数需要精确计算时才使用,因为需要额外的存储空间和计算开销。
数据量比较大时,可以使用 BIGINT 代替 DECIMAL,对应单位乘以相应倍数即可。
VACHAR 和 CHAR 类型
比定长类型更节省空间,因为它只使用必要的空间(如字符串越短使用空间越少)。
需要使用 1(<=255) 或 2(>255) 个额外字节记录字符串长度。
由于变长,Update 可能使行变得比原来更长,容易产生碎片
如 InnoDB 需要页分裂使得行可以放到页内。
适合 VACHAR 的场景:
适合 CHAR 的场景:
建议:最好的策略是只分配真正需要的空间。
todo Vachar(5) 和 Vachar(200) 又什么区别?空间开销一样吧?
适合场景:存储很大的数据,前者使用二进制,后者使用字符存储。
特殊之处:MySQL 把 BLOB 和 TEXT 值当作独立对象处理,值太大时会使用专门的“外部”存储区域存储,在行内存储指针指向外部实际值。
[idea]这个设计在程序开发中也可以使用,有时一些大属性占用存储较多但使用频率较低,可以单独存储在其他地方,通过指针进行引用,从而使得该对象更小。
不能将 BLOB 和 TEXT 列全部长度索引,不能用这些索引消除排序。
如果 Explain 执行计划的 Extra 列包含“Using temporary”,说明这个查询使用了「隐式临时表」。
优点:存储枚举时非常紧凑,节约空间
MySQL 内部将每个值在列表中位置保存为整数,在表的 .frm 文件中保存“数字-字符串”映射关系的“查找表”。
所以,使用数字作为枚举常量,这种双重性容易导致混乱。建议尽量避免这么做。
例如 ENUM('1','2',3'')
??枚举字段是按照内部存储整数而不是定义的字符串数字排序。
缺点:
建议通用设计实践:在“查找表”时采用整数主键而避免采用字符串的值进行关联,因为性能更好。