MySQL关联查询未命中索引排查及字符集修改咨询

API 探路者

2026-8-4

MySQL关联查询索引失效与字符集问题解决方案

一、关联查询后`bike_events`全表扫描的原因及解决办法

原因分析

从执行计划可见，bike_events（别名be）的查询类型为ALL（全表扫描），虽possible_keys列出多个候选索引，但实际未使用任何索引，核心诱因及辅助原因如下：

字符集不一致导致隐式转换：两张表的bike_name字段字符集不匹配（be为latin1，bi为utf8mb3），关联时MySQL会自动将latin1编码的be.bike_name转换为utf8mb3以兼容bi.bike_name，转换后的字段无法触发原有索引，直接导致索引失效。
关联范围条件影响优化器判断：查询中be.event_time >= bi.is_active_change_dt是跨表的范围过滤条件，优化器可能评估后认为该条件过滤后数据量仍较大，全表扫描的成本低于索引扫描。
统计信息过时：若bike_events表的统计信息未及时更新，MySQL优化器无法准确计算索引扫描的成本，可能错误选择全表扫描。

消除全表扫描的方案

优先统一字符集：解决字符集不一致问题（详见第二部分），这是索引失效的核心根源，统一后be.bike_name的索引可正常用于关联查询。
创建针对性联合索引：针对查询的过滤、关联、分组逻辑，创建覆盖式联合索引，让优化器无需回表即可完成所有计算：
```
CREATE INDEX bike_event_opt_idx ON bike_events (event_value, event_type, bike_name, event_time, event_reported_by);
```
该索引包含WHERE子句的过滤字段（event_value、event_type）、关联字段（bike_name）、范围字段（event_time）以及分组/返回字段（event_reported_by），可直接支撑过滤、关联、分组、聚合全流程。
更新表统计信息：执行以下命令让MySQL重新收集表的统计数据，帮助优化器做出正确决策：
```
ANALYZE TABLE bike_events;
```

临时强制使用索引：若上述方案暂时无法落地，可在查询中强制指定索引（仅作为临时过渡方案）：

select 
  distinct be.bike_name, 
  min(event_time) event_time, 
  event_type, 
  event_reported_by 
from 
  bike_info bi, 
  bike_events be FORCE INDEX (bike_event_idx3)
where 
  be.bike_name = bi.bike_name 
  and be.event_time >= bi.is_active_change_dt 
  and event_type in (
    'BIKE_FAULT', 'BIKE_LOCK_FAULT', 
    'BIKE_RELOCATING', 'BIKE_DAMAGED', 
    'BIKE_UNAVAILABLE', 'BIKE_MAINTENANCE'
  ) 
  and event_value = 1 
group by 
  be.bike_name, 
  event_type, 
  event_reported_by;

二、字符集差异对索引的影响及修改方法

字符集差异的影响

两张表字符集不一致确实会导致索引失效：MySQL执行关联条件be.bike_name = bi.bike_name时，会遵循“字符集向上兼容”规则，将latin1编码的be.bike_name转换为utf8mb3编码，转换后的字段无法使用原索引，直接导致bike_events只能通过全表扫描匹配关联数据。

修改字符集的方法

表层面永久修改（推荐）

将bike_events表的字符集统一为utf8mb3，同时同步所有字符类型字段的编码：

备份数据：操作前务必备份表数据，避免意外丢失：

CREATE TABLE bike_events_backup LIKE bike_events;
INSERT INTO bike_events_backup SELECT * FROM bike_events;

修改表及字段字符集：
```
ALTER TABLE bike_events CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci;
```
该命令会自动将表的默认字符集、所有字符类型（varchar、text等）字段的字符集统一为utf8mb3。

查询层面临时兼容（不推荐）

若暂时无法修改表结构，可在查询中显式将bi.bike_name转换为latin1，避免be.bike_name的隐式转换：

select 
  distinct be.bike_name, 
  min(event_time) event_time, 
  event_type, 
  event_reported_by 
from 
  bike_info bi, 
  bike_events be 
where 
  be.bike_name = CONVERT(bi.bike_name USING latin1) 
  and be.event_time >= bi.is_active_change_dt 
  and event_type in (
    'BIKE_FAULT', 'BIKE_LOCK_FAULT', 
    'BIKE_RELOCATING', 'BIKE_DAMAGED', 
    'BIKE_UNAVAILABLE', 'BIKE_MAINTENANCE'
  ) 
  and event_value = 1 
group by 
  be.bike_name, 
  event_type, 
  event_reported_by;

注意：该方法可能导致bi.bike_name中的特殊字符转换为乱码，仅作为临时过渡方案，最终仍需统一字符集。

附：相关查询语句、执行计划及表结构

查询语句

select 
  distinct be.bike_name, 
  min(event_time) event_time, 
  event_type, 
  event_reported_by 
from 
  bike_info bi, 
  bike_events be 
where 
  be.bike_name = bi.bike_name 
  and be.event_time >= bi.is_active_change_dt 
  and event_type in (
    'BIKE_FAULT', 'BIKE_LOCK_FAULT', 
    'BIKE_RELOCATING', 'BIKE_DAMAGED', 
    'BIKE_UNAVAILABLE', 'BIKE_MAINTENANCE'
  ) 
  and event_value = 1 
group by 
  be.bike_name, 
  event_type, 
  event_reported_by;

执行计划

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: be
   partitions: NULL
         type: ALL
possible_keys: bike_event_idx2,bike_event_idx1,bike_event_idx3,bike_event_idx4
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 79495612
     filtered: 8.28
        Extra: Using where; Using temporary
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: bi
   partitions: NULL
         type: eq_ref
possible_keys: bike_name_UNIQUE,bike_info_idx6
          key: bike_name_UNIQUE
      key_len: 38
          ref: yulu_1.be.bike_name
         rows: 1
     filtered: 33.33
        Extra: Using index condition; Using where

表结构

`bike_events`表

CREATE TABLE `bike_events` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `bike_name` varchar(12) NOT NULL DEFAULT '0',
  `event_type_id` int NOT NULL,
  `event_type` varchar(45) NOT NULL,
  `event_value` tinyint(1) DEFAULT '0',
  `event_comments` varchar(45) DEFAULT NULL,
  `event_time` int NOT NULL,
  `event_reported_by` int DEFAULT NULL,
  `source_id` tinyint(1) DEFAULT NULL,
  `source_cd` varchar(45) DEFAULT NULL,
  `whs_id` int DEFAULT NULL,
  `latitude` double(20,18) DEFAULT NULL,
  `longitude` double(20,18) DEFAULT NULL,
  `created_by` bigint unsigned NOT NULL DEFAULT '0',
  `created_dt` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `bike_event_idx2` (`event_type`),
  KEY `bike_event_idx1` (`bike_name`,`event_type`),
  KEY `bike_event_idx3` (`bike_name`,`event_type`,`event_time`),
  KEY `bike_event_idx4` (`bike_name`),
  KEY `bike_event_idx5` (`event_type_id`,`event_time`),
  KEY `idx_created_dt` (`created_dt`)
) ENGINE=InnoDB AUTO_INCREMENT=79702850 DEFAULT CHARSET=latin1

`bike_info`表

CREATE TABLE `bike_info` (
  `bike_id` bigint unsigned NOT NULL,
  `bike_name` varchar(12) NOT NULL DEFAULT '0',
  `imei` varchar(20) DEFAULT NULL,
  `loc_country_cd` varchar(45) DEFAULT NULL,
  `fleet_city_id` int NOT NULL DEFAULT '0',
  `fleet_id` smallint unsigned NOT NULL DEFAULT '1',
  `loc_city_cd` varchar(45) DEFAULT NULL,
  `intraCampus_Flag` varchar(1) DEFAULT 'N',
  `campus_name` varchar(45) DEFAULT NULL,
  `notes` varchar(50) DEFAULT NULL,
  `manu_date` int DEFAULT NULL,
  `invoice_number` int DEFAULT NULL,
  `invoice_date` int DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '0',
  `is_active_change_dt` int DEFAULT NULL,
  `is_active_change_by` int DEFAULT NULL,
  `is_assembled` int DEFAULT '0',
  `assembled_dt` int DEFAULT NULL,
  `assembled_by` int DEFAULT NULL,
  `ready_to_deploy` int DEFAULT '0',
  `ready_to_deploy_dt` int DEFAULT NULL,
  `deployed_status` int NOT NULL,
  `deployed_date` int DEFAULT NULL,
  `deployed_dt_YYYYMMDD` int DEFAULT NULL,
  `approval_dt` int DEFAULT NULL,
  `approved_by` int DEFAULT NULL,
  `retired_dt` int unsigned DEFAULT NULL,
  `retired_by` bigint unsigned DEFAULT '0',
  `retired_reason` varchar(100) DEFAULT NULL,
  `corporate_flag` int DEFAULT '0',
  `stolen_flag` int DEFAULT '0',
  `flag_sec_battery` int NOT NULL DEFAULT '0',
  `sec_battery_last_flagged_dt` int NOT NULL DEFAULT '0',
  `sec_battery_report_count` int NOT NULL DEFAULT '0',
  `firmware_version` varchar(20) DEFAULT NULL,
  `firmware_updated_dt` int unsigned DEFAULT NULL,
  `warehouse_tagged` int DEFAULT '0',
  `created_by` bigint unsigned DEFAULT NULL,
  `created_dt` int DEFAULT NULL,
  `updated_by` bigint unsigned DEFAULT NULL,
  `updated_dt` int DEFAULT NULL,
  PRIMARY KEY (`bike_id`),
  UNIQUE KEY `bike_name_UNIQUE` (`bike_name`),
  UNIQUE KEY `bike_info_ux2` (`imei`),
  KEY `bike_info_idx1` (`deployed_status`,`loc_city_cd`,`bike_name`),
  KEY `bike_info_idx2` (`loc_city_cd`),
  KEY `bike_info_idx3` (`loc_city_cd`,`deployed_status`,`campus_name`,`bike_name`),
  KEY `bike_info_idx5` (`fleet_id`),
  KEY `bike_info_idx6` (`bike_name`,`fleet_city_id`,`corporate_flag`),
  KEY `bike_info_idx7` (`updated_dt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

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

MySQL关联查询未命中索引排查及字符集修改咨询

一、关联查询后bike_events全表扫描的原因及解决办法 #

原因分析 #

消除全表扫描的方案 #

二、字符集差异对索引的影响及修改方法 #

字符集差异的影响 #

修改字符集的方法 #