时间戳

首页 > 解决方案 > 使用子查询的查询比使用固定数据而不是子查询的相同查询需要更长的时间

mysql - 使用子查询的查询比使用固定数据而不是子查询的相同查询需要更长的时间

问题描述

组合查询

select a, b from A where a > 5 and b in (select b from B where c = "some")

比固定查询花费大约 30 倍的时间

select a, b from A where a > 5 and b in (1, 2, 3)

虽然

A 上的 (a, b) 上有一个索引。

分析组合查询：

analyze select a, b from A where a > 5 and b in (select b from B)
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: A
         type: range
possible_keys: idx_a_b
          key: idx_a_b
      key_len: 8
          ref: NULL
         rows: 126459
       r_rows: 66181.00
     filtered: 100.00
   r_filtered: 100.00
        Extra: Using index condition; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: B
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: A.b
         rows: 1
       r_rows: 1.00
     filtered: 100.00
   r_filtered: 0.09
        Extra: Using where

请注意， r_rows = 66181 匹配select a, b from A where a > 5.

似乎 MariaDB 仅使用索引的一部分而忽略了它应该能够在第一步中从子查询中获得的 b。解释扩展显示 MariaDB 将我的查询替换为

select b, a from B join A where ((B.b = A.b) and (A.a > 5) and (B.c = "some"))

奇怪的是，如果给定子查询返回的固定集合 (1, 2, 3)，而不是子查询本身，MariaDB 确实似乎同时使用了索引的 a 和 b，这可以通过分析观察到固定查询：

analyze select a, b from A where a > 5 and y in (1, 2, 3)
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: A
         type: range
possible_keys: idx_a_b
          key: idx_a_b
      key_len: 10
          ref: NULL
         rows: 126459
       r_rows: 59.00
     filtered: 100.00
   r_filtered: 100.00
        Extra: Using index condition; Using temporary; Using filesort

r_rows = 59 匹配两个查询（组合查询和固定查询）的结果集大小。

如何让 MariaDB 使用与固定查询相同的查询计划，同时使用 A 索引中的 a 和子查询的 b？

标签： mysqlsqlindexingsubquerymariadb

解决方案

有时，查询的优化方式不同。当你有一个固定的值列表时，查询计划器会更了解你在做什么。

如果您在 中没有重复项b，则join通常会产生一个好的执行计划：

select a.a, a.b
from a join
     b
     on a.b = b.b
where a.a > 5;

我还建议尝试exists：

select a, b
from A
where a > 5 and
      exists (select 1 from B where b.b = a.b) ;

并确保您有一个索引b(b)：

create index idx_b_b on b(b);

推荐阅读