MySQL - 存储引擎与数据类型

MySQL 存储引擎与数据类型

MySQL 存储引擎概述
常用存储引擎对比
如何选择存储引擎
选择数据类型的基本原则
char 与 varchar
text 和 blob
浮点数与定点数
字符集

MySQL 存储引擎概述

MySQL 支持多种存储引擎，在处理不同类型的应用时，可以通过选择使用不同的存储引擎提高应用的效率，或者提供灵活的存储。MySQL 的存储引擎包括：MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED 等，其中 InnoDB 和 BDB 提供事务安全表，其他存储引擎都是非事务安全表。

常用存储引擎对比

特点	MyISAM	BDB	Memory	InnoDB	Archive
存储限制	没有	没有	有	64TB	没有
事务安全		支持		支持
锁机制	表锁	页锁	表锁	行锁	行锁
B树索引	支持	支持	支持	支持
哈希索引			支持		支持
全文索引	支持
数据缓存			支持	支持
索引缓存	支持	支持	支持	支持
数据可压缩	支持				支持
空间使用	低	低	N/A	高	非常低
内存使用	低	低	中等	高	低
批量插入速度	高	高	高	低	非常高
支持外键				支持

如何选择存储引擎

选择标准：根据应用特点选择合适的存储引擎，对于复杂的应用系统可以根据实际情况选择多种存储引擎进行组合。

MyISAM：默认的 MySQL 存储引擎（5.5 以前），在 Web、数据仓储和其他应用环境下最常使用。如果应用以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务完整性没有要求，则适合使用 MyISAM。每个 MyISAM 表在磁盘上存储成三个文件：.frm（存储表定义）、.MYD（存储数据）、.MYI（存储索引）。
InnoDB：用于事务处理应用程序，具有众多特性包括 ACID 事务支持。支持行锁、外键、崩溃恢复能力。如果应用需要事务支持、外键约束，或者更新操作频繁，适合使用 InnoDB。InnoDB 写的处理效率比 MyISAM 差一些，并且会占用更多磁盘空间。
Memory：将所有数据保存在 RAM 中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。适合存储临时表和需要快速访问的非关键数据。
Merge：允许将一系列等同的 MyISAM 表以逻辑方式组合在一起，作为一个对象引用。对于诸如数据仓储等 VLDB 环境十分适合。

查看当前表的存储引擎：

SHOW TABLE STATUS FROM crashcourse LIKE 'customers';

创建表时指定存储引擎：

CREATE TABLE mytable (...) ENGINE=InnoDB;

选择数据类型的基本原则

前提：使用适合的存储引擎。根据选定的存储引擎，确定如何选择合适的数据类型。

MyISAM：最好使用固定长度的数据列代替可变长度的数据列
MEMORY：无论使用 CHAR 或 VARCHAR 都没有关系，两者都作为 CHAR 类型处理
InnoDB：建议使用 VARCHAR 类型。InnoDB 内部的行存储格式没有区分固定长度和可变长度列，使用 VARCHAR 来最小化数据行的存储总量和磁盘 I/O 是比较好的

char 与 varchar

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同，最大长度和是否尾部空格被保留等方面也不同。

值	CHAR(4) 存储需求	VARCHAR(4) 存储需求
`''`	4 个字节	1 个字节
`'ab'`	4 个字节	3 个字节
`'abcd'`	4 个字节	5 个字节
`'abcdefgh'`	4 个字节	5 个字节

CHAR 是定长的，不足长度会用空格填充；VARCHAR 是变长的，需要 1 个额外字节记录长度。检索时 CHAR 列会删除尾部空格，VARCHAR 不会。

CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
INSERT INTO vc VALUES ('ab ', 'ab ');
SELECT CONCAT(v, '+'), CONCAT(c, '+') FROM vc;
-- 结果：'ab +' 和 'ab+'（CHAR 删除了尾部空格）

text 和 blob

在使用 text 和 blob 字段类型时要注意以下几点：

碎片问题：BLOB 和 TEXT 值执行大量删除或更新操作时会在数据表中留下很大的「空洞」，建议定期使用 OPTIMIZE TABLE 进行碎片整理。
合成索引：可以使用 MD5()、SHA1() 或 CRC32() 生成散列值存储在单独的列中，通过检索散列值找到数据行。注意这种技术只能用于精确匹配查询。
避免无谓检索：不要在不必要的时候检索大型的 BLOB 或 TEXT 值，避免 SELECT *。
分离到单独的表：把 BLOB 或 TEXT 列分离到单独的表中，可以减少主表中的碎片，使主表获得固定长度数据行的性能优势。

浮点数与定点数

CREATE TABLE test (c1 float(10,2), c2 decimal(10,2));
INSERT INTO test VALUES(131072.32, 131072.32);
SELECT * FROM test;
-- c1: 131072.31（浮点数误差！）
-- c2: 131072.32（定点数精确）

在 MySQL 中 float、double（或 real）是浮点数，decimal（或 numeric）是定点数。

浮点数优点：在长度一定的情况下能够表示更大的数据范围
浮点数缺点：会引起精度问题

使用原则：

浮点数存在误差问题
对货币等对精度敏感的数据，应该用定点数（decimal）表示或存储
编程中要特别注意浮点数的误差问题，尽量避免做浮点数比较
要注意浮点数中一些特殊值的处理

字符集

字符集是一套符号和编码的规则。MySQL 的字符集包括字符集（CHARACTER）和校对规则（COLLATION）两个概念：字符集定义存储字符串的方式，校对规则定义比较字符串的方式。字符集和校对规则是一对多的关系，MySQL 支持 30 多种字符集的 70 多种校对规则。

查看支持的字符集：

SHOW CHARACTER SET;
SHOW COLLATION LIKE 'utf8%';

Unicode 简述：Unicode 是由国际组织设计、可以容纳全世界所有语言文字的编码方案。有两套标准 UCS-2（2 字节）和 UCS-4（4 字节）。UTF-8 是 Unicode 的一种变长编码实现。

怎样选择合适的字符集：在能够完全满足应用的前提下，尽量使用小的字符集。常用保存汉字的字符集有 utf8、gb2312、gbk。gb2312 字库比 gbk 小，有些偏僻字不能保存，如果不能确定偏僻字出现的几率，最好选用 gbk 或 utf8。

字符集的设置：MySQL 的字符集和校对规则有 4 个级别的默认设置——服务器级、数据库级、表级和字段级。

-- 服务器级（在 my.cnf 中设置）
-- [mysqld]
-- default-character-set=utf8

-- 或启动时指定
-- mysqld --default-character-set=utf8

-- 查看当前服务器字符集
SHOW VARIABLES LIKE 'character_set_server';

-- 创建数据库时指定
CREATE DATABASE mydb DEFAULT CHARACTER SET utf8;

-- 创建表时指定
CREATE TABLE mytable (...) DEFAULT CHARSET=utf8;

如果没有特别指定，默认使用 latin1 作为服务器字符集。在应用开始阶段就按照需求正确选择合适的字符集，避免后期更换字符集的高代价操作。

MySQL - 存储引擎与数据类型