《高性能MYSQL》——Schema与数据类型优化
一、选择优化的数据类型
选择数据类型的原则
1、更小的通常更好
一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快,因为它们占用更小的磁盘、内存和CPU缓存,并且处理时需要的CPU周期也更少。
2、简单就好
简单数据类型的操作通常需要更少的CPU周期。
3、尽量避免NULL
查询中包含可为NULL的列对MYSQL来说更难优化,因为可为NULL的列使得索引、索引统计和值比较都更复杂。
整数类型
如果存储整数,可以使用这几种整数类型:TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT分别使用8, 16, 24, 32, 64位存储空间。整数类型有可选的UNSIGNED属性,表示不允许负值,这大致可以使正数的上限提高一倍。有符号和无符号类型使用相同的存储空间,并具有相同的性能,因此可以根据实际情况 选择合适的类型。
实数类型
实数是带有小数部分的数字。然而,它们不只是为了存储小数部分、也可以使用 DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型,也支持不精确类型。
FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。
DECIMAL类型用于存储精确的小数。
浮点类型在存储同样范围的值时,通常比DECIMAL使用更少的空间。FLOAT使用4个字 节存储。DOUBLE占用8个字节,相比FLOAT有更高的精度和更大的范围。
字符串类型
VARCHAR和CHAR类型
VARCHAR和CHAR是两种最主要的字符串类型。
VARCHAR类型用于存储可变长字符串,是最常见的字符串数据类型。它比定长类型 更节省空间,因为它仅使用必要的空间(例如,越短的字符串使用越少的空间)。
CHAR类型是定长的:MySQL总是根据定义的字符串长度分配足够的空间。CHAR适合存储很短的字符串,或者所有值都接近同一个长度。
BLOB和TEXT类型
BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。
日期和时间类型
DATETIME
这个类型能保存大范围的值,从1001年到9999年,精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中,与时区无关。使用8个字节的存储空间。
TIMESTAMP
TIMETAMP类型保存了从1970年1月1日午夜(格林尼治标准时间)以来的秒数,它和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间。
二、范式与反范式
范式的优点和缺点
优点:
范式化的更新操作通常比反范式化要快。
当数据较好地范式化时,就只有很少或者没有重复数据,所以只需要修改更少的数据。
范式化的表通常更小,可以更好地放在内存里,所以执行操作会更快。
缺点:
范式化设计的schema的缺点是通常需要关联。稍微复杂一些的査询语句在符合范式的 schema上都可能需要至少一次关联,也许更多。
反范式的优点和缺点
优点:
反范式化的schema因为所有数据都在一张表中,可以很好地避免关联。
可以设计有效的索引。
缺点:
表中的数据冗余较多,删除数据时候会造成表有些有用的信息丢失。
混用范式化和反范式化
事实是,完全的范式化和完全的反范式化schema都是实验室里才有的东西:在真实 世界中很少会这么极端地使用。在实际应用中经常需要混用,可能使用部分范式化的 schema、缓存表,以及其他技巧。
最常见的反范式化数据的方法是复制或者缓存,在不同的表中存储相同的特定列。
三、缓存表和汇总表
缓存表表示存储那些可以比较简单地从schema其他表获取(但是每次获取的速度比较慢)数据的表(例如, 逻辑上冗余的数据)。
汇总表表示那些使用GROUP BY语句聚合数据的表(例 如,数据不是逻辑上冗余的)。
四、schema设计原则
- 尽量避免过度设计,例如会导致极其复杂査询的schema设计,或者有很多列的表设 计(很多的意思是介于有点多和非常多之间)。
- 使用小而简单的合适数据类型,除非真实数据模型中有确切的需要,否则应该尽可能地避免使用NULL值。
- 尽量使用相同的数据类型存储相似或相关的值,尤其是要在关联条件中使用的列。
- 注意可变长字符串,其在临时表和排序时可能导致悲观的按最大长度分配内存。
- 尽量使用整型定义标识列。
- 避免使用MySQL已经遗弃的特性,例如指定浮点数的精度,或者整数的显示宽度。
- 小心使用ENUM和SET。虽然它们用起来很方便,但是不要滥用,否则有时候会变成 陷阱。最好避免使用BIT.
以上是 《高性能MYSQL》——Schema与数据类型优化 的全部内容, 来源链接: utcz.com/z/513583.html