MySQL中表关联因字段类型转换导致索引失效的问题解决

最初误以为索引失效是DATE与DATETIME列关联导致，实际重现问题后发现，根源是表b的USER字段类型与表a的USER字段类型不匹配，触发类型转换后无法使用b_DT_USER索引。

测试环境配置 #

表结构与索引创建语句 #

# 建表与创建索引
DROP TABLE a;
DROP TABLE b;

create table a
(
    DT  DATE,
    USER INT,
    COMMENT_SENTIMENT INT,
    PRIMARY KEY (USER, DT));
CREATE INDEX a_DT_USER_IDX ON a (DT,USER);

create table b
(
    id                                int auto_increment primary key,
    DT  DATETIME(6),
    USER mediumtext,
    COMMENT_SENTIMENT INT);
CREATE INDEX b_DT_USER_IDX ON b (DT);
CREATE UNIQUE INDEX b_DT_USER ON b (USER(16), DT);

测试数据插入 #

# 插入测试数据
INSERT INTO a VALUES('2023-01-01', 5, 4);
INSERT INTO b VALUES(NULL, '2023-01-01 00:00:00', 5, 4);

报错查询及信息 #

执行以下关联查询时触发索引失效报错：

EXPLAIN
SELECT *
FROM a
JOIN b
ON a.DT = b.DT
AND a.USER = b.USER;

报错信息：

[2023-01-24 18:00:14] [HY000][1739] 由于字段'USER'的类型或排序规则转换，无法在索引'b_DT_USER'上使用ref访问

解决方法：字段类型转换 #

对表b的USER字段做显式类型转换后，查询可正常执行且索引生效：

EXPLAIN
SELECT *
FROM a
JOIN b
ON a.DT = b.DT
AND a.USER = CAST(b.USER AS DECIMAL );

重要说明 #

原认为DATE与DATETIME列关联导致索引失效的结论为错误内容，以上述排查结果为准。

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