MySQL 升级后 MySQL NOT IN 查询速度变慢-6ren

MySQL 升级后 MySQL NOT IN 查询速度变慢

转载作者：可可西里更新时间：2023-11-01 08:17:32

这是一个非常简单的查询:

SELECT * FROM temp_company WHERE number NOT IN (SELECT number FROM company)

之前需要 15 分钟，但那是在缓冲池大小太小的 Mysql 安装上，15 分钟没问题，因为这是每月任务。我升级到 Mysql 5.7(从 5.1 或 5.2 之类的东西)，因为原始安装是 32 位，我无法将 innodb 缓冲池大小增加到该数据库所需的最小 10gb(我在一台机器上将其设置为 16GB 32GB RAM。一个月后我去运行这个查询，它在 6 小时后仍在运行。

上面的解释是:

id | select_type        | table        | partitions | type  | possible_keys | key    | key_len | ref | rows    | filtered | Extra       |
1  | PRIMARY            | temp_company |            | ALL   |               |        |         |     | 3226661 | 100.00   | Using where |
2  | DEPENDENT SUBQUERY | company      |            | index | number        | number | 33      |     | 3383517 | 100.00   | Using where |

company 和 temp_company 的 PRIMARY 索引是 id，但 number 是它们匹配的内容，并且在两者中都是 KEY，但以上是否表明它没有使用 temp_company 表的索引？

我想尝试的另一个逻辑查询是:

EXPLAIN SELECT tc.* FROM temp_company tc
LEFT JOIN company c on c.number = tc.number
WHERE c.number IS NULL

这同样慢，EXPLAIN 是:

id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref | rows    | filtered | Extra                                                           |
1  | SIMPLE      | tc    |            | ALL   |               |        |         |     | 3226661 | 100.00   |                                                                 |
2  | SIMPLE      | c     |            | index | number        | number | 33      |     | 3383517 | 100.00   | Using where; Ising index; Using join buffer (block nested loop) |

任何帮助将不胜感激。也许 Mysql 改变了它查找索引的方式？

**更新 1--------

显示创建的:公司

   CREATE TABLE `company` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `count_telephone` mediumint(8) unsigned NOT NULL,
  `count_fax` mediumint(8) unsigned NOT NULL,
  `count_person` mediumint(8) unsigned NOT NULL,
  `person_date` date DEFAULT NULL COMMENT 'Date the company_person relation was updated',
  `count_email_address` mediumint(8) unsigned NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `url_date` date DEFAULT NULL,
  `url_status` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Failure count for crawling the URL',
  `website_stamp_start` int(10) unsigned DEFAULT NULL,
  `website_stamp` int(10) unsigned DEFAULT NULL,
  `ch_url` varchar(255) DEFAULT NULL COMMENT 'Companies house URL',
  `keywords_stamp_start` int(10) unsigned DEFAULT NULL,
  `keywords_stamp` int(11) DEFAULT NULL,
  `number` varchar(30) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  `category` varchar(255) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `status_date` date DEFAULT NULL COMMENT 'Date the status field was updated',
  `country_of_origin` varchar(80) DEFAULT NULL,
  `dissolution_date` date DEFAULT NULL,
  `incorporation_date` date DEFAULT NULL,
  `account_ref_day` smallint(5) unsigned DEFAULT NULL,
  `account_ref_month` smallint(5) unsigned DEFAULT NULL,
  `account_next_due_date` date DEFAULT NULL,
  `account_last_made_up_date` date DEFAULT NULL,
  `account_category` varchar(255) DEFAULT NULL,
  `returns_next_due_date` date DEFAULT NULL,
  `returns_last_made_up_date` date DEFAULT NULL,
  `mortgages_num_charges` smallint(5) unsigned DEFAULT NULL,
  `mortgages_num_outstanding` smallint(5) unsigned DEFAULT NULL,
  `mortgages_num_part_satisfied` smallint(5) unsigned DEFAULT NULL,
  `mortgages_num_satisfied` smallint(5) unsigned DEFAULT NULL,
  `partnerships_num_gen_partners` smallint(5) unsigned DEFAULT NULL,
  `partnerships_num_lim_partners` smallint(5) unsigned DEFAULT NULL,
  `ext_name` varchar(255) DEFAULT NULL,
  `turnover` decimal(18,2) DEFAULT NULL,
  `turnover_date` date DEFAULT NULL,
  `trade_debtors` decimal(18,2) DEFAULT NULL,
  `other_debtors` decimal(18,2) DEFAULT NULL,
  `debtors_date` date DEFAULT NULL,
  `real_turnover_band` int(11) DEFAULT NULL,
  `est_turnover_band` int(11) DEFAULT NULL,
  `ext_address_date` date DEFAULT NULL,
  `care_of` varchar(255) DEFAULT NULL,
  `po_box` varchar(60) DEFAULT NULL,
  `line_1` varchar(255) DEFAULT NULL,
  `line_2` varchar(255) DEFAULT NULL,
  `town` varchar(60) DEFAULT NULL,
  `county` varchar(60) DEFAULT NULL,
  `country` varchar(60) DEFAULT NULL,
  `post_code` varchar(20) DEFAULT NULL,
  `DirScrapeID` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `homepage_keywords_stamp` (`keywords_stamp`),
  KEY `number` (`number`),
  KEY `url` (`url`),
  KEY `town` (`town`),
  KEY `county` (`county`),
  KEY `post_code` (`post_code`),
  KEY `name` (`name`),
  KEY `website_stamp` (`website_stamp`),
  KEY `website_stamp_start` (`website_stamp_start`),
  KEY `keywords_stamp_start` (`keywords_stamp_start`),
  KEY `turnover` (`turnover`),
  KEY `status` (`status`),
  KEY `category` (`category`),
  KEY `incorporation_date` (`incorporation_date`),
  KEY `real_turnover_band` (`real_turnover_band`),
  KEY `est_turnover_band` (`est_turnover_band`)
) ENGINE=InnoDB AUTO_INCREMENT=3706459 DEFAULT CHARSET=utf8

临时公司:

CREATE TABLE `temp_company` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `ch_url` varchar(255) DEFAULT NULL,
  `number` varchar(30) DEFAULT NULL,
  `category` varchar(255) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `country_of_origin` varchar(80) DEFAULT NULL,
  `dissolution_date` date DEFAULT NULL,
  `incorporation_date` date DEFAULT NULL,
  `account_ref_day` smallint(5) unsigned DEFAULT NULL,
  `account_ref_month` smallint(5) unsigned DEFAULT NULL,
  `account_next_due_date` date DEFAULT NULL,
  `account_last_made_up_date` date DEFAULT NULL,
  `account_category` varchar(255) DEFAULT NULL,
  `returns_next_due_date` date DEFAULT NULL,
  `returns_last_made_up_date` date DEFAULT NULL,
  `mortgages_num_charges` smallint(5) unsigned DEFAULT NULL,
  `mortgages_num_outstanding` smallint(5) unsigned DEFAULT NULL,
  `mortgages_num_part_satisfied` smallint(5) unsigned DEFAULT NULL,
  `mortgages_num_satisfied` smallint(5) unsigned DEFAULT NULL,
  `partnerships_num_gen_partners` smallint(5) unsigned DEFAULT NULL,
  `partnerships_num_lim_partners` smallint(5) unsigned DEFAULT NULL,
  `ext_name` varchar(255) DEFAULT NULL,
  `turnover` decimal(18,2) DEFAULT NULL,
  `turnover_date` date DEFAULT NULL,
  `trade_debtors` decimal(18,2) DEFAULT NULL,
  `other_debtors` decimal(18,2) DEFAULT NULL,
  `debtors_date` date DEFAULT NULL,
  `real_turnover_band` int(11) DEFAULT NULL,
  `est_turnover_band` int(11) DEFAULT NULL,
  `ext_address_date` date DEFAULT NULL,
  `care_of` varchar(255) DEFAULT NULL,
  `po_box` varchar(60) DEFAULT NULL,
  `line_1` varchar(255) DEFAULT NULL,
  `line_2` varchar(255) DEFAULT NULL,
  `town` varchar(60) DEFAULT NULL,
  `county` varchar(60) DEFAULT NULL,
  `country` varchar(60) DEFAULT NULL,
  `post_code` varchar(20) DEFAULT NULL,
  `sic_code` varchar(10) DEFAULT NULL,
  `DirScrapeID` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `number` (`number`),
  KEY `status` (`status`),
  KEY `name` (`name`),
  KEY `sic_code` (`sic_code`)
) ENGINE=InnoDB AUTO_INCREMENT=3297833 DEFAULT CHARSET=utf8

更新 2:查询的配置文件(限制为 5)

+-------------------------------+----------+
| Status                        | Duration |
+-------------------------------+----------+
| executing                     | 0.000001 |
| Sending data                  | 0.000112 |
| executing                     | 0.000001 |
| Sending data                  | 0.000111 |
| executing                     | 0.000001 |
| Sending data                  | 0.000110 |
| executing                     | 0.000001 |
| Sending data                  | 0.000110 |
| executing                     | 0.000001 |
| Sending data                  | 0.000110 |
| executing                     | 0.000001 |
| Sending data                  | 0.000111 |
| executing                     | 0.000001 |
| Sending data                  | 0.000111 |
| executing                     | 0.000001 |
| Sending data                  | 0.000112 |
| executing                     | 0.000001 |
| Sending data                  | 0.000112 |
| executing                     | 0.000001 |
| Sending data                  | 0.000112 |
| executing                     | 0.000001 |
| Sending data                  | 0.000112 |
| executing                     | 0.000001 |
| Sending data                  | 0.000112 |
| executing                     | 0.000001 |
| Sending data                  | 0.000112 |
| executing                     | 0.000001 |
| Sending data                  | 0.000113 |
| executing                     | 0.000001 |
| Sending data                  | 0.000114 |
| executing                     | 0.000001 |
| Sending data                  | 0.000114 |
| executing                     | 0.000001 |
| Sending data                  | 0.000114 |
| executing                     | 0.000001 |
| Sending data                  | 0.000115 |
| executing                     | 0.000001 |
| Sending data                  | 0.000116 |
| executing                     | 0.000001 |
| Sending data                  | 0.000115 |
| executing                     | 0.000001 |
| Sending data                  | 0.000115 |
| executing                     | 0.000001 |
| Sending data                  | 0.000116 |
| executing                     | 0.000001 |
| Sending data                  | 0.000116 |
| executing                     | 0.000001 |
| Sending data                  | 0.000115 |
| executing                     | 0.000001 |
| Sending data                  | 0.000115 |
| executing                     | 0.000001 |
| Sending data                  | 0.000116 |
| executing                     | 0.000001 |
| Sending data                  | 0.000116 |
| executing                     | 0.000001 |
| Sending data                  | 0.000117 |
| executing                     | 0.000001 |
| Sending data                  | 0.000117 |
| executing                     | 0.000001 |
| Sending data                  | 0.000117 |
| executing                     | 0.000001 |
| Sending data                  | 0.000118 |
| executing                     | 0.000001 |
| Sending data                  | 0.000118 |
| executing                     | 0.000001 |
| Sending data                  | 0.000118 |
| executing                     | 0.000001 |
| Sending data                  | 0.000118 |
| executing                     | 0.000001 |
| Sending data                  | 0.000118 |
| executing                     | 0.000001 |
| Sending data                  | 0.000118 |
| executing                     | 0.000001 |
| Sending data                  | 0.000120 |
| executing                     | 0.000001 |
| Sending data                  | 0.000120 |
| executing                     | 0.000001 |
| Sending data                  | 0.000121 |
| executing                     | 0.000001 |
| Sending data                  | 0.000123 |
| executing                     | 0.000001 |
| Sending data                  | 0.000121 |
| executing                     | 0.000001 |
| Sending data                  | 0.000120 |
| executing                     | 0.000001 |
| Sending data                  | 0.000121 |
| executing                     | 0.000001 |
| Sending data                  | 0.000121 |
| executing                     | 0.000001 |
| Sending data                  | 0.000121 |
| executing                     | 0.000001 |
| Sending data                  | 0.000122 |
| executing                     | 0.000001 |
| Sending data                  | 0.000123 |
| executing                     | 0.000001 |
| Sending data                  | 0.000124 |
| executing                     | 0.000001 |
| Sending data                  | 1.063880 |
| end                           | 0.000009 |
| query end                     | 0.000008 |
| closing tables                | 0.000009 |
| freeing items                 | 0.000007 |
| Waiting for query cache lock  | 0.000002 |
| freeing items                 | 0.000062 |
| Waiting for query cache lock  | 0.000002 |
| freeing items                 | 0.000001 |
| storing result in query cache | 0.000002 |
| cleaning up                   | 0.000028 |
+-------------------------------+----------+

最佳答案

我不知道为什么它突然运行得更慢了，但我建议转换为一个连接，它应该表现更好:

SELECT t.*
FROM temp_company t
LEFT JOIN company c ON c.number = t.number
WHERE c.number is null

这是通过联接处理 not in (...) 的一种相当标准的方法，并且有效，因为不匹配的外部联接在连接表的列。

关于MySQL 升级后 MySQL NOT IN 查询速度变慢，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/21074717/

文章推荐： c# - 我已经知道C，Java，C++等知识，如何快速学习C#？

文章推荐： android - 通过 Android 在 Kotlin 中实现 promise

文章推荐： c# - 如何让“关于”框出现在 C# 中？

文章推荐： mysql - aes_encrypt 插入表

php - 将时间:[Distance,速度]数据转换为距离:[Time,速度]数据
我在想出一个算法时遇到了麻烦... 我有一系列 GPS 数据，以 1 秒为间隔记录时间、速度、距离。假设距离是米，速度是米/秒。可能有超过 2 小时的数据，或 7200 个点。这里的“时间”字段主要是
Java集合排序和自定义排序-速度
使用java排序器，即: Collections.sort(myArrayList, new Comparator() { @Override public int c
一直困扰我的MySql优化/速度
有什么区别吗 SELECT * FROM my_table 和 SELECT my_column_id FROM my_table 地点: my_table 有百万行网站上有大量并发用户进行sql查
Mysql Order BY 速度
有2个样本。在第一个示例中，使用 orderby 可以更快地获得结果。 (根据 phpmyadmin 速度报告) 在另一个例子中，我没有使用 order by，它给出的结果较慢。 (根据 phpmy
Tensorflow shuffle_batch 速度
我注意到，如果我将训练数据加载到内存中并将其作为 numpy 数组提供到图中，与使用相同大小的 shuffle 批次相比，速度会有很大差异，我的数据有大约 1000 个实例。使用内存 1000 次迭
python - 如何提高python中虚线线图的效率(速度)
我在 python 中使用破折号。我正在绘制记录到 SQLite 数据库中的实时数据，目前，我正在绘制单个值与时间线图。我计划再添加 20 个图表，但目前，随着时间的增加， plotly 变慢，我认为
速度 hasNext 方法
我试图调用 hasNext Velocity 模板中的方法，以便根据 foreach 循环中的位置影响行为 - 仅 hasNext没有按照文档工作。这是 Velocity 用户指南的片段，关于 ha
performance - 三角函数的效率/速度
在我正在制作的游戏中，我有两个点，pt1 和 pt2，我想计算出它们之间的角度。我已经在较早的计算中计算出距离。显而易见的方法是对垂直距离上的水平距离进行反正切 (tan(theta) = opp/a
velocity - 速度，检查字符串是否为空而不为空的最有效方法是什么
我经常遇到字符串值不存在和/或为空的情况。这是测试这种情况的最佳方法吗？ #if( $incentive.disclaimer && $!incentive.disclaimer != '' )
variables - 速度:检查变量是否已定义的任何方法
我想将一个模板nested包含在其他模板cont1，cont2和cont3中。并且嵌套模板应仅对cont1隐藏一个特定控件。在包含在cont1中之前，我想为一些标志变量$hideMyControl
.net - 使用Azure媒体服务更改播放速度(速度)
是否可以更改从“Windows Azure Media Encoder”输出的音频的播放速度？我正在使用配置为“WMA High Quality Audio”的“Windows Azure Medi
velocity - 速度-无法合并时删除字段
我使用速度将String(template)与字段合并 hi there I'am ${name}, And I'am ${age} old. velocity将字段${name}和${age}与一种
c# - 速度 - 将位图数据复制到数组中还是直接使用它？
我使用的是 LockedBitmap 类，它简化了 C# 中位图数据的处理。目前它正在将数据复制到本地 byte[] 数组中，然后通过其类方法访问该数组以获取/设置像素颜色值。这比直接通过指针访问锁
java - 速度:如何定义全局变量
我尝试在 VM_global_library.vm 文件中添加一堆 #set($x=abc) 语句，但这些变量在我的 VM 模板中不可用。我想为图像的基本路径等设置一个全局变量。这可能吗？最佳答案
java - 速度。无法加载我的资源
我的项目结构: -src --main ---java ----makers -----SomeClass ---resources ----htmlPattern.vm 如何告诉 SomeClass
java - 速度 - 更正正则表达式以删除控制字符？
我正在尝试从 Velocity 中的字符串中删除不需要的字符(换行符可以，但不能像 EM 和 CAN ASCII 控制字符那样)。 #set($cleanScreen = $cleanScreen.r
java - 无法在点处分割 - 速度
我想在日.月.年之间的点处分割日期。例如:2015 年 1 月 14 日至 {14, 01, 2015}这是我使用的代码:dates3.get(0) 包含我从页面的文本字段获取的字符串“14.01.2
java - 速度:迭代问题
之后，从 1.5 升级到速度引擎 1.7 出现了 1.5 没有的问题。为了解释这个问题，我必须展示一个代码片段: #foreach($someVariable in $someCollection)
MySQL - "select"速度
我想知道从表中选择所有字段是否更快: SELECT * 或只选择您真正需要的: SELECT field1, field2, field3, field4, field5... 假设表有大约 10 个
iphone - 模仿照片应用程序的平移行为(速度)
我正在尝试模仿照片应用程序的行为，在该应用程序中，用户用手指平移照片并且照片具有一定的速度。由于我不会深入的原因，我不能将 UIScrollView 与它的缩放 UIImageView 一起使用，而是

可可西里

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

MySQL 升级后 MySQL NOT IN 查询速度变慢