如何优化这个 MySql 查询 - 连接三张表？

Question

如何优化这个 MySql 查询 - 连接三张表？

3

这个查询非常慢。它非常简单，使用了3个表，在JOIN和WHERE子句中所有列都被索引。我该如何优化我的查询或我的表格以加快查询速度？

这就是慢查询。运行时间需要15-20秒。

 SELECT
    user.id,
    user.name,
    user.key,
    user.secret,
    account.id,
    account.name,
    account.admin,
    setting.attribute,
    setting.value
  FROM        user
  INNER JOIN  account ON account.id       = user.account_id
  INNER JOIN  setting ON setting.user_id  = user.id
    AND setting.deleted = 0
  WHERE user.deleted = 0

很可能问题是由于在设置表上的连接引起的，因为下面的两个查询总共需要大约5秒。不过，5秒似乎仍然有点长？

  SELECT
    user.id,
    user.name,
    user.user_key,
    user.secret,
    account.id,
    account.name,
    account.admin
  FROM        user
  INNER JOIN  account ON account.user_id = user.id
  WHERE user.deleted = 0

  SELECT
    setting.user_id,
    setting.attribute,
    setting.value
  FROM setting
  WHERE setting.deleted = 0

慢查询的解释如下：

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

1, 'SIMPLE', 'user', 'ALL', 'PRIMARY,idx_id,idx_deleted', null, null, null, 600, 'Using where'
1, 'SIMPLE', 'account', 'eq_ref', 'PRIMARY', 'PRIMARY', '8', 'user.account_id', 1, null
1, 'SIMPLE', 'setting', 'ref', 'attribute_version_unique,idx_user_id,indx_deleted', 'attribute_version_unique', '8', 'user.id', 35, 'Using where'

模式：

CREATE TABLE user
(
    id BIGINT(20) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name VARCHAR(45) NOT NULL,
    user_key VARCHAR(45) NOT NULL,
    secret VARCHAR(16),
    account_id BIGINT(20) unsigned NOT NULL,
    name VARCHAR(40) NOT NULL,
    demo TINYINT(1) DEFAULT '0' NOT NULL,
    details VARCHAR(4000),
    date_created DATETIME NOT NULL,
    date_modified DATETIME NOT NULL,
    deleted TINYINT(1) DEFAULT '0' NOT NULL
);
CREATE INDEX idx_date_modified ON user (date_modified);
CREATE INDEX idx_deleted ON user (deleted);
CREATE INDEX idx_id ON pub_application (id);
CREATE UNIQUE INDEX idx_name_unique ON user (user_key);

CREATE TABLE account
(
    id BIGINT(20) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    display_name VARCHAR(100),
    admin TINYINT(1) DEFAULT '0' NOT NULL,
    visibility VARCHAR(15) DEFAULT 'public',
    cost DOUBLE,
    monthly_fee VARCHAR(300),
    date_created DATETIME NOT NULL,
    date_modified DATETIME NOT NULL,
    deleted TINYINT(1) DEFAULT '0'
);
CREATE INDEX idx_date_modified ON account (date_modified);

CREATE TABLE setting
(
    id BIGINT(20) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    user_id BIGINT(20) unsigned NOT NULL,
    attribute VARCHAR(45) NOT NULL,
    value VARCHAR(4000),
    date_created DATETIME NOT NULL,
    date_modified DATETIME NOT NULL,
    deleted TINYINT(1) DEFAULT '0' NOT NULL
);
CREATE UNIQUE INDEX attribute_version_unique ON setting (user_id, attribute);
CREATE INDEX idx_user_id ON setting (user_id);
CREATE INDEX idx_date_modified ON setting (date_modified);
CREATE INDEX indx_deleted ON setting (deleted);

- ab11

这是一个关于查询性能的良好问题。干得好！ - O. Jones

你的users表中只有600行数据，而且所有的连接都使用了索引，所以查询应该很快。也许问题出在服务器上？ - Artem Ilchenko

我不是很确定，但我找不到users表中account_id的索引，而且我理解它是与account表关联的外键。在连接ON account.id = user.account_id时，它可能会减慢整个查询速度。 - Mariusz Szurgot

1

我会问自己，“如果我有一百万个……”，无论我正在处理什么。如果你有一百万个用户设置，你在问题中的方法将成为一个很大的瓶颈。每分钟160K行不算慢。但它比交互式慢。15秒现在可能适合你，但随着系统的增长，情况只会变得更糟。 - O. Jones

有道理。感谢反馈/建议。 - ab11

显示剩余2条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- O. Jones · Accepted Answer

尊敬的用户，您遇到了一个常见的反模式。通常索引“所有列”是一个无用的举动。MySQL（截至2016年末）在满足查询时最多只能利用一张表的索引。因此，额外的索引可能对任何查询都没有帮助，并且肯定会增加INSERT和UPDATE操作的开销。

这个查询可以通过一些专门设计的复合覆盖索引来改进。

请在您的用户表上尝试使用此索引。它是一个覆盖索引：旨在包含满足查询所需的所有列。它按照与您的WHERE子句相匹配的顺序组织。

CREATE INDEX idx_user_account_setting 
          ON user (deleted , account_id, id, name, key, secret);

这个覆盖索引可能对你的setting表有所帮助。

CREATE INDEX idx_setting_user 
          ON setting (user_id, deleted , attribute, value);

如果第一个方法不起作用，也可以尝试这个方法：交换前两列的顺序。

CREATE INDEX idx_setting_user_alt
          ON setting (deleted, user_id, attribute, value);

最后尝试一下账户。

CREATE INDEX idx_account_user 
          ON account (id, name, admin);

请注意，如果这些建议对您有所帮助，请留下简短的评论告诉我们它们有多大的帮助。

阅读此内容：http://use-the-index-luke.com/。