联合视图索引失效问题：PHP API、字符集与参数绑定的影响

问题描述 #

为何索引在特定场景下被忽略，导致查询执行缓慢？经排查，该问题与UNION ALL的使用、所选PHP数据库API、字符集以及参数绑定实现的组合有关。

环境配置 #

创建视图my_view，定义如下：

SELECT type FROM table1

UNION ALL

SELECT type FROM table2

table1和table2结构相同，示例结构：

CREATE TABLE table1
(
    id INT UNSIGNED AUTO_INCREMENT,
    type INT NOT NULL,
    ... -- 其他数据列
    PRIMARY KEY (id)
);

CREATE INDEX my_idx ON table1 (type);

查询需求为获取type = 1的所有记录，执行语句：

SELECT * FROM my_view WHERE type = 1;

在SQL控制台执行EXPLAIN，索引正常使用，查询速度极快：

┌────┬─────────────┬────────────┬───────┬───────────────┬────────┬─────────┬────────┬──────┬─────────────┐
│ id │ select_type │ table      │ type  │ possible_keys │ key    │ key_len │ ref    │ rows │ Extra       │
╞════╪═════════════╪════════════╪═══════╪═══════════════╪════════╪═════════╪════════╪══════╪═════════════╡
│  1 │ PRIMARY     │ <derived2> │ ALL   │               │        │         │        │ 110  │ Using where │
│  2 │ DERIVED     │ table1     │ const │ my_idx        │ my_idx │ 5       │ const  │ 72   │ Using index │
│  3 │ UNION       │ table2     │ const │ my_idx        │ my_idx │ 5       │ const  │ 38   │ Using index │
└────┴─────────────┴────────────┴───────┴───────────────┴────────┴─────────┴────────┴──────┴─────────────┘

问题现象 #

使用PHP执行相同查询时，数据库会根据所选API、字符集及参数绑定方式决定是否忽略视图索引，失效场景的EXPLAIN结果：

┌────┬─────────────┬────────────┬───────┬───────────────┬────────┬─────────┬─────┬──────────┬─────────────┐
│ id │ select_type │ table      │ type  │ possible_keys │ key    │ key_len │ ref │ rows     │ Extra       │
╞════╪═════════════╪════════════╪═══════╪═══════════════╪════════╪═════════╪═════╪══════════╪═════════════╡
│ 1  │ PRIMARY     │ <derived2> │ ALL   │               │        │         │     │ 56247595 │ Using where │
│ 2  │ DERIVED     │ table1     │ index │               │ my_idx │ 5       │     │ 34706361 │ Using index │
│ 3  │ UNION       │ table2     │ index │               │ my_idx │ 5       │     │ 21541234 │ Using index │
└────┴─────────────┴────────────┴───────┴───────────────┴────────┴─────────┴─────┴──────────┴─────────────┘

调试结果 #

不同组合下索引的有效性：

版本信息 #

• MariaDB 10.6.18
• PHP 8.2.26

执行计划与SQL重写分析 #

执行EXPLAIN EXTENDED SELECT ...后执行SHOW WARNINGS，视图查询的重写结果一致：

┌───────┬──────┬──────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ Level │ Code │ Message                                                                                                  │
├───────┼──────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Note  │ 1003 │ /* select#1 */ select `my_view`.`type` AS `type` from `my_database`.`my_view` where `my_view`.`type` = 1 │
└───────┴──────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────┘

使用子查询替代视图时，重写结果出现差异：

查询语句 #

EXPLAIN EXTENDED SELECT * FROM (
    SELECT type from table1 UNION ALL SELECT type from table2
) AS q WHERE type = ?;

SHOW WARNINGS;

索引有效场景（✅）：条件被推入子查询 #

/* select#1 */
SELECT q.type AS type
FROM (
    /* select#2 */
    SELECT table1.type AS type
    FROM my_database.table1
    WHERE table1.type = 1
    
    UNION ALL
    
    /* select#3 */
    SELECT table2.type AS type
    FROM my_database.table2
    WHERE table2.type = 1
) q
WHERE q.type = 1;

索引失效场景（❌）：条件未被推入子查询 #

/* select#1 */
SELECT q.type AS type
FROM (
    /* select#2 */
    SELECT table1.type AS type
    FROM my_database.table1
    
    UNION ALL
                            
    /* select#3 */
    SELECT table2.type AS type
    FROM my_database.table2
) q
WHERE q.type = 1;

优化器追踪差异 #

启用MariaDB优化器追踪（设置optimizer_trace = enabled=on）后，通过SELECT * FROM information_schema.optimizer_trace查看结果，核心差异为：

• 索引有效场景：优化器将外层WHERE type = ?的条件下推到UNION ALL的两个子查询中，每个子查询使用my_idx索引过滤数据。
• 索引失效场景：优化器未执行条件下推，先扫描两个表的全量数据生成临时表，再对临时表进行过滤，导致索引无法发挥作用。

原因总结 #

核心问题是MariaDB优化器在处理utf8mb4字符集下的原生参数绑定时，存在条件下推逻辑缺陷：

1. 当使用模拟预处理（如PDO默认的ATTR_EMULATE_PREPARES=true、mysqli的execute_query）或latin1字符集时，参数会被客户端转换为常量值发送给数据库，优化器可以正常识别并将外层条件下推到UNION ALL的子查询中，从而使用索引快速过滤。
2. 当使用utf8mb4字符集+原生参数绑定（mysqli的bind_param、PDO关闭模拟预处理）时，优化器无法正确判断参数类型与视图内INT字段的兼容性，因此不执行条件下推，只能全量扫描后过滤，导致索引失效。

该问题已提交MariaDB Bug报告（编号：MDEV-35561）。

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