《高性能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既支持精确类型,也支持不精确类型。

FLOATDOUBLE类型支持使用标准的浮点运算进行近似计算。

DECIMAL类型用于存储精确的小数。

浮点类型在存储同样范围的值时,通常比DECIMAL使用更少的空间。FLOAT使用4个字 节存储。DOUBLE占用8个字节,相比FLOAT有更高的精度和更大的范围。

字符串类型

VARCHARCHAR类型

VARCHARCHAR是两种最主要的字符串类型。

VARCHAR类型用于存储可变长字符串,是最常见的字符串数据类型。它比定长类型 更节省空间,因为它仅使用必要的空间(例如,越短的字符串使用越少的空间)。

CHAR类型是定长的:MySQL总是根据定义的字符串长度分配足够的空间。CHAR适合存储很短的字符串,或者所有值都接近同一个长度。

BLOBTEXT类型

BLOBTEXT都是为存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。

日期和时间类型

DATETIME

这个类型能保存大范围的值,从1001年到9999年,精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中与时区无关。使用8个字节的存储空间。

TIMESTAMP

TIMETAMP类型保存了从197011日午夜(格林尼治标准时间)以来的秒数,它和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间。

二、范式与反范式

范式的优点和缺点

优点:

范式化的更新操作通常比反范式化要快。

当数据较好地范式化时,就只有很少或者没有重复数据,所以只需要修改更少的数据。

范式化的表通常更小,可以更好地放在内存里,所以执行操作会更快。

缺点:

范式化设计的schema的缺点是通常需要关联。稍微复杂一些的査询语句在符合范式的 schema上都可能需要至少一次关联,也许更多。

反范式的优点和缺点

优点:

反范式化的schema因为所有数据都在一张表中,可以很好地避免关联。

可以设计有效的索引。

缺点:

表中的数据冗余较多,删除数据时候会造成表有些有用的信息丢失。

混用范式化和反范式化

事实是,完全的范式化和完全的反范式化schema都是实验室里才有的东西:在真实 世界中很少会这么极端地使用。在实际应用中经常需要混用,可能使用部分范式化的 schema缓存表,以及其他技巧。

最常见的反范式化数据的方法是复制或者缓存,在不同的表中存储相同的特定列。

三、缓存表和汇总表

存表表示存储那些可以比较简单地从schema其他表获取(但是每次获取的速度比较慢)数据的表(例如, 逻辑上冗余的数据)。

汇总表表示那些使用GROUP BY语句聚合数据的表(例 如,数据不是逻辑上冗余的)。

四、schema设计原则

  • 尽量避免过度设计,例如会导致极其复杂査询的schema设计,或者有很多列的表设 计(很多的意思是介于有点多和非常多之间)。
  • 使用小而简单的合适数据类型,除非真实数据模型中有确切的需要,否则应该尽可能地避免使用NULL值。
  • 尽量使用相同的数据类型存储相似或相关的值,尤其是要在关联条件中使用的列。
  • 注意可变长字符串,其在临时表和排序时可能导致悲观的按最大长度分配内存。
  • 尽量使用整型定义标识列。
  • 避免使用MySQL已经遗弃的特性,例如指定浮点数的精度,或者整数的显示宽度。
  • 小心使用ENUMSET虽然它们用起来很方便,但是不要滥用,否则有时候会变成 陷阱。最好避免使用BIT.

 

 

 

 

 

以上是 《高性能MYSQL》——Schema与数据类型优化 的全部内容, 来源链接: utcz.com/z/513583.html

回到顶部