MySQL进阶

如何定位慢查询

常见慢查询

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

表象: 页面加载过慢、接口压测响应时间过长（超过一秒）

方案一: 开源工具

• 调试工具: Arthas
• 运维工具: Prometheus、Skywalking(我用的这个)

方案二: Mysql自带慢日志

慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位: 秒,默认10秒)的所有SQL语句的日志(调试阶段);

一般在生产环境下是不会开启的,因为会损耗一些MySQL的性能;

如果要开启慢查询日志,需要在Mysql的配置文件(/etc/my.cnf)中配置如下信息:

# 设置为1开启MySQL慢日志查询开关
slow_query_log = 1
# 设置慢日志存放的位置
slow_query_log_file = "存放日志文件的绝对路径"
# 设置慢日志的判断标准为1秒,SQL语句执行时间超过1秒,就会被视为慢查询,记录慢日志
long_query_time = 1
# 记录未使用索引的查询
#如果一个查询没有使用索引，MySQL可能需要进行全表扫描（Full Table Scan）来查找数据。对于大表来说，这种操作会非常耗时，并且消耗大量的系统资源。
log_queries_not_using_indexes = 1

配置完并保存后,需要重启mysql服务
操作: Win+R,输入services.msc,找到mysql服务,点击重启动此服务

在MySQL图形化操作界面输入

SHOW VARIABLES LIKE 'slow_query_log';

value为ON则开启了慢查询日志记录

如何分析优化慢查询

• 聚合查询
  可以增加一张临时表去解决

• 多表查询

  ​ 优化SQL语句结构

• 表数据量过大查询

  ​ 分析业务添加索引,避免MySQL扫描整张表进行查询

  以上三个通过SQL执行计划,分析并找到慢的原因

一个SQL语句执行很慢,如何进行分析

可以采用MySQL自带分析工具 EXPLAIN 或者 DESC ,命令获取MySQL如何执行SELECT语句的信息

展示的内容就不是数据了,而是SQL语句执行的情况

• 深度分页查询(覆盖索引)

了解过索引吗(什么是索引)

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

参考回答:

• 索引是帮助MySQL高效获取数据的数据结构
• 提高数据检索的效率,降低数据库的IO成本,因为不需要全表扫描
• 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗

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

在MySQL默认存储引擎InnoDB里,索引的底层数据结构是B+树

MySQL为什么不使用二叉树、红黑树、B树这些，而采用B+树作为索引的底层数据结构

---

1. 磁盘 I/O 效率更高

• B+树的非叶子节点不存储数据，仅存储键值和子节点指针，因此单个节点可以容纳更多的索引项，从而降低树的高度，减少磁盘 I/O 次数。
• 对比 B 树：B 树的每个节点都存储数据，导致节点能容纳的键值更少，树的高度更高，查询时需要更多的磁盘 I/O。
• 对比二叉树/红黑树：二叉树和红黑树在极端情况下可能退化成链表（如自增主键插入），导致查询复杂度从 O(log n) 退化为 O(n)，而 B+树始终保持平衡，查询稳定在 O(log n)。

---

2. 范围查询性能更优

• B+树的叶子节点通过双向链表连接，使得范围查询（如 WHERE id BETWEEN 100 AND 200）只需遍历叶子节点链表即可，无需回溯上层节点。
• 对比 B 树：B 树的数据分散在各个节点，范围查询需要多次随机 I/O，效率较低。
• 对比红黑树：红黑树没有链表结构，范围查询需要多次递归遍历，性能较差。

---

3. 更适合数据库的存储结构

• B+树的节点大小通常与磁盘页（16KB）对齐，减少磁盘碎片，提高读写效率。
• B 树的数据分散存储，可能导致节点分裂更频繁，影响插入和删除性能。
• 二叉树/红黑树的节点大小不固定，无法充分利用磁盘块，导致 I/O 效率低下。

---

4. 更高的缓存命中率

• B+树的非叶子节点仅存储索引，可以缓存更多索引信息，减少内存占用，提高缓存命中率。
• B 树的节点存储数据，缓存利用率较低，可能频繁触发磁盘读取。

---

5. 更稳定的查询性能

• B+树的所有查询都必须到达叶子节点，因此查询时间稳定在 O(log n)，而 B 树可能在中间节点找到数据（最快 O(1)），但整体性能波动较大。
• 红黑树的平衡性依赖旋转操作，在高并发写入时可能影响查询性能，而 B+树的调整更局部化，影响较小。

---

对比

数据结构	磁盘 I/O 效率	范围查询	节点存储方式	适用场景
B+树	极高（节点仅索引）	极优（链表遍历）	非叶子节点仅索引，数据在叶子节点	数据库索引
B 树	较高（节点含数据）	较差（需多次 I/O）	所有节点存储数据	文件系统
红黑树	低（高度不平衡）	差（无链表）	节点存储数据	内存索引
二叉树	极低（可能退化成链表）	极差	节点存储数据	不适用

综合来看,MySQL 选择 B+树 作为索引结构，主要是为了优化 磁盘 I/O、范围查询、缓存利用率 和 稳定性，使其更适合大规模数据存储和高并发查询场景。

​

B+树是在B树基础上的一种优化,使其更适合实现外存储索引结构,B+树只在叶子结点存储数据

参考回答:

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

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

什么是聚簇索引(也称聚集索引)?什么是非聚簇索引(也称二级索引或非聚集索引)?

• 聚簇索引: 将数据存储与索引放到了一块,索引数据结构的叶子节点保存了行数据
  特点: 必须有,而且只有一个
• 二级索引: 将数据与索引分开存储,索引数据结构的叶子节点关联的是对应的主键
  特点: 可以存在多个

聚集索引选取规则:

1. ​ 如果存在主键,主键索引就是聚集索引。
2. ​ 如果不存在主键,将使用第一个非空的(NOT NULL)唯一(UNIQUE)索引作为聚集索引。
3. ​ 如果表没有主键,或没有合适的唯一索引,InnoDB会自动生成一个6字节的单调递增的rowId作为隐藏的聚集索引。因为 row ID 的生成和维护会增加额外的开销,所以性能一般较差。
4. ​ MyISAM 和 InnoDB 的区别
   上述规则仅适用于 InnoDB 存储引擎。
   在 MyISAM 中，聚集索引的概念并不存在，MyISAM 使用的是堆表结构（Heap Table），数据按插入顺序存储，所有索引都是非聚集索引。

什么是回表查询?

​ 我们知道二级索引的B+树叶子节点存储的是对应的主键,通过二级索引找到对应的主键值,再到聚簇索引的B+树中查找整行数据,这个过程就是回表;

什么是覆盖索引?

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

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

MySQL超大分页怎么处理?

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

**解决方案:**创建覆盖索引能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化

select * from employee e,
(select id from employee order by id limit 9000000,10) e1
where e.id=e1.id

索引的创建原则有哪些?

• 主键索引
• 唯一索引
• 复合索引

创建原则:

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

什么情况下索引会失效?

• 违反最左前缀法则
  如果索引了多列,要遵循最左前缀法则。指的是查询从索引的最左列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引:

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

前两个字段name，status查询是走索引的，但是最后一个条件address没有用到索引。

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

• 字符串不加单引号，造成索引失效

查询时,没有对字符串加单引号,MySQL的查询优化器,会自动地进行类型转换,造成索引失效。

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

谈一谈对SQL优化的经验

• 表的设计优化 (数据类型的选择)
• 索引优化 (结合索引创建原则来说)
• SQL语句优化 (SQL语句避免造成索引失效的写法,select语句最好指明需要的字段)
• 主从复制、读写分离
• 分库分表 (一张表数据超过500万)

1. 表的设计优化(参考阿里开发手册《嵩山版》)
   比如设置合适的数值（tinyint int bigint），要根据实际情况选择
   比如设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率较低
2. SQL语句优化
   select语句务必指明字段名称（避免直接使用select *）
   SQL语句要避免造成索引失效的写法
   尽量用union all代替union， union会多一次过滤，效率低
   避免在where子句中对字段进行表达式操作
   join优化 能用inner join就不用left join和right join，如必须使用 一定要以小表为驱动，内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。 left join或right join，不会重新调整顺序,所以写左连接和外连接代码时候必须以小表为驱动,从而获得更好的性能
   
   建议将图片保存下来直接上传](https://img-建立三次数据库连接,每次连接执行1000次SQL
3. 主从复制、读写分离
   如果数据库的使用场景读的操作比较多的时候,为了避免写的操作所造成的性能影响 可以采用读写分离的架构。
   读写分离解决的是，数据库的写入，影响了查询的效率

事务相关

事务的四大特性是什么? 详细说一下?

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

比如我给你支付宝转100万，这个操作通常包括两个步骤，一是从我的账户减钱，二是往你的账户加钱，一步失败，整个操作都得回滚

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

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

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

并发事务问题

怎么解决并发事务的问题呢?(隔离级别)

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

注意：事务隔离级别越高，数据越安全，但是性能越低。通常情况下使用默认的隔离级别

undo log和redo log的区别

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

redo log

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

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

undo log

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

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

事务的隔离性是如何保证的?

锁：排他锁（如一个事务获取了一个数据行的排他锁，其他事务就不能获取该行的其他锁）

mvcc：多版本并发控制

解释一下MVCC

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

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

MVCC实现原理

• 记录中的隐藏字段
  

隐藏字段	含义
DB_TRX_ID	最近修改事务ID,记录插入这条记录或最后一次修改该记录的事务ID.
DB_ROLL_PTR	回滚指针,指向这条记录的上一个版本,用于配合undo log,指向上一个版本.
DB_ROW_ID	隐藏主键,如果表结构没有指定主键,将会生成该隐藏字段.

• undo log

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

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

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

不同事务或相同事务对同一条记录进行修改,会导致该记录的undo log生成一条记录版本链表,链表的头部是最新的旧数据,链表尾部是最早的旧记录。

• readview

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

1. 当前读
   读取的是记录的最新版本,读取时还要保证其它并发事务不能修改当前记录,会对读取的记录进行加锁。对于我们日常的操作，如：select…lock in share mode（）共享锁，select … for update、update、insert、delete（排他锁）都是一种当前读。
2. 快照读
   简单的select（不加锁）就是快照读，快照读 读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
   • Read Committed：每次select，都生成一个快照读。
   • Repeatable Read：开启事务后第一个select语句才是快照读的地方。

字段	含义
m_ids	当前活跃的事务ID集合
min_trx_id	最小活跃事务ID
max_trx_id	预分配事务ID,当前最大事务ID+1(因为事务ID是自增的)
creator_trx_id	ReadView创建者的事务ID

不同隔离级别下,生成ReadView的时机不同:

• READ COMMITTED: 在事务中每一次执行快照读时生成ReadView。
• REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

MySQL主从同步原理

前面提过一嘴主从同步,现在详细讲下

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

二进制日志binlog(记录了所有的DDL语句和DML语句),但不包括数据查询(SELECT、SHOW)语句

MySQL分库分表

主从同步只是分担了访问压力,让读写分开。但是解决不了海量数据插入的问题

分库分表的时机:

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