mysql - 另一个 "Why is this statement running slowly"查询

Question

我已经在 SO 上阅读了与我类似的问题并谷歌搜索了好几天，但似乎找不到适合我的情况的解决方案。请参阅下面的详细信息。当我运行下面的 sql 语句时，它需要一分钟多的时间。它也没有使用 tableA.col4 上的索引。为什么？

就像我说的，我已经在谷歌上搜索了很长一段时间，但现在似乎在兜圈子。非常感谢任何帮助!

---细节---(抱歉太长了，但我想最好有所有信息)

我有以下两个表，表 A 和表 B:

tableA:(500 万行...最终将是 2000 万行)

tableA | CREATE TABLE `tableA` (
  `col1` varchar(50) NOT NULL,
  `col2` int(10) NOT NULL DEFAULT '0',
  `col3` varchar(300) DEFAULT NULL,
  `col4` varchar(50) DEFAULT NULL,
  `col5` datetime DEFAULT NULL,
  PRIMARY KEY (`col1`,`col2`),
  KEY `col4` (`col4`),
  KEY `col5` (`col5`),
  KEY `col1` (`col1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

表A的索引:

+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tableA |          0 | PRIMARY    |            1 | col1         | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| tableA |          0 | PRIMARY    |            2 | col2         | A         |     4780162 |     NULL | NULL   |      | BTREE      |         |               |
| tableA |          1 | col4       |            1 | col4         | A         |         426 |     NULL | NULL   | YES  | BTREE      |         |               |
| tableA |          1 | col5       |            1 | col5         | A         |     2390081 |     NULL | NULL   | YES  | BTREE      |         |               |
| tableA |          1 | col1       |            1 | col1         | A         |     2390081 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

tableB:(100 万行...最终将达到 500 万左右)

| tableB | CREATE TABLE `tableB` (
  `col1` varchar(300) NOT NULL DEFAULT '',
  `col2` varchar(300) DEFAULT NULL,
  `col3` int(10) DEFAULT NULL,
  `col4` varchar(300) DEFAULT NULL,
  `col5` varchar(300) DEFAULT NULL,
  `col6` varchar(300) DEFAULT NULL,
  `col7` varchar(300) DEFAULT '0',
  `col8` varchar(300) DEFAULT '0',
  `col9` varchar(300) DEFAULT '0',
  `col10` varchar(300) DEFAULT NULL,
  `col11` varchar(300) DEFAULT NULL,
  `col12` mediumtext,
  `col13` decimal(15,2) DEFAULT NULL,
  `col14` decimal(15,2) DEFAULT NULL,
  `col15` varchar(300) DEFAULT NULL,
  `col16` varchar(300) DEFAULT NULL,
  `col17` decimal(15,2) DEFAULT NULL,
  `col18` decimal(15,2) DEFAULT NULL,
  `col19` varchar(300) DEFAULT NULL,
  `col20` varchar(300) DEFAULT NULL,
  `col21` decimal(15,2) DEFAULT NULL,
  `col22` decimal(15,2) DEFAULT NULL,
  `col23` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`col1`),
  KEY `col3` (`col3`),
  KEY `col5` (`col5`),
  KEY `col2` (`col2`),
  KEY `col1` (`col1`),
  FULLTEXT KEY `col12` (`col12`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

表A的索引:

+----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name      | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tableB |          0 | PRIMARY        |            1 | col1           | A         |      989765 |     NULL | NULL   |      | BTREE      |         |               |
| tableB |          1 | col3           |            1 | col3           | A         |        9799 |     NULL | NULL   | YES  | BTREE      |         |               |
| tableB |          1 | col5           |            1 | col5           | A         |       98976 |     NULL | NULL   | YES  | BTREE      |         |               |
| tableB |          1 | col2           |            1 | col2           | A         |      197953 |     NULL | NULL   | YES  | BTREE      |         |               |
| tableB |          1 | col1           |            1 | col1           | A         |      989765 |     NULL | NULL   |      | BTREE      |         |               |
| tableB |          1 | col12          |            1 | col12          | NULL      |           1 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

解释:

EXPLAIN SELECT *
FROM tableA LEFT OUTER JOIN tableB ON tableA.col1 = tableB.col1 
WHERE tableA.col4 NOT IN ('8/G','2','9/D','7','6/M') ORDER BY tableA.col5 DESC LIMIT 25

+----+-------------+----------+--------+-------------------+---------+---------+-----------------------+---------+-----------------------------+
| id | select_type | table    | type   | possible_keys     | key     | key_len | ref                   | rows    | Extra                       |
+----+-------------+----------+--------+-------------------+---------+---------+-----------------------+---------+-----------------------------+
|  1 | SIMPLE      | tableA   | ALL    | col4              | NULL    | NULL    | NULL                  | 4780162 | Using where; Using filesort |
|  1 | SIMPLE      | tableB   | eq_ref | PRIMARY, col1     | PRIMARY | 902     | mydb.tableA.col1      |       1 |                             |
+----+-------------+----------+--------+-------------------+---------+---------+-----------------------+---------+-----------------------------+

分析:

Status  Time
starting    0.000012
Waiting for query cache lock    0.000003
checking query cache for query  0.000079
checking permissions    0.000004
checking permissions    0.000003
Opening tables  0.000013
System lock 0.000006
Waiting for query cache lock    0.000027
init    0.000028
optimizing  0.000008
statistics  0.000404
preparing   0.000009
executing   0.000003
Sorting result  2.135376
Sending data    0.004359
Waiting for query cache lock    0.000004
Sending data    0.004735
Waiting for query cache lock    0.000004
....
....
....   
Sending data    0.008573
Waiting for query cache lock    0.000006
Sending data    0.005630
Waiting for query cache lock    0.000005
Sending data    0.005760
Waiting for query cache lock    0.000005
Sending data    0.005295
Waiting for query cache lock    0.000005
Sending data    0.005239
Waiting for query cache lock    0.000004
Sending data    0.004954
Waiting for query cache lock    0.000004
Sending data    82.246597
end 0.000012
query end   0.000004
closing tables  0.000009
freeing items   0.000014
logging slow query  0.000002
logging slow query  0.000003
cleaning up 0.000003

Answer 1

It also is not using the index on tableA.col4. Why?

嗯，tableA.col4 NOT IN ('8/G','2','9/D','7','6/M') 是罪魁祸首。 MySQL 确实不能使用索引来满足 NOT IN。

发生的事情是 MySQL 正在运行 tableA 的所有约 500 万行并构建一组行，其中 tableA.col4 NOT IN ('8/G','2','9/D','7 ', '6/M').然后按 col5 降序排序，然后(希望如此)从中挑选出前 25 行，然后执行连接。

tableA 上的 col5 和 col4(按此顺序)的多列索引可能有助于此查询，因为我相信 MySQL 将以相反的顺序处理索引，并在它获得满足 tableA 的 25 行后停止。 col4 NOT IN ('8/G','2','9/D','7','6/M').

所以我的建议是:

CREATE INDEX ix_tableA_col5_col4 ON tableA(col5,col4);

然后重新运行您的查询。