sql - MySQL MyISAM 表性能......痛苦地，痛苦地慢

Question

我有一个表结构，可以概括如下:

pagegroup
* pagegroupid
* name

有 3600 行

page
* pageid
* pagegroupid
* data

引用页面组；有 10000 行；每个页组可以有 1-700 行；数据列是 mediumtext 类型，每行包含 100k - 200kbytes 数据

userdata
* userdataid
* pageid
* column1
* column2
* column9

引用页面；大约有 300,000 行；每页可以有大约 1-50 行

上面的结构非常简单，问题是从用户数据到页面组的连接非常非常慢，即使我已经索引了所有应该索引的列。为此类连接(userdata inner_join page inner_join pagegroup)运行查询所需的时间超过 3 分钟。考虑到我根本没有选择数据列这一事实，这非常慢。查询时间过长的示例:

SELECT userdata.column1, pagegroup.name
FROM userdata
INNER JOIN page USING( pageid )
INNER JOIN pagegroup USING( pagegroupid )

请帮助解释为什么需要这么长时间以及我该怎么做才能让它更快。

编辑#1

解释乱码后的返回:

id  select_type  table      type    possible_keys        key      key_len  ref                         rows    Extra
1   SIMPLE       userdata   ALL     pageid                                                             372420
1   SIMPLE       page       eq_ref  PRIMARY,pagegroupid  PRIMARY  4        topsecret.userdata.pageid   1
1   SIMPLE       pagegroup  eq_ref  PRIMARY              PRIMARY  4        topsecret.page.pagegroupid  1

编辑#2

SELECT
u.field2, p.pageid
FROM
userdata u
INNER JOIN page p ON u.pageid = p.pageid;
/*
0.07 sec execution, 6.05 sec fecth
*/

id  select_type  table  type    possible_keys  key      key_len  ref                rows     Extra
1   SIMPLE       u      ALL     pageid                                              372420
1   SIMPLE       p      eq_ref  PRIMARY        PRIMARY  4        topsecret.u.pageid 1        Using index

SELECT
p.pageid, g.pagegroupid
FROM
page p
INNER JOIN pagegroup g ON p.pagegroupid = g.pagegroupid;
/*
9.37 sec execution, 60.0 sec fetch
*/

id  select_type  table  type   possible_keys  key          key_len  ref                      rows  Extra
1   SIMPLE       g      index  PRIMARY        PRIMARY      4                                 3646  Using index
1   SIMPLE       p      ref    pagegroupid    pagegroupid  5        topsecret.g.pagegroupid  3     Using where

故事的寓意

如果您遇到此类性能问题，请将中/长文本列保存在单独的表中。

Answer 1

userdata 表中 columnX 的数据类型和用途是什么？应该注意的是，任何文本数据类型(即不包括 char、varchar)都会强制在磁盘上创建任何临时表。现在，由于您正在进行不带条件、分组或排序的直接联接，因此它可能不需要任何临时表，除了聚合最终结果。

如果您向我们展示您的索引是如何创建的，我认为这也会非常有帮助。要记住的一件事是，虽然 InnoDB 将表的主键连接到每个索引，但 MyISAM 不会。这意味着如果您索引列 name 并使用 LIKE 搜索它，但仍想获得页组的 id；然后查询仍需要访问表以获取 id，而不是能够从索引中检索它。

在您的情况下，如果我正确理解您对 apphacker 的评论，这意味着获取每个用户页面组的名称。查询优化器希望使用索引进行连接，但对于每个结果，它还需要访问表以检索页组名称。如果您在 name 上的数据类型不大于中等 varchar，即没有文本，您还可以创建一个索引 (id, name)，这将使查询能够直接从索引中获取名称。

作为最后的尝试，您指出如果 mediumtext 不在页表中，整个查询可能会更快。

1. 我认为该列已从您正在运行的查询中排除？
2. 您也可以尝试将页面数据与页面“配置”分开，即它属于哪个组。然后你可能会有类似的东西:
   • 页面
     • 页面编号
     • 页面组别
   • 页面数据
     • 页面编号
     • 数据

希望这能让您更快地加入，因为页面中的任何栏目都不会占用太多空间。然后，当您需要显示某个页面时，您与 pageId 列上的 PageData 表连接以获取显示特定页面所需的数据。