MySQL MyISAM表性能...痛苦，痛苦，缓慢 [英] MySQL MyISAM table performance... painfully, painfully slow
问题描述
我有一个表结构，可以总结如下:
pagegroup
* pagegroupid
* name
具有3600行
page
* pageid
* pagegroupid
* data
参考页组； 有10000行； 每个页面组可以有1-700行之间的任何内容； data列的类型为mediumtext，该列每行包含100k-200kbytes数据
userdata
* userdataid
* pageid
* column1
* column2
* column9
参考页面； 有大约300,000行； 每页最多可以有1-50行
上面的结构很简单，问题是，即使我已经索引了所有应该索引的列，从userdata到页面组的连接也非常缓慢.对此类联接(用户数据inner_join页inner_join页组)运行查询所需的时间超过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
故事的道德感
如果遇到诸如此类的性能问题，请在单独的表中保留中/长文本列.
userdata表中columnX的数据类型和用途是什么?应该注意的是，任何文本数据类型(即不包括char，varchar)都将强制在磁盘上创建任何临时表.现在，由于您是在没有条件，分组或排序的情况下进行直接联接，因此除了聚合最终结果之外，它可能不需要任何临时表.
如果您向我们展示如何创建索引，我认为这也将非常有帮助.要记住的一件事是，虽然InnoDB将表的主键连接到每个索引，但MyISAM却没有.这意味着，如果您为列 name 编制索引并使用LIKE搜索它，但仍想获取页面组的 id ；然后查询仍然需要访问表以获取 id ，而不是能够从索引中检索它.
对于您而言，如果我正确理解您对 apphacker 的评论，这意味着获取每个用户页面组的名称.查询优化器希望将索引用于联接，但是对于每个结果，它也需要访问表以检索页面组名.如果 name 上的数据类型不大于中等varchar(即没有文本)，则还可以创建一个索引(id，name)，该索引使查询能够直接从索引中获取名称. /p>
作为最后的尝试，您指出，如果中间文本不在页面表中，则整个查询可能会更快.
- 假定您正在运行的查询中不包括此列?
- 您还可以尝试将页面数据与页面配置"分开，即它属于哪个组.然后，您可能会有类似以下内容:
- 页面
- pageId
- pageGroupId
- PageData
- pageId
- 数据
- 页面
由于Pages中的任何列都不占用太多空间，因此有望使您更快地加入.然后，当您需要显示某个页面时，您可以与pageId列上的PageData表结合，以获取显示特定页面所需的数据.
I've got a table structure that can be summarized as follows:
pagegroup
* pagegroupid
* name
has 3600 rows
page
* pageid
* pagegroupid
* data
references pagegroup; has 10000 rows; can have anything between 1-700 rows per pagegroup; the data column is of type mediumtext and the column contains 100k - 200kbytes data per row
userdata
* userdataid
* pageid
* column1
* column2
* column9
references page; has about 300,000 rows; can have about 1-50 rows per page
The above structure is pretty straight forwad, the problem is that that a join from userdata to page group is terribly, terribly slow even though I have indexed all columns that should be indexed. The time needed to run a query for such a join (userdata inner_join page inner_join pagegroup) exceeds 3 minutes. This is terribly slow considering the fact that I am not selecting the data column at all. Example of the query that takes too long:
SELECT userdata.column1, pagegroup.name
FROM userdata
INNER JOIN page USING( pageid )
INNER JOIN pagegroup USING( pagegroupid )
Please help by explaining why does it take so long and what can i do to make it faster.
Edit #1
Explain returns following gibberish:
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
Edit #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
Moral of the story
Keep medium/long text columns in a separate table if you run into performance problems such as this one.
What's the data type and purpose of columnX in the userdata table? It should be noted that any text data type (i.e excluding char, varchar) forces any temporary tables to be created on disk. Now since you're doing a straight join without conditions, grouping or ordering, it probably won't need any temporary tables, except for aggregating the final result.
I think it would also be very helpful if you show us how your indexes are created. One thing to remember is that while InnoDB concatenates the primary key of the table to each index, MyISAM does not. This means that if you index column name and search for it with LIKE, but still want to get the id of the page group; Then the query would still need to visit the table to get the id instead of being able to retrieve it from the index.
What this means, in your case, if I understand your comment to apphacker correctly, is to get the name of each users pagegroups. The query optimizer would want to use the index for the join, but for each result it would also need to visit the table to retrieve the page group name. If your datatype on name is not bigger than a moderate varchar, i.e. no text, you could also create an index (id, name) which would enable the query to fetch the name directly from the index.
As a final try, you point out that the whole query would probably be faster if the mediumtext was not in the page table.
- This column is excluded from the query you are running I presume?
- You could also try to separate the page data from the page "configuration", i.e. which group it belongs to. You'd then probably have something like:
- Pages
- pageId
- pageGroupId
- PageData
- pageId
- data
- Pages
This would hopefully enable you to join quicker since no column in Pages take up much space. Then, when you needed to display a certain page, you join with the PageData table on the pageId-column to fetch the data needed to display a particular page.
这篇关于MySQL MyISAM表性能...痛苦，痛苦，缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
