MySQL

该笔记基于b站黑马程序员Java面试题视频制作


1. 定位慢查询

在MySQL中如何定位慢查询？

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

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

定位慢查询可以通过开源工具或者MySQL慢日志

方案一：

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

方案二：

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志,如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

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

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息

/var/lib/mysql/localhost-slow.log

Query_time ：执行时间
select * from tb_sku: 执行的语句


如何定位慢查询？

  1. 介绍一下当时产生问题的场景（我们当时的一个接口测试的时候非常的慢，压测的结果大概5秒钟）

  2. 我们系统中当时采用了运维工具（ Skywalking ），可以监测出哪个接口，最终因为是sql的问题

  3. 在mysql中开启了慢日志查询，我们设置的值就是2秒，一旦sql执行超过2秒就会记录到日志中（调试阶段）



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

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

语法：

直接在select语句之前加上关键字 explain/desc

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

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

  • system：查询系统中的表

  • const：根据主键查询

  • eq_ref：主键索引查询或唯一索引查询

  • ref：索引查询

  • range：范围查询

  • index：索引树扫描

  • all：全盘扫描


那这个SQL语句执行很慢，如何分析？

可以采用MySQL自带的分析工具 EXPLAIN

  • 通过key和key_len检查是否命中了索引（索引本身存在是否失效的情况）
  • 通过type字段查看sql是否有进一步优化的空间，是否存在全索引扫描或全盘扫描
  • 通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复


2. 索引概念及索引底层数据结构

什么是索引？

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


B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key

B-Tree有如下特点:

  1. 所有键值分布在整颗树中（索引值和具体data都在每个节点里）；
  2. 任何一个关键字出现且只出现在一个结点中；
  3. 搜索有可能在非叶子结点结束（最好情况O(1)就能找到数据）；
  4. 在关键字全集内做一次查找,性能逼近二分查找；

B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构

B树与B+树对比:

①：磁盘读写代价B+树更低；②：查询效率B+树更加稳定；③：B+树便于扫库和区间查询


了解过索引吗？（什么是索引）

  • 索引（index）是帮助MySQL高效获取数据的数据结构(有序)

  • 提高数据检索的效率，降低数据库的IO成本（不需要全表扫描）

  • 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗


索引的底层数据结构了解过吗？

MySQL的InnoDB引擎采用的B+树的数据结构来存储索引

  • 阶数更多，路径更短
  • 磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据
  • B+树便于扫库和区间查询，叶子节点是一个双向链表


3. 聚蔟索引和非聚蔟索引

什么是聚集索引（聚蔟）？，什么是二级索引（非聚蔟索引）？，什么是回表？

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

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


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



4. 覆盖索引和超大分页优化

什么是覆盖索引？

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


MySQL超大分页处理

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。我们一起来看看执行limit分页查询耗时对比：

因为，当在进行分页查询时，如果执行 limit 9000000,10 ，此时需要MySQL排序前9000010 记录，仅仅返回 9000000 - 9000010 的记录，其他记录丢弃，查询排序的代价非常大 。

优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

1
2
3
4
select *
from tb_sku t,
(select id from tb_sku order by id limit 9000000,10) a
where t.id = a.id;

知道什么叫覆盖索引吗？

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

  • 使用id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。
  • 如果返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *

MySQL超大分页怎么处理？

问题：在数据量比较大时，limit分页查询，需要对数据进行排序，效率低

解决方案：覆盖索引+子查询



5. 索引创建原则

索引创建原则有哪些？

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


6. 索引失效

执行计划explain可以判断索引是否失效

什么情况下索引会失效？


  1. 违反最左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引。如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效

  2. 范围查询右边的列，不能使用索引

  3. 不要在索引列上进行运算操作， 索引将失效


  4. 字符串不加单引号，造成索引失效。由于，在查询时没有对字符串加单引号， MySQL的查询优化器，会自动的进行类型转换，造成索引失效


  5. 以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效


索引什么情况下会失效？

  1. 违反最左前缀法则
  2. 范围查询右边的列，不能使用索引
  3. 不要在索引列上进行运算操作， 索引将失效
  4. 字符串不加单引号，造成索引失效。(类型转换)
  5. 以%开头的Like模糊查询，索引失效


7. SQL的优化

  • 表的设计优化
  • 索引优化
  • SQL语句优化
  • 主从复制、读写分离
  • 分库分表

表的设计优化

  1. 比如设置合适的数值（tinyint int bigint），要根据实际情况选择
  2. 比如设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率稍低

SQL语句优化

  1. SELECT语句务必指明字段名称（避免直接使用select * ）

  2. SQL语句要避免造成索引失效的写法

  3. 尽量用union all代替union union会多一次过滤，效率低

    Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序

    Union All：对两个结果集进行并集操作，包括重复行，不进行排序

  4. 避免在where子句中对字段进行表达式操作

  5. Join优化 能用inner join 就不用left join 和 right join，如必须使用 一定要以小表为驱动，内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不会重新调整顺序


主从复制，读写分离

如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响 可以采用读写分离的架构。读写分离解决的是，数据库的写入，影响了查询的效率。



8. 事务

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

事务的特性（ACID）是什么？可以详细说一下嘛？

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

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

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

并发事务问题


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

√ 表示存在该问题 × 表示不存在该问题

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



9. undo log 和redo log 的区别

  • 缓冲池（buffer pool）:主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度
  • 数据页（page）:是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。页中存储的是行数据

redo log

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

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


undo log

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

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

undo log可以实现事务的一致性和原子性


undo log 和 redo log 的区别

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


10. MVCC

事务中的隔离性是如何保证的？

排他锁（如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁）mvcc : 多版本并发控制

MVCC-实现原理

  • 记录中的隐藏字段

  • undo log

    回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。

    当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。

    而update、delete的时候，产生的undo log日志不仅在回滚时需要，mvcc版本访问也需要，不会立即被删除。


事务中的隔离性是如何保证的(MVCC)？

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

  • 隐藏字段：

    1. trx_id(事务id)，记录每一次操作的事务id，是自增的
    2. roll_pointer(回滚指针)，指向上一个版本的事务版本记录地址

  • undo log：

    1. 回滚日志，存储老版本数据
    2. 版本链：多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表

  • ReadView解决的是一个事务查询选择版本的问题

    1. 根据readView的匹配规则和当前的一些事务id判断该访问那个版本的数据

    2. 不同的隔离级别快照读是不一样的，最终的访问的结果不一样

      RC ：每一次执行快照读时生成ReadView

      RR：仅在事务中第一次执行快照读时生成ReadView，后续复用



11. MySQL主从同步原理

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

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

复制分成三步：

  1. Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。
  2. 从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。
  3. slave重做中继日志中的事件，将改变反映它自己的数据。


12. 分库分表

分库分表的时机：

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

拆分策略

  • 垂直分库

    垂直分库：以表为依据，根据业务将不同表拆分到不同库中。

    特点：

    1. 按业务对数据分级管理、维护、监控、扩展
    2. 在高并发下，提高磁盘IO和数据量连接数

  • 垂直分表

    垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。

    特点：

    1. 冷热数据分离
    2. 减少IO过渡争抢，两表互不影响

    拆分规则：

    • 把不常用的字段单独放在一张表
    • 把text，blob等大字段拆分出来放在附表中

  • 水平分库

    水平分库：将一个库的数据拆分到多个库中。

    特点：

    1. 解决了单库大数量，高并发的性能瓶颈问题

    2. 提高了系统的稳定性和可用性

    路由规则:

    • 根据id节点取模
    • 按id也就是范围路由，节点1(1-100万 ),节点2(100万-200万)
    • …

  • 水平分表

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

    特点：

    1. 优化单一表数据量过大而产生的性能问题;
    2. 避免IO争抢并减少锁表的几率;
面试篇
#MySQL
MySQL
https://lzhengjy.github.io/2023/09/17/MySQL面试题/
作者
Zheng
发布于
2023年9月17日
许可协议