Mysql大师之路：mysql性能调优和高可用

饭叮叮的学习日记

已于 2024-08-31 05:06:49 修改

阅读量909

点赞数 20

文章标签： mysql 数据库

于 2024-08-31 05:06:47 首次发布

本文链接：https://blog.csdn.net/m0_55839103/article/details/141731710

版权

`EXPLAIN` 输出的关键字段

id: 查询的序列号，包含一组数字，表示查询中执行的顺序。
select_type: 查询的类型，表示查询是简单查询、联合查询或子查询等。
table: 查询的表名。
type: 表示访问类型，这是一个非常重要的字段，用来描述 MySQL 找到所需行的方式。常见的类型按效率从低到高排序如下：
- ALL: 全表扫描。最差的情况，表示 MySQL 需要遍历整个表来找到所需的行。此时没有使用索引。
- index: 全索引扫描。类似于全表扫描，但扫描的是索引而不是数据行。
- range: 索引范围扫描。使用了索引，但扫描了一个范围的行。例如，使用 BETWEEN 或 IN 操作符时会出现这种类型。
- ref: 非唯一索引扫描。使用了非唯一索引（或唯一索引的非唯一前缀），这意味着返回的行可能有多个。
- eq_ref: 唯一索引扫描。这种类型通常出现在多表连接中，使用主键或唯一索引进行等值查询，每次只返回一行。
- const/system: 表示最多有一个匹配行，因此它是一个常量，并且是效率最高的查询方式之一。
possible_keys: 可能用到的索引。列出查询中可能用到的索引。这个字段显示了查询中可能使用的所有索引。
key: 实际使用的索引。如果该字段的值是 NULL，表示没有使用索引。
key_len: 使用的索引的长度。表示 MySQL 实际使用的索引的字节数。这是计算存取效率的重要指标。
ref: 显示使用了哪个列或常数与 key 一起从表中选择行。
rows: 扫描的行数。MySQL 估计要读取多少行才能找到查询结果。
Extra: 额外的信息。显示其他执行信息，如 Using where 表示使用了 WHERE 过滤行，Using index 表示覆盖索引扫描等。

如何判断是否使用了索引

查看 key 字段:
- 如果 key 字段的值是某个索引的名称，表示使用了该索引。
- 如果 key 字段的值为 NULL，表示没有使用索引。
查看 type 字段:
- ALL: 表示没有使用索引，全表扫描。应该尽量避免这种情况。
- index: 表示索引扫描，但仍然是全表扫描的一种形式，效率较低。
- range/ref/eq_ref: 表示不同程度地使用了索引，其中 range 和 eq_ref 是最优的使用索引方式。
- const/system: 表示查询使用了常量或系统表进行查询，是非常高效的访问类型。

如何优化查询以使用索引

要优化查询以使用索引，可以考虑以下几点：

使用索引列的直接比较：尽量避免在 WHERE 子句中对索引列进行函数操作或计算。比如改为 id = 9 而不是 id + 1 = 10。
选择合适的索引：确保在查询条件中使用的列上建立了适当的索引。
检查和调整索引：使用 ANALYZE TABLE 和 SHOW INDEX FROM table_name 来检查和调整表上的索引情况。

`Using index`

含义: Using index 表示查询使用了覆盖索引（covering index）。覆盖索引是指查询所需的所有列数据都可以从索引中直接获取，而无需访问数据表的行。
优化效果: 使用覆盖索引时，MySQL 可以直接从索引中读取所有需要的数据，而不需要去访问实际的数据行（即不需要“回表”）。这通常会显著提高查询性能，因为读取索引通常比读取表中的数据更快。

`Using where`

含义: Using where 表示 MySQL 服务器在存储引擎检索行后，还需要进行额外的过滤操作。也就是说，存储引擎返回的行不一定满足 WHERE 子句的条件，MySQL 需要对这些行进行进一步的筛选。
优化效果: 当 Using where 出现时，表示 MySQL 服务器层还需对返回的数据进行条件检查，这可能会增加额外的处理开销，尤其是当 WHERE 子句过滤条件复杂或者数据量较大时。

如何找到慢 SQL

为了找到执行较慢的 SQL 查询，MySQL 提供了 慢查询日志（Slow Query Log） 功能。慢查询日志记录了执行时间超过设定阈值的 SQL 语句。

如何优化慢 SQL

一旦找到慢 SQL，就需要进行优化。常见的 SQL 优化方法如下：

优化数据访问:
- 减少数据行数: 使用 LIMIT 子句缩减查询返回的数据行数，避免无必要的大量数据读取。
- 避免 SELECT *: 仅查询所需的列，而不是使用 SELECT *，以减少数据传输量和内存使用。
拆分查询:
- 将大查询拆分为多个小查询，分而治之，逐步处理数据。这样可以减少单次查询的开销，并降低锁的持续时间。例如，对于需要删除大量数据的查询，可以批量删除，每次只处理一小部分数据，然后稍作等待，再处理下一批数据。
覆盖索引:
- 使用覆盖索引（covering index），即查询所需的所有列都包含在索引中，这样查询可以直接从索引中获取数据，避免回表，提高查询性能。如果没有覆盖索引，可以考虑添加合适的索引。
避免索引失效:
- 检查 SQL 语句是否存在导致索引失效的情况，例如对索引列进行函数操作或计算，未遵循最左前缀原则的联合索引等。这些情况会导致 MySQL 不使用索引，而进行全表扫描。
- 避免在 WHERE 子句中对索引列进行函数操作或计算。例如，将 WHERE YEAR(date) = 2020 改为 WHERE date BETWEEN '2020-01-01' AND '2020-12-31'。
分解联表查询:
- 考虑将复杂的联表查询分解为多个简单的单表查询，并在应用程序中进行数据聚合。或者通过增加冗余字段来减少联表查询的需求。
排序优化:
- 对于需要排序的场景，如果 EXPLAIN 的 Extra 字段中显示 Using filesort，表示使用了文件排序。可以通过对排序字段建立索引（或联合索引）来优化查询，使得排序操作可以利用索引的有序特性，避免文件排序。

深分页场景

使用传统的 LIMIT offset, count 方式会导致性能问题，特别是当 offset 很大时，因为 MySQL 需要扫描大量不必要的行，丢弃这些行，然后返回所需的结果集。这会导致大量的 I/O 操作，特别是在使用二级索引的情况下，回表的开销更大。针对深分页的性能问题，可以采用以下两种优化方案：

1. 记录上一次的最后一条记录（优化方式：基于游标的分页）

这种方法适合用户的分页操作是按顺序加载下一页的情形。例如，“上一页”与“下一页”的页面导航。通过这种方式，可以避免大偏移量带来的性能问题。

记录上一页的最后一条记录的标识（如 ID 或其他唯一字段）。
基于游标位置进行分页查询，直接从上一次查询结果的最后一条记录开始，跳过不必要的行。

2. 使用子查询优化（优化方式：减少回表操作）

这种方法适用于用户需要深度跳转到特定页面的情况，比如直接跳转到第 100 页。这种方法通过减少回表操作来优化查询性能。

使用子查询获取需要的主键 ID，然后再根据这些主键 ID 查询实际的数据行。
避免扫描和丢弃大量不必要的行，从而减少 I/O 操作。

MySQL 主从复制的三个阶段

MySQL 主从复制主要分为三个阶段：写入 binlog 日志、同步 binlog 日志、和回放 binlog 日志。下面是每个阶段的详细解释：

写入 binlog 日志（主库操作）：
- 当主库（Primary/主节点）上的数据发生更改（如 INSERT、UPDATE、DELETE 操作）时，这些操作会被记录在 binlog（二进制日志）中。binlog 日志用于记录所有对数据库进行更改的 SQL 语句。
- 主库写入 binlog 后，这些日志文件成为从库进行复制操作的基础。
同步 binlog 日志（从库接收日志）：
- 从库（Secondary/从节点）连接到主库后，主库会创建一个 log dump 线程，将 binlog 日志内容发送到从库。
- 从库创建一个专门的 I/O 线程，连接到主库的 log dump 线程，用于接收来自主库的 binlog 日志。收到 binlog 日志后，从库将这些日志写入到自己的 中继日志（relay log） 中。
- 一旦从库成功写入中继日志，它会向主库返回一个“复制成功”的响应，表明该日志部分已经安全地复制到从库。
回放 binlog 日志（从库更新数据）：
- 从库会创建一个 SQL 线程，负责读取中继日志（relay log）并解析 binlog 日志中的 SQL 语句。然后，这些 SQL 语句会在从库的存储引擎中重新执行，更新从库中的数据。
- 通过回放 binlog 日志，从库逐步追赶主库的状态，实现数据的一致性。

通过上述三个阶段的处理，MySQL 实现了主从复制，确保主库和从库之间的数据一致性。

MySQL 的复制模式

MySQL 提供了三种复制模式：同步复制、半同步复制、和异步复制。每种复制模式适合不同的应用场景。

异步复制（Asynchronous Replication）：
- 特点：主库在提交事务后，不等待从库的任何确认，立即向客户端返回结果。
- 优点：性能最佳，因为没有等待延迟，主库的事务提交速度很快。
- 缺点：如果主库宕机，有可能会丢失一些事务，因为这些事务可能还未同步到从库。
- 适用场景：对数据一致性要求不高，但性能要求较高的场景。
半同步复制（Semi-Synchronous Replication）：
- 特点：主库在提交事务后，会等待至少一个从库确认已接收到并写入 binlog 的响应后，才返回给客户端。
- 优点：在主库宕机的情况下，至少有一个从库有最新的事务数据，减少了数据丢失的风险。
- 缺点：性能略低于异步复制，因为需要等待至少一个从库的确认，但相比同步复制延迟更低。
- 适用场景：需要一定的数据安全性但不希望完全牺牲性能的场景，如对数据有较高要求的中等负载业务。
同步复制（Synchronous Replication）：
- 特点：主库在提交事务后，需要等待所有从库都确认已成功复制该事务，并写入 binlog 后，才返回给客户端。
- 优点：提供最高的数据一致性和安全性，确保所有从库都具有相同的数据。
- 缺点：性能最差，因为事务提交的延迟最大，需要等待所有从库的确认。
- 适用场景：对数据一致性要求极高的场景，如金融系统或需要绝对数据准确性的业务场景。

MySQL 主从复制的数据延迟：

使用缓存：将数据写入主库的同时写入缓存，后续读操作优先查询缓存，减少对从库的读取压力。但需要注意缓存与数据库之间的一致性问题。
直接查询主库：对于对数据延迟敏感的业务，强制读主库，确保获取最新数据。适用于读操作量较小且需要高实时性的数据。
优化主从复制配置：
- 提高主从库之间的网络带宽和稳定性。
- 使用并行复制和更多 I/O 线程，减少复制延迟。
- 减少从库的负载，专用从库分担特定任务。
数据分片（Sharding）：将数据分片到多个主从复制集群中，分散负载，减少单个集群的压力，提升性能。
使用半同步复制：在主库提交事务前，等待至少一个从库确认接收数据，降低数据丢失风险，同时减少延迟。