预期外的OOM场景

执行SELECT * FROM t1 时，若表t1 数据量较大（如百万级或千万级），且未使用 LIMIT 限制返回行数，结果集会占用服务器会话缓冲区和客户端结果缓存的大量内存。若网络传输或客户端处理速度较慢，内存无法及时释放，可能导致内存利用率持续上升，甚至触发 OOM（内存溢出） 风险。

• 只查询必要字段，而不使用select *。
• 加上limit使用分页查询，而不是一次性查询全量数据。

当查询无法通过索引直接返回有序结果时（如排序字段未建立索引），数据库会执行排序操作。排序过程会优先使用内存缓冲区（sort_buffer），其大小由参数 sort_buffer_size 控制。

• 内存排序：若待排序数据量小于 sort_buffer_size，排序在内存中完成，效率较高。
• 磁盘排序：若数据量超过 sort_buffer_size，数据库会将部分数据写入磁盘临时文件，排序效率显著下降。

每个连接独立分配 sort_buffer：每个执行排序的连接都会分配独立的 sort_buffer，即使单个连接的排序内存需求不高，高并发场景下多个连接同时排序，仍可能导致内存占用激增，甚至触发OOM（内存溢出）。

• 对大表（如上亿行）进行排序，且排序字段无索引，需全表扫描并排序。

  SELECT * FROM massive_table ORDER BY non_indexed_column DESC;

• 复杂的分组聚合，需要先排序再分组。

  SELECT user_id, COUNT(*) FROM huge_log_table GROUP BY user_id;

• 多表 JOIN 后对结果集进行排序，JOIN结果集大，且排序字段无索引，需对海量数据排序。

  SELECT a.*, b.name FROM big_table a
  JOIN another_table b ON a.id = b.a_id
  ORDER BY a.created_at
  LIMIT 1000 OFFSET 1000000;

• 尽可能利用索引避免排序，例如为排序字段建立联合索引。

  若查询为WHERE a = ? ORDER BY b，建立 (a, b) 联合索引，让 ORDER BY 直接利用索引顺序，避免排序。

• 适度调小sort_buffer_size值，确保sort_buffer_size不超过1MB；对于大规格实例可适当增大，但需注意高并发下的内存叠加效应。

当执行大表JOIN且连接条件无索引时，数据库会通过 join_buffer 缓存扫描的行数据以加速JOIN操作。

若连接字段（如 non_indexed_key）无索引，MySQL需对表进行全表扫描，并将所有匹配行加载到 join_buffer 中。

数据量越大，join_buffer占用内存越高，可能导致内存资源快速耗尽。

join_buffer_size 是每个JOIN操作独立分配的。高并发场景下，多个连接同时执行JOIN操作，每个连接都会分配独立的 join_buffer，导致内存占用呈线性增长，可能触发 OOM（内存溢出）。

• 场景1：连接条件无索引，需全表扫描并加载大量数据到 join_buffer。

  SELECT *
  FROM large_table_a A
  JOIN large_table_b B ON A.non_indexed_key = B.non_indexed_key;

• 场景2：笛卡尔积（无ON条件），导致两表所有行组合，生成 M*N 的巨大结果集，内存占用极高。

  SELECT * FROM table1, table2;

• 确保连接列有索引
  • 被驱动表的连接列必须有索引（如 t2.t1_id），这是JOIN操作性能优化的关键。
  • 为 ON 子句中使用的列创建索引

  SQL示例：

  假设查询：SELECT * FROM t1 JOIN t2 ON t1.id = t2.t1_id

  需要为 t2.t1_id 创建索引

  CREATE INDEX idx_t1_id ON t2(t1_id);

• 选择驱动表与被驱动表

  MySQL通常选择小表作为驱动表（可通过STRAIGHT_JOIN 强制指定），减少循环次数。大表作为被驱动表，通过索引加速匹配。

  如下SQL表示强制指定驱动顺序，指定 small_table 为驱动表，large_table 为被驱动表。

  SELECT* FROM small_table t1 STRAIGHT_JOIN large_table t2 ON t1.id = t2.ref_id;

• 通过 EXPLAIN 验证优化效果
  • type字段：确认被驱动表是否使用索引（如 ref 或 range）。
  • rows字段：评估扫描行数是否因索引优化而减少。
  • Extra字段：检查是否出现 Using filesort 或 Using temporary（需进一步优化）。

• 小规模IN字段：当IN列表中的字段数量较少（如几十个以内），MySQL会使用临时数组存储匹配值，内存占用较低。
• 大规模IN字段：当IN列表值数量较大（如数千个以上），可能超出range_optimizer_max_mem_size限制，导致优化器放弃 range 扫描优化，退化为全表扫描，间接引发内存飙升。此外，超大 IN 列表还会增加 SQL 解析和优化阶段的耗时。建议将 IN 列表值数量控制在 200 个以内，超过时使用分批查询或临时表替代。

IN列表包含1000个ID。

SELECT * FROM users WHERE id IN (1, 2, 3, ..., 1000);

优化策略：分批查询 + 业务层合并

将大规模IN列表拆分为多个小批次，降低单次查询的内存占用，避免哈希表过大。

1. 分批查询：
   • 将1000个ID拆分为10批，每批100个。
   • 每次查询仅处理100个ID，减少单次哈希表内存占用。

   -- 第1批：100个ID
   SELECT * FROM users WHERE id IN (1, 2, ..., 100);

   -- 第2批：100个ID
   SELECT * FROM users WHERE id IN (1001, 1002, ..., 200);

   -- 第10批：100个ID
   SELECT * FROM users WHERE id IN (9001, 9002, ..., 1000);

2. 业务层合并：

   在应用层（如Java、Python）合并10批查询结果，最终返回完整数据。

在大批量并发插入或更新操作中，尤其是在大事务场景下，InnoDB 引擎需要维护事务相关的锁信息、Buffer Pool 中的脏页，同时 redo log buffer 和 undo log 相关的内存资源在事务提交前不会完全释放。若操作涉及大量二级索引更新，Buffer Pool 的占用将显著增加，可能导致内存飙升，甚至触发 OOM。

• 事务资源占用：
  • undo log：用于事务回滚和MVCC（多版本并发控制），事务未提交前占用内存。
  • redo log：记录事务对数据页的修改，确保崩溃恢复。
• 二级索引更新：

  每个索引字段的更新（如 UPDATE table SET col1 = value1, col2 = value2）均需维护对应索引树，内存消耗呈线性增长。

• 将单次插入的 VALUES 数量控制在100 条以内，避免单次事务过大。
  分批操作后，及时提交事务（COMMIT），释放占用的undo/redo资源。

  -- 分批插入（每批100条）
  INSERT INTO users (id, name) VALUES  (1, 'Alice'), (2, 'Bob'), ..., (100, 'Zoe');

• 使用 LIMIT N 控制单次更新的行数（建议 N ≤ 100），避免大事务。

  避免在频繁更新的字段上创建非必要索引，减少二级索引更新开销。

  -- 分批更新（每次更新100行）
  UPDATE large_table 
  SET status = 'active' 
  WHERE condition = true 
  LIMIT 100;

当查询或更新操作涉及大量大字段（如 TEXT、BLOB）时，数据库需在内存中缓存更大的结果集，导致内存占用飙升。具体场景包括：

• 结果集缓存：查询大字段时（如 SELECT big_text_col FROM logs），需将整个字段加载到内存中。
• 排序操作：对大字段排序时，sort_buffer 中仅存储前 max_sort_length 字节 + rowid，不会加载完整字段内容；但结果集传输阶段仍需将完整字段读入内存。
• 去重操作：SELECT DISTINCT big_text_col 需在内存中维护临时哈希表或排序结构。
• 更新操作：批量更新大字段（如 UPDATE logs SET big_text_col = REPEAT('a', 10*1024*1024)）会生成大量临时数据，占用内存。

• 场景1：查询大字段，结果集占用大量内存

  SELECT big_text_col FROM logs;  

• 场景2：排序大字段，内存消耗激增

  SELECT * FROM logs ORDER BY big_text_col;  

• 场景3：去重大字段，需维护临时结构

  SELECT DISTINCT big_text_col FROM logs;  

• 场景4：批量更新大字段，生成临时数据

  UPDATE logs SET big_text_col = REPEAT('a', 10*1024*1024);

• 限制查询数据量
  • 分页查询：通过 LIMIT 控制单次返回的行数，避免一次性加载过多数据。
  • 避免全表扫描：若仅需部分字段，避免使用 SELECT *，仅查询必要字段。

• 优化排序与去重
  • 减少排序字段大小：若排序字段为大字段，可提取关键部分（如前缀）进行排序。
  • 使用索引：为排序字段创建索引，避免内存排序。

• 控制更新操作
  • 分批更新：将大字段更新拆分为小批次，避免单次事务过大。
  • 避免冗余更新：仅更新实际需要修改的行，减少不必要的数据处理