热爱生活

MySQL 基础知识

2018-09-01 Database

MySQL 数据库在互联网业务中的地位无需多言，本文从各个方面整理 MySQL 数据库的知识点。

关于数据库基础理论知识，请查看：数据库基础整理。

底层原理

MySQL 底层是使用了哪些技术，使得其具备高性能的特性呢？

逻辑组成结构

image

  • 连接处理层
    • 客户端网络连接处理
    • 授权认证
    • 安全认证等

image

MySQL客户端与服务端通信协议是半双工的：在任一时刻只能单方向发送数据，一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。从这个角度上讲，控制 max_allowed_packet 参数控制客户端单次发送数据大小、限制查询结果大小有利于提高交互速度。

  • 核心层
    • 查询解析
    • 分析优化
    • 缓存
      • 对请求进行哈希并记录，对相同请求直接返回缓存内的结果
      • 查询中包含函数、用户变量、临时表、系统表时则不会进行缓存
      • 表的数据和结构发生变化时将缓存失效，此操作需要获取锁
      • query_cache_type 等 query_cache_ 开头的参数
    • 函数、存储过程、触发器、视图
  • 存储引擎层
    • 执行事务处理操作
    • 数据存储和读取
    • 不解析 SQL，只是响应上层请求
    • 实现锁机制

存储引擎

MySQL 的存储引擎主要包括：

  • MyISAM
    • 低版本默认引擎
    • 支持全文检索、压缩、空间函数
    • 不支持事务和行级锁
    • 对全表加锁
  • InnoDB
    • 高版本默认引擎
    • 默认的事务性引擎
    • 性能优越、自动崩溃回复特性
    • 基于 MVCC 的高并发
    • 支持多隔离级别
    • 基于聚簇索引建立表
    • 采用可预测性预读技术减少访问从磁盘
    • 存储引擎实现
      • 最小存储单元是页
      • 页大小为 16K（参数 innodb_page_size）
        • 利用磁盘预读原理
        • （块/簇 (widows/linux) 文件系统最小单元 4K bytes）
      • .ibd 大小是 16K 的整数倍
  • Archive/Memory/CSV 等

索引及其优化

官方参考链接：14.6.2.1, Clustered and Secondary Indexes、8.3, “Optimization and Indexes”

  • 索引速度快

    普通查询需要遍历磁盘扇区内所有数据；通过在索引中记录待查询字段、记录对应位置，可以快速筛选出结果集；即便需要回表查询非索引字段，只需根据索引中记录的扇区位置查找对应数据即可。

    InnoDB 表是基于聚簇索引建立的，其索引结构和其他存储引擎有很大的不同，并且内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能自动在内存中创建 hash 索引以加速读操作的自适应哈希索引，以及能够加速插入操作的插入缓冲区等。

  • 索引的数据结构和底层实现
  • 聚簇索引 Clustered index
    • 是一种数据存储方式，InnoDB 在实现聚簇索引时，将索引和数据行保存在了同一个结构
    • 通常 InnoDB 表的聚簇索引就是主键
      • 若表中定义了主键则主键作为聚簇索引
      • 若表中无主键，且所有 key 列非空，则使用表的第一个 unique 索引作为聚簇索引
      • 若表中无主键也没有合适的 unique 索引，则内部生成一个包含 rowId 的隐藏的 GEN_CLUST_INDEX 列
    • 主键按顺序插入是最快的插入方式，否则建议 OPTIMIZE TABLE
    • 更新聚簇索引列的代价较高，会强制被更新的行移动到新位置
    • 可能会导致全表扫描变慢，比如行稀疏、页分裂导致存储不连续时
  • 非聚簇索引/二级索引
    • 除了聚簇索引列外其余索引都是二级索引
    • 二级索引记录里存储了该行的主键列和对应的索引列
      • 使用主键查询二级索引对应列的数据值
      • 若主键太长则会导致整体二级索引占用较大的空间
    • 非聚簇索引比聚簇索引多了一次读取数据的 I/O 操作，查找性能上会差些
  • 索引分类
    • 按数据结构分类
      • B+tree索引、Hash索引、全文索引、空间索引
    • 按存储方式分类
      • 聚簇索引、非聚簇索引
    • 按字段特性分类
      • 主键索引、唯一索引、普通索引、全文索引
    • 按字段个数分类
      • 单列索引、联合索引（也叫复合索引、组合索引）
  • 索引类型
    • 主键索引、唯一索引、普通索引、联合索引、全文索引
    • B-Tree
    • Hash
    • R-Tree
    • fulltext
  • 索引优点
    • 减少扫描数据量
    • 避免临时排序和临时表
    • 将随机 I/O 变为顺序 I/O
  • 联合索引 index merge
  • 优化方向
    • 按顺序建立索引
    • 覆盖索引避免回表
    • “三星”索引原则
      • WHERE 条件使用索引查询记录 ★
      • 如果索引中的数据顺序和查找中的排列顺序一致 ★★
      • 如果索引中的列包含了查询中需要的全部列 ★★★
  • 异常优化原因/不走索引
    • 执行计划的成本预估错误
    • 表数据量小
      • 一次 I/O 加载就可满足查询
      • 比走索引查询效率更高
      • 此时查询不走索引
    • 多个索引情况下，优化器采用了错误的索引
      • 使用 explain 分析
      • 导致查询效率偏低，
      • 使用 FORCE USE INDEX(...) 来强制指定索引
  • 优化策略
    • 反例
      • 参与函数、参与计算
      • 隐式类型转换（会导致索引失效）
      • 使用 OR 时多小心
      • 负向查询（not, not in, not like, <>, != ,!>,!<） 不会使用索引
    • 正例
      • varchar 前缀索引指定长度
        • 确定区分度方式见下面 sql 片段
        • 可以使得索引更小、更有效
        • 缺点：无法使用前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描。
      • 唯一特性字段必建 unique 索引
      • 禁止左模糊/全模糊查询
      • order by 字段的索引有序性
        • 正例：where a =? and b =? order by c; 索引： a_b_c 正常生效
        • 反例：索引中有范围查找，那么索引有序性无法利用
          • 如：WHERE a >10 ORDER BY b; 索引 a_b 无法排序
      • 利用覆盖索引避免回表
      • 利用延迟关联或子查询优化大量数据分页场景
      • 让区分度高的索引列在左边，区分度低的（如性别年龄省份等字段）在后面
  • 优化目标指标
    • 最低 range，要求是 ref，consts 最好
    • 若 type=index 相当于全表扫描性能最差
    • ORDER BY 避免出现 Using filesort
  • 判断索引的区分度
    • 适当的区分度可以保证效率又减小索引体积
-- varchar 列的区分度判断
SELECT COUNT(*) AS cnt,LEFT(city,3) AS pref FROM t_city GROUP BY pref ORDER BY cnt DESC LIMIT 10; -- 查找合适的列
SELECT COUNT(DISTINCT LEFT(列名, 索引长度))/COUNT(*) FROM t_city;
ALTER TABLE t_city ADD KEY (city(6));-- 设置索引长度
  • 延迟关联
SELECT a.* FROM tb a, (select id from tb where 条件 LIMIT 100000,20 ) b where a.id=b.id; -- 阿里开发手册示例
SELECT a.* FROM tb a, (select id from tb where id > 100000 LIMIT 20 ) b where a.id=b.id;
-- (sex，rating) 索引
SELECT <cols> FROM profiles INNER JOIN (
 SELECT <primary key cols> FROM profiles
 WHERE x.sex='M' ORDER BY rating LIMIT 100000, 10
AS x USING(<primary key cols>);
  • EXISTS/IN
    • 必须小表驱动大表
    • 做好 explain 确认
  • ONLY_FULL_GROUP_BY
    • 使用标准语法分组查询

    • ONLY_FULL_GROUP_BY Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

索引长度

varchar 类型字段的索引长度默认是最大长度 255 个字符，MySQL 默认每个 utf-8 字符占 3 字节。

数据存储碎片化

  • 行碎片
    • 数据被存储在多个地方
  • 行间碎片
    • 逻辑上连续的行却不是顺序存储
  • 剩余空间碎片
    • 数据页中有大量空余空间
OPTIMIZE TABLE <table>; -- MyISAM
ALTER TABLE <table> ENGINE=<engine>; -- InnoDB 换为同一个引擎

查询优化

表结构与索引、SQL语句优化、MySQL参数优化、硬件及系统配置，按照这个顺序，优化成本逐渐增加，优化效果逐渐明显。

explain 详解

左侧深度优先的查询执行计划。

  • id 编号
  • select_type 查询类型
    • 简单查询 SIMPLE 不包括子查询和 union
    • 复杂查询
      • PRIMARY 外层标记
      • SUBQUERY 包含在 SELECT 列中的子查询中的 SELECT（不在 FROM 子句中）
      • DERIVED 包含在 FROM 子句的子查询中的 SELECT
      • UNION UNION 中的第二个和随后的SELECT被标记为UNION
  • table 对应正在访问的表/别名
  • type
    • ALL 全表扫描
    • index 按索引顺序全表扫描
    • range 指定范围内的索引扫描
    • ref 返回匹配某值的行
    • eq_ref 返回匹配某值的一行
    • const,system 可以将某部分查询优化为常量，不再需要关联
    • NULL 说明不用再访问索引和表
  • possible_keys 初期判断可能使用的索引
  • key 优化器采用的索引
  • key_len 索引使用的字节数
  • ref 和前方表连接的字段,const代表是常量值连接
  • rows 预估需要查找的行数
  • filtered
    • 针对表里符合某个条件（WHERE 子句或联接条件）的记录数的百分比所做的一个悲观估算
  • Extra
    • Using index 使用覆盖索引从而避免回表
    • Using index condition：5.6版本之后新增的功能，称为索引下推；通过使用索引对存储引擎索引出的数据进行再过滤，减少回表查询的次数。
    • Using where 将在查询后过滤
    • Using temporary 对查询结果排序时将使用临时表
    • Using filesort 采用文件排序

profile

MySQL 文档链接

show variables like 'profiling';
set profiling = 'on';
select * from emp group by id order by userName;
-- 返回 Query_ID,Duration,Query(sql script)
show profiles;
-- 查询 Query_ID=14 这条 sql 执行过程的 cpu 消耗情况
-- 支持的 profile 选项：all,block io,cpu,ipc,memory,swaps 等，不再列出
show profile cpu, block io for query 14;
set profiling = 'off';

optimizer_trace

从 5.6 版本开始支持 optimizer tracing 功能来分析执行计划

SET optimizer_trace="enabled=on";
explain select * from user_info where name is not null;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

MRR 优化

MySQL 5.6 版本开始支持 Multi-Range Read(MRR)优化，目的是为减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，对于 IO-bound 类型的 SQL 查询语句可带来性能极大提升。

-- 关闭 mrr
SET @@optimizer_switch='mrr=off';
-- 开启 mrr
SET @@optimizer_switch='mrr=on,mrr_cost_based=off';

锁机制-InnoDB

锁粒度

按照粒度可以分为：全局锁、表级锁、行级锁。

尽量只锁定需要修改的部分数据，而不是所有的资源。更理想的方式是，只对会修改的数据片进行精确的锁定。在给定的资源上锁定的数据量越少则系统的并发程度越高。基于锁的开销、数据安全性和性能方面的平衡，MySQL 在多种存储引擎上实现了多种的锁策略。

  • 1、全局锁
    • 锁定库中的所有表
    • 获取方式
      • FLUSH TABLES WITH READ LOCK
        • 此时可以执行 mysqldump -uroot -h192.168.1.1 db -p > /root/1.sql 备份数据
      • unlock tables
      • 设置 read_only=1
    • 它与任何表锁都冲突
    • 比如 mysqldump时，DML/DDL 不允许，DQL 可以
  • 2、表级锁
    • 查看当前的表级锁
      • show status like 'table%'
      • show engine innodb status;show processlist;show status like '%lock%'
      • SHOW STATUS LIKE 'InnoDB_row_lock%' 查看行锁状态
    • 分类
      • 1、表锁
        • 表共享读锁
          • lock tables tb read;
        • 表独占写锁
          • lock tables tb write;unlock tables tb;
        • 解锁：unlock tables tb; 或者断开客户端
      • 2、元数据锁(meta data lock,MDL)
        • 系统自动控制，目的是维护表的元数据的数据一致性
        • 对表进行增删改查时，加MDL读锁
        • 对表结构变更时，加MDL写锁（排他）
        • SELECT * FROM performance_schema.metadata_locks;
      • 3、意向锁
        • 避免执行 DML 时行锁与表锁的冲突，让表锁不用检查每行数据是否都加锁
        • 线程A加行锁，此时表会有意向锁，线程B在获取表锁前先判断当前意向锁是否兼容
        • 意向共享锁（IS）
          • select...lock in share mode
          • 与表共享锁兼容，与表排他锁互斥
        • 意向排他锁（IX）
          • insert、update、delete
          • select...for update
          • 与表共享锁、表排他锁均互斥
  • 3、行级锁（InnoDB）
    • 3.1 行锁 RecordLock
      • 对唯一索引进行匹配时使用行锁
      • 行-共享锁
        • select...lock in share mode
      • 行-排他锁
        • insert/update/delete/select...for update
    • 3.2 间隙锁 GapLock
    • 3.3 临键锁 NextKeyLock(行锁和间隙锁的组合)
      • 查询非唯一索引时默认使用临键锁进行搜索和索引扫描，防止幻读
    • 对无索引的列加锁会升级为表锁
  • 命名锁
    • 在重命名或删除一个表时创建，命名锁与普通的表锁相冲突。
    • show open tables where in_use>=1 查看哪些表加锁了
  • 字符锁
    • 用 GET_LOCK(),RELEASE_LOCK(),IS_USED_LOCK(),IS_FREE_LOCK() 等函数在服务器级别内锁住和释放任意一个字符串。

5.7 默认未开启 performance-schema 全部功能，参考：performance-schema-quick-start、metadata-locks-table

--- 全部开启
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES',TIMED = 'YES';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES';
-- 仅想在线开启 metadata_locks，则操作如下：
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'global_instrumentation';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME ='wait/lock/metadata/sql/mdl';
--- 或者使用这种方法启用 metadata_locks
call sys.ps_setup_enable_instrument('wait/lock/metadata/sql/mdl%');
-- 启用 data_locks 8.0版本
UPDATE performance_schema.setup_instruments SET enabled='YES', timed='YES' WHERE name LIKE 'wait/lock/metadata%';
-- 查询定位语句
SELECT
    ps.*,
    lock_summary.lock_summary
FROM
    sys.processlist ps
    INNER JOIN (
        SELECT
            owner_thread_id,
            GROUP_CONCAT(
                DISTINCT CONCAT(
                    mdl.LOCK_STATUS,
                    ' ',
                    mdl.lock_type,
                    ' on ',
                    IF(
                        mdl.object_type = 'USER LEVEL LOCK',
                        CONCAT(mdl.object_name, ' (user lock)'),
                        CONCAT(mdl.OBJECT_SCHEMA, '.', mdl.OBJECT_NAME)
                    )
                )
                ORDER BY
                    mdl.object_type ASC,
                    mdl.LOCK_STATUS ASC,
                    mdl.lock_type ASC SEPARATOR '\n'
            ) as lock_summary
        FROM
            performance_schema.metadata_locks mdl
        GROUP BY
            owner_thread_id
    ) lock_summary ON (ps.thd_id = lock_summary.owner_thread_id);
select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx;

锁类型

  • 共享锁和排它锁

InnoDB 实现了两种行级别锁， shared (S) locks 和 exclusive (X) locks。

  • 共享锁允许持锁事务读取行记录
  • 排它锁允许持锁事务更新、删除行记录
  • 当某事务持有 row 的 S 锁时：
    • 其他事务可以立刻获取到 row 的 S 锁（即多个事务同时获取 row 的 S 锁）
    • 其他事务不能立刻获取到 row 的 X 锁
  • 当某事务持有 row 的 X 锁时：
    • 则 row 的 S 锁和 X 锁 均不能被其他事务立刻获取到
    • 需等待当前事务释放 row 的锁

共享锁和排他锁也叫读锁（read lock）和写锁（write lock），读锁是共享的，相互不阻塞；写锁是排他的，会阻塞其他的写锁和读锁，以确保在给定的时间里只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源。

  • 意向锁

当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以在需要锁定行的表上面添加一个合适的意向锁。

如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。

意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。所以，可以说 InnoDB 的锁定模式实际上可以分为四种：共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX），我们可以通过以下表格来总结上面这四种所的共存逻辑关系：

InnoDB 支持多种粒度的锁以实现行锁和表锁共存，例如 LOCK TABLES ... WRITE 会在指定表上加 X 锁。InnoDB 使用意向锁使多粒度锁更为实用。

意向锁是表级锁，用于说明事务随后将要获取的是哪种类型的锁（S 或 X），分为两种：

  • intention shared lock (IS) 表示某事务意图获取表中某些 row 的 S 锁
    • SELECT ... LOCK IN SHARE MODE 加 IS 锁
  • intention exclusive lock (IX) 表示事务意图获取表中某些 row 的 X 锁
    • SELECT ... FOR UPDATE 加 IX 锁

意向锁加锁协议如下：

  • 事务在获取 row 的 S 锁前，必须先获取 table 的 IS 或级别更高的锁
  • 事务在获取 row 的 X 锁前，必须先获取 table 的 IX 锁

表级别锁类型兼容性整理如下：

  X IX S IS
X Conflict Conflict Conflict Conflict
IX Conflict Compatible Conflict Compatible
S Conflict Conflict Compatible Compatible
IS Conflict Compatible Compatible Compatible

意向锁不阻止全表操作请求（例如 LOCK TABLES ... WRITE）以外的加锁请求。使用意向锁就是为了说明某个事务正在或将要对表中的某些 row 加锁。

用 SELECT ... IN SHARE MODE 获得 IS，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行 UPDATE 或者 DELETE 操作。但是在多个事务均获得共享锁后对该记录进行更新操作，则可能造成死锁。对于锁定行记录后需要进行更新操作的应用，应该使用 SELECT... FOR UPDATE 方式获得 IX。 ”

  • 行锁(Record Lock)
    • 是对索引记录加的锁
      • 若表无任何索引，则创建并使用隐藏的聚簇索引
    • 只有使用索引检索数据 InnoDB 才使用行级锁，否则 InnoDB 将使用表锁
    • 不同行记录如果是使用相同的索引行会出现锁冲突
    • 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
    • 即使 WHERE 条件使用了索引 MySQL 仍有可能使用表锁
      • 是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，
      • 如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引
  • 间隙锁(Gap Lock)
    • RR 级别，InnoDB
    • 使用范围条件查询且要求加共享锁或排他锁时 InnoDB 会给查询结果数据的索引项加锁
    • 给范围条件内（即使是并不存在的记录）加锁

      • gaps between all existing values in the range are locked.

    • A gap might span a single index value, multiple index values, or even be empty.

    • SELECT FROM t_user WHERE key_col>0 FOR UPDATE;
    • 对于加锁读(SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE 来说：
      • 加锁时取决于查询语句是否使用 unique index 和 unique search condition, or a range-type search condition.
      • 使用唯一索引的查询时只会加行锁，锁住记录对应的索引，不会加间隙锁。
        • 不包括查询条件只包括联合唯一索引的情况
      • 使用非唯一索引的查询时除了会加行锁，锁住记录对应的索引，还会使用 gap locks 或 next-key locks 以阻塞其他事务插入数据到当前查询范围覆盖的 gaps。
    • Gap lock 仅是为了防止其他事务插入数据到 gap 里。
    • Gap lock 可以共存
    • 目的
      • 防止其他事务插入造成幻读，满足隔离级别的要求
      • 满足数据恢复和复制的需要
    • 问题
      • 查询不当可能造成表锁
      • 若索引未覆盖全部查询条件可能会锁定部分范围外的行
      • 两个线程同时使用 SELECT FOR UPDATE 查询不存在的记录（RR 级别下）
        • 两个事务可以同时执行获取到锁
        • 但任何一个事务执行 INSERT 都会报死锁的错误
    • 使用 = 查询不存在的记录也会加锁；
  • next-key 锁(行锁和间隙锁的组合)
    • RR 级别下会以此方式对数据加锁减少幻读问题
    • 索引记录的行锁 + 索引记录之前的 gap 的 gap lock
    • InnoDB 采用如下行锁机制：
      • 查找扫描索引时，在扫描到的索引记录上加 S 或 X 锁
      • 这样行锁实际上就是索引记录的锁
      • 索引记录上的 next-key 锁也会影响到索引记录前的 gap
        • 若某事务持有了索引记录的 X 或 S 锁，其他事务则不能立刻在该索引记录前的 gap 插入新索引记录。
        • 举个例子，index包括 10, 11, 13, and 20. 可能的 next-key 锁如下：
          • (negative infinity, 10]
          • (10, 11]
          • (11, 13]
          • (13, 20]
          • (20, positive infinity)
    • 防止 RR 下出现 phantom rows (see Section 14.7.4, “Phantom Rows”).
  • Insert Intention Locks
    • 插入数据之前在 row 前的加的一种 gap lock
    • 这个锁使得多个事务插入数据到同一 index gap 不同位置的时候无需相互等待
  • AUTO-INC Locks
    • 事务插入自增列时的 table 锁
    • 一个事务必须等待另一个事务插入完后才能继续插入，以保证自增连续
    • innodb_autoinc_lock_mode 参数
  • Predicate Locks for Spatial Indexes
    • 此处不表

MySQL 的事务

主要整理 InnoDB 引擎的事务实现。

为了避免发生数据丢失的问题，当前事务数据库系统普遍都采用了 WAL（Write Ahead Log）策略，即当事务提交时，先写 redo log，再修改页（先修改缓冲池，再刷新到磁盘）；当由于发生宕机而导致数据丢失时，通过 redo log 来完成数据的恢复。关键点是日志先行，再写磁盘。引擎层会记录 redo log，服务层会记录binlog。

redo log 是物理日志，记录的是“在XXX数据页上做了XXX修改”；binlog是逻辑日志，记录的是原始逻辑，其记录是对应的SQL语句；binlog 是追加写入的，就是说 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志；而 redo log 是循环写入的。对数据库中的数据进行修改时必须保证日志先于数据落盘。当日志落盘后，就可以给用户返回操作成功，并不需要保证当时对数据的修改也落盘。如果数据库在日志落盘前 crash 那么相应的数据修改会回滚。在日志落盘后 crash 会保证相应的修改不丢失。

在 WAL 日志先行技术之前，数据库只需要把修改的数据刷回磁盘即可，用了这项技术，除了修改的数据，还需要多写一份日志，也就是磁盘写入量反而增大，但是由于日志是顺序的且往往先存在内存里然后批量往磁盘刷新，相比数据的离散写入，日志的写入开销比较小。

事务日志

事务日志可以帮助提高事务的效率，简述如下：

使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。

事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序 I/O，而不像随机 I/O 需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。

事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。目前大多数存储引擎都是这样实现的，我们通常称之为预写式日志（Write-Ahead Logging），修改数据需要写两次磁盘。

如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。具体的恢复方式则视存储引擎而定。

事务实现

关于数据库事务隔离级别等概念，请访问 数据库事务

  • 自动提交

MySQL 默认采用自动提交模式。也就是说，如果不是显式地开始一个事务，则每个查询都被当作一个事务执行提交操作。在当前连接中，可以通过设置 AUTOCOMMIT 变量来启用或者禁用自动提交模式：

SHOW VARIABLES LIKE 'AUTOCOMMIT';
SET AUTOCOMMIT=0;// 1,ON 表示启用 0,OFF 表示禁用

另外有些命令会强制提交当前的活动事务，比如 DDL 语句中导致大量数据修改的 ALTER TABLE 语句，另外 LOCK TABLES 等也是。

  • 隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT @@tx_isolation;
SELECT @@global.tx_isolation;
  • 显示锁定与隐式锁定

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

另外，InnoDB 也支持通过特定的语句进行显式锁定，这些语句不属于SQL规范：

SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE

MySQL 也支持 LOCK TABLES 和 UNLOCK TABLES 语句，这是在服务器层实现的，和存储引擎无关。但并不能替代事务处理。如果应用需要用到事务，还是应该选择事务型存储引擎。

注意：LOCK TABLES 和事务之间相互影响的情况非常复杂，除了事务中禁用了 AUTOCOMMIT，可以使用LOCK TABLES之外，其他任何时候都不要显式地执行 LOCK TABLES，不管使用的是什么存储引擎。

MVCC 机制

官网文档参考：14.3 InnoDB Multi-Versioning、14.7.2.1 Transaction Isolation Levels、14.7.1 InnoDB Locking、14.7.2.3 Consistent Nonlocking Reads

根据官方文档里关于 RR 级别的锁说明，整理如下：

  • RR 是 InnoDB 的默认隔离级别
    • 即同一个事务内的 SELECT 均基于 first read 时的快照.

多版本并发机制极大地提升了并发性能。MVCC 通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

两个隐藏列

InnoDB 的 MVCC，是通过在每行记录后面保存两个隐藏的列来实现的，分别保存了行的创建版本、行的过期版本（或删除版本）。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。下面看一下在 REPEATABLE READ 隔离级别下，MVCC 具体是如何操作的：

  • SELECT
    • 查询满足以下两个条件的记录：
    • 创建版本小于或等于当前事务版本。这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
    • 行的删除版本为空，或者大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。
  • INSERT
    • 为插入的每一行保存当前系统版本号作为创建版本。
  • DELETE
    • 为删除的每一行保存当前系统版本号作为删除版本。
  • UPDATE
    • 插入一行新记录，保存当前系统版本号作为行创建版本号，同时保存当前系统版本号到原来的行作为行删除标识。？？？此处描述细节待确认？？？

快照读、当前读

  • 快照读
    • 执行 SELECT 时 InnoDB 默认执行快照读，会记录下此次查询结果
    • 后续 SELECT 时会返回这个数据
    • 这样其余事务提交就不会影响当前 SELECT 的结果，实现了可重复读
      • 注意如下场景：
        1. A 开启事务
        2. B 开启事务，写入数据 x，提交事务。
        3. A 查询数据，返回结果是包括 x 的（x 符合 WHERE 条件）。
        4. B 再开启事务写入数据 y，提交事务。
        5. A 查询数据结果不会变。
  • 当前读
    • 对于会对数据修改的操作(update、insert、delete)都会采用当前读模式
    • 执行操作时会读取最新的记录，即会读取其他事务已提交的数据。
    • 假设要 UPDATE 一条记录
      • 另一个事务中已经 DELETE 掉这条数据并且 COMMIT 了
      • 如果 UPDATE 就会失败

redo、undo

排序规则

每种 CHARSET 都有多种它所支持的 COLLATE，例如 Latin1 编码的默认 COLLATE 为latin1_swedish_ci，utf8mb4 编码的默认值为 utf8mb4_general_ci。

很多 COLLATE 都带有 _ci 即 Case Insensitive 也就是说 A 和 a 在排序和比较的时候一样，select * from table1 where field1='a' 同样可以把 field1 为 A 的记录筛选出。相对应的 _cs 后缀的 COLLATE 则是 Case Sensitive 大小写敏感的。

SHOW COLLATION
SHOW COLLATION WHERE `Default` = 'Yes'

Search

    Post Directory