【一起学习Mysql】Mysql数据类型优化

每日一句：那些说星星好看的人一定没有看过你的眼睛

前言

在数据库的优化过程中，大的方向分为数据库表优化、SQL优化、服务器优化等这几方面，其中一般在开发过程中，常规的优化的点主要就是前2项，也是开发人员直接参与的两点。本文就简单从数据表优化介绍（数据类型优化）

如何选择好的数据类型

数据类型的选择的要点有哪些？

• 更小的类型通常更好
• 简单就好
• 尽量避免NULL

以上三点看起来很简单，应该是大多数时设计数据库也会去遵循的规则。具体代表什么意思呢？

• 更小的类型通常更好

  更小的类型意味着字段占用的空间更小，无论是对于表的大小还是创建索引，占用的磁盘文件就会更小，查询带来的磁盘I/O更少，效率就更高

• 简单就好

  在指定字段类型时，遵循合适的类型，一般来讲，如果可以使用整型就不用字符串，整型占用空间会比字符串类型更小，并且在比较时也比字符串更快

• 尽量避免NULL

  通常来讲如果该列不是有需求存储NULL，可以将该列设置为NOT NULL，因为如果为NULL，该列需要额外占用一个字节用来保存是否为NULL的标识符（一般这个性能提升较小）

整数类型

MySQL数据库有两种类型的数字，整数和实数

整数

如果存储整数，有以下几种类型：TINYINT（1）、SMALLINT（2）、MEDIUMINT（3）、INT（4）、BIGINT（8），括号中代表该类型占用的字节

整数的优化要点：

• 整数类型还有可选的UNSIGNED属性，表示不允许负值，如果设置为UNSIGNED，大概可以将整数的上限提高一倍。例如：TINYINT UNSIGNED的取值范围：0~255，TINYINT的取值范围：-128-127。
• int(11)表示的并不是该整数类型占用11个字符空间，整型占用的空间就是4字节，后面的(11)表示在和Mysql交互的客户端工具显示的字符数，不会限制该整型的合法范围（-2^31 到 2^31 - 1）

实数

如果存储实数，有以下几种类型：FLOAT、DOUBLE、DECIMAL。一般在更精确的计算中使用DECIMAL。

• DECIMAL(18,9)表示整数位9位，小数位9位，一共18位。DECIMAL在计算时需要额外的空间和开销，所以在需要准确计算的时候使用DECIMAL。

字符串类型

字符串类型有以下几种：char、varchar、blob、text。char/varchar为一组，blob/text为一组。

• char

  • CHAR类型的特点为不可变长度字符串，一旦确定长度，则该列保存的字符串长度不能超过定义的长度。
  • MYSQL会删除CHAR所有的末尾空格
  • 如果CHAR的长度不够定义的列的长度，则会在字符串的后面使用空格补齐
  • CHAR最大的长度为255个字符
  • CHAR一般用来保存定长的数据，例如：手机号、MD5加密的数据等

• CHAR类型在查询和更新相当于VARCHAR更快

• VARCHAR

  • VARCHAR类型是可变长度的字符串，相对来说比CHAR省空间
  • VARCHAr类型的长度是可变的，所以需要有1或者2个额外的字节空间来记录字符串的长度，如果列的最大长度小于或者等于255，则额外字节只需要1个，否则需要2个字节。
  • VARCHAR最大的长度为65535个字节
  • VARCHAR因为是可变长度，所以在更新的时候，如果更新之后的长度更大，则会产生页分裂的情况（MYSQL存储数据的基本单位为页，那么这个列的长度变大，MYSQL为了让页中可以保存下这个数据，那么就需要进行页分裂，让当前这条数据可以放进页内）

  **小知识点1：**一般定义varchar(255)就认为是最大值，是最大的吗？

  其实不是的，varchar最大可以保存65535个字节，之所以是varhcar(255) 这个里面是255字符，是因为InnoDB中，对于字符串可以索引的最大字节为767，255*3=765字节，765+1+1=767，1个字节表示额外空间，1个字节表示可为NULL。

  小知识点2：VARCHAR(5)和VARCHAR(200)保存同一个字符串hello，有什么区别吗？

  可以从两个方面来说：

  1. 两个在保存hello到磁盘上占用的内存空间是相同的，因为VARCHAR是可变长度的类型，保存到磁盘上使用的是同一个字符串，所以记录的长度都相同
  2. 但是如果在排序和统计的时候，MYSQL会给这两个长度分配不同内存来进行排序，就算存储相同的字符串，但是MYSQL会分配不同的内存。

  所以，在定义列的类型时，更小的类型一般更好

• BLOB/TEXT

  BLOB/TEXT是为存储很大的数据设计的字符串类型。

  • BLOB采用的二进制方式存储、TEXT采用字符方式存储

  • 二进制的类型有：TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB。字符类型有：TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT。

  • MYSQL在对BLOB/TEXT进行排序时和其他的列不同，会使用到前缀索引（只对字符串的前max_sort_length字节而不是整个字符串进行索引。）

  • 一般来讲，避免使用BLOB、TEXT类型，如果非要使用这两种类型，尽量使用前缀索引的方式。

• 使用枚举替换字符串

  Mysql中也有枚举类型，枚举定义：一系列不重复字符串的预定义集合。

  例如：

  CREATE TABLE enum_test (
     e ENUM('apple', 'dog')
  )
  # 插入数据的时候，对于枚举字段的数据，只可以是在创建时预定义的集合（或者alter修改枚举的预定义集合增加）
  insert into enum_test(e) values('apple');
  insert into enum_test(e) values('dog');
  

  • 枚举如果保存数据：Mysql在保存枚举的时候会压缩到1-2个字节，保存时并不是直接保存字符串，而是保存的1,2这样的整数，所以Mysql中维护了一个查找表’整数’-'字符串’这样的类似Map的表，每次查询具体数据，需要根据保存的整数到查找表中查找具体的字符串。
  • 枚举的优点：占用空间小，减少磁盘I/O，并且字符串列表固定。
  • 枚举的缺点：需要去枚举维护的查找表中根据’整数’-'字符串’这样的数据进行查找，所以会有额外的查询时间。
  • 枚举的使用场景：如果某个类的值是固定不变的（例如：性别），并且使用枚举带来的空间减少超过需要去查找表中的额外性能开销，可以使用枚举，不建议在大量不固定的字符串列使用枚举

枚举的使用注意：在创建枚举列的时候可以定义字符串集合，还可以通过ALTER TABLE的方式来修改枚举的集合，不过最好是在末尾顺序增加，否则会有数据错误。例如：e ENUM(‘apple’, ‘dog’)，如果增加为e ENUM('apple', 'cat', 'dog')，那么原本保存2的整数原本应该是dog，现在变成了cat

时间日期类型

Mysql日期类型有：date、time、datetime、timestamp

日期类型中有两种相似的datetime或者timestamp

• datetime

  该类型可以保存更大的时间范围：1001-9999年，并且展示的格式为：yyyy-MM-dd hh:mm:ss，占用的空间为8字节，日期和具体的时区没有关系

• timestamp

  时间戳类型，表示的时间范围有限，1970-2038年，展示的格式为时间戳格式，表示的具体时间和时区有关系，占用的空间为4字节

选择标识符

为标识列选择合适的数据类型对于查询效率提升也很大，例如：主键、外键等标识列

• 主键

  选择的数据类型一般会有整型、字符串来作为数据类型，选择整型比字符串带来性能更大，应该避免字符串作为标识列。因为整型占用的空间更小，比较更快，并且一般整型是有序递增，减少表的磁盘空间，查询的时候减少磁盘I/O。（例如：自增ID和UUID的选择，一般选择自增ID效率更快）

• 外键
  对外键来讲，也会遵从整型优先，字符串也可以（应该避免），不过一旦选择了一张数据类型，那么在所有的关联表中都使用相同的数据类型，否则在关联查询的时候会出现隐式类型转换（如果查询时某个字段的数据类型和给到的数据类型不同，Mysql会自动进行类型转换，不过会导致无法走索引）

小知识点：如果使用列来保存IP，选择什么数据类型？

推荐使用整型来保存，因为IP就是32位的无符号整数，中间带上小数点只是为了方便阅读。Mysql中提供INET_ATON()、INET_NTOA()函数来对IP和数字之间进行转换

SELECT INET_ATON('192.104.11.1') -> 3228044033

SELECT inet_ntoA(3228044033) -> 192.104.11.1

范式和反范式

在开发过程中，一般来讲不会出现极端的范式，也不可能出现完全的反范式，都是混合使用，具体情况具体分析，总体符合某种情况下可以提升对数据库操作的性能。

• 范式

  在范式化的数据库中，数据只会出现一次，不会出现冗余字段，符合数据库三范式规则。

  优点

  • 数据表空间更小，占用的磁盘空间小，可以更好的加载到内存中，执行操作更快。
  • 范式化的更新操作比反范式更快，因为无需更新多个列的数据

  缺点

  • 通茶查询完整的信息都需要进行表关联查询，在数据量较大的情况下或者分库分表的场景下，较为复杂并且查询速度较慢。

• 反范式

  反范式的数据库中，会存在冗余的列作为业务场景的需要，减少表的关联查询。

  优点

  • 通过增加冗余的数据，减少表的关联查询，查询相对较为简单，当数据量大的时候，这种方式较快
  • 在一张表的情况下，可以使用较为优的索引策略

  缺点

  • 表的体积会增大，占用磁盘空间更大
  • 存在冗余数据，更新数据时相对复杂，需要维护数据的一致性。

相对来说在实际应用中，范式和反范式会混合使用，根据具体情况具体选择。在反范式的情况下，根据更新频率、查询频率等选择冗余的列（因为更新在反范式会较为复杂，查询频率高的列可以作为冗余列来减少连表查询）

ALTER TABLE

Mysql的ALTER TABLE操作表的结构的时候，大多数情况下都会锁表并重建表，具体的过程为：使用新的表结构创建一个空表，然后对旧表进行锁表并查询数据插入到新表中，然后删除旧表。这个操作非常耗时并且会阻塞Mysql对该表的其他操作。如果该表还有大量的索引，就会更慢。如果表很大而且内存不足，就会更慢。

• 一般而言，大部分的ALTER TABLE都会导致阻塞、锁表重建表等

例如：

修改列的默认值：

ALTER TABLE xxx MODIFY COLUMN yyy tinyint(3) not null default 5;

MODIFY COLUMN就会锁表并重建表，如果表数据量大会相当慢，所有的MODIFY COLUMN都会重建表

使用更快的方式：

ALTER TABLE xxx ALTER COLUMN yy set default 5;

ALTER COLUMN就会更快，不涉及重建表操作。

小总结

小小的总结一下，数据类型的优化有：

• 避免过度的设计，可以适当的扩展，无需过度设计，否则系统会更加复杂

• 简单就好，更小的类型通常更好，选择数据类型时尽量符合当前字符含义即可，在指定列时尽量避免为NULL，除非有需要保存NULL

• 整型有多种类型，根据实际的数据范围进行选择，如果不保存负值范围，可以指定为UNSIGNED，会让取值范围增加一倍，INT(11)并不表示保存的字符个数，保存的数据范围只和选取的整型类型有关，后面指定的范围只是客户端工具显示的字符数

• 带有小数的数字，如果要求精确（例如金钱的计算），请使用DECIMAL

• 如果是定长的字符串，请使用CHAR更好，查询和更新比VARCHAR更快，不过注意最大只有255个字符

• 如果是可变长度的字符串，请使用VARCHAR，相当于CHAR更节省空间，最大长度为65535字节。VARHCAR(255)在InnoDB中为可使用索引的最大长度。注意：VARCHAR(10)和VARCHAR(200)保存相同的字符串使用相同的存储空间，但是在排序和统计等分配的内存块是不同的，所以在指定具体的大小时，需要根据具体的字段值来分配

• 时间日期类型推荐选择TIMESTAMP占用空间更小，不过如果需要和时区无关已经显示日期格式，可以使用DATETIME

• 尽量使用整型来作为标识列

• 范式是好的，但是大多数场景下，反范式也是必需的。

• 大部分情况下，ALTER TABLE语句会锁表并且重建表（Mysql中大部分修改表结构的操作，都会使用新结构创建一个空表，从旧表中查询数据插入到新表，并删除旧表，相当耗时，需要根据表的大小和内存来进行操作）

------ 本文参考书籍：《高性能Mysql》

微信公众号「指尖上的代码」，欢迎关注～

你的点赞和关注是写文章最大的动力～