mysql数据类型哪个效率更高_MySQL数据类型选择

当一个列可以选择多种数据类型时&＃xff0c;应该优先选择数字类型&＃xff0c;其次是日期或二进制类型&＃xff0c;最后是字符类型。越简单的数据类型&＃xff0c;需要的处理资源就越少。

对于相同级别的数据类型&＃xff0c;应该优先选择占用空间小的数据类型。因为在数据库中&＃xff0c;数据处理是以页为单位的&＃xff0c;每个页存储的数据量是一定的(Innodb一页是16K)&＃xff0c;列的长度越小&＃xff0c;单页能容纳的行数就越多。这有利于减少磁盘IO&＃xff0c;提高数据库性能。

最好指定列为NOT NULL。可为NULL的列对MySQL来说很难优化&＃xff0c;因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列也会占用更多的存储空间。

整数类型

注意&＃xff0c;MySql虽然可以为整数指定宽度&＃xff0c;例如int(11)&＃xff0c;但是这没有任何意义&＃xff0c;它并不会限制值的合法范围

实数类型

可以利用DECIMAL存储比BIGINT还大的整数。

CPU不支持对DECIMAL的直接计算&＃xff0c;是MySQL自身实现了对DECIMAL的高精度计算。MySQL将DECIMAL类型的数字用二进制字符串存储&＃xff0c;每4个字节存9个数字。例如&＃xff0c;DECIMAL(18,9)小数点两边各存储9个数字&＃xff0c;所以小数点两边各占用4个字节&＃xff0c;再加上小数点本身占用1个字节&＃xff0c;总共占用9个字节。DECIMAL类型的计算效率没有DOUBLE和FLOAT高。

MySQL使用DOUBLE作为内部浮点计算类型。

因为DECIMAL类型需要比较大的空间和计算开销&＃xff0c;所以应该只在对小数进行精确计算时才使用DECIMAL。

VARCHAR和CHAR类型

varchar类型用于存储可变长字符串&＃xff0c;它只占用必要的存储空间。

varchar需要使用1或2个额外字节记录字符串的长度&＃xff1a;如果字符串的最大长度小于255则只占用1个字节用于记录字符串长度&＃xff0c;如果字符串的长度大于255则要占用2个字节用于记录字符串的长度。这就代表varchar的最大长度就是65535。但是在实际中varchar列不能达到65535这么大&＃xff0c;因为对innodb来说&＃xff0c;65535是一行中所有varchar列共享的长度。

下面的这些情况使用varchar类型是合适的&＃xff1a;

字符串的最大长度比平均长度大很多

列的更新很少。这是因为行是变长的&＃xff0c;在update时可能使行变的比原来更长。如果行占用的空间增长&＃xff0c;并且在页内没有更多的空间可以存储&＃xff0c;就需要存储引擎特别的处理。例如&＃xff0c;Innodb存储引擎需要分裂页使行放入页内。这会导致增加内存碎片&＃xff0c;降低数据库性能。

char类型是定长的。它的最大宽度为255。

char类型字符串末尾的空格会被删除。

下面的这些情况使用char类型是合适的&＃xff1a;

存储很短的字符串&＃xff0c;或者所有字符串长度都比较接近。短字符串用char类型存储比用varchar类型效率要高。

经常变更的数据。因为char类型是定长的&＃xff0c;对其变更不会产生内存碎片。

varchar和char的宽度都是以字符为单位的。两种类型相比&＃xff0c;char类型会去掉字符串末尾的空格&＃xff0c;而varchar类型不会。

VARCHAR(5)和VARCHAR(20)存储hello的空间开销是一样的&＃xff0c;那么使用更短的列有什么优势吗&＃xff1f;

有很大的优势。MySql通常会分配固定大小的内存块存储内部值。虽然varchar类型在磁盘上是采用变长的方式存储&＃xff0c;但在内存中存储varchar类型字符串使用的是其最大宽度。所以更长的列会消耗更大的内存。应该只分配真正需要的空间。

日期和时间类型

MySql能存储的最小时间粒度为秒&＃xff0c;但是MySql可以使用微妙级别的粒度进行临时运算。

DATETIME类型&＃xff1a;

DATETIME可以保存从1001年到9999年之间的时间值&＃xff0c;精度为秒&＃xff0c;占用8个字节的存储空间。它将时间值保存到格式为YYYYMMDDHHMMSS的整数中&＃xff0c;保存的时间值与时区无关。

TIMESTAMP类型:

TIMESTAMP就是时间戳&＃xff0c;它保存了从1970年1月1日零点以来的秒数&＃xff0c;与UNIX时间戳值相同。TIMESTAMP只占用4个字节的存储空间&＃xff0c;表示的时间范围只能从1970年到2038年。

TIMESTAMP显示的值依赖于时区&＃xff0c;下面例子展示了在不同的时区下TIMESTAMP类型的值会有变化。

创建的test_date表分别包含了timestamp类型的列和datatime类型的列。通过set time_zone&＃61;&＃39;&＃43;10:00&＃39;命令设置时区。向表中插入一行记录&＃xff0c;两个列的值都是当前时间。通过select语句查看结果&＃xff0c;两种不同类型列的值是相同的。再通过set time_zone&＃61;&＃39;-10:00&＃39;命令修改时区&＃xff0c;从第二次select语句的执行结果可以看到&＃xff0c;timestamp类型列的值已经改变了&＃xff0c;而datetime类型列的值没有改变。

timestamp还有datetime没有的特殊属性&＃xff0c;默认情况下&＃xff0c;如果insert时没有指定第一个timestamp类型列的值时&＃xff0c;MySql会自动设置这个列的值为当前时间。在更新一行记录时&＃xff0c;MySql也会自动更新timestamp类型列的值。

可以从例子中看到&＃xff0c;MySql会自动给timestamp类型的列用当前时间赋值。而datetime类型的列不会。

当更新列时&＃xff0c;timestamp类型的列也会自动更新成当前时间。

标识列的类型选择

标识列可能会和其它值进行比较(如在关联操作中)&＃xff0c;或者根据标识列寻找其它列。标识列也可能作为其它表的外键使用。所以为标识列选择恰当的数据类型非常重要。

整数类型是标识列最好的选择&＃xff0c;因为整数计算很快并且可以AUTO_INCREMENT。

应该尽可能避免采用字符串类型作为标识列。因为字符串类型很消耗空间&＃xff0c;并且计算比数字类型要慢。