MariaDB 10.7查询因索引选择过慢，如何指定最优索引？

数据老陈

2026-8-22

问题描述

有一条查询语句在MariaDB 10.3中执行速度很快，但迁移到MariaDB 10.7后，执行耗时长达6分钟。

查询语句

SELECT
    products.code AS productCode,
    products.`name` AS productDescription,
    products.unit_of_measure,
    product_types.fg_or_rp AS productType,
    product_batches.is_blocked,
    CASE WHEN product_batches.expiry_date < NOW() AND products.is_batch_tracked = 1 THEN
        1
    ELSE
        0
    END AS is_expired,
    sum(pallet_items.quantity) AS quantity_soh
FROM
    products
    INNER JOIN product_batches ON product_batches.product_id = products.id
    INNER JOIN pallet_items ON pallet_items.product_batch_id = product_batches.id
    INNER JOIN pallets ON pallets.id = pallet_items.pallet_id
    INNER JOIN storage_locations ON storage_locations.id = pallets.current_location_id
    INNER JOIN product_types ON products.product_type_id = product_types.id
    INNER JOIN stock_locations ON stock_locations.id = storage_locations.stock_location_id
WHERE
    stock_locations.stock_group_id in(
        SELECT
            id FROM stock_groups
        WHERE
            stock_groups.include_in_stock_on_hand = 1)
GROUP BY
    products.code, products.`name`, unit_of_measure, product_batches.is_blocked,
    CASE WHEN product_batches.expiry_date < NOW() AND products.is_batch_tracked = 1 THEN
        1
    ELSE
        0
    END, product_types.fg_or_rp, product_batches.is_blocked
ORDER BY
    products.code

索引选择差异

通过EXPLAIN分析发现，两个版本在pallet_items表的索引选择上存在明显差异：

MariaDB 10.3（执行快速）使用索引：pallet_items_pallet_id_foreign
MariaDB 10.7（执行缓慢）使用索引：pallet_items_product_batch_id_foreign

相关表结构

pallet_types

CREATE TABLE `pallet_types` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

storage_locations

CREATE TABLE `storage_locations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `entry_x` int(11) DEFAULT NULL,
  `entry_y` int(11) DEFAULT NULL,
  `entry_z` int(11) DEFAULT NULL,
  `exit_x` int(11) DEFAULT NULL,
  `exit_y` int(11) DEFAULT NULL,
  `exit_z` int(11) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `storage_function_id` bigint(20) unsigned DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `max_quantity` int(10) unsigned DEFAULT NULL,
  `is_multi_product` tinyint(1) DEFAULT 0,
  `stock_location_id` bigint(20) unsigned DEFAULT 2,
  `client_location_code` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `storage_locations_storage_function_id_foreign` (`storage_function_id`),
  KEY `storage_locations_stock_location_id_foreign` (`stock_location_id`),
  CONSTRAINT `storage_locations_stock_location_id_foreign` FOREIGN KEY (`stock_location_id`) REFERENCES `stock_locations` (`id`),
  CONSTRAINT `storage_locations_storage_function_id_foreign` FOREIGN KEY (`storage_function_id`) REFERENCES `storage_functions` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=770 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

stock_locations

CREATE TABLE `stock_locations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `stock_group_id` bigint(20) unsigned NOT NULL,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `stock_locations_stock_group_id_foreign` (`stock_group_id`),
  CONSTRAINT `stock_locations_stock_group_id_foreign` FOREIGN KEY (`stock_group_id`) REFERENCES `stock_groups` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

stock_groups

CREATE TABLE `stock_groups` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `include_in_stock_on_hand` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

pallet_statuses

CREATE TABLE `pallet_statuses` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

pallet_ledger_entries

CREATE TABLE `pallet_ledger_entries` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `pallet_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned NOT NULL,
  `product_batch_id` bigint(20) unsigned DEFAULT NULL,
  `quantity` decimal(14,4) NOT NULL,
  `event_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `reference` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `soh_pallet` decimal(14,4) NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pallet_ledger_entries_product_id_foreign` (`product_id`),
  KEY `pallet_ledger_entries_product_batch_id_foreign` (`product_batch_id`),
  KEY `pallet_ledger_entries_pallet_id_foreign` (`pallet_id`),
  KEY `pallet_ledger_entries_user_id_foreign` (`user_id`),
  CONSTRAINT `pallet_ledger_entries_pallet_id_foreign` FOREIGN KEY (`pallet_id`) REFERENCES `pallets` (`id`),
  CONSTRAINT `pallet_ledger_entries_product_batch_id_foreign` FOREIGN KEY (`product_batch_id`) REFERENCES `product_batches` (`id`),
  CONSTRAINT `pallet_ledger_entries_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  CONSTRAINT `pallet_ledger_entries_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=231839 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

products

CREATE TABLE `products` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_type_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_batch_tracked` tinyint(1) NOT NULL DEFAULT 1,
  `metric_of_weight` decimal(10,3) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `unit_of_measure` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `cost` decimal(20,10) DEFAULT NULL,
  `unit_ean` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `shrink_ean` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `case_ean` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `shipping_weight` decimal(10,2) DEFAULT NULL,
  `shipping_weight_unit` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `net_weight` decimal(10,2) DEFAULT NULL,
  `net_weight_unit` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `size` decimal(10,2) DEFAULT NULL,
  `size_unit` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `production_line` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `shelf_life_days` int(11) DEFAULT NULL,
  `exclude_from_receiving_stack` tinyint(1) NOT NULL DEFAULT 0,
  `ignore_batch_no_check` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `products_product_type_id_foreign` (`product_type_id`),
  CONSTRAINT `products_product_type_id_foreign` FOREIGN KEY (`product_type_id`) REFERENCES `product_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=738 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

product_types

CREATE TABLE `product_types` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `fg_or_rp` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

解决方案

要让MariaDB 10.7选择更优的索引，可尝试以下几种方法：

1. 强制指定索引

直接在查询中通过FORCE INDEX强制数据库使用pallet_items_pallet_id_foreign索引，这是最直接的临时解决方案：

SELECT
    products.code AS productCode,
    products.`name` AS productDescription,
    products.unit_of_measure,
    product_types.fg_or_rp AS productType,
    product_batches.is_blocked,
    CASE WHEN product_batches.expiry_date < NOW() AND products.is_batch_tracked = 1 THEN
        1
    ELSE
        0
    END AS is_expired,
    sum(pallet_items.quantity) AS quantity_soh
FROM
    products
    INNER JOIN product_batches ON product_batches.product_id = products.id
    INNER JOIN pallet_items FORCE INDEX (pallet_items_pallet_id_foreign) ON pallet_items.product_batch_id = product_batches.id
    INNER JOIN pallets ON pallets.id = pallet_items.pallet_id
    INNER JOIN storage_locations ON storage_locations.id = pallets.current_location_id
    INNER JOIN product_types ON products.product_type_id = product_types.id
    INNER JOIN stock_locations ON stock_locations.id = storage_locations.stock_location_id
WHERE
    stock_locations.stock_group_id in(
        SELECT
            id FROM stock_groups
        WHERE
            stock_groups.include_in_stock_on_hand = 1)
GROUP BY
    products.code, products.`name`, unit_of_measure, product_batches.is_blocked,
    CASE WHEN product_batches.expiry_date < NOW() AND products.is_batch_tracked = 1 THEN
        1
    ELSE
        0
    END, product_types.fg_or_rp, product_batches.is_blocked
ORDER BY
    products.code

2. 更新表统计信息

MariaDB 10.7的查询优化器依赖最新的表统计信息来评估索引成本，升级后统计信息可能未同步导致选择错误索引。执行以下命令更新相关表的统计信息：

ANALYZE TABLE pallet_items, pallets, storage_locations, stock_locations, stock_groups, products, product_batches, product_types;

3. 创建复合索引

针对查询逻辑创建更高效的复合索引，减少回表操作。例如在pallet_items表上创建包含关联字段和聚合字段的索引：

CREATE INDEX idx_pallet_items_pallet_batch_qty ON pallet_items (pallet_id, product_batch_id, quantity);

该索引覆盖了关联所需的pallet_id、product_batch_id以及聚合用的quantity，能直接从索引中获取数据，提升查询效率。

4. 重构查询逻辑

将子查询改为JOIN操作，帮助优化器更好地理解数据关联关系，从而选择更优的执行计划：

SELECT
    products.code AS productCode,
    products.`name` AS productDescription,
    products.unit_of_measure,
    product_types.fg_or_rp AS productType,
    product_batches.is_blocked,
    CASE WHEN product_batches.expiry_date < NOW() AND products.is_batch_tracked = 1 THEN
        1
    ELSE
        0
    END AS is_expired,
    sum(pallet_items.quantity) AS quantity_soh
FROM
    products
    INNER JOIN product_batches ON product_batches.product_id = products.id
    INNER JOIN pallet_items ON pallet_items.product_batch_id = product_batches.id
    INNER JOIN pallets ON pallets.id = pallet_items.pallet_id
    INNER JOIN storage_locations ON storage_locations.id = pallets.current_location_id
    INNER JOIN stock_locations ON stock_locations.id = storage_locations.stock_location_id
    INNER JOIN stock_groups ON stock_groups.id = stock_locations.stock_group_id AND stock_groups.include_in_stock_on_hand = 1
    INNER JOIN product_types ON products.product_type_id = product_types.id
GROUP BY
    products.code, products.`name`, unit_of_measure, product_batches.is_blocked,
    CASE WHEN product_batches.expiry_date < NOW() AND products.is_batch_tracked = 1 THEN
        1
    ELSE
        0
    END, product_types.fg_or_rp, product_batches.is_blocked
ORDER BY
    products.code

5. 调整优化器参数

临时调整优化器参数，强制其选择更优的执行路径。例如关闭基于成本的MRR（Multi-Range Read）优化：

SET SESSION optimizer_switch='mrr=off,mrr_cost_based=off';

测试有效后，可考虑在my.cnf或my.ini中持久化该设置，但需注意全局调整可能影响其他查询。

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

MariaDB 10.7查询因索引选择过慢，如何指定最优索引？

查询语句 #

索引选择差异 #

相关表结构 #

pallet_types #

storage_locations #

stock_locations #

stock_groups #

pallet_statuses #

pallet_ledger_entries #

products #

product_types #

1. 强制指定索引 #

2. 更新表统计信息 #

3. 创建复合索引 #

4. 重构查询逻辑 #

5. 调整优化器参数 #