选择MYSQL列的类型(2)_MySql_学习笔记★闵涛★计算机学习电脑编程软硬件技巧


	转至繁体中文版	\| 网站首页 \| 图文教程 \| 资源下载 \| 站长博客 \| 图片素材 \| 武汉seo \| 武汉网站优化 \|

选择MYSQL列的类型(2)

作者：闵涛文章来源：闵涛的学习笔记点击数：625 更新时间：2009/4/22 20:44:06

列类型的选择会在几个方面影响查询性能。如果记住下几节讨论的一般准则，将能够选出有助于MySQL有效处理表的列类型。
1. 数值与串的运算
数值运算一般比串运算更快。例如比较运算，可在单一运算中对数进行比较。而串运算涉及几个逐字节的比较，如果串更长的话，这种比较还要多。如果串列的值数目有限，应该利用ENUM 或SET 类型来获得数值运算的优越性。这两种类型在内部是用数表示的，可更为有效地进行处理。例如替换串的表示。有时可用数来表示串值以改进其性能。例如，为了用点分四位数（d o t t e d - q ua d）表示法来表示IP 号，如19 2 . 16 8 . 0 . 4，可以使用串。但是也可以通过用四字节的UNSIGNED 类型的每个字节存储四位数的每个部分，将IP 号转换为整数形式。这即可以节省空间又可加快查找速度。但另一方面，将IP 号表示为INT 值会使诸如查找某个子网的号码这样的模式匹配难于完成。因此，不能只考虑空间问题；必须根据利用这些值做什么来决定哪种表示更适合。
2. 更小的类型与更大的类型
更小的类型比更大的类型处理要快得多。首先，它们占用的空间较小，且涉及的磁盘活动开销也少。对于串，其处理时间与串长度直接相关。一般情况下，较小的表处理更快，因为查询处理需要的磁盘I/O 少。对于定长类型的列，应该选择最小的类型，只要能存储所需范围的值即可。例如，如果MEDIUMINT 够用，就不要选择B I G I N T。如果只需要FLOAT精度，就不应该选择D O U B L E。对于可变长类型，也仍然能够节省空间。一个BLOB 类型的值用2 字节记录值的长度，而一个LONGBLOB 则用4 字节记录其值的长度。如果存储的值长度永远不会超过6 4 K B，使用BLOB 将使每个值节省2 字节（当然，对于TEXT 类型也可以做类似的考虑）。
3. 定长与可变长类型
定长类型一般比可变长类型处理得更快：
■ 对于可变长列，由于记录大小不同，在其上进行许多删除和更改将会使表中的碎片更多。需要定期运行OPTIMIZE TABLE 以保持性能。而定长列就没有这个问题。
■ 在出现表崩溃时，定长列的表易于重新构造，因为每个记录的开始位置是确定的。可变长列就没有这种便利。这不是一个与查询处理有关的性能问题，但它必定能加快表的修复过程。如果表中有可变长的列，将它们转换为定长列能够改进性能，因为定长记录易于处理。在试图这样做之前，应该考虑下列问题：
■ 使用定长列涉及某种折衷。它们更快，但占用的空间更多。CHAR(n) 类型列的每个值总要占用n 个字节（即使空串也是如此），因为在表中存储时，值的长度不够将在右边补空格。而VARCHAR(N) 类型的列所占空间较少，因为只给它们分配存储每个值所需要的空间，每个值再加一个字节用于记录其长度。因此，如果在CHAR 和VARCHAR列之间进行选择，需要对时间与空间作出折衷。如果速度是主要关心的因素，则利用CHAR 列来取得定长列的性能优势。如果空间是关键，应该使用VARCHAR 列。
■ 不能只转换一个可变长列；必须对它们全部进行转换。而且必须使用一个ALTE RTABLE 语句同时全部转换，否则转换将不起作用。
■ 有时不能使用定长类型，即使想这样做也不行。例如对于比255 字符长的串，没有定长类型。
4. 可索引类型
索引能加快查询速度，因此，应该选择可索引的类型。
5. NULL 与NOT NULL 类型
如果定义一列为NOT NULL，其处理更快，因为MySQL在查询处理中不必检查该列的值弄清它是否为NULL，表中每行还能节省一位。避免列中有NULL 可以使查询更简单，因为不需要将NULL 作为一种特殊情形来考虑。通常，查询越简单，处理就越快。所给出的性能准则有时是互相矛盾的。例如，根据MySQL能对行定位这一方面来说，包含CHAR 列的定长行比包含VARCHAR 列的可变长行处理快。但另一方面，它也将占用更多的空间，因此，会导致更多的磁盘活动。从这个观点来看， VARCHAR 可能会更快。作为一个经验规则，可假定定长列能改善性能，即使它占用更多的空间也如此。对于某个特殊的关键应用，可能会希望以定长和可变长两种方式实现一个表，并进行某些测试以决定哪种方式对您的特定应用来说更快。
2.3.4 希望对值进行什么样的比较
根据定义串的方式，可以使串类型以区分大小写或不区分大小写的方式进行比较和排序。表2-14 示出不区分大小写的每个类型及其等价的区分大小写类型。根据列定义中给不给出关键字B I N A RY，有的类型（CHAR、VARCHAR）是二进制编码或非二进制编码的。其他类型（B L O B、T E X T）的“二进制化”隐含在类型名中。

请注意，二进制（区分大小写）类型仅在比较和排序行为上不同于相应的非二进制（不区分大小写）类型。任意串类型都可以包含任意种类的数据。特别是， TEXT 类型尽管在列类型名中称为“T E X T（文本）”，但它可以很好地存储二进制数据。如果希望使用一个在比较时既区分大小写，又可不区分大小写的列。可在希望进行区分大小写的比较时，利用B I N A RY 关键字强制串作为二进制串值。例如，如果my_col 为一个CHAR 列，可按不同的方式对其进行比较：
my_col = “A B C” 不区分大小写
BINARY my_col =“A B C” 区分大小写
my_col = BINARY“A B C” 区分大小写
如果有一个希望以非字典顺序存储的串值，可考虑使用ENUM 列。ENUM 值的排序是根据列定义中所列出枚举值的顺序进行的，因此可以使这些值以任意想要的次序排序。
2.3.5 计划对列进行索引吗
使用索引可更有效地处理查询。索引的选择是第4 章中的一个主题，但一般原则是将WHERE 子句中用来选择行的列用于索引。如果您要对某列进行索引或将该列包含在多列索引中，则在类型的选择上可能会有限定。在早于3.23.2 版的MySQL发行版中，索引列必须定义为NOT NULL，并且不能对BLOB 或TEXT 类型进行索引。这些限制在MySQL3.23.2 版中都撤消了，但如果您正使用一个更早的版本，不能或不愿升级，那么必须遵从这些约束。不过在下列情形中可以绕过它们：
■ 如果可以指定某个值作为专用的值，那么能够将其作为与NULL 相同的东西对待。对于DATE 列，可以指定“0000 - 00 - 00”表示“无日期”。在串列中，可以指定空串代表“缺值”。在数值列中，如果该列一般只存储非负值，则可使用- 1。
■ 不能对BLOB 或TEXT 类型进行索引，但如果串不超过255 它符，可使用等价的VARCHAR 列类型并对其进行索引。可VARCHAR(255) BINARY 用于BLOB 值，将VARCHAR(255) 用于TEXT 值。
2.3.6 列类型选择问题的相互关联程度
不要以为列类型的选择是相互独立的。例如，数值的取值范围与存储大小有关；在增大取值的范围时，需要更多的存储空间，这会影响性能。另外，考虑选择使用AUTO _INCREMENT 来创建一个存放唯一序列号的列有何含义。这个选择有几个结果，它们涉及列
的类型、索引和NULL 的使用，现列出如下：
■ AUTO_INCREMENT 是一个应该只用于整数类型的列属性。它将您的选择限定在TINYINT 到BIGINT 之上。
■ AUTO_INCREMENT 列应该进行索引，从而当前最大的序列号可以很快就确定，不用对表进行全部扫描。此外，为了防止序列号被重用，索引号必须是唯一的。这表示必须将列定义为PRIMARY KEY 或定义为UNIQUE 索引。
■ 如果所用的MySQL版本早于3 . 2 3 . 2，则索引列不能包含NULL 值，因此，必须定义列为NOT NULL。所有这一切表示，不能像如下这样只定义一个AUTO_INCREMENT 列：