WQhuanm
MySQL学习笔记

MySQL学习笔记

2025年02月21日 6.1k 字 大概 25 分钟

MySQL的执行原理

1. MySQL的基本架构

  • MySQL 可以分为 Server 层和存储引擎层两部分
    • 存储引擎层负责数据的存储和提取
    • server层：所有跨存储引擎的功能都在此实现，比如存储过程、触发器、视图、内置函数等。

注：查询缓存板块弊大于利，因为许多是无效缓存。8.0之后已经删除

2. SQL请求流程

  1. 连接器：拦截器与客户端建立连接，权限验证（默认为长连接，可以接受多个请求）
  2. 分析器：对SQL语句词法分析，语法分析，检查SQL语句执行权限
  3. 优化器：优化SQL语句，分析索引使用
  4. 执行器：打开目标表，从存储引擎获取所有数据后响应给客户端

日志系统

  • 默认引擎InnoDB有专属日志：redo log（重做日志），具备crash-safe 能力
  • server层也有自己的日志：binlog（归档日志）。
  • 当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log，并更新内存，这个时候更新就算完成了。之后空闲再更新数据到磁盘
  • 日志进行两阶段提交
    （WAL，Write-Ahead Logging）：它的关键点就是先写日志，再写磁盘。
    只有redolog执行完接着binlog完整写入，然后redolog再进行事务提交才算事务生效

事务

1. 事务支持是在引擎层面实现的。MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务。比如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一。

2. ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）

  1. 原子性：事务要么都成功，要么都失败（回滚到事务前状态）
    • 原理：undo log,事务对DB的修改，都会生成相应的undo log，回滚则执行undo log
  2. 一致性：数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。
  3. 隔离性：事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
  4. 持久性：事务一旦提交，它对数据库的改变就应该是永久性的
    • 原理：redo log ，具有crash-safe能力，保证提交的事务的操作一定可以正确更新到DB

3. 多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题

  1. 脏读：当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据）
  2. 不可重复读：事务A中先后两次读取同一个数据，两次读取的结果不一样（因为被他人事务更改了）
  3. 幻读：幻读是在RR隔离级别下，且为当前读的时候才会出现，即当前读下出现“新插入的行”（因为insert的原因，行锁只能限制原有的行，insert的新行会破坏原有数据一致性问题：如进行修改操作时新插入的行却不受阻塞）
    • 解决方法：引入间隙锁 (Gap Lock),间隙锁只与“往这个间隙中插入一个记录”这个操作存在冲突 （当然，如果把隔离等级改为RC，就不存在幻读问题了）
    • 然而间隙锁会带来新死锁的可能：

      A，B2个在同一个间隙开了间隙锁，间隙锁互相阻塞导致死锁(只是2个同样的语句并发执行就有死锁可能)

4. 事务隔离级别：

  • 读未提交（read uncommitted）是指，一个事务还没提交时，它做的变更就能被别的事务看到。
  • 读提交（read committed,RC）是指，一个事务提交之后，它做的变更才会被其他事务看到。
  • 可重复读（repeatable read,RR）（默认隔离级别）是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的（开启事务时建了一致性视图（不是一般的view视图））。
  • 串行化/序列化（serializable ），顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

5. innodb支持RC和RR隔离级别实现是用的一致性视图(consistent read view)

  • InnoDB每个事务都有一个事务ID,叫做transaction id(严格递增)。每行数据的每个版本都会记录生成他的事务id（记为 row trx_id），作为隐藏列存在于聚簇索引中（InnoDB 每行有隐藏列 TransactionID（事务ID） 和 Roll Pointer（回滚指针，用于事务回滚）
  • 一致性视图就是从当前版本回退到创建视图时的版本，实现一致性读。只有在一致性视图创建前的已提交事务的数据是可见的
  • 而更新数据有些不同，更新都是先读后写的，而这个读，只能读当前最新版本的值，称为“当前读”（current read）。
    事务内部产生新的数据版本是可见的，因此更新后的查询基于当前读更新后的新版本
  • 在RR隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图。
    在RC隔离级别下，每一个语句执行前都会重新算出一个新的视图。保证内部执行语句前获取所有已执行事务的数据

索引

1. 索引是在存储引擎层实现的，InnoDB 使用了 B+ 树索引模型

  • 只有叶子结点存数据，叶子结点使用链指针连成链表。能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。（N叉树，减少树高）
  • 树的每个结点存储数据是页，内部结点存取的是索引字段，所以索引字段越小，则一个内部结点可以有越多儿子，加快查询速度
  • innodb的B+树分裂策略
    • 每个索引页面维护了一个上次插入的位置，以及上次的插入是递增/递减的标识
      1. 对于新插入的数据，如果不满足递增/递减的约束，采用传统的50%分裂策略，把50%的数据移入新的一页
      2. 否则，采用优化策略，原本满的页数据保留。对于新插入的递增/递减数据，如果相邻页可以存放数据，并入，否则，将其单独放入新页（减少了空间利用率低的问题）
      • 并入策略是为了避免下图这种BUG使得空间利用率更低

2. 索引类型分为主键索引和非主键索引。

  • 主键索引的叶子节点存的是整行数据。主键索引也被称为聚簇索引（clustered index，索引结构和全部数据一起存放）。
    主键查询只需搜索主键索引树
  • 非主键索引的叶子节点内容是主键的值。非主键索引也被称为二级索引（secondary index）。
    二级索引会把不包含的（联合）主键字段加入索引末尾来排序。
    普通索引查询则需要先搜索其索引树，得到主键值再搜索主键索引树，这个过程称为回表。
  • 主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多（一个叶子结点页可以存取更多数据）。

3. 覆盖索引：普通索引查询的主键字段+索引字段已经覆盖查询需求，则无需回表

  • 索引覆盖也会回表的情况（不是完全避免回表）
    1. 使用了覆盖索引但超过了最大索引长度，或是字符串只取了前缀作为索引
    2. 因为事务需要获取版本号
      • 只有聚簇索引有事务版本号
      • 二级索引的每一页都有PAGE_MAX_TRX_ID（页内数据最后一次被修改的事务id最大值）。
      • 如果当前页的PAGE_MAX_TRX_ID在Read View（事务快照）之前则无需回表，否则二级索引必须回表找到聚簇索引才能找到read view之前的版本号

4. 最左前缀原则

联合索引的排序是自左向右按字段排序。查询时无需满足索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。
这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

  • 通过调整字段顺序可以优化多个索引占用的空间和索引数量，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
  • 对于既有联合查询，又有各自字段的查询，需要维护(a,b),(b)2种索引以满足要求，此时a为大字段，b为小字段即可节省空间

5. 索引下推优化（index condition pushdown）

可以在索引遍历过程中，对索引中包含的所有字段先做判断，直接过滤掉不满足查询条件的记录，减少回表次数。

6. 索引选择异常和处理

  • 数据发生大量变化时，导致优化器的统计信息可能不准确，从而选错索引。使用ANALYZE TABLE分析表，以保持统计信息的准确性，优化查询性能。
    即使信息准确，优化器仍然可能判断出错，解决思路：
    • 使用force index强制使用目标索引，但是会导致后期维护不便
    • 通过sql语句引导
    • 增加更合适索引供其选择，或删除索引
  • 如果where对字段值进行了函数等修改（或者是隐式修改，如字段类型不同进行隐式类型转换），优化器将会放弃使用字段索引（应保证等式左值纯粹为字段值）
    甚至等式两边的表的字符集不同，则也会进行隐式字符集转换导致无法使用索引

7. 字符串索引创建思路

  • 直接创建完整索引，这样可能比较占用空间；（若业务量数据实际不多，这种方法反而好，省去风险）
  • 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；（查询的数据需要回表确认字符串匹配）
  • 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题，但不支持范围扫描。
  • 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，但不支持范围扫描。

8. 索引重建的重要性

  • 索引可能因为删除，或者页分裂等原因，导致数据页有空洞，浪费内存，降低查询效率，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。
  • MySQL支持Online DDL操作，可以在不锁定表的情况下重建索引。ALTER TABLE table_name ENGINE=InnoDB;

MySQL锁

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

  • 全局锁的典型使用场景是，做全库逻辑备份。
  • 表级别的锁有两种：一种是表锁（lock tables ），一种是元数据锁（meta data lock，MDL）。
    • 表锁一般是在数据库引擎不支持行锁的时候才会被用到的。
      • 一般的表锁，读锁（共享锁，S，LOCK TABLE t READ），写锁（排他锁，X,LOCK TABLE t WRITE）
      • 特殊表锁：意向锁，意向锁只与表锁冲突，不与行锁及意向锁冲突，在添加行锁时innodb会自动添加相应的意向锁从而保证行锁与表锁兼容
        意向共享锁（intention shared lock, IS）,意向排他锁（intention exclusive lock, IX）
    • MDL的作用是保证读写的正确性。
      • 当对一个表做增删改查操作（DML，Data Manipulation Language）的时候，加 MDL 读锁；
      • 当要对表做结构变更操作(DDL，Data Definition Language)的时候，加 MDL 写锁。
      • MDL锁是系统默认会加的，在语句执行开始时申请，但直到事务提交才释放。
  • 行锁（两阶段锁）：行锁是在需要的时候自动加上的，但要等到事务结束时才释放。这个就是两阶段锁协议。
    • 手动加锁方式：共享锁（S锁,lock in share mode），排他锁（X锁,for update）
      事务加了锁后不再是普通的快照读（一致性读），变成了当前读
    • 两阶段锁的特性所以要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放，减少锁住的时间。（如更新语句应优先放于插入后，更新涉及行锁的抢夺）

2.Innodb RR隔离等级下行锁加锁原理

  • 行锁加锁的基本单位是next-key lock（临键锁，间隙锁和行锁（记录锁）合称）。
    • 每个 next-key lock 是前开后闭区间。间隙锁是开区间，而记录锁锁定的是明确的行。
    • 锁是加在索引上的，不同索引有各自的锁，如果条件符合覆盖索引且为读锁，则只有覆盖索引会被加锁，主键索引的使用不受锁影响。
    • 加next-key锁实际是间隙锁，行锁这样一个一个加，直到完成或某个具体的锁被阻塞为止，即存在next-key的间隙锁加上，而行锁阻塞停止的情况，如下图

      B间隙锁锁上，但是行锁被A阻塞，A插入被B间隙锁阻塞，造成死锁，回滚B
  • 只有被遍历的对象才能被加锁，且只要被遍历就一定会加锁
    • 索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
      索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
    • 如果查询不到的符合条件的索引，只会对包括条件的必要区间加锁
    • 如果没有符合条件的索引，则会进行全表遍历，遍历的行/间隙全部加锁（一般最后整个表都被上锁了，除非limit救场拿完足够数目停止扫描）
  • 非索引字段进行update或delete等操作（加锁，而且还是写锁），代价极高。所有记录上锁，以及所有间隔的锁。
  • RC一般只有记录锁，且语句执行过程中加上的行锁，在语句执行完成后，就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交。
    也就是说，读提交隔离级别下，锁的范围更小，锁的时间更短，这也是不少业务都默认使用读提交隔离级别的原因。

数据库缓冲池

1. change buffer（插入缓冲）

  • 更新数据页时，若数据页在内存中就直接更新，否则在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。
    change buffer 是可以持久化的数据，在内存中有拷贝，也会被写入到磁盘上。
  • 只有普通索引可以使用chang buffer，唯一索引的更新操作都要先判断这个操作是否违反唯一性约束，这必须要将数据页读入内存才能判断。
    在数据保证了唯一性下或者无要求唯一性时，优先使用普通索引提升性能
    在一些“归档库”的场景，归档数据已经是确保没有唯一键冲突了。要提高归档效率，可以考虑把表里面的唯一索引改成普通索引。
  • 对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
    若一个业务的更新模式是写入之后马上会做查询，change buffer 反而起到了副作用。

2. Innodb的flush（刷脏页）策略

  • 当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。
  • flush场景
    1. redo log写满，必须停止所有写操作清理redo log ，最为致命
    2. 内存页写满，淘汰的旧页为“脏页”，需要对其刷新，会占用资源，影响DB响应速度
    3. 空闲/关机时刷新，对性能影响不大
  • innodb_io_capacity，设置磁盘的IOPS，根据磁盘性能确定刷脏页的最大速度
    刷脏页速度取决一下因素
    • 当前脏页比列，越接近 innodb_max_dirty_pages_pct 是脏页比例上限(默认75%) 需要越快
    • redo log剩余空间(分配合理的redo log内存也很重要)

3. Buffer Pool

  • Buffer Pool管理缓存在内存的数据页，能加速查询速度
    • 数据页淘汰算法是改进过的最近最少使用 (Least Recently Used, LRU) 算法

      内存页由链表链起来，分为young和old区域，链表的LRU_OLD指针指向old区域开头
    • 新插入的数据放在old区域头部，如果短时间内被再次访问，才会转到young区域开头，否则还是在old区域开头
    • 这种方法保证了一定的内存命中率，防止全表扫描等大量冷数据涌入内存影响命中率

SQL语句解析

1. COUNT(*)性能

由于innodb的事务是通过多版本并发控制（MVCC,Multi-Version Concurrency Control）实现，每个事务当前的记录在并发下不一定相同，所以无法单纯记录表的行数，所以必须通过遍历整颗索引树（选择最小那颗）来计数，所以在大表中count效率不佳。

  • 可以通过建立一张计数表的方式，来为每张表维护count值（利用事务特性解决该问题）
  • count效率比较（低到高）：count(字段)<count(主键 id)<count(1)≈count(*)
    • 对于 count(主键 id) 来说，InnoDB 会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
    • 对于 count(1) 来说，InnoDB 遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
    • 对于 count(字段) 来说：取字段值，有字段索引则遍历该索引树，否则遍历最慢的主键索引树
    • 对于count(*)来说，并不会把全部字段取出来，而是专门做了优化，不取值，按行累加。

2.ORDER BY 的排序方式

  • 如果有符合排序条件的索引，无需排序（复合索引不能把order by、group by的列放在索引的最前面，因为查询中总是where先于order by执行）
  • MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。
    所有符合条件的行都会放入buffer进行快排，如果buffer放不下，则每次快排后把数据存入磁盘临时文件，最后磁盘对所有临时文件使用归并排序合并成大文件
  • rowid排序：如果全字段的总长度太大，超过设定的值：max_length_for_sort_data，使用rowid排序
    该排序只选择主键和排序字段放入sort_buffer来排序，但是最后需要回表取回所有目标字段（多了磁盘读获取目标索引页内容，不一定就好于全字段排序）

3. JOIN的优化

  • join的原则是小表（A，行数n）驱动大表（B,被驱动表，行数m）
    A使用全表扫描，每次读入数据到join buffer供B配（显然，join buffer设置越大速度越快）
  • 若B有符号查询条件的索引，使用（ndex Nested-Loop Join，NLJ），复杂度k* n * m * log(m)(k为A读入join buffer 次数)
  • 无符合的索引则使用hash join，把join buffer数据构建成哈希表，加速B表的匹配

4. 慢查询优化策略

  1. 索引优化：索引合适，对查询条件发挥作用
  2. 优化数据库结构：将字段很多的表分解成多个表，建立中间表（将大表间的联合查询改为中间表（临时表，小表）与大表的联合查询

5. Limit 1000000,20 的优化（深度分页问题）

分页的问题，limit是把offset+target数据全部查出来后再丢弃offset数据，查询数据每行都回表，性能降低
低效率SQL：SELECT * FROM 表 a, where 条件 LIMIT 100000,20;

  • 延迟关联（本质是索引覆盖）
    SQL更改为：SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
    1. 子查询在利用索引的同时，只查询符号条件的id，无需回表，效率高
    2. 查出来的数据是小表 join 原来的大表a，对于小表的每个id，直接从a表的主键索引拿到对应的行，只有目标的20行使用了主键索引

6. NULL字段避免使用

  • 索引查询时，会降低查询效率
  • 执行count，distinct，比较符等会出现数据丢失的情况（因为null不被计入）

7. explain

  1. type（性能高到低）

    1. system 当表中只有一条记录并且该表使用的在储引擎的统计数据是精确的，比如MyISAM、Memory
    2. const 主键/唯一索引列进行等值匹配时
    3. eq_ref 多表 JOIN 时，被驱动表通过主键或唯一索引进行等值匹配。
    4. ref 通过普通索引等值查询，可能返回多行。
    5. fulltext 使用全文索引时的访问方式。
    6. ref_or_null 类似 ref，但允许值为null
    7. index_merge 使用Intersection交集、Union并集、sort-Union排序并集，这三种索引合并的方式来执行查询（用于and/or条件时）
    8. unique_subquery 子查询使用主键或唯一索引
    9. index_subquery 子查询使用普通索引
    10. range 索引范围扫描
    11. index 使用索引覆盖，无需回表，但遍历整个索引
    12. ALL 全表扫描

  2. extra

    1. Using filesort：使用了外部排序
    2. Using temporary：使用了临时表
    3. Using index：使用了覆盖索引
    4. Using index condition：使用了索引下推优化
    5. Using where：server层需要过滤数据，因为存储层的索引未完全覆盖where限制的条件
    6. Using join buffer：使用了连接缓存

Memory引擎（内存表）

  1. Memory默认使用hash索引

    内存表的数据部分以数组的方式单独存放，而主键 id 索引里，存的是每个数据的位置，数据按写入顺序存放。主键 id 是 hash 索引（不支持范围扫描，只能使用全表扫描），可以看到索引上的 key 并不是有序的。
  2. Memory也支持B-tree索引
  3. Memory的优缺
    1. 不支持事务，存在并发安全，只支持表锁，并发性能不行。数据库重启，内存表被清空，持久性不行，不适合做普通表
    2. 作为内存临时表的优势
      1. 临时表不会被其他线程访问，没有并发性的问题；
      2. 临时表重启后也是需要删除的，清空数据这个问题不存在；
      3. 对于join没有索引的字段且该表只取部分数据，先把他们存到临时内存表再join显然性能更高

参考文章

MySQL 实战 45 讲

本文作者：WQhuanm
本文链接：https://wqhuanm.github.io/Issue_Blog/2025/02/20/7_MySQL学习笔记/
版权声明：本文采用 CC BY-NC-SA 3.0 CN 协议进行许可
MySQL