高性能mysql笔记——第四章

Schema与数据类型优化

数据类型的选择

  1. 更小的通常更好
  2. 简单就好,应该使用Mysql内建的类型而不是字符串来存储日期和时间;应该用整形存储IP地址。
  3. 尽量避免NULL;难优化,占用更多存储空间,特殊处理,索引记录需要额外的字节。

    整数类型(whole number)

TINYINT、SMAILLINT、MEDIUMINT、INT、BIGINT

mysql可以为整数类型指定宽度,比如INT(11),对大多数应用是没有意义的;它不会限制值的合法范围,只是规定了mysql的一些交互工具(例如命令行客户端)用来显示字符的个数。对于存储和计算来说,INT(1)和INT(11)是相同的。

实数类型(real number)

带有小数部分的数字。DECIMAL、FLOAT、DOUBLE

DECIMAL用于存储精确的小数。

mysql对小数进行精确计算需要额外的空间和计算开销,在数据量大的时候,考虑使用BIGINT代替DECIMAL,根据小数的位数乘以相应的倍数存储即可。

字符串类型

  • VARCHAR

    用于存储可变长字符串,比定长类型更节省空间,仅使用必要的空间。有一种情况例外,如果mysql表使用ROW_FORMAT=FIXED创建的话,每一行都会使用定长存储。

    VARCHAR需要使用1或2个额外字节去记录字符串的长度:如果列的最大长度小于等于255字节,则使用1个字节表示,否则使用2个字节。

    但是,由于行是变长的,在update时可能使行变得比原来更长,导致需要做额外的工作。

  • CHAR

    定长字符串类型。

    与VARCHAR不同的是,在存储CHAR值时,mysql会删除所有的末尾空行。CHAR适合存储很短的字符串或者所有值都接近一个同一个长度,例如MD5。

    对于经常变更的数据,CHAR也比VARCHAR更好,因为不容易产生碎片。

  • BLOB和TEXT

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

    mysql把每个BLOB和TEXT值当做一个独立的对象处理。当值太大时,InnoDB会使用专门的外部存储区域来进行存储,此时每个值在行内需要1~4个字节存储一个指针,然后在外部存储区域存储实际的值。

    BLOB类型存储的是二进制数据,没有排序规则或字符集,而TEXT类型有字符集和排序规则。

    mysql只对这种类型的每个列的最前max_sort_length进行排序,而不是整个字符串做排序

    mysql不能讲blob和text列全部长度的字符串进行索要,也不能使用这些索引消除排序。

    尽量避免使用BLOB和TEXT类型。

  • 枚举(ENUM)

日期和时间类型

Mysql能存储的最小时间粒度为秒,但是mysql可以使用微妙级的粒度进行临时计算。

  • DATETIME

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

  • TIMESTAMP

    保存了从1970年1月1日午夜以来的秒数,与UNIX时间戳相同,只使用了4个字节的存储空间,因此范围比DATETIME小得多;只能表示从1970-2038年。可以使用BIGINT类型存储微妙级别的时间戳。

位数据类型

  • BIT

    谨慎使用BIT类型,最好避免使用。

  • SET

选择标识符(identifier)

1
2
3
4
5
6
7
8
标识列  又称为自增序列;
含义 : 可以不用手动的插入值, 系统提供默认的序列值(1-->n)
特点 :
1.标识列必须和主键搭配? 不一定,但要求是一个key
2.一个表可以有几个标示列?至多一个!
3.标识列的类型, 只能是数值型;
4.标识列可以通过 set auto_increment_increment = 3;设置步长
5.可以通过 手动插入值
  • 整数通常是标识列最好的选择,很快并且可以使用auto_increment

  • ENUM和SET类型通常是一个糟糕的选择,大部分情况下都要避免这么做。

  • 字符串类型

    如果可能,尽量避免使用字符串类型作为标识列,因为很消耗空间,通常比数字类型慢。

    对于随机的字符串也需要多加注意,如md5、sha、uuid产生的字符串,会任意分布在很大的空间内,这会导致insert和一些select语句变得很慢:

    • 因为插入值会随机地写到索引的不同位置,所以使得insert语句更慢。这会导致页分裂、磁盘随机访问,以及对于聚簇存储引擎产生聚簇索引碎片。
    • select语句会变得更慢,因为逻辑上相邻的行会分布在磁盘和内存的不同地方。
    • 随机值导致缓存对所有类型的查询语句效果都很差,因为会使得缓存赖以工作的访问局部性原理失效。

    如果存储uuid值,则应该移除“-”符号,或者更好的做法是,用UNHEX()函数转换uuid值为16字节的数字,并且存储在一个BINARY(16)列中。检索时可以通过HEX()函数来格式化为十六进制格式。

特殊类型数据

  • IPV4地址,人们经常使用VARCHAR(15)列来存储IP地址。然而,它们实际上是32位无符号整数,不是字符串。用小数点将地址分为四段的表示方法只是为了让人们阅读容易。所以应该用无符号整数存储IP地址。MYSQL提供INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换。

MySQL schema设计中的陷阱

  • 太多的列
  • 太多的关联
  • 全能的枚举
  • 变相的枚举

范式和反范式

在范式化的数据库中,每个事实数据会出现并且只出现一次。相反,反范式化的数据库中,信息是冗余的,可能会存储在多个地方。

  • 范式化的优点
    • 范式化的更新操作通常比反范式化更快。
    • 范式化的表通常更小,可以更好地放在内存里,所以执行操作会更快。
    • 很少有多余的数据意味着更少需要使用DISTINCT或者GROUP BY语句。
  • 范式化的缺点
    • 通常需要关联,范式化可能将列存放在不同的表中。
  • 混用范式化和反范式化:
    • 最常见的反范式化数据的方法是复制或者缓存,在不同的表中存储相同的特定列。

缓存表和汇总表

  • 汇总表:

    • 保存的是使用GROUP BY语句聚合数据的表。
    • 实时计算统计值是很昂贵的操作,因为要么需要扫描表中的大部分数据,要么查询语句只能在某些特定的索引上才能有效运行,而这类特定索引一般会对UPDATE操作有影响,所以一般不希望创建这样的索引。
  • 缓存表:

    • 表示存储那些可以比较简单地从schema其他表获取(但每次获取的速度比较慢)数据的表。
  • 当使用缓存表和汇总表时,必须决定是实时维护数据还是定期重建。哪个更好依赖于应用程序,但是定期重建并不只是节省资源,也可以保持表不会有很多碎片,以及有完全顺序组织的索引(更加高效)。重建需要影子表的实现。

  • 影子表:

    • 指的是一张在真实表“背后”创建的表,当完成了建表操作后,可以通过一个原子的重命名操作切换影子表和原表。

      例如,如果需要重建my_summary,则可以先创建my_summary_new,然后填充好数据,最后和真实表做切换。

      1
      2
      3
      4
      DROP TABLE IF EXISTS my_summary_new, my_summary_old;
      CREATE TABLE my_summary_new LIKE my_summary;
      --插入纪录至my_summary_new
      RENAME TABLE my_summary TO my_summary_old, my_summary_new TO my_summary;

      在将my_summary这个名字分配给新建的表之前,将原始的my_summary表重命名为my_summary_old,就可以在下一次重建之前一直保留旧版本的数据,如果新表有问题,则可以很容易地进行快速回滚操作。

  • 物化视图

  • 计数器表

总结

  • 尽量避免过度设计,例如会导致极其复杂查询的schema设计,或者有很多列的表设计
  • 使用小而简单的合适数据类型,除非真实数据模型中有确切的需要,否则应该尽可能地避免使用NULL值
  • 尽量使用相同的数据类型存储相似或相关的值,尤其是要在关联条件中使用的列。
  • 注意可变长字符串,其在临时表和排序时可能导致悲观的按最大长度分配内存。
  • 尽量使用整形定义标识列
  • 避免使用Mysql已经遗弃的特性,例如指定浮点数的精度,或者整数的显示宽度。
  • 小心使用ENUM和SET,最好避免使用BIT