[InnoDB]性别字段为什么不适合加索引

本文深入探讨InnoDB存储引擎中聚集索引与普通索引的工作原理,通过具体实例对比分析,揭示在不同场景下索引对查询性能的影响。特别关注在全表扫描时,普通索引与聚集索引之间的效率差异。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

表结构与数据

id为主键,id为奇数sex=1,id为偶数sex=0
sex=0,50000条数据;sex=1,50000条数据
表结构在这里插入图片描述

CREATE TABLE `people` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `sex` tinyint(1) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_initData`()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i<=100000 DO
        INSERT INTO people(name, sex) VALUES(CONCAT('姓名',i),0);
        SET i = i+1;
    END WHILE;
END

CALL proc_initData();
UPDATE people SET sex = 1 WHERE MOD(id,2) = 1;

添加的索引类型在这里插入图片描述

测试结果:

SELECT * FROM people WHERE sex = 0;
SELECT * FROM people WHERE sex = 1;
无sex索引有sex索引
sex=0在这里插入图片描述在这里插入图片描述
sex=1在这里插入图片描述在这里插入图片描述

可以看到相同的sql,加索引之后比不加索引慢许多。

原因

在InnoDB中每一个表都会有聚集索引,如果表定义了主键,则主键就是聚集索引。一个表只有一个聚集索引,其余为普通索引。
索引的结构是B+树,非叶子节点存储key,叶子节点存储value。

  1. 聚集索引,叶子节点存储行记录,InnoDB索引和记录是存储在一起的。
  2. 普通索引,叶子节点存储了主键的值。

以上表的索引结构示例如下(PS:索引结构仅供参考)
聚集索引
在这里插入图片描述
sex列普通索引
在这里插入图片描述

在使用普通索引查询时,会先加载普通索引,通过普通索引查询到实际行的主键。再使用主键通过聚集索引查询相应的行。以此循环查询所有的行。
若直接全量搜索聚集索引,则不需要在普通索引和聚集索引中来回切换。
相比两种操作的总开销可能扫描全表效率更高。

感谢:
https://mp.weixin.qq.com/s/tmkRAmc1M_Y23ynduBeP3Q
https://blog.jcole.us/innodb/
https://blog.youkuaiyun.com/u012978884/article/details/52416997?utm_source=blogxgwz0
https://draveness.me/mysql-innodb

<think>我们正在讨论为什么在区分度低的字段(如性别)上建立索引效率高。区分度是指字段同值的数量与总记录数的比例。例如,性别字段通常只有“男”和“女”两个值,因此区分度很低。索引的核心目的是快速定位数据。在数据库中,索引通常使用B+树等数据结构,它们通过减少需要扫描的数据量来提高查询速度。然而,当字段的区分度很低时,索引的效果会大打折扣,原因如下:1.**索引的选择性(Selectivity)低**:选择性是指索引同值的数量与总记录数的比例。选择性越高,索引的效果越好。对于性别字段,假设有100万条记录,只有两个值(男和女),那么每个值对应大约50万条记录。当我们使用索引查询时(如`WHERE gender='男'`),数据库会定位到“男”这个索引节点,但随后需要扫描该节点下的50万条记录。这相当于几乎扫描了半个表,索引并没有显著减少扫描的数据量。2.**索引维护的开销**:虽然索引可以速查询,但维护索引(如插入、更新、删除操作)需要额外的开销。每次数据修改时,数据库都需要更新索引。对于区分度低的字段建立索引后,由于每个索引键对应大量数据,当数据修改时,索引的更新可能会涉及更多的页操作(如页分裂和页合并),从而增I/O负担。3.**查询优化器可能忽略索引**:当数据库查询优化器发现使用某个索引需要扫描大量行时(例如超过总行数的20%),它可能会选择全表扫描而是使用索引。因此,即使我们在性别字段建立索引,优化器也可能使用它,导致索引成为“无效索引”。4. **存储空间浪费**:索引需要占用额外的存储空间。一个区分度低的字段上的索引,其键值重复率很高,但索引结构仍然需要为每个索引条目存储键值和指向数据行的指针。如果索引键值很长(如一个长字符串),这种浪费会更明显。5. **回表操作的成本**:如果索引是非聚集索引(如InnoDB的二级索引),那么通过索引查找到键值后,还需要根据指针(通常是主键)去聚集索引中查找完整的数据行(称为回表)。当索引键对应的数据行很多时,回表操作需要大量的随机I/O,性能可能比全表扫描更差。数学上,索引的选择性可以用以下公式表示: $$\text{选择性}= \frac{\text{同值的数量}}{\text{总记录数}}$$例如,性别字段的选择性为$2/N$,其中$N$为总记录数。当$N$很大时,选择性趋近于0,索引的效率也就越低。因此,对于区分度低的字段建立索引往往得偿失。在实际应用中,我们通常建议只为区分度高的字段(如用户ID、手机号等)建立索引。当然,也有例外情况,比如当低区分度字段与其他高区分度字段组成复合索引时,可能会有效(如查询条件同时包含性别和年龄,可以建立(性别,年龄)的复合索引)[^3]。引用[^3]中提到:“区分度高的字段适合索引”并非绝对真理。例如,在数据仓库的星型模型中,维度表的外键(通常是低区分度的)上建立索引可能是有益的,因为这类查询通常涉及大表连接和聚合。但总的来说,在OLTP系统中,低区分度字段单独建索引通常效率高。总结:在区分度低的字段(如性别)上建立索引效率高的主要原因是索引的选择性低,导致数据库扫描大量数据行,并且索引维护成本高,优化器可能选择使用索引。 §§相关问题§§1.如何计算一个字段的区分度?2.在什么情况下,低区分度字段索引可能有效?3.复合索引中区分度低的字段应该放在前面还是后面?</think>### 为什么区分度低的字段(如性别适合建立索引? #### 1. **索引效率的核心原理** 索引的核心价值在于**快速缩小数据搜索范围**。理想情况下,索引能通过键值直接定位少量数据页(B+树叶子节点)。效率公式可表示为: $$ \text{查询效率} \propto \frac{1}{\text{索引键值重复率}} $$ 当字段区分度低时(如性别只有“男/女”两个值),索引失去筛选能力。 #### 2. **低区分度索引的缺陷分析** - **扫描大量数据行** 假设表有 100 万行数据,性别分布均匀(50万男/50万女)。执行 `WHERE gender='男'` 时: - **无索引**:全表扫描 100 万行 - **有索引**:索引先定位到“男”键值,仍需扫描 **50万行**(回表查数据页) 此时索引 I/O 开销可能 **高于全表扫描**(尤其机械硬盘)[^2][^3]。 - **优化器弃用索引** 当数据库优化器预判索引需扫描 >20% 数据时(经验阈值),会直接选择**全表扫描**。例如: ```sql EXPLAIN SELECT * FROM users WHERE gender='男'; -- 结果可能显示 "type: ALL"(全表扫描) ``` - **存储与维护成本失衡** - **空间浪费**:索引需存储所有键值+指针。性别索引可能占用数MB空间,但仅含 2 个有效键值。 - **维护开销**:每次数据更新需同步更新索引,但查询收益极低[^1]。 #### 3. **与高区分度字段的对比** | 场景 | 字段示例 | 索引效果 | |---------------------|--------------|-----------------------------| | 高区分度 (90%+) | 用户ID/手机号 | 直接定位 1~n 行,I/O 极少 | | 低区分度 (<10%) | 性别/状态码 | 定位后仍需扫描海量行,I/O 高 | > 💡 **极端案例**:在 10 亿行表中查 `WHERE status=0`(status 仅 0/1 两个值),索引查询耗时可能比全表扫描**慢 2 倍**以上。 #### 4. **例外情况** 低区分度字段索引在特定场景可能有效: - **复合索引中作为前缀**:如 `(gender, age)`,通过 `WHERE gender='男' AND age>30` 缩小范围 - **覆盖索引**:查询仅需索引字段时(如 `SELECT gender FROM ...`),避免回表 - **极高频查询**:字段值分布极度倾斜(如 99% 为“男”),查“女”时索引有效[^3]。 #### 总结 区分度低的字段索引本质问题是: **索引未能显著减少扫描的数据量**,反而增存储和维护成本。优化建议: 1. 优先为高区分度字段索引 2. 低区分度字段需与其他字段组成复合索引 3. 通过 `SELECT COUNT(DISTINCT column)/COUNT(*)` 计算区分度(低于 0.1 慎建索引)[^2]。 --- ### 相关问题 1. **如何计算字段区分度?数学公式是什么?** 2. **复合索引中低区分度字段应放在什么位置?** 3. **什么情况下低区分度字段索引反而能提升性能?**
评论 5
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值