乐趣区

关于mysql:MySQL开发篇存储引擎的选择真的很重要吗

jiezi

前言

谁说 MySQL 查问千万级别的数据很拉跨？我明天就要好好的和你拉拉家常，畅谈到深夜，一起过元旦！这篇文章也是年前的最初一篇，心愿能带给大家些许播种，人不知; 鬼不觉查找文档和参考实体书籍就写了这么多，本人都感觉到意外。不禁感叹到，晓得的越多，才晓得不晓得的更多。

开发人员或者是 DBA 都应该关注 MySQL 应用的存储引擎，抉择适合存储引擎对你的利用性能晋升是显著的。在浏览到本文的时候，必定是有肯定的 MySQL 或者其它数据库根底的，不然有些中央看着会很吃力。重点中央，我都进行了加粗解决，这样更容易获取要害知识点。

对于存储引擎，一篇文章也不可能八面玲珑，对集体感觉比拟重要、于工作无益的方面进行论述。如果真的去深挖，预计得一本书的篇幅。顺带还介绍一些数据类型抉择、字符集设置、索引的应用；视图、存储过程、函数以及触发器啊等等会在下一篇博文进行具体的形容。但本文不会做太具体的叙述。本篇文章以存储引擎的抉择为外围，如果有呈现瑕疵的中央，心愿您能留下贵重的倡议。

明天发现了一个 神奇的参数：-site:xxxx.net

注释

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

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 等查问反对的引擎，这里只展现局部哟：

[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 数据库了哟！

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

再看演示 应用 InnoDB 存储引擎创立的表 test，同样为了演示，当时随机生成了 1kw 条数据。

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 工具 创立，占用磁盘空间很小。因为每个记录是被独自压缩，所以拜访开始十分小。

梳理一下 MyISAM 存储引擎的要点，如下图 1 -2-2- 1 所示：

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

2.2、InnoDB 存储引擎

长处与毛病 ：InnoDB 存储引擎提供了具备 提交 （commit）、 回滚 （rollback）和 解体复原 能力的 事务平安 。但比照 MyISAM 存储引擎，InnoDB 写的解决效率绝对差一些，并且会占用更多的磁盘空间保留数据和索引。下图是我存储了 1kw 条数据的表，并且应用的是 InnoDB 存储引擎。student01 表同样应用了 InnoDB 存储引擎，存储数据为 100w 条。从下图 能够看出存储数据索引在.ibd 文件中、表构造则存在.frm 文件中。

2.2.1、主动增长列

InnoDB 表的主动增长列能够手工插入，但插入的值为空或者 0，则理论插入的将是主动主动增长后的值。

原本想持续应用 bols 那张表作为演示的，思来想去还是正经一点。为了演示，我又新增了一张表为 autoincre_test，示意 id 设置为主键且自增长，存储引擎抉择 InnoDB。而后插入了 3 条数据进行演示。查问以后线程最初插入数据的记录应用值：

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 物理模型图，这句话做了超链接，能够间接拜访。给出我之前逆向生成的 world 数据库的物理模型：

在创立索引时，能够指定在删除、更新父表时，对子表进行的相应操作蕴含：

  • restrict
  • cascade
  • set null 和 no action

其中 restrict 和no action雷同，restrict 限度在子表有关联记录的状况下父表无奈更新 ；cascade 示意在父表更新或删除的时候，级联更新或者删除子表对应记录 ；set null 示意在父表更新或删除的时候，子表的对应字段被 set null。 抉择 cascade 以及 set null 时须要审慎 操作，有可能导致数据失落。

在导入多个表的数据时，如果疏忽表之前的导入程序，能够临时敞开外键查看；同样 执行 load data 和 alter table 时也能够临时敞开外键查看 放慢解决的速度，晋升效率。敞开外键查看的命令为：

set foreign_key_checks=0;

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

set foreign_key_checks=1;

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

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 文件达到目标，但能够应用如下命令操作：

alter table table_name discard tablespace;
alter table table_name import tablespace;

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

留神 ：即使多表存储更有劣势，然而 共享表存储空间仍旧是必须的，InnoDB 将外部数据字典和在线重做日志存在这个文件中。

梳理一下 InnoDB 存储引擎的要点，如下图 1 -2-2- 2 所示：

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

2.3、MEMORY 存储引擎

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

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：

-- 创立索引指定索引类型为 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 为分表：

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 进行插入：

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：

-- 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 引擎进行测试。

-- 批改创立的 tolove 表为 MYISAM 引擎进行测试
ALTER TABLE test.`tolove` ENGINE=MYISAM;

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

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

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

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

如果在工具中无奈看全，能够导出成 xml、csv、html 等查问，以下是我查问出本人创立表时设置的存储引擎为InnoDB：

-- 显示出我创立的 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 存储引擎进行测试，的确比拟合乎我的利用场景。

对于存储引擎的抉择，总结简化如下图 1 -3：

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

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

OPTIMIZE TABLE test.`test`;

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

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 个字节的前缀索引，创立索引的语法如下：

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

同样能够 应用 alter table语句去新增索引，给 girl 表的字段 girl_name 新增一个索引：

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 反对的字符集。我只展现局部：

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 字符集。

SHOW COLLATION LIKE 'gbk%';

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

  • Linux 发行版中装置个别在 my.cnf 中配置；
  • Windows 下在 my.ini 文件中配置
[mysqld]
character-set-server=utf-8
character-set-server=gbk

额定再提一点，判断字符集所占字节，能够 应用函数 LENGTH()：

SELECT LENGTH('中');

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

五、MySQL 示例数据库 sakila

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

1、函数

创立函数，应用 DELIMITER 申明，应用CREATE FUNCTION 创立函数，tolove 表的创立在介绍存储引擎过程中曾经有展现过。

/** 创立函数 生成学号 **/
DELIMITER $
CREATE FUNCTION rand_number() RETURNS INT
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i= FLOOR(1+RAND()*100);
    RETURN i;
END $
DELIMITER $

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

/** 创立函数 生成姓名随机字符串 **/
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 创立：

/** 创立存储过程 **/
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 调用存储过程，随机生成百万数据：

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

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

-- 删除函数 rand_name
DROP FUNCTION rand_name;
-- 删除存储过程 insert_tolove
DROP PROCEDURE insert_tolove;

3、触发器

创立触发器应用 CREATE TRIGGER，这里就援用 sakila 数据库实例。如果存在，应用了判断语句 IF EXISTS，而后删除 DROP TRIGGER 曾经存在的触发器。

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 脚本。

文末留一个神秘的参数，通过此种形式能够过滤你不想看到的内容哟！无论在手机端或者 PC 端都可失效，亲测可用。

xxxx(检索的内容) -site:xxxn.net
-- 或者
xxxx(检索的内容) -site:xxshu.com

继续更新优化中 …

总结

以上就是此次文章的所有内容的，心愿能对你的工作有所帮忙。感觉写的好，就拿出你的一键三连。在公众号上更新的可能要快一点，目前还在欠缺中。能看到这里的，都是帅哥靓妹。如果感觉总结的不到位，也心愿能留下您贵重的意见，我会在文章中进行调整优化。

原创不易，转载也请表明出处和作者，尊重原创。不定期上传到 github 或者 gitee。认准龙腾万里 sky，如果看见其它平台不是这个 ID 收回我的文章，就是转载的。linux 系列文章 ：《 初学者如何入门 linux，原来 linux 还能够这样学》曾经上传至 github 和 gitee。集体 github 仓库地址，个别会先更新 PDF 文件，而后再上传 markdown 文件。如果拜访 github 太慢，能够应用 gitee 进行克隆。

tips：应用 hexo 搭建的动态博客也会定期更新保护。

https://github.com/cnwangk/SQL-study

<H5 align=center> 作者：龙腾万里 sky</H5>

退出移动版