MySQL数据类型优化

前言

本文主要讲述MySQL中一些数据类型的优化点！如整数类型、实数类型、字符串类型、日期和时间等…MySQL支持的数据类型非常多，对数据类型进行优化也是提高MySQL性能的一种重要手段，在考虑如何优化数据类型时先考虑如下几个原则

1.更小通常更好
一般情况下，在正确使用且合理的使用数据类型时，应该尽量使用最小数据类型(如整型，最小的类型为TINYINT，但是我们通常会使用INT，如果存储的值范围只在0-100，那么我们完全可以使用TINYINT作为最小存储类型)，通常更小数据类型通常更快，因为占用的磁盘、内存、CPU缓存会更少，并且处理时需要的CPU周期也更少。弊端：如果存储的值超过了最小类型值的范围，后期调整起来比较麻烦，且耗时。如果实在拿捏不准那就选择你认为业务上不会超过范围的最小类型。

2.简单就好
简单数据类型操作起来通常使用更少的CPU周期，例如：整数比字符操作代价更低，因为字符集合校对规则(做排序规则)使用字符比整型更加复杂这里举两个例子

• 使用MySQL内部提供的数据类型，如date、time、datatime而不是使用字符串来存储日期和时间
• 使用整型存储IP地址，下面特殊类型中会讲 --》

3.尽量避免NULL
通常我们表中中会有些NULL的数据，尽管业务上这样没毛病，允许为NULL，但是在对MySQL做优化时这个就更加复杂了，最好指定为NOT NULL除非真的需要存储为NULL，在查询中包含可为NULL的列性能优化起来更难，因为NULL的列使得索引，索引统计和值对比都更加复杂,并且NULL会使用更多的存储空间，MySQL内部也需要特殊处理，在可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MyISAM引擎中甚至还可能导致固定大小的索引变为可变大小的索引(例如证书类型的索引)，虽然这里是尽量避免使用NULL但是改外NUT NULL带来的性能提升也是仅仅有限的，就是比较小，如果计划在列上建立索引，就因该尽量避免设计为可为NULL的列，例外：InnoDB使用单独的为(bit)存储NULL值，对于稀疏数据(绝大部分值为NULL，少数行的列为非NULL的值，有很好的空间效率)但是这点不适用于MyISAM。

通常我们设计表字段的时候是先确定大类型，在通过实际业务确定具体类型，值得注意的是相同大类型不同子类数据有时也有一些特殊的属性和行为，下面就来对比一下

整数类型

整数类型中MySQL支持如下几种

类型	大小	范围(有符号)	范围(无符号)	用途
TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值

这里有几个值得注意点

1. INT类型和INTEGER类型：我们有时候看到的是INT类型，有时候看到的是INTEGER类型，实际上关键字INT是INTEGER的同义词，没有区别，而且不同的可视化工具对二者的支持是不同的，可能有的工具只有INT类型提供选择

2.UNSIGNED(无符号)属性：整数类型在创建的时候可以选择UNSIGNED(无符号)属性，表示是否可以为负数，这样可以是正数上限提高一倍，例如TINYINT的范围是-128，127，但是设置UNSIGNED后那么范围可以为0~255,有无符号类型使用的是相同的存储空间，并且性能是一样的，所以可以根据实际情况选择合适的类型

3.整数类型指定宽度：例如INT(11)对于大多数应用是没有意义的，这样设置11位并不会限制值的合法范围，只是用来规定限制交互工具显示字符个数，对于存储和计算INT(1)和INT(20)是一样的。

实数类型

实数类型是带有小数部分的数字，这里带小数并不是说存储的整数类型小，在使用DECIMA存储时比BIGINT存储还大。MySQL即支持精确类型，也支持不精确类型

类型	大小	范围(有符号)	范围(无符号)	用途
FLOAT	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度，浮点数值
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度，浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

注意
1.计算 ：类型用于存储精确小数，在MySQL5.0和之后的版本，DECIMAL 支持精确计算，之前的版本是使用浮点运算来实现DECIMAL 的计算的，但是这样会导致丢失精度，在5.0和之后的版本DECIMAL 只是一个存储类型，因为CPU不支持DECIMAL 的直接计算，所以5.0和之后的版本MySQL自身内部实现了DECIMAL 的高精度计算，相对而言CPU直接指出的原生浮点计算，所以浮点运算明显更快
2.精度：浮点类型和DECIMAL 都可以指定精度，对于DECIMAL 而言可以指定小数点前后允许出现最大位数，这样会影响列的空间消耗，有很多种方式制定浮点需要的精度，则会是MySQL悄悄使用不同的数据类型或者在存储时对值进行取舍，这些精度定义是非标准的，所以建议只指定类型，不指定精度
3.限制：MySQL5.0和更高的版本DECIMAL 允许最多65个数字，而早期的MySQL限制为254个数字，并且保存为未压缩的字符串，即每个数字一个字节，然而这些实际上计算中并不能使用这么大的数字，因为DECIMAL 只是一种存储格式在计算中DECIMAL 会转为DOUBLE类型
4.技巧：浮点类型在存储和DECIMAL 相同的范围值时通常使用更少的空间，FLOAT使用4个字节存储DOUBLE是8个，相比FLOAT有更高的精度和更大的范围，和整数类型一样，能选择的只有存储类型，MySQL使用DOUBLE作为内部浮点计算类型。考虑需要而外空间和计算开销，所以尽量在对小数进行精确计算的时候才是用DECIMAL，如存储财务数据时，且数据量比较大，可以考虑使用BIGINT替代DECIMAL 进行计算，我们可以将财务数据精确到万分之一，也就是将金额乘以一百万，然后在存储在BIGINT中，这样同样可以避免浮点计算导致精度丢失和DECIMAL 精确计算高代价问题

字符串类型

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

MySQL中支持多种支付串类型，而且种字符串类型对应的又有很多的变种，在4.1和5.0版本发生了很大的变化，是的情况变得更加复杂，从4.1开始，每个字符串列可以自定义自己的字符串和排序规则，或者是校对规则，这些东西会很大程度影响性能

VARCHAR和CHAR

首先VARCHAR和CHAR是两种最主要的字符串类型，而且二者存储在磁盘和内存中是个存储引擎的具体实现相关的，这里只要讲InnoDB和MyISAM

VARCHAR：VARCHAR存储可变长度字符串，是最常见的字符串数据类型

1. 相比定长类型更省空间，仅使用必要空间(例如:越短的字符串使用越少的空间)，例外:如果MySQL表使用ROW_FORMAT=FIXED创建的话，每行定长存储，这样浪费空间
2. VARCHAR需要使用1或2个额外字节记录字符串长度，如果列长度≤255字节，那么只需要1个字节表示，(这也是我们看到一些企业建表的时候使用255的原因)，VARCHAR(10),的列需要11个直接存储空间，VARCHAR(1000)的列实际需要1002个字节，因为2个字节存储长度信息
3. VARCHAR类型是节省了空间，所以对性能也有帮助，但是由于长度可变，在UPDATE时可能是的行变的比原来长，需要做额外的工作(如果一行占用的空间增长，并且没有更多的空间可以存储，那么在MyISAM中会将行拆成不同的片段存储，InnoDB则需要分裂页来实现)
4. 以下情况优先使用VARCHAR类型，字符串列的最大长度比平均长度大很多，列更新少(所以碎片问题可忽略)，使用了UTF-8这样复杂的字符集，每个字符都是用不同的字节进行存储,注意:在5.0或者更高的版本后MySQL在存储和检索时会保留末尾空格，但是4.1或更老的版本会剔除末尾空格
   

CHAR：CHAR类型是定长的，MySQL总是根据定义的字符串长度分配足够的空间

1. 存储CHAR时，MySQL会删除所有末尾的空格
2. CHAR适合存储很短的字符串，或者接近同一长度的，空间利用率更高，例如适合存储MD5，例如存储Y、N,CHAR(1)只需要1个字节，但是VARCHAR(1)却需要2个字节，因为还有一个需要记录长的的而外字节，
3. CHAR适合存储经常变更的数据，相比VARCHAR类型，因为CHAR不易长生碎片
CHAR类型可能有一点难以理解，下面以一个具体的例子来演示
   
   数据如何存储取决于存储引擎的具体实现，并非所有的存储引擎都会按照相同的方式处理定长和变长的字符串，如Memory只支持定长的行，不过填充和截取空格的行在不同的存储引擎上都是一样的，以为这是在MySQL服务器层处理的。

与VARCHAR和CHAR类似的还有BINARY和VARBINARY，这两个是存储二进制字符串的，二进制字符串和常规字符串非常类似，只是存储的是字节码而不是字符，填充也不一样，MySQL填充BINARY是采用\0而不是空格，在检索时也不会去掉填充值，注意当需要存储二进制数据时，最好使用字节码而不是字符串，二进制比较的优势不止在大小写敏感，MySQL比较BINARY字符串时，每次按一个字节，并且根据该字节的数值进行比较，而且二进制比较比字符简单很多，所以更快

BLOB和TEXT类型
BLOB和TEXT类型是为了存储很大的数据设计的字符串类型，分别采用二进制和字符方式存储，与其他类型不同，MySQL会将BLOB和TEXT类型当做一个独立的对象处理，存储引擎在存储时通常会做特殊处理，当BLOB和TEXT类型值太大是InnoDB会使用专门外部的空间存储，此时每个值在行内需要1~4个字节存储一个指针，然后外部存储区域存储实际的值，BLOB和TEXT类型之间仅有存储类型一个是二进制，一个是字符串，BLOB没有排序规则和字符集，而TEXT有字符集和排序规则

MySQL对BLOB和TEXT类型类型排序个其他类型排序是不同的，只对每个列的最前max_sort_length字节而不是整个字符串做排序，如果只需要排序前面一小部分字符，则可以减小max_sort_length或者使用ORDER_BY SUSTRING(cloumn,length),MySQL不能将BLOB和TEXT类型列全部长度的字符串进行索引，也不能使用这些索引消除排序

如果EXPLAIN执行计划的Extra列包含Using temporary 则说明这个查询使用了隐私临时表

时间和日期类型

MySQL中支持多种时间、日期类型，这类类型不像其他类型，大部分时间类型都没有替代品，因此没有什么最佳选择的问题，只需要考虑实际业务场景如下

类型	大小	范围	格式	用途
DATE	3 bytes	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3 bytes	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
DATETIME	8 bytes	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4 bytes	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

DATETIME和TIMESTAMP区别：对于大多数场景二者都能用，但是是有很大的区别的，DATETIME可以保存大范围的值，并且存入的值是什么样取出来的时候就是什么样，和所在的时区无关，TIMESTAMP只能保存指定范围的时间，MySQL内部提供FROM_UNIXTIME(）函数将Unix时间转为为如期，并提供UNIX_TIMESTAMP()函数将日期转换为时间戳，在老版本的4.0以及之前的版本，日期时间的各部分不会显示任务标点符号，4.1以及之后的版本会按照和DATETIME一样格式化值，但是这仅仅是显示格式上的区别，各个版本TIMESTAMP在存储格式上都是一样的，TIMESTAMP依赖时区，如存储值为0时显示的时间为1969-12-31 19:00:00 和格林尼治时间差5个小时，这是因为和连接访问的时区有关的，TIMESTAMP默认值及时没有给TIMESTAMP设置默认值，那么在插入的时候或者更新的时候(不指定值)，会以当前时间插入，TIMESTAMP列默认为 NOT NULL的这也和其他数据类型不一样

注意
除业务需要情况下，尽量使用TIMESTAMP，因为比DATETIME空间利用效率更高，但是切记不要使用整数类型存储时间戳，这样没有任何意义，并且格式通常不方便，但是特殊除外，如比微妙级别的，那么我们可以使用BIGINT类型存储时间戳，或者使用BOUBLE存储秒之后的小数部分，这两种方式都可以