尽管有索引，MySQL的选择查询性能很差

Question

我们有两个表、消息和客户合同，定义如下：

create table customercontracts (customer_id varchar(20), 
                                contractnumber varchar(20), 
                                role varchar(4));
alter table customercontracts add index contractnumber (contractnumber);

create table messages (customer_id varchar(20), 
                                contractnumber varchar(20), 
                                message varchar(400));
alter table messages add index contractnumber (contractnumber);
alter table messages add index customer_id (customer_id );

还有这样的查询：

select * from messages m, customercontracts c 
 where m.customer_id = '12345' 
   and c.contractnumber = m.contractnumber;

大约有4,000条消息行和3,000,000条客户合同行。尽管customer_id和契约号都有索引，但上面的查询大约需要4秒才能执行。“Ex平原”(在MySQL工作台中)显示了对客户合同的全表扫描，查询成本为628,000。

问题：

1)当我在这些表上有索引时，为什么会对客户合同进行全表扫描？业绩不佳的原因是什么？

2)如何重写此查询以使其具有表现性？

Answer 1

我想我终于找到了答案。上面的示例表创建代码没有显示字符编码(对我来说是一个学习，不要快捷示例代码！)事实证明，customercontacts表是用utf8编码的，而messages表是用utf8mb4编码的。

当连接不同字符编码的列时，不能使用索引。

这回答了“为什么会发生这种事”。为了解决这个问题，我们修改了utf8m4表，将索引列的编码降级为utf8 (同时将表其余部分的编码保持为utf8mb4)。这对我们来说是固定的表现。

Answer 2

我建议在message customer_id上尝试复合索引、契约号和检查性能，尽量避免在select中使用*。

Answer 3

我会考虑添加一个整数"id“字段作为customercontracts的主键，然后通过customercontracts_id而不是messages表中的契约号进行引用。你应该已经看到了性能的提高。

或者，您可以尝试在两个合同编号列上添加全文索引，但我建议使用第一个选项。