MySQL 8.0.23 SELECT查询性能骤降问题排查与优化建议请求

问题现象 #

某SELECT查询在MySQL 5.7中执行仅需850毫秒，但升级至MySQL 8.0.23后，执行时间长达2.20秒。核心问题是MySQL 8.0未选用预期的索引，导致性能大幅下降，且添加额外筛选条件时索引选择错误的情况会进一步恶化。cart表数据量约为6亿条。

涉及查询语句 #

SELECT c.id,
c.cart_type as cartType,
c.name,
c.storefront_id as storefrontId,
c.contact_id as contactId,
c.account_number as accountNumber,
c.ship_to as shipTo,
c.name1 as shipToName1,
c.name2 as shipToName2,
c.city,
c.country,
c.status,
c.order_type as orderType,
c.currency as currency,
c.updated_at as updatedOn,
CASE WHEN EXISTS (SELECT 1
FROM contact_active_cart cac
WHERE cac.cart_id = c.id) THEN 'true' ELSE 'false' END as isActive
FROM (SELECT id
FROM cart c
WHERE c.cart_type = 'cart'
AND c.storefront_id = 1
AND c.status IN (
7, 8, 6, 2, 0
)
AND c.account_number in (
'0100000106', '0100000212', '0100000806', '0100000860', '0100001329', '0100001537', '0100010879',     '0100012479', '0100012553', '0100012579'
        )
ORDER BY created_at ASC
LIMIT 0,
200 ) as cart_inner_subselect
INNER JOIN cart c ON cart_inner_subselect.id = c.id;

表结构定义 #

CREATE TABLE `cart` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `contact_id` varchar(255) NOT NULL,
  `storefront_id` bigint NOT NULL,
  `cart_type` varchar(50) DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL,
  `ship_to` varchar(255) NOT NULL,
  `account_number` varchar(40) DEFAULT NULL,
   `name1` varchar(35) DEFAULT NULL,
  `name2` varchar(35) DEFAULT NULL, `city` varchar(35) DEFAULT NULL,
  `state` varchar(80) DEFAULT NULL,
  `country` varchar(80) DEFAULT NULL,
  `order_type` varchar(5) DEFAULT NULL,
  `currency` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk-cart` (`contact_id`,`storefront_id`,`name`,`cart_type`,`submission_datetime`),
  KEY `idx-cart-cart_type` (`cart_type`),
  KEY `idx-cart-cart_overview_1` (`cart_type`,`storefront_id`,`status`,`account_number`,`created_at`),
  KEY `idx-cart-cart_overview_contact` (`cart_type`,`storefront_id`,`status`,`account_number`,`contact_id`,`created_at`),
  KEY `idx-cart-cart_overview_shipTo` (`cart_type`,`storefront_id`,`status`,`account_number`,`ship_to`,`created_at`),
  KEY `idx-cart-cart_overview_all` (`cart_type`,`storefront_id`,`status`,`account_number`,`contact_id`,`ship_to`,`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=24331499 DEFAULT CHARSET=utf8mb3;

CREATE TABLE `contact_active_cart` (
  `contact_id` varchar(255) NOT NULL,
  `cart_id` bigint NOT NULL,
  `storefront_id` bigint NOT NULL,
  PRIMARY KEY (`contact_id`,`storefront_id`),
  KEY `idx-contact_active_cart-cart_id` (`cart_id`),
  KEY `idx-contact_active_cart-contact_id` (`contact_id`),
  KEY `idx-contact_active_cart-storefront_id` (`storefront_id`),
  CONSTRAINT `contact_active_cart_FK` FOREIGN KEY (`cart_id`) REFERENCES `cart` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

当前问题细节 #

• MySQL 5.7可正确选用idx-cart-cart_overview_1索引，但8.0.23版本未选用该索引
• 给idx-cart-cart_overview_1添加created_at字段后，查询耗时有所减少，但仍高于5.7版本的表现
• 在WHERE子句中添加contact_id或ship_to等额外筛选条件时，查询会完全忽略预期索引，转而使用idx-cart-cart_type，导致响应时间进一步增加

优化建议 #

1. 强制指定索引 #

在子查询中通过FORCE INDEX强制优化器使用目标索引，避免索引选择错误：

SELECT id
FROM cart c FORCE INDEX(`idx-cart-cart_overview_1`)
WHERE c.cart_type = 'cart'
AND c.storefront_id = 1
AND c.status IN (7, 8, 6, 2, 0)
AND c.account_number in ('0100000106', '0100000212', '0100000806', '0100000860', '0100001329', '0100001537', '0100010879', '0100012479', '0100012553', '0100012579')
ORDER BY created_at ASC
LIMIT 0, 200

2. 更新表统计信息 #

MySQL 8.0的优化器依赖准确的表统计信息，执行以下命令更新cart表的统计数据，帮助优化器做出正确的索引选择：

ANALYZE TABLE cart;

3. 重构查询语句 #

去掉嵌套子查询，改用LEFT JOIN替代EXISTS判断，简化查询逻辑，让优化器更容易生成高效执行计划：

SELECT c.id,
       c.cart_type as cartType,
       c.name,
       c.storefront_id as storefrontId,
       c.contact_id as contactId,
       c.account_number as accountNumber,
       c.ship_to as shipTo,
       c.name1 as shipToName1,
       c.name2 as shipToName2,
       c.city,
       c.country,
       c.status,
       c.order_type as orderType,
       c.currency as currency,
       c.updated_at as updatedOn,
       CASE WHEN cac.cart_id IS NOT NULL THEN 'true' ELSE 'false' END as isActive
FROM cart c
LEFT JOIN contact_active_cart cac ON cac.cart_id = c.id
WHERE c.cart_type = 'cart'
  AND c.storefront_id = 1
  AND c.status IN (7, 8, 6, 2, 0)
  AND c.account_number IN ('0100000106', '0100000212', '0100000806', '0100000860', '0100001329', '0100001537', '0100010879', '0100012479', '0100012553', '0100012579')
ORDER BY c.created_at ASC
LIMIT 0, 200;

4. 升级至更高版本 #

MySQL 8.0后续版本（如8.0.30及以上）对优化器进行了大量改进，修复了不少索引选择相关的问题，升级到新版本可能直接解决当前的索引选择错误问题。

5. 检查优化器参数 #

对比MySQL 5.7和8.0.23的optimizer_switch参数设置，调整部分可能影响索引选择的参数（如use_index_extensions、condition_fanout_filter等），调整前建议先备份数据库，确保参数修改的安全性。

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