关于Mysql内连接不走索引的问题

Question

例如下文：

select * from test_one a
left join test_two b on a.id = SUBSTR(b.newid,1,7) 

这种SQL的查询语句很慢，请问这种方式有什么办法走索引或其他方式加快sql运行速度吗？

Answer 1

在SQL查询中，当使用函数（如 SUBSTR）来处理连接条件中的字段时，这通常会导致索引失效，因为数据库通常不能有效地使用索引来查找被函数处理过的数据。在你提供的查询中，SUBSTR(b.newid,1,7) 使得 test_two 表的 newid 字段上的任何索引都无法被利用，这可能导致查询性能下降。

要优化这种查询，可以考虑创建计算列索引：

在 test_two 表上创建一个新的计算列，该列存储 newid 字段的前7个字符。
为这个新列创建索引。
然后，可以在连接条件中使用这个新列，而不是使用 SUBSTR 函数。

ALTER TABLE test_two ADD COLUMN newid_prefix VARCHAR(7);
UPDATE test_two SET newid_prefix = SUBSTR(newid, 1, 7);
CREATE INDEX idx_newid_prefix ON test_two(newid_prefix);

-- 然后在查询中使用新列
SELECT * FROM test_one a
LEFT JOIN test_two b ON a.id = b.newid_prefix;