MySQL调优系列（二）——数据类型优化

野性酷女 2024-03-22 13:12 40阅读 0赞

### 一、总则 ###

#### 1、更小的数据类型更好 ####

应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少，但是要确保没有低估需要存储的值的范围，如果无法确认哪个数据类型，就选择你认为不会超过范围的最小类型。

#### 2、简单的就好 ####

简单数据类型的操作通常需要更少的CPU周期，例如：  
1、整型比字符操作代价更低，因为字符集和校对规则是字符比较比整型比较更复杂  
2、使用mysql自建类型而不是字符串来存储日期和时间  
3、用整型存储IP地址（整型存储可以减少空间，但是减少了阅读性）

#### 3、尽量避免null ####

如果查询中包含可为NULL的列，对mysql来说很难优化，因为可为null的列使得索引、索引统计和值比较都更加复杂。坦白来说，通常情况下null的列改为not null带来的性能提升比较小，所以没有必要将所有的表的schema进行修改，但是应该尽量避免设计成可为null的列。

### 二、细则 ###

#### 1、整型 ####

可以使用的几种整数类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT分别使用8，16，24，32，64位存储空间。**尽量使用满足需求的最小数据类型。**

#### 2、字符和字符串 ####

> 1、char长度固定，即每条数据占用等长字节空间；最大长度是255个字符，适合用在身份证号、手机号等定长字符串。  
> 2、varchar可变长度，可以设置最大长度；最大空间是65535个字节，适合用在长度可变的属性。  
> 3、text不设置长度，当不知道属性的最大长度时，适合用text 。

![在这里插入图片描述][340af42e93194c6098197b08c365d235.png]  
按照查询速度：char>varchar>text。

#### 3、BLOB和TEXT类型 ####

MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理。  
都是为了存储很大数据而设计的字符串类型，分别采用二进制和字符方式存储。

#### 4、datetime和timestamp ####

![在这里插入图片描述][9af7744eb97f41e583dc9c1530073e0c.png]

> 1、不要使用字符串类型来存储日期时间数据  
> 2、日期时间类型通常比字符串占用的存储空间小  
> 3、日期时间类型在进行查找过滤时可以利用日期来进行比对  
> 4、日期时间类型还有着丰富的处理函数，可以方便的对时间类型进行日期计算  
> 5、使用int存储日期时间不如使用timestamp类型

#### 5、使用枚举代替字符串类型 ####

可以使用枚举类代替常用的字符串类型，mysql存储枚举类型会非常紧凑，会根据列表值的数据压缩到一个或两个字节中，mysql在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的查找表。  
create table enum\_test(e enum(‘fish’,‘apple’,‘dog’) not null);  
insert into enum\_test(e) values(‘fish’),(‘dog’),(‘apple’);  
select e+0 from enum\_test;  
枚举在实际存储存储的是1.2.3数字。

#### 6、特殊类型数据 ####

人们经常使用varchar(15)来存储ip地址，然而，ip的本质是32位无符号整数不是字符串，可以使用INET\_ATON()和INET\_NTOA函数在这两种表示方法之间转换。

select inet_aton('1.1.1.1');
    select inet_ntoa(16843009);

#### 7、主键选择 ####

代理主键（与业务无关的，无意义的数字序列），自然主键（事物属性中的自然唯一标识）。  
推荐使用代理主键，不与业务耦合，更容易维护。通用的键策略能够减少需要编写的源码数量，减少系统的总体拥有成本。比如id。

#### 8、字符集 ####

> 1.纯拉丁字符能表示的内容，没必要选择 latin1 之外的其他字符编码，因为这会节省大量的存储空间。  
> 2.如果我们可以确定不需要存放多种语言，就没必要非得使用UTF8或者其他UNICODE字符类型，会造成大量的存储空间浪费。  
> 3.MySQL的数据类型可以精确到字段，所以当我们需要大型数据库中存放多字节数据的时候，可以通过对不同表不同字段使用不同的数据类型来较大程度减小数据存储量，进而降低 IO 操作次数并提高缓存命中率。但是设计起来更加复杂。

#### 9、存储引擎选择 ####

见[MySQL调优系列（三）——存储引擎的选择][MySQL]。

#### 10、适当的数据冗余或者拆分 ####

1、可以通过空间换取时间的方式来优化（数据冗余）：频繁引用且只能通过 Join 2张(或者更多)大表的方式才能得到的独立小字段。会造成大量不必要的 IO。  
2、当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。（拆数据库或者拆表）

#### 11、合理使用范式和反范式 ####

并不是一味的遵守范式就是好的，应根据实际业务情况合理使用范式和反范式，重点是看业务需求是什么，这也是一个设计者应该具备的能力。

[340af42e93194c6098197b08c365d235.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/5eb3b5700b8f474ea69687b18da7016d.png
[9af7744eb97f41e583dc9c1530073e0c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/43f4801be32a42579a3995401dd3f4ac.png
[MySQL]: https://blog.csdn.net/liwangcuihua/article/details/130554963?spm=1001.2014.3001.5501