还有另一个“为什么这条语句运行缓慢”的查询

Question

几天来，我一直在网上阅读和我的问题类似的问题，但似乎找不到一个适合我的情况的解决方案。请查看下面的详细信息。当我运行下面的sql语句时，它需要一分钟以上的时间。它也没有在tableA.col4上使用索引。为什么？

正如我所说的，我已经搜索了相当长的一段时间，但在这一点上似乎还在兜圈子。任何帮助都是非常感谢的！

-细节-(对不起，它太长了，但我想最好还是有所有的信息)

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

tableA：(500万rows...will最终成为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 |

tableA上的索引：

+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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万rows...will最终达到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 |

tableA上的索引：

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

它也没有在tableA.col4上使用索引。为什么？

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

正在发生的事情是，MySQL正在遍历大约500万行tableA，并构建一组tableA.col4不在IN ('8/G'，'2'，'9/D'，'7'，'6/M')的行。然后按col5以降序对该集合进行排序，然后(希望)从中挑选前25行，然后执行连接。

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

所以我的建议是：

CREATE INDEX ix_tableA_col5_col4 ON tableA(col5,col4);

然后重新运行您的查询。

Answer 2

嗯。只是在做一个快速拍摄。分析输出表明发送数据需要82.246597的时间，所以可能会得到很多输出，这需要很长时间来传输？