含可空参数的WHERE子句查询性能优化及方案咨询

数据表结构 #

branches表 #

CREATE TABLE `branches` (
  `id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `new_meeting_expire_days` tinyint unsigned DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `country` varchar(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `state_id` smallint unsigned DEFAULT NULL,
  `slug` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `branches_name_unique` (`name`),
  UNIQUE KEY `branches_slug_unique` (`slug`),
  KEY `branches_branch_id_state_id_active_index` (`country`,`state_id`,`active`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

departments表 #

CREATE TABLE `departments` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `branch_id` smallint unsigned NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `manager_id` bigint unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `departments_name_unique` (`name`),
  KEY `departments_manager_id_foreign` (`manager_id`),
  KEY `departments_branch_id_manager_id_name_index` (`branch_id`,`manager_id`,`name`),
  CONSTRAINT `departments_branch_id_foreign` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `departments_manager_id_foreign` FOREIGN KEY (`manager_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

执行的查询及计划 #

执行的SQL查询：

explain format=tree    SELECT id, name, branch_id
FROM departments
WHERE (1 IS NULL OR branch_id = 1)

查询计划输出：

-> Limit: 200 row(s)  (cost=0.8 rows=5)
-> Covering index lookup on departments using departments_branch_id_manager_id_name_index (branch_id=1)  (cost=0.8 rows=5)

疑问 #

上述WHERE子句中的“1”实际是存储过程的可空参数，想了解当前的过滤写法是否合理，是否有更优的实现方案？

当前写法的问题 #

当前(param IS NULL OR branch_id = param)的写法存在两个明显缺陷：

1. 参数非空时虽能命中索引，但参数为NULL时，条件等价于TRUE，MySQL会执行全表扫描；
2. 这种静态条件写法容易让MySQL复用执行计划，当参数在NULL和非NULL之间切换时，无法根据参数实际值选择最优执行路径，可能导致性能波动。

更优实现方案 #

方案1：动态SQL（推荐） #

在存储过程中根据参数是否为NULL，拼接不同的SQL逻辑，让MySQL为每种场景生成最优计划：

DELIMITER //
CREATE PROCEDURE get_departments(IN p_branch_id SMALLINT UNSIGNED)
BEGIN
    IF p_branch_id IS NULL THEN
        SELECT id, name, branch_id FROM departments;
    ELSE
        SELECT id, name, branch_id FROM departments WHERE branch_id = p_branch_id;
    END IF;
END //
DELIMITER ;

这种方式的优势在于：参数非空时利用索引快速定位数据，参数为空时直接扫描全表（或优化器选择更合适的方式），完全避免了条件判断带来的计划复用问题。

方案2：静态SQL条件优化（兼容场景） #

如果必须使用静态SQL，可以调整条件写法，确保参数非空时仍能命中索引：

SELECT id, name, branch_id
FROM departments
WHERE branch_id <=> COALESCE(p_branch_id, branch_id)

不过这种写法在参数为空时，COALESCE(p_branch_id, branch_id)等价于branch_id，branch_id <=> branch_id永远为真，依然会触发全表扫描，性能优化空间不如动态SQL。

方案3：强制索引（特殊场景使用） #

若想强制MySQL在参数非空时使用索引，可添加索引提示，但这种方式会限制优化器的自主选择，当数据分布变化时可能导致性能下降，仅适合特殊场景：

SELECT id, name, branch_id
FROM departments FORCE INDEX (departments_branch_id_manager_id_name_index)
WHERE (p_branch_id IS NULL OR branch_id = p_branch_id)

总结 #

优先采用动态SQL方案，它能让MySQL针对参数的不同取值生成最适配的执行计划，彻底解决静态条件写法的性能隐患。若受限于场景无法使用动态SQL，再考虑静态条件优化，但需注意全表扫描的性能影响。

内容的提问来源于stack exchange，提问作者Petro Gromovo