如何优化这个MySql查询-连接3个表?

This query is very slow. It is pretty simple and the 3 tables used are indexed on all columns in JOIN and WHERE clauses. How can I optimize my query, or my tables for this query?

这个查询非常缓慢。它非常简单，使用的3个表在JOIN和WHERE子句中的所有列上都建立了索引。我如何优化我的查询或我的表来查询这个查询?

This is the slow query. It takes 15-20 seconds to run.

这是一个缓慢的查询。跑步需要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

It is likely issue is caused by join on the setting table, because the below two queries take about 5 seconds total. Although, 5 seconds still seems a little long?

很可能是由于设置表上的join引起的问题，因为下面两个查询总共花费了大约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

The explain for the slow query:

缓慢查询的解释:

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'

The schema:

模式:

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);

1 个解决方案

#1

With respect, you've stumbled across a common antipattern. Indexing "all columns" ordinarily is a useless move. MySQL (as of late 2016) can exploit at most one index per table when satisfying a query. So the extra indexes are likely to help no queries, and definitely add overhead on INSERT and UPDATE operations.

出于尊重，您偶然发现了一个常见的反模式。对“所有列”进行索引通常是一个无用的动作。MySQL(截至2016年年底)在满足查询时最多只能使用一个索引。因此，额外的索引很可能不会帮助查询，并且肯定会增加插入和更新操作的开销。

This query might be improved by some purpose-designed compound covering indexes.

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

Try this index on your user table. It's a covering index: intended to contain all the columns necessary to satisfy the query. It's organized in an order that matches your WHERE clause.

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

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

This covering index might help on your setting table

这个覆盖索引可能对设置表有所帮助

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

Also try this one, switching the order of the first two columns, if the first one doesn't help.

也可以试试这个，如果前两列的顺序没有帮助的话。

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

Finally try this one on account.

最后试试这个。

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

Please, if these suggestions help leave a brief comment telling how much they helped.

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

Read this. http://use-the-index-luke.com/

读这篇文章。http://use-the-index-luke.com/

#1