字符串类型的选择

    MySQL支持多种字符串类型，每种类型还有很多变种。这些数据类型在4.1和5.0版本发生了很大的变化，使得情况更加复杂。从MySQL 4.1开始，每个字符串列可以定义自 己的字符集和排序规则，或者说校对规则（collation)。这些东西会很大程度上影响性能。

    VARCHAR 和 CHAR 类型

    VARCHAR和CHAR是两种最主要的字符串类型。不幸的是，很难精确地解释这些值是怎么 存储在磁盘和内存中的，因为这跟存储引擎的具体实现有关。下面的描述假设使用的存 储引擎是InnoDB和/或者MylSAM。如果使用的不是这两种存储引擎，请参考所使用 的存储引擎的文档。先看看VARCHAR和CHAR值通常在磁盘上怎么存储。请注意，存储引擎存储CHAR或者 VARCHAR值的方式在内存中和在磁盘上可能不一样，所以MySQL服务器从存储引擎读 出的值可能需要转换为另一种存储格式。下面是关于两种类型的一些比较。

    

    VARCHAR

    VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间（例如，越短的字符串使用越少的空间）。有 一种情况例外，如果MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都会使用定长存储，这会很浪费空间。VARCHAR需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或 等于255字节，则只使用1个字节表示，否则使用2个字节。假设采用latinl字符集， 一个VARCHAR(10)的列需要11个字节的存储空间。VARCHAR(1000)的列则需要1002个字节，因为需要2个字节存储长度信息。VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在 UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用 的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，不同的存储引擎的处理方式是不一样的。例如，MylSAM会将行拆成不同的片段存储，InnoDB 则需要分裂页来使行可以放进页内。其他一些存储引擎也许从不在原数据位置更新数据。下面这些情况下使用VARCHAR是合适的：字符串列的最大长度比平均长度大很多； 列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。在5.0或者更髙版本，MySQL在存储和检索时会保留末尾空格。但在4.1或更老 的版本，MySQL会剔除末尾空格InnoDB则更灵活，它可以把过长的VARCHAR存储为BLOB。

    CHAR

    CHAR类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间。当存储 CHAR值时，MySQL会删除所有的末尾空格（在MySQL 4.1和更老版本中VARCHAR 也是这样实现的——也就是说这些版本中CHAR和VARCHAR在逻辑上是一样的，区 别只是在存储格式上）。CHAR值会根据需要采用空格进行填充以方便比较。CHAR适合存储很短的字符串，或者所有值都接近同一个长度。如，CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比 VARCHAR更好，因为定长的CHAR类型不容易产生碎片。对于非常短的列，CHAR比 VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是VARCHAR(1)却需要两个字节，因为还有 一个记录长度的额外字节。

    

    与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY,它们存储的是二进制字符串。二进制字符串跟常规字符串非常相似，但是二进制字符串存储的是字节码而不是字符。 填充也不一样：MySQL填充BINARY采用的是\0 (零字节）而不是空格，在检索时也不会去掉填充值。

    当需要存储二进制数据，并且希望MySQL使用字节码而不是字符进行比较时，这些 类型是非常有用的。二进制比较的优势并不仅仅体现在大小写敏感上。MySQL比较 BINARY字符串时，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比 较比字符比较简单很多，所以也就更快。

    BLOB和TEXT类型

    BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符 方式存储。实际上，它们分别属于两组不同的数据类型家族：字符类型是TINYTEXT，SMALLTEXT， TEXT，MEDIUMTEXT，L0NGTEXT;对应的二进制类型是 TINYBLOB，SMALLBLOB，BLOB， MEDIUMBLOB，LONGBLOB。BLOB是 SMALLBLOB 的同义词，TEXT是 SMALLTEXT 的同义词。与其他类型不同，MySQL把每个BLOB和TEXT值当作一个独立的对象处理。存储引擎 在存储时通常会做特殊处理。当BLOB和TEXT值太大时，InnoDB会使用专门舍“外部”存储区域来进行存储，此时每个值在行内需要1〜4个字节存储一个指针，然后在外部 存储区域存储实际的值。

    BLOB和TEXT家族之间仅有的不同是BLOB类型存储的是二进制数据，没有排序规则或字 符集，而TEXT类型有字符集和排序规则。BLOB/TEXT的值被用来排序的时候，只有前N个字节会被使用，N 对应的是数据库中的一个常量值 (max_sort_length), 如果你想指定更多的字节被用来排序，那么你可以增加max_sort_length的值或者是使用ORDER BY SUBSTRING(column, length)函数来处理

当BLOB/TEXT 被用作索引或者排序的时候，不能使用整个字段的值.在万不得已的情况下要避免把BOLB/TEXT用作索引或是排序。因为MySQL 的Memory 引擎不支持BLOB 和TEXT 类型，所以，如果查询的过程中涉及到BLOB /TEXT,则需要使用MyISAM 磁盘临时表，即使只有几行数据也是如此（在最新的Percona Server 的Memory 引擎支持BLOB 和TEXT 类型）。

因为MySQL 的Memory 引擎不支持BLOB 和TEXT 类型，所以，如果查询的过程中涉及到BLOB /TEXT,则需要使用MyISAM 磁盘临时表，即使只有几行数据也是如此（在最新的Percona Server 的Memory 引擎支持BMemory引擎频繁的访问磁盘临时表会产生严重的性能开销，最好的解决方案是尽量避免使用BLOB 和TEXT 类型。如果实在无法避免，有一个技巧是在所有用到BLOB 字段的地方都使用SUBSTRING(column, length) 将列值转换为字符串（在ORDER BY 子句中也适用），这样就可以使用内存临时表了。但是要确保截取的子字符串足够短，不会使临时表的大小超过max_heap_table_size 或tmp_table_size，超过以后MySQL 会将内存临时表转换为MyISAM 磁盘临时表。

转载于:https://my.oschina.net/u/2427170/blog/598995