Mysql存储引擎

目录

Mysql有哪些存储引擎

Mysql存储引擎IMyISAM与InnoDB区别

MyISAM索引与InnoDB索引的区别？

InnoDB引擎的4大特性

如何选择存储引擎

一张表，里面有ID自增主键，当insert了17条记录以后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18仍是15 ?

innodb的事务与日志的实现方式

---

一、存储引擎

• Mysql有哪些存储引擎

mysql使用show engines 查看当前Mysql支持哪些存储引擎

 通过上图可以看出Mysql支持9种存储引擎，但是常用的存储引擎一般是innoDB，MyISAM，MEMORY这三种存储引擎

• Mysql存储引擎IMyISAM与InnoDB区别

	MyISAM	InnoDB
存储结构	每张表被存放在三个文件：frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件	所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB
存储空间	MyISAM可被压缩，存储空间较小	InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
可移植性、备份及恢复	由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作	免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了
文件格式	数据和索引是分别存储的，数据.MYD，索引.MYI	数据和索引是集中存储的，.ibd
记录存储顺序	按记录插入顺序保存	按主键大小有序插入
外键	不支持	支持
事务	不支持	支持
锁支持	表级锁定	行级锁定、表级锁定，锁定力度小并发能力高
SELECT	MyISAM更优
INSERT、UPDATE、DELETE		InnoDB更优
select count(*)	myisam更快，因为myisam内部维护了一个计数器，可以直接调取。
索引的实现方式	B+树索引，myisam 是堆表	B+树索引，Innodb 是索引组织表
哈希索引	不支持	支持
全文索引	支持	不支持

• MyISAM索引与InnoDB索引的区别？

1. InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
2. InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
3. MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
4. InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。
    

• InnoDB引擎的4大特性

1. 插入缓冲（insert buffer)

2. 二次写(double write)

3. 自适应哈希索引(ahi)

4. 预读(read ahead)

• 如何选择存储引擎

1. 如果没有特别的需求，使用默认的Innodb即可。
2. MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。
3. Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。

• 一张表，里面有ID自增主键，当insert了17条记录以后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18仍是15 ?

1. 若是建立的表的类型是InnoDB，若是新增一条记录（不重启mysql的状况下），这条记录的id是18；可是若是重启（文中提到的）MySQL的话，这条记录的ID是15。由于InnoDB表只把自增主键的最大ID记录到内存中，因此重启数据库或者对表OPTIMIZE操做，都会使最大ID丢失。
2. 若是表的类型是MylSAM，那么这条记录的ID就是18。由于MylSAM表会把自增主键的最大ID记录到数据文件里面，重启MYSQL后，自增主键的最大ID也不会丢失。

• innodb的事务与日志的实现方式

    1.有多少种日志

•  redo和undo

    2.日志的存放形式

•  redo：在页修改的时候，先写到 redo log buffer 里面， 而后写到 redo log 的文件系统缓存里面(fwrite)，而后再同步到磁盘文件(fsync)。 Undo：在 MySQL5.5 以前， undo 只能存放在 ibdata*文件里面， 5.6 以后，能够经过设置 innodb_undo_tablespaces 参数把 undo log 存放在 ibdata*以外。

    3.事务是如何经过日志来实现的，说得越深刻越好

• 基本流程以下： 由于事务在修改页时，要先记 undo，在记 undo 以前要记 undo 的 redo， 而后修改数据页，再记数据页修改的 redo。 Redo（里面包括 undo 的修改） 必定要比数据页先持久化到磁盘。 当事务须要回滚时，由于有 undo，能够把数据页回滚到前镜像的 状态，崩溃恢复时，若是 redo log 中事务没有对应的 commit 记录，那么须要用 undo把该事务的修改回滚到事务开始以前。 若是有 commit 记录，就用 redo 前滚到该事务完成时并提交掉。