MYSQL---存储引擎

MYSQL—存储引擎

一、基本概念

存储引擎：表的类型以及表在计算机上的存储方式。
存储引擎的概念存在于MySql中，Oracle中只有OLTP和OLAP模式的区分。不同的存储引擎决定了MySql数据库表可以用不同的方式存储。我们可以根据数据的特点来选择不同的存储引擎。

查询数据库用到的存储引擎：SHOW ENGINES

MySql支持8中存储引擎，其中最主要的有2个：InnoDB、MyISAM。

查询数据表存放的目录：
查询语句：show VARIABLES like ‘%data%’,其中datadir对应的就是数据存放本地的目录

二、InnoDB存储引擎

1. InnoDB提供事务处理、回滚、崩溃修复能力和多版本并发控制的事务安全。在MySql5.5之后数据库默认是InnoDB。它是MySql上第一个提供外键约束的表引擎。而且InnoDB对事务处理的能力，也是其他引擎不能比拟的。
2. InnoDB总支持外键（POREIGN KEY）。
3. 创建的表结构存储在.frm文件中。数据和索引存储在innodb_data_home_dir和innodb_data_file_path定义的表空间中。
4. 提供行级锁
   5.允许AUTO_INCREMENT，自动增长的行不能为空，并且值必须唯一

InnoDB优势是提供了良好的事务处理、崩溃修复能力和并发控制。
缺点：读写效率较差，占用的数据空间相对较大。
支持事务、行级锁，外键，聚集索引。适用于更新密集的表，容灾性也较好。

innodb是B+tree模式
innodb实际是I/O操作，采用的是MVCC来支持高并发

三、MyISAM存储引擎

1.不支持事务，不支持外键，访问速度快，对数据完整性没有要求，或者以select或者insert为主的应用基本可以使用这个引擎来创建表
MyISAM在磁盘上存储成3个文件，其中文件名和表名相同，扩展名不同：
.frm文件(存储表定义)
.MYD文件(MYData，存储数据)
.MYI文件(MYIndex，存储索引)
MyISAM优势在于占用空间小，处理速度快，缺点是不支持事务的完整性和并发性
2.支持表级锁，适用于select或者insert密集的表

支持3种不同的存储格式：静态表、动态表、压缩表
==静态表：==表中的字段都是非变长字段，这样每个记录都是固定长度的，优点存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多（因为存储时会按照列的宽度定义补足空格）ps：在取数据的时候，默认会把字段后面的空格去掉，如果不注意会把数据本身带的空格也会忽略。
==动态表：==记录不是固定长度的，这样存储的优点是占用的空间相对较少；缺点：频繁的更新、删除数据容易产生碎片，需要定期执行OPTIMIZE TABLE或者myisamchk-r命令来改善性能
==压缩表：==因为每个记录是被单独压缩的，所以只有非常小的访问开支

四、Memory存储引擎

使用memory存储引擎的出发点是：速度
为了得到最快的响应时间，采用的逻辑存储介质是系统内存，并且默认是HASH索引。虽然在内存中存储表数据确实会提供很高的性能，==但是当mysql守护进程崩溃时，所有的memory数据都会丢失。==获得速度的同时也存在一定的丢失数据风险。
memory存储数据表的数据要求是长度不变的类型，所以不能使用blob或者text这样长度可变的类型
其中vachar是数据长度可变的类型，但因为它在mysql中当做是长度不变的char，所以也可以使用。
一般在以下情况下使用memory存储引擎：
1）目标数据小，而且被非常频繁的访问。在内存中访问，会消耗内存，设置参数：max_heap_table_size控制表的大小
2）临时数据，要求必须立即使用，可以放在内存表中
3）存储在memory数据丢失，不会对应用服务造成任何损失
4）memory表的生命周期很短，一般是一次性的

MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引，两种不同类型的索引有其不同的使用范围
Hash索引优点：
Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。
Hash索引缺点： 那么不精确查找呢，也很明显，因为hash算法是基于等值计算的，所以对于“like”等范围查找hash索引无效，不支持；
Memory类型的存储引擎主要用于哪些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果，。对存储引擎为memory的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。

五、Merge存储引擎
merge存储引擎实质上是几个相同myisam表的聚合器，merge表中并没有数据，对merge表进行的所有操作，实际上是对内部的myisam表进行操作。
应用场景：
对于服务器日志这种信息，一般常用的存储策略是将数据分成很多表，每个名称与特定的时间端相关。例如：可以用12个相同的表来存储服务器日志数据，每个表用对应各个月份的名字来命名。当有必要基于所有12个日志表的数据来生成报表，这意味着需要编写并更新多表查询，以反映这些表中的信息。与其编写这些可能出现错误的查询，不如将这些表合并起来使用一条查询，之后再删除Merge表，而不影响原来的数据，删除Merge表只是删除Merge表的定义，对内部的表没有任何影响。

六、Archive存储引擎

Archive是归档的意思，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能。在MySQL 5.5版以前，Archive是不支持索引，但是在MySQL 5.5以后的版本中就开始支持索引了。Archive拥有很好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用。

以zlib对表数据进行压缩，磁盘I/O更少
数据存储在ARZ为后缀的文件中
特点：只支持insert和select操作，只允许在自增ID列上加索引

七、InnoDB和Myisam区别

1.innodb支持事务，MyISAM不支持，对于innodb每一条sql语言都默认封装成事务，自动提交，会影响速度
2.innodb是聚集索引，数据文件和索引是绑在一起的，必须要有主键；通过主键索引效率高，但是辅助索引需要两次查询，先查询到主键，然后通过主键查询到数据；
MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的，所以查询速度比较快
3.innodb不保存表的具体行数，执行select count(*) from table时需要全表扫描；
而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快
4.innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高

八、如何选择存储引擎

存储引擎的对比：

特性	InnoDB	Myisam	Memory
事务安全	支持	无	无
存储限制	64TB	有	有
空间使用	高	低	低
内存使用	高	低	高
插入数据的速度	低	高	高
对外键的支持	支持	无	无