一次“反直觉”的 MySQL 索引优化实践：低区分度字段真的不能建索引吗？

胡玉洋　

857人浏览 · 2026-01-06 08:30:00

胡玉洋　 · 2026-01-06 08:30:00 发布

在 MySQL 索引优化的常规认知中，我们通常会被反复强调一条原则：

区分度低的字段不适合建索引。

例如性别、状态位、逻辑删除标识等字段，往往只有极少的取值，索引选择性差，收益有限。

但在某些特定业务场景下，这个结论并非绝对。本文记录了一次相对“非常规”的 MySQL 索引优化实践，也正是围绕**逻辑删除字段（is_deleted）**展开。

在这里插入图片描述

一、问题背景

有一张用户工作经历表 tms_digital_work_experience，用于存储用户的历史工作信息，表结构如下：

CREATE TABLE `tms_digital_work_experience` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '记录id',
  `user_id` varchar(255) NOT NULL DEFAULT '' COMMENT '人员用户id',
  `user_name` varchar(255) DEFAULT '' COMMENT '人员用户名称',
  `company` varchar(512) DEFAULT '' COMMENT '公司名称',
  `department` varchar(512) DEFAULT '' COMMENT '所属部门',
  `job` varchar(512) DEFAULT '' COMMENT '任职岗位',
  `job_content` longtext COMMENT '工作内容',
  `start_time` datetime DEFAULT NULL COMMENT '开始时间',
  `end_time` datetime DEFAULT NULL COMMENT '截止时间',
  `description` longtext COMMENT '描述',
  `is_deleted` tinyint NOT NULL DEFAULT '0' COMMENT '是否删除(0：未删除 1：已删除)',
  `create_usercode` varchar(50) NOT NULL DEFAULT '' COMMENT '创建人',
  `create_username` varchar(50) NOT NULL DEFAULT '' COMMENT '创建人名称',
  `update_usercode` varchar(50) NOT NULL DEFAULT '' COMMENT '更新人',
  `update_username` varchar(50) NOT NULL DEFAULT '' COMMENT '更新人名称',
  `create_time` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间',
  `update_time` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_user_id` (`user_id`,`is_deleted`)
) ENGINE=InnoDB COMMENT='工作经历表';

二、数据写入与删除模式

系统中存在一个每日执行的定时任务，用于同步用户的最新工作经历：

先按 user_id 逻辑删除该用户的历史记录
再插入最新的一批工作经历

伪代码如下：

List<WorkExperience> workExperienceList = fetchWorkExperience(userId);
workExperienceMapper.deleteByUserId(userId);
workExperienceMapper.saveBatch(workExperienceList);

这里有两个关键点：

历史数据不会被物理删除，只做逻辑删除
每天都会产生大量 is_deleted = 1 的旧数据

随着时间推移，表中逐渐堆积了大量“无效但必须保留”的历史记录。

三、数据现状

当前表中数据量情况如下：

总数据量：约 500 万
有效数据（is_deleted = 0）：约 3 万
有效数据占比约为 0.6%

也就是说，这是一张**典型的“有效数据极少，历史数据极多”**的表。

四、慢 SQL 问题定位

公司设置的慢 SQL 阈值为 500ms。

某个复杂查询（包含多表 JOIN 和子查询）偶发超过阈值，通过执行计划定位，发现下面这段 SQL 是主要性能瓶颈：

SELECT user_id, COUNT(*) AS cnt
FROM tms_digital_work_experience
WHERE is_deleted = 0
GROUP BY user_id;

对应的执行计划如下：

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	tms_digital_work_experience	index	idx_user_id	idx_user_id	4994163	Using where; Using index

关键问题分析

使用了 idx_user_id (user_id, is_deleted) 索引
扫描行数接近全表（约 500 万）
is_deleted = 0 的记录比例极低（约 0.6%）

原因在于：

在索引树中，user_id 相同的情况下，
大量记录的 is_deleted = 1
is_deleted 只有 0 / 1 两个取值，区分度极低
MySQL 需要扫描大量索引记录后，才能过滤出极少量有效数据

五、解决思路分析

方案一：物理清理历史数据（最理想）

定期将 is_deleted = 1 的数据归档到历史表，再从当前表中物理删除。

优点：

表体积直接下降
所有查询都会受益

缺点：

当前开发侧无权限直接执行
强依赖 DBA，流程成本较高

方案二：为低区分度字段单独建联合索引（非常规做法）

尽管 is_deleted 区分度极低，但在当前业务场景中：

查询几乎只关心 is_deleted = 0
有效数据占比极低
使用索引可以一次性排除 99% 无效数据

因此，反而具备了建索引的价值。

创建索引：

ALTER TABLE tms_digital_work_experience
ADD INDEX idx_is_deleted_user_id (is_deleted, user_id);

添加索引后，对应的执行计划如下：

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	tms_digital_work_experience	index	idx_user_id,idx_is_deleted_user_id	idx_is_deleted_user_id	63882	Using index

这里的核心思想是：

不是字段本身区分度高不高，而是能否在当前查询模式下大幅缩小扫描范围。

方案三：结合时间维度进一步收敛数据范围（推荐）

在本例中，有效数据具有一个明显特征：

有效记录几乎都是最近新创建的。

因此，可以引入 create_time 作为第二层过滤条件。

创建复合索引：

ALTER TABLE tms_digital_work_experience
ADD INDEX idx_is_deleted_create_time_user_id (is_deleted, create_time, user_id);

查询条件调整为：

SELECT user_id, COUNT(*) AS cnt
FROM tms_digital_work_experience
WHERE is_deleted = 0
  AND create_time >= CURDATE() - INTERVAL 7 DAY
GROUP BY user_id;

这样做的好处是：