登录
当前位置： 首页 > 面试题库 >

MySQL升级后，MySQL NOT IN查询的速度大大降低

长孙景天
2023-03-14
问题内容：

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

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，但是它们匹配的数字是数字，并且两者都是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 -------

SHOW CREATE的：公司

   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

temp_company：

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 |
+-------------------------------+----------+

问题答案：

事实证明，问题在于temp_company表号字段没有像Company表那样将ascii_bin设置为其排序规则。

如MySQL论坛（http://forums.mysql.com/read.php?24,603620,603732#msg-603732）所述，具有不同排序规则或字符集的varchar字段被视为具有不同的类型，因此它们之间不能使用索引。

补救措施是在temp_company表的number字段上设置相同的排序规则。然后，查询花费了3.3秒（使用左联接方法花费了2.7秒）。



 类似资料：
  • 在会话中加载实体后，休眠查询的速度大大降低

    问题内容： 我正在使用Hibernate EntityManager，并且在我的Hibernate查询中遇到了奇怪的变慢。看一下这段代码： 这在我的机器上运行大约750毫秒。考虑到它只是选择一个恒定的整数，但并不能很快，但是可以接受。在启动查询之前，在EntityManager会话中加载任何实体的时刻就出现了我的问题： em.find（）速度很快，但是运行时1000个查询增加了十倍多，达到约10秒

  • 大查询集群不能降低查询成本

    我对BigQuery中的集群表（带有日期分区）有一个问题。我有一个由名为entity_id的列集群的表。问题是，我希望在进行由这些集群列过滤的查询时看到字节读取减少，但根据BigQuery Web UI，它无论如何都在进行全扫描。 例如：＜br＞从＜code＞project.usersDataset中选择*。users_cluster其中entity_id='405849241'限制为1000 返

  • mysql响应查询速度慢

    `public class Main{private static Connection connect=null；private static Statement PreparedStatement=null；private static ResultSet ResultSet=null；

  • 大幅提升MySQL中InnoDB的全表扫描速度的方法

    本文向大家介绍大幅提升MySQL中InnoDB的全表扫描速度的方法，包括了大幅提升MySQL中InnoDB的全表扫描速度的方法的使用技巧和注意事项，需要的朋友参考一下  在 InnoDB中更加快速的全表扫描  一般来讲,大多数应用查询的时候都会用索引，查找很少的几行数据（主键查找或百行内的查询），但有时候我们需要全表查询。典型的全表扫描就是逻辑备份  (mysqldump) 和 online sc

  • 使用maxPageSize和maxResultSize进行Dynamo Db查询，降低查询速度

    根据AWS文档，我正在使用dynamo db分页： -- -- 令人惊讶的是，当我不设置任何页面大小（1）时，DynamoDb查询速度明显更快，根据文档，情况应该不是这样。我假设dynamo db在默认情况下返回所有结果，如果我们不指定pageSize。

  • MySql查询，选择大于

    问题内容： 我有一个名为faq_questions的表，其结构如下： 我试图建立一个给定排序顺序的查询，选择具有最高排序顺序的行。 例子： 好的，假设我为已知的排序顺序（id 4）输入了5，我需要它返回ID为3的行。由于不能保证sort_order是连续的，所以我不能只选择known_sort_order + 1。 谢谢！ 问题答案： 似乎太简单了，但是看起来像您所需要的：

相关阅读

MYSQL查询执行速度非常慢如何处理大型mysql查询Django order_by查询集，升序和降序mysql创建表查询中的行大小太大错误MySQL查询按父级然后子级排序

相关文章

MySQL条件查询OrientDB升级MySQL慢查询日志MySQL查询数据表百度/滴滴后端/大数据实习面经

相关问答

MySQL查询优化-提高速度和效率mysql - 如何针对MySQL大表JOIN查询索引与查询优化？JPA条件等效查询子查询最大或最大MongoDB查询大集合Flyway H2和MySql升级后不匹配

相关工具

升级树cheat-linux (升级版)大众ERP加速度传感器的使用放置大地

相关文档

Java 函数速查好用的中文速查表Linux API 速查手册汇编速查手册Laravel 5.1 LTS 速查手册
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.