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