note · 6,826 字

MySQL

MySQL:索引、事务、锁、MVCC、日志、慢查询与 SQL 优化。

MySQL笔记

MySQL

语法书写顺序

​​​​​​lligupialSELECT [DISTINCT] 列名          -- 1. 选择要查询的列
FROM 表名                      -- 2. 指定数据来源表
[JOIN 关联表 ON 连接条件]       -- 3. 关联其他表（若有）
[WHERE 行过滤条件]             -- 4. 过滤行数据（排除不满足条件的行）
[GROUP BY 分组字段]            -- 5. 按字段分组
[HAVING 分组过滤条件]          -- 6. 过滤分组结果（仅对分组后的数据生效）
[ORDER BY 排序字段 [ASC/DESC]]  -- 7. 对结果集排序
[LIMIT 限制返回行数]            -- 8. 限制最终返回的记录数（常用于分页）

如何进行慢查询优化

  1. 发现与定位 - 慢查询日志
  2. 深度分析 读懂Explain执行计划 type``possible_key``key``key_len Extra
  3. 大部分优化索引
    1. 避免索引失效
    2. 深度分页优化
    3. 不要select *触发覆盖索引 避免回表查询
  4. 架构与系统优化
    1. 引入缓存
    2. 读写分离 主从
    3. 大表数据同步至Elasticsearch``ClickHouse

MySQL超大分页处理

通过创建覆盖索引能够比较好地提高性能

  1. 先查询id（有主键索引），再到原来的表中查询。
  • 即覆盖索引加子查询的形式优化
select*
from tb_skt t,
    (select id from tb_sku order by id limit 90000000,10)a
where t.id=a.id;
  1. 游标分页

前端传Id给后端

-- 假设前端传来了上一页最后一条记录的 id 为 100500
SELECT * FROM orders WHERE id > 100500 ORDER BY id ASC LIMIT 10;
  1. 限制最大页数/Elasticsearch/Clickhouse

索引创建原则有哪些

  1. 针对于数据量较大，且查询比较频繁的表建立索引。单表超过 10 万数据（增加用户体验）。 重要
  2. 针对于常作为查询条件（**where**）、排序（**order by**）、分组（**group by**）操作的字段建立索引。 重要
  3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
  4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
  5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。 重要
  6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。 重要
  7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

什么情况下索引会失效？

通过explain判断索引是否失效了

  • 违反最左前缀法则（联合索引）
  • 范围查询右边的列，不能使用索引
  • 不要在索引列上进行任何操作（计算、函数、类型转换），索引将失效
  • 字符串不加单引号，造成索引失效
  • 以%开头的like模糊查询，索引失效

在实际开发中，判断索引到底有没有失效，或者为什么失效，不能只凭记忆这些规则。最靠谱的方法是在 SQL 语句前加上 **EXPLAIN** 关键字，通过查看执行计划中的 type、key、Extra 等字段，来看 MySQL 优化器最终是如何选择执行路径的。

一个SQL语句执行很慢，如何分析

最常见的是索引问题

  1. 没建索引
  2. 索引失效
  3. 优化器选错索引

〔图片缺失:本地路径不可用〕采用EXPLAIN 或者 DESC 命令获取 MySQL 如何执行SELECT语句信息

  • type 这条SQL的连接类型，性能由好到差为NULL、System、const、eq_ref、ref、range、index、all
  • possible_key 当前sql可能会使用到的索引
  • key 当前sql实际命中的索引
  • key_len 索引占用的大小
  • Extra
    • Using where;Using Index 查找使用了索引，不需要回表查询
    • Using Index condition 查找使用了索引，但是需要回表查询数据
    • Using filesort: 表示 MySQL 需要进行额外的排序操作，而不是按照索引顺序读取数据（通常需要优化）

SQL语句编写不当

  1. 无脑Select *
  2. 深度分页问题
  3. JOIN 遵循 小表驱动大表 原则

数据量、架构与系统资源

  1. 单表数据量过大 考虑分库分表

SQL优化的经验

  • 表的设计优化（阿里开发手册《嵩山版》）
    • 设置合适的数值（tinyint int bigint），根据实际情况选择。
    • 设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长，效率稍低。
  • 索引优化
    • 创建索引的原则
    • 失效情况
  • SQL语句优化
    • SELECT 语句务必指明字段名称（避免直接使用select *）
    • SQL 语句要避免造成索引失效的写法
    • 尽量用union all代替union，union会多一次过滤，效率低
    • 避免在where子句中对字段进行表达式操作
    • Join 优化：能用inner join就不用left join、right join；如必须使用，一定要以小表为驱动。内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边；left join或right join不会重新调整顺序
  • 主从复制、读写分离
  • 分库分表

InnoDB - 现代Mysql的绝对主力

  • 支持事务
  • 行级锁
  • MVCC 多版本并发控制
  • 崩溃恢复能力：Redo Log 和 Undo Log
  • 支持外键
  • 聚簇索引

其他引擎

  • MyISAM 5.5之前的默认引擎 不支持事务 不支持崩溃恢复 表级锁 非聚簇索引
  • Memoery 内存引擎 默认使用Hash索引 被Redis替代了

*锁机制

颗粒度分

  1. 全局锁
    • 锁定数据库中所有的表 数据备份
  2. 表级锁
    • 表锁 锁住整张表粒，度较大
    • 元数据锁 防止DML（增删改查）和DDL（表结构变更）冲突 隐式加的锁
    • 意向锁 避免加表锁时一行一行检查行锁的加锁情况 隐式
  3. 行级锁
    • 行锁 对单个记录加锁 RC和RR都支持
    • 间隙锁 锁的时记录间间隙 RR下才有
    • 临键锁 锁的时当前记录+记录前的间隙 配合间隙锁解决幻读问题

什么时候加行锁 什么时候加表锁

  • 行锁： 当你的 SQL 语句（如 UPDATE、DELETE、SELECT ... FOR UPDATE 等）在执行时，能够通过索引精确匹配到数据记录时，InnoDB 就会加行锁。
  • 表锁： 当你的 SQL 语句无法使用索引，或者 MySQL 优化器认为全表扫描比走索引更快时，为了保证数据一致性，InnoDB 会放弃行锁，直接退化/升级为表锁。

日志

BinLog - 服务器层面 Binary log （所有引擎共用）

（redo log是InnoDB存储引擎层特有）

二进制格式记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW）语句。

作用：

  1. MySQL 的主从复制。
  2. 数据恢复 (Point-in-Time Recovery)
  3. 异构数据同步 阿里巴巴Canal， Canal 会伪装成一个 MySQL 的从库，主动向 MySQL 主库发送 dump 请求

BinLog 与 RedoLog 区别

维度Binlog (归档日志)Redo Log (重做日志)
所属层次MySQL Server 层（所有引擎共用）InnoDB 存储引擎层特有
核心作用主从复制 (Replication) 与 数据恢复 (PITR)保证机器宕机后的 Crash-Safe（崩溃恢复）
日志格式逻辑日志（记录 SQL 语句或行数据变化）物理日志（记录“在某个数据页上做了什么修改” ，比如：将表空间 10、页 50、偏移量 100 处的值改为 2 ）
写入方式追加写（写满一个文件就换下一个，不覆盖）循环写（空间固定，写到末尾会绕回开头覆盖旧数据） 只能用来保证“当前正在进行的事务”不丢失

ErrorLog 错误日志

查询日志

慢查询日志

什么是索引

空间换时间 目录 底层是B+树

索引（Index）是帮助MySQL高效获取数据的数据结构

索引类型

主键索引针对于表中主键创建的索引默认自动创建，只能有一个PRIMARY
唯一索引避免同一个表中某数据列中的值重复可以有多个UNIQUE
普通索引快速定位特定数据可以有多个
全文索引全文索引查找的是文本中的关键词，而不是比较索引中的值可以有多个FULLTEXT
联合索引最左前缀匹配原则可以有多个
  • 按照物理存储分类
    • 聚簇索引
    • 非聚簇索引
  • 按照底层数据结构分类
    • B+树 InnoDB引擎 适合范围查询和排序
    • Hash索引 Memory引擎 单条等值查询极快 不支持范围查询和排序

索引的底层数据结构

B+树 默认存储引擎InnoDB就是B+树

  • 非叶子节点仅存指针，在一个数据页（16KB）中可以存更多指针，降低树的高度
  • 只有叶子节点才会挂载数据 效率稳定
  • 叶子节点之间有双向指针，方便扫库和区间查询

红黑树（二叉平衡查找树）的高度会很高，有很多层

聚簇索引与非聚簇索引

分类含义特点
聚集索引 （聚簇索引）(Clustered Index)将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据必须有，而且只有一个
二级索引 （非聚簇索引）(Secondary Index)将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键可以存在多个
  • 回表查询：先在二级索引查询，由次结果到聚集索引查询。

什么是覆盖索引

  • 查询使用了索引，并且需要返回的列在该索引中已经全部能够找到。
  • 即不需要回表查询。尽量避免Select*

事务的特性 ACID

  • 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败回滚。
    • undo log
  • 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
    • 其他三者共同保证
  • 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
    • 锁：共享锁和排他锁
    • MVCC 多版本并发控制 + 隔离级别
  • 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。即使数据库发生崩溃，提交的数据也不会丢失。
    • redo log

并发事务问题

问题描述
脏读一个事务读到另外一个事务还没有提交的数据。
不可重复读一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
幻读一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了 “幻影”。

解决方案：对事务进行隔离

隔离级别脏读不可重复读幻读
Read uncommitted 未提交读√√√
Read committed 读已提交×√√
Repeatable Read (默认) 可重复读××√
Serializable 串行化×××

x是解决了的意思 即某一种现象不会发生

undo log和redo log的区别

存储引擎层面

undo log 原子性和一致性

回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚 和 MVCC（多版本并发控制）。undo log 和 redo log 记录物理日志不一样，它是逻辑日志。

  • 可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，
  • 当 update 一条记录时，它记录一条对应相反的 update 记录。当执行 rollback 时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚。s

redo log 持久性

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file），前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘、发生错误时，进行数据恢复使用。

MVCC

Multiversion Concurrency Control

  • 隐藏列
    • DB_TRX_ID
    • DB_ROLL_PTR 指向历史数据 存储在UndoLog中
    • Raw Id（当没有主键的时候）
  • UndoLog
  • ReadView - 这是判断“该读哪个版本”的核心裁决者。当一个事务执行查询时，InnoDB 会为它生成一个 Read View，里面记录了当前系统中活跃（尚未提交）的事务 ID 列表。
    • 如果版本的 TRX_ID小于 Read View 中的最小活跃事务 ID：说明这个版本在当前事务开启前就已经提交了，可以读。
    • 如果版本的 TRX_ID大于等于 Read View 中的最大事务 ID：说明这个版本是当前事务开启后才产生的新事务修改的，绝对不能读。
    • 如果版本的 TRX_ID 落在 Read View 的活跃事务列表中：说明修改这条数据的事务还没提交，不能读。
    • 如果不满足上述条件，就顺着 DB_ROLL_PTR 找下一个更老的版本，直到找到能读的为止。

快照读和当前读

快照读是指基于 MVCC（多版本并发控制） 机制读取数据。它读取的可能是数据的历史版本，而不是数据库中最新的数据。

  • 核心特点：不加锁，是非阻塞读。因为不加锁，所以极大地提高了数据库的并发性能。
  • 触发场景： 普通的、不加锁的 SELECT 语句。
    • 例如：SELECT * FROM users WHERE id = 1;
  • 底层机制： 依赖于 Read View (读视图) 和 Undo Log (回滚日志)。在读取时，InnoDB 会根据当前事务的 ID 和 Read View 来判断应该读取哪个版本的数据，从而避免读取到其他未提交事务的脏数据。

当前读是指读取数据库中最新提交的数据版本，并且在读取时必须对数据加锁，以防止其他事务并发修改这些数据。

  • 核心特点：加锁，是阻塞读。为了保证读取到的数据是最新的，并且在处理期间不被篡改，必须通过加锁来排斥其他写操作。
  • 触发场景： 所有加锁的查询以及所有的 DML（增删改）操作。
    • 加锁查询：SELECT ... LOCK IN SHARE MODE (共享锁 / S锁)
    • 排他查询：SELECT ... FOR UPDATE (排他锁 / X锁)
    • 修改操作：UPDATE、DELETE、INSERT。
      • 注意：执行 _UPDATE_ 或 _DELETE_ 时，InnoDB 必须先执行一次“当前读”，找到最新的数据行及其位置，然后再进行修改操作。
  • 底层机制： 依赖于 悲观锁（如 Record Lock 记录锁、Gap Lock 间隙锁、Next-Key Lock 临键锁）。在 RR 隔离级别下，当前读通常会使用 Next-Key Lock 来锁定记录及其相邻的间隙，从而防止幻读（Phantom Read）。

当Mysql执行update的时候 具体发生了什么

  1. 准备阶段 Server层
    1. 连接器 验证用户的身份和权限 建立数据库连接
    2. 分析器 分析update语法是否正确
    3. 优化器 决定使用哪个索引
    4. 执行器 调用InnoDB接口进行执行
  2. 执行与内存修改 InnoDB层
    1. 执行接口查询
    2. 内存中修改
    3. 记录到UndoLog
    4. 更新内存
  3. 日志记录与两阶段踢掉
    1. 写入RedoLog
    2. 写入Binlog
    3. 提交事务
← back to notes