关联分区表查询优化：如何让SELECT仅扫描单个分区？

数据表结构 #

表bases：

• id int（主键PK）
• descripcion char
• iestado int
• tipo int

表prospectos_x_bases：

• id int（主键PK）
• base int
• prospecto
• provincia
• iestado int

表prospectos（按provincia字段分区）：

• provincia int
• id int
• nombre char
• telefono_fijo int
• telefono_movil int
• domicilio char
• 主键（PK）：provincia, id
• 二级键（SK）：telefono_fijo、telefono_movil

查询现象对比 #

全分区扫描的关联查询 #

EXPLAIN format=json
 SELECT  prospectos_x_bases.base, prospectos.id,prospectos.nombre
    FROM  prospectos_x_bases
    JOIN  prospects
       ON prospectos.provincia = prospectos_x_bases.provincia
      and  prospectos_x_bases.prospecto = prospectos.id;

执行该查询时，会扫描prospectos表的所有分区。

单分区扫描的查询 #

EXPLAIN format=json 
SELECT  prospectos_x_bases.base, prospectos.id,prospectos.nombre
    FROM  prospectos_x_bases
    JOIN  prospectos  ON prospectos.provincia = 20
      and  prospectos_x_bases.prospecto = prospectos.id;

执行该查询时，仅扫描prospectos表的单个分区（p20）。

我明白这是因为第一个查询中数据库引擎无法提前获知provincia字段的值，所以只能扫描所有分区。

问题 #

如何让这类关联SELECT查询仅使用单个分区？是否可以拆分两次查询替代关联？是否需要先保存第一次查询的结果再执行第二次查询？

prospectos表的分区定义 #

PARTITION BY RANGE (`provincia`)
(
PARTITION p01 VALUES LESS THAN (2) ENGINE=InnoDB,
PARTITION p02 VALUES LESS THAN (3) ENGINE=InnoDB,
PARTITION p03 VALUES LESS THAN (4) ENGINE=InnoDB,
PARTITION p04 VALUES LESS THAN (5) ENGINE=InnoDB,
PARTITION p05 VALUES LESS THAN (6) ENGINE=InnoDB,
PARTITION p06 VALUES LESS THAN (7) ENGINE=InnoDB,
PARTITION p07 VALUES LESS THAN (8) ENGINE=InnoDB,
PARTITION p08 VALUES LESS THAN (9) ENGINE=InnoDB,
PARTITION p09 VALUES LESS THAN (10) ENGINE=InnoDB,
PARTITION p10 VALUES LESS THAN (11) ENGINE=InnoDB,
PARTITION p11 VALUES LESS THAN (12) ENGINE=InnoDB,
PARTITION p12 VALUES LESS THAN (13) ENGINE=InnoDB,
PARTITION p13 VALUES LESS THAN (14) ENGINE=InnoDB,
PARTITION p14 VALUES LESS THAN (15) ENGINE=InnoDB,
PARTITION p15 VALUES LESS THAN (16) ENGINE=InnoDB,
PARTITION p16 VALUES LESS THAN (17) ENGINE=InnoDB,
PARTITION p17 VALUES LESS THAN (18) ENGINE=InnoDB,
PARTITION p18 VALUES LESS THAN (19) ENGINE=InnoDB,
PARTITION p19 VALUES LESS THAN (20) ENGINE=InnoDB,
PARTITION p20 VALUES LESS THAN (21) ENGINE=InnoDB,
PARTITION p21 VALUES LESS THAN (22) ENGINE=InnoDB,
PARTITION p22 VALUES LESS THAN (23) ENGINE=InnoDB,
PARTITION p23 VALUES LESS THAN (24) ENGINE=InnoDB,
PARTITION p24 VALUES LESS THAN (25) ENGINE=InnoDB,
PARTITION p99 VALUES LESS THAN MAXVALUE ENGINE=InnoDB
);

实际业务代码 #

SELECT id FROM bases
     WHERE bases.estado = 0;
WHILE($row = mysqli_fetch_array($result))
{          
  $base = $row['id'];
  SELECT  prospectos_x_bases.base, prospectos.id,prospectos.nombre,
          prospectos.telefono_fijo,prospectos.telefono_movil
    FROM  prospectos_x_bases
    JOIN  prospectos
       ON prospectos.provincia = prospectos_x_bases.provincia
     and  prospectos.id        = prospectos_x_bases.prospecto
    WHERE prospectos_x_bases.base = $base;
}

解决方案 #

1. 按provincia拆分查询（推荐） #

既然prospectos是按provincia分区的，你可以先从prospectos_x_bases中获取当前base对应的所有provincia值，再逐个针对每个provincia执行关联查询，这样就能精准命中单个分区：

// 第一步：获取当前base对应的所有唯一provincia值
$base = $row['id'];
$provincia_result = mysqli_query($conn, "SELECT DISTINCT provincia FROM prospectos_x_bases WHERE base = $base");

while($prov_row = mysqli_fetch_array($provincia_result)) {
    $provincia = $prov_row['provincia'];
    // 第二步：针对单个provincia执行查询，仅扫描对应分区
    $query = "
        SELECT  prospectos_x_bases.base, prospectos.id,prospectos.nombre,
                prospectos.telefono_fijo,prospectos.telefono_movil
        FROM  prospectos_x_bases
        JOIN  prospectos
           ON prospectos.provincia = $provincia
         AND prospectos.id = prospectos_x_bases.prospecto
        WHERE prospectos_x_bases.base = $base
    ";
    $result = mysqli_query($conn, $query);
    // 处理查询结果
}

这种方式让数据库引擎明确知道要访问的分区，彻底避免全分区扫描。

2. 优化索引辅助分区裁剪 #

如果不想拆分查询，可以给prospectos_x_bases添加复合索引(base, provincia, prospecto)。这个索引能让数据库快速过滤出指定base的行，同时提前拿到provincia值，帮助优化器更好地判断需要扫描的分区。不过这种方式不能100%保证触发分区裁剪，最终取决于优化器的执行计划。

3. 批量分区查询减少请求次数 #

如果当前base对应的provincia数量不多，可以先收集所有provincia值，用IN子句批量查询：

$base = $row['id'];
// 收集当前base对应的所有provincia
$provincias = [];
$provincia_result = mysqli_query($conn, "SELECT DISTINCT provincia FROM prospectos_x_bases WHERE base = $base");
while($prov_row = mysqli_fetch_array($provincia_result)) {
    $provincias[] = $prov_row['provincia'];
}
$prov_str = implode(',', $provincias);

// 批量查询对应分区的数据
$query = "
    SELECT  prospectos_x_bases.base, prospectos.id,prospectos.nombre,
            prospectos.telefono_fijo,prospectos.telefono_movil
    FROM  prospectos_x_bases
    JOIN  prospectos
       ON prospectos.provincia IN ($prov_str)
     AND prospectos.id = prospectos_x_bases.prospecto
    WHERE prospectos_x_bases.base = $base
";
$result = mysqli_query($conn, $query);

这种方式能减少查询次数，同时引擎会对每个provincia值进行分区裁剪，只扫描对应的分区。

内容的提问来源于Stack Exchange，提问作者Federico Coco