MySQL架构与存储引擎

逻辑架构

MySQL的逻辑结构图将整个MySQL分成四层

连接层

最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

连接到服务器，服务器需要对其进行验证，也就是用户名、IP、密码验证，一旦连接成功，还要验证是否具有执行某个特定查询的权限（例如，是否允许客户端对某个数据库某个表的某个操作）。

当MySQL启动（MySQL服务器就是一个进程），等待客户端连接，每一个客户端连接请求，服务器都会新建一个线程处理（如果是线程池的话，则是分配一个空的线程），每个线程独立，拥有各自的内存处理空间。

#查看最大连接数
mysql> show VARIABLES like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set, 1 warning (0.01 sec)

连接到服务器，服务器需要对其进行验证，也就是用户名、IP、密码验证，一旦连接成功，还要验证是否具有执行某个特定查询的权限（例如，是否允许客户端对某个数据库某个表的某个操作）

SQL处理层

这一层主要功能有：SQL语句的解析、优化，缓存的查询，MySQL内置函数的实现，跨存储引擎功能（所谓跨存储引擎就是说每个引擎都需提供的功能（引擎需对外提供接口）），例如：存储过程、触发器、视图等。

如果是查询语句（select语句），首先会查询缓存是否已有相应结果，有则返回结果，无则进行下一步（如果不是查询语句，同样调到下一步）
解析查询，创建一个内部数据结构（解析树），这个解析树主要用来SQL语句的语义与语法解析；
优化：优化SQL语句，例如重写查询，决定表的读取顺序，以及选择需要的索引等。这一阶段用户是可以查询的，查询服务器优化器是如何进行优化的，便于用户重构查询和修改相关配置，达到最优化。这一阶段还涉及到存储引擎，优化器会询问存储引擎，比如某个操作的开销信息、是否对特定索引有查询优化等。

缓存

MySQL查询缓存保存查询返回的完整结构。当查询命中该缓存时，MySQL会立刻返回结果，跳过了解析、优化和执行阶段。

查询缓存系统会跟踪查询中涉及的每个表，如果这些表发生了变化，那么和这个表相关的所有缓存数据都将失效。

MySQL将缓存存放在一个引用表中，通过一个哈希值引用，这个哈希值包括了以下因素，即查询本身、当前要查询的数据库、客户端协议的版本等一些其他可能影响返回结果的信息。

当判断缓存是否命中时，MySQL不会进行解析查询语句，而是直接使用SQL语句和客户端发送过来的其他原始信息。所以，任何字符上的不同，例如空格、注解等都会导致缓存的不命中。

当查询语句中有一些不确定的数据时，则不会被缓存。例如包含函数NOW()或者CURRENT_DATE()的查询不会缓存。包含任何用户自定义函数，存储函数，用户变量，临时表，mysql数据库中的系统表或者包含任何列级别权限的表，都不会被缓存。

有一点需要注意，MySQL并不是会因为查询中包含一个不确定的函数而不检查查询缓存，因为检查查询缓存之前，MySQL不会解析查询语句，所以也无法知道语句中是否有不确定的函数。

事实则是，如果查询语句中包含任何的不确定的函数，那么其查询结果不会被缓存，因为查询缓存中也无法找到对应的缓存结果。

有关查询缓存的配置如下所示：

query_cache_type:是否打开查询缓存。可以设置为OFF、ON和DEMAND。DEMAND表示只有在查询语句中明确写明SQL_CACHE的语句才会放入查询缓存。
query_cache_size:查询缓存使用的总内存空间。
query_cache_min_res_unit:在查询缓存中分配内存块时的最小单元。较小的该值可以减少碎片导致的内存空间浪费，但是会导致更频繁的内存块操作。
query_cache_limit:MySQL能够查询的最大查询结果。如果查询结果大于这个值，则不会被缓存。因为查询缓存在数据生成的时候就开始尝试缓存数据，所以当结果全部返回后，MySQL才知道查询结果是否超出限制。超出之后，才会将结果从查询缓存中删除。

mysql> show variables like '%query_cache_type%';  -- 默认不开启
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+
1 row in set, 1 warning (0.00 sec)


mysql> show variables like '%query_cache_size%'; -- 默认值1M
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| query_cache_size | 1048576 |
+------------------+---------+
1 row in set, 1 warning (0.00 sec)


mysql> SET GLOBAL query_cache_type = 1; -- 会报错
ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it

query_cache_type只能配置在my.cnf文件中，这大大限制了qc的作用，在生产环境建议不开启，除非经常有sql完全一模一样的查询。

QC严格要求2次SQL请求要完全一样，包括SQL语句，连接的数据库、协议版本、字符集等因素都会影响

对查询缓存的优化是数据库性能优化的重要一环。判断流程大致如下图所示。

缓存命中率可以通过如下公式计算：Qcache_hits/(Qcache_hits + Com_select)来计算

解析查询

解析器通过关键字将SQL语句进行解析，并生成对应的解析树。MySQL解析器将使用MySQL语法规则验证和解析查询。

预处理器则根据一些MySQL规则进行进一步检查解析书是否合法，例如检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。

SELECT DISTINCT 
  < select_list > 
FROM
  < left_table > < join_type > 
  JOIN < right_table > 
    ON < join_condition > 
WHERE < where_condition > 
GROUP BY < group_by_list > 
HAVING < having_condition > 
ORDER BY < order_by_condition > 
LIMIT < limit_number > ;

查询优化器

查询优化器会将解析树转化成执行计划。一条查询可以有多种执行方法，最后都是返回相同结果。优化器的作用就是找到这其中最好的执行计划。
生成执行计划的过程会消耗较多的时间，特别是存在许多可选的执行计划时。如果在一条SQL语句执行的过程中将该语句对应的最终执行计划进行缓存，当相似的语句再次被输入服务器时，就可以直接使用已缓存的执行计划，从而跳过SQL语句生成执行计划的整个过程，进而可以提高语句的执行速度。

MySQL使用基于成本的查询优化器(Cost-Based Optimizer，CBO)。它会尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最少的一个。
优化器会根据优化规则对关系表达式进行转换，这里的转换是说一个关系表达式经过优化规则后会生成另外一个关系表达式，同时原有表达式也会保留，经过一系列转换后会生成多个执行计划，然后CBO会根据统计信息和代价模型(Cost Model)计算每个执行计划的Cost，从中挑选Cost最小的执行计划。由上可知，CBO中有两个依赖：统计信息和代价模型。统计信息的准确与否、代价模型的合理与否都会影响CBO选择最优计划。

1
2
3

EXPLAIN select * from account where name ='';
-- where 1=1 经优化不走where语句
EXPLAIN select * from account where 1=1;

1	EXPLAIN select * from account where id is null; -- 默认不会使用where条件

通过上面的sql大概就能看出，虽然现在还没学执行计划，但通过这个已经看出一个sql并不一定会去查询物理数据，sql解析器会通过优化器来优化程序员写的sql

1 2	explain select * from account t where t.id in (select t2.id from account t2); show warnings;

引擎层

存储引擎层，存储引擎真正负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信，不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取，例如：MYISAM和InnoDB

存储层

数据存储层，主要讲数据存储在运行于裸设备的文件系统之上。并完成存储引擎的交互(文件系统)

存储结构

逻辑存储结构

在mysql中其实还有个schema的概念，这概念没什么太多作用，只是为了兼容其他数据库，所以也提出了这个，在mysql中 database 和schema是等价的

create database demo;
show databases;
drop schema demo;  -- 等价于drop database demo;
show databases;

物理存储结构

数据库的数据库（DataDir），mysql安装的时候都要指定datadir，其查看方式为：

1	show VARIABLES like 'datadir'; --其规定所有建立的数据库存放位置

数据库

创建了一个数据库后，会在上面的datadir目录新建一个子文件夹

表文件

用户建立的表都会在上面的目录中，它和具体的存储引擎相关，但有个共同的就是都有个frm文件，它存放的是表的数据格式。

1	mysqlfrm --diagnostic D:\Program Files\MySql\data\account.frm (要查看.frm需要安装mysql utilities)

mysql utilities 安装

tar -zxvf mysql-utilities-1.6.5.tar.gz

cd mysql-utilities-1.6.5

python ./setup.py build

python ./setup.py install

存储引擎

常见的MySQL存储引擎：MyISAM，Innodb，Archive，Memory，Federated

查看引擎

查看你的mysql现在已提供什么存储引擎

1 2	show engines;

查看你的mysql当前默认的存储引擎

1	show variables like '%storage_engine%';

mysql>  show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set, 1 warning (0.00 sec)

MyISAM

简介

由于这个原因呢，现在还有大量的服务器在使用这种MyISAM的表，另外MyISAM表也是MYQL大部分系统表和临时表所使用的一种存储引擎。

这里所说的临时表，是指在排序分组等操作中，当数量超过一定的大小之后，由查询优化器所建立的磁盘临时表，MyISAM存储引擎，会将表存储在两个系统文件中，一个是数据文件，以MYD为扩展名，另一个是索引文件，以MYI为扩展名。

MyISAM 管理非事务表、是ISAM 的扩展格式。除了提供ISAM里所没有的索引的字段管理等的大量功能、MyISAM 还使用一种表格锁定的机制、来优化多个并发的读写操作。MyISAM 提供高速存储和检索、以及全文搜索能力；在MYSQL5.5.5版本及以下的所有MYSQL配置里被支持。也是默认的存储引擎。

特性

并发性和所级别

MyISAM使用的是表级锁，而不是行级，也就意味着，给表中的数据进行修改时，需要对整个表，进行加锁，而在对表中的数据进行读取时，也需要就是对所以的表加共享锁，从这里我们可以看到，使用MyISAM搜索引擎的表读取和写入两种操作的是互斥的，当然在一些情况下呢，当我们对表格数据进行读取时，也可以在表的末尾插入数据，因此呢，从上面介绍可以看出，MyISAM对于读写混合操作的并发性并不太好，如果只是只读操作的话，就并发性而言，性能还是可以接受的，因为共享锁并不会阻塞共享锁

表损坏修复

MyISAM对由于任意意外关闭而损坏的MyISAM表，进行检查和修复操作，这里所说的修复的并不是事物恢复，因为MyISAM并不是一种是事务型的存储引擎，所以他也不可能记录进行事务恢复所需要的相关日志，所以要注意对MyISAM表进行修复，可能会造成数据丢失

其他特性

不支持事务、不具备AICD特性(原子性、一致性、分离性、永久性);
表级别锁定形式(更新数据时锁定整个表、这样虽然可以让锁定的实现成本很小但是同时大大降低了其并发的性能)；
读写相互阻塞(不仅会在写入的时候阻塞读取、还会在读取的时候阻塞写入、但是读取不会阻塞读取);
只会缓存索引(myisam通过key_buffer_size来设置缓存索引，提高访问性能较少磁盘IO的压力、但是只缓存索引、不缓存数据);
读取速度快、占用资源比较少;
不支持外键约束、只支持全文检索；
是MySQL5.5.5版本之前的默认存储引擎；

应用场景

不需要事务支持的场景;
读多或者写多的单一业务场景、读写频繁的则不适合、会阻塞；
读写并发访问较低的业务；
数据修改相对较少的业务；
以读为主的业务(如博客、图片信息数据库、用户数据库等);
对数据的一致性要求不是很高的业务；
服务器硬件资源相对比较差的机器；

调优精要

设置合适的索引(缓存机制)；
调整读写优先权限、根据业务需求、确保重要操作更有执行权限；
启用延时插入(尽量批量插入、降低写的频率);
写数据的时候、顺序操作、让insert数据都写入到尾部、减少阻塞；
分解大的时间长的操作、降低单个操作的阻塞时间；
降低并发数(减少数据库的访问、高并发场景的话、可以使用队列机制);
对于静态更新不频繁的数据库数据、充分利用Query Cache或者Memcached缓存服务、极大可能的提高访问效率；
count的时候、只有count(*)会直接返回行数、才是效率最高的；带有where条件的count都需要进行全部数据的访问；
可以配置主从数据库的时候、主数据库使用InnoDB、从数据库使用MyISAM、进行读写分离；

创建数据库

创建数据库，创建表并指定存储引擎为MyISAM，字符集为utf8

create database myisamdb;
use myisamdb;
create table myisam (id int unsigned auto_increment primary key,
name varchar(20) not null )engine=myisam charset=utf8;

文件构成

就是一个文件系统中myIsam这张表的存储方式，在这里我们可以看到，有三个以myIsam开头的文件，除了上面提到过的MYD、MYI两个文件之外呢，还有一个以frm为扩展名的文件，这个文件的并不是MyISAM存储引擎所特有的一个文件，而对于mysql所有的存储引擎来说，都会有一个frm为扩展名的文件，这个文件是用于记录这个表的结构的，而MYD和MYI两个文件的是MyISAM存储引擎所特有的一个文件。

文件结构

ls -l  myisam*

-rw-r--r-- 1 baiyp 197609     8586 12月 22 19:58 myisam.frm
-rw-r--r-- 1 baiyp 197609 24117248 12月 22 20:01 myisam.MYD
-rw-r--r-- 1 baiyp 197609 10779648 12月 22 20:01 myisam.MYI

在存储的时候、每一个MyISAM的表都对应硬盘上的三个文件(同一文件名、不同扩展名)：

.frm：保存表的定义、这个文件不是MyISAM引擎的一部分、是数据库服务器的一部分；
.MYD：保存表的数据；
.MYI：保存表的索引文件；
(.MYD 和 .MYI 是MyISAM 的关健点 )

表压缩

MySQL的MyISAM存储引擎支持压缩表空间，压缩后的表空间会减少，但是压缩后的表是只读的，不能插入和更新数据，如果需要更新，则需要解压后更新，再压缩。

示例

将数据插入到表myisam表中

1 2	insert into myisam(name) values('联想tinkpad'),('Dell外星人'), ('雷神玄武'),('mac pro');

使用蠕虫复制将myisam表空间迅速膨胀

1	insert into myisam select null,name from myisam;

查看压缩前的myisam表空间大小

1	ls -lh myisam*

$ ls -lh myisam*
-rw-r--r-- 1 baiyp 197609 8.4K 12月 22 19:58 myisam.frm
-rw-r--r-- 1 baiyp 197609  23M 12月 22 20:01 myisam.MYD
-rw-r--r-- 1 baiyp 197609  11M 12月 22 20:01 myisam.MYI

使用myisampack命令将myisam表空间进行压缩

1	myisampack -b -f D:\Program Files\MySql\data\myisamdb\myisam

查看压缩后的myisam表空间大小

ls -lh myisam*
-rw-r--r-- 1 baiyp 197609 8.4K 12月 22 19:58 myisam.frm #表结构文件
-rw-r--r-- 1 baiyp 197609  12M 12月 22 20:01 myisam.MYD #表空间文件
-rw-r--r-- 1 baiyp 197609 1.0K 12月 22 20:20 myisam.MYI #表索引文件

由于压缩后表空间会改变，导致索引找不到记录对应的索引位置，所以要恢复索引

1	myisamchk -rq D:\Program Files\MySql\data\myisamdb\myisam

最后主要对标进行刷新

1 2	mysql> flush table myisam; Query OK, 0 rows affected (0.00 sec)

然后尝试插入数据会发现无法插入表变成了只读的

1 2	mysql> insert into myisam(name) values('联想tinkpad'); ERROR 1036 (HY000): Table 'myisam' is read only

Innodb

简介

InnoDB是事务安全的mysql存储引擎，也是mysql的默认存储引擎，特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读。同时也被设计用来最有效地利用以及使用内存和CPU。

Innodb是一种事务性存储引擎，完全支持事务得ACID特性，具有Redo Log和Undo Log，并且Innodb支持行级锁（并发程度更高）。

特性

支持事务(事务是指逻辑上的一组操作,组成这组操作的各个单元,要么全成功,要么全失败)

2. 行级锁定(更新时一般是锁定当前行):通过索引实现,全表扫描仍然会是锁定整个表,注意间隙锁的影响.

3. 读写阻塞与事务隔离级别相关.

4. 具有非常高效的缓存特性,能缓存索引,也能缓存数据.

5. 整个表和主键以Cluster方式存储,组成一颗平衡树.

6. 所有Secondary Index 都会保存主键信息.

7. 支持分区,表空间.类似于Oracle数据库.

8. 支持外键约束,不支持全文索引,5.5之前支持,后面不再支持.

9. 和MyISAM相比,InnoDB对于硬件资源要求比较高.

应用场景

需要支持事务的业务(例如转账,付款)

2. 行级锁定对于高并发有很好的适应能力,但是需要保证查询是通过索引完成.

3. **数据读写及更新都比较频繁的场景,**如:BBS,SNS,微博,微信等.

4. 数据一致性要求很高的业务.如:转账,充值等.

5. 硬件设备内存较大,可以很好利用InnoDB较好的缓存能力来提高内存利用率,尽可能减少磁盘IO的开销.

引擎调优精要

主键尽可能小,避免给Secondary index带来过大的空间负担.

2. 避免全表扫描,因为会使用表锁.

3. 尽可能缓存所有的索引和数据,提高响应速度,减少磁盘IO消耗.

在大批量小插入的时候,**尽量自己控制事务而不要使用autocommit自动提交,**有开关参数可以控制提交.

5. 合理设置Innodb_flush_log_at_trx_commit 参数值,不要过度追求安全性.

如果Innodb_flush_log_at_trx_commit的值为0,log buffer每秒就会被刷写日志文件进入磁盘,提交事务的时候不做任何操作.

6. 避免主键更新,因为这会带来大量的数据移动.

创建数据库

create database innodatabase;
use innodatabase;
create table innodb_table (id int unsigned auto_increment primary key,
name varchar(20) not null )engine=InnoDB charset=utf8;

文件构成

从物理意义上来讲，InnoDB表由共享表空间、日志文件组（redo文件组）、表结构定义文件组成。若将innodb_file_per_table设置为on，则系统将为每一个表单独的生成一个table_name.ibd的文件（独占表空间），在此文件中，存储与该表相关的数据、索引、表的内部数据字典信息。表结构文件则以.frm结尾，这与存储引擎无关。

文件结构

1
2
3

$ ls -l  innodb_table*
-rw-r--r-- 1 baiyp 197609  8586 12月 23 10:51 innodb_table.frm
-rw-r--r-- 1 baiyp 197609 98304 12月 23 10:51 innodb_table.ibd

.frm：保存表的定义、这个文件不是InnoDB引擎的一部分、是数据库服务器的一部分；
.ibd：这个文件包括了单独一个表的数据内容以及索引内容。

1 2	show variables like '%innodb_file_per_table%'; --可以查看是哪种类型的表空间

mysql> show variables like '%innodb_file_per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set, 1 warning (0.00 sec)

innodb_file_per_table = ON,也就是独立表空间的表.

共享表空间

会把表集中存储在一个系统表空间里。即每一个数据库的所有表的数据，索引文件全部放在一个文件中。该文件目录默认的是服务器的数据目录。默认的文件名为:ibdata1 初始化为10M。

作用：MySQL服务实例承载的所有数据库的所有InnoDB表的数据信息、索引信息、各种数据信息以及事物的回滚信息，全部存放在共享表空间文件中
位置：位于数据库根目录
文件名：ibdata1
大小：默认初始大小10M
可以使用“ show variables like ‘innodb_data_file_path’; ”来查看该文件的属性

独占表空间

每一个表分别创建一个表空间，这时。在对应的数据库目录里每一个表都有.ibd文件(这个文件包括了单独一个表的数据内容以及索引内容)。

作用：存放表的数据信息、索引信息、各种数据信息以及事物的回滚等信息
位置：位于数据库目录下
文件名：表名.ibd

【注意】：任何InnoDB表的元数据信息都必须要存储在共享表空间中，所以共享表空间必须存在，即便将全局系统变量innodb_file_per_table的值设置为 NO 开启

CSV格式存储

CSV存储引擎可以将csv文件作为mysql的表进行处理。存储格式就是普通的csv文件。

存储结构特点

数据以文本方式存储在文件中（Innodb则是二进制）

.CSV文件存储表内容
.CSM文件存储表的元数据如表状态和数据量
.frm文件存储表结构信息

CSV特点

以CSV格式进行数据存储（逗号隔开，引号）
所有的列必须都是不能为NULL的
不支持索引（不适合大表，不适合在线处理）
可以对数据文件直接编辑（保存文本文件内容）

示例

我们新建一个csv文件，但是不指定not null

1 2	mysql> create table mycsv(id int,c1 varchar(10),c2 char(20)) engine=csv; ERROR 1178 (42000): The storage engine for the table doesn't support nullable columns

我们指定各列不为空

1 2	mysql> create table mycsv(id int not null ,c1 varchar(10) not null,c2 char(20) not null) engine=csv; Query OK, 0 rows affected (0.01 sec)

向表中插入数据

mysql> insert into mycsv values ( 1,'aaa','bbb'),(2,'ccc','ddd') ;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from mycsv;
+----+-----+-----+
| id | c1  | c2  |
+----+-----+-----+
|  1 | aaa | bbb |
|  2 | ccc | ddd |
+----+-----+-----+
2 rows in set (0.00 sec)

修改下csv文件

vi mycsv.csv

1,"aaa111","bbb111"
2,"ccc222","ddd222"

首先我们刷新数据表

再次查询数据库

mysql> select * from mycsv;
+----+-----+-----+
| id | c1  | c2  |
+----+-----+-----+
|  1 | aaa | bbb |
|  2 | ccc | ddd |
+----+-----+-----+
2 rows in set (0.00 sec)

mysql> flush TABLES;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from mycsv;
+----+--------+--------+
| id | c1     | c2     |
+----+--------+--------+
|  1 | aaa111 | bbb111 |
|  2 | ccc222 | ddd222 |
+----+--------+--------+
2 rows in set (0.00 sec)

我们发现在文件中修改的数据已经添加到了数据库

增加索引

1 2	mysql> create index idx_id on mycsv(id); ERROR 1069 (42000): Too many keys specified; max 0 keys allowed

证明不支持索引

使用场景

适合做为数据交换的中间表（能够在服务器运行的时候，拷贝和拷出文件，可以将电子表格存储为CSV文件再拷贝到MySQL数据目录下，就能够在数据库中打开和使用。同样，如果将数据写入到CSV文件数据表中，其它web程序也可以迅速读取到数据。

Memory

简介

MEMORY是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。这些特性与前面的两个很不同。

每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。值得注意的是，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了，可以释放内存，甚至删除不需要的表。

MEMORY默认使用哈希索引。速度比使用B型树索引快。当然如果你想用B型树索引，可以在创建索引时指定。

注意，MEMORY用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。因此，基于MEMORY的表的生命周期很短，一般是一次性的。

特性

文件系统存储特点
也称HEAP存储引擎，所以数据保存在内存中
支持HASH索引和BTree索引
所有字段都是固定长度 varchar(10) = char(10)
不支持Blog和Text等大字段
Memory存储引擎使用表级锁

最大大小由max_heap_table_size参数决定

mysql> show VARIABLES like 'max_heap_table_size';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+
1 row in set, 1 warning (0.00 sec)

适用场景

主要用于内容变化不频繁的表，或者作为中间的查找表。对表的更新要谨慎因为数据没有被写入到磁盘中，服务关闭前要考虑好数据的存储

hash索引用于查找或者是映射表（邮编和地区的对应表）
用于保存数据分析中产生的中间表
用于缓存周期性聚合数据的结果表

创建数据库

mysql> create database memorydb;
Query OK, 1 row affected (0.00 sec)

mysql> use memorydb;
Database changed

mysql> create table mymemory(id int,c1 varchar(10),c2 char(10)) engine = memory;
Query OK, 0 rows affected (0.01 sec)

文件构成

1 2	$ ls -l mymemory* -rw-r--r-- 1 baiyp 197609 8608 12月 23 13:52 mymemory.frm # frm文件存储表结构（MySQL服务器层）

示例

创建带有text类型的表

我们发限无法创建带有Text大字段类型

1 2	mysql> create table tb_mymemory(id int,c1 varchar(10),c2 char(10),c3 text) engine = memory; ERROR 1163 (42000): The used table type doesn't support BLOB/TEXT columns

创建索引

mysql> create index idx_c1 on mymemory(c1);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx_c2 using btree on mymemory(c2);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

show index from mymemory;

show TABLE status LIKE ‘mymemory’;

与临时表的区别

Ferderated

简介

Ferderated存储引擎，默认是不支持的，如果要开启该存储引擎，需要在MySQL安装目录下的my.ini文件中，加上fedetated=1。重启MySQL即可生效。

由于Ferderated表数据不存储本地文件系统，而全部存储到远程服务器上，因此在本地系统中，除了要存储Ferdetated表结构信息，还要存储远程服务器信息。

特点

提供了访问远程MySQL服务器上表的方法
本地不存储数据，数据全部放到远程服务器上
本地需要保存表结构和远程服务器的连接信息

使用场景

偶尔的统计分析及手工查询

如何使用

默认禁止，启用需要再启动时增加federated参数

mysql://user_name[:password]@hostname[:port_num]/db_name/table_name

总结

不同的存储引擎都有各自的特点，以适应不同的需求，如表所示。为了做出选择，首先要考虑每一个存储引擎提供了哪些不同的功能。

功能	MylSAM	MEMORY	InnoDB	Archive
存储限制	256TB	RAM	64TB	None
支持事务	No	No	Yes	No
支持全文索引	Yes	No	No	No
支持树索引	Yes	Yes	Yes	No
支持哈希索引	No	Yes	No	No
支持数据缓存	No	N/A	Yes	No
支持外键	No	No	Yes	No

MyISAM

不支持事务、也不支持外键，优势是访问速度快，对事务完整性没有要求或者以select，insert为主的应用基本上可以用这个引擎来创建表
支持3种不同的存储格式，分别是：静态表；动态表；压缩表

静态表

表中的字段都是非变长字段，这样每个记录都是固定长度的，优点存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多（因为存储时会按照列的宽度定义补足空格）ps：在取数据的时候，默认会把字段后面的空格去掉，如果不注意会把数据本身带的空格也会忽略。

    ###### 动态表

记录不是固定长度的，这样存储的优点是占用的空间相对较少；缺点：频繁的更新、删除数据容易产生碎片，需要定期执行OPTIMIZE TABLE或者myisamchk-r命令来改善性能

压缩表

因为每个记录是被单独压缩的，所以只有非常小的访问开支

InnoDB

该存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM引擎，写的处理效率会差一些，并且会占用更多的磁盘空间以保留数据和索引。
InnoDB存储引擎的特点：支持自动增长列，支持外键约束

MEMORY存储引擎

Memory存储引擎使用存在于内存中的内容来创建表。每个memory表只实际对应一个磁盘文件，格式是.frm。memory类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。
MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引，两种不同类型的索引有其不同的使用范围

如何选择

可以根据以下的原则来选择 MySQL 存储引擎：

如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 是一个很好的选择。
如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。
如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。
如果只有 INSERT 和 SELECT 操作，可以选择Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。

MySQL架构与存储引擎