为什么这个查询运行得这么慢？

Question

我有两个 MySQL 表 A 和 B。A 仅包含一个 varchar 列（我们称其为 A1），其中包含大约 23000 条记录。表 B（70000 条记录）还有一些列，其中一列与表 A 中的 A1 对应（我们称其为 B1）。我想知道 A 中的哪些值不在 B 中的相应列中，因此我使用：

SELECT A1
FROM A
LEFT JOIN B
    ON A1 = B1
WHERE B1 IS NULL 

A1 和 B1 列都定义了索引。但这个查询运行速度仍然非常慢。我已经运行了解释，这是输出：

id  select_type table   type    possible_keys   key     key_len ref rows    Extra
1   SIMPLE      A       index   \N              PRIMARY 767     \N  23269   Using index
1   SIMPLE      B       ALL     \N              \N      \N      \N  70041   Using where; Not exists

UPDATE: SHOW CREATE TABLE 对于两个表（更改了原始名称）；

CREATE TABLE `A` (
  `A1` varchar(255) NOT NULL,
  PRIMARY KEY  (`A1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8


CREATE TABLE `B` (
  `col1` int(10) unsigned NOT NULL auto_increment,
  `col2` datetime NOT NULL,
  `col3` datetime default NULL,
  `col4` datetime NOT NULL,
  `col5` varchar(30) NOT NULL,
  `col6` int(10) default NULL,
  `col7` int(11) default NULL,
  `col8` varchar(20) NOT NULL,
  `B1` varchar(255) default NULL,
  `col10` tinyint(1) NOT NULL,
  `col11` varchar(255) default NULL,
  PRIMARY KEY  (`col1`),
  KEY `NewIndex1` (`B1`)
) ENGINE=MyISAM AUTO_INCREMENT=70764 DEFAULT CHARSET=latin1

'另一个编辑：SHOW TABLE STATUS 中的 data_length 和 index_length

table   data_length index_length
A       465380      435200
B       5177996     1344512

Answer 1

您在 OUTER JOIN 中比较的两列的字符集不同。我不确定是否是这个原因，所以我测试并得到了这些结果：

SELECT A1
FROM A
LEFT JOIN B ON A1 = B1
WHERE B1 IS NULL

-- Table A..: 23258 rows, collation = utf8_general_ci
-- Table B..: 70041 rows, collation = latin1_swedish_ci
-- Time ....: I CANCELLED THE QUERY AFTER 20 MINUTES

-- Table A..: 23258 rows, collation = latin1_swedish_ci
-- Table B..: 70041 rows, collation = latin1_swedish_ci
-- Time ....: 0.187 sec

-- Table A..: 23258 rows, collation = utf8_general_ci
-- Table B..: 70041 rows, collation = utf8_general_ci
-- Time ....: 0.344 sec

解决方案：使两个表（或至少两列）的字符集相同。

Answer 2

此查询将扫描表 A 的所有行，但如果您在 B1 上有索引，那么它很可能不会扫描表 B：

select A1
from A
where not exists (
    select *
    from B
    where B.B1 = A.A1
)

在运行此查询或原始查询之前，您可以尝试在以下位置运行 ANALYZE TABLE为了更新这些表的密钥分布信息：

ANALYZE TABLE A, B

如果这没有帮助，那么您可以尝试使用索引，例如：

select A1
from A ignore index (PRIMARY)
where not exists (
    select *
    from B force index (NewIndex1)
    where B.B1 = A.A1
)

Answer 3

看起来A1和B1是很大的领域。

您为 A1 和 B1 创建了索引

请确保它们已建立索引！

SELECT A1
FROM A
WHERE A1 NOT IN (
    SELECT B1 AS A1 From B;
)

Answer 4

如果我使用您的 CREATE TABLES 语句并在您的 SELECT 语句上运行 EXPLAIN，我会得到以下结果：

id  select_type  table  type  possible_keys  key       key_len  ref  rows  Extra  
1   SIMPLE       A      index NULL           PRIMARY   767      NULL 2     Using index 
1   SIMPLE       B      index NULL           NewIndex1 258      NULL 4     Using where; Using index 

在我的 MySQL 版本（5.1.41）上，索引按预期使用，所以我认为这可能是 MySQL 中已经修复的错误，假设您的索引设置就像您发布的创建表语句中一样。你使用什么版本的MySQL？

Answer 5

尝试这个查询：

SELECT B1
FROM B
WHERE not B1  in (
  select A1
  from a
)