Mysql 自连接性能

Question

假设我有一个如下所示的表格:

+----+----------+------------+-------+------+
| id | category |      state | A1code| val  |
+----+----------+------------+-------+------+
|  1 |        1 |    Florida | 13000 | 12   |
|  2 |        1 |    Florida | 13001 | 14   |
|  3 |        1 |    Florida | 13002 | 15   |
|  4 |        2 |    Florida | 13000 | 12   |
|  5 |        2 |    Florida | 13001 | 17   |
|  6 |        2 |    Florida | 13002 | 16   |
|  7 |        1 |  Calfornia | 13000 | 15   |
|  8 |        1 |  Calfornia | 13001 | 13   |
|  9 |        1 |  Calfornia | 13002 | 14   |
| 10 |        2 |  Calfornia | 13000 | 12   |
| 11 |        2 |  Calfornia | 13001 | 14   |
| 12 |        2 |  Calfornia | 13002 | 16   |
....
+----+----------+------------+------+

我需要以这种方式得到结果:

state, A1code, category1, category2
Florida,13000, 12,12
Florida,13001, 14,17
Florida,13002, 15,16
Calfornia,13000, 15,12
Calfornia,13001, 13,14
Calfornia,13002, 14,16
....

我目前看到的 sql 是这样的:

SELECT A.STATE, A.A1CODE, A.val AS category1, B.val AS category 2
FROM DUMMY_TABLE A
INNER JOIN DUMMY_TABLE B
USING (STATE,A1CODE)
WHERE A.category = 1 AND B.category = 2;

对于一个大约 60k 长的表，此查询在计算机上运行大约需要 40 秒。

现在有一个像

这样的查询

SELECT A.STATE, A.A1CODE, A.val AS category1
FROM DUMMY_TABLE A
WHERE A.category = 1

运行时间不到 0.1 秒，我想要的只是合并两个类别的结果，一定有更快的方法吗？

(当我尝试将数据库从 MS ACCESS 移植到 MYSQL 时出现此问题。在 MYSQL 上花费约 40 秒的相同查询在 MS ACCESS 中运行需要约 1 秒。)

提前致谢

Answer 1

尝试以下变体

  SELECT A.STATE, A.A1CODE, A.val AS category1, B.val AS category 2
    FROM (select A.STATE A.A1code, A.val as category1 from DUMMY_TABLE A  where A.category = 1 ) as A
    LEFT JOIN DUMMY_TABLE B
    USING (STATE,A1CODE)
    WHERE B.category = 2  ;

一定要快得多。

但实际上这取决于 1) 您在第一个查询中有多少列 2) 您是否有关于 A1CODE、STATE 的索引。

所以要加速尝试

create index DUMMY_TABLE_get on DUMMY_TABLE(A1CODE,STATE);

还有一些方法可以将此查询加速到 10-100 毫秒，但它们需要添加额外的字段/触发器。因此，如果您不想每分钟都收到该查询，那就没有意义了。

请注意，40 秒对于您的要求来说也太多了。可能你雇用了 mysql 管理员来调整你的 mysql 服务器(使用更多 RAM 进行连接，增加 keybuffer 等)