mysql连接字段需要索引_mysql – 索引字段上的连接不使用索引

我有两张桌子.我写了一个查询来在一列上加入它们.此列在两个表中都已编制索引,但MySQL未使用索引.有人a)告诉我为什么和b)告诉我如何让MySQL使用索引快速加入这些表.

第一张表：

CREATE TABLE `dol_msa_zip_assoc` (

`pkey` int(5) unsigned NOT NULL AUTO_INCREMENT,

`zip` varchar(5) NOT NULL DEFAULT '',

`pmsa_msa` varchar(5) NOT NULL DEFAULT '',

PRIMARY KEY (`pkey`),

KEY `zip` (`zip`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

它包含42690条记录.

另一张桌子：

CREATE TABLE `v3_msa_zip_assoc` (

`pkey` int(5) unsigned NOT NULL AUTO_INCREMENT,

`zip` varchar(9) NOT NULL DEFAULT '',

`pmsa_msa` varchar(6) NOT NULL DEFAULT '',

PRIMARY KEY (`pkey`),

KEY `zip` (`zip`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

它包含42486条记录.

我的查询,旨在查找第一个表中不在第二个表中的记录：

SELECT d.*, o.* FROM `dol_msa_zip_assoc` d

LEFT JOIN `v3_msa_zip_assoc` o

ON o.zip = d.zip

WHERE o.zip IS NULL

当我解释此查询时,我看到zip列上的索引未被使用：

+----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------------------------+

| 1 | SIMPLE | d | ALL | NULL | NULL | NULL | NULL | 42915 | NULL |

| 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 42486 | Using where; Not exists; Using join buffer (Block Nested Loop) |

+----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------------------------+

导致此失败的原因可能是什么？是因为一个zip列是latin1而另一个是utf8？如何使用此查询来使用索引,以便它不需要运行年龄？

编辑：反转JOIN的顺序显然确实利用了索引：

SELECT d . * , o . *

FROM `v3_msa_zip_assoc` o

LEFT JOIN `dol_msa_zip_assoc` d ON d.zip = o.zip

WHERE d.zip IS NULL

这是解释：

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------------------+

| 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 42486 | NULL |

| 1 | SIMPLE | d | ref | zip | zip | 17 | func | 1 | Using where; Not exists |

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------------------+

编辑2：我已经改变旧表的结构以使用utf8排序规则.现在定义如下：

CREATE TABLE `v3_msa_zip_assoc` (

`pkey` int(5) unsigned NOT NULL AUTO_INCREMENT,

`zip` varchar(9) NOT NULL DEFAULT '',

`pmsa_msa` varchar(6) NOT NULL DEFAULT '',

PRIMARY KEY (`pkey`),

KEY `zip` (`zip`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这似乎解决了这个问题：

mysql> EXPLAIN SELECT d . * , o . *

-> FROM `dol_msa_zip_assoc` d

-> LEFT JOIN `v3_msa_zip_assoc` o ON o.zip = d.zip

-> WHERE o.zip IS NULL;

+----+-------------+-------+------+---------------+------+---------+---------------------+-------+-------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+---------------+------+---------+---------------------+-------+-------------------------+

| 1 | SIMPLE | d | ALL | NULL | NULL | NULL | NULL | 42915 | NULL |

| 1 | SIMPLE | o | ref | zip | zip | 29 | myplan_v4_dev.d.zip | 1 | Using where; Not exists |

+----+-------------+-------+------+---------------+------+---------+---------------------+-------+-------------------------+