MySql引擎介绍

最新推荐文章于 2020-08-16 20:25:26 发布

0箫逝

最新推荐文章于 2020-08-16 20:25:26 发布

阅读量176

点赞数 1

分类专栏： Mysql温故知新文章标签： Mysql

本文链接：https://blog.csdn.net/a867565870/article/details/84957677

版权

Mysql温故知新专栏收录该内容

2 篇文章 0 订阅

订阅专栏

引擎简介

发动机(引擎)是汽车的核心，汽车发动机各种各样，目前常见的汽车发动机机型有：直列发动机； V型发动机； W型发动机等... 各种发动机结构各有自己的优缺点，这也成了用户选择汽车的一个最重要因素...

为了满足不同的需求，MySQL数据库也为我们提供了不同的引擎，可以让我们根据自己的需求和x场景选择不同的引擎来达到不同的效果。目前mysql支持的引擎主要是以下四个： ① MyISAM存储引擎 ② InnoDB存储引擎 ③ MEMORY存储引擎 ④ MERGE存储引擎四种引擎各有自己的优缺点，结合日常生产，①和②引擎是我们平时最常用的引擎，接下来我们将会着重对这两个引擎做进一步的了解。

四大引擎

MyISAM是MySQL的默认数据库引擎（5.5版之前），由早期的ISAM（Indexed Sequential Access Method：有索引的顺序访问方法）所改良。虽然性能极佳，但却有一个缺点：不支持事务处理（transaction）。 InnoDB，是MySQL的数据库引擎之一，为MySQL AB发布binary的标准之一。

InnoDB由Innobase Oy 公司所开发，2006年五月时由甲骨文公司并购。与传统的ISAM与MyISAM相比，InnoDB的最大特色就是支持了ACID（详解见PPT后文）兼容的事务（Transaction）功能。

MEMORY是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。这些特性与前面的两个很不同。该种引擎的的优点是读写速度很快，但是缺点也很明显，就是需要服务器有足够的内存来维持MEMORY存储引擎的表的使用，并且由于不支持事物安全，所以安全性并不高。另外，创建临时表不会持久化，有数据丢失风险。

MERGE存储引擎把一组MyISAM数据表当做一个逻辑单元来对待，让我们可以同时对他们进行查询。构成一个MERGE数据表结构的各成员MyISAM数据表必须具有完全一样的结构。每一个成员数据表的列必须按照同样的顺序定义同样的名字和类型，索引也必须按照同样的顺序和同样的方式定义。如我们创建了一个MERGE为引擎的日志表(后边UNION中log_*的表结构必须一样)： CREATE TABLE log_merge(dt DATETIME NOT NULL, info VARCHAR(100) NOT NULL, INDEX(dt)) ENGINE = MERGE UNION = (log_2004, log_2005, log_2006, log_2007); ENGINE选项的值必须是MERGE，UNION选项列出了将被收录在这个MERGE表里得各有关表。

MylSAM与InnoDB对比

1) 事务支持 MyISAM不支持事务(非事务安全型)。 InnoDB支持。InnoDB的AUTOCOMMIT默认是打开的，即每条SQL语句会默认被封装成一个事务，自动提交，这样会影响速度，所以最好是把多条SQL语句显示放在begin和commit之间，组成一个事务去提交。

2) 存储结构 MyISAM：每个MyISAM在磁盘上存储成三个文件。同一文件名，不同扩展名。 .frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。 InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

3) 存储空间 MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。 InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

4) 可移植性、备份及恢复 MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。 InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。

5) 事务支持 MyISAM：强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。 InnoDB：提供事务支持事务，外部键等高级数据库功能。具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

6) AUTO_INCREMENT MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列。 InnoDB：InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。

7) 表锁差异 MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。 InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的性能。但是InnoDB 的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。 InnoDB支持数据行锁定，而MyISAM不支持行锁定，只支持锁定整个表。即MyISAM同一个表上的读锁和写锁是互斥的，MyISAM并发读写时如果等待队列中既有读请求又有写请求，默认写请求的优先级高，即使读请求先到，会造成查询进程堵塞，所以MyISAM不适合于有大量查询和修改并存的情况。

8) 全文索引 MyISAM：支持(FULLTEXT类型的)全文索引 InnoDB：MySql5.7以上支持(FULLTEXT类型的)全文索引，MySql5.7以下可以使用sphinx插件支持全文索引。

9) 表主、外键 MyISAM：允许没有任何索引和主键的表存在。不支持外键。 InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个用户不可见的6字节的主键。InnoDB的主键范围更大，最大是MyISAM的2倍。支持外键。 10) 表的具体行数 MyISAM：保存有表的总行数，如果select count(*) from table;会直接取出出该值。 InnoDB：没有保存表的总行数(只能遍历)，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。

11) CURD操作 MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。 InnoDB：如果你的数据执行大量的INSERT或UPDATE，使用InnoDB表性能更佳。 DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

12) 查询效率没有where的count(*)使用MyISAM要比InnoDB快得多。MyISAM内置了一个计数器，count(*)时直接从计数器中读，而InnoDB必须扫描全表。所以在InnoDB上执行count(*)时一般要伴随where，且where中要包含主键以外的索引列。为什么这里特别强调“主键以外”？因为InnoDB中主键是和raw data存放在一起的，而其他索引列则是单独存放，然后有个指针指向primary key。所以只是count(*)的话使用其他索引列进行where扫描更快。MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。