OFFER DASH

主题

MySQL 高频考点 ​

索引相关 ​

索引的底层数据结构 ​

MySQL 索引对比：

特性MyISAMInnoDB
索引类型非聚集索引（数据和索引分离）主键索引为聚集索引（数据与主键绑定），其他索引为非聚集索引
数据结构B+树B+树
主键索引叶子节点存储数据的物理地址叶子节点直接存储完整数据行
辅助索引叶子节点存储数据的物理地址叶子节点存储主键值（需二次查询主键索引）
事务支持不支持支持（ACID兼容）
行级锁仅表级锁支持行级锁（默认）
外键约束不支持支持
全文索引支持（5.6以前）5.6前不支持，5.6+支持
崩溃恢复数据易损坏，恢复困难通过Redo Log和Undo Log保障恢复
适用场景读密集、无事务、静态数据读写混合、事务、高并发、数据一致性要求高

各个索引数据结构选型对比：

数据结构优点缺点为何不适合InnoDB
B+Tree1. 阶数高，查询路径短，效率高
2. 内部节点不存数据，减少磁盘I/O
3. 叶子节点双向链表，支持高效范围查询		--
二叉树理想情况下（平衡时）查询效率为O(log N)1. 顺序插入退化为链表，查询效率O(N)
2. 数据量大时层级深，磁盘I/O次数多		层级深导致检索慢；无法保证平衡性，易退化
红黑树自平衡，避免退化为链表1. 仍为二叉树结构，层级深（数据量大时高度远高于B+Tree）
2. 插入/删除需频繁调整平衡，代价高		层级深导致磁盘I/O多；频繁平衡操作影响性能
B-Tree多叉树结构，比二叉树层级更低1. 内部节点存储数据，占用空间大，导致单节点键值减少（树变高）
2. 范围查询需中序遍历，效率低		数据存储分散增加磁盘I/O；范围查询性能差；插入/删除需频繁平衡节点

聚簇索引和非聚簇索引 ​

对比维度聚簇索引非聚簇索引（二级索引）
存储方式数据和索引存储在一起数据和索引分开存储
数量限制必须有且仅有一个可以存在多个
叶子节点存储整行数据（数据页）存储对应记录的主键值（InnoDB）或数据指针（如 MyISAM）
查询效率主键查询极快（直接定位数据）需要二次查询（回表查询），效率较低
索引维护数据插入/更新时可能触发页分裂和页合并维护成本相对较低
应用场景主键查询、范围查询（物理存储有序）非主键列的快速查询、覆盖索引

聚簇索引的选举规则：

  • 存在主键：主键即聚簇索引
  • 不存在主键：使用第一个唯一索引（UNIQUE）作为聚簇索引
  • 都不存在：InnoDB 会自动生成一个 rowid 作为隐藏的聚簇索引

回表查询：

例如 现有表 USER，其中 USER 表的主键为 id，唯一索引 name：

对该表执行语句：SELECT * FROM USER WHERE name = 'OfferDash'

  • 上述语句查询时会走二级索引 name
  • 由于 name 是非聚簇索引，叶子节点只保存了 name 对应的主键 id 的值
  • 因此，需要再通过 id 查询全部信息，这个过程就是回表查询

即，通过二级索引找到对应的主键值，再到聚簇索引中查找整行数据，这个过程就是回表。

覆盖索引优化：

查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。

例如：现有表 USER，其中属性分别为 id，name，gender，其中主键为 id，对 name 创建了唯一 索引，对于以下 sql：

  • SELECT * FROM USER WHERE id = 1：走 id 聚簇索引，返回全部的数据，是覆盖索引。
  • SELECT id FROM USER WHERE name = 'OfferDash'：走 name 二级索引，返回 id 和 name，是覆盖索引。
  • SELECT id, name FROM USER WHERE name = 'OfferDash'：走 name 二级索引，返回 id 和 name，是覆盖索引。
  • SELECT * FROM USER WHERE name = 'OfferDash'：走 name 二级索引，返回 id 和 name，但 gender 需要回表查询，不是覆盖索引。

索引失效的场景 ​

失效场景失效原因
违反最左前缀法则1.联合索引必须从最左字段开始查询，跳过中间字段时后续字段失效
2.完全跳过最左字段则整个索引失效
范围查询后索引失效范围查询（如 >、<、BETWEEN）后的字段索引失效，但范围查询前的字段仍有效。
索引列运算导致失效对索引列进行运算或使用函数导致索引失效。
类型转换导致失效字符串字段与数值比较时索引失效，数值字段与字符串比较时不失效。
模糊查询导致失效以 % 开头的 LIKE 查询失效，尾部模糊查询有效。

实战：现有表格 USER，对字段 name 创建索引，对字段 age 创建索引，对 name，age，gender 创建联合索引：

  • SELECT * FROM USER WHERE name = 'OfferDash'：索引不失效
  • SELECT * FROM USER WHERE name = 'OfferDash' AND age = 114：索引不失效
  • SELECT * FROM USER WHERE age = 114：索引失效，跳过了 name
  • SELECT * FROM USER WHERE name = 'OfferDash' AND gender = 'man'：索引失效，跳过了 age
  • SELECT * FROM USER WHERE name = 'OfferDash' AND age <= 114 AND gender = 'man'：索引部分失效，由于 age 使用了范围查询，name 和 age 会走索引，但 gender 没有走索引
  • SELECT * FROM USER WHERE SUBSTRING(name, 1, 1) = 'O'：在索引列 name 上做运算，索引失效
  • SELECT * FROM USER WHERE name LIKE '%Dash'：头部模糊查询，索引失效
  • SELECT * FROM USER WHERE name LIKE 'Offer%'：尾部模糊查询，索引不失效

事务相关 ​

事务的特性 ​

事务是数据库中的一个逻辑单元，包含多个操作，这些操作要么全部成功，要么全部失败。其核心特性是 ACID：

  • 原子性（Atomicity）：事务中的操作要么全部完成，要么全部回滚，不可分割。
  • 一致性（Consistency）：事务执行前后，数据库必须保持合法状态（如约束、触发器等）。
  • 隔离性（Isolation）：并发事务之间互不干扰，每个事务的操作对其他未提交的事务不可见。
  • 持久性（Durability）：事务提交后，修改永久保存，即使系统故障也不丢失。

事务的隔离级别 ​

并发事务带来的问题：

问题描述示例
脏读读到其他事务未提交的数据。事务 A 读取事务 B 未提交的数据，事务 B 回滚导致事务 A 读到无效数据。
不可重复读同一事务多次读取同一数据，结果不一致（因其他事务修改数据并提交）。事务 A 第一次读数据为 100，事务 B 修改为 200 并提交，事务 A 再读为 200。
幻读同一事务多次查询结果集不一致（因其他事务插入或删除数据并提交）。事务 A 查询无记录，事务 B 插入一条记录并提交，事务 A 再查询发现新记录。

由此引出事务的隔离级别：

隔离级别解决的问题存在的问题备注
读未提交无脏读、不可重复读、幻读最低隔离级别，性能高但数据一致性差。
读已提交解决脏读不可重复读、幻读Oracle 默认级别
可重复读解决脏读、不可重复读幻读（理论）MySQL 默认级别，通过 MVCC 和锁机制避免幻读
串行化解决脏读、不可重复读、幻读无最高隔离级别，事务串行执行，性能最低。

可重复读是否解决了幻读 ​

解决了部分情况下的幻读问题，没有完全解决：

  • 事务 A 开启，事务 B 插入数据并提交
  • 事务 A 读记录不会发生幻读
  • 事务 A 插入，事务 B 插入的数据会发生主键冲突，产生了幻读问题

可重复读解决幻读问题有两种方式：

  • 针对快照读（普通select语句）：
    • 通过MVCC方式解决了幻读
    • 因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的
    • 即使中途有其他事务插入了一条数据，是查询不出来这条数据的
  • 针对当前读(select…for update等语句)：
    • 通过next-key lock(记录锁+间隙锁)方式解决了幻读
    • 因为当执行select for update语句的时候，会加上next-key lock
    • 如果有其他事务在next-key lock锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入

SQL 优化 ​

定位慢查询 ​

定位方式MySQL 自带慢查询日志Arthas
数据来源MySQL Server 记录的慢查询日志JVM 运行时方法调用跟踪，可定位到代码层
使用方式需修改MySQL配置（如 slow_query_log）无需修改应用代码，动态attach到JVM
性能开销开启慢查询日志对MySQL有一定I/O开销轻量级，对应用性能影响较小
监控方式mysqldumpslow 或 pt-query-digest 分析日志支持实时动态监控方法调用，定位到具体方法

MySQL慢查询日志：

  • 配置文件：/etc/my.cnf
  • 查询是否生效： SHOW VARIABLES LIKE '%slow_query%'，SHOW VARIABLES LIKE '%long_query_time%'
  • 开启慢日志：slow_query_log = 1 或 SET GLOBAL slow_query_log = 'ON'
  • 设置超时记录时间：long_query_time = x 或 SET GLOBAL long_query_time = x，x 为秒，超过 x 秒记录该 sql
  • 日志保存位置：/var/lib/mysql/localhost-slow.log 或 SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log'

Arthas监控

  • 监控方法调用耗时：trace com.example.mapper.UserMapper selectById
  • 监控JDBC查询：trace *JDBC4Connection* query
  • 监控Druid连接池：watch com.alibaba.druid.pool.DruidPooledConnection * '{params, throwExp}' -x 3

分析慢查询 ​

直接在 SELECT 语句之前加上关键字 EXPLAIN / DESC:

sql
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
字段/概念优化建议
id查询执行顺序标识。相同id按顺序执行，不同id从大到小执行（子查询可能导致不同id）
select_type
  • SIMPLE：简单单表查询
  • PRIMARY：外层主查询
  • UNION：UNION中第二个及以后的查询
  • SUBQUERY：SELECT/WHERE中的子查询
type（性能排序）
  • NULL：未访问表（最佳）
  • system > const > eq_ref > ref > range > index > all（最差）
  • 优化重点：至少达到range级别，避免all全表扫描
possible_keys可能使用的索引（检查是否包含预期索引，若无则需创建）
key实际使用的索引（若为NULL说明未走索引，需优化）
key_len索引使用的字节数（相同查询条件下，值越小效率通常越高）
Extra
  • Using where; Using Index：覆盖索引，无需回表（最优）
  • Using index condition：使用索引但需回表
  • Using filesort或Using temporary：需优化（出现临时表或文件排序）

常见索引优化：

  • 索引是否命中：possible_keys不为空但key为 NULL，需强制索引或优化SQL
  • 索引是否失效：type为all或index 说明索引失效
  • 是否使用覆盖索引：Extra出现Using index 说明走了二级索引
  • 索引长度优化：对比key_len与字段实际长度，过大会影响内存使用

页面历史

Released under the CC BY-NC-SA 4.0 License

Copyright © 2025 OFFER DASH