MYSQL如何选择合适的数据类型

怼烎@ 2024-03-27 09:01 84阅读 0赞

1.选择数据类型的基本原则 前提: 使用适合存储引擎。

myisam数据表，最好使用固定长度的数据列代替可变长度的数据列。

2）. memoRY存储引擎和数据列

memoRY数据表目前都使用固定长度的数据行存储，因此无论使用chaR或VaRchaR列都没有关系。

两者都是作为chaR类型处理的。

3）. innodb 存储引擎和数据列 建议使用 VaRchaR类型，对于innodb数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的 chaR列不一定比使用可变长度VaRchaR列简单。

因而，主要的性能因素是数据行使用的存储总量。

由于chaR平均占用的空间多于VaRchaR，因 此使用VaRchaR来最小化需要处理的数据行的存储总量和磁盘i/o是比较好的。

2. 固定长度数据列与可变长度的数据列1）.char与varchar l chaR和VaRchaR类型类似，但它们保存和检索的方式不同。

它们的最大长度和是否尾部空格被保留等方面也不同。

在存储或检索过程中不进行大小写转换。

下面的表显示了将各种字符串值保存到chaR(4)和VaRchaR(4)列后的结果，说明了chaR和VaRchaR之间的差别：值chaR(4)存储需求VaRchaR(4)存储需求""" "4个字节""1个字节"ab""ab "4个字节"ab "3个字节"abcd""abcd"4个字节"abcd"5个字节"abcdefgh""abcd"4个字节"abcd"5个字节 请注意上表中最后一行的值只适用不使用严格模式时；如果mysQL运行在严格模式，超过列长度不的值不保存，并且会出现错误。

从chaR(4)和VaRchaR(4)列检索的值并不总是相同，因为检索时从chaR列删除了尾部的空格。

通过下面的例子说明该差别： mysql> cReate tabLe vc (v VaRchaR(4), c chaR(4)); Query oK, 0 rows affected (0.02 sec) mysql> inseRt into vc VaLues ("ab ", "ab "); Query oK, 1 row affected (0.00 sec) mysql> seLect concat(v, "+"), concat(c, "+") FRom vc; +----------------+----------------+ | concat(v, "+") | concat(c, "+") | +----------------+----------------+ | ab + | ab+ | +----------------+----------------+ 1 row in set (0.00 sec)2）. text和blob 在使用text和blob字段类型时要注意以下几点,以便更好的发挥数据库的性能. （1）. bLob和teXt值也会引起自己的一些问题，特别是执行了大量的删除或更新操作的时候。

删除这种值会在数据表中留下很大的"空洞"，以后填入这些"空洞"的记录可能长度不同,为了提高性能,建议定期使用 oPtimize tabLe 功能对这类表进行碎片整理. （2）. 使用合成的（synthetic）索引。

合成的索引列在某些时候是有用的。

一种办法是根据其它的列的内容建立一个散列值，并把这个值存储在单独的数据列中。

接下来你就可以通过检索散列值找到数据行了。

但是，我们要注意这种技术只能用于精确匹配的查询（散列值对于类似<或>=等范围搜索操作符 是没有用处的）。

我们可以使用md5()函数生成散列值，也可以使用sha1()或cRc32()，或者使用自己的应用程序逻辑来计算散列值。

请记住数值型散列值可以很高效率地存储。

同样，如果散列算法生成的字符串带有尾部空格，就不要把它们存储在chaR或VaRchaR列中，它们会受到尾部空格去除的影响。

合成的散列索引对于那些bLob或teXt数据列特别有用。

用散列标识符值查找的速度比搜索bLob列本身的速度快很多。

（3）. 在不必要的时候避免检索大型的bLob或teXt值。

例如，seLect \*查询就不是很好的想法，除非你能够确定作为约束条件的wheRe子句只会找到所需要的数据行。

否则，你可能毫无目的地在网络上传输大量的值。

这也是 bLob或teXt标识符信息存储在合成的索引列中对我们有所帮助的例子。

你可以搜索索引列，决定那些需要的数据行，然后从合格的数据行中检索bLob或 teXt值。

（4）. 把bLob或teXt列分离到单独的表中。

在某些环境中，如果把这些数据列移动到第二张数据表中，可以让你把原数据表中 的数据列转换为固定长度的数据行格式，那么它就是有意义的。

这会减少主表中的碎片，使你得到固定长度数据行的性能优势。

它还使你在主数据表上运行 seLect \*查询的时候不会通过网络传输大量的bLob或teXt值。

3. 浮点数与定点数为了能够引起大家的重视，在介绍浮点数与定点数以前先让大家看一个例子： mysql> cReate tabLe test (c1 float(10,2),c2 decimal(10,2)); Query oK, 0 rows affected (0.29 sec) mysql> insert into test values(131072.32,131072.32); Query oK, 1 row affected (0.07 sec) mysql> select \* from test; +-----------+-----------+ | c1 | c2 | +-----------+-----------+ | 131072.31 | 131072.32 | +-----------+-----------+ 1 row in set (0.00 sec) 从上面的例子中我们看到c1列的值由131072.32变成了131072.31，这就是浮点数的不精确性造成的。

在mysql中float、double（或real）是浮点数，decimal（或numberic）是定点数。

浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围；它的缺点是会引起精度问题。

来源：[https://www.weidianyuedu.com][https_www.weidianyuedu.com]

[https_www.weidianyuedu.com]: https://www.weidianyuedu.com