代码之家 › 专栏 › 技术社区 › Salman Arshad

mysql myisam表性能…痛苦,痛苦缓慢

myisam query-optimization performance mysql sql

Salman Arshad · 技术社区 · 16 年前

我有一个表格结构,可以总结如下:

pagegroup
* pagegroupid
* name

有3600行

page
* pageid
* pagegroupid
* data

引用页面组; 有10000行; 每个页面组可以有1-700行之间的任何内容; 数据列的类型为MediumText,该列每行包含100k-200kbytes数据。

userdata
* userdataid
* pageid
* column1
* column2
* column9

参考页; 约30万行; 每页大约有1-50行

上面的结构是非常直接的forwad,问题是从userdata到page group的连接非常非常慢,尽管我已经索引了所有应该索引的列。为此类联接(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

故事的寓意

如果遇到性能问题(如此问题),请将中/长文本列保留在单独的表中。

6 回复 | 直到 15 年前

PatrikAkerstrand 16 年前

用户数据表中ColumnX的数据类型和用途是什么?应该注意的是,任何文本数据类型(即不包括char、varchar)都会强制在磁盘上创建任何临时表。现在,由于您正在执行一个没有条件、分组或排序的直接联接,所以它可能不需要任何临时表,除了聚合最终结果。

我认为如果您向我们展示您的索引是如何创建的,这也将非常有帮助。需要记住的一点是,尽管innodb将表的主键连接到每个索引,但myisam不会。这意味着如果索引列名称用like搜索它,但仍然想得到 身份证件 页组;那么查询仍然需要访问表以获取 身份证件 而不是从索引中检索它。

如果我理解你的意见 密码器 正确的做法是获取每个用户的页面组的名称。查询优化器希望为联接使用索引,但对于每个结果,它还需要访问表以检索页面组名。如果您的数据类型为名称不大于中等varchar,即没有文本,还可以创建一个索引(id,name),使查询能够直接从索引中提取名称。

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

我想,您正在运行的查询中排除了此列吗?
您还可以尝试将页面数据与页面“配置”分开,即它属于哪个组。你可能会有这样的东西:
- 页
  - 佩奇
  - 肺结核
- 帕吉达
  - 佩奇
  - 数据

希望这能使您更快地加入,因为页面中的任何列都不会占用太多空间。然后,当需要显示某个页面时,可以在pageid列上与page data表联接,以获取显示特定页面所需的数据。

Sander Marechal 16 年前

了解mysql对查询的作用的简单方法是让它向您解释查询。运行此命令并查看输出:

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

MySQL将告诉您它处理查询的顺序以及使用的索引。创建索引并不意味着MySQL实际使用索引。

也见 Optimizing queries with EXPLAIN

编辑

你的解释输出看起来不错。它对userdata表进行全表扫描,但这是正常的,因为您希望返回其中的所有行。优化这一点的最佳方法是重新考虑应用程序。您真的需要返回所有372K行吗?

Andomar 16 年前

我假设用户数据表非常大,不适合内存。MySQL必须从硬盘读取整个表,即使它只需要两个小列。

您可以通过定义一个包含查询所需全部内容的索引来消除扫描整个表的需要。这样,索引就不是一种方便搜索主表的方法,而是表本身的简写版本。MySQL只需要从磁盘读取速记表。

索引可能如下所示:

column1, pageid

这必须是非聚集的,否则它将成为大桌子的一部分,从而破坏它的目的。见 this page 关于mysql如何决定集群的索引的想法。最简单的方法似乎是确保pageid上有一个主键,它将被集群化,因此辅助column1+pageid索引将是非集群化的。

Alex Martelli 16 年前

一个可能的问题是,MySQL每个查询只使用一个索引,并且可能没有一个包含这些列的索引——或者MySQL的查询优化器没有选择它。什么? EXPLAIN SELECT &告诉你这里吗?

Tomalak 16 年前

我将从分解查询开始,找出是否有一个慢的部分和一个快的部分,或者两者都慢(抱歉,我不喜欢使用语法,所以我将继续使用):

SELECT 
  u.userdata, p.pageid
FROM
  userdata u
  INNER JOIN page p ON u.pageid = p.pageid

SELECT 
  p.pageid, g.pagegroupid
FROM
  page 
  INNER JOIN pagegroup g ON p.pagegroupid = g.pagegroupid

这给了你什么?把这些和 EXPLAIN EXTENDED 将提供其他提示。

Bjorn 16 年前

看起来您正在对上的所有行执行联接 userdata 然后尝试选择所有内容。这就是每一个 page 在一个 pagegroup 具有 用户数据 . 哪里是 WHERE 条款?没有 LIMIT ,您想要多少个结果?你为什么不把你的排数下来呢 用户数据 在你的行列 explain 结果,这将加快查询速度。呵呵。