Shaoqun Liu's blog
搜索文档…
数据类型优化
0x00 原则
- 1.更小的通常更好，更小的数据类型占用更少的磁盘空间、内存和CPU缓存，就比如说要存储性别，可以使用
unsigned tinyint类型，没必要使用int - 2.简单就好，整数操作往往比字符操作或浮点数计算代价更低，比如我们要存银行卡余额，就尽量用整数，以分做单位，就比如56.89元存成int型写作5689分钱，浮点数要考虑精度，就上面银行卡余额的例子，如果存成double类型，精度丢失就容易导致钱对不上
- 3.不要用NULL，而且必须把字段定义为NOT NULL并且提供默认值，原因如下
- 如果定义表时没有指定NOT NULL，那么默认都是允许NULL的
- 处理NULL值只能采用IS NULL或IS NOT NULL，而不能采用=、in、<、<>、!=、not in这些操作符号，例如：where name!=’Jack’，如果存在name为NULL值的记录，查询结果就不会包含name为NULL值的记录
- NULL值需要更多的存储空间且会拉低数据库的处理性能，无论是表还是索引中每行中的NULL的列都需要额外的空间来标识，增加数据库处理记录的复杂性，同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多
- NULL的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化
- 例外：在使用
InnoDB存储稀疏数据时，使用NULL有很好的空间效率，因为InnoDB使用单独的位来存储NULL，注：稀疏数据就是大部分数据为NULL，只有一小部分不是NULL，此点不适用于MyISAM
- 4.尽量不用长数据类型，就比如BLOB或TEXT以及很大的VARCHAR或CHAR，举个例子，有一个1000万行的表，里面有一个使用UTF-8编码的VARCHAR(1000)的字段，每个字符占用3个字节，最坏情况下一行这个VARCHAR(1000)字段需要3000字节，如果在ORDER BY中使用这个列，并查询扫描整个表，我们就需要30001000万=30GB的临时表，这是一笔极大的开销，会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，*导致内存命中率急剧降低，影响数据库性能
0x01 数据类型的选择
0x00 数字
整数：
整数有
TINYINT SMALLINT MEDIUMINT INT BIGINT 5种类型，分别占用8 16 24 32 64位存储空间，可以使用unsigned关键字仅存储正数，使其存储的正数的上限增加1倍，但要注意有符号和无符号占用相同的存储空间，具有相同的性能同时MySQL可以为整数指定宽度，例如
INT(9)，但是，他不会限制值的合法范围，加了和不加占用相同的存储空间且具有相同的性能，后面的9仅限制了MySQL的交互工具（MySQL命令行）用来展示字符的个数实数：
FLOAT DOUBLE使用标准的浮点数运算进行近似计算，是不精确类型DECIAML用来存储精确的小数，可进行精确计算，为精确类型FLOAT DOUBLE在存储相同范围的值时比DECIMAL占用更少的空间，且计算代价较低最后还是那句话：能用整数就不用小数，小数尽量采用扩大倍数的方式转换成整数，就比如一开始存储银行卡余额的例子，以分为单位将钱转换为整数，然后再在前端转换为小数
不要将手机号存成整数！建议存成VARCHAR(20)，原因如下
like '1350634%'查询数据库中所有山东省莱芜市移动手机号，用整数就很难实现
0x01 字符串
VARCHAR：
VARCHAR是可变长字符串，仅占用必要的存储空间，当然也可以让他变成定长字符串，就是对表指定
ROW_FORMAT=FIXEDVARCHAR会占用额外的1-2个字节来记录字符串的长度，如果列的长度小于255则使用1个字节，否则使用2个字节
VARCHAR会节约空间，但不一定节约性能，例如UPDATE一个VARCHAR字段，新的字符串长度要比原来的大，就会导致数据库需要做额外的工作
一般在下列场景下使用VARCHAR：
- 1.字符串的最大长度要比平均长度大得多
- 2.列的更新很少，所以碎片不是问题
- 3.使用了像UTF-8这样的复杂字符集，每个字符都使用不同的字节数来存储
注意：
InnoDB会把过长的VARCHAR存储成BLOBCHAR：
CHAR是定长的，注意MySQL会自动删除CHAR类型字符串最后的空格，VARCHAR不会
一般在下列场景下使用CHAR：
- 1.比较短的字符串
- 2.长度都相同字符串，就比如说密码的SHA-1值，SHA-1算法算出的16进制字符串就是定长的40位，这个时候将其存储为CHAR会相对好一些
- 3.经常变更的字符串，用CHAR存储效率要高于VARCHAR
慷慨不一定明智用VARCHAR(5)和VARCHAR(200)存储hello的空间开销是一样的，但是在查询时VARCHAR(200)会占用更多的内存，尤其是在使用内存临时表进行排序或操作时所以最好的策略是只分配真正需要的空间
0x02 长数据
二进制长数据使用BLOB类型，长字符串使用TEXT类型，BLOB和TEXT分别为两组不同的数据类型家族，BLOB为
TINYBLOB SMALLBLOB MEDIUMBLOB LONGBLOB，TEXT也类似如上，BLOB和TEXT默认都对应了SMALL的那一个MySQL会将BLOB和TEXT作为一个独立的对象存储，
InnoDB会使用专门的外部存储区域存储，然后在行内使用1-4字节的指针MySQL在排序时对于BLOB或TEXT类型，仅排序前
max_sort_length个字节MySQL不会对BLOB或TEXT中的全部数据进行索引，也不能对其使用索引消除排序
尽量不用长数据类型，原因已在0x00原则第4点说明
如果你想存放照片或用户文件这样的数据，不如在数据库里存放文件的URI，不要让数据库去干他不擅长的事情
0x03 枚举类型
尽量不要用枚举类型，原因有如下几点
- 枚举类型可以通过新建一个表的形式代替，而且效果更好，枚举类型的更新需要进行DDL操作（ALTER TABLE），新建一个表出来的话，直接INSERT就可以了
- 枚举类型排序时使用其内部存储的那个整数进行排序，比如ENUM('WWW', 'AAA')，排序的结果就是先WWW再AAA，而不是依据字符串排序为AAA再WWW
0x04 日期时间
DATETIME：
这个能保存很大范围的时间，精度为秒，把时间封装到YYYYMMDDHHMMSS这样的一个整数中，占用8个字节，默认情况下他会显示成2018-02-11 21:50:40的形式
TIMESTAMP：
这个就是Unix时间戳，从1970年1月1日到现在的秒数，占用4个字节，表示的范围从1970-2038年
在数据库中把时间都存成GMT时间（格林尼治标准时间），这样我们就能来回倒换时区了
0x05 位数据类型
位数据类型用于存放紧凑的位数据，底层存放均为字符串类型
BIT：
BIT(1)存放1个位，BIT类型最多存放64个位，BIT的行为因存储引擎而异MyISAM会打包存储所有的BIT列，比如17个单独的BIT列需要存储17位的数据，MyISAM需要3个字节（24个位）来存放，计算方法为：ROUNDUP(17/8)*8InnoDB和Memory引擎会为每个BIT列使用一个足够存储的最小整数类型，所以不能节省空间
BIT底层存放为字符串类型，所以当1存放在BIT(1)中是得到的结果是字符1的ASCII码值而不是数字1，如下：
1
mysql> create table bittest(a bit(8));
2
Query OK, 0 rows affected (0.27 sec)
3
4
mysql> insert into bittest value(65); # 插入数字
5
Query OK, 1 row affected (0.00 sec)
6
7
mysql> insert into bittest value(b'01000010'); # 插入二进制数据
8
Query OK, 1 row affected (0.00 sec)
9
10
mysql> select a, a+0 from bittest;
11
+------+------+
12
| a | a+0 |
13
+------+------+
14
| A | 65 |
15
| B | 66 |
16
+------+------+
17
2 rows in set (0.00 sec)
Copied!
我们可以看出，使用INSERT语句插入数字65，然后SELECT出来是65所对应的ASCII字符A，在SELECT一个数学表达式时
a+0，所参与运算的值为数字65最好不要使用BIT类型，如果你使用BIT类型的缘由是要存储布尔值，可以使用CHAR(1)或者TINYINT来表示
存放布尔值的野路子之使用CHAR(0)CHAR(0)可以保存NULL值和长度为0的字符串（空字符串）我们可以假定:当is null的时候为true，当is not null的时候为false当值为NULL的时候他占用1个bitMySQL permits you to create a column of typeCHAR(0). This is useful primarily when you have to be compliant with old applications that depend on the existence of a column but that do not actually use its value.CHAR(0)is also quite nice when you need a column that can take only two values: A column that is defined asCHAR(0) NULLoccupies only one bit and can take only the valuesNULLand''(the empty string).既然他是野路子，就说明不推荐使用这样的方法
SET：
SET跟枚举差不多，如果需要保存一组布尔值就可以考虑使用SET，它在MySQL内部以一系列打包的位的集合来表示
不推荐使用SET，SET和枚举一样改变需要进行DDL操作（ALTER TABLE)，DDL操作成本太高，灵活性较差
不过如果存放ACL访问控制列表这样的，只有几个固定的取值还是可以的，对于Linux下文件的
rwx权限控制还是建议将其转换为整数存放0x02 标识列（identifier）的选择
标识列主要就是用于主键这样的，用来区分每一行的数据，要注意的一点是一旦选择了一种类型，要确保每个关联表中使用精确的相同类型，包括像unsigned这样的属性，混用不同的数据类型容易导致性能问题，在比较时需要进行隐式类型转换，有可能导致很难发现的错误，整数是最好的选择，速度快且可以使用
AUTO_INCREAMENT避免使用字符串类型，首先因为他们很消耗空间，而且比较时速度较整数慢，对于
MyISAM引擎默认会对字符串进行压缩存储，会导致很大的性能问题更要避免使用MD5或SHA1产生的值做标识列，因为MD5或SHA1产生的值散列分布在很大的空间内，会导致INSERT和SELECT变得很慢，原因如下：
- INSERT时插入的值会随机散列在索引的不同位置，会导致页分裂，磁盘随机访问
- SELECT时逻辑上相邻的行为会分布在磁盘和内存的不同地方
- 随机值导致索引对所有类型的查询语句效果都很差，整个数据集都一样热，缓存任何一部分特定数据到内存都没有好处，如果工作集比内存大，缓存将会有很多刷新和不命中