InnoDB 存储引擎实物模型和锁的使用

InnoDB 是一种兼顾高可靠性和高性能的通用存储引擎。在 MySQL 8.0 中，InnoDB 是默认的 MySQL 存储引擎。InnoDB 存储引擎主要有以下优势：

• DML 操作遵循 ACID（事务）模型，事务具有提交、回滚和崩溃恢复功能，以保护用户数据。
• 行级锁定和 Oracle 风格一致的读取方式提高了多用户并发性和性能。
• InnoDB 表根据主键优化查询。每张 InnoDB 表都有一个称为聚簇索引的主键索引，该索引用于最小化 I/O 查询。
• 为了维护数据的完整性，InnoDB 支持外键约束。使用外键检查插入、更新和删除操作，以确保这些操作不会导致相关表数据之间的不一致。

InnoDB 的优点有：

• 如果服务器由于硬件或软件问题而意外退出，那么无论当时数据库中发生了什么，我们在重新启动数据库后都不需要执行任何特殊操作。InnoDB具备崩溃恢复功能，它会自动完成在崩溃之前提交的所有更改，并撤销正在进行但未提交的更改，使得在重新启动后可以从上次中断的地方继续进行后续操作。
• InnoDB存储引擎维护自己的缓冲池，把经常使用的数据存放在内存中，用于主内存缓存表和索引数据的查询。在专用数据库的服务器上，多达80%的物理内存通常分配给缓冲池。
• InnoDB存储引擎的表支持外键。
• InnoDB存储引擎的校验机制可以检测到磁盘或内存中的数据损坏。
• 当为数据库的表设置主键列时，系统会自动优化主键列，在where子句、order by子句、group by子句和连接操作中提高主键的使用效率。
• InnoDB存储引擎不仅允许对同一张表进行并发读写访问，它还缓存更改的数据以减少对磁盘的I/O操作。
• 当从表中重复访问相同的行数据时，自适应哈希索引会接管这些数据，以提高查询效率。
• 可以压缩表和索引数据，以及加密表数据。
• 支持在线DDL操作。
• 支持通过查询 information_schema 表来监控存储引擎的内部工作。
• 支持通过查询 performance_schema 表来监控存储引擎性能的详细信息。
• 将 InnoDB 表与来自其他 MySQL 存储引擎的表混合使用。
• InnoDB存储引擎提高了处理大量数据时的CPU效率。
• InnoDB存储引擎即使在文件大小限制为2GB的操作系统上也可以操作大数据量的表。

InnoDB 存储引擎实践

使用InnoDB存储引擎时的推荐做法：

• 在查询时最好根据主键查询，如果没有主键，可以指定一个自增值作为主键。
• 在使用连接查询时，最好在连接列上定义外键，而且表中的连接列使用相同的数据类型声明，这样可以提高查询性能。外键还会将删除和更新操作传播到所有受影响的表中，并在父表中不存在相应id时阻止在子表中插入数据，从而保证数据的完整性。
• 关闭自动提交。每秒提交数百次会限制服务性能，可以执行“set autocommit=0;”进行关闭。
• 通过用start transaction和commit语句将相关的DML操作集分组到事务中。
• 不要使用lock tables语句。InnoDB存储引擎允许多个会话同时对同一张表进行读取和写入，而不会影响可靠性和高性能。如果需要获得对一行数据的独占写访问权限，可以使用select … for update语法锁定待更新的行。
• 启用innodb_file_per_table变量或使用通用表空间将表内的数据和索引放入单独的文件中。innodb_file_per_table默认情况下为启用状态。
• 可以在InnoDB存储引擎不牺牲读写能力的情况下压缩表。
• 为了防止启用用户不想使用的存储引擎来创建表，可以使用 sql_mode=no_engine_substitution 选项来运行服务器。

如果想要知道当前服务器的默认存储引擎，则可以使用下面的SQL语句来查看：

show engines;

InnoDB和ACID模型

ACID（事务）模型是一组数据库设计原则，是业务数据和关键任务应用的可靠性保证。在MySQL中，InnoDB存储引擎是严格遵守ACID模型的存储引擎，因此数据不会损坏，执行数据的结果不会因软件崩溃或硬件故障等异常情况而失真。当数据本身符合ACID的特性时，应用程序不需要重新发明一致性检查和崩溃恢复机制的轮子。如果有额外的软件保护措施、超可靠的硬件或可以容忍少量数据丢失的应用程序，则可以调整MySQL设置以获得更高的性能或吞吐量。下面我们将介绍InnoDB存储引擎的ACID模型。

MySQL事务主要用于处理操作量大、复杂度高的数据。比如，在人员管理系统中删除一个人员，既要删除人员的基本资料，又要删除和该人员相关的信息，如信箱、文章等，如果其中有一项没有删除成功，则其他内容也不能被删除。这样，这些数据库操作语句就构成了一个事务。

一般来说，事务必须满足4个条件(ACID)：原子性（Atomicity，或称为不可分割性）、一致性(Consistency)、隔离性（Isolation，又称为独立性）、持久性(Durability)。

原子性：一个事务中的所有操作可以全部完成或全部失败，但不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚(Rollback)到事务开始前的状态，就像这个事务从来没有执行过一样。如图14-2所示，用户A给用户B转账1000，A账户减去1000，B账户加上1000，这个操作是一个原子操作，是不可分割的。如果同时成功，则数据库中A账户减去1000，B账户加上1000；如果同时失败，则操作回滚，数据库中的数据不变。

一致性：在事务开始之前和事务结束以后，数据库的完整性不会被破坏。这表示写入的数据必须完全符合所有的预设规则，包含数据的精确度、串联性，以及后续数据库可以自发性地完成预定的工作。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据不一致。事务隔离分为不同级别，包括未提交读(read uncommitted)、提交读(read committed)、可重复读(repeatable read)和串行化(serializable)。

持久性：事务处理结束后，对数据的修改就是永久的，即使系统故障也不会丢失。

手动提交

在MySQL命令行的默认设置下，事务都是自动提交的，即执行SQL语句后就会马上执行commit操作。因此，要显式地开启一个事务需要使用命令 begin 或 start transaction，或者执行命令 set autocommit=0 来禁用当前会话的自动提交。

用 begin rollback commit 实现

• begin：开始一个事务。
• rollback：事务回滚。
• commit：事务确认。

直接用 set 来改变 MySQL 的自动提交模式

锁机制

数据是一种供许多用户共享访问的资源，如何保证数据库并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁的冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对于数据库而言就显得尤为重要。MySQL中的锁可以按照不同的维度进行分类，以下是常见的几种锁：

按照粒度分类：

• 表级锁：锁住整张表，其他会话不能对特定行进行修改。
• 行级锁：在某些情况下，MySQL会将锁的粒度进行缩小，锁住某几行。行级锁比表级锁更细粒度，其他会话仍然可以修改表中其他行。

按照类型分类：

• 共享锁（S锁或者读锁）：多个会话可以同时对同一份数据对象进行共享锁定，读取数据对象的操作都是可行的，但写入数据对象的操作则会被阻塞。
• 排他锁（X锁或者写锁）：只有一个会话可以对一个数据对象进行排他锁定，其他会话不能进行读取或者写入数据对象的操作，只能等待排他锁被释放。
• 意向锁：用于在同一个事务的行级锁和表级锁之间进行转换和协调，降低锁定的粒度并提高并发性能。
• 乐观锁：假定并发操作之间没有冲突，不会阻塞数据读写操作，只在提交数据时进行数据版本的校验和冲突检测，如果存在冲突则回滚操作。
• 悲观锁：默认并发操作之间存在冲突，通过锁机制保护数据对象的完整性，会降低并发性能。
• 间隙锁：锁住一个数据对象前后的间隔区域以避免幻读问题。
• 记录锁：锁住某个或某些行，但是不包含间隙。
• 临建锁：锁住临时表，例如MySQL的 temporary table。
• 死锁：两个或多个事务互相需要对方占有的资源而陷入的循环等待状态。

幻读和不可重复读

事务A 按照一定条件进行数据读取， 期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据 称为幻读。

如果事务A 按一定条件搜索， 期间事务B 删除了符合条件的某一条数据，导致事务A 再次读取时数据少了一条。这种情况归为 不可重复读。