mysql常用引擎的基本特性

mysql常用的三种引擎

 

      (1)  MyISAM存储引擎

            不支持事务、也不支持外键，支持全文索引优势是访问速度快，对事务完整性没有 要求或者以select，insert为主的应用基本上可以用这个引擎来创建表支持3种不同的存储格式，分别是：静态表；动态表；压缩表

             静态表：表中的字段都是非变长字段，这样每个记录都是固定长度的，优点存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多（因为存储时会按照列的宽度定义补足空格）ps：在取数据的时候，默认会把字段后面的空格去掉，如果不注意会把数据本身带的空格也会忽略。

            动态表：记录不是固定长度的，这样存储的优点是占用的空间相对较少；缺点：频繁的更新、删除数据容易产生碎片，需要定期执行OPTIMIZE TABLE或者myisamchk-r命令来改善性能

            压缩表：因为每个记录是被单独压缩的，所以只有非常小的访问开支

           MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的

 

         物理文件结构

        每一个表在MyISAM存储引擎中都以三个以表名命名的物理文件构成。

        (1)任何存储引擎都不可或缺的存放表结构定义的.frm（Form）文件 

        (2)存放表数据的.MYD文件（My Data）

        (3)存放索引数据的.MYI文件（My Index）

 

        这里着重说一下.MYD文件的文件存放格式，分为静态固定长度、动态可变长度、压缩三种格式。表数据是默认不压缩的，在创建表时，可以通过ROW_FORMAT设置默认为压缩{COMPRESSED|DEFAULT}，也可以通过MyISAMpack工具进行压缩。

       当表数据没有被压缩时，静态与动态的区分就与表中定义的字段类型有关了。当出现VARCHAR等可变长度类型时，这个表就是动态的；当没有出现任何可变长类型时，这个表就是静态的。

     

    (2)  InnoDB存储引擎*

      该存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM引擎，写的处理效率会差一些，并且会占用更多的磁盘空间以保留数据和索引.不支持全文索引.支持自动增长列，支持外键约束.在InnoDB存储引擎表中，每张表都有个主键，

      InnoDB特性:

      1.InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败

      2.InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务.

      3.InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大.

      4.InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快.如果语句后面加上where条件,那么两个引擎就没什么区别了.

       5.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。

       6.InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表

      7.如果在创建表时没有显式地定义主键（Primary Key），则InnoDB存储引擎会按如下方式选择或创建主键。首先表中是否有非空的唯一索引（Unique NOT NULL），如果有，则该列即为主键。不符合上述条件，InnoDB存储引擎自动创建一个6个字节大小的指针。

 

     物理文件结构

      .frm与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等。 
      索引和数据文件保存在.ibd文件和.ibdata文件： 
      这两种文件都是存放innodb数据的文件，之所以用两种文件来存放innodb的数据，是因为innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。

        独享表空间存储方式使用.ibd文件，并且每个表一个ibd文件，共享表空间存储方式使用.ibdata文件，所有表共同使用一个ibdata文，觉得使用哪种方式的参数在mysql的配置文件中 innodb_file_per_table

      如果是共享表空间，通过innodb_data_file_path指定后，每次增加数据文件后必须停机重启才能生效，很不方便.

 

    

      逻辑存储结构

       InnoDB存储引擎的逻辑存储结构和Oracle大致相同，所有数据都被逻辑地存放在一个空间中，我们称之为表空间（tablespace）。表空间又由段（segment）、区（extent）、页（page）组成。页在一些文档中有时也称为块（block），1 extent = 64 pages,InnoDB存储引擎的逻辑存储结构大致如图所示。

 

 

       表空间

 

       表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都是存放在表空间中。默认情况下InnoDB存储引擎有一个共享表空间ibdata1，即所有数据都放在这个表空间内。如果我们启用了参数innodb_file_per_table，则每张表内的数据可以单独放到一个表空间. 对于启用了innodb_file_per_table的参数选项，需要注意的是，每张表的表空间内存放的只是数据、索引和插入缓冲，其他类的数据，如撤销（Undo）信息、系统事务信息、二次写缓冲（double write buffer）等还是存放在原来的共享表空间内。这也就说明了另一个问题：即使在启用了参数innodb_file_per_table之后，共享表空间还是会不断地增加其大小。

        现在我们来做个实验，实验之前我已经将innodb_file_per_table设为ON了，看看初始共享表空间文件有多大：

        show variables like 'innodb_file_per_table' \G

       system ls -lh /usr/local/var/mysql/ibdata1

        共享表空间ibdata1的大小为76M。

         set autocommit=0;

        update mytest set salary=0;

         system ls -lh /usr/local/var/mysql/ibdata1

          首先将自动提交设为0，即我们需要显式提交事务（注意，上面结束时我们并没有commit或者rollback该事务）。接着我们执行会产生大量Undo操作的语句update mytest set salary=0，完成后我们再观察共享表空间，会发现ibdata1已经增长到了114MB，这就说明了共享表空间中还包含有Undo信息。如果我rollback这个事务，ibdata1这个表空间会不会缩减至原来的58MB大小？我们接下去就来验证： 

       rollback;

       system ls -lh /usr/local/var/mysql/ibdata1

        很“可惜”，还是114MB，即InnoDB存储引擎不会在rollback时去收缩这个表空间。虽然InnoDB不会帮你回收这些空间，但是MySQL会自动判断这些Undo信息是否需要，如果不需要，则会将这些空间标记为可用空间，供下次Undo使用。master thread每10秒会执行一次full purge操作。因此很有可能的一种情况是，你再次执行上述的UPDATE语句后，会发现ibdata1不会再增大了，那就是这个原因了。

 

    段

 

上图中显示了表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB存储引擎表是索引组织的（index organized），因此数据即索引，索引即数据。那么数据段即为B+树的页节点（上图的leaf node segment），索引段即为B+树的非索引节点（上图的non-leaf node segment）。

与Oracle不同的是，InnoDB存储引擎对于段的管理是由引擎本身完成，这和Oracle的自动段空间管理（ASSM）类似，没有手动段空间管理（MSSM）的方式，这从一定程度上简化了DBA的管理。

需要注意的是，并不是每个对象都有段。因此更准确地说，表空间是由分散的页和段组成。

 

    区

 

区是由64个连续的页组成的，每个页大小为16KB，即每个区的大小为1MB。对于大的数据段，InnoDB存储引擎最多每次可以申请4个区，以此来保证数据的顺序性能。

在我们启用了参数innodb_file_per_talbe后，创建的表默认大小是96KB。区是64个连续的页，那创建的表的大小至少是1MB才对啊？其实这是因为在每个段开始时，先有32个页大小的碎片页（fragment page）来存放数据，当这些页使用完之后才是64个连续页的申请。

通过一个实验来显示InnoDB存储引擎对于区的申请：

create table t1 (

　　col1 int not null auto_increment,

　　col2 varchar (7000),

　　primary key(col1)

)engine=InnoDB；

system ls -lh /usr/local/var/mysql/test/t1.ibd

创建了t1表，col2字段设为varchar(7000),这样能保证一个页中可以存放2条记录。可以看到，初始创建完t1后表空间默认大小为96KB.

 

   页

 

同大多数数据库一样，InnoDB有页（page）的概念（也可以称为块），页是InnoDB磁盘管理的最小单位。与Oracle类似的是，Microsoft SQL Server数据库默认每页大小为8KB，不同于InnoDB页的大小（16KB），且不可以更改（也许通过更改源码可以）。

常见的页类型有：

1. 数据页（B-tree Node）。
2. Undo页（Undo Log Page）。
3. 系统页（System Page）。
4. 事务数据页（Transaction system Page）。
5. 插入缓冲位图页（Insert Buffer Bitmap）。
6. 插入缓冲空闲列表页（Insert Buffer Free List）。
7. 未压缩的二进制大对象页（Uncompressed BLOB Page）。
8. 压缩的二进制大对象页（Compressed BLOB Page）。

行

InnoDB存储引擎是面向行的（row-oriented），也就是说数据的存放按行进行存放。每个页存放的行记录也是有硬性定义的，最多允许存放16KB/2～200行的记录，即7992行记录。这里提到面向行（row-oriented）的数据库，那么也就是说，还存在有面向列（column-orientied）的数据库.

 

     (3)  MEMORY存储引擎

Memory存储引擎使用存在于内存中的内容来创建表。每个memory表只实际对应一个磁盘文件，格式是.frm。memory类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。 
MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引，两种不同类型的索引有其不同的使用范围

Hash索引优点： 
Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。 
Hash索引缺点： 那么不精确查找呢，也很明显，因为hash算法是基于等值计算的，所以对于“like”等范围查找hash索引无效，不支持；

Memory类型的存储引擎主要用于哪些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果.

 

 

（4）不同存储引擎的横向对比

 

 

特点MyISAMBDBMemoryInnoDB

存储限制	没有	没有	有	64TB
事务安全		支持		支持
锁机制	表锁	页锁	表锁	行锁
B树索引	支持	支持	支持	支持
哈希索引			支持	支持
全文索引	支持
集群索引				支持
数据缓存			支持	支持
索引缓存	支持		支持	支持
数据可压缩	支持
空间使用	低	低	N/A	高
内存使用	低	低	中等	高
批量插入的速度	高	高	高	低
支持外键				支持