为什么要进行数据类型优化?
mysql支持的数据类型非常多,选择正确的类型对获取高性能至关重要。
选取数据类型的的原则
- 更小的通常更好
一般情况下使用正确存储数据的最小数据类型,因为它们占取更小的磁盘,内存和CPU缓存 - 简单就好
例如整型比字符操作代价更低,如采用MySQL内建类型(date,time,datatime)存储时间和日期而不是字符串,另一个是用整型存储ip地址。 - 尽量避免NULL
通常情况下最好使用 NOT NULL 存储,除非真的需要 NULL:- 可为 NULL 的列会占用更多存储空间
- 可为 NULL 的列使索引,索引统计,和值比较都更加复杂
- 当可为 NULL 的列被索引时,每个索引记录都需要额外的空间。
- 不同子数据类型的特殊行为
例如,DATETIME 和 TIMESAMP 可以存储相同类型的数据,时间和日期精确到秒,然而 TIMESTAMP 只使用 DATETIME 一半的存储空间,并且会根据时区变化拥有自动更新能力。
各种数据类型的优化
1. 整数类型
-
数字的类型有两种:整数,实数。整数有下列几种类型:TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8,16,24,32,64位存储空间,它们可以存储的值的范围是 -2 的 N-1 次方到 2 的 N-1 次方减 1 。其中 N 是 存储空间位数。
-
整数类型有可选的
UNSIGNED
属性,表示不允许为负值,同时整数上线提高一倍。例如 TINYINT 的存储范围是 -128 ~ 127 ,而 TINYINT UNSIGNED 的存储范围是 0 ~ 255。有无符号都使用相同的存储空间,有相同的性能。 -
MySQL可以指定整数宽度,如 INT(20) ,但它实际上是没有用的,它不会限定取值的范围,只是规定了 mysql 的交互工具(如 mysql命令行等)显示字符的个数。对于存储和计算 INT(1) 和 INT(20) 是没有区别的。
2.实数类型
-
实数是即带有小数的数字,分为不精确类型 FLOAT 和 DOUBLE 进行近似运算,精确类型 DECIAML 进行精确运算。
-
DECIAML 可以指定精度:DECIAML(a,b) 中 a代表该数字最多允许的位数,位数是小数部分和整数部分的位数之和,b表示小数部分的最大位数,b<=a。例如 DECIAML(5,4) 代表最多只能有5位,整数部分最对只能有1位,小数部分最多只能有4位。mysql5.0及更高版本将数字打包存储到一个二进制字符串里(每4个字节存9个数字)。
-
MySQL 5.0 及更高版本 DECIAML 类型最多允许 65个数字。
3.字符串类型
-
从 mysql4.1 开始,每个字符串可以自定义字符集和排列顺序,或者校对规则。将会很大程度影响性能。
-
VARCHAR
- varchar 存储可变长字符串,比定长字符串更节省空间,越短的字符串使用越少的空间。
- varchar 使用 1 或 2个额外字节记录字符串长度,如果列长度小于或等于 255 用 1 个字节,否则用 2 个字节。
- 适合使用 varchar 的情况
- 字符串列的最大长度比平均长度大得多
- 列更新很少
-
CHAR
- char 是定长的:MySQL 总是根据定义的字符串长度分配足够空间
- 适合使用 char 的场景
- 所有值接近同一长度,如 MD5 。
- 很短字符串,如 CHAR(1) 存储 Y 和 N的值只需要 1 个字节,而 VARCHAR(1) 却需要两个字节,还有一个需要记录长度的字节。
-
注意
使用 VARCHAR(5) 和 VARCHAR(200) 存储 'hello’的空间开销是一样的,但是更长的列或消耗更多的内存。
4.BLOB 和 TEXT 类型
-
为了存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。BLOB 采用二进制数据没有排序规则和字符集,而 TEXT 有。
-
mysql 不能将 BLOB 和 TEXT 的全部长度字符串作为索引,只能使用前面一小部分。