Scheme与数据类型优化

选择合适的类型

要点

  • 更小的通常更好:尽可能把列的大小指定在合理的范围;
  • 简单,例如:能用整型就用整型;避免字符串表示日期,应该用内建的日期和时间类型;ip应转成long存储等;
  • 尽量避免默认值为 NULL,尤其是当需要在该列上创建索引的时候;
  • 例外情况:如果是稀疏数据,比如很多行都为NULL,只有少部分数据为NULL,使用NULL可以很好的提高空间利用率;

整数类型

  • 有符号和无符号类型使用相同的存储空间,并具有相同的性能;
  • 整数计算一般使用64位 BIGINT整数(一些聚合函数除外,它们使用 DECIMALDOUBLE 进行计算);
  • 整型可以指定宽度,例如:INT(11) ,对大多数应用没有意义:它不会影响合法范围,只是给一些交互功工具(例如:MySQL命令行终端)用来提示显示字符宽度。不影响存储和计算;

实数类型

  • 高精度计算:DECIMAL ,非CPU直接计算,通过MySQL服务器自身实现高精度计算,性能不如:FLOATDOUBLE
  • 大整数:BIGINT,可以把小数乘以小数位数(按精度需要,提高相应的倍数)来存储,可以提高计算性能;
  • 浮点相对精度较低:FLOATDOUBLE,CPU直接支持浮点计算,精度不一定很高,但运算更快;

字符串类型

VARCHAR : 变长,省空间,更新时需要做更多额外的工作,容易碎片化;
CHAR:定长,占用固定的空间,不容易产生碎片;

  • 存储和存储引擎具体的实现有关;
  • VARCHAR 需要额外1或2个字节记录字符串长度;
  • 虽然VARCHAR 是变长存储,但也要控制好长度,例如:VARCHAR(5)VARCHAR(200)对于存储 hello 来讲,空间开销是一样的,但在内存中保存时,通常会分配固定的大小来保持。在使用“内存临时表”进行排序或操作时会影响存储性能,在使用“磁盘临时表”进行排序时也一样,所以应该正确的分配空间;

BLOBTEXT

不同点

BLOB: 二进制存储,没有排序规则或字符集
TEXT:有字符集和排序规则 (参考:MySQL 中的 Character Set 与 Collation

查询和排序问题

  • 涉及变量:max_sort_length
  • BLOBTEXT 列的查询会使用磁盘临时表,严重影响性能,应该避免查询列(排序列)使用 BLOBTEXT
    如果无法避免时,通过使用 SUBSTRING(column, length) 将列值转换为字符串(ORDER BY 也同样适用),这样可以适用内存临时表进行计算,但要确保子串足够短,避免超过 max_heap_table_sizetmp_table_size ,超过以后又会使用磁盘临时表。