走进高性能Mysql（二）

Mysql中的事务

自动提交（AUTOCOMMIT）

Mysql默认采用自动提交（AUTOCOMMIT）模式即如果不是显式的开始一个事务，每个查询都被当作一个事务执行提交操作。

可以通过设置AUTOCOMMIT变量来启用或者禁用自动提交模式，如图。

1或ON表示开启，0或OFF表示禁用。当其为0时，所有查询都在一个事务中，直到显式执行COMMIT或ROLLBACK，该事务结束，同时又开始另一个新事务。当然这对非事务表，例如MyISAM或者内存表不会有影响，因为他们本身就没有COMMIT或ROLLBACK概念，相当于一直处于AUTOCOMMIT启用模式。

此外还有一些命令在执行之前会强制执行COMMIT提交当前的活动事务，例如：DDL中ALTER TABLE。另外还有LCOK TABLES等其他语句也会导致同样的结果。

Mysql可通过执行SET TRANSACTION ISOLATION LEVEL命令来设置隔离级别。新的隔离级别会在下一个事务开始的时候生效。可在配置文件中设置整个数据库的隔离级别，也可以只改变当前会话的隔离级别语句如下

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITED;

事务中混合使用存储引擎

那我们思考下一个问题如果说一个事务中混合使用存储引擎会怎样。

我们知道事务都是由Mysql下层的存储引擎实现。所以在同一个事务中，使用多种存储引擎是不可靠的。

假如我们事务中混合使用了事务型和非事务型的表（例如InnoDB和MyISAM表），正常提交情况下不会有什么问题。
但如果事务需要回滚，非事务型的表上的变更就无法撤销，这会导致数据库处于不一致的状态，这种情况就很难修复了，所以每张表选择合适的存储引擎很重要。

隐式和显式锁定

InnoDB采用的是两阶段锁定协议（two-phase locking protocol）。在事务执行过程中，随时都可执行锁定，锁只有在执行COMMIT或ROLLBACK的时候才会释放，且所有锁都在同一时刻被释放，上述为隐式锁定，InnoDB会根据隔离级别在需要时候自动加锁。

此外InnoDB也支持通过特定的语句进行显示锁定，这些语句并不属于SQL规范。

SELECT ... LCOK IN SHAPE MODE
SELECT ... FOR UPDATE

显示的使用诸如LOCK TABLES语句完全没必要还影响性能，不如InnoDB的存储引擎。

总只一句话，如果应用需要事务就应该选择事务型存储引擎。

多版本并发控制（MVCC,Multiversion Concurrency Contral）

划重点啊，面试题经常问。Mysql的大多事务型存储引擎实现的都不是简单的行级锁，一般都同时实现了多版本并发控制（MVCC）。不仅Mysql，Oracle、PostgreSQL等其他数据库也都实现了MVCC，各自实现机制不尽相同，因此MVCC没有一个统一的实现标准。

Mysql的MVCC可以看为是行级锁一个变种，在很多情况下避免了加锁操作，开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。

MVCC是通过保存数据在某个时间点的快照来实现。不同存储引擎MVCC实现不同，典型的有乐观(optimistic)并发和悲观(pessimistic)并发控制。下面我们讲解一下简化版InnoDB的MVCC的工作流程。

InnoDB的MVCC，通过每行记录后面保存两个隐藏的列来实现。这两列一个保存了行的创建时间，一个保存行的过期时间(或删除时间)，存储的不是实际的时间值而是一个系统版本号(应该有个规则其中一部分就是时间戳我猜测)。事务开始时的版本号做为事务的版本号，用来和查询到的每行记录的版本号做比较，看一下REPEATABLE READ隔离级别下，MVCC具体如何操作的：

有了这两列可以避免大多数操作都可以不用加锁，这样设计很明显读数据操作简单，性能很好。不足的地方每行记录多加两列需要额外的存储空间，需要更多的行检查工作以及额外的维护工作。

注意：MVCC只在REPEATABLE READ和READ COMMITED两个隔离级别工作，可以联想一下啊READ UNCOMMITED总是读取最新的数据行，不符合MVCC思想，而SERIALIZABLE会对所有读取的行都加锁。

Mysql的存储引擎

重点又来了，本节对各种存储引擎做一个简单的概述。

文件系统中，Mysql将每个数据库保存为数据目录下的一个子目录。创建表时，Mysql会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。

我们可以通过SHOW TABLE STATUS命令显示表的相关信息，例如下图mysql的user表：

这是个MyISAM表，我们看下每一行的含义。

InnoDB

InnoDB采用MVCC支持高并发，实现了四个标准的隔离级别，默认级别为REPEATABLE READ(可重复读)，并且通过**间隙锁(next-key locking)**策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，来防止幻影行的插入。

InnoDB是基于聚簇索引建立的，索引及优化在后续文章单独讲。聚簇索引对主键查询有很高的性能，聚簇索引也称为主键索引。它的二级索引(secondary index，非主键索引)包含主键列，所以主键列很大的话，其他所有索引都会很大。若表上的索引较多的话，主键应当尽可能的小。

InnoDB内部做很多优化，包括从磁盘读取数据时采用的可预测性读，能够在动在内存中创建hash索引以加速读操作的自适应哈希索引(adaptive hash index)，以及能够加速插入操作的**插入缓冲区（insert buffer）**等。

支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

来自官网的介绍https://dev.mysql.com/doc/refman/8.0/en/innodb-introduction.html

MyISAM

MyISAM不支持事务和行级锁，所以缺陷在于崩溃后无法安全恢复。但是其包含大量特性包括**全文索引、压缩、空间函数(GIS)**等。
对于只读数据，或者表较小依然可用MyISAM
特性

压缩表

如果表创建并导入数据以后，不会在进行修改，这种表很适合MyISAM压缩表。

性能
数据以紧密格式存储，在某些场景下性能很好。最典型的性能问题还是表锁的问题，若查询长期处于“Locked”状态，那没跑了。

来自官网的介绍https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html

Memory
如果你需要快速的访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么Memory引擎表很适合，他比MyISAM要快一个量级，因为所有的数据都保存在内存当中，不需要进行磁盘I/0。

适用于以下场景：
查找或者映射表，例如邮编与区的映射
缓存周期性聚合数据的结果
保存数据分析中产生的中间数据

支持Hash索引查找很快。不支持BLOB或TEXT类型的列，且每行的长度是固定的，即使指定varchar也会转换成char(我猜这样是为了防止内存浪费)

来自官网的介绍https://dev.mysql.com/doc/refman/8.0/en/memory-storage-engine.html

Archive
Archive只支持INSERT和SELECT操作，它会缓存所有的写并利用zlib对插入的行进行压缩，但每次都是全表扫描。

适合日志和数据采集类的应用

另外支持行级锁和专用的缓冲区，可实现高并发插入。在一查询开始到返回表所有行，引擎会阻止其他select执行。批量插入之前对读操作不可见，模仿了MVCC一些特性，但Archive不是一事务型的引擎，而是针对高速插入和压缩做了优化。

来自官网的介绍https://dev.mysql.com/doc/refman/8.0/en/archive-storage-engine.html

CSV
顾名思义，是针对csv文件的，对csv文件转换很方便。

其他存储引擎和第三方的就自己做个了解吧，这里就不提了。

选择合适的存储引擎

说了这么多存储引擎那我们该去选择这些存储引擎呢？

我们可以通过几个维度去综合的考虑该如何选择存储引擎

事务：首选就是InnoDB（或者XtraDB）
备份：需要在线热备份那也得InnoDB
特性：例如只有MyISAM支持空间搜索等等。

下面我们通过几个场景来说一下如何选择引擎？

日志型应用：假设你需要实时地记录一台中心电话交换机的没一通电话日志到Mysql中。对插入速度有很高的要求，MyISAM或者Archive引擎对这类比较合适，因为他们开小弟，而且插入速度非常快。
订单处理：订单这一类应用那必须得支持事务喽，没有半完成订单的这个说法选InnoDB
CR-ROM：若要发布一个基于CD-ROM或者DVD-ROM并且使用Mysql数据文件的应用，可考虑MyISAM或MyISAM压缩表，这样表间可以隔离并且可在不同介质上相互拷贝。
大数据量：数据量增长到 10TB以上的级别，可能需要建立数据仓库。Infobright是最成功的解决方案。也有一些大数据库不适合Infobright，可能适合TokuDB。

关于引擎的语句

创建一个新的存储引擎的表
CREATE TABLE test engine = InnoDB;
创建一个类似test_0表的表
CREATE TABLE test LIKE test_0;
将test表修改为InnoDB
ALTER TABLE test ENGINE = InnoDB;

---

以上就是本文的全部内容了，能力有限，理性对待

如果感觉还不错的话，欢迎点赞和关注🦋

分享经验，贴近项目，crud永不为奴！！！

---

欢迎大家关注我的公众号，公众号也会实时发布Java项目相关的文章！！！

---

1.一文搭建本地git服务器

2.Java实现短信验证码