【MySQL调优系列】数据类型优化

点滴岁月

于 2022-12-29 13:28:48 发布

阅读量138

点赞数

分类专栏： MySQL调优系列文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/hcycsdn520/article/details/128478421

版权

MySQL调优系列专栏收录该内容

6 篇文章 0 订阅

订阅专栏

文章目录

前言
一、整数类型
二、字符和字符串类型
三、datetime和timestamp类型
四、使用枚举代替字符串类型
五、特殊类型数据
总结

前言

MySQL 可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，执行的查询就会越快。因此 MySQL调优可以从数据类型入手。

一、整数类型

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

二、字符和字符串类型

1. 字符类型（char）

char长度固定，即每条数据占用等长字节空间；最大长度是255个字符，适合用在身份证号、手机号等定长字符串。有以下几个特点：

最大长度：255；
会自动删除末尾的空格；
检索效率、写效率会比varchar高，以空间换时间。

应用场景：

存储长度波动不大的数据，如md5摘要；
存储短字符串，经常更新的字符串。

2. 字符串类型（varchar）

varchar可变程度，可以设置最大长度；最大空间是65535个字节，适合用在长度可变的属性。一般根据实际内容长度保存数据。有以下几个特点：

使用最小的符合需求的长度；
varchar(n) n小于等于255使用额外一个字节保存长度，n>255使用额外两个字节保存长度；
varchar(10)与varchar(100)保存同样的内容，硬盘存储空间相同，但内存空间占用不同，是指定的大小；
varchar在mysql5.6之前变更长度，或者从255一下变更到255以上时时，都会导致锁表。

应用场景：

存储长度波动较大的数据，如：文章，有的会很短有的会很长；
字符串很少更新的场景，每次更新后都会重算并使用额外存储空间保存长度；
适合保存多字节字符，如：汉字，特殊字符等

3. BLOB和TEXT类型

MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理。
两者都是为了存储很大数据而设计的字符串类型，分别采用二进制和字符方式存储。
text不设置长度，当不知道属性的最大长度时，适合用text。

按照查询速度：char>varchar>text

三、datetime和timestamp类型

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

1. datetime类型

具体有以下几个特点：

占用8个字节；
与时区无关，数据库底层时区配置，对datetime无效；
可保存到毫秒；
可保存时间范围大；
不要使用字符串存储日期类型，占用空间大，损失日期类型函数的便捷性。

2. timestamp类型

具有以下特点：

占用4个字节；
时间范围：1970-01-01到2038-01-19；
精确到秒；
采用整形存储；
依赖数据库设置的时区；
自动更新timestamp列的值。

3. date类型

具有以下特点：

占用的字节数比使用字符串、datetime、int存储要少，使用date类型只需要3个字节；
使用date类型还可以利用日期时间函数进行日期之间的计算；
date类型用于保存1000-01-01到9999-12-31之间的日期。

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

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

五、特殊类型数据

工作中经常使用varchar(15)来存储ip地址，然而，它的本质是32位无符号整数不是字符串，可以使用INET_ATON()和INET_NTOA函数在这两种表示方法之间转换
案例：
select inet_aton(‘1.1.1.1’)
select inet_ntoa(16843009)

总结

数据类型优化我们可以从三个原则入手：

更小的一般更好：

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

例如：设计两张表，设计不同的数据类型，查看表的容量。

public class Test {
    public static void main(String[] args) throws Exception{
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","root","root");
        PreparedStatement pstmt = conn.prepareStatement("insert into test values(?,?)");
        for (int i = 0; i < 20000; i++) {
            pstmt.setInt(1,i);
            pstmt.setString(2,i+"");
            pstmt.addBatch();
        }
        pstmt.executeBatch();
        conn.close();
    }
}