MySql的多存储引擎架构, 默认的引擎InnoDB与 MYISAM的区别(滴滴 阿里)

1、存储引擎是什么?

　　MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。MySql默认配置了许多不同的存储引擎，可以预先设置或者在MySql服务器中启用。

　　2、MYSQL支持的数据引擎

　　MyISAM：最大的特点是不支持事务，默认fulltext 全文索引；(阿里面试)默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。

　　InnoDB：最大的特点是支持事务处理，默认B+ Tree索引；具有众多特性，包括ACID事务支持,InnoDB默认加锁方式是next-key 锁。。

　　BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性。

　　Memory：默认hash索引；将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。

　　Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。

　　Archive：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。

　　Federated：能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。

　　Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。

　　Other：其他存储引擎包括CSV(引用由逗号隔开的用作数据库表的文件)，Blackhole(用于临时禁止对数据库的应用程序输入)，以及Example引擎(可为快速创建定制的插件式存储引擎提供帮助)。

 3、   MyISAM引擎与InnoDB的区别（百度面试）：

1、MyIASM是非事务安全的，不支持事务，而InnoDB是事务安全的，支持事务这是最重要的区别(百度面试)

2、MyIASM锁的粒度是表级的，而InnoDB支持行级锁

3、MyIASM支持全文类型索引，而InnoDB不支持全文索引

4、MyIASM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyIASM

5、MyIASM表保存成文件形式，跨平台使用更加方便　

Innodb引擎 

　　Mysql中默认的是MyISAM数据引擎，可惜此引擎不支持事务处理，我们需要将默认的数据引擎改为InnoDB。其中InnoDB和BerkeleyDB支持事务处理，只是默认的情况下都是被disable的。所有的引擎里面，InnoDB性能最强大，算是商业级的。启动InnoDB引擎的方法如下：

　　1)关闭mysql的服务

　　2)修改my.ini

　　将default-storage-engine=INNODB前的注释(#)去掉

　　将skip-innodb这行注释(加上#)

　　3)保存后重启mysql服务 

Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别。该引擎还提供了行级锁和外键约束， 

 其实innodb支持三种锁：Mysql间隙锁最大的特点是在repeatable read隔离级别下，使用间隙锁来防止幻读。(百度面试)

参考：Mysql 间隙锁原理，以及Repeatable Read隔离级别下可以防止幻读原理(百度)

(a)记录锁（也叫行锁），

(b)间隙锁，

(c)next-key锁统统属于排他锁。

行锁
记录锁其实很好理解，对表中的记录加锁，叫做记录锁，简称行锁。

生活中的间隙锁
编程的思想源于生活，生活中的例子能帮助我们更好的理解一些编程中的思想。
生活中排队的场景，小明，小红，小花三个人依次站成一排，此时，如何让新来的小刚不能站在小红旁边，这时候只要将小红和她前面的小明之间的空隙封锁，将小红和她后面的小花之间的空隙封锁，那么小刚就不能站到小红的旁边。
这里的小红，小明，小花，小刚就是数据库的一条条记录。
他们之间的空隙也就是间隙，而封锁他们之间距离的锁，叫做间隙锁。 

next-key锁
next-key锁其实包含了记录锁和间隙锁，即锁定一个范围，并且锁定记录本身，InnoDB默认加锁方式是next-key 锁。

 

MyIASM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。不过和Innodb不同，MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。

应用场景：

1、MyIASM管理非事务表，提供高速存储和检索以及全文搜索能力，如果再应用中执行大量select操作，应该选择MyIASM
2、InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，应该选择InnoDB

　　这些区别能够影响应用程序的性能和功能，因此你必须针对业务类型来选择合适的引擎，才能最大的发挥MySQL的性能优势。 

 

 

 

支持多种存储引擎是众所周知的MySQL特性，也是MySQL架构的关键优势之一。如果能够理解MySQL Server与存储引擎之间是怎样通过API交互的，将大大有利于理解MySQL的核心基础架构。本文将首先介绍MySQL的整体逻辑架构，然后分析MySQL的存储引擎API并介绍如何编写自己的MySQL存储引擎。

MySQL逻辑架构

MySQL作为一个大型的网络程序、数据管理系统，架构非常复杂。下图大致画出了其逻辑架构。

MySQL由以下几部分组成：

  1. 连接池组件。
  2. 管理服务和工具组件。
  3. SQL接口组件。
  4. 查询分析器组件。
  5. 优化器组件。
  6. 缓冲（Cache）组件。
  7. 插件式存储引擎。
  8. 物理文件。

Connectors

MySQL首先是一个网络程序，其在TCP之上定义了自己的应用层协议。所以要使用MySQL，我们可以编写代码，跟MySQL Server建立TCP连接，之后按照其定义好的协议进行交互。当然这样比较麻烦，比较方便的办法是调用SDK，比如Native C API、JDBC、PHP等各语言MySQL Connector，或者通过ODBC。但通过SDK来访问MySQL，本质上还是在TCP连接上通过MySQL协议跟MySQL进行交互。

Connection Management

每一个基于TCP的网络服务都需要管理客户端链接，MySQL也不例外。MySQL会为每一个连接绑定一个线程，之后这个连接上的所有查询都在这个线程中执行。为了避免频繁创建和销毁线程带来开销，MySQL通常会缓存线程或者使用线程池，从而避免频繁的创建和销毁线程。

客户端连接到MySQL后，在使用MySQL的功能之前，需要进行认证，认证基于用户名、主机名、密码。如果用了SSL或者TLS的方式进行连接，还会进行证书认证。

SQL Interface

MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种SQL语言接口。

Parser

MySQL会解析SQL查询，并为其创建语法树，并根据数据字典丰富查询语法树，会验证该客户端是否具有执行该查询的权限。创建好语法树后，MySQL还会对SQl查询进行语法上的优化，进行查询重写。

Optimizer

语法解析和查询重写之后，MySQL会根据语法树和数据的统计信息对SQL进行优化，包括决定表的读取顺序、选择合适的索引等，最终生成SQL的具体执行步骤。这些具体的执行步骤里真正的数据操作都是通过预先定义好的存储引擎API来进行的，与具体的存储引擎实现无关。

Caches & Buffers

MySQL内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条Select语句的执行结果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过程了。

Pluggable Storage Engine

存储引擎的具体实现，这些存储引擎都实现了MySQl定义好的存储引擎API的部分或者全部。MySQL可以动态安装或移除存储引擎，可以有多种存储引擎同时存在，可以为每个Table设置不同的存储引擎。存储引擎负责在文件系统之上，管理表的数据、索引的实际内容，同时也会管理运行时的Cache、Buffer、事务、Log等数据和功能。

MySQL 5.7.11默认支持的存储引擎如下：

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

File System

所有的数据，数据库、表的定义，表的每一行的内容，索引，都是存在文件系统上，以文件的方式存在的。当然有些存储引擎比如InnoDB，也支持不使用文件系统直接管理裸设备，但现代文件系统的实现使得这样做没有必要了。

在文件系统之下，可以使用本地磁盘，可以使用DAS、NAS、SAN等各种存储系统。

存储引擎API

MySQL定义了一系列存储引擎API，以支持插件式存储引擎架构。API以Handler类的虚函数的方式存在，可在代码库下的./sql/handler.h中查看详细信息，可在handler类的注释中看到描述：


/**
  The handler class is the interface for dynamically loadable
  storage engines. Do not add ifdefs and take care when adding or
  changing virtual functions to avoid vtable confusion

  Functions in this class accept and return table columns data. Two data
  representation formats are used:
  1. TableRecordFormat - Used to pass [partial] table records to/from
     storage engine

  2. KeyTupleFormat - used to pass index search tuples (aka "keys") to
     storage engine. See opt_range.cc for description of this format.

  TableRecordFormat
  =================
  [Warning: this description is work in progress and may be incomplete]
  The table record is stored in a fixed-size buffer:

    record: null_bytes, column1_data, column2_data, ...

  //篇幅原因，略去部分内容。

*/
class handler :public Sql_alloc
{
    //篇幅原因，不列出具体代码。读者可直接在源码文件./sql/handler.h中找到具体内容。
}

下面我将分类描述部分存储引擎API。

创建、打开和关闭表

通过函数create来创建一个table：


/**
  *name：要创建的表的名字
  *from：一个TABLE类型的结构，要创建的表的定义，跟MySQL Server已经创建好的tablename.frm文件内容是匹配的
  *info：一个HA_CREATE_INFO类型的结构，包含了客户端输入的CREATE TABLE语句的信息
*/

int create(const char *name, TABLE *form, HA_CREATE_INFO *info);

通过函数open来打开一个table：


/**
  mode包含以下两种
  O_RDONLY  -  Open read only
  O_RDWR    -  Open read/write
*/
int open(const char *name, int mode, int test_if_locked);

通过函数close来关闭一个table：


int close(void);

对表加锁

当客户端调用LOCK TABLE时，通过external_lock函数加锁：


int ha_example::external_lock(THD *thd, int lock_type)

全表扫描


//初始化全表扫描
virtual int rnd_init (bool scan);

//从表中读取下一行
virtual int rnd_next (byte* buf);

通过索引访问table内容


//使用索引前调用该方法
int ha_foo::index_init(uint keynr, bool sorted) 


//使用索引后调用该方法
int ha_foo::index_end(uint keynr, bool sorted)

//读取索引第一条内容
int ha_index_first(uchar * buf);

//读取索引下一条内容
int ha_index_next(uchar * buf);

//读取索引前一条内容
int ha_index_prev(uchar * buf);

//读取索引最后一条内容
int ha_index_last(uchar * buf);

//给定一个key基于索引读取内容
int index_read(uchar * buf, const uchar * key, uint key_len,
                         enum ha_rkey_function find_flag)

事务处理


//开始一个事务
int my_handler::start_stmt(THD *thd, thr_lock_type lock_type)

//回滚一个事务
int (*rollback)(THD *thd, bool all); 

//提交一个事务
int (*commit)(THD *thd, bool all);

如何编写自己的存储引擎

在MySQL的官方文档上，有对于编写自己的存储引擎的指导文档，链接如下。

作为编写自己存储引擎的开始，你可以查看MySQL源码库中的一个EXAMPLE存储引擎，它实现了必须要实现的存储引擎API，可以通过复制它们作为编写我们自己存储引擎的开始：


sed -e s/EXAMPLE/FOO/g -e s/example/foo/g ha_example.h > ha_foo.h
sed -e s/EXAMPLE/FOO/g -e s/example/foo/g ha_example.cc > ha_foo.cc

 

posted @ 2017-05-19 14:25  aspirant  阅读(2294)  评论(0)  编辑  收藏  举报