高性能MySQL-类型选择和优化


高性能MySQL-类型选择和优化

MySQL支持的数据类型非常多,选择正确(适合)的数据类型对于获得高性能至关重要。SQL的优化是一件随时都可以做的事情,但是数据类型的修改往往需要付出很高的代价和成本。在设计数据库时就应当选择正确的数据类型。下面有几种简单的原则可以用来参考。

  • 更小的通常更好。

    一般情况下,应当尽量使用可以正确存储数据的最小数据类型。因为占用更少的磁盘、内存和CPU缓存,所以更小的数据类型通常更快。你应当估算存储数据的范围选择与之类型符合的存储数据最小的类型,例如存储年龄,年龄的大小一般不会超过200岁所以`tinyint unsigned`更好
  • 简单就好。

    简单的数据类型的操作通常需要更少的CPU周期。例如,整型比字符串的操作代价更低。
  • 尽量避免使用NULL

    除非真的需要存储`NULL`值,不然最好指定列外`NOT NULL`。在查询当中`NULL`使得索引、索引统计和值比较变得复杂,很难优化。这不是一个绝对的结论。虽然把NULL的列改为`NOT NULL`对于性能的提升比较少,除非`NULL`引起了性能问题。应当尽量避免在索引列中使用`NULL`。
    

在MySQL中有很多可以存储相同类型数据的数据类型,只是存储的长度和范围不一样、允许的精度不同,或者需要的物理空间不同。相同的大类型的不同子类型有时也有一些特殊的行为和属性。下面我们具体讨论一下。

整数类型

有两种类型的数字:整数和实数。可以存储整数的类型有:TINYINT(8位)、SMALLINT(16位)、MEDIUMINT(24位)、INT(32位)、BIGINT(64位)。存储范围计算公式为:-2^(N-1)~2^(N-1),其中N为存储空间的位数。MySQL可以为整数类型指定宽度,但这只影响一些交互工具的显示,不会影响存储和合法范围,意义不大。

整数类型有可选的UNSIGNED属性,表示不允许负数。也称无符号类型。有符号和无符号类型使用相同的存储空间,具有相同的性能,只是存储范围不同。数据类型的选择决定了内存和磁盘中保存的数据,整数计算MySQL一般使用64为的BIGINT整数(有一些特殊情况)。

实数类型

实数是带有小数部分的数字。使用DECIMAL可以存储比BIGINT更大的整数。MySQL还支持FLOATDOUBLE类型支持使用标准的浮点运算进行近似计算。CPU不支持DECIMAL运算,因此关于DECIMAL的运算都是MySQL基于浮点类型的运算,是MySQL自己实现的,但是CPU支持浮点运算,因此浮点类型相比实数类型具有更好的性能。浮点类型运算会有精度丢失的问题,因此建议在使用浮点类型时如果可以转换成整数类型存储就使用整数类型,除非程序能容忍这种精度丢失。

字符串类型

MySQL中有很多字符串类型,还有很多变种。字符串类型列可以定义自己的字符串集和排序规则,这些东西会很大程度上影响性能。VARCHARCHAR是两种最主要的字符串类型。两种数据类型的不同取决于存储引擎。

  • VARCHAR

    `VARCHAR`用于存储可变长字符串,比`CHAR`使用更少的空间。但是如果`UPDATE`了一个比原来字符串更长的字符串可能引起碎片。`VARCHAR`适合存储字符串列的最大长度比平均长度大很多的列;列的更新很少;UTF-8字符集。
  • CHAR

    `CHAR`用于存储定长的字符串,`CHAR`适合存储所有字符串长度都接近的列。`CHAR`不容易产生碎片。
    

VARCHAR(5)VARCHAR(200)存储‘hello’的空间开销是一样的,哪个更好?事实证明更长的列会使用更多的内存,因此最好的策略是只分配真正需要的空间。

MySQL还有BLOBTEXT类型用来存储很大的数据而设计的字符串数据类型。他们家族中有很多数据类型:TINYBLOBSMALLBLOBBLOBMEDIUMBLOBLONGBLOBTINYTEXTSMALLTEXTTEXTMEDIUMTEXTLONGTEXTBLOB使用二进制存储,TEXT使用字符串存储,当存储的值太大时,InnoDB会使用专门的“外部”存储区域来进行存储实际的值,“内部”只存储一个指向“外部”存储的指针。BLOB没有字符集和排序规则,但TEXT有。

在大部分情况下,我们可以使用枚举(ENUM)类型代替字符串类型进行存储。在MySQL内部会将每个值在列表保存一种“数字-字符串”的映射,存储枚举时也是非常紧凑的(每个值大约一到两个字节,比TINYINT还小)。例如存储性别类型,因为范围我们是可预知的,这就很适合枚举类型。不要使用数字作为枚举的value,这回导致双重性的混乱。

需要注意枚举类型排序时默认按照枚举索引的key进行排序,我们可以使用FIELD()函数显式的指定排序顺序。枚举类型的字符串列表是固定的,修改列表必须使用ALTER TABLE,这可能会使MySQL重建表(取决于MySQL版本)。

日期和时间类型

MySQL中保存时间和日期的类型有很多:TIMESTAMP、DATE和YEAR等。MySQL的时间精度为妙(MariaDB支持微秒级别)。大部分的时间类型没有替代品,但是有几点需要注意一下。两个相似的类型DATETIME和TIMESTAMP:他们都表示一个确切的时间,不同的是,DATETIME能表示的范围更大(1001-9999),使用8个字节存储;TIMESTAMP保存的是Unix时间戳范围也就是Unix时间戳的范围,使用4个字节存储。DATETIME保存的是固定格式的字符串,不区分时区(不管你的服务器或系统是什么时区,显示的都是同一个时间),TIMESTAMP保存的是Unix时间戳,区分时区(系统或者服务器或者连接设置的时间戳不同时,可能会有不同的时间显示)。

将Unix时间戳使用整数类型存储不会带来任何收益,所以不推荐这么做。

位数据类型

MySQL有少数几种存储类型使用紧凑的位存储类型。这些类型不管底层格式和处理方式如何,从技术的角度来看都是字符串类型。

BIT类型的存储最大长度是64位,但是在检索时是包含二进制的字符串,而在数字上下文的时候又会当作数字对待,最好避免这种类型的使用。

SET类型适合保存许多TURE/FALSE值,但是改变列的定义需要ALTER TABLE,这个代价比较大。我们可以使用TINYINT模拟这些数据,但是对于程序角度不是很好理解,需要注意。通常我们使用INYINT进行位运算模拟很多TURE/FALSE的存储。

选择标识符(identifier)

标识符的数据类型选择非常重要。选择标识符列的类型时,不应当只考虑值的类型,还应当考虑到MySQL中的一些计算和比较。标识符列有可能被其他表当作外键使用,要保持这些列的类型是一致的。在满足值的范围需求并预留了增长空间时,尽量选择更小的数据类型。整数类型是推荐的数据类型,ENUM和SET是极不推荐的类型,字符串类型如果可能避免使用。

schema设计

虽然列的数据类型选择至关重要,但是有些引起性能问题的并不是数据类型,有可能是我们的数据库结构设计不合理。下面我们一一讨论一下

  • 太多的列:MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据,然后在服务器层解码成各个列,这个操作的代价是非常高的,列的数量越多代价越大。列的数量没有什么硬性规定,我们应该检查表的大多数列是不是都会用到,还是只为了数据存储需求。
  • 太多的关联:MySQL限制了每个关联操作最多只有61张表,太多的表关联会引起性能问题。单个查询最好能在12个表以内完成。
  • 注意防止过度使用枚举,如下SQL,国家类型应该使用外键关联的方式而不是定义一个很长的枚举,因为一旦需要修改枚举的时候代价是噩梦级别的。

    CREATE TABLE ... (country enum('','1','2',...,'31')
  • NULL的使用:尽量避免NULL的使用,我们可以使用类似-1这类的值代表未知或者没有的值。但是不要极端。
  • 完全范式和完全反范式:数据库设计的范式是的优点有很多,例如:表很小很容易载入内存、数据冗余量小、数据更新方便等。缺点也是很明显的,当我们需要一份完整的数据的时候往往需要通过联表得到,联表的代价是昂贵的,甚至有些时候索引的利用率很低时,会使得查询变得很慢。范式的具体细节我会在单独的一篇中讲解。我们应当使用混合范式来进行数据库设计,适当的增加冗余(缓存字段)是可以的。

声明:Iamzz|版权所有,违者必究|如未注明,均为原创|本网站采用BY-NC-SA协议进行授权

转载:转载请注明原文链接 - 高性能MySQL-类型选择和优化


人生如逆旅,我亦是行人。