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 |
+----+-------------+-------+------+---------------+------+---------+---------------------+-------+-------------------------+

标签： mysqlsql

mysql - 加入索引字段不使用索引

问题描述

解决方案

推荐阅读