MySQL 查询优化(运行 7.6 秒)

Question

这是我的查询:

SELECT `users`.`id`, `users`.`username`, `users`.`profile_picture_url`, `users`.`credits_offered`, `users`.`country_id`, `users`.`user_level`, `users`.`credits`
FROM `users`
LEFT JOIN `history` ON `history`.`actor_id` = 100 AND `history`.`receiver_id` = `users`.`id`
LEFT JOIN `blocked` ON `blocked`.`actor_id` = 100 AND `blocked`.`receiver_id` = `users`.`id`
WHERE `history`.`receiver_id` IS NULL
AND `blocked`.`receiver_id` IS NULL
AND `users`.`instagram_active` = 1
AND `users`.`banned` = 0
AND `users`.`shown_to_others` = 1
AND `users`.`featured_user` = 0
AND `users`.`id` IN(
SELECT `user_id` FROM `interests` WHERE `interest` IN(
SELECT `interest` FROM `interests` WHERE `user_id` = 100 AND `active` = 1))
AND `users`.`id` <> 100
AND `users`.`featured_user` = 0
ORDER BY (`users`.`credits` >= `users`.`credits_offered`) DESC, `users`.`credits_offered` DESC, `users`.`credits` DESC
LIMIT 0, 25

用户表:

CREATE TABLE `users` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `instagram_id` int(11) NOT NULL,
 `username` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `bio` text COLLATE utf8_unicode_ci,
 `website` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `profile_picture_url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `full_name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
 `media_count` int(11) unsigned NOT NULL,
 `followed_by_count` int(11) unsigned NOT NULL,
 `follows_count` int(11) unsigned NOT NULL,
 `last_updated` datetime NOT NULL,
 `last_updated_instagram` datetime NOT NULL,
 `instagram_active` tinyint(1) DEFAULT NULL,
 `last_login` datetime NOT NULL,
 `inserted_on` datetime NOT NULL,
 `banned` tinyint(1) NOT NULL DEFAULT '0',
 `banned_reason` text COLLATE utf8_unicode_ci,
 `oauth_token` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
 `user_level` tinyint(4) NOT NULL,
 `shown_to_others` tinyint(1) NOT NULL DEFAULT '1',
 `credits_offered` tinyint(1) unsigned NOT NULL DEFAULT '2',
 `active` tinyint(1) NOT NULL DEFAULT '1',
 `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `registered_ip` varchar(17) COLLATE utf8_unicode_ci DEFAULT NULL,
 `credits` int(11) NOT NULL,
 `email_notifications` tinyint(1) NOT NULL DEFAULT '1',
 `todays_followers` int(11) NOT NULL DEFAULT '0',
 `todays_followers_hour` int(11) NOT NULL,
 `total_followers` int(11) NOT NULL,
 `credits_yesterday` int(11) NOT NULL,
 `email_is_verified` tinyint(1) NOT NULL DEFAULT '0',
 `email_announcements` tinyint(1) NOT NULL DEFAULT '1',
 `email_credits` tinyint(1) NOT NULL DEFAULT '1',
 `verification_code` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
 `country_id` bigint(20) unsigned DEFAULT NULL,
 `browser_info_id` bigint(20) unsigned DEFAULT NULL,
 `featured_user` tinyint(1) NOT NULL DEFAULT '0',
 `emailed_credits` tinyint(1) NOT NULL DEFAULT '0',
 `followers_same_interests` tinyint(1) NOT NULL DEFAULT '0',
 UNIQUE KEY `id` (`id`),
 UNIQUE KEY `instagram_id` (`instagram_id`),
 KEY `country_id` (`country_id`),
 KEY `browser_info_id` (`browser_info_id`),
 KEY `username` (`username`,`instagram_active`,`banned`),
 CONSTRAINT `users_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
 CONSTRAINT `users_ibfk_2` FOREIGN KEY (`browser_info_id`) REFERENCES `browser_info` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3524 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

兴趣表:

CREATE TABLE `interests` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` bigint(20) unsigned NOT NULL,
 `interest` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
 `inserted_dt` datetime NOT NULL,
 `active` tinyint(1) NOT NULL DEFAULT '1',
 UNIQUE KEY `id` (`id`),
 KEY `user_id` (`user_id`),
 KEY `interest` (`interest`),
 CONSTRAINT `interests_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11828 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

阻塞表:

CREATE TABLE `blocked` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `actor_id` bigint(20) unsigned NOT NULL,
 `receiver_id` bigint(20) unsigned DEFAULT NULL,
 `blocked_reason` enum('Skipped','Blocked') COLLATE utf8_unicode_ci NOT NULL,
 `inserted_dt` datetime NOT NULL,
 `active` tinyint(1) NOT NULL DEFAULT '1',
 `browser_info_id` bigint(20) unsigned DEFAULT NULL,
 UNIQUE KEY `id` (`id`),
 KEY `actor_id` (`actor_id`,`receiver_id`),
 KEY `receiver_id` (`receiver_id`),
 KEY `browser_info_id` (`browser_info_id`),
 CONSTRAINT `blocked_ibfk_1` FOREIGN KEY (`actor_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `blocked_ibfk_2` FOREIGN KEY (`receiver_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `blocked_ibfk_3` FOREIGN KEY (`browser_info_id`) REFERENCES `browser_info` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=35800 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

历史表:

CREATE TABLE `history` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `actor_id` bigint(20) unsigned NOT NULL,
 `receiver_id` bigint(20) unsigned NOT NULL,
 `credits` int(11) NOT NULL,
 `dt` datetime NOT NULL,
 `browser_info_id` bigint(20) unsigned DEFAULT NULL,
 UNIQUE KEY `id` (`id`),
 KEY `actor_id` (`actor_id`,`receiver_id`),
 KEY `browser_info_id` (`browser_info_id`),
 KEY `receiver_id` (`receiver_id`),
 CONSTRAINT `history_ibfk_1` FOREIGN KEY (`browser_info_id`) REFERENCES `browser_info` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
 CONSTRAINT `history_ibfk_5` FOREIGN KEY (`actor_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `history_ibfk_6` FOREIGN KEY (`receiver_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=148552 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

我在探查器上运行我的查询，这是我得到的:

Status  Time

starting    0.000009
Waiting for query cache lock    0.000003
Waiting on query cache mutex    0.000003
checking query cache for query  0.000150
checking permissions    0.000004
checking permissions    0.000001
checking permissions    0.000002
checking permissions    0.000002
checking permissions    0.000004
Opening tables  0.000113
System lock 0.000018
Waiting for query cache lock    0.000003
Waiting on query cache mutex    0.000068
init    0.000122
optimizing  0.000031
statistics  0.000171
preparing   0.000070
executing   0.000003
Sorting result  0.000096
optimizing  0.000007
statistics  0.000010
preparing   0.000146
optimizing  0.000006
statistics  0.000025
preparing   7.110198
Sending data    0.000589
end 0.000009
query end   0.000011
closing tables  0.000070
freeing items   0.000045
logging slow query  0.000003
cleaning up 0.000014

因此“准备”此查询平均需要 7.11 秒。这太长了，我觉得如果我更擅长 SQL，可以缩短它。有人能帮我吗？我已经添加了所有需要弄清楚的表格。在这上面停留了一段时间。

解释: id select_type 表类型 possible_keys key key_len ref rows Extra 1 PRIMARY 用户范围 id id 8 NULL 1880 Using where;使用文件排序 1 PRIMARY history ref actor_id,receiver_id actor_id 16 const,bradley_instapromote.users.id 1 Using where;使用索引；不存在 1 PRIMARY blocked ref actor_id,receiver_id actor_id 17 const,bradley_instapromote.users.id 1 Using where;使用索引 2 DEPENDENT SUBQUERY interests index_subquery user_id user_id 8 func 2 Using where 3 DEPENDENT SUBQUERY interests index_subquery user_id,interest interest 77 func 6 使用where

解释 IMG:

Answer 1

将子选择转换为连接

SELECT `users`.`id`, `users`.`username`, `users`.`profile_picture_url`, `users`.`credits_offered`, `users`.`country_id`, `users`.`user_level`, `users`.`credits`
FROM `users`
INNER JOIN interests AS a ON (a.user_id = users.user_id)
INNER JOIN interests AS b ON (a.interest = b.interes AND b.user_id = 100 and b.active = 1)
LEFT JOIN `history` ON `history`.`actor_id` = 100 AND `history`.`receiver_id` = `users`.`id`
LEFT JOIN `blocked` ON `blocked`.`actor_id` = 100 AND `blocked`.`receiver_id` = `users`.`id`
WHERE `history`.`receiver_id` IS NULL
AND `blocked`.`receiver_id` IS NULL
AND `users`.`instagram_active` = 1
AND `users`.`banned` = 0
AND `users`.`shown_to_others` = 1
AND `users`.`featured_user` = 0
AND `users`.`id` <> 100
AND `users`.`featured_user` = 0
ORDER BY (`users`.`credits` >= `users`.`credits_offered`) DESC, `users`.`credits_offered` DESC, `users`.`credits` DESC
LIMIT 0, 25

或者运行一个查询来获取兴趣列表，然后直接在用户查询中使用它