mysql优化（一）-数据类型优化

Bertha 。 2023-03-13 09:04 14阅读 0赞

### 目录 ###

*  为什么要进行数据类型优化？
 *  选取数据类型的的原则
 *  各种数据类型的优化
 *   *   *  1. 整数类型
         *  2.实数类型
         *  3.字符串类型
         *  4.BLOB 和 TEXT 类型

# 为什么要进行数据类型优化？ #

mysql支持的数据类型非常多，选择正确的类型对获取高性能至关重要。

# 选取数据类型的的原则 #

1.  更小的通常更好  
    一般情况下使用正确存储数据的最小数据类型，因为它们占取更小的磁盘，内存和CPU缓存
2.  简单就好  
    例如整型比字符操作代价更低，如采用MySQL内建类型（date，time，datatime）存储时间和日期而不是字符串，另一个是用整型存储ip地址。
3.  尽量避免NULL  
    通常情况下最好使用 NOT NULL 存储，除非真的需要 NULL：
    
     *  可为 NULL 的列会占用更多存储空间
     *  可为 NULL 的列使索引，索引统计，和值比较都更加复杂
     *  当可为 NULL 的列被索引时，每个索引记录都需要额外的空间。
4.  不同子数据类型的特殊行为  
    例如，DATETIME 和 TIMESAMP 可以存储相同类型的数据，时间和日期精确到秒，然而 TIMESTAMP 只使用 DATETIME 一半的存储空间，并且会根据时区变化拥有自动更新能力。

# 各种数据类型的优化 #

### 1. 整数类型 ###

1.  数字的类型有两种：整数，实数。整数有下列几种类型：TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8,16,24,32,64位存储空间，它们可以存储的值的范围是 -2 的 N-1 次方到 2 的 N-1 次方减 1 。其中 N 是 存储空间位数。
2.  整数类型有可选的 `UNSIGNED` 属性，表示不允许为负值，同时整数上线提高一倍。例如 TINYINT 的存储范围是 -128 ~ 127 ，而 TINYINT UNSIGNED 的存储范围是 0 ~ 255。有无符号都使用相同的存储空间，有相同的性能。
3.  MySQL可以指定整数宽度，如 INT(20) ，但它实际上是没有用的，它不会限定取值的范围，只是规定了 mysql 的交互工具（如 mysql命令行等）显示字符的个数。对于存储和计算 INT(1) 和 INT(20) 是没有区别的。

### 2.实数类型 ###

1.  实数是即带有小数的数字，分为不精确类型 FLOAT 和 DOUBLE 进行近似运算，精确类型 DECIAML 进行精确运算。
2.  DECIAML 可以指定精度：DECIAML(a,b) 中 a代表该数字最多允许的位数，位数是小数部分和整数部分的位数之和，b表示小数部分的最大位数，b<=a。例如 DECIAML(5,4) 代表最多只能有5位，整数部分最对只能有1位，小数部分最多只能有4位。mysql5.0及更高版本将数字打包存储到一个二进制字符串里（每4个字节存9个数字）。
3.  MySQL 5.0 及更高版本 DECIAML 类型最多允许 65个数字。

### 3.字符串类型 ###

1.  从 mysql4.1 开始，每个字符串可以自定义字符集和排列顺序，或者校对规则。将会很大程度影响性能。
2.  VARCHAR
    
     *  varchar 存储可变长字符串，比定长字符串更节省空间，越短的字符串使用越少的空间。
     *  varchar 使用 1 或 2个额外字节记录字符串长度，如果列长度小于或等于 255 用 1 个字节，否则用 2 个字节。
     *  适合使用 varchar 的情况
        
        1.  字符串列的最大长度比平均长度大得多
        2.  列更新很少
3.  CHAR
    
     *  char 是定长的：MySQL 总是根据定义的字符串长度分配足够空间
     *  适合使用 char 的场景
        
        1.  所有值接近同一长度，如 MD5 。
        2.  很短字符串，如 CHAR(1) 存储 Y 和 N的值只需要 1 个字节，而 VARCHAR(1) 却需要两个字节，还有一个需要记录长度的字节。
4.  注意  
    使用 VARCHAR(5) 和 VARCHAR(200) 存储 'hello’的空间开销是一样的，但是更长的列或消耗更多的内存。

### 4.BLOB 和 TEXT 类型 ###

1.  为了存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。BLOB 采用二进制数据没有排序规则和字符集，而 TEXT 有。
2.  mysql 不能将 BLOB 和 TEXT 的全部长度字符串作为索引，只能使用前面一小部分。