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

| 我们有一个中央登录名，用于支持多个网站。为了存储用户的数据，我们有一个“ 0”表来存储每个用户帐户，然后是每个站点的“ 1”表以获取特定于站点的信息。 我们注意到，一个将表连接到主键ѭ2on上的查询正在缓慢执行。我希望那里的一些SQL专家可以解释为什么它使用
WHERE
搜索users_site1表并建议我们如何对其进行优化。这是缓慢的查询和解释结果： 
mysql> explain select a.user_id as \'id\',a.username,a.first_name as \'first\',a.last_name as \'last\',a.sex,u.user_id as \'profile\',u.facebook_id as \'fb_id\',u.facebook_publish as \'fb_publish\',u.facebook_offline as \'fb_offline\',u.twitter_id as \'tw_id\',u.api_session as \'mobile\',a.network from accounts a left join users_site1 u ON a.user_id=u.user_id AND u.status=\"R\" where a.status=\"R\" AND u.status=\"R\" AND a.facebook_id=\'1234567890\';
+----+-------------+-------+--------+----------------+---------+---------+-----------------------+-------+-------------+
| id | select_type | table | type   | possible_keys  | key     | key_len | ref                   | rows  | Extra       |
+----+-------------+-------+--------+----------------+---------+---------+-----------------------+-------+-------------+
|  1 | SIMPLE      | u     | ALL    | PRIMARY        | NULL    | NULL    | NULL                  | 79769 | Using where |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY,status | PRIMARY | 4       | alltrailsdb.u.user_id |     1 | Using where |
+----+-------------+-------+--------+----------------+---------+---------+-----------------------+-------+-------------+
2 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 `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`)
);
    
2020-05-23 5 条评论
分享

没有找到相关结果

    已邀请:

    倾坞髓

    在“ 0”表的“ 6”列上添加索引。 当前，MySql正在扫描整个“ 1”表，因为它无法直接在“ 9”表中找到记录。     
    2020-05-23 0 0
    分享

    净爽

    最少在
    accounts.user_id
    ，
    user_site1.user_id
    和
    accounts.facebook_id
    上创建3个索引。 
    user_id
    索引可能已经存在，因为它们被定义为PK。     
    2020-05-23 0 0
    分享

    味芯憨

    您的查询正在基于Facebook ID和帐户“状态”在表ѭ0中查找行。您没有任何有助于此操作的索引，因此MySQL正在执行表扫描。我建议以下索引： 
    ALTER TABLE accounts ADD INDEX (facebook_id, user_id)
    如果需要，您甚至可以在索引中包含“ 16”列。这是否是一个好主意，实际上取决于它是否有助于使索引成为您计划运行的任何其他查询的优化器的诱人选择。 PS。注释“在哪里使用”是正常的，并且在大多数查询中都应如此。这里要注意的是MySQL没有使用索引，并且认为它必须检查大量的行（当您传递特定的ID号时，肯定不会出现这种情况）。     
    2020-05-23 0 0
    分享

    锑寝粒

    可能是因为您尚未在要搜索的列上创建索引？？？尝试索引连接语句中使用的列。没有索引，您正在扫描所有数据集。 
    CREATE INDEX accounts_user_id_index ON accounts (user_id);
CREATE INDEX accounts.facebook_id_index ON accounts (status);
CREATE INDEX user_site1.user_id_index ON user_site1 (user_id);
        
    2020-05-23 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    或代码 OrCode.com 备案号：粤ICP备15020848号-1
    Escape time: 0.037384033203125