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

表结构与数据

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.csdn.net/u012978884/article/details/52416997?utm_source=blogxgwz0
https://draveness.me/mysql-innodb

  • 8
    点赞
  • 21
    收藏
    觉得还不错? 一键收藏
  • 5
    评论
如果你的MySQL InnoDB表中,where字段索引,但是在使用order by聚集索引进行排序时仍然很慢,可能是由于以下几个原因导致的: 1. 索引选择不当:虽然你的where字段索引,但是在order by聚集索引排序时,可能没有使用到合适的索引。使用EXPLAIN语句来查看查询计划,确认是否使用了正确的索引。你可以尝试创建一个包含where字段和排序字段的复合索引,以提高查询效率。 2. 数据量过大:如果表中的数据量非常大,即使有合适的索引,仍然可能导致排序操作变慢。考虑通过分页查询或者限制结果集的大小来减少排序的数据量。 3. 硬件性能问题:如果服务器硬件配置较低,例如内存不足或者磁盘IO性能不佳,也可能导致排序操作变慢。确保服务器具备足够的资源以支持高效的排序操作。 4. 查询优化:请检查SQL语句是否存在其他影响性能的因素,例如过多的关联表、不必要的数据类型转换等。优化查询语句可以提高整体性能。 5. 调整配置参数:针对InnoDB引擎,你还可以尝试调整一些相关的配置参数来优化排序操作。例如,增sort_buffer_size参数的值,提高排序缓冲区的大小。 总之,解决MySQL InnoDB的where字段索引,但是order by聚集索引排序很慢的问题,需要综合考虑索引选择、数据量、硬件性能、查询优化和配置参数等方面的因素。通过合理的索引设计、优化查询语句和调整配置参数,可以提高排序操作的性能。
评论 5
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值