mysql left join where with an or，扫描整个表(无索引)

coder 2023-10-06 原文

我尝试了几种不同的方法，但结果很糟糕。

核心问题是成员搜索正在扫描所有成员，忽略索引。

主要原因(据我所知)是这个片段

(Member.priv_profile = 3 OR MyFriend.status_id IN (1,2))

单独使用该 OR 片段的任一侧都可以正常工作，获取索引，扫描几行，因此性能良好。

我真的不想将此查询拆分为 2 并执行 UNION，但我们可能不得不这样做，除非有人能想出一个好方法使此选择与重要的 OR 一起“工作”。

mysql> ALTER TABLE `members` ADD INDEX A (is_active, last_name, first_name);
Query OK, 140019 rows affected (6.82 sec)
Records: 140019  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `members` ADD INDEX B (is_active, last_name, first_name, priv_profile);
Query OK, 140019 rows affected (7.70 sec)
Records: 140019  Duplicates: 0  Warnings: 0

mysql> explain SELECT COUNT(*) AS `count` FROM `ao_prod`.`members` AS `Member` 
LEFT JOIN `ao_prod`.`member_friends` AS `MyFriend` ON (`MyFriend`.`member_2_id` = `Member`.`id` AND member_1_id = '150365')  
WHERE `Member`.`is_active` = '1' AND NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '') AND (`Member`.`priv_profile` = 3 OR `MyFriend`.`status_id` IN (1,2));
+----+-------------+----------+------+----------------------------------------------+-------------+---------+-------+--------+--------------------------+
| id | select_type | table    | type | possible_keys                                | key         | key_len | ref   | rows   | Extra                    |
+----+-------------+----------+------+----------------------------------------------+-------------+---------+-------+--------+--------------------------+
|  1 | SIMPLE      | Member   | ALL  | active_delete,scope,member_search_alerts,A,B | NULL        | NULL    | NULL  | 140019 | Using where              |
|  1 | SIMPLE      | MyFriend | ref  | member_1_id                                  | member_1_id | 4       | const |    155 | Using where; Using index |
+----+-------------+----------+------+----------------------------------------------+-------------+---------+-------+--------+--------------------------+
2 rows in set (0.00 sec)

// without the "public profile" part

mysql> explain SELECT COUNT(*) AS `count` FROM `ao_prod`.`members` AS `Member` 
LEFT JOIN `ao_prod`.`member_friends` AS `MyFriend` ON (`MyFriend`.`member_2_id` = `Member`.`id` AND member_1_id = '150365')  
WHERE `Member`.`is_active` = '1' AND NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '') AND (`MyFriend`.`status_id` IN (1,2));
+----+-------------+----------+--------+------------------------------------------------------+-------------+---------+------------------------------+------+--------------------------+
| id | select_type | table    | type   | possible_keys                                        | key         | key_len | ref                          | rows | Extra                    |
+----+-------------+----------+--------+------------------------------------------------------+-------------+---------+------------------------------+------+--------------------------+
|  1 | SIMPLE      | MyFriend | range  | member_1_id                                          | member_1_id | 5       | NULL                         |  251 | Using where; Using index |
|  1 | SIMPLE      | Member   | eq_ref | PRIMARY,active_delete,scope,member_search_alerts,A,B | PRIMARY     | 4       | ao_prod.MyFriend.member_2_id |    1 | Using where              |
+----+-------------+----------+--------+------------------------------------------------------+-------------+---------+------------------------------+------+--------------------------+
2 rows in set (0.00 sec)

// without the "my connection" part

mysql> explain SELECT COUNT(*) AS `count` FROM `ao_prod`.`members` AS `Member` 
LEFT JOIN `ao_prod`.`member_friends` AS `MyFriend` ON (`MyFriend`.`member_2_id` = `Member`.`id` AND member_1_id = '42983')  
WHERE `Member`.`is_active` = '1' AND ( NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '')) AND (`Member`.`priv_profile` = 3);
+----+-------------+----------+------+----------------------------------------------+-------------+---------+-------------+------+-------------+
| id | select_type | table    | type | possible_keys                                | key         | key_len | ref         | rows | Extra       |
+----+-------------+----------+------+----------------------------------------------+-------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | Member   | ref  | active_delete,scope,member_search_alerts,A,B | scope       | 2       | const,const | 2007 | Using where |
|  1 | SIMPLE      | MyFriend | ref  | member_1_id                                  | member_1_id | 4       | const       |  252 | Using index |
+----+-------------+----------+------+----------------------------------------------+-------------+---------+-------------+------+-------------+
2 rows in set (0.01 sec)

// as a subquery vs. join (no workie)

mysql> explain SELECT COUNT(*) AS `count` FROM `ao_prod`.`members` AS `Member` 
WHERE `Member`.`is_active` = '1' AND NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '') AND ( `Member`.`id` IN (
 SELECT member_2_id FROM member_friends WHERE member_1_id = 150365 AND status_id IN (1,2)  
));
+----+--------------------+----------------+-------+----------------------------------------------+-------------+---------+------+--------+--------------------------+
| id | select_type        | table          | type  | possible_keys                                | key         | key_len | ref  | rows   | Extra                    |
+----+--------------------+----------------+-------+----------------------------------------------+-------------+---------+------+--------+--------------------------+
|  1 | PRIMARY            | Member         | ALL   | active_delete,scope,member_search_alerts,A,B | NULL        | NULL    | NULL | 140019 | Using where              |
|  2 | DEPENDENT SUBQUERY | member_friends | range | member_1_id                                  | member_1_id | 5       | NULL |    155 | Using where; Using index |
+----+--------------------+----------------+-------+----------------------------------------------+-------------+---------+------+--------+--------------------------+
2 rows in set (0.01 sec)

// sketch of the possible, ugly UNION

mysql> explain SELECT COUNT(*) AS `count` FROM `ao_prod`.`members` AS `Member` LEFT JOIN `ao_prod`.`member_friends` AS `MyFriend` ON (`MyFriend`.`member_2_id` = `Member`.`id` AND member_1_id = '42983')  WHERE `Member`.`is_active` = '1' AND ( NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '')) AND (`MyFriend`.`status_id` IN (1,2))
    -> UNION
    -> SELECT COUNT(*) AS `count` FROM `ao_prod`.`members` AS `Member`  WHERE `Member`.`is_active` = '1' AND ( NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '')) AND (`Member`.`priv_profile` = 3)
    -> GROUP BY Member.id
    -> ;
+----+--------------+------------+--------+------------------------------------------------------+-------------+---------+------------------------------+------+----------------------------------------------+
| id | select_type  | table      | type   | possible_keys                                        | key         | key_len | ref                          | rows | Extra                                        |
+----+--------------+------------+--------+------------------------------------------------------+-------------+---------+------------------------------+------+----------------------------------------------+
|  1 | PRIMARY      | MyFriend   | range  | member_1_id                                          | member_1_id | 5       | NULL                         |  251 | Using where; Using index                     |
|  1 | PRIMARY      | Member     | eq_ref | PRIMARY,active_delete,scope,member_search_alerts,A,B | PRIMARY     | 4       | ao_prod.MyFriend.member_2_id |    1 | Using where                                  |
|  2 | UNION        | Member     | ref    | active_delete,scope,member_search_alerts,A,B         | scope       | 2       | const,const                  | 2007 | Using where; Using temporary; Using filesort |
| NULL | UNION RESULT | <union1,2> | ALL    | NULL                                                 | NULL        | NULL    | NULL                         | NULL |                                              |
+----+--------------+------------+--------+------------------------------------------------------+-------------+---------+------------------------------+------+----------------------------------------------+
4 rows in set (0.02 sec)

// using index hinting to no avail

mysql> explain SELECT COUNT(*) AS `count` 
FROM `ao_prod`.`members` AS `Member` 
USE INDEX (A)  
LEFT JOIN `ao_prod`.`member_friends` AS `MyFriend` ON (`MyFriend`.`member_2_id` = `Member`.`id` AND member_1_id = '150365')  
WHERE `Member`.`is_active` = '1' AND NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '') AND (`Member`.`priv_profile` = 3 OR `MyFriend`.`status_id` IN (1,2));
+----+-------------+----------+------+---------------+-------------+---------+-------+--------+--------------------------+
| id | select_type | table    | type | possible_keys | key         | key_len | ref   | rows   | Extra                    |
+----+-------------+----------+------+---------------+-------------+---------+-------+--------+--------------------------+
|  1 | SIMPLE      | Member   | ALL  | A             | NULL        | NULL    | NULL  | 140245 | Using where              |
|  1 | SIMPLE      | MyFriend | ref  | member_1_id   | member_1_id | 4       | const |    181 | Using where; Using index |
+----+-------------+----------+------+---------------+-------------+---------+-------+--------+--------------------------+
2 rows in set (0.01 sec)

这里是所涉及表的创建语句(完整的、丑陋的表和显示的所有其他索引)

CREATE TABLE IF NOT EXISTS `member_friends` (
  `id` varchar(36) NOT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  `member_1_id` int(11) NOT NULL DEFAULT '0',
  `member_2_id` int(11) NOT NULL DEFAULT '0',
  `status_id` tinyint(3) NOT NULL DEFAULT '0',
  `requested_by` tinyint(3) NOT NULL DEFAULT '0',
  `requested` datetime DEFAULT NULL,
  `accepted` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `member_1_id` (`member_1_id`,`status_id`,`member_2_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `members_fields`
 ADD PRIMARY KEY (`id`), ADD KEY `key` (`key`), ADD KEY `member_key` (`member_id`,`key`);

CREATE TABLE IF NOT EXISTS `members` (
`id` int(11) NOT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  `profile_updated` datetime NOT NULL,
  `last_login` datetime DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL,
  `email` varchar(256) NOT NULL DEFAULT '',
  `password` varchar(40) NOT NULL,
  `first_name` varchar(128) NOT NULL DEFAULT '',
  `middle_name` varchar(128) NOT NULL,
  `last_name` varchar(128) NOT NULL DEFAULT '',
  `suffix` varchar(32) NOT NULL,
  `company` varchar(128) NOT NULL,
  `address` varchar(128) NOT NULL,
  `address_2` varchar(128) NOT NULL,
  `city` varchar(128) NOT NULL,
  `state` varchar(5) NOT NULL,
  `zip` varchar(16) NOT NULL,
  `location_name` varchar(128) NOT NULL,
  `image_url` varchar(256) NOT NULL,
  `slug` varchar(64) NOT NULL,
  `headline` varchar(256) NOT NULL,
  `experience_level` varchar(64) NOT NULL,
  `apply_job_states` varchar(256) NOT NULL COMMENT 'CSV list',
  `apply_job_us` tinyint(1) NOT NULL DEFAULT '0',
  `apply_job_ca` tinyint(1) NOT NULL DEFAULT '0',
  `apply_job_traveling` tinyint(1) NOT NULL DEFAULT '0',
  `apply_job_international` tinyint(1) NOT NULL DEFAULT '0',
  `apply_job_fulltime` tinyint(1) NOT NULL DEFAULT '0',
  `apply_job_parttime` tinyint(1) NOT NULL DEFAULT '0',
  `apply_job_perdiem` tinyint(1) NOT NULL DEFAULT '0',
  `contact_for_professional_opportunities` tinyint(1) NOT NULL DEFAULT '0',
  `contact_for_job_inquiries` tinyint(1) NOT NULL DEFAULT '0',
  `contact_for_new_ventures` tinyint(1) NOT NULL DEFAULT '0',
  `contact_for_expertise_requests` tinyint(1) NOT NULL DEFAULT '0',
  `country` varchar(2) NOT NULL,
  `timezone` varchar(32) NOT NULL,
  `phone` varchar(16) NOT NULL,
  `fax` varchar(16) NOT NULL,
  `birthday` varchar(5) NOT NULL COMMENT 'MM/DD (required)',
  `birth_year` varchar(4) DEFAULT NULL COMMENT 'YYYY (optional)',
  `corp_id` int(11) NOT NULL DEFAULT '0',
  `is_deleted` tinyint(1) NOT NULL,
  `url` varchar(256) DEFAULT NULL,
  `emails` varchar(512) NOT NULL COMMENT 'JSON list of alternate emails',
  `phones` varchar(512) NOT NULL COMMENT 'JSON list of alternate phones',
  `lat` float NOT NULL,
  `lon` float NOT NULL,
  `facebook_id` varchar(32) NOT NULL,
  `connect_id` int(11) NOT NULL,
  `is_student` tinyint(1) NOT NULL DEFAULT '0',
  `is_career_center_recruiter` tinyint(1) NOT NULL DEFAULT '0',
  `is_continuing_education_portal_manager` tinyint(1) NOT NULL DEFAULT '0',
  `is_manually_approved` tinyint(1) NOT NULL DEFAULT '0',
  `is_employer` tinyint(1) NOT NULL DEFAULT '0',
  `is_jobseeker` tinyint(1) NOT NULL DEFAULT '0',
  `is_jobseeker_badge` tinyint(1) NOT NULL DEFAULT '0',
  `is_contributor` tinyint(1) NOT NULL DEFAULT '0',
  `priv_profile` tinyint(3) NOT NULL DEFAULT '1',
  `priv_email` tinyint(3) NOT NULL DEFAULT '0',
  `priv_phone` tinyint(3) NOT NULL DEFAULT '0',
  `has_certification` tinyint(1) DEFAULT NULL,
  `has_state_license` tinyint(1) DEFAULT NULL,
  `job_title` varchar(64) NOT NULL,
  `occupation_id` int(11) NOT NULL,
  `occupation_other` varchar(64) NOT NULL,
  `work_setting_id` int(11) NOT NULL,
  `work_setting_other` varchar(64) NOT NULL,
  `memberships_honors_awards` text NOT NULL
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1093688 ;

ALTER TABLE `members`
 ADD PRIMARY KEY (`id`), ADD KEY `is_cc` (`is_career_center_recruiter`,`corp_id`), ADD KEY `is_ce` (`is_continuing_education_portal_manager`,`corp_id`), ADD KEY `corp_id` (`corp_id`), ADD KEY `active_delete` (`is_active`,`is_deleted`), ADD KEY `delete` (`is_deleted`), ADD KEY `email_pass` (`email`,`password`), ADD KEY `apply_job_states` (`apply_job_states`,`apply_job_us`,`apply_job_ca`), ADD KEY `experience_level` (`experience_level`), ADD KEY `latlon` (`lat`,`lon`), ADD KEY `location` (`state`,`zip`), ADD KEY `slug` (`slug`,`is_active`,`priv_profile`), ADD KEY `scope` (`is_active`,`priv_profile`,`state`), ADD KEY `member_search_alerts` (`is_active`,`is_jobseeker`,`profile_updated`,`priv_profile`,`apply_job_us`,`apply_job_ca`);

更新:根据要求，这里是优化器设置

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on
1 row in set (0.00 sec)

注意:这已经过测试

服务器版本:5.6.20-68.0-56-log - Percona XtraDB Cluster (GPL)，25.7 版
服务器版本:5.5.29-0ubuntu0.12.04.1
服务器版本:5.1.72 - 源分发

最佳答案

在这种情况下，其中一个表是 MyISAM，另一个是 InnoDB

当我将两者都切换到 InnoDB 时，它神奇地从 ALL 更改为 ref 并从扫描所有行变为子集。

mysql> explain SELECT COUNT(*) AS `count` FROM `ao_prod`.`members` AS `Member`  LEFT JOIN `ao_prod`.`member_friends` AS `MyFriend` ON (`MyFriend`.`member_2_id` = `Member`.`id` AND member_1_id = '150365')   WHERE `Member`.`is_active` = '1' AND NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '') AND (`Member`.`priv_profile` = 3 OR `MyFriend`.`status_id` IN (1,2));
+----+-------------+----------+------+---------------+-------------+---------+-------+--------+--------------------------+
| id | select_type | table    | type | possible_keys | key         | key_len | ref   | rows   | Extra                    |
+----+-------------+----------+------+---------------+-------------+---------+-------+--------+--------------------------+
|  1 | SIMPLE      | Member   | ALL  | A             | NULL        | NULL    | NULL  | 140245 | Using where              |
|  1 | SIMPLE      | MyFriend | ref  | member_1_id   | member_1_id | 4       | const |    181 | Using where; Using index |
+----+-------------+----------+------+---------------+-------------+---------+-------+--------+--------------------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE `members` ENGINE = InnoDB;
Query OK, 140245 rows affected (1 min 8.10 sec)
Records: 140245  Duplicates: 0  Warnings: 0

mysql> explain SELECT COUNT(*) AS `count` FROM `ao_prod`.`members` AS `Member`  LEFT JOIN `ao_prod`.`member_friends` AS `MyFriend` ON (`MyFriend`.`member_2_id` = `Member`.`id` AND member_1_id = '150365')   WHERE `Member`.`is_active` = '1' AND NOT(`Member`.`first_name` = '' AND `Member`.`last_name` = '') AND (`Member`.`priv_profile` = 3 OR `MyFriend`.`status_id` IN (1,2));
+----+-------------+----------+------+---------------+-------------+---------+-------+-------+--------------------------+
| id | select_type | table    | type | possible_keys | key         | key_len | ref   | rows  | Extra                    |
+----+-------------+----------+------+---------------+-------------+---------+-------+-------+--------------------------+
|  1 | SIMPLE      | Member   | ref  | A             | A           | 1       | const | 53916 | Using where              |
|  1 | SIMPLE      | MyFriend | ref  | member_1_id   | member_1_id | 4       | const |   181 | Using where; Using index |
+----+-------------+----------+------+---------------+-------------+---------+-------+-------+--------------------------+

关于mysql left join where with an or，扫描整个表(无索引)，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/26933946/

有关mysql left join where with an or，扫描整个表(无索引)的更多相关文章

ruby-on-rails - 协会的 Rails 索引 - 2
我发现自己需要这个。假设cart是一个包含用户列表的模型。defindex_of_itemcart.users.each_with_indexdo|u,i|ifu==current_userreturniendend获取此类关联索引的更简单方法是什么？最佳答案 indexArray上的方法与您的index_of_item方法相同，例如cart.users.index(current_user)返回数组中第一个对象的索引==给obj。如果未找到匹配项，则返回nil。关于ruby-on-
ruby - Rails -- :id attribute? 所需的数据库索引 - 2
因此，当我遵循MichaelHartl的RubyonRails教程时，我注意到在用户表中，我们为:email属性添加了一个唯一索引，以提高find的效率方法，因此它不会逐行搜索。到目前为止，我们一直在根据情况使用find_by_email和find_by_id进行搜索。然而，我们从未为:id属性设置索引。:id是否自动索引，因为它在默认情况下是唯一的并且本质上是顺序的？或者情况并非如此，我应该为:id搜索添加索引吗？最佳答案大多数数据库(包括sqlite，这是RoR中的默认数据库)会自动索引主键，对于RailsMigration
ruby - 引用具有指定索引的枚举器值 - 2
假设我有一个可枚举对象enum，现在我想获取第三个项目。我知道一种通用方法是转换成数组，然后使用索引访问，如:enum.to_a[2]但这种方式会创建一个临时数组，效率可能很低。现在我使用:enum.each_with_index{|v,i|breakvifi==2}但这非常丑陋和多余。执行此操作最有效的方法是什么？最佳答案你可以使用take剥离前三个元素，然后剥离last从take给你的数组中获取第三个元素:third=enum.take(3).last如果您根本不想生成任何数组，那么也许:#Ifenumisn'tanEnum
ruby - 将 Logstash 中的时间戳时区转换为输出索引名称 - 2
在我的场景中，Logstash收到的系统日志行的“时间戳”是UTC，我们在Elasticsearch输出中使用事件“时间戳”:output{elasticsearch{embedded=>falsehost=>localhostport=>9200protocol=>httpcluster=>'elasticsearch'index=>"syslog-%{+YYYY.MM.dd}"}}我的问题是，在UTC午夜，Logstash在外时区(GMT-4=>America/Montreal)结束前将日志发送到不同的索引，并且索引在20小时(晚上8点)之后没有日志，因为“时间戳”是UTC。我们已
ruby-on-rails - 在 Rails 中需要整个目录树的好方法是什么？ - 2
我正在使用Rails3.2.2并希望递归加载某个目录中的所有代码。例如:[Railsroot]/lib/my_lib/my_lib.rb[Railsroot]/lib/my_lib/subdir/support_file_00.rb[Railsroot]/lib/my_lib/subdir/support_file_01.rb...基于谷歌搜索，我试过:config.autoload_paths+=["#{Rails.root.to_s}/lib/my_lib/**"]config.autoload_paths+=["#{Rails.root.to_s}/lib/my_lib/**/"
ruby - 从特定索引开始迭代数组 - 2
我想从特定索引开始遍历数组。我该怎么做？myj.eachdo|temp|...end 最佳答案执行以下操作:your_array[your_index..-1].eachdo|temp|###end 关于ruby-从特定索引开始迭代数组，我们在StackOverflow上找到一个类似的问题： https://stackoverflow.com/questions/44151758/
ruby - Array of Arrays，根据索引处的数组内容删除一个索引？ - 2
我一直在努力学习如何处理由数组组成的数组。假设我有这个数组:my_array=[['ORANGE',1],['APPLE',2],['PEACH',3]我将如何找到包含'apple'的my_array索引并删除该索引(删除子数组['APPLE',2]因为'apple'包含在该索引的数组中)？谢谢-我非常感谢这里的帮助。最佳答案您可以使用Array.select过滤掉项目:>>a=[['ORANGE',1],['APPLE',2],['PEACH',3]]=>[["ORANGE",1],["APPLE",2],["PEACH",3
ruby - 如何使用部分字符串搜索数组并返回索引？ - 2
我想使用部分字符串搜索数组，然后获取找到该字符串的索引。例如:a=["Thisisline1","Wehaveline2here","andfinallyline3","potato"]a.index("potato")#thisreturns3a.index("Wehave")#thisreturnsnil使用a.grep将返回完整的字符串，使用a.any?将返回正确的true/false语句，但都不会返回匹配的索引找到了，或者至少我不知道该怎么做。我正在编写一段代码，该代码读取文件、查找特定header，然后返回该header的索引，以便它可以将其用作future搜索的偏移量。如果
ruby-on-rails - Rails 4 从迁移索引中删除迁移 ID - 2
如何在rakedb:migrate:status中删除带有“**NOFILE**”的迁移ID列表？例如:StatusMigrationIDMigrationName--------------------------------------------------up20131017204224Createusersup20131218005823**********NOFILE**********up20131218011334**********NOFILE**********我不明白为什么当我自己手动删除它时它仍然保留旧的迁移文件，因为我正在研究迁移的工作原理。这是为了记录吗？但
ruby - 根据子哈希值获取数组索引 - 2
假设我有这个:[{:id=>34,:votes_count=>3},{:id=>2,:votes_count=>0},]如何根据id获取索引？我想要做的是在搜索id:34时返回0，在搜索id:21/。什么是最有效的方法？最佳答案你可以将一个block传递给#index:array.index{|h|h[:id]==34}#=>0 关于ruby-根据子哈希值获取数组索引，我们在StackOverflow上找到一个类似的问题： https://stackove

mysql left join where with an or，扫描整个表(无索引)

有关mysql left join where with an or，扫描整个表(无索引)的更多相关文章

随机推荐