mysql - 更多奇怪的 MySQL 行为 - 查询优化帮助

Question

我们有一个中央登录，用于支持多个网站。为了存储我们的用户数据，我们有一个accounts 表来存储每个用户帐户，然后是每个站点的users 表以获取站点特定信息。我们还有一个简单的 connections 表，用于存储用户之间的连接。

我们注意到，一个以主键 user_id 连接表的查询执行缓慢。我希望那里的一些 SQL 专家可以解释为什么它使用 WHERE 来搜索 users_site1 表并建议我们如何优化它。这是慢速查询和解释结果:

mysql> explain select a.username,a.first_name,a.last_name,a.organization_name,a.organization,a.city,a.state,a.zip,a.country,a.profile_photo,a.facebook_id,a.twitter_id,u.reviews from accounts a join users_site1 u ON a.user_id=u.user_id where a.user_id IN (select cid2 from connections where cid1=10001006 AND type="MM" AND status="A") OR a.user_id IN (select cid1 from connections where cid2=10001006 AND type="MM" AND status="A") order by RAND() LIMIT 4;
+----+--------------------+-------------+--------+-------------------+---------+---------+-----------------------+-------+----------------------------------------------+
| id | select_type        | table       | type   | possible_keys     | key     | key_len | ref                   | rows  | Extra                                        |
+----+--------------------+-------------+--------+-------------------+---------+---------+-----------------------+-------+----------------------------------------------+
|  1 | PRIMARY            | u           | ALL    | PRIMARY           | NULL    | NULL    | NULL                  | 79783 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | a           | eq_ref | PRIMARY           | PRIMARY | 4       | exampledb.u.user_id |     1 |                                              |
|  3 | DEPENDENT SUBQUERY | connections | ref    | PRIMARY,cid1,cid2 | cid2    | 6       | const,const           |     2 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | connections | ref    | PRIMARY,cid1,cid2 | cid1    | 6       | const,const           |     1 | Using where                                  |
+----+--------------------+-------------+--------+-------------------+---------+---------+-----------------------+-------+----------------------------------------------+
4 rows in set (0.00 sec)

以下是每个表的定义:

CREATE TABLE `accounts` (
  `user_id` int(9) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(40) DEFAULT NULL,
  `facebook_id` bigint(15) unsigned DEFAULT NULL,
  `facebook_username` varchar(30) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  `profile_photo` varchar(100) DEFAULT NULL,
  `first_name` varchar(40) DEFAULT NULL,
  `middle_name` varchar(40) DEFAULT NULL,
  `last_name` varchar(40) DEFAULT NULL,
  `suffix_name` char(3) DEFAULT NULL,
  `organization_name` varchar(100) DEFAULT NULL,
  `organization` tinyint(1) unsigned DEFAULT NULL,
  `address` varchar(200) DEFAULT NULL,
  `city` varchar(40) DEFAULT NULL,
  `state` varchar(20) DEFAULT NULL,
  `zip` varchar(10) DEFAULT NULL,
  `province` varchar(40) DEFAULT NULL,
  `country` int(3) DEFAULT NULL,
  `latitude` decimal(11,7) DEFAULT NULL,
  `longitude` decimal(12,7) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `about_me` varchar(2000) DEFAULT NULL,
  `activities` varchar(300) DEFAULT NULL,
  `website` varchar(100) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `referrer` int(4) unsigned DEFAULT NULL,
  `referredid` int(9) unsigned DEFAULT NULL,
  `verify` int(6) DEFAULT NULL,
  `status` char(1) DEFAULT 'R',
  `created` datetime DEFAULT NULL,
  `verified` datetime DEFAULT NULL,
  `activated` datetime DEFAULT NULL,
  `network` datetime DEFAULT NULL,
  `deleted` datetime DEFAULT NULL,
  `logins` int(6) unsigned DEFAULT '0',
  `api_logins` int(6) unsigned DEFAULT '0',
  `last_login` datetime DEFAULT NULL,
  `last_update` datetime DEFAULT NULL,
  `private` tinyint(1) unsigned DEFAULT NULL,
  `ip` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`),
  KEY `facebook_id` (`facebook_id`),
  KEY `status` (`status`),
  KEY `state` (`state`)
);

CREATE TABLE `users_site1` (
  `user_id` int(9) unsigned NOT NULL,
  `facebook_id` bigint(15) unsigned DEFAULT NULL,
  `facebook_username` varchar(30) DEFAULT NULL,
  `facebook_publish` tinyint(1) unsigned DEFAULT NULL,
  `facebook_checkin` tinyint(1) unsigned DEFAULT NULL,
  `facebook_offline` varchar(300) DEFAULT NULL,
  `twitter_id` varchar(60) DEFAULT NULL,
  `twitter_secret` varchar(50) DEFAULT NULL,
  `twitter_username` varchar(20) DEFAULT NULL,
  `type` char(1) DEFAULT 'M',
  `referrer` int(4) unsigned DEFAULT NULL,
  `referredid` int(9) unsigned DEFAULT NULL,
  `session` varchar(60) DEFAULT NULL,
  `api_session` varchar(60) DEFAULT NULL,
  `status` char(1) DEFAULT 'R',
  `created` datetime DEFAULT NULL,
  `verified` datetime DEFAULT NULL,
  `activated` datetime DEFAULT NULL,
  `deleted` datetime DEFAULT NULL,
  `logins` int(6) unsigned DEFAULT '0',
  `api_logins` int(6) unsigned DEFAULT '0',
  `last_login` datetime DEFAULT NULL,
  `last_update` datetime DEFAULT NULL,
  `ip` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
);

CREATE TABLE `connections` (
  `cid1` int(9) unsigned NOT NULL DEFAULT '0',
  `cid2` int(9) unsigned NOT NULL DEFAULT '0',
  `cid3` int(9) unsigned NOT NULL DEFAULT '0',
  `type` char(2) NOT NULL,
  `status` char(1) NOT NULL,
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`cid1`,`cid2`,`type`,`cid3`),
  KEY `cid1` (`cid1`,`type`),
  KEY `cid2` (`cid2`,`type`)
);

Answer 1

您应该使用另一个连接，而不是 WHERE a.userid IN( ... ) OR a.userid IN( ... ):

select 
a.username,a.first_name,a.last_name,a.organization_name,a.organization,a.city,
a.state,a.zip,a.country,a.profile_photo,a.facebook_id,a.twitter_id,u.reviews 
from accounts a 
join users_site1 u ON a.user_id=u.user_id 
join ( select cid2 as id from connections 
       where cid1=10001006 AND type="MM" AND status="A"
       union
       select cid1 as id from connections
       where cid2=10001006 AND type="MM" AND status="A" ) c
on a.user_id = c.id
order by RAND() LIMIT 4;