MySQL知识总结

MYSQL

1.MYSQL优化问题

一、如何定位慢查询？

原因：

  • 聚合查询
  • 多表查询
  • 表数据量过大查询
  • 深度分页查询
  • 表象：页面加载过慢、接口压测响应时间超过1s

工具：

  • 调试工具：arthas

  • 运维工具：prometheus、skywalking

  • MYSQL中自带的慢日志查询，需要配置开启和设置慢日志的时间。

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

回答：先介绍自己的场景+用的工具+扩展自己知道的其他知识

二、定位到了语句之后，如何分析呢？

工具/语句：

在语句面前添加EXPLAN或者DESC

  • possible-keys：当前sql可能用到的索引
  • key：实际命中的索引
  • key-len：索引占用的大小
  • extra：额外的优化建议，是否出现了回表（如果出现using index condition 就表明有优化的空间）
  • type：连接类型，性能由好到坏：NULL、system、const（主键查询）、eqref（唯一索引或者主键索引查询）、ref（索引查询）、range（范围查询）、index（全索引查询）、all（全盘扫描）
  • 如果是index和all就需要优化了。

回答：

面试官：那这个SQL语句执行很慢，如何分析呢？
候选人：如果一条sgl执行很慢的话，我们通常会使用mysal自动的执行计划explain来去查看这条sql的执行情况，

比如在这里面可以通过key和key_len检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，

第二个，可以通过yp字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，

第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

三、什么是索引？

回答（定义+特点）

  • 索引(index)是帮助MySQL高效获取数据的数据结构（有序）,其实就是b+树。
  • 提高数据检索的效率，降低数据库的IO成本（不需要全表扫描）
  • 通过索引列对数据进行排序（B+树），降低数据排序的成本，降低了CPU的消耗
  • mysql默认的innoDB引擎采用的是B+树的数据结构来存储索引

四、索引的底层（B+树和特点）

1.B树的特点

  1. B树最多四个key，最多五个字节点，每个节点也是最多四个key。
  2. B树每一个节点都存储了数据，所以每一次根据索引进行查询的时候都会额外查询多余的数据导致磁盘读写高。
  3. 阶数更多，路径更短

2.B+树的特点

1. B+树：磁盘读写代价更低，**非叶子节点只存储指针**，叶子阶存储数据
1. B+树便于扫库和区间查询，叶子节点是一个**双向链表**
1. 查询效率B+树更加稳定（可以忽略）
1. 阶数更多，路径更短
  • 可能会问B+树和B树的区别

###五、什么是聚簇索引？

回答

  • 聚簇索引（聚集索引）：数据和索引放到一块，B+树的叶子节点保存整行数据，有且只有一个

    📖注意：(如果有一个主键。那么主键就是索引。如果不存在主键，那么unique索引为聚集索引，如果都没有，那么innoDB就会自动生成rowid作为隐藏索引！)

  • 非聚簇索引（二级索引）：数据和索引分开存储，B+树的叶子节点保存对应的主键，可以有多个。

    💡**注意：**二级索引一般都是一行数据，而不是聚簇索引一样是一个数据。

  • **回表查询：**举个例子方便理解，比如我们查询

    1
    select * from user where name ="猪博客";

    此时先走二级索引查询到聚集索引，之后根据聚集索引查找到我们需要的数据。

    📖笔记：通过二级索引查找到主键值，到聚集索引中查找整行数据，这个过程就是回表查询。

七、覆盖索引

回答

覆盖索引是指查询使用了索引，返回的列，必须在 索引中全部能够找到

说人话就是，一次查询就可以直接找出来，不需要回表查询。

  • 使用id查询（主键），直接走聚集索引查询，一次索引扫描，直接返回整行数据，效率高
  • 如果返回的列中没有所要查询的字段，有可能会触发回表查询，所以尽量避免用select*

八、超大分页如何处理

定义

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，效率越低

回答

可以用覆盖索引+子查询来解决。

面试官：知道什么叫覆盖索引嘛？
候选人：嗯~，清楚的
覆盖索引是指select查询语句使用了索引，在返回的列，必须在索引中全部能够找到，如果我们使用id查询，它会直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。
如果按照二级索引查询数据的时候，返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select*,尽量在返回的列中都包含添加索引的字段
面试官：MYSQL超大分页怎么处理？
候选人：嗯，超大分页一般都是在数据量比较大时，我们使用了Iit分页查询，并且需要对数据进行
排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决
先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以了
因为查询i的时候，走的覆盖索引，所以效率可以提升很多

九、索引创建的原则（1256要提及）

  1. 数据量较大，且查询频繁的表（单表超过10万数据）
  2. 常作为查询条件、排序、分组（where、order by 、 group by）操作
  3. 尽量选择区分度高的列作为索引，区分度越高，效率越高（尽量建立唯一索引！！）
  4. 如果是字符串类型的字段，可以利用字符串的特点建立前缀索引
  5. 尽量使用联合索引，查询时很多时候联合索引可以覆盖索引，节省存储空间，避免回表。
  6. 控制索引数量
  7. 如果索引列不能存NULL要加not null！！

十、索引失效（5）

判断标准

  • 违反了最左前缀法则
  • 范围查询右边的列是无法使用范围索引的
  • 不要再索引列上进行运算操作，否则索引失效
  • 字符串不加单引号
  • 以like开头的模糊索引会导致失效（%在前面的都会失效）

工具

执行计划explain

十一、sql的优化经验

  • 表的设计优化
  • 索引优化 （九+十）
  • sql语句的优化
  • 主从复制，读写分离
  • 分库分表

表的设计优化（参考阿里开发手册）

  1. 设置合适的数值，根据实际情况选择（tinyint int bigint）
  2. char和varchar，char定长效率高，var可变长度，效率低一些

sql语句的优化

  1. select语句避免 （*） 这种查找
  2. sql避免索引失效
  3. union all 代替 union ，union会多一次过滤，效率低
  4. 避免where子句中对字段进行表达式操作
  5. join优化，能用innerjoin就不用左右join，如果要用，一定要以小表为驱动，内连接会对两个表进行优化，优先把小表放外边，大表放里边，左右join不会重新排序

2.事务

2.1事务介绍

  • 事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，要么一起成功，要么一起失败。
  • 最好的例子就是银行转账。
  • 特性：ACID
    • 原子性：要么一起成功，要么一起失败。
    • 一致性：事务完成时，必须使所有数据都保持一致状态
    • 隔离性：数据库提供隔离机制，保证独立运行。
    • 持久性：一旦回滚或者提交，就是永久的。

2.2事务的问题

  1. 并发事务的问题

    问题 描述
    脏读 一个事务读到另外一个事务还没有提交的数据
    不可重复读 两次读取的数据不同
    幻读 查询不到，插入的时候又发现已经存在

  2. 解决

    隔离级别 脏读 不可重复读 幻读
    未提交读 ✔️ ✔️ ✔️
    读已提交 ❌ ✔️ ✔️
    可重复读（默认） ❌ ❌ ✔️
    串行化 ❌ ❌ ❌

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

  3. undo log 和redo log的区别

    • 缓冲池（bufferpool）：主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度

    • 数据页（page）：是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16KB。页中存储的是行数据

    **区别：**对于redo log （重做日志），记录的是事务提交时数据页的物理修改，用来实现事务的持久性。

    该日志文件由两部分组成：重做日志缓冲（redologbuffer）以及重做日志文件（redolog file），前者是在内存中，后者在磁盘中。

    当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘，发生错误时，进行数据恢复使用。

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

​ 可以认为当delete一条记录时，undolog中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。

​ 当执行rollback时，就可以从undolog中的逻辑记录读取到相应的内容并进行回滚。

总结：

  • redolog：记录的是数据页的物理变化，服务宕机可用来同步数据
  • undolog：记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据
  • redolog保证了事务的持久性，undolog保证了事务的原子性和一致性

2.3事务的隔离性、

  1. 事务的隔离性怎么解决？
  • 排他锁
  • mvcc多版本并发控制

解释一下mvcc！

MVCC

定义：MySQL中的多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突

  • 隐藏字段

​ ①trx_id（事务id)，记录每一次操作的事务id，是自增的

​ ②roll_pointer(回滚指针)，指向上一个版本的事务版本记录地址

  • undo log

​ ①回滚日志，存储老版本数据

​ ②版本链：多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表

  • readView解决的是一个事务查询选择版本的问题
    • 根据readView的匹配规则和当前的一些事务id判断该访问那个版本的数据
    • 不同的隔离级别快照读是不一样的，最终的访问的结果不一样
      • RC：每一次执行快照读时生成ReadView
      • RR：仅在事务中第一次执行快照读时生成ReadView，后续复用

3其他问题

3.1主从同步

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

MySQL主从复制的核心就是二进制日志binlog（DDL（数据定义语言）语句和DML（数据操纵语言）语句）

①主库在事务提交时，会把数据变更记录在二进制日志文件Binlog中。

②从库读取主库的二进制日志文件Binlog，写入到从库的中继日志RelayLog。

③从库重做中继日志中的事件，将改变反映它自己的数据

3.2分库分表

主从同步是用来分担访问压力的，主写，从读。

分库分表的时机：

  • 单表的数据量达到1000W或者20G以后

  • 数据库的用户量和数据量到了一定数量的时候，优化已经解决不了性能的问题了。

  • io瓶颈、cpu瓶颈

1.拆分策略

垂直

垂直分库：以表为依据，根据业务将不同的表拆分到不同的库中。（将库中不同业务，如登录和商品，拆分到不同的库中分别存储）

垂直分表：以字段为依据，根据字段属性将不同的字段拆分到不同表中。（距离，商品的详细信息和商品id拆分）

  • 冷热数据分离
  • 减少io过渡争抢、两表互不影响。

水平（例如id%3来进行表格的访问）

水平分库：将一个库的数据拆分到多个库中。（海量存储的问题）

  • 解决单库大数量，高并发的性能瓶颈问题
  • 提高了系统的稳定性和可用性

水平分表：将一个表的数据拆分到多个表中（可以在同一个库内）

2.新的问题

分库分表会出现很多新的问题，比如多个表的id可能自增重复，多个表可能事务提交不一致，多个表的分页排序、跨节点关联查询等。
中间件来优化：

  • sharding-sogere
  • mycat
面试知识
#学习过程 #数据库
MySQL知识总结
https://pink-pigpig.github.io/pingpinghome/2025/07/24/MySQL知识总结/
作者
pink-pigpig
发布于
2025年7月24日
许可协议