MySQL经典面试题

优化

在MySQL中，如何定位慢查询？

慢查询情况：

  • 聚合查询
  • 多表查询
  • 表数据量过大查询
  • 深度分页查询

表象：页面加载过慢、接口压测响应时间过长（超过1s）

  • 方案一：开源工具

    • 调试工具：Arthas
    • 运维工具：Prometheus、Skywalking

  • 方案二：MySQL自带慢日志

    • 慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。

    • 如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

      1
      2
      3
      4
      #开启MySQL慢日志查询开关
      slow_query_log=1
      #设置慢日志的时间为2秒，SQL语句执行时间超过2s，就会视为慢查询，记录慢查询日志
      long_query_time=2

      image-20250228215439818

面试话术：

image-20250228215534050

生产条件下不会开启慢日志，会损耗一些MySQL的性能

image-20250301151148854

SQL语句执行计划

可以采用EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息

image-20250301151452282

  • possible_key：当前sql可能会使用到的索引

  • key：当前sql实际命中的索引

  • key_len：索引占用的大小

    通过key和key_len两个查看是否可能会命中索引，索引本身存在是否有失效的情况

  • Extra：额外的优化建议

    image-20250301151746303

    是否出现了回表，如果出现了，可以尝试添加索引或修改返回字段来修复

  • type：这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、index、all

    • NULL：这条sql语句执行时没有用到表

    • system：查询系统中的表

    • const：根据主键查询

    • eq_ref：主键索引查询或唯一索引查询，只能返回一条

    • ref：索引查询，可能是多条

    • range：范围查询

    • index：索引树扫描

    • all：全盘扫描

      如果是index或all，就需要优化

image-20250301152443378

image-20250301152452368

什么是索引

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库还维护着满足特定查找算法的数据结构（B+树），这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

数据结构对比

image-20250301162227435

image-20250301162324100

image-20250301162516876

非叶子节点存储指针，效率更高，磁盘读写代价低；数据都存储在叶子节点上，查询效率稳定；且叶子节点之间存在链表，适合扫库和区间查询

image-20250301162609034

image-20250301162716307

什么是聚簇索引，什么是非聚簇索引（二级索引）/什么是回表查询

聚簇索引和二级索引

自己创建的索引一般都是二级索引

image-20250301165015054

image-20250301164944588

回表查询

image-20250301165113065

回表查询：先通过二级索引拿到主键值，再根据主键值通过聚集索引拿到整行数据。

image-20250301165359176

image-20250301165408707

什么是覆盖索引

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

image-20250301165747296

image-20250301165838849

MySQL超大分页处理

image-20250301165919934

image-20250301170035881

image-20250301170112485

image-20250301170150771

索引创建原则有哪些

image-20250301170518979

image-20250301170745974

image-20250301170826569

image-20250301170833626

什么情况下索引会失效

联合索引中每个索引字段存在顺序

image-20250301171640691

通过执行计划explain判断哪块索引失效

  • 违反最左前缀法则
    • 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。
    • 如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效
    • 可以理解为联合索引中，从哪一个索引开始在sql语句中where后面的判断语句部分没出现，那么从它开始（包括自己）的索引都失效
  • 范围查询的右边的列，不能使用索引
    • 可以理解为联合索引中，从哪一个索引开始在sql语句中where后面的判断语句部分是通过大于小于号进行范围查询的，那么从它下一个索引开始的索引都失效（即范围查询的这个索引不失效，而是下一个开始失效）
  • 不要在索引列上进行运算操作，索引将失效
    • 比如substring
  • 字符串不加单引号，造成索引无效
    • 由于MySQL的查询优化器会自动的进行类型转换，造成索引无效
  • 以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊查询匹配，索引不会失效。如果是头部模糊匹配，索引失效。
    • 即%xxx会失效，xxx%则不会失效

image-20250301172528795

SQL优化经验

image-20250301173007451

image-20250301173356967

第四点中，union all查询后两个条件查询出来的数据合并，所以会有重复的数据；而union查询后自动查重，不会有重复数据。

第五点中，image-20250301173449584小循环放外面，MySQL就只需要3次的数据库连接，再在里面进行1000次的数据操作就可以了.

image-20250301173751987

image-20250301173826033

image-20250301173858284

其他

事务相关

事务的特性是什么

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

  • 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
  • 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
  • 隔离性（Isolution）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

image-20250301182824237

并发事务带来哪些问题？怎样解决？MySQL的默认隔离级别？

  • 并发事务问题：脏读、不可重复读、幻读
  • 隔离级别：读未提交、读已提交、可重复读、串行化

image-20250301183022777

image-20250301183030586

事务B读到的是事务A中更改的数据，但是事务A还未提交

image-20250301183132212

在事务A两次读操作中间事务B进行了数据的修改，导致两次读操作读到的内容不一致

image-20250301183428881

在解决了不可重复读的前提下，事务A读数据库发现没有这条数据，这时事务B插入了这条数据，导致A插入时报错，A再读时还是显示数据库中没有，可是插不进去

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

image-20250301183458465

解决了问题是×，还有这种问题是√

注意：事务隔离级别越高，数据越安全，但是性能越低

image-20250301183904097

undo log和redo log的区别

image-20250301184006096

image-20250301184107511

如果服务器出问题了，内存中修改的内容无法同步给服务器，不符合事务的持久性。

redo log：

image-20250301184308902

undo log：

image-20250301184509335

image-20250301184554580

解释一下MVCC？/事务中的隔离性是如何保证的？

image-20250301184837073

全程Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突。

MVCC的具体实现，主要依赖于数据库记录中的隐式字段、undo log日志、readView。

image-20250301185110726

  • 记录中的隐藏字段

    image-20250301185239862

  • undo log

    image-20250301185310446

    image-20250301185517209

  • readView

    ReadView（读视图）是快照读SQL执行时MVCC提取数据的依赖，记录并维护系统当前活跃的事务（未提交的）id。

    image-20250301185758303

    image-20250301190011520

MVCC的实现原理

image-20250301190206900

当前未提交的事务是活跃的事务

image-20250301190555301

image-20250301190641838

image-20250301190813653

从后往前依次根据标准判断，第一个符合的就是使用的记录

image-20250301190912997

image-20250301190942311

image-20250301191058541

image-20250301191119516

主从同步原理

image-20250301230021758

MySQL主从复制的核心就是二进制日志

image-20250301230133251

image-20250301230246571

分库分表

分担了访问压力，解决存储压力

分库分表的时机：

  1. 前提，项目业务数据逐渐增多，或业务发展比较迅速（单表的数据量达1000W或20G以后）
  2. 优化已解决不了性能问题（主从读写分离、查询索引……）
  3. IO瓶颈（磁盘IO、网络IO）、CPU瓶颈（聚合查询、连接数太多）

拆分策略

image-20250301231055572

image-20250301231153515

image-20250301231258683

image-20250301231346964

image-20250301231437780

新的问题和新的技术

image-20250301231622762

image-20250301231814158