MySQL索引未按预期生效及tinyint查询写法差异问题

涉及表结构 #

create table post
(
    id         int auto_increment
        primary key,
    comment    int                  null,
    is_used    tinyint(1) default 1 not null,
    is_deleted tinyint(1) default 0 not null
);

create table post_log
(
    id         int auto_increment
        primary key,
    post_id    int      not null,
    created_at datetime not null,
    user       int      null,
    constraint post_log_post_id_fk
        foreign key (post_id) references post (id)
);

create index post_log_created_at_index
    on post_log (created_at);

问题复现的查询语句 #

1. 索引正常生效的查询

explain
SELECT *
FROM post p
INNER JOIN post_log pl ON p.id = pl.post_id
WHERE pl.created_at > DATE('2022-06-01')
    AND pl.created_at < DATE('2022-06-08')
    AND p.is_used is TRUE
    AND p.is_deleted is FALSE;

2. 触发post表全表扫描的查询1

explain
SELECT *
FROM post p
INNER JOIN post_log pl ON p.id = pl.post_id
WHERE pl.created_at > DATE('2022-06-01')
    AND pl.created_at < DATE('2022-06-08')
    AND p.is_used = 1
    AND p.is_deleted = 0;

3. 触发post表全表扫描的查询2

explain
SELECT *
FROM post p
INNER JOIN post_log pl ON p.id = pl.post_id
WHERE pl.created_at > DATE('2022-06-01')
    AND pl.created_at < DATE('2022-06-08')
    and p.comment = 111;

问题1：tinyint = 1和tinyint IS TRUE的查询条件区别 #

两者核心差异在优化器的处理逻辑，而非最终匹配结果：

• 匹配规则层面：两者最终匹配的行结果完全一致，IS TRUE是MySQL原生布尔判断语法，仅识别数值1为真值、0为假值；= 1是普通数值比较，在当前tinyint(1)的字段类型下不会出现隐式转换，返回结果和IS TRUE完全相同。
• 优化器估算层面：IS TRUE/FALSE属于布尔常量判断，优化器会默认这类条件的筛选逻辑更严格，会用固定的低匹配度估算符合条件的行数；=1/=0属于普通数值比较，优化器会基于表的统计信息（比如字段的不同值占比、直方图信息）估算符合条件的行数，估算结果和实际数据分布强相关。

问题2：不同查询索引命中表现差异的原因 #

你观察到的"post表全表扫描"本质是MySQL优化器选择了不同的JOIN驱动表顺序，并非*post_log.created_at索引失效*：

• 第一条查询（IS TRUE条件）的执行逻辑：优化器通过布尔条件的固定估算规则，判定post表经过条件过滤后返回的行数远多于post_log表经过created_at时间范围过滤返回的行数，因此选择post_log作为驱动表：先通过post_log_created_at_index索引取出时间范围内的所有post_log记录，再通过post_id外键关联post表主键做匹配，这时候你看到的就是created_at索引正常生效，post表走主键关联，没有全表扫描。
• 第二条查询（=1条件）的执行逻辑：优化器基于统计信息估算post表经过is_used=1、is_deleted=0过滤后返回的行数更少，因此选择post表作为驱动表。由于post表没有为is_used、is_deleted字段建立索引，只能走全表扫描拿到符合条件的post id，再关联post_log表匹配时间条件，这时候就会出现post表全表扫描的现象。
• 第三条查询（comment=111条件）的执行逻辑：优化器估算comment=111的筛选度很高，符合条件的post行极少，同样选择post表作为驱动表。由于post表的comment字段没有索引，只能全表扫描找到comment=111的记录，再关联post_log表匹配时间条件，因此也会触发post表全表扫描。

优化建议 #

• 如果需要稳定使用created_at索引，可以在查询post_log表时加FORCE INDEX (post_log_created_at_index)强制走索引，避免优化器选错执行计划。
• 可以为post表的常用筛选字段建立联合索引，比如idx_used_deleted(is_used,is_deleted)、idx_comment(comment)，避免post表作为驱动表时出现全表扫描。
• 定期执行ANALYZE TABLE post,post_log更新表统计信息，减少优化器的行数估算误差。

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