Java-Note

InnoDB Architecture (opens new window)

innodb-architecture

# 数据库中的事务控制

# 事务相关知识

  • 事务（Transaction，简写为 tx）：由一步或几步数据库操作（DML 语句）序列组成的逻辑执行单元，这系列操作要么全部执行，要么全部放弃执行（一组原子性的 SQL 查询）

  • 事务的 4 个特性（ACID 性）：原子性、一致性、隔离性、持续性

    1. 原子性（Atomicity）：事务是数据库的逻辑工作单位，事务中包含的所有操作要么全部提交成功，要么全部失败回滚
    2. 一致性（Consistency）：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态
    3. 隔离性（Isolation）：一个事务所做的修改在最终提交以前，对其它事务是不可见的
    4. 持久性（Durability）：也称永久性，一个事务一旦提交，则其所做的修改就会永久保存到数据库中，接下来的其它操作或故障不会对其执行结果有任何影响

  • MySQL InnoDB 通过 undo log (opens new window)（回滚日志，逻辑日志）实现原子性，通过锁机制和 MVCC 实现隔离性，通过 redo log (opens new window)（重做日志，物理日志）和 WAL 实现持久性，这三者协同工作，并与数据库约束和应用逻辑共同确保了一致性

  • 预写式日志（Write-Ahead Logging, WAL）机制：在修改表的数据时，先需要修改其内存拷贝，再把该修改行为记录到重做日志 Buffer（redo log buffer）中，在事务结束后将 redo log 写入磁盘，并通知文件系统刷新缓存中的数据页到磁盘文件

  • 事务控制的命令：begin 或 start transaction、commit、rollback

  • 事务自动提交：

    1. MySQL 默认采用自动提交（autocommit = 1）模式，即如果不是显式地开始一个事务，则每个查询都被当作一个事务执行提交操作
    2. 在执行 DDL、DCL 操作之前会强制执行 commit 提交当前的活动事务

  • 事务并发可能会导致的问题：

    1. 脏读：一个事务读到另一个事务未提交的更新数据
    2. 不可重复读：一个事务两次读同一行数据，期间有另一个事务提交了更新，导致这两次读到的数据不一样
    3. 幻读：一个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当再次读取该范围的记录时，会产生幻行
    4. 第一类丢失更新（回滚丢失）：撤消一个事务时，把其它事务已提交的更新的数据回滚掉了
    5. 第二类丢失更新（覆盖丢失）：提交一个事务时，把其它事务已提交的更新的数据覆盖了

    不可重复读是针对数据库的单一条记录；幻读不是针对一条数据库记录而言，而是多条记录

  • 事务隔离级别：

    1. 读未提交：事务中的修改，即使没有提交，对其它事务也都是可见的
    2. 读已提交：一个事务开始时，只能“看见”其它已经提交的事务所做的修改
    3. 可重复读：同一个事务中多次读取同样记录的结果是一致的，当 A 事务修改了一条记录但未提交时，B 事务将不允许修改这条记录（会被阻塞，innodb_lock_wait_timeout，默认是 50s）
    4. 可串行化：事务顺序执行，事务在读取的每一行数据上都加锁

事务的隔离级别

  • InnoDB 默认的事务隔离级别是 Repeatable Read（RR，可重复读），并且通过间隙锁（gap lock，RR 级别特有）策略防止幻读的出现
  • InnoDB 的锁机制（写操作时施加排他锁）防止了回滚丢失的发生
  • 在实际开发中，防止覆盖丢失通常需要应用层采用乐观锁（如版本号机制）或悲观锁（select ... for update）等策略来应对
  • InnoDB 的并发控制机制
    • MVCC（多版本并发控制）
      • 只在 RC 和 RR 两个隔离级别下工作
      • 确保事务只能读取已提交的数据版本
      • 解决脏读、不可重复、普通 select 时的幻读（快照读）
    • 锁机制：
      • 行锁（record lock）：锁定单行记录
        • 写操作时需要获得该数据的排他锁，确保在事务修改数据期间，其他事务无法修改相同数据，避免回滚丢失
      • 间隙锁（gap lock）：锁定一个范围的索引记录间隙，防止其他事务在间隙中插入新数据，从而有效防止幻读
      • 临键锁（next-key lock）：行锁 + 间隙锁的组合，锁定一个范围及其本身的行
      • 意向锁（intention lock）：表级锁，表明事务稍后将对表中的行施加哪种类型的锁（共享或排他），用于快速判断表内是否有行被锁定

# 数据库锁

  • 根据加锁范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类

# 全局锁

  • 主要用在全库备份过程中，对所有表进行全局读锁定 flush tables with read lock (FTWRL)
  • 在备份过程中整个库完全处于只读状态，客户端断开连接后，MySQL 会自动释放这个全局锁

# 表级锁

  • MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)

# 表锁

  • lock tables tb1_name [as alias] {read [local] | [low_priority] write} [, tb1_name [as alias] {read [local] | [low_priority] write}] ...：锁定用于当前线程的表，如果一个线程获得对一个表的 read 锁定，该线程（和所有其它线程）只能从该表中读取；如果一个线程获得对一个表的 write 锁定，只有保持锁定的线程可以对表进行写入，其它的线程被阻止，直到锁定被释放时为止
  • unlock tables：释放被当前线程保持的任何锁定

# 元数据锁（MDL）

  • 当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁
  • MDL 不需要显式使用，在访问一个表的时候会被自动加上

# 行锁

  • 行锁是在引擎层由各个引擎自己实现的

# 行锁模式及加锁方法

  • InnoDB 实现了以下两种类型的行锁：

    • 共享锁（S Lock）：允许一个事务去读一行数据，阻止其它事务获得相同数据集的排他锁
    • 排他锁（X Lock）：允许获得排他锁的事务更新数据，阻止其它事务取得相同数据集的共享读锁和排他写锁

  • 另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁

    • 意向共享锁（IS Lock）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁
    • 意向排他锁（IX Lock）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁

InnoDB行锁模式兼容性列表

  • 如果一个事务请求的锁模式与当前的锁兼容，InnoDB 就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放

  • 两阶段锁协议：在 InnoDB 事务中，行锁是在需要的时候才加上，要等到事务结束时才释放，因此如果事务中需要锁多个行，应把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放

  • 加锁方法

    1. 意向锁是 InnoDB 自动加的，不需用户干预
    2. 对于 update、delete 和 insert 语句，InnoDB 会自动给涉及数据集加排他锁（X）
    3. 对于普通 select 语句，InnoDB 不会加任何锁
    4. 事务可以通过以下语句显式给记录集加共享锁或排他锁
      • 共享锁（S）：select * from table_name where ... lock in share mode
      • 排他锁（X）：select * from table _name where ... for update

# 行锁实现方式

  • InnoDB 的行锁是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB 将通过隐藏的聚簇索引来对记录加锁

  • 如果不通过索引条件检索数据，那么 InnoDB 将对表中的所有记录加锁，实际效果跟表锁一样

  • InnoDB 行锁分为 3 种情形：

    1. Record Lock：对索引项加锁
    2. Gap Lock：对索引项之间的“间隙”、第一条记录前的“间隙”或最后一条记录后的“间隙”加锁
    3. Next-Key Lock：前两种的组合，对记录及其前面的间隙加锁

  • 当使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙（GAP），为了防止幻读以及保证恢复和复制的正确性，InnoDB 也会对这个间隙加锁，这种锁机制就是所谓的 Next-Key 锁

# 死锁和死锁检测

  • 死锁：两个事务都需要获得对方持有的排他锁才能继续完成事务，即循环锁等待

  • 发生死锁后，有两种方式解除：

    • 一种是锁等待超时（默认 innodb_lock_wait_timeout = 50，单位：s），Lock wait timeout exceeded; try restarting transaction
    • 另一种是死锁检测，InnoDB 自动检测到死锁后（默认 innodb_deadlock_detect = on），主动回滚死锁链条中的某一个事务，让其它事务得以继续执行，Deadlock found when trying to get lock; try restarting transaction

死锁检测要耗费大量的 CPU 资源，因为每个新来的被堵住的线程，都需要判断会不会由于自己的加入导致了死锁
热点行更新导致的性能问题的解决方法：控制并发度，对于相同行的更新，在进入引擎之前排队，或者将该热点行改成逻辑上的多行

  • 避免死锁：不同的程序尽量约定以相同的顺序来访问表

# 并发控制机制

  1. 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作，即在查询的时候给这个数据上锁，这个锁排斥其他的修改锁，等到这个线程提交了或者回滚了，其他线程要查这个数据才能往下查
    使用数据库自身的排它锁机制（写锁）：DML 操作自动会加上排它锁，DQL 操作需要手动加上排它锁：select * from 表名 for update

  2. 乐观锁：假设不会发生并发冲突，只在提交更新操作时检查是否违反数据完整性（乐观锁不能解决脏读的问题）

    • 在表中额外增加一个列（整数类型），用来表示修改的版本号，修改一次就把版本增加 1，且在提交更新操作时检查版本号使用与之前查询出来的版本号一致（通过判断执行更新操作后的影响行数是否为 0）
    • 通过重入机制降低操作失败的概率，需要使用按时间戳或者限制重入次数，避免多次的重入带来过多执行 SQL 的问题
    select id, name, version from person where id = 10;
update person set name  = 'java', version =  version + 1
where id = 10 and version = #{version};
    1
    2
    3
  • 并发量不大且不允许脏读（如资金相关的金融敏感信息），应使用悲观锁
  • 并发量非常大，悲观锁会带来非常大的性能问题，应选择使用乐观锁
  • 如果每次访问冲突概率小于 20%，推荐使用乐观锁，否则使用悲观锁

# 数据库事务隔离的实现

  • **锁机制：**在读取数据前，对其加锁，阻止其他事务对数据进行修改
  • MVCC（多版本并发控制）：通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取，从用户的角度来看，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（MultiVersion Concurrency Control，简称 MVCC 或 MCC）
  • 区分快照读与当前读
    • **快照读：**普通的 select ... 语句，基于 MVCC 和 Read View，无锁，一致性读
    • **当前读：**加锁的读，如 select ... lock in share mode、select ... for update，以及 insert、update、delete 语句，这些操作会读取数据的最新版本并施加锁（临键锁等）

# 多版本并发控制

  • InnoDB 的 MVCC 实现原理

    • 每行数据都包含两个隐藏字段：
      • DB_TRX_ID：记录最后修改（包括 insert、update、delete）该行数据的事务ID
      • DB_ROLL_PTR：回滚指针，指向该行数据在 undo log 中的上一个历史版本
    • undo log：每次对数据进行修改（update、delete）时，InnoDB 都会将旧版本的数据拷贝到 undo log 中，并通过回滚指针将这些版本连接起来，形成一个版本链
    • read view：当事务执行快照读（普通 select 语句）时，会生成一个 Read View（读视图），用来判断当前事务能看到版本链中的哪个数据版本

  • 注意：MVCC 只在 RC 和 RR 两个隔离级别下工作

  • InnoDB 中，每个事务或者语句有自己的一致性读视图（consistent read view），普通查询语句是一致性读（consistent read）

  • 在 RR 隔离级别下，

    • 使用 begin/start transaction 启动事务，一致性视图是在执行第一个快照读语句时创建的，之后事务里的其它查询都共用这个一致性视图
    • 使用 start transaction with consistent snapshot 启动事务，一致性视图是在事务启动时创建的，之后事务里的其它查询都共用这个一致性视图

  • 在 RC 隔离级别下，无论以哪种方式启动事务，每一个语句执行前都会重新算出一个新的视图

  • 更新数据都需要先读后写，而这个读是当前读（current read），即总是读取已经提交完成的最新版本

  • 更新数据时如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待

# InnoDB 索引 (opens new window)

  • 索引是对数据库表中一列或多列的值进行排序的一种结构

# 索引分类

InnoDB存储引擎索引特性

  • 主键索引

  • 唯一索引：唯一索引与主键索引的区别是，唯一索引允许为 NULL

  • 普通索引

  • 联合索引/复合索引：对表上的多个列进行索引

    联合索引数据结构

    • 最左匹配原则：
      1. 在使用复合索引时，MySQL 会根据索引中列的顺序，从最左边的列开始向右匹配查询条件，直到遇到范围查询（如 >、<、between、like、in）或无法匹配的条件就停止匹配
      2. 比如 a = 1 and b = 2 and c > 3 and d = 4，如果建立 (a, b, c, d) 顺序的索引，d 是用不到索引的，如果建立 (a, b, d, c) 的索引则都可以用到，a, b, d 的顺序可以任意调整
      3. = 和 in 可以乱序，比如 a = 1 and b = 2 and c = 3，建立 (a, b, c) 索引可以任意顺序，MySQL 的查询优化器会优化成索引可以识别的形式（等值查询条件）
      4. like 查询只有在通配符 % 不出现在开头时才能利用索引
    • 索引覆盖：索引包含了查询所需的所有字段，无需回表
    • 索引下推 (opens new window)（index condition pushdown）：在 MySQL 5.6 引入，将部分 where 子句的过滤条件下推到存储引擎层去执行，在索引遍历过程中，对联合索引中包含的字段先做判断，提前过滤掉不满足条件的记录，减少回表次数和服务器层的数据处理量

  • 前缀索引：对于列的值较长，比如 text、varchar，只对值的前 N 个字符建立索引，而非整个字段，来在查询效率和存储空间之间取得平衡（如果不指定 N，默认使用全字段长度创建索引）

# 存储数据的方式

InnoDB数据页的结构

  • 为了减少磁盘随机读取次数，InnoDB 采用页而不是行的粒度来保存数据，即数据被分成若干页，以页为单位保存在磁盘中。InnoDB 的页大小（innodb_page_size），默认 16KB。
  • 对于 4KB、8KB、16KB、32KB 的 innodb_page_size，最大行长略低于数据库页面的一半。如果行超过最大行长度，则将可变长度的列用外部页存储，直到该行符合最大行长度限制。
  • 各个数据页组成一个双向链表，每个数据页中的记录按照主键顺序组成单向链表；每一个数据页中有一个页目录，方便按照主键查询记录；页目录通过槽把记录分成不同的小组，每个小组有若干条记录。
  • 页中的记录都是按照索引值从小到大的顺序存放的，新增记录就需要往页中插入数据，现有的页满了就需要新创建一个页，把现有页的部分数据移过去，这就是页分裂；如果删除了许多数据使得页比较空闲，还需要进行页合并。页分裂和合并，都会有 IO 代价，并且可能在操作过程中产生死锁。

# B+ 树索引

B+ 树索引适用于全键值、键值范围或键前缀查找

两种索引的非叶子节点都是只存索引数据；聚簇索引的叶子节点存的是行数据的所有字段信息，非聚簇索引的叶子节点存的是行数据所对应的主键和索引列信息。

# 聚集索引 / 聚簇索引

InnoDB聚集索引示意图

  • InnoDB 存储引擎表是索引组织表，即表中数据按照主键顺序存放

  • 聚集索引（clustered index）：按照表的主键（如果没有主键，就选择第一个非空唯一索引，或隐式定义一个主键）构造一棵 B+ 树，同时叶子节点中存放整张表的行记录数据，也将聚集索引的叶子节点称为数据页

  • 每张表只能拥有一个聚集索引

  • B+ 树的特点包括：

    • 最底层的节点叫作叶子节点，用来存放数据
    • 其他上层节点叫作非叶子节点，仅用来存放目录项（索引数据），作为索引
    • 非叶子节点分为不同层次，通过分层来降低每一层的搜索量
    • 所有节点按照索引键大小排序，构成一个双向链表，加速范围查找

  • 主键应尽量越短越好

    • 聚集索引以外的索引称为二级索引。在 InnoDB 中，二级索引中的每条记录都包含该行的主键列，以及为二级索引指定的列。 InnoDB 使用这个主键值来搜索聚集索引中的行。如果主键很长，二级索引会占用更多的空间，所以主键短是有利的。

  • 单表数据量太大为什么查询会变慢？

    • B+ 树的查询需从根节点逐层访问至叶子节点，每层需一次磁盘I/O
    • 如果 B+ 树想存储更多的数据，那树结构层级就会更高，查询一条数据时，需要经历的磁盘 IO 变多，因此查询性能变慢
    • InnoDB 中 B+ 树的高度一般保持在三层以内比较好

# 二级索引 / 辅助索引 / 非聚集索引

InnoDB辅助索引示意图

  • 二级索引（secondary index）的叶子节点中保存的不是实际数据，而是主键，获得主键值后去聚集索引中获得数据行（这个过程叫回表）

# 哈希索引

  • InnoDB 存储引擎支持的哈希索引是自适应的，InnoDB 存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引
  • InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature. InnoDB Storage Engine Features (opens new window)

# 全文检索

  • 全文检索通常使用倒排索引（inverted index）来实现
  • 在辅助表（auxiliary table）中存储单词与单词自身在一个或多个文档中所在位置之间的映射
  • 通常利用关联数组，其表现形式：{word, Documentld}（inverted file index）或者 {word, (Documentld, Position) }（full inverted index）
  • InnoDB 存储引擎采用 full inverted index 的方式：索引表中的每一项都包括一个属性值和具有该属性值的各记录的地址

# 索引合并

  • 从 5.0 版本开始引入了 index_merge 索引合并优化
  • index_merge 作用：
    1. 索引合并是把几个索引的范围扫描合并成一个索引
    2. 索引合并的时候，会对索引进行并集，交集或者先交集再并集操作，以便合并成一个索引
    3. 这些需要合并的索引只能是一个表的，不能对多表进行索引合并

# 索引的利弊

  • 索引的好处

    1. 提高表数据的检索效率
    2. 如果排序的列是索引列，大大降低排序成本
    3. 在分组操作中如果分组条件是索引列，也会提高效率

  • 索引需要额外的维护成本：当数据做 update、insert、delete 时，也需对相关索引数据进行处理，因此会降低 update、insert、delete 效率

# 建立与使用索引

  • 按需创建

  • 在经常用作过滤条件或进行 order by、group by 的字段上建立索引

  • 在用于连接的列（主键/外键）或排序的列上建立索引

  • 不要在选择性非常差的字段上建立索引

  • 对于经常更新的列避免建立索引

  • 在经常存取的多个列上建立复合索引，但要注意复合索引的建立顺序要按照使用的频度来确定

  • 建立的索引支持多种过滤条件：在索引中加入更多的列，并通过 in( ) 的方式覆盖那些不在 where 子句中的列

  • 避免多个范围条件：对于范围条件查询，MySQL 无法再使用范围列后面的其他索引列

  • 对字符串类型的字段建立索引时，必须指定索引长度（一般长度为 20 的索引，区分度会高达 90%以上）

  • 在被驱动表建索引：left join 为右表加索引；right join 为左表加索引；inner join 大表加索引

  • 重建索引

    1. 重建主键索引：alter table t engine=InnoDB
    2. 重建普通索引：alter table t drop index k; alter table t add index(k);

  • 使用 force index 强行选择一个索引，如 select * from t force index(k)

# 索引的限制

  1. blob 和 text 类型的列只能创建前缀索引
  2. 索引列上使用了函数运算后（如 abs(column)），无法使用索引
  3. 使用不等于（!= 或者 <>）的时候 MySQL 无法使用索引
  4. 使用 like 操作的时候如果条件以通配符开始（'%abc...'），无法使用索引
  5. 使用 or 连接条件，且只有 or 一侧字段有索引，无法使用索引
  6. 使用非等值查询的时候 MySQL 无法使用 Hash 索引
  7. 如果 MySQL 估计使用全表扫描要比使用索引快，则不使用索引

数据库基于成本决定是否走索引（CBO，Cost Based Optimizer），成本包括 IO 成本和 CPU 成本：

  1. IO 成本，是从磁盘把数据加载到内存的成本。默认情况下，读取数据页的 IO 成本常数是 1（也就是读取 1 个页成本是 1）。
  2. CPU 成本，是检测数据是否满足条件和排序等 CPU 操作的成本。默认情况下，检测记录的成本是 0.2。

查看表的统计信息：show table status like 'table_name';

全表扫描的成本 = 聚簇索引占用的页面数（记录占用的总字节数 Data_length 除以页大小） + 表中的记录数（Rows） * 0.2

# 查询性能优化 (opens new window)

  • 遵循一些原则让优化器能够按照预想的合理的方式运行
  • 不做、少做、快速地做

# DQL 执行流程

  1. 客户端发送一条查询给服务器
  2. 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果，否则进入下一阶段（如果查询缓存是打开的）
  3. 解析器、预处理器对 SQL 语句进行解析、预处理，再由优化器生成对应的执行计划（一棵指令树）
  4. 执行器根据优化器生成的执行计划，调用存储引擎的 API 来执行查询
  5. 将结果返回给客户端，并存放到查询缓存中

MySQL 8.0 版本直接将查询缓存（Query Cache）的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。因为在高并发环境下容易导致严重的锁竞争，且一旦底层表数据有任何变化，所有相关的缓存都会失效，维护缓存一致性的开销常常大于其带来的性能收益。

# 性能优化相关命令

# EXPLAIN 命令 (opens new window)

  • 使用方式：explain 待执行的SQL
  • id：执行查询的序列号，select 子句的编号，select 子句分为简单查询和复杂查询（共 3 种：简单子查询、派生表查询、union 查询）
    • id 如果相同，可以认为是一组，从上往下顺序执行
    • 在所有组中，id 值越大，优先级越高，越先执行
    • id 为 NULL 最后执行
  • select_type：select 子句使用的查询类型
    1. SIMPLE：简单的 select 查询，查询中不包含子查询或者 union
    2. PRIMARY：子查询中的最外层查询，查询中若包含任何复杂的子部分，最外层的 select 被标记为 primary
    3. SUBQUERY：子查询内层查询的第一个 select，结果不依赖于外部查询结果集
    4. DEPENDENT SUBQUERY：子查询中内层的第一个 select，依赖于外部查询的结果集
    5. DERIVED：在 from 子句的子查询中的 select 被标记成 derived（派生表）
    6. UNION：union 语句中第二个 select 开始的后面所有 select，第一个 select 为 PRIMARY
    7. DEPENDENT UNION：子查询中的 union，且为 union 中从第二个 select 开始的后面所有 select，同样依赖于外部查询的结果集
    8. UNCACHEABLE SUBQUERY：结果集无法缓存的子查询
    9. UNION RESULT：union 中的合并结果
  • table：本次查询访问的数据表，从上到下，代表 SQL 优化器选择的表 join 顺序
  • type：对表所使用的访问方式：all | index | range | ref | eq_ref | const, system | null，从左至右，性能由最差到最好（一般需保证查询至少达到 range 级别）
    1. all：全表扫描
    2. index：全索引扫描（遍历整个索引树来查询匹配的行）
    3. rang：对索引进行范围检索
    4. ref：使用非唯一索引扫描或唯一索引的前缀扫描
    5. eq_ref：最多只会有一条匹配结果，一般是通过主键或者唯一键索引来访问
    6. ref_or_null：与 ref 的唯一区别就是在使用索引引用查询之外再增加一个空值的查询
    7. index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行 merge 之后再读取表数据
    8. index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或者唯一索引
    9. unique_subquery：子查询中的返回结果字段组合是主键或者唯一约束
    10. const：读常量，且最多只会有一条记录匹配，由于是常量，所以实际上只需要读一次
    11. system：系统表，表中只有一行数据
  • possible_keys：查询时可能使用的索引（有助于高效查找行的索引），如果没有使用索引，为 null
  • key：出于最小化查询成本考虑，SQL 优化器实际使用的索引
  • key_len：使用到索引字段的长度（字节数）
  • ref：表之间的引用，显示索引的哪一列被使用了，哪些列或常量被用于查找索引列上的值。过滤的方式，比如 const（常量），column（join），func（某个函数）
  • rows：扫描行的数量，根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数（通过收集到的统计信息估算，重新统计索引信息 analyze table t）
  • filtered：针对表里符合某个条件（where 子句或联接条件）的记录数的百分比所做的一个悲观估算，把 rows 列和这个百分比相乘可以得到 MySQL 估算它将和查询计划里前一个表关联的行数
  • Extra：查询中每一步实现的额外细节信息，sql 解析的额外信息，例如当出现 using filesort、using temporary、using where 时，查询需要优化
    1. Using index：覆盖索引，即所需要的数据只需要在 Index 即可全部获得，无需回表，性能较好
    2. Using index condition：索引下推，即在存储引擎层利用索引来过滤数据，减少了回表次数
    3. Using temporary：使用临时表保存中间结果，主要常见于 group by 和 order by 等操作中，通常需优化
    4. Using where：如果不是读取表的所有数据，或者不是仅仅通过索引就可以获取所有需要的数据，则会出现 Using where 信息，表示服务器层在存储引擎返回行后，又使用了 where 条件进行过滤
    5. Using join buffer (Block Nested Loop)、Using join buffer (Batched Key Access)、Using join buffer (hash join)：连接查询时使用了连接缓冲区，通常意味着连接字段可能缺少有效索引
    6. Using filesort：当查询中包含 order by 操作，而且无法利用索引完成排序操作的时候，MySQL 查询优化器不得不选择相应的排序算法来实现，通常需优化
    7. Using intersect(...)、Using union(...)、Using sort_union(...)：索引合并，合并类型（交集、并集、排序并集）
    8. Distinct：查找 distinct 值，所以当 MySQL 找到了第一条匹配的结果后，将停止该值的查询而转为后面其他值的查询
    9. Full scan on NULL key：子查询中的一种优化方式，主要在遇到无法通过索引访问 null 值的使用
    10. Not exists：在某些左连接中 MySQL 查询优化器所通过改变原有查询的组成而使用的优化方法，可以部分减少数据访问次数
    11. No tables：查询语句中使用 from dual 或者不包含任何 from 子句
    12. Impossible WHERE noticed after reading const tables：MySQL 查询优化器通过收集到的统计信息判断出不可能存在结果
    13. Select tables optimized away：当我们使用某些聚合函数来访问存在索引的某个字段的时候，MySQL 查询优化器会通过索引而直接一次定位到所需的数据行完成整个查询。当然，前提是在查询中不能有 group by 操作。如使用 min() 或者 max() 的时候
    14. Using index for group-by：数据访问和 Using index 一样，所需数据只需要读取索引即可，而当查询中使用了 group by 或者 DISTINCT 子句的时候，如果分组字段也在索引中，Extra 中的信息就会是 Using index for group-by
    15. Using where with pushed condition：这是一个仅仅在 NDBCluster 存储引擎中才会出现的信息，而且还需要通过打开 Condition Pushdown 优化功能才可能会被使用，控制参数为 engine_condition_pushdown
  • 分析与优化方向
    • type 为 all 或 index：考虑为 where 子句中的条件字段或连接字段添加索引
    • key 为 NULL：表示未使用索引，需检查 possible_keys 并创建合适的索引
    • rows 值非常大：尝试通过添加索引或优化查询条件来减少需要扫描的行数
    • Extra 出现 Using temporary 或 Using filesort：尤其是对于 group by、order by 操作，考虑为排序和分组的字段添加索引
    • Extra 出现 Using filesort：考虑为 order by 子句中的字段添加索引

# show warnings

  • 显示在当前会话中执行语句所导致的条件（错误，警告和注释）的信息

# 使用 optimizer_trace 查看执行计划

set optimizer_trace = "enabled=on";
select * from person where name >'name84059' and create_time>'2020-01-24 05:00:00';
select * from information_schema.OPTIMIZER_TRACE;
set optimizer_trace = "enabled=off";
1
2
3
4

# 使用 profiling 分析 SQL 语句

  1. 在 Session 级别开启 profiling：set profiling = 1;
  2. 执行查询，在 profiling 过程中所有的 query 都可以记录下来
  3. 查看记录的 query：show profiles;
  4. 选择要查看的 profile：show profile for query 1;
  5. 选择要查看的 profile 的 cpu、block io 明细：show profile cpu, block io for query 1;

# 慢查询分析

  • https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html
  • 慢查询日志相关配置选项：
    • slow_query_log：设置是否打开慢查询日志的开关，默认值为 OFF
    • long_query_time：定义慢查询的时间，单位为秒，默认值为 10
    • slow_query_log_file: 设置慢查询日志文件的路径，如果没有指定，文件名为 {host_name}-slow.log
    • log_output='FILE' 将日志存入文件；log_output='TABLE' 表示将日志存入数据库（日志记录到系统的专用日志表比记录到文件耗费更多的系统资源）
    • log_queries_not_using_indexes：设置是否未使用索引的查询也被记录到慢查询日志中，默认值为 OFF

# 常见的查询优化

  • 优化表结构：适当使用冗余数据；大表拆小表，有大数据的列（如类型为 text）单独拆成一张表；把常用属性分离成小表

  • 字段尽可能地设置为 NOT NULL，值可为 NULL 的列使得索引、索引统计和值比较都更复杂，且需要额外的空间来记录值是否为 NULL

  • 切分查询：将大查询切分成小查询

  • 分解关联查询：对每一个表进行一次单表查询，然后将结果在应用层中进行关联
    优点：让缓存的效率更高，执行单表查询可以减少锁的竞争，可以对数据库进行拆分，可以使用 in( ) 代替关联查询

  • 优化关联查询：考虑到关联的顺序，确保 on 或者 using 子句中的列上有索引；确保任何的 group by 和 order by 中的表达式只涉及到一个表中的列；小结果集驱动大结果集；在 on 或者 using 子句后加上过滤条件对右边表做过滤

  • 优化子查询：尽可能使用关联查询代替

  • 优化 count() 查询：使用 count(*) 统计行数；使用近似值；增加汇总表

  • 优化 limit 分页：利用延迟关联或者子查询优化超多分页场景

    • 延迟关联：当 offset 特别大时，先快速定位需要获取的 id 段，然后再关联，即 select a.* from table_1 a, (select id from table_1 where 条件 limit 100000, 20) b where a.id = b.id，注意索引需要完全覆盖才有优化效果
    • 游标分页：通过把分页限定条件优化为筛选条件，降低了分页起始位置

  • 其它
    尽量避免使用负向查询：not、!=、<>、!<、!>、not exists、not in、not like 等
    尽量避免在 where 子句中使用 or 来连接条件，同一字段改用 in，不同一字段改用 union
    尽量避免 in 操作，无法避免时，需要控制 in 后边的集合元素数量在 1000 个之内

手动改用 join 的方式优化含子查询的 update 或者 delete 单表修改语句

A limitation on UPDATE and DELETE statements that use a subquery to modify a single table is that the optimizer does not use semijoin or materialization subquery optimizations. As a workaround, try rewriting them as multiple-table UPDATE and DELETE statements that use a join rather than a subquery.（使用子查询修改单个表的 UPDATE 和 DELETE 语句时，优化器不使用 semijoin 或 materialization 优化子查询。作为解决方法，尝试将它们重写为使用 join 而不是子查询的多表 UPDATE 和 DELETE 语句。）

https://dev.mysql.com/doc/refman/5.7/en/subquery-optimization.html

# MySQL 复制 (opens new window)

# 复制的工作原理

MySQL复制过程

  1. 主库记录变更：主库（master）把数据更改记录到二进制日志（bin log）中
  2. 从库（slave）I/O 线程跟主库建立连接，将主库上的二进制日志内容复制到自己的中继日志（relay log）中
    • 从库发起请求：从库上的 I/O 线程会主动连接到主库，并向主库请求从上次记录的位置或指定的 GTID 开始获取 bin log 内容。
    • 主库响应请求：主库在接收到从库的请求后，会启动一个 Binlog Dump 线程（Binlog Dump Thread），将请求的 bin log 内容发送给从库。每个连接到主库的从库，主库都会为其创建一个独立的 Binlog Dump 线程。
    • 从库接收写入：从库的 I/O 线程接收到主库发来的 bin log 事件后，会将其写入到本地的中继日志（relay log）中。
    • 当连接建立后，主库的 Binlog Dump 线程在 bin log 有新增时，会主动将新增的日志事件发送给已连接的从库。
  3. 从库应用变更：从库 SQL 线程将中继日志中新增加的日志内容解析成 SQL 语句，并在自身从数据库上按顺序执行这些 SQL 语句（称为重放）

# binlog 格式

  • STATEMENT（基于 SQL 语句）：记录执行的 SQL 原文
    • 优点：日志量小，节省磁盘 I/O（例如批量更新仅记录一条 SQL）
    • 缺点：可能引发主从不一致（如使用 now()、uuid() 等非确定性函数时）
  • ROW（基于行数据变更）：记录每行数据的修改细节（如 id=1 的行从 name='Tom'变为 name='John'）
    • 优点：数据一致性高，避免函数或存储过程复制问题
    • 缺点：日志量巨大（例如 UPDATE 影响 100 万行会记录 100 万条变更）
  • MIXED（混合模式）：动态选择格式，普通 SQL 用 STATEMENT，复杂操作（如非确定性函数）自动切为 ROW
    • 平衡点：兼顾日志大小与一致性，但配置调试较复杂

SHOW VARIABLES LIKE 'binlog_format';

MySQL 5.7.7 及之后，默认格式改为 ROW

# canal (opens new window) 工作原理

  • canal 模拟 MySQL slave 的交互协议，伪装自己为 MySQL slave，向 MySQL master 发送 dump 协议
  • MySQL master 收到 dump 请求，开始推送 binary log 给 slave (即 canal )
  • canal 解析 binary log 对象（原始为 byte 流）

# 主要功能

  • 由于从库复制是异步的，所以从库上可能会存在脏数据（数据丢失、同步延迟）
  • 读 / 写分离：将不能容忍脏数据的读查询和写分配到分配到主库，其它的读（如评论、报表、日志等）查询分配到从库
  • 数据库备份、数据分布、读取的负载平衡、高可用性和故障转移

# 复制方式

  1. 异步复制（Asynchronous Replication）：主库在执行完客户端提交的事务后会立即将结果返回给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果 crash 掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。MySQL 主从复制默认采用异步复制方式。

  2. 全同步复制（Fully synchronous Replication）：当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

  3. 半同步复制 (opens new window)（Semisynchronous Replication）：介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到 relay log 中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个 TCP/IP 往返的时间。所以，半同步复制最好在低延时的网络中使用。（从 MySQL 5.5 开始，MySQL 以插件的形式支持半同步复制）

# 从库并行复制

  1. 基于 schema 的并行复制
  2. 基于 group commit 的并行复制
  3. 基于 write-set 的并行复制

# 使用 Keepalived 实现主从自动切换

# 使用代码实现

  • 使用 Spring 的 AbstractRoutingDataSource 实现多数据源切换
  • DataSource 以 key-value 形式存储在 targetDataSources，determineTargetDataSource() 方法动态获取当前数据源（数据源名称通过 determineCurrentLookupKey() 确定），如果当前数据源不存并且默认数据源也不存在就抛出异常
  • 根据传入的 key 值切换到对应的 DataSource 上

# 分库分表

云数据库MySQL_5.7性能

  • 阿里巴巴《Java开发手册》提出：单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表

# 分表方式

  • MySQL 的分表是解决单表数据量过大、提升数据库性能的常见方案，主要包括垂直分表和水平分表两种核心方式

# 垂直分表

  • 将原本一个包含很多列的大表，拆分成多个表。通常将访问频率高、占用空间小的核心字段放在一张主表（热数据）中；将访问频率低、占用空间大的字段放在另一张扩展表（冷数据）中。两者通过主键关联。
  • 优点：核心业务查询时能够更高效地利用数据库缓存，减少磁盘 I/O，因为单次读取的数据页可以容纳更多行热数据
  • 缺点：查询完整数据时需要关联多张表，增加 SQL 复杂度

# 水平分表

  • 将海量数据按照某种规则分散到多个结构相同的表中
  • 选择一个分表键（Sharding Key），并制定一个分片算法，根据该算法决定一条数据应该存入哪个子表
  • 常见的分片算法：
    • 范围分片：如按时间（创建年月）或 ID 区间划分
    • 哈希取模分片：对分表键（如用户 ID）进行哈希运算后取模
    • 一致性哈希分片：为解决哈希取模扩容难题而出现，能最大限度减少扩容时的数据迁移量
      • 哈希环
      • 节点映射、数据映射到虚拟节点
      • 定位规则：在环上，从数据映射到的位置出发，沿顺时针方向前进，遇到的第一个节点，就是该数据应该存储的节点
  • 优点：缓解单表的数据压力，提升查询和写入性能
  • 缺点：跨分片的查询（如没有分表键的条件查询、分页查询、聚合统计）会变得非常复杂和低效

# 中间件

# Mycat (opens new window)

Mycat架构

  • Mycat 核心配置文件

    • server. xml：配置连接 Mycat 的用户名、密码、数据库名
    • schema xml：配置 schema、datanode、datahost
    • rule. xml：分片规则

  • Mycat 常用的分片规则：取模范围分片、一致性 hash 分片、范围分片、枚举分片、取模分片、日期分片、按月分片、冷热数据分片

# ShardingSphere

MySQL优化

Updated at: 2025-10-27 01:39:57

← 02 JDBC →