MySQL数据类型（高性能MySQL读书笔记）

最新推荐文章于 2022-04-25 17:23:47 发布

笑吥语

最新推荐文章于 2022-04-25 17:23:47 发布

阅读量100

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/qq_40623110/article/details/106956158

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

选择优化的数据类型

字段的选择

选择简单的数据类型，但确保没有低估需要存储的值的范围。

减少占用的磁盘、内存和CPU缓存，并且在处理的时候需要的CPU周期也更少。

尽量避免null。

null的列使得索引、索引统计和值比较都更为复杂。可为null的列会占用更多的存储空间，在MySQL中也需要特殊处理。

例如：MyISAM当可为null的列被索引时，每个索引记录需要一个额外的字节（InnoDB存储null使用单独的位）。

整数类型

数字有两种类型：整数和实数。

整数类型：可以使用UNSIGNED属性，表示不允许负值。

TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT （8、16、24、32、64）

实数类型

实数类型是带有小数部分的数字。可以使用DECIMAL存储比BIGINT还大的整数。

DECIMAL也可以存储精确的小数，FLOAT和DOUBLE支持使用标准的浮点运算进行近似计算（CPU不支持DECIMAL的直接计算，自持原生浮点运算，所以浮点运算更快）。

MySQL5.0+将DECIMAL打包成一个二进制字符串中（每4个字节表示9个数字）。

字符串类型

VARCHAR（4.1+不会删除末尾的空格）

该类型存储可变长的字符串，比定长类型更节省空间。但是因为是变长的，在update的时候因为变长做额外的工作。例：

如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，不同的存储引擎的处理方式是不一样的。例如，MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。

需要使用1或2个额外的字节记录字符串的长度（如果长度小于等于255 使用1个字节，大于255使用两个字节）。

适用场景：

字符串列的最大长度比平均长度大很多;

列的更新很少，所以碎片不是问题;

使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

CHAR（会删除末尾的空格）

定长，不容易产生碎片。使用空格填充。

BLOB（二进制，没有排序和字符集）和 TEXT（字符）

实际上，它们分别属于两组不同的数据类型家族:字符类型是TINYTEXT, SMALLTEXT,TEXT，MEDIUMTEXT，LONGTEXT ;对应的二进制类型是TINYBLOB，SMALLBLOB, BLOB,MEDIUMBLOB，LONGBLOB。 BLOB 是SMALLBLOB的同义词，TEXT 是SMALLTEXT的同义词。

如果值太大的时候InnoDB会将其存储在外部存储区域进行存储，每个值在行内需要1~4个字节存储指针。

只使用最前max_sort_length字节进行排序，而不是整个字符串，无法利用索引消除排序。

如果EXPLAIN执行计划的Extra列包含”Using temporary”，则说明这个查询使用了隐式临时表（使用MyISAM磁盘临时表）。

使用枚举（ENUM）代替字符串类型

# 'fish'：1 'apple'：2 'dog'：3
CREATE TABLE enum_test(
e enum('fish','apple','dog') not NULL);
# 两种insert语句，这种比较好
INSERT INTO enum_test(e) values ('fish'),('dog'),('apple');

这三行数据实际存储为整数，而不是字符串，可以通过数字上下文环境检索看到这个双重属性。

缺点：

枚举字段是按照内部存储的整数而不是自定义的字符串进行排序的。如果想改变这种排序，可以在定义枚举列的时候按照需要的顺序，或者使用select e from enum_test order by field(e,'apple','dog','fish')指定排序顺序，但是无法利用索引消除排序。
在添加或删除字符串必须使用alter table，可以在列表末尾添加元素。

由于MySQL把每个枚举值保存为整数，并且必须进行查找才能转换为字符串，所以枚举列有一些开销。通常枚举的列表都比较小，所以开销还可以控制，但也不能保证一直如此。在特定情况下，把CHAR/VARCHAR列与枚举列进行关联可能会比直接关联CHAR/VARCHAR列更慢。

日期和时间类型

DATETIME

能够保存大的范围的值，从1001~9999年，精度为秒。封装成的格式是YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节的存储空间。

TIMESTAMP

TIMETAMP 类型保存了从1970年1月1日午夜(格林尼治标准时间)以来的秒数,它和UNIX时间戳相同。TIMESTAMP 只使用4个字节的存储空间，因此它的范围比DATETIME小得多:只能表示从1970年到2038年。MySQL提供了FROM_ UNIXTIME() 函数把Unix时间戳转换为日期，并提供了UNIX_ TIMESTAMP() 函数把日期转换为Unix时间戳。

比较

TIMESTAMP（默认为not null）提供的值与时区有关，后者则保留文本表示的日期和时间。

位数据类型

这几种位存储数据内部使用整数存储ENUM和SET类型，在作比较的时候转换成字符串。

BIT

可以使用BIT列在一列中存储一个或多个true/false值。BIT(1) 定义一个包含单个位的字段，BIT(2) 存储2个位，依此类推。BIT 列的最大长度是64个位。

缺点：

MySQL把BIT当做字符串类型，而不是数字类型。在检索是，结果是一个包含二进制0，1的字符串，而不是ASCII码的“0”或“1”。

如果想在一个bit的存储空间中存储一个true/false值。另一个方法是创建一个可以为空的CHAR(0)列。该列可以保存空值(NULL) 或者长度为零的字符串(空字符串)。

SET

如果需要保存很多true/false值，可以考虑合并这些列到一个SET数据类型，它在MySQL内部是以一系列打包的位的集合来表示的。这样就有效地利用了存储空间，并且MySQL有像FIND_ IN_ SET() 和FIELD()这样的函数，方便地在查询中使用。

缺点：

改变列的定义的代价高，需要alter table。（ENUM可以在末尾添加）

例子：保存访问权限的控制表

create table acl(prems set('CAN_READ','CAN_WRITE','CAN_DELETE') not null);

insert into act(perms) values('CAN_READ','CAN_DELETE');

选择标识符

标识列：一般来说更有可能用标识列与其他值进行比较(例如，在关联操作中)，或者通过标识列寻找其他列。标识列也可能在另外的表中作为外键使用，所以为标识列选择数据类型时，应该选择跟关联表中的对应列一样的类型。

选择标识列的类型时，不仅仅需要考虑存储类型，还需考虑MySQl对这种类型怎么执行计算和比较。（ENUM和SET在作比较的时候转换为字符串）

缓存表和汇总表

“缓存表”：存储那些可以比较简单地从schema其他表获取（但是每次获取的速度比较慢）数据的表（例：逻辑的冗余数据）

”汇总表“：保存的是使用GROUP BY语句聚合数据的表（例：不是逻辑上冗余的）。

加快 ALTER TABLE 操作的速度

新的结构创建一个空的表，从旧表中查出所有数据插入新表，然后删除旧表。

先在一台不提供服务的机器上执行ALTER TABLE操作，然后提供服务的主库进行切换。
”影子拷贝“：用要求额表的结构创建一张和源表无关的信标，然后通过重命名和删表操作交换两张表。

ALTER TABLE 允许使用ALTER COLUMN（修改.frm文件）、MODIFY COLUMN （表重建）和CHANGE COLUMN 语句修改列。这三种操作都是不一样的。

只修改.frm文件

创建一张有相同结构的空表，并进行所需要的修改(例如增加ENUM常量)。
执行FLUSH TABLES WITH READ LOCK。这将会关闭所有正在使用的表，并且禁止任何表被打开。
交换.frm文件.
执行UNL .0CK TABL .ES来释放第2步的读锁。

# 创建一个相同表结构的新表
create table film_new like film;
#修改表结构
alter table film_new modify column rating enum('G','PG','PG-13','R' ,'NC-17', 'PG-14') default 'G'；
#关闭所有正在使用的表，并禁止任何表打开
FLUSH TABLES WITH READ LOCK
#更改名字之后，解锁
UNLOCK TABLES

快速创建MyISAM索引

高效地将载入数据到MyISAM表中：先禁用索引、载入数据，再重新启用索引（对唯一索引无效，MyISAM会在内存中构造唯一索引，并且为载入的每一行检查唯一性。当索引的大小超过有效内存大小，载入操作就会越来越慢。在现代版本的InnoDB版本中，有一个类似的技巧，这依赖于InnoDB的快速在线索创建功能。这个技巧是，先删除所有的非唯一索引，然后增加新的列，最后重新创建除掉的索引）。

# 禁用索引
alter table tablename disable keys
#启用索引
alter table tablename enable keys

操作步骤：

用需要的表结构创建一张表，但是不包括索引。
载入数据到表中以构建.MYD文件。
按照需要的结构创建另外一张空表，这次要包含索引。这会创建需要的frm和.MYI 文件。
获取读锁并刷新表。
重命名第二张表的frm和.MYI文件，让MySQL认为是第一张表的文件。
释放读锁。
使用REPAIRTABLE来重建表的索引。该操作会通过排序来构建所有索引，包括唯一索引。

笑吥语

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据类型（高性能MySQL读书笔记）

选择优化的数据类型字段的选择选择简单的数据类型，但确保没有低估需要存储的值的范围。减少占用的磁盘、内存和CPU缓存，并且在处理的时候需要的CPU周期也更少。尽量避免null。 null的列使得索引、索引统计和值比较都更为复杂。可为null的列会占用更多的存储空间，在MySQL中也需要特殊处理。例如：MyISAM当可为null的列被索引时，每个索引记录需要一个额外的字节（InnoDB存储null使用单独的位）。整数类型数字有两种类型：整数和实数。整数类型：可以使用
复制链接

扫一扫