InnoDB和MyISAM

文章摘自微信公众号架构师之路+自己的总结，作为学习笔记记录下来，如有侵权，联系必删

 

MySQL最常用的两个引擎：MyISAM和InnoDB

两种引擎的差异：

（1）如果对整表进行count(*)，由于在存储过程中，MyISAM会对表的行数进行存储，从而读取较快，但是InnoDB会逐行扫描统计，从而费时较多。如果加上where限定则两种方式都要逐行扫描。

（2）MyISAM支持全文索引，InnoDB5.6之前不支持全文索引。在数据量并发大的情况下，使用全文索引会导致小量请求占用大量数据库资源，所以在高并发的业务下，全文索引并不索引。

（ps.数据库中索引分为聚集索引（主键索引）、普通索引、唯一索引、全文索引

全文索引能够利用粉刺等多种文本处理方法智能地筛选出我们需要的结果。

实现方式：

https://www.cnblogs.com/PaulMa/p/5238682.html

//创建表
CREATE TABLE article (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT (title, content) --在title和content列上创建全文索引
);

//在建好表后添加全文索引
//索引名称为fulltext_article
ALTER TABLE article
ADD FULLTEXT INDEX fulltext_article (title, content)

//模糊查询
SELECT * FROM article WHERE content LIKE '%查询字符串%'

//全文索引查询
SELECT * FROM article WHERE MATCH(title, content) AGAINST('查询字符串')

）

（3）MyISAM不支持事务，InnoDB支持事务（保持数据一致性）

事务提供了commit、rollback、崩溃修复的能力，能很好的处理系统异常崩溃的情况，并保护文件免损伤，但事务也非常耗费性能，通常建议对一致性要求较高的业务使用复杂事务。MyISAM可以通过表锁来实现类似于事务的行为，但对性能影响较大。

（4）MyISAM不支持外键，InnoDB支持外键

在高并发的情况下，建议有应用程序保证完整性，而不使用外键

（5）MyISAM只支持表锁，InnoDB可以支持行锁。所以，在数据量大，高并发的状况下，InnoDB的性能较优。不命中索引，InnoDB也不能使用行锁。

（ps.如何检查是否命中了索引http://www.cnblogs.com/zhuifeng-mayi/p/9285724.html

         索引不命中的情况http://www.cnblogs.com/zhuifeng-mayi/p/9285724.html）

（6）InnoDB索引和记录是存储在一起的，而MyISAM的索引和记录是分开存储的

（ps.索引分为聚集索引和普通索引

聚集索引：叶子结点存储行记录；普通索引：叶子节点存储主键

意味普通索引InnoDB中要扫描两遍

）

InnoDB优于MyISAM的一点是能高效处理并发

并发导致的问题：数据不一致。

并发控制：对并发进行处理，保持数据一致。

并发控制的常用技术：锁、数据多版本。

（1）锁

普通锁原理：（本质是串行执行）操作数据前，锁住实施互斥，操作完成后，释放锁才允许其他任务执行

普通锁问题：不改变该数据的“读操作”也不可以进行，任务执行为串行方式，效率低。

改进的锁：（实现读读并发）

                    共享锁（S锁(Share Locks)，读取数据时加S锁；排他锁（X锁(exclusive Locks)）,修改数据时加X锁）

                    共享锁之间不互斥(读读可并行)，排他锁于任何锁互斥（写读、写写不可以并行）

 

（2）数据多版本

（实现读写并发）能够进一步提高并发，原理如下：

写任务发生时，将数据克隆一份，以版本号区分；写任务操作新克隆数据，直至提交；并发读任务可以继续读取旧版本数据，不至于阻塞。

 

redo，undo，回滚段

redo日志保障已提交事务的ACID特性。

数据库事务提交后，要将数据更新到磁盘上，以保证ACID特性（原子性、一致性、隔离性、持久性）。磁盘的随机写性能较低，如果每次都刷盘，会极大营西那个数据库的吞吐量。

解决方法：将修改行为先写到redo日志（此时为顺序写），再定期将数据刷到磁盘上。（随机写转为顺序写）

                    数据库崩溃，未刷盘数据，重启后，会重做redo日志里的内容，保证提交的事务所修改的数据都刷到磁盘上。

 

undo日志保障未提交数据事务不会对数据库的ACID特性产生影响。

数据库事务未提交时，会将事务修改数据镜像（即修改前的旧版本）存放到undo日志里，当事务回滚时，或者数据库崩溃，可以利用undo日志，撤销未提交事务对数据库产生的影响。

insert操作，undo日志记录新数据的PK，回滚时直接删除；

delete/update操作，undo日志记录旧数据row，回滚时直接恢复；

他们分别存在不同的buffer里。

 

回滚段

存储undo日志的地方

加入事务rollback，此时可以通过回滚段里的undo日志回滚。

假设事务提交，回滚段里的undo日志可以删除。

 

InnoDB是基于多版本并发控制的存储引擎

InnoDB能处理高并发的根本原因是多版本并发控制（MVCC），行锁、并发、事务回滚等多种特性与MVVC有关。

MVVC通过“读取旧版本数据”来降低并发事务的锁冲突，提高任务的并发度。

 

InnoDB的内核会对所有row数据增加三个内部属性：

DB_TRX_ID，6字节，记录每一行最近一次修改它的事务id

DB_ROLL_PTR，7字节，记录指向回滚段undo日志的指针

DB_ROW_ID，6字节，单调递增的行id

 

InnoDB为什么能够做到高并发：回滚段里的数据是历史数据的快照，这些数据是不会被修改的，select可随意并发读。

                                                       快照读，一致性不加锁的读是并发能很高的核心原因。

InnoDB所有普通锁均为快照读。

InnoDB的多版本控制、快照度机制，能够通过读取回滚段中的历史数据，在事务读取记录的时候不用加锁，从而支持高并发。