随笔
Search K

Appearance

一、问题现象 ​

数据库CPU频繁告警，使用率大于95%，同时伴有慢SQL告警，大于0.5秒

二、sql分析 ​

命中索引，但是扫描行数过多

sql
SELECT *
FROM w_user_brand_provider_money
WHERE id_code = 13247945
  AND is_grow = 0
  AND abm_level = 5
  AND status IN (0, 1)
order by created_at desc
LIMIT 1;

三、扫描行数过多可能引发的问题 ​

在 MySQL 中，扫描的行数过多可能会带来以下几个问题：

1. 性能下降 ​

  • 增加查询时间：扫描大量数据需要消耗更多的 I/O 资源和 CPU 时间，尤其是在数据量较大的情况下，查询会变得非常慢。
  • 锁争用：扫描过多行时，可能会导致表级锁或行级锁的争用，影响其他查询或更新的执行。

2. 内存和缓存压力 ​

  • 缓存命中率下降：如果扫描的数据量大于 MySQL 的缓存（如 InnoDB Buffer Pool 或查询缓存）的容量，MySQL 需要频繁地从磁盘读取数据，降低缓存命中率，增加 I/O 开销。
  • 内存溢出风险：如果查询涉及的数据量超过服务器内存，可能会导致 MySQL 服务崩溃或操作系统触发内存交换（swap），进一步降低系统性能。

3. I/O 负载过高 ​

  • 磁盘 I/O 开销：扫描大量行通常意味着大量的磁盘读写操作，尤其是全表扫描。这会增加磁盘的负载，进而影响数据库的响应时间和吞吐量。
  • 磁盘抖动（Disk Thrashing）：如果磁盘 I/O 负载过高，系统可能会因为频繁的 I/O 操作而导致性能大幅下降，甚至引发系统瓶颈。

4. 数据库锁和并发问题 ​

  • 锁等待：扫描大量行可能导致更长时间的锁持有，特别是在事务中执行大规模查询或更新时，其他事务可能因为锁等待而导致延迟或死锁。
  • 并发查询影响：大量行的扫描可能会拖慢其他并发执行的查询，影响数据库的整体并发能力。

5. 查询计划不优化 ​

  • 全表扫描（Full Table Scan）：如果查询没有合适的索引，可能会导致全表扫描，而不是使用索引来加速查询。这是导致扫描行数过多的常见原因。
  • 错误的索引使用：即便存在索引，如果查询执行计划没有正确使用索引，也可能会导致扫描的行数过多。

6. 潜在的数据一致性问题 ​

  • 长时间运行的查询：扫描大量行的查询往往需要更长的时间，在高并发的环境下，长时间运行的查询可能会读取到不一致的数据（特别是非事务型查询）。

四、磁盘IO触发机制 ​

在 MySQL 中，触发磁盘 I/O 操作的条件并不完全取决于扫描的行数，而是取决于以下几个因素：

1. InnoDB 缓存机制 ​

InnoDB 存储引擎使用了 Buffer Pool 来缓存数据页和索引页。每次查询时，MySQL 会首先从 Buffer Pool 中读取数据。如果数据已经在内存中，查询就不需要额外的磁盘 I/O 操作；如果数据不在内存中，MySQL 会从磁盘加载相应的数据页到 Buffer Pool 中，这时才会触发磁盘 I/O。

  • 页大小：InnoDB 的默认页大小是 16KB，当需要访问某一行时，MySQL 并不是逐行读取，而是按页读取。这意味着如果一行的数据在某个页面内且该页面已缓存在 Buffer Pool 中，就不需要触发磁盘 I/O。
  • 缓存命中率：如果查询的数据或索引已经被缓存，多次扫描不会产生新的磁盘 I/O 操作。如果缓存未命中，就会从磁盘加载更多的数据页，从而产生 I/O。

2. 页扫描 vs. 行扫描 ​

MySQL 并不是按照行来进行磁盘 I/O，而是按照 数据页。每个数据页包含了多行数据，因此在扫描过程中，MySQL 一次性会从磁盘读取整个数据页，而不是逐行扫描。所以，决定是否触发磁盘 I/O 的主要因素是是否需要从磁盘加载新的数据页。

示例：假设每个数据页包含 100 行数据，如果查询需要扫描 200 行，且这 200 行所在的页面都不在内存中，那么 MySQL 需要读取 2 个数据页，这时就会触发 2 次磁盘 I/O。

3. 索引使用情况 ​

如果查询使用了合适的索引，MySQL 只需要扫描相对较少的数据页来获取需要的行。如果查询没有使用索引或者进行了全表扫描，则可能需要读取更多的数据页，增加磁盘 I/O 操作的可能性。

4. 磁盘 I/O 触发场景 ​

以下情况会更容易触发磁盘 I/O 操作：

  • 缓存未命中：当查询的数据页未被缓存在 Buffer Pool 中时，MySQL 需要从磁盘加载这些页。
  • 全表扫描：如果查询没有使用索引，MySQL 需要扫描整张表的数据页，这会触发大量的磁盘 I/O，尤其是表较大的时候。
  • 大范围查询：当查询返回大量行，且这些行分布在多个数据页上，MySQL 可能需要从磁盘加载大量数据页。
  • 写入操作：插入、更新或删除操作也会涉及到数据页的修改和刷新，这也会触发磁盘 I/O。

如何减少磁盘 I/O： ​

  • 增加 Buffer Pool 大小：通过增大 Buffer Pool 的大小，减少从磁盘读取数据页的频率。
  • 使用合适的索引：优化查询，确保查询条件能够有效使用索引，避免全表扫描。
  • 查询优化：限制返回行的数量，尽量减少扫描的范围和数据量。
  • 数据库分区：对于特别大的表，考虑使用分区表，减少一次性需要扫描的数据量。

五、索引的存储 ​

1. 磁盘上的索引 ​

索引最初是存储在磁盘上的，特别是在数据量较大或表很大的情况下，所有索引不可能完全放在内存中。MySQL 会把索引的数据存储在与表数据相同的文件中。

  • InnoDB：索引和数据存储在数据文件（如 .ibd 文件）中，数据和索引按页存储，每个页的大小默认为 16KB。
  • MyISAM：索引存储在独立的 .MYI 文件中，数据存储在 .MYD 文件中。

当 MySQL 需要访问某些行时，如果需要使用索引来定位行，MySQL 会从磁盘上读取相应的索引页到内存中进行查找。

2. 内存中的索引 ​

为了提高性能，MySQL 会将部分索引缓存到内存中，以减少每次查询时从磁盘读取的时间。这些缓存的索引存储在不同的缓存结构中，主要有：

InnoDB Buffer Pool（InnoDB 存储引擎） ​

  • InnoDB Buffer Pool 是 MySQL 用于缓存数据页和索引页的内存区域。它的作用是将频繁使用的数据和索引保存在内存中，以提高查询的性能。
  • 当查询使用索引时，InnoDB 会先检查所需的索引页是否已经在 Buffer Pool 中。如果在内存中找到了索引页，则直接使用；如果没有找到，则从磁盘加载相应的索引页到 Buffer Pool。
  • 缓存策略：InnoDB 使用 LRU（最近最少使用）算法管理 Buffer Pool，会将最常使用的数据和索引页保留在内存中，而较少使用的会逐渐被淘汰。

Key Cache（MyISAM 存储引擎） ​

  • Key Cache 是 MyISAM 引擎的索引缓存区域。MyISAM 存储引擎将索引存储在磁盘上的 .MYI 文件中，并将使用频繁的索引块缓存在内存的 Key Cache 中。
  • 通过调整 Key Cache 的大小，可以提升 MyISAM 引擎的索引访问性能。

3. 索引的加载和淘汰 ​

  • 索引不会一开始就全部加载到内存中，只有在查询过程中需要使用到的索引页才会被加载到内存（Buffer Pool 或 Key Cache）中。
  • 当缓存区满了之后，MySQL 会根据缓存策略将不常用的页淘汰出缓存，腾出空间给新的数据和索引页。

4. 如何查看索引是否在内存中 ​

在 InnoDB 中，可以通过以下方式监控缓存的使用情况：

  • 使用 SHOW ENGINE INNODB STATUS 命令查看 Buffer Pool 的使用情况，包括缓存的命中率。
  • 使用 SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%' 查看有关从 Buffer Pool 读取数据和索引页的统计信息。
  • Innodb_buffer_pool_read_requests：表示从 Buffer Pool 中读取数据页和索引页的请求数。
  • Innodb_buffer_pool_reads：表示从磁盘读取数据页或索引页的次数。这个值越高，说明缓存命中率较低，需要优化。

5. 优化内存中索引的利用 ​

  • 增加 InnoDB Buffer Pool 的大小：确保更多的索引和数据能够被缓存在内存中，减少磁盘 I/O 操作。
  • 使用合适的索引：确保查询使用了合适的索引，避免不必要的全表扫描或索引扫描，这样可以更好地利用缓存资源。
  • 定期分析和优化表：使用 ANALYZE TABLE 和 OPTIMIZE TABLE 可以帮助 MySQL 更好地使用索引，提升查询性能。

Last updated: