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

2023-12-21

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

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 上的主索引是 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

这同样慢，解释是：

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 (...)通过连接，并且可以工作，因为外部连接don't匹配连接表的列中有空值。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql

sql

mysql57

Mysql 升级后 MySQL NOT IN 查询速度变慢的相关文章

根据日期顺序排名

我的数据如下 Heading Date A 2009 02 01 B 2009 02 03 c 2009 02 05 d 2009 02 06 e 2009 02 08 我需要如下排名 Heading Date Rank A 2009 02
无法在 Mac 上启动 MySQL

使用 Brew 安装后我无法运行 MySQL 我使用的是 OS X El Capitan 版本 10 11 3 和 MySQL Server 版本 5 7 11 当我启动服务器时我收到启动 MySQL 错误服务器退出而不更新 PID
sql查询获取从一月到当月的所有数据，即使没有记录

我不擅长 sql 所以任何帮助世界都很棒我有一个 SQL 查询可以获取从一月到当月注册的记录我的代码示例 SELECT DatePart YEAR p createStamp as TheYear DatePart MONTH p c
MySQL 错误 1172 - 结果包含多行

在存储过程中运行查询时我从 MySQL 收到此错误错误代码 1172 结果包含多行我理解错误我正在做一个SELECT INTO var list 因此查询需要返回单行当我使用LIMIT 1 or SELECT DISTINCT 错
Reporting Services 在哪里存储其日志文件

最相关的谷歌结果似乎表明为了访问日志我们必须将您自己的日志表部署到数据库并制作报告服务写入它 http technet microsoft com en us library ms157403 aspx 简而言之 Reporting S
mysql 中的二进制、十六进制和八进制值

我对在 mysql 数据库中使用二进制十六进制和八进制系统非常感兴趣首先请给我一个建议为什么我们在存储信息时需要它们因为信息太多或者为什么另外哪种类型的值必须存储在标记系统中另外这里还有像这是例子 gt SELECT 5
JDBC插入实数数组

我试图将一个真实的数组插入到 postgresql 数组中该表的定义是 String sqlTable CREATE TABLE IF NOT EXISTS ccmBlock sampleId INTEGER block REAL 插入内
在存储过程中验证用户的最简单方法？

我需要一个存储过程可以通过发送以下内容来检查登录尝试时他们是否是有效用户login and password查看它们在数据库中是否匹配有没有一种简单的方法可以做到这一点如果没有更多信息我目前能提供的最好信息是 CREATE STOR
用 pandas DataFrame 替换 mysql 数据库表中的行

Python 版本 2 7 6 熊猫版本 0 17 1 MySQLdb 版本 1 2 5 在我的数据库中 PRODUCT 我有一张桌子 XML FEED 表 XML FEED 很大数百万条记录我有一个 pandas DataFrame
如何从批量数据中的mysql列中删除所有非数字字符

我想从列中删除所有非数字字符我的数据库中有大量数据目前我正在使用以下链接中描述的方法 http venerableagents wordpress com 2011 01 29 mysql numeric functions http
内置函数将每个单词的第一个字母大写

如果 SQL Server 中已存在此类函数我不想为此创建自定义函数输入字符串 This is my string to convert预期输出 This Is My String To Convert SET ANSI NULLS O
提高第一个查询的性能

如果执行以下数据库 postgres 查询则第二次调用要快得多我猜第一个查询很慢因为操作系统 linux 需要从磁盘获取数据第二个查询受益于文件系统级别和 postgres 中的缓存有没有一种方法可以优化数据库以快速获得结果fir
如何识别拼写不同的相似单词

我想从数据库中过滤掉重复的客户名称一位客户可能有多个同名但拼写差异不大的系统条目这是一个示例名为 Brook 的客户可能有 3 个系统条目有了这个变化布鲁克贝尔塔布鲁克贝尔塔比鲁克贝尔塔假设我们将此名称放入一个数据库列
MYSQL - 使用逗号分隔字符串作为变量输入的存储过程

我希望有人能够提供帮助我已经创建了我的第一个存储过程没什么花哨的但是我遇到了问题我想给它一个字符串输入例如 1 2 3 4 5 然后它执行一个简单的操作SELECT FROM TABLE WHERE EAN IN VAR 所以存储
日期语句之间的 JPQL SELECT [关闭]

Closed 这个问题是无法重现或由拼写错误引起 help closed questions 目前不接受答案我想将此 SQL 语句转换为等效的 JPQL SELECT FROM events WHERE events date BETWE
如何使用 BigQuery 有效地选择另一个表中匹配子字符串的记录？

我有一个包含数百万个字符串的表我想将其与包含大约两万个字符串的表进行匹配如下所示 standardSQL SELECT record FROM record JOIN fragment ON record name LIKE CONCA
从Django中具有外键关系的两个表中检索数据？ [复制]

这个问题在这里已经有答案了 This is my models py file from django db import models class Author models Model first name models CharFie
随机组合 MySQL 数据库中的两个单词

我有一个包含名词和形容词的数据库例如 id type word 1 noun apple 2 noun ball 3 adj clammy 4 noun keyboard 5 adj bloody ect 我想创建一个查询它将抓取 10
快速将列的副本添加到 MySQL 表

我需要一种快速的方法来复制表中的 DATETIME 列并为其指定一个新名称我的表中有一个名为 myDate 的列名为 myResults 我需要一个查询来在名为 newDate 的表中创建一个新列该列的数据与 myDate 列完全相同
我是否需要在外键上指定 ON DELETE NO ACTION？

我有以下与 SQL Server 2012 一起使用的 DDL CREATE TABLE Subject SubjectId INT IDENTITY 1 1 NOT NULL Name NVARCHAR 50 Not NULL CONST

随机推荐

startIntentSenderForResult 已弃用 Android

代码片段 private fun requestHint val hintRequest HintRequest Builder setPhoneNumberIdentifierSupported true build val creden
从 Google 服务帐户模拟 Azure 服务主体

我想使用 Google 服务帐户 JWT 令牌获取 Azure 服务主体 SP 的临时凭据这是从 GKE 工作负载调用 Azure API 所必需的而无需在 GKE 中存储长期 SP 凭据这种联合对于 GCP gt Azure 是否可
调用 CLR 存储过程

简而言之在哪里可以找到 C VB 客户端示例代码该代码使用一些 argumnet 如 sqlxml 数据调用 CLR 存储过程并接收数据读取器或其他形式的结果另外如何定期接收通过 SQlContext Pipe Send 方法发送
为什么 Seq.newBuilder 返回 ListBuffer？

看着 val sb Seq newBuilder Int println sb getClass getName sb 1 sb 2 val s sb result println s getClass getName 输出是 scala
分布式分析系统数据一致性的架构设计

我正在重构一个将进行大量计算的分析系统并且我需要一些关于可能的架构设计的想法来解决我面临的数据一致性问题当前架构我有一个基于队列的系统其中不同的请求应用程序创建最终由工作人员使用的消息 Each 请求应用程序将大型计算分解为较小的
Parse.com 失败：ReferenceError

我正在尝试在 Cloud Code 中运行以下解析后台作业 Parse Cloud job sendAlert function sendAlert Parse Push send data content available 1 succ
C 中的 FILE 指针如何工作？

如果我们写 in main FILE p fopen filename anymode 我的问题是什么是p指点文件指针p指向由 C 库处理的结构该结构在给定的打开模式下管理指定文件的 I O 功能你无法先验地判断它指向的是静态分配的
从 Twig 模板名称解析本地文件路径

转换该字符串的编程步骤是什么 AcmeProjectBundle home html twig 进入这个 path Symfony src Acme ProjectBundle Resources views home html twig
Google 地图地理编码不返回国家/地区

我需要一个可靠的方法来获取city state and country place id从纬度和经度我正在尝试使用 Google 地图地理编码但问题是某些坐标不会返回administrative level 1 州或国家地区以这个
sendRedirect 还是 request Dispatch 效率更高？

SendRedirect 或 requestdispatch 哪个应该更优选哪个更有效它们做两件截然不同的事情所以你不能只根据效率来决定发送重定向会将浏览器发送到不同的 URL 该 URL 对浏览器可见你可能想要也可能不想要例如
CSS 样式冲突预防和命名空间技术

假设您正在构建一个名为 Awesome Widget 的概念性小部件并且您希望完全保护它不与周围元素或作为小部件内的内容驻留的子元素发生冲突我们不想要什么 div ul li 解决方案 1 CSS 子组合器使用 CSS 子级组合器选择
ggplot2：将 geom_bar 基线设置为 1 而不是零

我正在尝试制作比率的条形图使用 geom bar 并希望将 x 轴设置为 y 1 因此比率 1 将位于轴上方我可以用 geom point 做类似的事情 ggplot data aes x ratio y reorder place
在 R闪亮应用程序中通过闪亮表输入数据

我想构建一个闪亮的应用程序它获取矩阵数据作为输入并根据对其进行一些操作返回一个表作为输出通过搜索我发现 ShinyTable 包可能很有用我尝试了下面闪亮的代码但结果应用程序显示为灰色并且没有结果 library shinyTa
缺少显示/添加数据源

任何帮助将不胜感激我正在使用 Visual studio 2010 目前我正在 ASP net mvc3 中制作一个项目在这个项目中我有 edmx 数据集并希望使用它来制作 rdlc 报告所以最终我无法在报表设计的数据菜单中获取显
Laravel 9 错误路线 |不支持GET方法

我在通过 API 获取某些客户经过身份验证的用户的列表时遇到问题当我在 Postman 中使用此路线时收到以下错误 Symfony Component HttpKernel Exception MethodNotAllowedHtt
spyder IDE 的 ipython 启动配置

尝试向我的 IPython 配置文件添加一些导入以便当我在 Spyder IDE 中打开内核时它们始终会加载 Spyder 有一个 Qt 界面我认为所以我 a 使用以下命令检查以确保我位于配置文件的正确目录中ipython loca
gcc 交叉编译链接器错误：无法读取符号：无法识别文件格式collect2

我正在尝试使用 gcc eabi 交叉编译包应用程序我已经修改了配置文件以指向所有目标目录但链接器在链接某些共享库时仍然报错确切的错误如下 Linking CXX shared library home aeodrs JAUS 2 1
在 WPF 网格/列表中跨多列的最佳方法？

我有一个在 WPF 中编写的自定义用户控件来显示一些数据我想在列表中显示此用户控件但我还想提供多个列标题与用户控件上的某些属性匹配以便用户可以对用户控件中包含的属性进行排序我不确定解决这个问题的最佳方法我目前有一个显示这些用户控
RxJS 减少不继续

为什么 flatMap 不会导致下游缩减触发我得到的代码如下 handleFiles flatMap files gt Rx Observable from files flatMap file i gt fileReader file
Mysql 升级后 MySQL NOT IN 查询速度变慢

这是一个非常简单的查询 SELECT FROM temp company WHERE number NOT IN SELECT number FROM company 之前需要 15 分钟但那是在缓冲池大小太小的 Mysql 安装上 15

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

Mysql 升级后 MySQL NOT IN 查询速度变慢 的相关文章

随机推荐

热门标签

Mysql 升级后 MySQL NOT IN 查询速度变慢的相关文章