MySQL技术内幕
MySQL 

MySQL 体系结构

从概念上来说，数据库是文件的集合，是依照数据模型组织起来的并存放于二级存储器中的数据集合；数据库实例是程序，是位于用户与操作系统之间一层数据管理软件。应用程序只有通过数据库实例才能和数据库打交道

Alt text

存储引擎是基于表的，而不是数据库

表

如果创建表时没有指定主键，innoDB 使用如下方式创建主键：

  1. 判断表是否有非空的唯一索引，如果有，使用该列作为主键。如果有多个非空的唯一索引，根据索引的创建顺序选择第一个。
  2. 自动创建一个6字节大小的指针

InnoDB 的存储结构

所有数据都被逻辑地放在同一个空间中，称为表空间。表空间又有段（segment）、区（extend）、页（page）组成。

区由连续页组成，每个区有1MB，每个页默认有16KB（可以设置），一个区由连续的16个页组成

索引和算法

索引太多，应用程序的性能会受到影响；索引太少，查询性能会受到影响

锁

数据库使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。 InnoDB 不只在行级别上对表数据上锁，在操作缓冲池中的 LRU 列表，删除，添加、移动 LRU 列表中的元素时，为了保证一致性，也必须有锁的介入。

InnoDB 存储引擎中的锁

锁的类型

InnoDB 实现了两种标准的行级锁：

  • 共享锁：允许事务读一行数据
  • 排他锁：允许事务删除或更新一行数据

InnoDB 支持多粒度锁定，允许事务在行级上的锁和表级的锁同时存在。意向锁是将锁定的对象分为多个层次，先获取粗粒度的锁（表），再获取细粒度的锁（行）

  • 意向共享锁：事务获取一张表中某几行的共享锁
  • 意向排他锁：事务获取一张表中某几行的排它锁

查看锁：

select * from infomation_schema.INNODB_LOCKS;

一致性非锁定读：InnoDB 通过行多版本控制的方式读取当前执行时间数据库中行的数据。若读取行正在执行 DELETE 或 UPDATE 操作，会读取行的一个快照数据。 在 READ COMMITED 事务隔离级别下，非锁定读总是读取被锁定行的最新的快照数据；而在 REPEATABLE READ 事务隔离级别下，非锁定读总是读取事务开始时的行数据版本。 其他事务隔离级别不使用非锁定读。

锁的算法

InnoDB 引擎有三种行锁算法：

  • Record Lock: 单个行记录上的锁
  • Gap Lock：间隙锁，锁定一个范围，但不包含记录本身,唯一作用就是防止其他事务的插入操作，以此防止幻读的发生。
  • Next-Key Lock：临键锁，Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身

phantom problem

phantom problem 是指在同一事务下，连续执行同样的 SQL 导致不同的结果，第二次 SQL 返回之前不存在的行。 InnoDB 采用 临键锁 来避免 phantom problem。对于 select * from t where a>2 for update ，其锁住的不是 5 这个单个值，而是对 (2, +∞) 这个范围加了排它锁。因此对于这个范围的插入都是不允许的。

InnoDB 在 REEATABLE READ 的事务隔离级别下，使用 临键锁 来加锁。而在 READ COMMITED 的隔离级别下仅使用 Record Lock

锁的问题

锁提高了并发，但却会带来潜在的问题

脏读 (READ UNCOMMITTED)

一个事务可以读取到另外一个事务中未提交的数据。

不可重复读(READ COMMITTED)

在一个事务内多次读取同一个数据集合，在该事务未结束前，另外一个事务也访问同一个数据集合，并做了DML操作。导致第一个事务的两次读取数据可能不一样。 不可重复读读到的是已经提交的数据，脏读读到的是未提交的数据

-- A事务
SET@@ tx_isolation='read-committed';

BEGIN
SELECT * FROM t;
-- 1 row

-- B 事务开始
SET@@ tx_isolation='read-committed';
BEGIN
INSERT INTO t SELECT 2;
COMMIT;
-- B 事务结束

SELECT * FROM t;
-- 2 row

InnoDB 使用 Next-Key Lock 避免了不可重复读的现象

丢失更新

一个事务的更新操作被另一个事务更新操作覆盖，从而导致数据不一致：

  1. 事务 T1 查询一行数据，放入本地内存，并显示给终端用户User1
  2. 事务 T2 也查询该行数据，并将数据显示给User2
  3. User1 修改这行记录，更新并提交数据库
  4. User2 修改这行记录，更新并提交数据库

解决方案： 在步骤1和步骤2中，对用户读取的记录加上一个排他锁。这样步骤2就必须等待步骤3完成。

加排他锁方式：SELECT cash FROM account FOR UPDATE

死锁

死锁指两个或两个以上的事务在执行过程中，因争夺锁资源而造成一种互相等待的现象。 解决死锁的方式：

  1. 将任何等待都回滚，并且事务重新开始。会导致并发性能下降，很难发现并且浪费资源。
  2. 设置超时，一个事务进行回滚，另一个等待事务就能继续进行。
  3. 数据库还采用 wait-for graph (等待图) 来进行死锁检测。需要保存两种信息：锁的信息链表、事务等待链表。InnoDB 会回滚undo量最小的事务。

事务

事务把数据库从一种一致状态转换为另一种一致状态，事务需要符合 ACID 特性：

  • 原子性：事务是不可分割的工作单位
  • 一致性：事务将数据库从一种状态转换到下一种一致的状态
  • 隔离性：每个读写事务的对象对其他事务操作对象相互分离
  • 持久性：事务一旦提交，其结果就是永久性的

事务的实现

原子性、一致性、持久性通过数据库的 redo log 和 undo log 来完成。redo log 称为重做日志，用来保证事务的原子性和持久性。

InnoDB引擎默认的隔离级别是 REPEATABLE READ，在该级别下使用临键锁避免了幻读，达到了 SERIALIZABLE 隔离级别

不好的事务习惯

  • 在循环中进行事务提交：每次提交都会写一次重做日志
local_offer #MySQL 
navigate_before navigate_next