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

有没有办法加快这个查询的速度?

mysql sql

Ethan · 技术社区 · 15 年前

这个查询真的很慢。需要9到10秒。。。

SELECT DISTINCT a.*
FROM addresses a
LEFT JOIN contacts c
ON c.id = a.contact_id
LEFT JOIN organizations o
ON o.id = a.organization_id
ORDER BY c.last_name, c.first_name, o.name
LIMIT 0, 24

如果我把这个评论删掉 ORDER BY 子句查询运行得快得多--大约5毫秒。但是我需要以支持搜索结果的分页。用户需要按联系人和组织对地址进行排序。

表结构

addresses
---------
id int NOT NULL
contact_id int       # could be NULL
organization_id int  # could be NULL

contacts
--------
id int NOT NULL
first_name varchar(255)
last_name varchar(255)

organizations
-------------
id int NOT NULL
name varchar(255)

它们都是InnoDB表。

联系人表中有以下索引:

  KEY `idx_contacts_first_name` (`first_name`),
  KEY `idx_contacts_last_name` (`last_name`),
  KEY `idx_contacts_first_name_last_name` (`first_name`,`last_name`)

在组织表上:

  KEY `idx_organization_name` (`name`)

数据量

Addresses:     22,271
Contacts:      17,906
Organizations:  8,246

描述输出

mysql> DESCRIBE
    -> SELECT DISTINCT a.*
    -> FROM addresses a
    -> LEFT JOIN contacts c
    -> ON c.id = a.contact_id
    -> LEFT JOIN organizations o
    -> ON o.id = a.organization_id
    -> ORDER BY c.last_name, c.first_name, o.name
    -> LIMIT 0, 24;
+----+-------------+-------+--------+---------------+---------+---------+--------------------------------------------+-------+---------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                                        | rows  | Extra                           |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------------------------+-------+---------------------------------+
|  1 | SIMPLE      | a     | ALL    | NULL          | NULL    | NULL    | NULL                                       | 22387 | Using temporary; Using filesort | 
|  1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY | 4       | contactdb_v2_development.a.contact_id      |     1 | Distinct                        | 
|  1 | SIMPLE      | o     | eq_ref | PRIMARY       | PRIMARY | 4       | contactdb_v2_development.a.organization_id |     1 | Distinct                        | 
+----+-------------+-------+--------+---------------+---------+---------+--------------------------------------------+-------+---------------------------------+
3 rows in set (0.00 sec)

5 回复 | 直到 15 年前

sleske 15 年前

我尝试了你的例子,使用了相似数量的数据,在我的低端笔记本电脑(奔腾M 1,7 GHz)上,查询只需不到一秒钟的时间(第一次运行时,以后运行的时间甚至更少)。

你是不是碰巧忘记了id栏上的PK?你没提,所以只要问一下。。。如果您忘记了这一点,性能显然会很糟糕——更不用说每个DBA都会在没有PK的情况下畏缩。

否则,请尝试以下操作:

DESCRIBE <your query>

这将为您提供MySQL的查询计划。张贴(编辑你的问题),它应该更清楚什么花了这么长时间。

进一步考虑:

查询的性能总是有问题,因为您要求数据库读取和排序 所有地址 并展示它们。订单意味着它必须在归还任何东西之前阅读所有内容,所以它总是很慢。这样放置整个数据库有什么意义?用户会翻阅几千条记录吗?

Mike Burton 15 年前

如果您在服务器端没有太多的资源限制,并且这件事不会扩展太多,那么您没有太多的数据,因此您可以简单地在该级别进行排序和分页。

Matt Wrock 15 年前

idx_contacts_last_name_first_name last_name , first_name )

Kenny Evitt 15 年前

尝试将SQL更改为以下内容:

SELECT a.column1, a.column2, ...
FROM addresses a
LEFT JOIN contacts c
ON c.id = a.contact_id
LEFT JOIN organizations o
ON o.id = a.organization_id
GROUP BY a.column1, a.column2, ...
ORDER BY c.last_name, c.first_name, o.name
LIMIT 0, 24

我找到了 GROUP BY 快得多 DISTINCT 总的来说,虽然我不知道为什么会这样。

Ewan Todd 15 年前

让我看看。

地址:22271
联系人:17906

地址一个左连接联系人c给出了大约20000*20000~40000000个比较结果

LEFT JOIN organizations为大约20000个结果提供了大约10000*20000~200万个比较

比我们需要的多一点(比如,大约4倍):

SELECT * FROM contacts ORDER BY last_name, first_name LIMIT 100

然后将这些加入到他们的地址中,保留前100名左右

     SELECT a.* 
       FROM (SELECT * FROM contacts ORDER BY last_name, first_name LIMIT 0, 100) AS c
  LEFT JOIN addresses a
         ON c.id = a.contact_id
      LIMIT 0, 100

然后加入这些组织

 SELECT * 
   FROM (
        SELECT * 
          FROM (SELECT * FROM contacts ORDER BY last_name, first_name LIMIT 0, 100) AS c
     LEFT JOIN addresses a
            ON c.id = a.contact_id
         LIMIT 0, 100
         ) AS ca LEFT JOIN organizations o
      ON o.id = ca.organization_id
ORDER BY ca.last_name, ca.first_name, o.name
   LIMIT 0, 24

我确信语法是错误的,但我同样确信,减少每个阶段的结果集的原则指向了一种有指导意义的方法。我可能也做了一些折衷,结果接近10秒的答案,但要快得多。