MySQL 开发篇，存储引擎的选择

发表于 更新于 分类于 阅读次数： 10 本文字数： 11k 阅读时长 ≈ 39 分钟

前言

谁说 MySQL 查询千万级别的数据很拉跨？我今天就要好好的和你拉拉家常，畅谈到深夜，一起过除夕！这篇文章也是年前的最后一篇，希望能带给大家些许收获，不知不觉查找文档和参考实体书籍就写了这么多，自己都感觉到意外。不禁感慨到，知道的越多，才知道不知道的更多。

开发人员或者是 DBA 都应该关注 MySQL 使用的存储引擎，选择合适存储引擎对你的应用性能提升是明显的。在阅读到本文的时候，肯定是有一定的 MySQL 或者其它数据库基础的，不然有些地方看着会很费劲。重点地方，我都进行了加粗处理，这样更容易获取关键知识点。

关于存储引擎，一篇文章也不可能面面俱到，对个人觉得比较重要、于工作有益的方面进行阐述。如果真的去深挖，估计得一本书的篇幅。顺带还介绍一些数据类型选择、字符集设置、索引的使用；视图、存储过程、函数以及触发器啊等等会在下一篇博文进行详细的描述。但本文不会做太详细的叙述。本篇文章以存储引擎的选择为核心，如果有出现瑕疵的地方，希望您能留下宝贵的建议。

正文

一、存储引擎的选择（表类型）

1、存储引擎的介绍

与到多数关系型数据库的区别在于 MySQL 有一个存储引擎的概念，针对不同的存储需求可以选择最合适的存储引擎。MySQL 中的插件式的存储引擎是其一大特色，用户可以根据应用的需求选择如何存储、是否索引，是否使用事务。嘿嘿，你也可以根据业务环境去适配最适合自己业务的存储引擎。

Oracle 从中嗅到了商机，收购了 MySQL，从此有了企业版（商业支持）。社区版依旧可以免费下载。另一大魅力也是因为开源，社区高度活跃，人人都可贡献。接下来介绍几种使用比较多的存储引擎，存储引擎并无优劣之分，有的只是谁更适合对应的生产业务环境。

MySQL5.0 中支持的存储引擎有 FEDERATED、MRG_MYISAM、MyISAM、BLACKHOLE、CSV、MEMORY、ARCHIVE、NDB Cluster、BDB、EXAMPLE、InnoDB（MySQL5.5 以及 MariaDB10.2 之后的默认存储引擎）、PERFORMANCE_SCHEMA（非常规存储数据引擎）。下面给出 MySQL 与 MariaDB 支持的存储器引擎的对比，可以看出 MariaDB 新增了 Aria 引擎：

查看存储引擎

通过 MySQL 登录自带的字符界面输入 show engines\G; 或者使用支持 MySQL 查询的工具 SQLyog、phpMyAdmin、MySQL workbench 等查询支持的引擎，这里只展示部分哟：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
[test@cnwangk ~]$ mysql -uroot -p
Enter password: 
mysql> show engines\G;
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
9 rows in set (0.00 sec)

作用描述：

  • Engine：引擎名称（描述）；
  • Support：当前版本数据库是否支持该存储引擎，YES：支持、NO：不支持；Supports transactions, row-level locking, and foreign keys，个人字面上翻译这段话：支持事务、行级别锁和外键；
  • Comment：对该存储引擎的详情描述，比如描述该引擎否支持事务和外键；
  • Transactions：对该存储引擎是否支持事务的描述，YES：支持、NO：不支持；
  • XA：是否满足 XA 规范。XA 规范是开放群组关于分布式事务处理 (DTP) 的规范。YES：支持、NO：不支持；
  • Savepoints：字面意思是保存点，对事物控制是否支持，YES：支持、NO：不支持。

小声哔哔，如果你能阅读明白官方的一些英文文档，这将有助于你对 MySQL 存储引擎的进一步理解，养成阅读源码或者文档的能力。

顺带的提一下 MySQL 的妹妹 MariaDB。在 MySQL 的复刻版本 MariaDB 中 10.2 之前使用的自带的新引擎 Aria，在 MariaDB10.2 之后使用的默认存储引擎也是 InnoDB，足以看出 InnoDB 存储引擎的优秀之处。MariaDB 的 API 和协议兼容 MySQL，另外又添加了一些功能，以支持本地的非阻塞操作和进度报告。这意味着，所有使用 MySQL 的连接器、程序库和应用程序也将可以在 MariaDB 下工作。在此基础上，由于担心甲骨文 MySQL 的一个更加封闭的软件项目，Fedora 等 Linux 发行版已经在最新版本中以 MariaDB 取代 MySQL，维基媒体基金会的服务器同样也使用 MariaDB 取代了 MySQL。

主要需要了解的几种存储引擎：

  • MyISAM
  • InnoDB
  • MEMORY
  • MERGE

下面将着重介绍我最近看书认识的几种常用的存储引擎，对比各个存储引擎之间的区别，帮助我们理解不同存储引擎的使用方式。更多详情可以参考 MySQL 的官方文档。

2、部分存储引擎的特性

存储引擎 / 支持特性 存储限制 事务安全 锁机制 B 树索引 哈希索引 全文索引 集群索引 数据缓存 索引缓存 数据可压缩 空间使用 内存使用 批量插入速度 外键支持
MyISAM 有 表锁 支持 支持 支持 支持 低 低 高
InnoDB 64TB 支持 行锁 支持 支持（5.6） 支持 支持 支持 高 高 低 支持
MEMORY 有 表锁 支持 支持 支持 支持 N/A 中等 高
MERGE 没有 表锁 支持 支持 低 低 高
NDB 有 行锁 支持 支持 支持 低 高 高

InnoDB 存储引擎在 MySQL5.6 版本开始支持全文索引。在 MySQL5.7 推出了虚拟列，MySQL8.0 新特性加入了函数索引支持。

2.1、MyISAM 存储引擎

MyISAM 是 MySQL5.5 之前默认的存储引擎。MyISAM 不支持事务、不支持外键。优势在于访问速度快，对事务完整性没有特殊要求或者以 select 和 insert 为主的应用基本上可以使用 MyISAM 作为存储引擎创建表。我们先弄个例子出来演示，事先准备了一张数据千万级别的表，看看这个存储引擎的特性：

我已经创建好了数据库为 test，在 test 中分别创建了两张表 test 和 tolove。test 表在创建的时候指定默认存储引擎为 MyISAM，tolove 表指定存储引擎为 InnoDB。
使用 MyISAM 存储引擎创建的表 tolove，查询存储有 1kw 数据的表 tolove。

tips：你可以使用 use test，切换到 test 数据库，就不用像我这样查询 tolove 表去指定 test 数据库了哟！

1
2
3
4
5
6
7
MySQL [(none)]> select count(*) from test.tolove;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.000 sec)

再看演示使用 InnoDB 存储引擎创建的表 test，同样为了演示，事先随机生成了 1kw 条数据。

1
2
3
4
5
6
7
MySQL [(none)]> select count(*) from test.test;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (3.080 sec)

进行对比同样存储 1kw 条数据的表，使用 MyISAM 作为存储引擎查询速度堪称光速 1 row in set (0.000 sec)，使用 InnoDB 存储引擎查询速度稍逊一筹 1 row in set (3.080 sec)。

MyISAM 在磁盘中存储的文件：

每个 MyISAM 在磁盘上存储成 3 个文件，其文件名和表名都相同，扩展名分别是：

  • .frm：存储表定义；
  • .MYD：MYData，存储数据；
  • .MYI：MYindex，存储索引。

数据文件和索引文件可以存放在不同的目录，平均分布 IO，获得更快的速度，提升性能。需要指定索引文件和数据文件存储的路径，创建表时通过 DATA DIRECTORY 和 INDEX DIRECTORY 参数指定，表明不同 MyISAM 表的索引文件和数据文件可以存放在不同的路径下。当然，需要给予该路径的访问权限。

MyISAM 损坏处理方式 ：

MyISAM 类型的表可能会损坏，原因多种多样。损坏后的表有可能不能被访问，会提示需要修复或者访问后提示返回错误结果。MyISAM 类型的表，可以通过提供的修复工具执行 CHECK TABLE 语句检查 MyISAM 表的健康程度，使用 REPAIR TABLE 语句修复一个损坏的表。表损坏可能会导致数据库异常重新启动，需要尽快修复并确定原因好做应对策略。

使用 MyISAM 存储引擎的表支持 3 种不同的存储格式，如下：

  • 静态表，固定长度；
  • 动态表
  • 压缩表

静态表是 MyISAM 存储引擎的默认存储格式，字段长度是定长，记录都是固定长度。优势在于存储迅速、容易缓存、出现故障易恢复；缺点是相对耗存储空间。需要注意的是：如需保存内容后面的空格，默认返回结果会去掉后面的空格。

动态表包含变长字段，记录不是固定长度，存储优势：占用空间相对较小、但频繁删除和更新记录会产生碎片。这时，需要定期执行 optimize table 语句或者 myisamchk -r 命令来改善性能，出现故障恢复相对较难。

压缩表由 mysiampack 工具创建，占用磁盘空间很小。因为每个记录是被单独压缩，所以访问开始非常小。

顺带安利一波，前段时间发现 WPS 也能够制作精美的思维导图，并且支持一键导入到 doc 文件中。普通用户最多可存储 150 个文件。之前也用过 XMind、processon、gitmind 等等，现在使用 WPS 更方便了。

2.2、InnoDB 存储引擎

优点与缺点：InnoDB 存储引擎提供了具有提交（commit）、回滚（rollback）和崩溃恢复能力的事务安全。但对比 MyISAM 存储引擎，InnoDB 写的处理效率相对差一些，并且会占用更多的磁盘空间保留数据和索引。

2.2.1、自动增长列

InnoDB 表的自动增长列可以手工插入，但插入的值为空或者 0，则实际插入的将是自动自动增长后的值。

本来想继续使用 bols 那张表作为演示的，思来想去还是正经一点。为了演示，我又新增了一张表为 autoincre_test，表示 id 设置为主键且自增长，存储引擎选择 InnoDB。然后插入了 3 条数据进行演示。查询当前线程最后插入数据的记录使用值：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
MySQL [test]> create table autoincre_test(id int not null auto_increment,name varchar(16),primary key(id))engine=innodb;
Query OK, 0 rows affected (0.018 sec)

MySQL [test]> insert into autoincre_test values(1,'1'),(0,'2'),(null,'3');
Query OK, 3 rows affected (0.007 sec)
Records: 3  Duplicates: 0  Warnings: 0

MySQL [test]> select * from autoincre_test;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
+----+------+
3 rows in set (0.000 sec)

select last_insert_id();
MySQL [test]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.000 sec)

tips：可以通过 alter table table_name=n; 语句强制设置自动增长列的初始值，默认从 1 开始，但该强制的默认值是保留在内存中的，如果使用该值之前数据库重新启动，强制默认值则会丢失，就需要重新设置，毕竟使用内存没有加载到磁盘中。

通过上面的演示，你会发现插入记录是 0 或者空时，实际插入的将是自动增长后的值。通过 last_insert_id() 函数可以查询当前线程最后插入数据的记录使用值。如果一次插入多条记录，则返回的是第一条记录使用的自动增长值，这里就不演示插入多条数据了。记住一点，可以使用 last_insert_id() 去查询 id 记录值。

对于 InnoDB 表，自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列。但对于 MyISAM 表，自动增长列可以是组合索引的其它列。这样插入记录后，自动增长列是按照组合索引的前面几列排序后递增的。你可以创建一张表指定 MyISAM 存储引擎，然后将两列字段组合索引进行测试验证。

2.2.2、外键约束

在 MySQL 中，目前支持外键约束的存储引擎只有 InnoDB。创建外键的时候，要求父表必须有对应的索引。子表创建外键的时候，也会自动创建对应的索引。下面将通过实例进行讲解。可以从 MySQL 官网下载示例数据库 world 和 sakila 进行参考。

  • city 表，FOREIGN KEY (CountryCode) REFERENCES country (Code)
  • country 表
  • countrylanguage 表，FOREIGN KEY (CountryCode) REFERENCES country (Code)

通过 MySQL workbench 或者 Navicat 逆向生成物理模型进行参考，更加直观。插一句，在 MySQL 的官网同样有一个 sakila 数据库是关于演员电影的，也提供了 sakila 的 ERR 物理模型图，这句话做了超链接，可以直接访问。

在创建索引时，可以指定在删除、更新父表时，对子表进行的相应操作包含：

  • restrict
  • cascade
  • set null 和 no action

其中 restrict 和 no action 相同，restrict 限制在子表有关联记录的情况下父表无法更新；cascade 表示在父表更新或删除的时候，级联更新或者删除子表对应记录；set null 表示在父表更新或删除的时候，子表的对应字段被 set null。选择 cascade 以及 set null 时需要谨慎操作，有可能导致数据丢失。

在导入多个表的数据时，如果忽略表之前的导入顺序，可以暂时关闭外键检查；同样执行 load data 和 alter table 时也可以暂时关闭外键检查加快处理的速度，提升效率。关闭外键检查的命令为：

1
set foreign_key_checks=0;

执行完导入数据或者修改表的操作后，通过开启外键检查命令改回来：

1
set foreign_key_checks=1;

对于 InnoDB 类型的表，外键信息可以通过 show create table 或者 show table status 查看。比如查找 world 数据库中的 city 表：

1
MySQL [sakila]> show table status like 'city'\G

关于外键约束就提这么多，没有演示创建以及删除，因为贴太多的 SQL 语句太占篇幅了。可以到 MySQL 官网下载 world 和 sakila 数据库进行测试。

2.2.3、存储方式

InnoDB 存储表和索引有两种方式：

  • 共享表空间存储
  • 多表空间存储

使用共享表空间存储，这种方式创建的表的表结构保存在.frm 文件中，数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中，可以是多个文件。在开头介绍 InnoDB 存储引擎时也提到过文件存储位置。

使用多表空间存储，这种方式创建的表的表结构仍然保存在.frm 文件中，但每个表的数据和索引单独保存在.ibd 文件中。如果是个分区表，则每个分区对应单独的.ibd 文件，文件名为表名 + 分区名。可以在创建分区的时候指定每个分区的数据文件位置，以此来平均分布磁盘的 IO，达到缓解磁盘压力的目的。如下是在 Windows 下使用 InnoDB 存储了海量数据的文件：

使用多表空间存储需要设置参数 innodb_file_per_table，重启数据库服务器才能生效哟。多表空间的参数生效后，只对新建的表生效。多表空间的数据文件无大小限制，无需设置初始大小，也不需设置文件的最大限制与扩展大小等参数。使用多表空间存储优势在于方便单表备份和恢复操作。虽然不能直接复制.frm 和.ibd 文件达到目的，但可以使用如下命令操作：

1
2
alter table table_name discard tablespace;
alter table table_name import tablespace;

将备份恢复到数据库中，单表备份，只能恢复到原来所在的数据库中，无法恢复到其它数据库中。如过需要将单表恢复至其它目标数据库中，则需要通过 mysqldump 和 mysqlimport 来实现。

注意：即便多表存储更有优势，但是共享表存储空间依旧是必须的，InnoDB 将内部数据字典和在线重做日志存在这个文件中。

关于 InnoDB 存储引擎就介绍到此处了，更多详情可以参考 MySQL 的官方文档。是不是发现了我只在 MyISAM 和 InnoDB 存储引擎做了总结的思维导图。没错，只做了这两个，因为这俩最常用。至于为啥是粉色背景，因为老夫有一颗少女心！

2.3、MEMORY 存储引擎

MEMORY 存储引擎使用存在与内存中的内容来创建表。每个 MEMORY 表只对应一个磁盘文件，格式是.frm。MEMORY 类型的表访问速度极快，存在内存中当然快。这就是 Redis 为什么这么快？不仅小？还能持久？咱回到正题，MEMORY 存在内存中并默认使用 hash 索引，一旦服务关闭，表中数据会丢失。创建一张名为 GIRLS 的表指定存储引擎为 MEMORY，注意了在 UNIX 和 Linux 操作系统下，是对字段和表名大小是写敏感的，关键字不影响。

1
2
3
4
CREATE TABLE GIRLS (
  ID int NOT NULL,GIRE_NAME varchar(64) NOT NULL,GIRL_AGE varchar(10) NOT NULL,
  CUP_SIZE varchar(2) NOT NULL,PRIMARY KEY (ID)
) ENGINE=MEMORY DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

还记得在介绍存储引擎做的那会张表格吗，有介绍到 MEMORY 支持 B TREE 索引。虽然 MEMORY 默认使用的索引是 hash 索引，但是你可以手动指定索引类型。例如默认手动指定使用关键字 USING HASH：

1
2
3
4
5
6
7
8
9
10
-- 创建索引指定索引类型为hash。
create index mem_hash USING HASH on GIRLS(ID);
-- 查询索引类型，简化了一下，只展示了部分参数。
mysql> SHOW TABLE STATUS LIKE 'GIRLS'\G
*************************** 1. row ***************************
           Name: GIRLS
         Engine: MEMORY
        Version: 10
     Row_format: Fixed
1 row in set (0.00 sec)

虽然 MEMORY 容易丢失数据，但是在启动 MySQL 服务的时候，我们可以使用 **–init-file 选项，将 insert into … select 或者 load data infile** 这样的语句存放在这个指定的文件中，就可以在服务启动时从持久稳固的数据源装载表。

服务器需要提供足够的内存来维持所有在同一时间使用的 MEMORY 表，当不在需要 MEMORY 表内容之时，释放被 MEMORY 表使用的内存。仔细思考一下，如果内存用了不释放那将有多可怕。此时可以执行 delete form 或 truncate table 亦或完整地删除整个表，使用 drop table。这里提一点，在 Oracle 中也同样支持 truncate，使用 truncate 的好处在于不用再去考虑回滚（rollback），效率更高。使用 truncate 需要在命令模式下使用，其它客户端工具可能不支持。

每个 MEMORY 表中存放的数据量大小，受 max_heap_table_size 系统变量约束，初始值为 16MB，可以根据需求调整。通过 max_rows 可以指定表的最大行数。

MEMORY 存储引擎最大特色是快，主要用于内容变化不频繁的代码表，或者是为了做统计提供的中间表，效率更高。使用 MEMORY 时需谨慎，万一忘了这厮重启数据就没了就尴尬了。所以在使用时，考虑好重启服务器后如何取得数据。

关于 MEMORY 存储引擎就介绍到这里，大部分都是些理论知识，更多的需要自己去实践测试。

2.4、MERGE 存储引擎

MERGE 存储引擎是一组 MyISAM 表的组合，这些 MyISAM 表必须结果完全相同，MERGE 表本身没有数据，对 MERGE 类型的表可以进行查询、更新、删除操作，实际上是对内部的 MyISAM 表进行操作的。对于 MERGE 类型表的插入操作，通过 insert_method 子句定义插入的，可以有 3 个不同的值，使用 first 或 last 插入操作对应开始与最后一个表上。如果不定义这个子句，或者定义为 NO，表示不能对 MERGE 表进行操作。

对 MERGE 表进行 DROP 操作，只是对 MERGE 的定义进行删除，对内部表没有任何影响。MERGE 表上保留两个文件，文件名以表的名字开始，分别为：

  • .frm 文件存储表定义；
  • .mrg 文件包含组合表的信息，包含表组成、插入数据依据。

可以通过修改.mrg 文件来修改表，但修改后需要使用 flush tables 刷新。测试可以先创建两张存储引擎为 MyISAM 的表，再建一张存储引擎为 MERGE 存储引擎的表。如下所示创建 demo 为总表指定引擎为 MERGE，demo01 和 demo02 为分表：

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `merge_demo` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT,`NAME` VARCHAR(16) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`ID`)) ENGINE=MERGE UNION=(merge_demo01,merge_demo02)  
INSERT_METHOD=LAST DEFAULT CHARSET=utf8 COLLATE=utf8_bin 

CREATE TABLE `merge_demo01` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT,`NAME` VARCHAR(16) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`ID`)) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
  
CREATE TABLE `merge_demo02` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT,`NAME` VARCHAR(16) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`ID`)) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin

通过插入数据验证 MERGE 确实是一个 MyISAM 的组合，就是这么神奇。如下所示，只对 demo01 和 demo02 进行插入：

1
2
3
4
5
6
7
8
9
10
INSERT INTO study.`merge_demo01` VALUES(1,'demo01');
INSERT INTO study.`merge_demo02` VALUES(1,'demo02');
mysql [study]> select * from merge_demo;
+----+--------+
| ID | NAME   |
+----+--------+
|  1 | demo01 |
|  1 | demo02 |
+----+--------+
2 rows in set (0.000 sec)

插入完数据，分别查看 demo01 和 demo02 各只有一条数据，总表可以看到俩分表的全部数据。关键是指定了 insert_method=last。MERGE 表和分区表的区别，MERGE 并不能智能地将记录插入到对应表中，而分区表可以做到。通常我们使用 MERGE 表来透明的对多个表进行查询和更新操作。可以自己在下面测试总表插入数据，看分表的情况，我这里就不贴代码了。

关于 MySQL 自带的几款常用存储引擎就介绍到此，感兴趣的可以私下测试验证，更多参考请到官网获取 API 或者 DOC 文档。

除了 MySQL 自带的一些存储引擎之外，常见优秀的第三方存储引擎有 TokuDB，一款开源的高性能存储引擎，适用于 MySQL 和 MariaDB。更多详情可以去 TokuDB 官网了解哟。

2.5、修改表的存储引擎

创建新表时，如果不指定存储引擎，系统会使用默认存储引擎。在 MySQL5.5 之前默认的存储引擎为 MyISAM，在 MySQL5.5 之后默认的存储引擎为 InnoDB。如果想修改默认存储引擎，可以通过配置文件指定 default-table-type 的参数。关于存储引擎的查看，在上面介绍存储引擎的时候已经有说明了。

方法一：建表即指定当前表的存储引擎

在创建 tolove 表的时候就指定存储引擎，例如指定存储引擎为 MyISAM，默认编码为 utf8：

1
2
3
4
5
6
-- Create Table
CREATE TABLE `tolove` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,`GIRL_NAME` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `GIRL_AGE` varchar(64) COLLATE utf8_bin DEFAULT NULL,`CUP_SIZE` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

测试生成的数据量比较大，随机生成了 1 千万条数据。查询（select）业务相对较多，在建表的时候就指定默认存储引擎 MyISAM，统计（count）的效率很高。以我的渣渣电脑，使用 INNODB 存储引擎，统计一次需要 2~3 秒左右。在上面讲到 MYISAM 的时候，已经将查询时间进行过对比。

方法二：使用 alter table 修改当前表的存储引擎

修改创建的 tolove 表为 MYISAM 引擎进行测试。

1
2
-- 修改创建的tolove表为MYISAM引擎进行测试
ALTER TABLE test.`tolove` ENGINE=MYISAM;

修改 test 表的存储引擎为 INNODB 进行测试。

1
2
-- 修改表的存储引擎为INNODB进行测试
ALTER TABLE test.`test` ENGINE=INNODB;

SHOW CREATE TABLE 查询表的存储引擎，分别查询 test 表和 tolove 表，在讲存储引擎为 MyISAM 的时候，有演示过哟！

1
2
SHOW CREATE TABLE test.`test`;
SHOW CREATE TABLE test.`tolove`;

如果在工具中无法看全，可以导出成 xml、csv、html 等查询，以下是我查询出自己创建表时设置的存储引擎为 InnoDB：

1
2
3
4
-- 显示出我创建的test表的SQL语句存储引擎为InnoDB
CREATE TABLE `test` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `STU_NAME` varchar(50) NOT NULL, `SCORE` int(11) NOT NULL, `CREATETIME` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8
-- 显示出我创建的tolove表的SQL语句，存储引擎为MyISAM
CREATE TABLE `tolove` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `GIRL_NAME` varchar(64) COLLATE utf8_bin DEFAULT NULL, `GIRL_AGE` varchar(64) COLLATE utf8_bin DEFAULT NULL, `CUP_SIZE` varchar(10) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

存储引擎的修改就介绍这么多，看到我的自增长列（AUTO_INCREMENT）ID 到了 20000001，之前随机生成过一次 1kw 条数据哟！有一部分解释说明我写在了代码块中，看起来更加舒服。

3、存储引擎的选择

在选择合适的存储引擎时，应根据应用特点选择合适的存储引擎。对于复杂的应用系统，你可以选择多种存储引擎满足不同的应用场景需求。如何选择合适的存储引擎呢？存储引擎的选择真的很重要吗？

确实应该好好思考，在并不复杂的应用场景下，可能 MyISAM 存储引擎就能满足日常开销。或许在另外一种场景之下 InnoDB 才是最佳选择，综合性能更好，满足更多需求。

MyISAM 是 MySQL 的默认的插件式存储引擎，是 MySQL 在 5.5 之前的默认存储引擎。如果应用以读和插入操作居多，只有很少的更新和删除操作，对事务完整性、并发性没有很高的需求，此时首选是 MyISAM 存储引擎。在 web 和数据仓库最常用的存储引擎之一。

InnoDB 用于事务处理应用程序，并且支持外键。是 MySQL 在 5.5 之后的默认存储引擎，同样也是 MariaDB 在 10.2 之后的默认存储引擎，足见 InnoDB 的优秀之处。如果应用对事务完整性有较高的要求，在并发情况下要求数据高度一致性。数据除了插入和查询以外，还包括很多的更新和删除操作，那么 InnoDB 应该是比较合适的存储引擎选择。InnoDB 除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（commit）、回滚（rollback）。对类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 也是合适的选择。插点题外话，本人在工作中使用 Oracle 数据库也有一段时间，Oracle 的事务确实很强大，处理大数据压力很强。

MEMORY 存储引擎将所有的数据存在 RAM 中，在需要快速定位记录和其它类似数据的环境下，可提供极快的访问。MEMORY 的缺陷在于对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常重启后表的数据是可恢复的。MEMORY 表通常用于更新不太频繁的小表，快速定位访问结果。

MERGE 用于将一组等同的 MyISAM 存储引擎的表以逻辑方式组合在一起，并作为一个对象应用它们。MERGE 表的优点在于可以突破对单个 MyISAM 表大小的限制，并通过将不同的表分布在多个磁盘上，改善 MERGE 表的访问效率。对数据局仓库等 VLDB 环境很适合。

最后，关于存储引擎的选择都是根据别人实际经验去总结的。并不是一定契合你的应用场景，最终需要用户对各自应用进行测试，通过测试来获取最合适的结果。就像我开始列举的示例，数据量很庞大，对查询和插入业务比较频繁，我就开始对 MyISAM 存储引擎进行测试，确实比较符合我的应用场景。

4、表的优化（碎片整理）

在开始介绍存 MyISAM 和 InnoDB 储引擎的时候，我也展示过存储大量数据所占的磁盘空间。使用 OPTIMIZE TABLE 来优化 test 数据库下的 test 表，优化之前，这张表占据磁盘空间大概在 824M；通过优化之后，有明显的改善，系统回收了没有利用的空间，test 表所耗磁盘空间明显下降，优化之后只有 456M。这里就不贴磁盘所占空间的截图了。

1
OPTIMIZE TABLE test.`test`;

优化之后，统计（count）数据效率也有所提升，大概在 2.5sec 左右：

1
2
3
4
5
6
7
mysql [test]> select count(*) from test; -- 使用的是innodb存储引擎测试
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (2.468 sec)

优化之前，统计数据大概在 3.080 sec。经过对比，效率提升是可观的。

你也可以使用 explain 执行计划对查询语句进行优化。关于 MySQL 优化方面的知识，并不是本文的重点，就不做过多描述。

二、索引设计与使用

1、索引简介

在涉及到 MySQL 的面试当中，会提到最左前缀索引，都被玩成梗了。

MySQL 所有列类型都可以被索引，对相关列合理的使用索引是提高查询（select）操作性能的最佳方法。根据引擎可以定义每张表的最大索引数和最大索引长度，MySQL 的每种存储引擎（MyISAM、InnoDB 等等）对每张表至少支持 16 个索引，总索引长度至少为 256 字节。大多数存储引擎有更高的限制。

MyISAM 和 InnoDB 存储引擎默认创建的表都是 BTREE 索引。在 MySQL8.0 之前是不只支持函数索引的，MySQL5.7 推出了虚拟列功能，在 MySQL8.0 开始支持函数索引，也是 8.0 版本的新特性之一。

MySQL 支持前缀索引，对索引字段的前 N 个字符创建索引，前缀索引长度和存储引擎有关。有很多人经常会问到，MySQL 支持全文索引吗？我的回答是：支持。MySQL5.6 之前 MyISAM 存储引擎支持全文索引（FULLTEXT），5.6 之后 InnoDB 开始支持全文索引。

为 test 表创建 10 个字节的前缀索引，创建索引的语法如下：

1
CREATE INDEX girl_name ON table_name(test(10));

同样可以使用 alter table 语句去新增索引，给 girl 表的字段 girl_name 新增一个索引：

1
ALTER TABLE test.`girl` ADD INDEX idx_girlname(girl_name);

对于使用索引的验证可以使用 explain 执行计划去判断。关于索引的简述就介绍这么多，更多基础知识可以参考官方文档或者权威书籍。

2、设计索引原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则。有助于提升索引的使用效率。

搜索的索引列，不一定是所要选择的列。最合适的索引列，往往是出现在 where 子句中的列，或者是连接子句中指定的列，而不是出现在 select 后选择列表中的列。

使用唯一索引。考虑某列中值的分布，索引列的基数越大，索引效果越好。

使用短索引。如果对字符串列进行索引，应指定一个前缀长度。比如 char (100)，思考一下，重复度的问题。是全部索引来的快，还是对部分字符进行索引更优？

利用最左前缀。在创建一个 N 列的索引时，实际上是创建了 MySQL 可利用的 N 个索引。多列索引可以起几个索引的作用，利用索引中最左边的列表来匹配行。这样的列集称为最左前缀。都快被涉及到 MySQL 的面试玩成梗了，哈哈。

注意不要过度使用索引。不要以为使用索引好处多多，就在所有的列上全部使用索引，过度使用索引反而会适得其反。每个额外的索引会占用磁盘空间，对磁盘写操作性能造成损耗。在重构的时候，索引也得更新，造成不必要的时间浪费。

InnoDB 存储引擎的表。对于使用 InnoDB 存储引擎的表，记录默认按一定的顺序保存。有如下几种情况：

  • 如果有明确定义的主键，则遵循主键顺序保存；
  • 在没有主键，但有唯一索引的情况下，会遵循唯一索引顺序保存；
  • 既没有主键又没有唯一索引，表中会自动生成一个内部列，并遵循这个列的顺序保存。

以上就是对索引设计原则的简单介绍。

3、B-TREE 与 HASH 索引

使用这些索引时，应该考虑索引是否当前使用条件下生效！在使用 MEMORY 存储引擎的表中可以选择使用 HASH 索引或者 B-TREE 索引，两种不同的索引有其各自适用的范围。

HASH 索引。只用于这类关系操作符：=、<=> 的操作比较，优化器不能使用 HASH 索引来加速 order by 操作。MySQL 不能确定在两个值之间大约有多少行。

B-TREE 索引。对于 B-TREE 索引，使用 >、<、>=、<=、BETWEEN、!= 或者 <>、亦或是使用 like ‘condition’。其中’condition’不以通配符开始的操作符时，都可以使用相关列上的索引。

关于索引就介绍到这里。合理的使用索引将有助于提升效率，但并不是使用的索引越多越好。

三、数据类型选择

  • 字符串类型 char 与 varchar
  • 浮点数和定点数
  • 日期类型

工作中，个人使用经验。Oracle 里面使用 BLOB 存储大字段比较频繁，TEXT 相对少见，使用 VARCHAR2 类型比较多。但在 MySQL 中是不支持 VARCHAR2 类型的。

1、CHAR 与 VARCHAR

char 和 varchar 类型类似，用于存储字符串，但它们保存和检索的方式不同。char 类型属于固定长度（定长）类型的字符串，varchar 属于可变长度的字符串类型。在 MySQL 的严格模式中，使用的 char 和 varchar，超过列长度的值不会被保存，并且出现错误提示。

char 优缺点。char 是固定长度，处理速度比 varchar 要快，但缺点是浪费存储空间，没有 varchar 那么灵活。varchar。随着 MySQL 的不断升级，varchar 类型也在不断优化，性能也在提升，被用于更多的应用中。

MyISAM 存储引擎：建议使用固定长度的数据列代替可变长度的数据列。

InnoDB 存储引擎：建议使用 VARCHAR 类型。

MEMORY 存储引擎：使用固定长度数据类型存储。

2、TEXT 与 BLOB

一般情况，存储少量的字符串时，会选择 char 和 varchar 类型。而在保存较大文本时，通常选择 TEXT 或者 BLOB 大字段，二者之间的区别在于 BLOB 能存二进制数据，比如：照片，TEXT 类型只能存字符数据。这也是为什么我在开始的时候提及到个人工作中见到 BLOB 类型相对较多。TEXT 和 BLOB 还包括不同类型：

  • TEXT、LONGTEXT、MEDIUMINT、MEDIUMTEXT、TINYTEXT；
  • BLOB、LONGBLOB、MEDIUMBLOB、TINYBLOB。

区别在于存储文本长度和字节不同。

需要注意的点：

  • BLOB 和 TEXT 值会引起一些性能问题，尤其是执行大量删除操作时；
  • 可以使用合成索引提高大字段的查询性能；
  • 在不必要的时候避免检索大字段；
  • 将 BLOB 和 TEXT 分离到不同的表中。

3、浮点数与定点数

浮点类型一般用于表示含有小数部分的值。列举一些示例：

  • double 类型：用于浮点数（双精度）；
  • decimal 类型：MySQL 中表示定点数；
  • float 类型：用于浮点数（单精度）。

学过 Java 语言的同学，对这些浮点类型并不陌生吧。

注意点：浮点数存在误差问题，对精度比较敏感的数据，避免对浮点类型做比较。

4、日期类型

谈到日期类型，又让我想起了 7 年前学 Java 语言的时候，会写一个工具类（Utils.java），将常用的处理日期的方法写进去然后调用。经常用到的一个方法（SimpleDateFormat），对时间戳进行转换格式化。

MySQL 中常用的日期类型有：

  • DATE
  • DATETIME
  • TIME
  • TIMESTAMP

如果需要记录年月日时分秒，并且记录的年份比较久远，最好用 DATETIME，而不要使用 TIMESTAMP 时间戳。TIMESTAMP 表示的范围比 DATETIME 短得多。

四、字符集（字符编码）设置

从本质上来说，计算机只能是被二进制代码（010101）。因此，不论是计算机程序还是处理的数据，最终都会转换成二进制代码，计算机才能识别。为了让计算机不仅能做科学计算，也能处理文字信息，于是计算机字符集诞生了。

字符编码（英语：Character encoding）、字集码是把字符集中的字符编码为指定集合中某一对象)（例如：比特模式、自然数序列、8 位组或者电脉冲），以便文本在计算机中存储和通过通信网络的传递。常见的例子包括将拉丁字母表编码成摩斯电码和 ASCII。其中，ASCII 将字母、数字和其它符号编号，并用 7 比特的二进制来表示这个整数。通常会额外使用一个扩充的比特，以便于以 1 个字节的方式存储。

在计算机技术发展的早期，如 ASCII（1963 年）和 EBCDIC（1964 年）这样的字符集逐渐成为标准。但这些字符集的局限很快就变得明显，于是人们开发了许多方法来扩展它们。对于支持包括东亚 CJK 字符家族在内的写作系统的要求能支持更大量的字符，并且需要一种系统而不是临时的方法实现这些字符的编码。

引用自维基百科对字符编码的介绍。

1、Unicode

Unicode 是什么？是统一编码，是计算机科学领域的业界标准。从最初的的 1.0.0 到目前最新的 14.0 版本，对应 ISO/IEC 10646-N:xxxx。说一下 UTF-8、UTF-16、UTF-16LE、UTF-32BE、UTF-32LE 等等大家应该很熟悉了。

2、常见字符集

常见的字符集：

  • UTF-8：泛用性最广泛；
  • GBK：对中文支持非常友好，在 GB2312 基础上进行了扩充；
  • GB2312：对中文字符集支持，；
  • GB18030：支持中文字符集，解决 GBK 强制力不够的问题。

3、MySQL 支持的字符集

通过 show character set; 命令可以查看 MySQL 支持的字符集。我只展示部分：

1
2
3
4
5
6
7
mysql [test]> show character set;
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |
| utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |
| utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |

或者你还可以使用 DESC information_schema.CHARACTER_SETS 查看所有字符集和字符集默认的校对规则。

查看相关字符集校对规则，可以使用 SHOW COLLATION 配合 LIKE 模糊搜索 gbk 字符集。

1
SHOW COLLATION LIKE 'gbk%';

MySQL 字符集设置：默认可以过配置文件设置 character-set-server 参数。

  • Linux 发行版中安装一般在 my.cnf 中配置；
  • Windows 下在 my.ini 文件中配置 
1
2
3
[mysqld]
character-set-server=utf-8
character-set-server=gbk

额外再提一点，判断字符集所占字节，可以使用函数 LENGTH()：

1
SELECT LENGTH('中');

如果使用的是 UTF-8 编码，默认汉字是占用 3 个字节，使用 GBK 则占用 2 个字节。字符编码就介绍到这里。

五、MySQL 示例数据库 sakila

视图、存储过程、函数、触发器。这里给出我自己随机生成海量数据用到的函数和存储过程。

1、函数

创建函数，使用 DELIMITER 声明，使用 CREATE FUNCTION 创建函数，tolove 表的创建在介绍存储引擎过程中已经有展示过。

1
2
3
4
5
6
7
8
9
/** 创建函数 生成学号 **/
DELIMITER $
CREATE FUNCTION rand_number() RETURNS INT
BEGIN
	DECLARE i INT DEFAULT 0;
	SET i= FLOOR(1+RAND()*100);
	RETURN i;
END $
DELIMITER $

创建函数：用于生成姓名随机字符串

1
2
3
4
5
6
7
8
9
10
11
12
13
14
/** 创建函数 生成姓名随机字符串 **/
DELIMITER $
CREATE FUNCTION rand_name(n INT) RETURNS VARCHAR(255)
BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT '';
	DECLARE i INT DEFAULT 0;
	WHILE i < n DO
	SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
	SET i = i+1;
	END WHILE;
	RETURN return_str;	
END $
DELIMITER $

2、存储过程

创建存储过程，使用 CREATE PROCEDURE 创建：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
/** 创建存储过程 **/
DELIMITER $
CREATE PROCEDURE insert_tolove(IN max_num INT(10))
BEGIN
	DECLARE i INT DEFAULT 0;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
	START TRANSACTION;
	WHILE i< max_num DO
	INSERT INTO test.`tolove`(ID,GIRL_NAME,GIRL_AGE,CUP_SIZE) VALUES(NULL,rand_name(5),rand_number(),NULL);
	SET i = i + 1;
	END WHILE;
COMMIT;
END $
DELIMITER $

使用 CALL 调用存储过程，随机生成百万数据：

1
2
/** 调用存储过程 **/
CALL insert_tolove(100*10000);

删除函数或者存储过程，使用 DROP 关键字

1
2
3
4
-- 删除函数rand_name
DROP FUNCTION rand_name;
-- 删除存储过程insert_tolove
DROP PROCEDURE insert_tolove;

3、触发器

创建触发器使用 CREATE TRIGGER，这里就引用 sakila 数据库实例。如果存在，使用了判断语句 IF EXISTS，然后删除 DROP TRIGGER 已经存在的触发器。

1
2
3
4
5
6
7
8
9
DELIMITER $$
USE `sakila`$$
DROP TRIGGER /*!50032 IF EXISTS */ `customer_create_date`$$
CREATE
    /*!50017 DEFINER = 'root'@'%' */
    TRIGGER `customer_create_date` BEFORE INSERT ON `customer` 
    FOR EACH ROW SET NEW.create_date = NOW();
$$
DELIMITER ;

4、sakila 数据库

在文中我反复提到了 MySQL 的示例数据库 sakila，是一个完整的学习 MySQL 的好例子。包含了视图、存储过程、函数和触发器。可以去 MySQL 的官网获取 SQL 脚本。

以上就是此次文章的所有内容的，希望能对你的工作有所帮助。

—END—