10、存储引擎*

一、存储引擎概述

  为了方便管理，人们把连接管理、查询管理、语法解析、查询优化这些不涉及真实数据存储的功能划分为MySQL Server的功能，把真实存储数据的功能划分为存储引擎的功能。所以在MySQL Server完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端就好了。

-- 查看存储引擎
SHOW ENGINES;
-- 查看默认的存储引擎
SHOW VARIABLES LIKE '%storage_engine%';
SELECT @@default_storage_engine;
-- 修改默认的存储引擎
SET DEFAULT_STORAGE_ENGINE = MyISAM;
-- 创建表时指定存储引擎
CREATE TABLE 表名(
    建表语句; 
) ENGINE = 存储引擎名称;
-- 修改表的存储引擎
ALTER TABLE 表名 ENGINE = 存储引擎名称;

二、引擎介绍

2.1、MyISAM和InnoDB(重要)

  MySQL5.5之前的默认存储引擎是MyISAM，MySQL5.5之后改为InnoDB。

　　InnoDB存储引擎，行级锁，提供了具有提交、回滚和崩溃回复能力的事务安全，支持自动增长列，支持外键约束。它具备外键支持功能的事务存储引擎，是MySQL的默认事务型引擎，提供了良好的事务管理、崩溃修复能力和并发控制。缺点是其读写处理效率较差，占用的数据空间相对比较大，是MYISAM的2.5倍。InnoDB不仅缓存索引还要缓存真实数据，内存大小对性能有决定性的影响。

  MyISAM存储引擎（MySQL 5.5之前默认的存储引擎），全表锁，拥有较高的执行速度，如果是小型应用，系统以读操作和插入数据为主，只有很少的更新、删除操作，并且对事务的要求没有那么高，则可以选择这个存储引擎。MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高。MyISAM存储引擎的优势在于占用空间小，处理速度块；缺点是不支持事务、行级锁、外键、崩溃后无法灰度。

　　灰度部署原理：将打包后的项目分成两个文件夹，一个是生产环境文件夹，对用户开放，还有一个是灰度文件夹，只有固定IP或者固定测试账号才能访问到。

　　注意：对于InnoDB每一条SQL语言都默认封装成事务，自动提交， 这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；对一个包含外键的InnoDB表转为MYISAM会失败；InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的;InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快.

对比项	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据
自带系统表使用	Y	N
关注点	性能：节省资源、消耗少、简单业务	事务：并发写、事务、更大资源
默认安装	Y	Y
默认使用	N	Y

2.2、Archive引擎

  Archive是归档的意思，仅仅支持插入和查询两种功能（行被插入后不能再修改）。Archive引擎拥有很好的压缩机制，使用zlib压缩库，记录请求的时候实时的进行压缩，经常被作为仓库使用。在创建Archive表的时，存储引擎会创建名称以表名开头的文件，数据文件的扩展名为.ARZ。Archive引擎采用了行级锁该Archive引擎支持AUTO_INCREMENT列属性。AUTO_INCREMENT列可以具有唯一或非唯一索引。尝试在任何其它列上创建索引会导致错误。Archive引擎在 MySQL 5.5 以后支持索引功能。根据英文的测试结果来看，同样的数据量下，Archive表比MyISAM表小大约75%，比支持事务处理的InnoDB表小大约83%。Archive表适合日志和数据采集（归档）类应用；适合存储大量的独立的作为历史记录的数据。拥有很高的插入速度，但是对查询的支持较差。

特征	支持
B树索引	不支持
备份/时间点恢复（在服务器中实现，而不是在存储引擎中）	支持
集群数据库支持	不支持
聚集索引	不支持
压缩数据	支持
数据缓存	不支持
加密数据（加密功能在服务器中实现）	支持
外键支持	不支持
全文检索索引	不支持
地理空间数据类型支持	支持
地理空间索引支持	不支持
哈希索引	不支持
索引缓存	不支持
锁粒度	行锁
MVCC	不支持
存储限制	没有任何限制
交易	不支持
更新数据字典的统计信息	支持

2.3、Blackhole引擎

  Blackhole引擎没有任何存储机制，它会丢弃所有插入的数据，不做任何保存。但服务器会记录Blackhole表的日志，索引可以用于复制数据到备份，或者简单的记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。

2.4、CSV引擎

  CSV引擎可以将普通的CSV文件作为MySQL的表来处理，可以作为一种数据交换的机制，但不支持索引。CSV引擎对于数据快速导入、导出是有明显优势的；CSV存储的数据直接可以在操作系统里，用文本编译器，或者excel读取；创建CSV表时，服务器会创建一个纯文本数据文件，其名称以表名开头并带有.CSV扩展名。当你将数据存储到表中时，存储引擎将其以逗号分隔值格式保存到数据文件中。

2.5、Memory引擎

  Memory全表锁，采用的逻辑介质是内存，响应速度很快，但会占用和数据量成正比的内存空间且数据在mysql重启时会丢失，mysql守护线程崩溃的时候数据会丢失，另外，要求存储的数据长度不变的格式。Memory同时支持哈希（HASH）索引和B+树索引。哈希索引相等的比较快，但是对于范围的比较慢很多。默认使用哈希（HASH）索引，其速度要比使用B型树（BTREE）索引快，但不适用于精确查找，主要用于那些内容变 化不频繁的代码表。如果希望使用B树索引，可以在创建索引时选择使用。Memory表的大小是受到限制的。表的大小主要取决于两个参数，分别是max_rows和max_heap_table_size。其中，max_rows可以在创建表时指定；max_heap_table_size的大小默认是16MB，可以按需要进行扩大。Memory数据文件于索引文件分开存储，每一个基于Memory存储引擎的表实际上对应一个磁盘文件，该文件的文件名与表名相同，类型为frm类型，该文件中只存储表的结构，而其数据文件都是存储在内存中的，这样有利于数据的快速处理，提高整个表的处理效率；缺点是其数据易丢失，声明周期短。Memory表至少比MyISAM表要快一个数量级。

2.5、Federated引擎

  Federated索引是访问其它MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。

2.6、Merge引擎

  管理多个MyISAM表构成的表集合。

2.7、NDB引擎

  也叫做NDB Cluster存储引擎，主要用于MySQL Cluster分布式集群环境，类似于Oracle的RAC集群。

2.8、引擎对比

特点	MyISAM	InnoDB	Memory	Merge	NDB
存储限制	有	64TB	有	没有	有
事务安全		支持
锁机制	表锁	行锁	表锁	表锁	行锁
B树索引	支持	支持	支持	支持	支持
哈希索引			支持		支持
全文索引	支持
集群索引		支持
数据索引		支持	支持		支持
索引缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据	支持	支持	支持
数据可压缩	支持
空间使用	低	高	N/A	低	低
内存使用	低	高	中等	低	高
批量插入的速度	高	低	高	高	高
支持外键		支持