优化MySQL/MariaDB中200万行数据的SELECT JOIN查询

现有表结构 #

hub表（2061430行） #

-- dev_db_5843.hub definition
CREATE TABLE `hub` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `serial_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `device_type` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `serial_id` (`serial_id`),
  UNIQUE KEY `device_id` (`device_id`),
  KEY `ix_hub_test` (`serial_id`,`device_type`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=15874707 DEFAULT CHARSET=utf8;

cct_meta表（2023086行） #

-- dev_db_5843.cct_meta definition
CREATE TABLE `cct_meta` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hub_id` int(11) DEFAULT NULL,
  `inn` varchar(14) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hub_id` (`hub_id`),
  KEY `ix_cct_meta_inn` (`inn`),
  CONSTRAINT `cct_meta_ibfk_1` FOREIGN KEY (`hub_id`) REFERENCES `hub` (`id`) ON DELETE CASCADE,
) ENGINE=InnoDB AUTO_INCREMENT=2747768 DEFAULT CHARSET=utf8;

当前查询及问题 #

执行以下JOIN查询耗时约20秒：

SELECT
    hub.serial_id AS hub_serial_id,
    cct_meta.inn AS cct_meta_inn
FROM cct_meta 
JOIN hub ON cct_meta.hub_id = hub.id 
WHERE hub.serial_id IS NOT NULL AND hub.device_type = 1

已做的分析操作 #

1. 统计device_type分布：

SELECT device_type FROM hub GROUP BY device_type;

查询结果显示device_type存在多组取值（原配图展示分组结果）

2. 统计serial_id重复情况：

SELECT serial_id, COUNT(*)  FROM hub GROUP BY serial_id;
-- 返回2,057,296行，说明绝大多数serial_id为唯一值

（原配图展示serial_id分组计数结果）

3. EXPLAIN分析查询：

EXPLAIN
SELECT
    hub.serial_id AS hub_serial_id,
    cct_meta.inn AS cct_meta_inn
FROM cct_meta 
JOIN hub ON cct_meta.hub_id = hub.id 
WHERE hub.serial_id IS NOT NULL AND hub.device_type = 1;

（原配图展示EXPLAIN执行计划输出）

尝试过多种索引组合但未达到预期优化效果，认为200万级数据查询不应耗时20秒，寻求加速思路。

优化思路 #

1. 调整查询驱动表顺序 #

当前查询以cct_meta为驱动表，需全表扫描后关联hub做过滤。建议改为以hub为驱动表，先过滤出符合条件的hub记录，再关联cct_meta，减少关联数据量：

SELECT
    hub.serial_id AS hub_serial_id,
    cct_meta.inn AS cct_meta_inn
FROM hub
JOIN cct_meta ON hub.id = cct_meta.hub_id
WHERE hub.serial_id IS NOT NULL AND hub.device_type = 1

2. 创建针对性覆盖索引 #

针对hub表的过滤、查询及关联字段，创建覆盖索引，避免回表查询：

CREATE INDEX idx_hub_devtype_serial ON hub(device_type, serial_id, id);

该索引包含过滤条件device_type、查询字段serial_id以及关联所需的id，可直接通过索引完成过滤和数据获取，跳过主表访问。

3. 修复cct_meta索引碎片 #

cct_meta表的hub_id唯一索引虽存在，但如果有索引碎片会影响关联效率，可定期执行索引重建：

ALTER TABLE cct_meta ENGINE=InnoDB;

4. 优化数据库配置参数 #

• 调整innodb_buffer_pool_size至服务器内存的50%-70%，确保大部分数据缓存到内存，减少磁盘IO；
• 检查join_buffer_size参数，若关联过程存在临时缓存需求，可适当调高（避免设置过大引发内存竞争）。

5. 简化过滤逻辑 #

从统计结果看，serial_id分组数接近总数据量，说明非空占比极高，若serial_id IS NOT NULL过滤掉的数据极少，可直接移除该条件，减少过滤开销。

内容的提问来源于stack exchange，提问作者Lev Kakalashvili