代码之家 › 专栏 › 技术社区 › Hailwood

将一个查询拆分为四个以避免大规模联接?

optimization mysql php

Hailwood · 技术社区 · 14 年前

所以我有一个这样的查询:

SELECT col1, col2, col3 ...
FROM action_6_members m
LEFT JOIN action_6_5pts f ON f.member_id = m.id
LEFT JOIN action_6_10pts t ON t.member_id = m.id
LEFT JOIN action_6_weekly w ON w.member_id = m.id
WHERE `draw_id` = '1' ORDER BY m.id DESC LIMIT 0, 20;

现在这是一个大规模的加入(350万*4万*2万)

所以我的想法是:

做 SELECT * FROM action_6_members WHERE 拖曳线 = '1' ORDER BY id DESC LIMIT 0, 20;

然后使用PHP构建 $in = "IN(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)" ;

然后运行
select * from action_6_5pts where member_id in $in
select * from action_6_10pts where member_id in $in
select * from action_6_weekly where member_id in $in

然后用php把它们都弄脏,

这意味着,尽管我使用四个不同的查询,但我只从每个查询中选择20行,而不是对所有查询进行连接。

我会注意到显著的绩效奖金吗?

更新
所以,普遍的共识是,“不要这样做!”

以下是应用程序的概述

它接收到一个代码,

代码可以是5pt、10pt或每周代码,

这三种代码类型都在单独的表中。这三个表有代码和成员ID

成员ID链接到操作成员表中的ID。

当一个代码被声明时,数据被填充在action_6_成员表中。

然后,该成员的ID将填入所声明代码的表中。

上面的查询选择前二十个成员。

所以我的问题是。

我能做些什么来改进这个?

目前,查询完成前,所有内容都已超时。

行动小组成员

CREATE TABLE `action_6_members` (
  `id` int(11) NOT NULL auto_increment,
  `draw_id` int(11) NOT NULL,
  `mobile` varchar(255) NOT NULL,
  `fly_buys` varchar(255) NOT NULL,
  `signup_date` datetime NOT NULL,
  `club` int(11) NOT NULL default '0' COMMENT '1 = yes, 2 = no',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1337 DEFAULT CHARSET=latin1

动作\ 5分和10分

CREATE TABLE `action_6_5pts` (
  `code` varchar(255) NOT NULL,
  `member_id` int(11) NOT NULL,
  PRIMARY KEY  (`code`),
  KEY `member_id` (`member_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

ActoNo66-周刊

CREATE TABLE `action_6_weekly` (
  `id` int(11) NOT NULL auto_increment,
  `code` varchar(255) NOT NULL,
  `member_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `member_id` (`member_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3250001 DEFAULT CHARSET=latin1

更新2:解释查询

id select_type table type possible_keys key       key_len ref  rows   Extra  
1  SIMPLE      m     ALL  \N            \N        \N      \N   1390   Using temporary; Using filesort  
1  SIMPLE      f     ALL  member_id     \N      \N      \N   36000  
1  SIMPLE      t     ALL  member_id     \N      \N      \N   18000  Using where  
1  SIMPLE      w     ref  member_id     member_id 4    m.id 525820 Using where

刚刚通过了: 来自DB 7.26、4.60、2.45的最新负载数据

1.0是正常的最大负载…上面的任何内容都意味着它必须“突发”并调用其他进程来处理。也就是说,7.26意味着负载是刀片服务器最大负载的7倍,因此必须求助于其他人。

所以现在这个查询不仅仅是一个怪物,它把吃怪物当作零食…

8 回复 | 直到 14 年前

Nicholas Knight 14 年前

一般来说,如果您的SQL查询能够完全建模您想要做的事情,那么它可能比将它拆分成用PHP(或任何其他语言)粘合在一起的片段更快。 在一定范围内 .

这些界限是:

在MySQL中不应该有奇怪的病理行为。
您必须对所有必要的列拥有合理的索引。
没有(或没有) 可能的 )您只能在PHP中合理地检测/处理这种情况,在这种情况下,您需要中途中止查询。
您的结果集在病理学上并不庞大(例如,它适合于内存,并且不超过 max_allowed_packet 在里面 my.cnf )

现在,这并不能解决您的SQL(或在PHP中建议的替代实现)是 最优的 对于你正在做的,但只有当你的应用程序做了什么以及你实际想要达到的终点的更多信息时,你才能解决这个问题。可能没事,也可能不。

在快速浏览您对表结构的更新时,没有任何东西会跳到我身上,因为这很可能是导致性能问题的原因,但是:

不要使用Myisam,除非你已经确定你需要它。InnoDB是您的朋友,尤其是当表有相当多的写流量时。Myisam的全桌锁真的会咬你。拥有用于引用完整性的外键也不错。
action_6_weekly 有 id 作为 PRIMARY KEY 和A UNIQUE KEY 关于… 身份证件 . 这是多余的。 主键 实际上是 唯一密钥 ,您不需要创建单独的 唯一密钥 .
产量 EXPLAIN 在你的问题上会很有趣。

OMG Ponies 14 年前

应用程序和数据库之间的多次往返?不,与单个查询相比,这不会提供性能奖励。

Borealid 14 年前

您不必使用PHP来完成这项工作,您可以使用子查询在一个查询中完成这项工作,也可以使用多查询存储过程。

要查看哪一个更快,请对它们进行基准测试。

Cfreak 14 年前

奇怪的是,我会不同意这个共识,至少在某种程度上是这样。

首先,你永远不应该使用左连接。这很诱人,但几乎总是个坏主意。我假设在您的案例中,action_6_5pts、action_6_10pts和action_weekly表可能不包含所有成员ID。(我在猜测您的数据,所以如果每个表都保证包含所有成员ID,那么从查询中去掉左键,您就可以了。)

我怀疑有一个更好的方法可以让你的数据在一开始。通常,最好将相同类型的数据合并到一个表中。我不想猜测你的数据,所以我给你一个伪例子。我见过很多时候,人们会将相似的数据分为多个表(较小的表更好吗?)不总是这样。例如,如果您正在构建一个发票系统,那么可能会考虑将发票按月拆分为单独的表。所以你创建发票2010年1月,发票2010年2月…等等,但是如果你想搜索呢?同一个客户可能不是在所有的几个月,所以很难得到一个只有该客户的列表,而不使用左连接。讨厌。我们不喜欢左进!太慢了!

更好的方法是使用一个带有日期的发票表(索引!)以及每个客户ID。任何连接都保证找到发票,除非客户没有发票(这无关紧要)。

现在,在您的情况下,也许您可以在一个表中制作5分和10分的标记,每周是日期吗?我在做假设,不知道更多,很难给你“正确”的答案。

现在我说我不同意这个共识。如果不更改数据,通常情况下,如果有一个非常大的表(如您所说),那么使用in语句将查询拆分为4个比左联接更好。如果您想加快速度,可以使用union将所有4个合并为1个。这应该比左连接还要快。

你也可以很容易地证明这一点。接受您的查询并将关键字explain放在它前面,然后直接在mysql上执行它(使用以下工具之一:命令行、mysql-gui甚至phpmyadmin)。这将给您一个它计划如何将表连接在一起的细目。

对于这个答案,输出的解释太长,但一般来说,每一行输出都会告诉您查询将联接多少行。越小越好。它还将告诉您它将如何加入。”如果可能的话,您希望避免使用“临时”或“使用文件排序”(尽管如果您排序,它会出现,所以请做好准备)。还有一个列,用于说明行将使用什么键联接。如果该列为空,则应尝试创建一个索引以使其更好地工作。

希望有帮助!祝你好运!

oezi 14 年前

不要那样做。数据库在连接表和选择相关行方面速度非常快,就像执行大量的单个查询一样快。

staticsan 14 年前

在您尝试之前,您不会知道这种方法是否会或在多大程度上提高性能。根据我的经验,将这类查询更改为离散查询并不是可以预测的。您要寻找的是MySQL中的一个转折点,在这里创建超过一定大小的内部表是一个杀手。一旦你知道那一点在哪里 在您的安装中 然后,您可以玩查询拼接和后处理的游戏。

Benjamin Anderson 14 年前

您应该在联接中使用IN子句,而不是使用极限。限制在联接之后运行,而不是作为查询的一部分。

Dave Rix 14 年前

我可能疯了,但我看不到桌子上的索引 action_6_members 对于你正在筛选的字段 draw_id 在原始查询中。

这意味着查询必须扫描 行动小组成员 然后与其他人会合。

向添加索引 拖曳线 专栏可能会有所帮助。

您可以创建的组合键( 拖曳线 , id 但是如果你不从 行动小组成员 表(如果不是,则可以使用多字段索引而不是读取数据表)

希望有帮助…