MySQL 查询速度优化

Question

我有以下代码，单独运行非常快，但是当我对许多entity_id执行此操作时，查询开始变得越来越慢（循环是PHP foreach）。例如，此查询仅需要0.078秒，但在循环中的不同实体上执行相同的查询需要长达2.1秒。 查询似乎随着我放入循环中的实体越来越多而变得越来越慢。为什么？如何改进/优化查询？

foreach($entity_ids as $entity_id) {
    SELECT COUNT(*) as prev, DATE_FORMAT(`created`, '%Y%m%d') AS date_group 
    FROM articles_entities 
    WHERE entity_id = '$entity_id' 
    AND `created` >= DATE_SUB(CURDATE(), INTERVAL 10 DAY) 
    GROUP BY date_group

    // store result
}

我有以下表结构：

CREATE TABLE `articles_entities` (
  `id` CHAR(36) NOT NULL,
  `article_id` CHAR(36) NOT NULL,
  `entity_id` CHAR(36) NOT NULL,
  `created` DATETIME DEFAULT NULL,
  `modified` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `article_id` (`article_id`),
  KEY `entity_id` (`entity_id`),
  KEY `created` (`created`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

- Lizard

Answer 1

我猜你有一个ID的数组，想要从你的表中取出记录(按照你的语句控制的方式)，其中ID字段匹配数组值之一。

与其在PHP中使用循环运行多个SQL语句，最好的做法是构建一个主语句，然后使用PHP处理结果。你可以使用SQL IN语句来实现这一点：

    // where $entity_ids is an array eg 1,2,3,4,5

    $sql="SELECT entity_id AS 'alt_entity_id', COUNT(entity_id) as prev, DATE_FORMAT(`created`, '%Y%m%d') AS 'date_group'  
    FROM articles_entities 
    WHERE entity_id IN ".implode(",",$entity_ids)." 
    AND `created` >= DATE_SUB(CURDATE(), INTERVAL 10 DAY) 
    GROUP BY date_group, entity_id";
    // you may wish to revese the group fields, as you require, you may also wish to change the count field to date_group, depending on what you wish to be counted

这将对你所拥有的所有id值进行一次原始查询，按日期和传递的id值进行分组。然后，您可以使用PHP从返回的结果集中过滤出特定id的结果。

这比循环执行查询产生的开销要高效得多。

你返回的结果集将类似于：

 entity_id | count(entity_id) | date_group
 ----------|------------------|------------
     1     |        3         | 2010-04-01
     1     |        3         | 2010-03-01
     1     |        3         | 2010-02-01
     2     |        2         | 2010-01-01
     2     |        2         | 2010-02-01
     3     |        1         | 2010-06-01
     4     |        2         | 2010-06-01
     4     |        2         | 2010-02-01

- SW4

---

答案中唯一缺少的是选择实体ID AS alt_entity_id，因为我无法确定每个实体是哪一个（请注意，我不能只选择entity_id，因为它会给出错误的结果）。我还必须交换分组。如果您更新您的答案，我将接受它。 - Lizard

Answer 2

我不知道你从哪里获取循环中的实体值，但在循环内执行此查询总是会带来巨大的性能开销。如果你是从先前的 SQL 查询中获取 entity_ids，那么重构你的 SQL 以将初始查询与循环查询连接起来可能更有意义，这样你就可以在单个 SQL 查询中返回所有所需的数据。

- Mark Baker

---

$entity_ids来自哪里？ - Mark Baker

我首先从同一张表中选择符合特定条件（不同的日期范围）的文章，并返回其中的实体。 - Lizard

@Lizard - 我的意思是，应该可以将第一个选择与您循环选择的内容结合起来：如果您发布两个查询（以及任何其他相关的表结构），我们可能能够制定一个单一的查询，这将意味着您不必在循环中执行一系列查询。 - Mark Baker

Answer 3

将所有的id存入一个数组中，将其连接起来形成一个字符串，并使用“where in”以优化的方式获取详细信息。

$enitityIDS = array();
    foreach($entity_ids as $entity_id) {
       $enitityIDS[]=$entity_id;
    }
    $entityIDString = join(",",$enitityIDS);

然后执行

SELECT COUNT(*) as prev, DATE_FORMAT(`created`, '%Y%m%d') AS date_group 
    FROM articles_entities 
    WHERE entity_id in (".$entityIDString.")
    AND `created` >= DATE_SUB(CURDATE(), INTERVAL 10 DAY) 
    GROUP BY date_group, entity_id

最佳方式

- Anish Joseph

---

不要忘记你还需要按id字段对查询进行分组，否则IN子句的结果是无法区分的。 - SW4

Answer 4

你大概要处理多少个实体？

你能否将所需的实体插入到单独的表中并进行连接，而不是进行多个查询？

- Jaydee