Java-Note

InnoDB Architecture (opens new window)

innodb-architecture

# 数据库中的事务控制

# 事务相关知识

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

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

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

  • 事务的 ACID 是通过 InnoDB 日志和锁来保证：事务的隔离性是通过数据库锁的机制实现的，原子性和持久性通过 redo log (opens new window)（重做日志）来实现，一致性通过 undo log (opens new window)（回滚日志）来实现

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

  • 事务控制的命令：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（可重复读），并且通过间隙锁（next-key locking）策略防止幻读的出现

事务的隔离级别

# 数据库锁

  • 根据加锁范围，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 的行锁模式及加锁方法

  • 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 将对表中的所有记录加锁，实际效果跟表锁一样

  • 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%，推荐使用乐观锁，否则使用悲观锁

# 数据库实现事务隔离的两种方式

  • 一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改
  • 另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取，从用户的角度来看，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（MultiVersion Concurrency Control，简称 MVCC 或 MCC）

# 多版本并发控制

  • InnoDB 的 MVCC 是通过在每行记录后面保存两个隐藏的列来实现的，一个保存了行创建时的系统版本号（行版本号），一个保存行删除时的系统版本号（行删除标识）

    • select 时，InnoDB 只查找符合条件的数据行：行版本号小于或等于当前事务版本，并且行删除标识为未定义或大于当前事务版本号，且两个版本号对应的事务都已经提交
    • update 时，InnoDB 为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识

  • 注意：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）就停止匹配，比如 a = 1 and b = 2 and c > 3 and d = 4，如果建立 (a, b, c, d) 顺序的索引，d 是用不到索引的，如果建立 (a, b, d, c) 的索引则都可以用到，a, b, d 的顺序可以任意调整
      2. = 和 in 可以乱序，比如 a = 1 and b = 2 and c = 3 建立 (a, b, c) 索引可以任意顺序，MySQL 的查询优化器会优化成索引可以识别的形式
    • 索引覆盖：所需要的数据只需要在索引即可全部获得，不需要再到表中取数据
    • 索引下推 (opens new window)（index condition pushdown）：在 MySQL 5.6 引入，在索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

  • 前缀索引：对于列的值较长，比如 blob、text、varchar，将值的前一部分作为索引

# InnoDB 存储数据的方式

InnoDB数据页的结构

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

# B+ 树索引

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

# 聚集索引 / 聚簇索引（clustered index）

InnoDB聚集索引示意图

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

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

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

  • B+ 树的特点包括：

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

  • 主键应尽量越短越好

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

单表数据量太大为什么查询会变慢？
如果 B+ 树想存储更多的数据，那树结构层级就会更高，查询一条数据时，需要经历的磁盘 IO 变多，因此查询性能变慢。

# 二级索引 / 辅助索引 / 非聚集索引（secondary index）

InnoDB辅助索引示意图

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

# 哈希索引

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

# 全文检索（Full-Text Search）

  • 全文检索通常使用倒排索引（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. 使用非等值查询的时候 MySQL 无法使用 Hash 索引
  6. 如果 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)

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

# MySQL 执行 DQL 的流程

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

MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 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 即可全部获得，而不需要再到表中取数据，即表示 SQL 使用了覆盖索引，性能较好
    2. Using index condition：索引下推优化
    3. Using temporary：使用临时表保存中间结果，主要常见于 group by 和 order by 等操作中
    4. Using where：如果不是读取表的所有数据，或者不是仅仅通过索引就可以获取所有需要的数据，则会出现 Using where 信息
    5. Using join buffer (Block Nested Loop)
    6. Using filesort：当查询中包含 order by 操作，而且无法利用索引完成排序操作的时候，MySQL 查询优化器不得不选择相应的排序算法来实现
    7. Distinct：查找 distinct 值，所以当 MySQL 找到了第一条匹配的结果后，将停止该值的查询而转为后面其他值的查询
    8. Full scan on NULL key：子查询中的一种优化方式，主要在遇到无法通过索引访问 null 值的使用
    9. Not exists：在某些左连接中 MySQL 查询优化器所通过改变原有查询的组成而使用的优化方法，可以部分减少数据访问次数
    10. No tables：查询语句中使用 from dual 或者不包含任何 from 子句
    11. Impossible WHERE noticed after reading const tables：MySQL 查询优化器通过收集到的统计信息判断出不可能存在结果
    12. Select tables optimized away：当我们使用某些聚合函数来访问存在索引的某个字段的时候，MySQL 查询优化器会通过索引而直接一次定位到所需的数据行完成整个查询。当然，前提是在查询中不能有 group by 操作。如使用 min() 或者 max() 的时候
    13. Using index for group-by：数据访问和 Using index 一样，所需数据只需要读取索引即可，而当查询中使用了 group by 或者 DISTINCT 子句的时候，如果分组字段也在索引中，Extra 中的信息就会是 Using index for group-by
    14. Using where with pushed condition：这是一个仅仅在 NDBCluster 存储引擎中才会出现的信息，而且还需要通过打开 Condition Pushdown 优化功能才可能会被使用，控制参数为 engine_condition_pushdown

# 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 复制

# 复制的工作原理

MySQL复制过程

  1. 主库（master）把数据更改记录到二进制日志（bin log）中
  2. 从库（slave）I/O 线程跟主库建立一个普通的客户端连接，将主库上的二进制日志内容复制到自己的中继日志（relay log）中（主库发送信号量通知其有新的事件产生）
  3. 从库 SQL 线程将中继日志中新增加的日志内容解析成 SQL 语句，并在自身从数据库上按顺序执行这些 SQL 语句（称为重放）

# 主要功能

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

# 复制方式

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

  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性能

  • 单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表

# Mycat (opens new window)

Mycat架构

  • Mycat 核心配置文件

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

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

# Sharding-JDBC

MySQL优化

Updated at: 2024-05-28 22:31:45

← 02 JDBC →