mysql数据库存储列表 mysql数据存储种类

转载

mob64ca1409970a 2023-09-18 00:15:40

文章标签 mysql数据库存储列表 mysql 优化 MySQL 字符串 文章分类 MySQL 数据库

1、整数类型

MySQL 中存储整数可以使用这几种整数类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，其分别使用8位，16位，24位，32位以及64位存储空间，其可以存储的范围为-2^(N-1) 到 2^(N-1)-1 ，其中N为存储空间的位数。
另外可以使用UNSINGED属性，标识不允许使用负值，这样可以使得其范围扩大一倍。例如使用TINYINT类型的范围-128-127，使用TINYINT UNSIGNED 类型后的范围为0-255，使用有符号类型和无符号类型具有相同的性能，这点可以根据实际情况使用，选择合适的类型。
选择合适的数据类型可以决定MySQL在存储这些整数的方式，但是整数的计算通过使用的是64 位的BIGINT类型，一些聚合函数例外，比如书AVG，他们通常使用 DECIMAL类型或者DOUBLE 类型。

一般的时候用我们通常喜欢指定宽度，例如 INT(11) ，但大多数情况下这是没有意义的，他不会限制数据的合法范围，只是规定了MySQL客户端显示的方式，对于存储和计算来说 INT(1) 和 INT(32) 是相同的。

2、实数类型

实数（real number）是带有小数部分的数据，但是实数类型的存在并非是存储小数，还可以用DECIAL类型保存比BIGINT还大的数据。在实数类型中MySQL支持精准类型也支持不精准类型。
Float & Double 支持标准的浮点型非精准允许那，所以MySQL中Float & Double的运算和具体的系统运算有关。
Decimal 类型用于存储精确的小数，在MySQL5.0 + 以上的版本，MySQL实现了Decimal的运算，但是相对于CPU直接支持的Float & Double类型，浮点型运算显然更快。

Float占用4个字节，Double占用8个字节
浮点型存储所占空间通常比Decimal更少
和整型数据一样，Float和Double只是存储类型，MySQL内部使用Double作为计算类型
因此占用存储空间和CPU资源，通常建议只有在精确计算的地方使用Decimal
数据量较大的时候建议使用BigInt 代替浮点型以及Decimal

3、字符串类型

MySQL 支持多种字符串类型的数据，并且每种类型还有很多变种。从MySQL 4.1 开始，每个字符胡灿可以定义自己的字符集和排序规则，或者说校对规则(collation), 这些东西在很大程度上回影响性能。

VARCHAR 和 CAHR 类型

VARCHAR 和CHAR 是两种主要的字符串类型，这两种类型在不同的存储引擎中的存储方式不同，下面的介绍仅仅是以INNODB 或者 MyISAM 存储引擎来介绍的。

VARCHAR 类型可以用于存储可边长字符串，是最常见的字符串数据类型，他比定长的类型更节省空间，因为它仅仅使用必要的空间，比如定义 VARCHAR(255) 和 CHAR(255) 来存储 "HELLO"的话，VARCHAR 仅仅占用 5+1 个字节数据(VARCHAR需要用1个或者2个字节保存字符串的长度，超过255 则就是2个字节)，而CHAR类型则要占用255 个字节。

虽然 VARCHAR 存储节省可空间，对性能有所帮助，但由于行是边长的，所以在 UPDATE 的时候可能需要拓展空间，原业内没有更多的空间来做存储，这需要更多的额外工作。MyISAM 会将行分为不同的存储片段，InnoDB 会分裂页使之能够存储到业内连续的空间中。

但 CHAR 类型是定长的，当存储 CHAR类型的数据是，MySQL 会删除尾部的空格，而 VARCHAR 则不会删除。在存储方法上，CHAR 类型的会在尾部填充空格以保持数据填充为CHAR的长度。对于经常变动的类型，CHAR 类型也不容易产生碎片，另外对于非常小的字段类型，CHAR(1) 也比 VARCHAR(1) 占用的空间更小( VARCHAR 还需要额外的存储字符串长度 )

BLOB 和 TEXT 类型

BLOB 和 TEXT 类型都是为了存储很大的数据而设计的字符串数据类型，分为采用了二进制存储和字符存储的方式，所以BLOB 没有排序规则和字符集，TEXT则有之。实际上他们分属于两种不同的存储类型，字符集类型有: TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT 对应的二进制存储类型包括： TINYBLOB，SMALLBLOB，BLOB，MEDIUMBLOB，LONGBLOB。

当存储BLOB或者TEXT这种大数据对象的时候，MYSQL 会把其当做独立的对象处理，MySQL 会使用专门的外部存储区域来存储，此时每个行内只需要存储1-4个字节的指针，在外部存储区域存储真正的值。MYSQL 对于BLOB和TEXT 排序仅仅会根据变量的前 max_sort_length 的自己进行排序，并不会根据整个内容排序，所以如果需要较少的排序，则可以减少 max_sort_length;

mysql root@localhost:(none)> SHOW VARIABLES LIKE 'max_sort_length';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_sort_length | 1024  |
+-----------------+-------+

使用枚举 (ENUM) 代替字符串

有时候可以使用枚举类型代替字符串类型。MySQL 在存储枚举类型的时候非常紧凑，会把其所在枚举位置的整数存储，存储的关系是一个数字-字符传映射表。下面的实例中可以证明MySQL处理索引的时候存储的是数字类型。

-- 创建表
mysql root@localhost:a>  create table enum_t (name ENUM('fish','apple','dog','cat'));
Query OK, 0 rows affected

-- 插入数据
mysql root@localhost:a> INSERT INTO enum_t VALUES('fish'),('apple'),('cat');
Query OK, 3 rows affected


-- 查询数据
mysql root@localhost:a> select name,name + 0 from enum_t;
+-------+----------+
| name  | name + 0 |
+-------+----------+
| fish  | 1.0      |
| apple | 2.0      |
| cat   | 4.0      |
+-------+----------+
3 rows in set

可以看到，fish 对应的在枚举集合的位置为1，cat对应的位置为4，所以其+0 操作的值为数字，也证明了之前表述的枚举在存储的时候存储是代表其在枚举集合的位置的整数。有趣的是，在对枚举类型排序的时候，也是按照这个整数进行排序，而不是枚举的字符串形式排序,这个问题在业务中需要特别注意。

mysql root@localhost:a> select name,name + 0 from enum_t ORDER BY name DESC;
+-------+----------+
| name  | name + 0 |
+-------+----------+
| cat   | 4.0      |
| apple | 2.0      |
| fish  | 1.0      |
+-------+----------+
3 rows in set


-- 如果需要对枚举值排序，则使用如下的方式

mysql root@localhost:a> select name,name + 0 from enum_t ORDER BY FIELD(name,'apple','cat','dog','fish') DESC;
+-------+----------+
| name  | name + 0 |
+-------+----------+
| fish  | 1.0      |
| cat   | 4.0      |
| apple | 2.0      |
+-------+----------+
3 rows in set

4、时间日期类型

MySQL 也提供了多种日期类型，比如 YEAR 和 DATE，在MySQL 中能够存储的时间类型最小粒度是秒(MySQL 的开源版本 MariaDB 可以保存到微秒)。一般情况下，我们常常使用 MySQL 提供的 DATETIME 以及 TIMESTAMP 两种类型。

DATETIME 可以保存的时间范围为 1001年到9999年，精度为秒，使用8个字节存储，其内部存储把DATETIME 封装为 YYYYMMDDHHMMSS 格式的整数然后保存，因此其与时区无关。DATETIME是一种可排序的无歧义的时间格式，这是ANSI定义的时间和日期表示方法。
TIMESTAMP 保存了从1970年1月1日凌晨开始到现在的秒数。TIMESTAMP 使用4个字节存储，相比于 DATETIME的8个字节，其表示的时间范围就很少，其只能标识1970年-2038年。TIMESTAMP 的显示依赖于时区，MySQL 时区，操作系统时区甚至 MySQL 连接的时区设置都会响应显示结果。TIMESTAMP 默认是不允许为NULL的，不过仍然可以通过命令设置默认为NULL。

CREATE TABLE `test-timestamp`
(
    `id`    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `value` TIMESTAMP NULL DEFAULT NULL
);

5、标识符

为标识列选择正确且合适的数据类型非常重要。一般来说标识列通常用来作为关联查询，作为唯一标识以及当做外键使用。所以选择标识列应当和关联的字段列类型一致,一旦类型确认下来，关联的字段类型也需要完全匹配，甚至 UNSIGNED 这样的修饰符，混用不同的数据类型可能导致性能问题，有时候在比较的时候自动转换可能也会造成意外的问题。同时在选择标识符的类型的时候，不仅仅需要考虑标识符的类型也需要考虑 MySQL 中怎么对此类型计算和比较。

整数类型作为标识符

整数类型通常是标识符的最好的选择，一般选择 BIGINT UNSIGNED作为标识，并且选择自增主键。

CREATE TABLE `PK`
(
    `id`    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
);

枚举类型或 Set 集合类型作为标识符

通常来说，使用枚举类和Set作为标识符通常不是一个非常好的选择，尽管对于只有几行记录的定义表而言。枚举类或者Set 适合定义有序的状态或者有限的集合等等场景，而得标识符

字符串类型作为标识

在尽可能的情况下，尽量避免使用字符串作为标识符类型。因为其很消耗空间，大部分情况下逗比整数类型慢，尤其是在MyISAM 存储类型的数据库中，MyISAM 下默认对字符串做压缩处理，这导致查询慢得多，在基准测试中，性能有6倍之差。

对于一些特殊的随机字符串也需要注意，例如 MD5() ,UUID() 等等这类函数产生的字符串通常会任意分布在很大的空间上，可能会导致INSERT 或者 SELECT 变慢

插入值会插入到索引的不同位置，所以使得INSERT 操作变慢，导致页分类，磁盘随机读取
导致SELECT 操作变慢，逻辑上相邻的数据却分不到不同的位置
导致缓存效果不好，因为随机分布在不同的位置，导致缓存依赖的局部访问失效，数据分散，缺少热区数据。

虽然 UUID 虽然不均匀，其整体还是有一定的顺序的，但是仍然不如整数访问快。

6、特殊类型

某些类型数据并不能直接与 MySQL 内置的数据类型对应，使用微秒级别的时间就是一个例子，此时不能在使用 DATETIME 作为存储类型。另外一个特别的例子就是 IP 地址，IP地址表面上看是字符串，通常使用VARCHAR(15)来存储 IP 地址，但实际上 IP 地址是一个32位的无符号数，不是字符串，使用 . 分割只是为了明显的区分易读，所以应该使用32位的无符号数存储 IP 地址。MySQL 也提供了相关的函数用户转换比如 INET_ATON 和 INET_NTOA

mysql root@localhost:(none)> SELECT INET_ATON('192.168.1.1') AS VALUE;
+------------+
| VALUE      |
+------------+
| 3232235777 |
+------------+
1 row in set

----

mysql root@localhost:(none)> SELECT INET_NTOA(3235235779) AS IP;
+-----------------+
| IP              |
+-----------------+
| 192.213.199.195 |
+-----------------+
1 row in set