MariaDB关联查询未用索引，添加重复索引后才生效的异常问题

在MariaDB 10.6.8版本中发现异常行为：关联查询的条件基于已建立复合索引的列，但查询优化器并未使用该索引；只有当为相同列创建重复索引后，索引才会被正常使用，该现象在单条数据和大量数据场景下测试结果一致。

表定义 #

MariaDB [ac]> show create table employee;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                     |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
  `id` varchar(10) COLLATE utf8mb3_bin NOT NULL,
  `name` varchar(300) COLLATE utf8mb3_bin NOT NULL,
  `sal` varchar(255) COLLATE utf8mb3_bin DEFAULT NULL,
  PRIMARY KEY (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin ROW_FORMAT=DYNAMIC |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

MariaDB [ac]> show create table employee_details;
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table            | Create Table                                                                                                                                                                                                                                                                                                 |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee_details | CREATE TABLE `employee_details` (
  `id` varchar(10) COLLATE utf8mb3_bin NOT NULL,
  `name` varchar(300) COLLATE utf8mb3_bin NOT NULL,
  `mobile` varchar(255) COLLATE utf8mb3_bin DEFAULT NULL,
  KEY `empkey` (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin ROW_FORMAT=DYNAMIC |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

首次关联查询的EXPLAIN结果 #

插入单条记录后执行关联查询，EXPLAIN输出如下：

MariaDB [ac]> explain select * from employee e inner join employee_details ed on e.id=ed.id and e.name=ed.name;
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                           |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
|    1 | SIMPLE      | e     | ALL  | PRIMARY       | NULL | NULL    | NULL | 1    |                                                 |
|    1 | SIMPLE      | ed    | ALL  | empkey        | NULL | NULL    | NULL | 1    | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+

注意：possible_keys中列出了empkey，但查询实际未使用该索引

添加重复索引后的查询结果 #

为employee_details表的相同字段添加重复索引：

MariaDB [ac]> alter table employee_details add key empkey1(id, name);
Query OK, 0 rows affected, 1 warning (0.006 sec)
Records: 0  Duplicates: 0  Warnings: 1

再次执行相同的关联查询，EXPLAIN输出如下：

MariaDB [ac]> explain select * from employee e inner join employee_details ed on e.id=ed.id and e.name=ed.name;
+------+-------------+-------+------+----------------+---------+---------+-------------------+------+-------+
| id   | select_type | table | type | possible_keys  | key     | key_len | ref               | rows | Extra |
+------+-------------+-------+------+----------------+---------+---------+-------------------+------+-------+
|    1 | SIMPLE      | e     | ALL  | PRIMARY        | NULL    | NULL    | NULL              | 1    |       |
|    1 | SIMPLE      | ed    | ref  | empkey,empkey1 | empkey1 | 934     | ac.e.id,ac.e.name | 1    |       |
+------+-------------+-------+------+----------------+---------+---------+-------------------+------+-------+
2 rows in set (0.001 sec)

此次possible_keys中列出了两个索引，新索引empkey1被成功使用

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