这个查询非常慢。它非常简单,使用了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);
users
表中只有600行数据,而且所有的连接都使用了索引,所以查询应该很快。也许问题出在服务器上? - Artem Ilchenkousers
表中account_id
的索引,而且我理解它是与account
表关联的外键。在连接ON account.id = user.account_id
时,它可能会减慢整个查询速度。 - Mariusz Szurgot