MySQL存储引擎

一：存储引擎概念

　　存储引擎其实就是对于数据库文件的一种存取机制，如何实现存储数据，如何为存储的数据建立索引以及如何更新，查询数据等技术实现的方法。MySQL中的数据用各种不同的方式存储在文件（或内存）中，每一种存储的方式都使用不同的存储机制，索引技巧等，最终提供广泛的不同功能和能力。在MySQL中将这些不同的技术及配套的相关功能称为存储引擎。

1：MySQL逻辑架构

这里主要注意引擎层，因为本文主要围绕索引展开，具体的MySQL逻辑架构参考：MySQL逻辑架构及执行过程
第一层：连接层，所包含的服务并不是MySQL所独有的技术。它们都是服务于C/S程序或者是这些程序所需要的：连接处理，身份验证，安全性等。
第二层：服务层，这是MySQL的核心部分。通常叫做SQL Layer。在MySQL数据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权
       限判断，sql解析，行计划优化，query cache的处理以及所有内置的函数(如日期，时间，数学运算，加密)等等。各个存储引擎提供的
       功能都集中在这一层，如存储过程，触发器，视图等。
第三层：引擎层。通常叫做StorageEngine Layer，也就是底层数据存取操作实现部分，由多种存储引擎共同组成。它们负责存储和获取所有
       存储在MySQL中的数据。就像Linux众多的文件系统一样。每个存储引擎都有自己的优点和缺陷。服务器是通过存储引擎API来与它们交
       互的。这个接口隐藏了各个存储引擎不同的地方。对于查询层尽可能的透明。这个API包含了很多底层的操作。如开始一个事物，或者取
       出有特定主键的行。存储引擎不能解析SQL，互相之间也不能通信。仅仅是简单的响应服务器的请求。
第四层：存储层。将数据存储在文件系统上，并完成与存储引擎的交户。

二：MySQL查询与修改引擎

1：查询支持的存储引擎

可以通过SQL命令查看支持的存储引擎：SHOW engines;
    +--------------------+---------+------------------+--------------+------+------------+
    | Engine             | Support | Comment          | Transactions | XA   | Savepoints |
    +--------------------+---------+------------------+--------------+------+------------+
    | ndbcluster         | NO      | Clustered, fau...| NULL         | NULL | NULL       |
    | FEDERATED          | NO      | Federated MySQ...| NULL         | NULL | NULL       |
    | MEMORY             | YES     | Hash based, st...| NO           | NO   | NO         |
    | InnoDB             | DEFAULT | Supports trans...| YES          | YES  | YES        |
    | PERFORMANCE_SCHEMA | YES     | Performance Sc...| NO           | NO   | NO         |
    | MyISAM             | YES     | MyISAM storage...| NO           | NO   | NO         |
    | ndbinfo            | NO      | MySQL Cluster ...| NULL         | NULL | NULL       |
    | MRG_MYISAM         | YES     | Collection of ...| NO           | NO   | NO         |
    | BLACKHOLE          | YES     | /dev/null stor...| NO           | NO   | NO         |
    | CSV                | YES     | CSV storage en...| NO           | NO   | NO         |
    | ARCHIVE            | YES     | Archive storag...| NO           | NO   | NO         |
    +--------------------+---------+------------------+--------------+------+------------+
字段说明
  Engine：存储引擎名称
  Support：是否支持该引擎以及该引擎是否为默认存储引擎，YES表示支持，NO表示不支持，DEFAULT表示默认引擎
  Comment：存储引擎的简单介绍
  Transactions：表示该引擎是否支持事务
  XA：说明该存储引擎是否支持分布事务
  Savepoints：说明该存储引擎是否支持部分事务回滚

2：修改系统默认的存储引擎

查询会话级别的默认存储引擎：
    SHOW SESSION VARIABLES LIKE 'default_storage_engine';
    SELECT @@SESSION.default_storage_engine;
      +----------------------------------+
      | @@SESSION.default_storage_engine |
      +----------------------------------+
      | InnoDB                           |
      +----------------------------------+
Ⅰ：临时修改全局（系统级别）的存储引擎：
  SET @@GLOBAL.default_storage_engine=MyISAM;
  SELECT @@GLOBAL.default_storage_engine;
    +---------------------------------+
    | @@GLOBAL.default_storage_engine |
    +---------------------------------+
    | MyISAM                          |
    +---------------------------------+
Ⅱ：临时修改会话级别的存储引擎：
    SET @@SESSION.default_storage_engine=MyISAM;
    SELECT @@SESSION.default_storage_engine;
      +---------------------------------+
      | @@SESSION.default_storage_engine|
      +---------------------------------+
      | MyISAM                          |
      +---------------------------------+
Ⅲ：修改my.cnf配置文件（全局修改，需要重启MySQL服务）：
  [mysqld]
  default-storage-engine=引擎名字
  使用vim修改后务必重启MySQL：systemctl.mysqld

3：设置表的存储引擎

　　存储引擎是负责对表中的数据进行提取和写入工作的，我们可以为不同的表设置不同的存储引擎，也就是说不同的表可以有不同的物理存储结构，不同的提取和写入方式。

创建表基本语法：
CREATE TABLE [IF NOT EXISTS] [数据库名.]表名(
           字段1, 数据类型 [约束条件] [默认值] [字段备注信息],
           字段1, 数据类型 [约束条件] [默认值] [字段备注信息],
           字段1, 数据类型 [约束条件] [默认值] [字段备注信息],
           ...
           [表约束条件]
      )[表字符集] [表校对集] [表存储引擎];
创建表示例：
    CREATE DATABASE dbtest1;
    CREATE TABLE IF NOT EXISTS dbtest1.student(
          sid INT(4) PRIMARY KEY AUTO_INCREMENT COMMENT "主键ID",
          sname VARCHAR(6) NOT NULL COMMENT "姓名",
          ssex ENUM('男','女') DEFAULT '男' COMMENT "性别",
          sage INT(4) COMMENT "年龄"
      )CHARACTER SET UTF8 COLLATE utf8_general_ci ENGINE INNODB;  
查询创建表的存储引擎：
    SHOW CREATE TABLE dbtest1.student\G
    *************************** 1. row ***************************
           Table: student
    Create Table: CREATE TABLE `student` (
      `sid` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
      `sname` varchar(6) NOT NULL COMMENT '姓名',
      `ssex` enum('男','女') DEFAULT '男' COMMENT '性别',
      `sage` int DEFAULT NULL COMMENT '年龄',
      PRIMARY KEY (`sid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
    注：UTF8默认就是utf8mb3
    注：创建数据库时是不存在存储引擎设置的
　　补充：
　　　　也可以使用查询表状态SQL语句：SHOW TABLE STATUS LIKE '表名';
　　　　严谨点就是查询某一数据库下的某一表：SHOW TABLE STATUS FROM 数据库名 WHERE name = '表名';
修改表存储引擎：
　　ALTER TABLE dbtest1.student ENGINE MyISAM;

三：部分存储引擎介绍

1：InnoDB引擎（重要）

　　InnoDB是一个事务型存储引擎，提供了对数据库ACID事务的支持，并实现了SQL标准的四种隔离级别，具有行级锁定（这一点说明锁的粒度小，在写数据时，不需要锁住整个表，因此适用于高并发情形）及外键支持（所有数据库引擎中仅有它支持外键）；该引擎的设计目标便是处理大容量数据的数据库系统，MySQL在运行时InnoDB会在内存中建立缓冲池，用于缓存数据及索引。

具体说明：
    ①：MySQL从3.23.34开始就包含InnoDB存储引擎。MySQL5.5及之后版本，默认采用InnoDB引擎。
    ②：InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。
        可以确保事务的完整提交(Commit)和回滚(Rollback)。
    ③：除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎。
    ④：除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。
    ⑤：InnoDB的数据文件结构不同：参考MySQL数据目录
    ⑥：InnoDB是为处理巨大数据量的最大性能设计
    ⑦：对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引。
    ⑧：MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
缺点：
    ①：该引擎不支持FULLTEXT类型的索引；
    ②：没有保存表的行数，在执行SELECT COUNT(*) FROM表名时，需要遍历扫描全表，而MyISAM支持保存表行数；
适用场景：
    ①：经常需要更新的表，适合处理多重并发的更新请求；
    ②：支持事务和外键约束；
    ③：可以从灾难中恢复（通过bin-log日志等）
    ④：支持自动增加列属性auto_increment

2：MyISAM引擎

　　MyISAM引擎是MySQL主流引擎之一，但它相比起InnoDB，没有提供对数据库事务的支持，不支持细粒度的锁（行锁）及外键，当表INSERT与UPDATE时需要锁定整个表，因此效率会低一些，在高并发时可能会遇到瓶颈；但MyISAM引擎独立与操作系统，可以在Windows及Linux上使用，这说明可以轻松的将其从Windows移植到Linux上，每当我们建立一个MyISAM引擎表时，就会在本地磁盘创建三个文件，文件名就是表名（如创建tb_demo表）：tb_demo.frm存储表定义、tb_demo.MYD存储数据、tb_demo.MYI存储索引。

具体说明：
    ①：MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务、行级锁、外键，
        但有个严重问题就是崩溃后无法安全恢复。
    ②：MySQL5.5版本之前默认的存储引擎就是MyISAM。
    ③：主要优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主则使用MyISAM。
    ④：针对数据统计有额外的常数存储。故而 COUNT(*) 的查询效率很高。
    ⑤：MyISAM的数据文件结构不同：参考MySQL数据目录
    ⑥：该存储引擎下主要以读为主的业务场景
缺点：
    ①：若数据库服务发生宕机后导致表损坏则不能安全恢复数据。
适用场景：
    ①：MyISAM极度强调快速读取
    ②：MyIsam表中自动存储了表的行数，需要时直接获取即可
    ③：适用于不需要事物支持、外键功能、及需要对整个表加锁的情形

3：Memory（Heap）引擎

　　Memory采用的逻辑介质是内存，每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，它的数据是放在内存中的，并且默认使用HASH索引。但是当mysqld守护进程崩溃或关闭的时候，数据会丢失。HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MYISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。Memory同时支持散列索引和B树索引，B树索引可以使用部分查询和通配查询，也可以使用<,>和>=等操作符方便数据挖掘，散列索引相等的比较快但是对于范围的比较慢很多。

具体说明：
    ①：Memory同时支持哈希（HASH）索引和B+树索引。
    ②：Memory表至少比MyISAM表要快一个数量级。
    ③：Memory表的大小是受到限制的。表的大小主要取决于两个参数，分别是max_rows和max_heap_table_size。
        其中max_rows可以在创建表时指定；max_heap_table_size的大小默认为16MB，可以按需要进行扩大。
    ③：数据文件与索引文件分开存储。
    查询表的大小：
    SHOW SESSION VARIABLES LIKE 'max_heap_table_size';
    +---------------------+----------+
    | Variable_name       | Value    |
    +---------------------+----------+
    | max_heap_table_size | 16777216 |
    +---------------------+----------+
    创建测试：
        CREATE TABLE t1(id INT PRIMARY KEY, c INT) engine=Memory MAX_ROWS=3;
缺点：
    ①：要求存储的数据是数据长度不变的格式，Blob和Text类型数据不可用（长度不固定）
    ②：数据易丢失，生命周期短；用完表格后表格便被删除
适用场景：
    ①：那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果
    ②：目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，如果太大的数据会造成内存溢出。
    ③：数据是临时的，而且必须立即能取出用到，于是可存放在内存中
    ④：存储在Memory表中的数据如果突然间丢失的话也没有太大的关系

四：MyISAM和InnoDB对比

　　MySQL5.5之前的默认存储引擎是MyISAM(MyISAM是由早期的ISAM改良的)，5.5之后改为了InnoDB。

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

.