【Mysql】数据库引擎

存储引擎

MySQL常见的两种存储引擎：MyISAM和InnoDB

数据库引擎

数据库引擎的定义

访问数据库时，通过数据库引擎访问数据库文件。以关系数据库为例，发送SQL语句给数据库引擎，数据库引擎解释SQL语句，提取出需要的数据进行返回，数据库引擎就是SQL语句的解释器。
数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可以控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求，这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如索引、视图和存储过程）

数据库引擎的任务

• 设计并创建数据库以保存系统所需要的关系或XML文档
• 实现系统以访问或更改数据库中存储的数据，实现网站或使用数据的应用程序
• 提供日常管理支持优化数据库的性能

mySql引擎的类别

Mysql支持三个引擎：ISAM、MYISAM、HEAP
另外两种类型INNODB、BERKLEY(BDB)也可以使用

ISAM引擎

是一个定义明确且经理时间考验的数据表格管理方法，在设计时就考虑到数据库被查询的次数远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源
ISAM的不足在于，不支持事务处理、不支持外来建、不能够容错、不支持索引

MyISAM引擎

MyISAM是MySQL的ISAM拓展格式。除了ISAM里没有的索引（ISAM允许没有任何索引和主键的表存在，索引都是保存行的地址）和字段管理的大量功能，MyISAM还使用一种表格锁定的机制（表级锁），来优化多个并发的读写操作，其代价是需要经常运行OPTIMIZE TABLE命令，用来恢复被更新机制所浪费的空间，否则随便也会随之增加，最终影响数据访问性能。
MyISAM强调了快速读取操作。

Heap引擎

Heap存储引擎将数据存储在内存中，由于没有磁盘I/O的等待，所以该一起宁的表具有极高的插入、更新和查询效率。这种存储引擎默认使用哈希索引，速度比B±Tree型索引快。
但是由于存储在内存中，所以保存的数据具有不稳定性，如果Mysql进程发生异常、重启或计算机关机，都会造成这些数据小时，所以这种存储引擎中的表声明周期很短，一般只用一次。

InnoDB引擎

InnoDB引擎是Mysql灵活性的技术产品，mysql+api。
InnoDB比ISAM和MYISAM引擎慢
InnoDB为mysql表提供了ACID事务支持，系统崩溃修复能力和多版本并发控制的行级锁，该引擎提供了行级锁和外键约束所以InnoDB是事务性数据库首选的引擎。
采用B+数实现，索引与存储数据存储在同一文件中

InnoDB与MyISAM对比

存储结构：

• InnoDB使用共享表空间存储方式，所有数据存在一个单独的表空间里，这个表空间由很多个文件组成，一个表可以跨越多个文件存在，InnoDB表空间最大限制为64TB，这个大小包括所有索引和其他相关数据。使用单独表空间存储方式时，每个表的数据以一个单独的文件来存储，这个单独文件存放，表单限制了文件系统的大小。

• MySIAM中每个表存在分离的文件中，每个MyISAM中的表在磁盘上存储成三个文件，每一个文件均以表的名字开始，扩展名指出文件类型：，frm文件存储表定义;MYD文件存储表的数据;MYI文件存存储表的索引

存储空间：

• InnoDB存储引擎为在主内存建立其专用的缓冲池来缓存数据和索引，所以需要更多的内存和存储。

• MyISAM可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。【压缩后空间比压缩前减少60%~70%，但仅只读】

索引与数据
索引（index）是帮助MySQL高效获取数据的数据结构。MYISAM和Innodb都是用了树这种数据结构作为索引

• Innodb引擎的索引结构是B+Tree，但是这颗树的叶节点data域保存了完整的数据记录，所以InnoDB的数据文件本身就是索引文件，索引和数据是紧密捆绑的。data域的key是数据表的主键。没有使用压缩从而会造成Innodb比MyISAM体积庞大不少。
  

-InnoDB的数据文件本身按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显示指定，MySQL系统会自动选择一个可以作为唯一标识数据记录的列作为主键，如果不存在这种列，MySQl自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型

InnoDB的辅助索引data域存储的也是相应记录主键的值而不是地址，所以当辅助索引查找时，会先根据辅助索引找到主键，再根据主键索引找到实际数据。所以InnoDB的所有辅助索引都引用主键作为Data域

• MyISAM引擎的索引结构也是B+Tree,其中B+Tree的data域存储的内容为实际数据的地址，也就是说它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，所以 MyISAM的索引文件和数据文件是分开的，索引文件仅保存数据记录的地址。MyISAM索引是有压缩的，内存使用率就对应提高了不少。
  
  在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而 辅助索引的key可以重复。与InnoDB不同的是MYISAM辅助索引存储的 data域存储的是地址而不是主键。假设我们以Col1为主键，然后我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：
  是否保存行数

• InnoDB中不保存表的具体行数，执行select count(*) from table 时，InnoDB要扫描一遍增高饿表来计算有多少行。

• myISAM中存储了表的行数，于是select count(*) from table时只需要直接取已经保存好的值而不需要进行全表扫描。

锁

• MyISAM支持的是表级锁，而InnoDB支持的是行级锁。

• 但由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “a%”

可移植性、备份及恢复

• InnoDB免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。

• MyISAM引擎中的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作

mysql数据引擎更换方式

• 查看当前数据库支持的引擎和默认的数据库引擎：
  show engines;

• 更改数据库引擎：

  • 更改方式1:
    修改配置文件my.ini，将my-small.ini另存为my.ini，在default-storage-engine=InnoDB,重启服务，数据库默认引擎更改为了InnoDB
  • 更改方法2：在键表的时候指定
    create table mytbl(id int primary key,name varchar(50)) type=MyISAM
  • 更改方式3：建表后更改
    alter table mytbl2 type =InnoDB;

• 查看修改结果
  show create table table_name;
  show table status from table_name;