一个诡异的MySQL索引选择问题-CSDN博客

本文链接：https://blog.csdn.net/xiaocszn/article/details/86289651

本文深入探讨MySQL中tb_habit_log_total表的索引优化问题，解析为何在不同数据量下，MySQL会选择不同的索引路径，并揭示如何通过精确指定ORDER BY字段使MySQL选择最优索引，从而大幅提升查询效率。

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

表结构及数据量

CREATE TABLE `tb_habit_log_total` (
    `totalID` INT(11) NOT NULL AUTO_INCREMENT,
    `openId` VARCHAR(100) COLLATE UTF8MB4_UNICODE_CI NOT NULL DEFAULT '',
    `habitID` INT(11) NOT NULL,
    `joinCreateDate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `totalSuccessDay` INT(11) DEFAULT '0',
    `totalLastDate` DATE DEFAULT NULL,
    `joinState` TINYINT(1) DEFAULT '1',
    `totalAdminRole` TINYINT(1) DEFAULT '0',
    PRIMARY KEY (`totalID`),
    UNIQUE KEY `uk_hid_oid` (`habitID` , `openId`) USING BTREE,
    KEY `openId` (`openId`),
    KEY `idx_totalSuccessDay` (`totalSuccessDay`),
    KEY `idx_totalLastDate` (`totalLastDate`),
    KEY `idx_totalAdminRole` (`totalAdminRole`),
    KEY `idx_joinCreateDate` (`joinCreateDate`),
    KEY `idx_hid_js_tid` (`habitID` , `joinState` , `totalID` , `joinCreateDate`) USING BTREE
)  ENGINE=INNODB AUTO_INCREMENT=22854700 DEFAULT CHARSET=UTF8MB4;

索引情况