大型数据库的查询优化

Question

嗨，我需要帮助，以优化查询大型数据库记录超过100万。当前查询的执行时间为27-30秒。

SELECT SQL_CALC_FOUND_ROWS
candidate.candidate_id AS candidateID,
candidate.candidate_id AS exportID,
candidate.is_hot AS isHot,
candidate.date_modified AS dateModifiedSort,
candidate.date_created AS dateCreatedSort,
candidate.first_name AS firstName,
candidate.last_name AS lastName,
candidate.city AS city,
candidate.state AS state,
candidate.key_skills AS keySkills,
owner_user.first_name AS ownerFirstName,
owner_user.last_name AS ownerLastName,
CONCAT(owner_user.last_name,
        owner_user.first_name) AS ownerSort,
DATE_FORMAT(candidate.date_created, '%m-%d-%y') AS dateCreated,
DATE_FORMAT(candidate.date_modified, '%m-%d-%y') AS dateModified,
candidate.email2 AS email2 FROM
candidate
    LEFT JOIN
user AS owner_user ON candidate.owner = owner_user.user_id
    LEFT JOIN
saved_list_entry ON saved_list_entry.data_item_type = 100
    AND saved_list_entry.data_item_id = candidate.candidate_id WHERE
is_active = 1 GROUP BY candidate.candidate_id ORDER BY    dateModifiedSort 
DESC LIMIT 0 , 15

是否有任何方法可以缩短查询的执行时间。我还在表中添加了索引，但它不太好用。

​

​

Answer 1

我在下面的查询中更改了表别名，使用它必须解决您的问题

SELECT SQL_CALC_FOUND_ROWS
candidate.candidate_id AS candidateID,
candidate.candidate_id AS exportID,
candidate.is_hot AS isHot,
candidate.date_modified AS dateModifiedSort,
candidate.date_created AS dateCreatedSort,
candidate.first_name AS firstName,
candidate.last_name AS lastName,
candidate.city AS city,
candidate.state AS state,
candidate.key_skills AS keySkills,
user.first_name AS ownerFirstName,
user.last_name AS ownerLastName,
CONCAT(user.last_name,
        user.first_name) AS ownerSort,
DATE_FORMAT(candidate.date_created, '%m-%d-%y') AS dateCreated,
DATE_FORMAT(candidate.date_modified, '%m-%d-%y') AS dateModified,
candidate.email2 AS email2 FROM
candidate
    LEFT JOIN
user ON candidate.owner = user.user_id
    LEFT JOIN
saved_list_entry ON saved_list_entry.data_item_type = 100
    AND saved_list_entry.data_item_id = candidate.candidate_id WHERE
is_active = 1 GROUP BY candidate.candidate_id ORDER BY    dateModifiedSort 
DESC LIMIT 0 , 15

使用以下查询为联接条件创建索引

create index index_user user(user_id);

create index index_saved_list_entry saved_list_entry(data_item_type,data_item_id);

create index index_candidate candidate(is_active,candidate_id,dateModifiedSort);

Answer 2

您使用的是查询模式

     SELECT a vast bunch of stuff
       FROM a complex assembly of JOIN operations
      ORDER BY some variable DESC
      LIMIT 0,small number

这在本质上是低效的:为了满足您的查询，MySQL服务器必须构造一个庞大的结果集，然后它必须对整个事情进行排序，然后取前十五行，然后丢弃其余的。

为了提高效率，你需要整理更少的东西。这里有个方法可以做到。看起来你想要找到最近修改过的十五位候选人。此查询将相当便宜地检索这些候选人的ID。它利用了你的一个索引。

                   SELECT candidate_id
                     FROM candidate
                    ORDER BY date_modified DESC
                    LIMIT 0, 15

然后，您可以在主查询中使用它作为子查询。增加这样的一句：

  WHERE candidate.candidate_id IN (
                   SELECT candidate_id
                     FROM candidate
                    ORDER BY date_modified DESC
                    LIMIT 0, 15)

在适当的位置查询。

还请注意，您使用的是非标准和潜在有害的MySQL特定扩展到组。您的查询工作正常，但如果候选人有多个所有者，则在随机选择后只会返回一个。

最后，您似乎已经在大表中的许多列上放置了单列索引。这是一个臭名昭著的SQL ：所有这些索引都会减慢插入和更新操作，而且其中大多数索引可能对加速查询没有任何作用。当然，对于这个查询，唯一有用的索引是date_modified上的索引和主键。

许多复杂的查询最好使用特定的多列索引。一堆单列索引对这样的查询没有帮助。

Answer 3

首先，一个候选人，我怀疑总是一个条目的ID，所以你为什么要做一个组是超出我，这很可能是可以删除和改进一点。

其次，您正在对"saved_list_entry“表执行左联接，但实际上没有从其中提取任何列，因此这可能会被完全删除。

第三，从对小组的考虑出发，不再适用，我建议更新您的索引如下：

table             index
CANDIDATE         ( is_active, date_modified, candidate_id, owner )
user              ( user_id )
saved_list_entry  ( data_item_id, data_item_type )

由于您的订单是按降序修改的日期，将它放在is_active的第二个位置(其中条件)，它将快速地遍历您的前15个。然而，您的SQL_CALC_FOUND_ROWS仍然需要通过所有其他排位赛，但结果集将按索引预先排序以匹配。

SELECT SQL_CALC_FOUND_ROWS
      c.candidate_id AS candidateID,
      c.candidate_id AS exportID,
      c.is_hot AS isHot,
      c.date_modified AS dateModifiedSort,
      c.date_created AS dateCreatedSort,
      c.first_name AS firstName,
      c.last_name AS lastName,
      c.city AS city,
      c.state AS state,
      c.key_skills AS keySkills,
      u.first_name AS ownerFirstName,
      u.last_name AS ownerLastName,
      CONCAT(u.last_name, u.first_name) AS ownerSort,
      DATE_FORMAT(c.date_created, '%m-%d-%y') AS dateCreated,
      DATE_FORMAT(c.date_modified, '%m-%d-%y') AS dateModified,
      c.email2 AS email2 
   FROM
      candidate c
         LEFT JOIN user u
            ON c.owner = u.user_id
         LEFT JOIN saved_list_entry s
            ON c.candidate_id = s.data_item_id
            AND s.data_item_type = 100
   WHERE
      c.is_active = 1 
   GROUP BY 
      c.candidate_id 
   ORDER BY    
      c.date_modified DESC 
   LIMIT 
      0, 15