mysql - 我怎样才能让这个查询运行得更快？

Question

我怎样才能让这个查询运行得更快......？

SELECT    account_id,           account_name,           account_update,           account_sold,           account_mds,           ftp_url,                   ftp_livestatus,           number_digits,           number_cw,          client_name,           ppc_status,           user_name FROM              Accounts,          FTPDetails,          SiteNumbers,          Clients,          PPC,          Users WHERE    Accounts.account_id = FTPDetails.ftp_accountid AND      Accounts.account_id = SiteNumbers.number_accountid AND      Accounts.account_client = Clients.client_id     AND      Accounts.account_id = PPC.ppc_accountid AND      Accounts.account_designer = Users.user_id   AND      Accounts.account_active = 'active' AND      FTPDetails.ftp_active = 'active' AND      SiteNumbers.number_active = 'active' AND      Clients.client_active = 'active'    AND      PPC.ppc_active = 'active'   AND      Users.user_active = 'active' ORDER BY          Accounts.account_update DESC

提前致谢:)

EXPLAIN 查询结果:

我真的没有设置任何外键...我试图避免对数据库进行更改，因为很快就必须进行全面检修。

只有主键是每个表的id e.g. account_id, ftp_id, ppc_id ...

Answer 1

索引

• 您需要 - 至少 - JOIN 条件中使用的每个字段的索引。

• 出现在 WHERE 或 GROUP BY 或 ORDER BY 子句中的字段索引在大多数情况下也很有用.

• 当在一个表中，两个或多个字段用于 JOIns(或 WHERE 或 GROUP BY 或 ORDER BY)时，这些(两个或更多)字段的复合(组合)索引可能比单独的索引更好.例如，在 SiteNumbers 表中，可能的索引是复合 (number_accountid, number_active) 或 (number_active, number_accountid)。

• bool 值(开/关、事件/不活动)字段中的条件有时会减慢查询速度(因为索引不是选择性的，因此不是很有用)。在这种情况下，重组(父亲规范化)表格是一种选择，但您可能可以避免增加的复杂性。

---

除了通常的建议(检查 EXPLAIN 计划、在需要的地方添加索引、测试查询的变体)，

我注意到在您的查询中有部分笛卡尔积。表 Accounts 与三个表 FTPDetails、SiteNumbers 和 PPC 具有一对多关系。这会产生这样的效果，例如，如果您有 1000 个帐户，并且每个帐户都与 10 个 FTPDetails、20 个 SiteNumbers 和 3 个 PPC 相关，则查询将为每个帐户返回 600 行(10x20x3 的乘积)。总共 60 万行，其中有许多数据是重复的。

您可以改为将查询拆分为三个加一个用于基本数据(帐户和其余表)。这样，只有 34K 行数据(长度更小)将被传输:

Accounts JOIN Clients JOIN Users 
  (with all fields needed from these tables)
  1K rows

Accounts JOIN FTPDetails
  (with Accounts.account_id and all fields from FTPDetails)
  10K rows

Accounts JOIN SiteNumbers
  (with Accounts.account_id and all fields from SiteNumbers)
  20K rows

Accounts JOIN PPC
  (with Accounts.account_id and all fields from PPC)
  3K rows

然后在客户端使用来自 4 个查询的数据来显示组合信息。

---

---

我会添加以下索引:

Table Accounts
  index on (account_designer)
  index on (account_client)
  index on (account_active, account_id)
  index on (account_update)

Table FTPDetails
  index on (ftp_active, ftp_accountid)

Table SiteNumbers
  index on (number_active, number_accountid)

Table PPC
  index on (ppc_active, ppc_accountid)