mysql - 缓慢的 MySQL "INNER JOIN"-6ren

mysql - 缓慢的 MySQL "INNER JOIN"

转载作者：IT老高更新时间：2023-10-28 23:45:17

在一个网站上，我正在使用 django 发出一些请求:

django 行:

CINodeInventory.objects.select_related().filter(ci_class__type='equipment',company__slug=self.kwargs['company'])

生成这样的 MySQL 查询:

SELECT *
FROM `inventory_cinodeinventory`
INNER JOIN `ci_cinodeclass` ON ( `inventory_cinodeinventory`.`ci_class_id` = `ci_cinodeclass`.`class_name` )
INNER JOIN `accounts_companyprofile` ON ( `inventory_cinodeinventory`.`company_id` = `accounts_companyprofile`.`slug` )
INNER JOIN `accounts_companysite` ON ( `inventory_cinodeinventory`.`company_site_id` = `accounts_companysite`.`slug` )
INNER JOIN `accounts_companyprofile` T5 ON ( `accounts_companysite`.`company_id` = T5.`slug` )
WHERE (
`ci_cinodeclass`.`type` = 'equipment'
AND `inventory_cinodeinventory`.`company_id` = 'thecompany'
)
ORDER BY `inventory_cinodeinventory`.`name` ASC

问题是主表中只有 40 000 个条目，处理时间为 0.5 秒。

我检查了所有索引，创建了排序或连接所需的索引:我仍然有问题。

有趣的是，如果我将最后一个 INNER JOIN 替换为 LEFT JOIN，请求速度会快 10 倍!不幸的是，由于我使用 django 进行请求，我无法访问它生成的 SQL 请求(我不想自己执行原始 SQL)。

对于最后一个作为“INNER JOIN”的连接，EXPLAIN 给出:

+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+------------------------------------+---------+------------------------------------------------+-------+---------------------------------+
| id | select_type | table                     | type   | possible_keys                                                                                            | key                                | key_len | ref                                            | rows  | Extra                           |
+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+------------------------------------+---------+------------------------------------------------+-------+---------------------------------+
|  1 | SIMPLE      | accounts_companyprofile   | const  | PRIMARY                                                                                                  | PRIMARY                            | 152     | const                                          |     1 | Using temporary; Using filesort |
|  1 | SIMPLE      | inventory_cinodeinventory | range  | inventory_cinodeinventory_41ddcf59,inventory_cinodeinventory_543518c6,inventory_cinodeinventory_14fe63e9 | inventory_cinodeinventory_543518c6 | 152     | NULL                                           | 42129 | Using where                     |
|  1 | SIMPLE      | T5                        | ALL    | PRIMARY                                                                                                  | NULL                               | NULL    | NULL                                           |     3 | Using join buffer               |
|  1 | SIMPLE      | accounts_companysite      | eq_ref | PRIMARY,accounts_companysite_543518c6                                                                    | PRIMARY                            | 152     | cidb.inventory_cinodeinventory.company_site_id |     1 | Using where                     |
|  1 | SIMPLE      | ci_cinodeclass            | eq_ref | PRIMARY                                                                                                  | PRIMARY                            | 92      | cidb.inventory_cinodeinventory.ci_class_id     |     1 | Using where                     |
+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+------------------------------------+---------+------------------------------------------------+-------+---------------------------------+

对于最后一次加入“LEFT JOIN”，我得到了:

+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+---------+---------+------------------------------------------------+------+-------------+
| id | select_type | table                     | type   | possible_keys                                                                                            | key     | key_len | ref                                            | rows | Extra       |
+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+---------+---------+------------------------------------------------+------+-------------+
|  1 | SIMPLE      | accounts_companyprofile   | const  | PRIMARY                                                                                                  | PRIMARY | 152     | const                                          |    1 |             |
|  1 | SIMPLE      | inventory_cinodeinventory | index  | inventory_cinodeinventory_41ddcf59,inventory_cinodeinventory_543518c6,inventory_cinodeinventory_14fe63e9 | name    | 194     | NULL                                           |  173 | Using where |
|  1 | SIMPLE      | accounts_companysite      | eq_ref | PRIMARY                                                                                                  | PRIMARY | 152     | cidb.inventory_cinodeinventory.company_site_id |    1 |             |
|  1 | SIMPLE      | T5                        | eq_ref | PRIMARY                                                                                                  | PRIMARY | 152     | cidb.accounts_companysite.company_id           |    1 |             |
|  1 | SIMPLE      | ci_cinodeclass            | eq_ref | PRIMARY                                                                                                  | PRIMARY | 92      | cidb.inventory_cinodeinventory.ci_class_id     |    1 | Using where |
+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+---------+---------+------------------------------------------------+------+-------------+

似乎对于“INNER JOIN”的情况，MySQL 没有找到 T5 连接的任何索引:为什么？

分析给出:

starting                            0.000011
checking query cache for query  0.000086
Opening tables                  0.000014
System lock                     0.000005
Table lock                          0.000052
init                            0.000064
optimizing                          0.000021
statistics                          0.000180
preparing                           0.000024
Creating tmp table                  0.000308
executing                           0.000003
Copying to tmp table            0.353414   !!!
Sorting result                  0.037244
Sending data                    0.035168
end                             0.000005
removing tmp table                  0.550974   !!!
end                             0.000009
query end                           0.000003
freeing items                   0.000113
storing result in query cache   0.000009
logging slow query                  0.000002
cleaning up                     0.000004

看来，mysql有一个使用临时表的步骤。此步骤不会发生在 LEFT JOIN 中，只会发生在 INNER JOIN 中。我试图通过在查询中包含“强制加入索引”来避免这种情况，但它没有帮助......

连接表是:

CREATE TABLE IF NOT EXISTS `accounts_companysite` (
  `slug` varchar(50) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `deleted` tinyint(1) NOT NULL,
  `company_id` varchar(50) NOT NULL,
  `name` varchar(128) NOT NULL,
  `address` longtext NOT NULL,
  `city` varchar(64) NOT NULL,
  `zip_code` varchar(6) NOT NULL,
  `state` varchar(32) NOT NULL,
  `country` varchar(2) DEFAULT NULL,
  `phone` varchar(20) NOT NULL,
  `fax` varchar(20) NOT NULL,
  `more` longtext NOT NULL,
  PRIMARY KEY (`slug`),
  KEY `accounts_companysite_543518c6` (`company_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `accounts_companyprofile` (
  `slug` varchar(50) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `deleted` tinyint(1) NOT NULL,
  `name` varchar(128) NOT NULL,
  `address` longtext NOT NULL,
  `city` varchar(64) NOT NULL,
  `zip_code` varchar(6) NOT NULL,
  `state` varchar(32) NOT NULL,
  `country` varchar(2) DEFAULT NULL,
  `phone` varchar(20) NOT NULL,
  `fax` varchar(20) NOT NULL,
  `contract_id` varchar(32) NOT NULL,
  `more` longtext NOT NULL,
  PRIMARY KEY (`slug`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `inventory_cinodeinventory` (
  `uuid` varchar(36) NOT NULL,
  `name` varchar(64) NOT NULL,
  `synopsis` varchar(64) NOT NULL,
  `path` varchar(255) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `deleted` tinyint(1) NOT NULL,
  `root_id` varchar(36) DEFAULT NULL,
  `parent_id` varchar(36) DEFAULT NULL,
  `order` int(11) NOT NULL,
  `ci_class_id` varchar(30) NOT NULL,
  `data` longtext NOT NULL,
  `serial` varchar(64) NOT NULL,
  `company_id` varchar(50) NOT NULL,
  `company_site_id` varchar(50) NOT NULL,
  `vendor` varchar(48) NOT NULL,
  `type` varchar(64) NOT NULL,
  `model` varchar(64) NOT NULL,
  `room` varchar(30) NOT NULL,
  `rack` varchar(30) NOT NULL,
  `rack_slot` varchar(30) NOT NULL,
  PRIMARY KEY (`uuid`),
  KEY `inventory_cinodeinventory_1fb5ff88` (`root_id`),
  KEY `inventory_cinodeinventory_63f17a16` (`parent_id`),
  KEY `inventory_cinodeinventory_41ddcf59` (`ci_class_id`),
  KEY `inventory_cinodeinventory_543518c6` (`company_id`),
  KEY `inventory_cinodeinventory_14fe63e9` (`company_site_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

我还尝试通过添加 my.cnf 来调整 MySQL:

join_buffer_size        = 16M
tmp_table_size          = 160M
max_seeks_for_key       = 100

...但它没有帮助。

使用 django，使用 Postgresql 代替 Mysql 很容易，所以我试了一下:在 db 中使用相同的查询和相同的数据，postgres 比 Mysql 快得多:使用 INNER JOIN 时要快 x10(分析表明它使用与Mysql不同的索引)

你知道为什么我的 MySQL INNER JOIN 这么慢吗？

编辑 1:

经过一些测试，我将问题归结为这个请求:

SELECT *
FROM `inventory_cinodeinventory`
INNER JOIN `accounts_companyprofile` ON `inventory_cinodeinventory`.`company_id` = `accounts_companyprofile`.`slug`
ORDER BY `inventory_cinodeinventory`.`name` ASC

这个请求很慢，我不明白为什么。没有 'ORDER BY' 子句，它很快，但没有它，虽然，名称索引已设置:

CREATE TABLE IF NOT EXISTS `inventory_cinodeinventory` (
  `uuid` varchar(36) NOT NULL,
  `name` varchar(64) NOT NULL,
  `synopsis` varchar(64) NOT NULL,
  `path` varchar(255) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `deleted` tinyint(1) NOT NULL,
  `root_id` varchar(36) DEFAULT NULL,
  `parent_id` varchar(36) DEFAULT NULL,
  `order` int(11) NOT NULL,
  `ci_class_id` varchar(30) NOT NULL,
  `data` longtext NOT NULL,
  `serial` varchar(64) NOT NULL,
  `company_id` varchar(50) NOT NULL,
  `company_site_id` varchar(50) NOT NULL,
  `vendor` varchar(48) NOT NULL,
  `type` varchar(64) NOT NULL,
  `model` varchar(64) NOT NULL,
  `room` varchar(30) NOT NULL,
  `rack` varchar(30) NOT NULL,
  `rack_slot` varchar(30) NOT NULL,
  PRIMARY KEY (`uuid`),
  KEY `inventory_cinodeinventory_1fb5ff88` (`root_id`),
  KEY `inventory_cinodeinventory_63f17a16` (`parent_id`),
  KEY `inventory_cinodeinventory_41ddcf59` (`ci_class_id`),
  KEY `inventory_cinodeinventory_14fe63e9` (`company_site_id`),
  KEY `inventory_cinodeinventory_543518c6` (`company_id`,`name`),
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

编辑 2:

可以使用“FORCE INDEX FOR ORDER BY (name)”解决先前的请求。不幸的是，这个提示不适用于我主题中的初始请求...

编辑 3:

我通过将 'uuid' 主键从 varchar 替换为整数来重建数据库:它根本没有帮助......坏消息。

编辑 4:

我试过 Mysql 5.5.20 :不是更好。对于这个特定请求，Postgresql 8.4 的速度提高了 10 倍。

我修改了一点resquest(删除了T5加入):

SELECT *
FROM `inventory_cinodeinventory`
INNER JOIN `ci_cinodeclass` ON ( `inventory_cinodeinventory`.`ci_class_id` = `ci_cinodeclass`.`class_name` )
INNER JOIN `accounts_companyprofile` ON ( `inventory_cinodeinventory`.`company_id` = `accounts_companyprofile`.`slug` )
INNER JOIN `accounts_companysite` ON ( `inventory_cinodeinventory`.`company_site_id` = `accounts_companysite`.`slug` )
WHERE (
`ci_cinodeclass`.`type` = 'equipment'
AND `inventory_cinodeinventory`.`company_id` = 'thecompany'
)
ORDER BY `inventory_cinodeinventory`.`name` ASC

这工作正常，但我还有一些其他要求，只是在这个技巧不起作用的地方有点不同。

实际上，经过搜索，似乎只要您将两个具有“很多共同点”的表连接起来，也就是说，右表的一半行可以连接到左表的行(它是我的情况):Mysql 更喜欢使用表扫描而不是索引:我发现某处更快(!!)

最佳答案

您真正的问题在于您的第一个解释中的第二行:

+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+------------------------------------+---------+------------------------------------------------+-------+---------------------------------+
| id | select_type | table                     | type   | possible_keys                                                                                            | key                                | key_len | ref                                            | rows  | Extra                           |
+----+-------------+---------------------------+--------+----------------------------------------------------------------------------------------------------------+------------------------------------+---------+------------------------------------------------+-------+---------------------------------+   
|  1 | SIMPLE      | inventory_cinodeinventory | range  | inventory_cinodeinventory_41ddcf59,inventory_cinodeinventory_543518c6,inventory_cinodeinventory_14fe63e9 | inventory_cinodeinventory_543518c6 | 152     | NULL                                           | 42129 | Using where                     |

您正在使用此 WHERE 子句分析 42129 行:

AND `inventory_cinodeinventory`.`company_id` = 'thecompany'

如果您还没有，您应该在inventory_cinodeinventory 上为(company_id, name)

建立一个索引

即

ALTER TABLE `inventory_cinodeinventory`
  ADD INDEX `inventory_cinodeinventory__company_id__name` (`company_id`, `name`);

这样您的 WHERE 和 ORDER BY 子句就不会发生冲突，从而导致错误的索引选择，这似乎正在发生。

如果您确实已经有这些列的索引，我会建议按此顺序运行 OPTIMIZE TABLE inventory_cinodeinventory; 以查看它是否让 MySQL 使用正确的索引。

一般来说，你有一个更大的问题(我认为这是由于 Django 的设计，但我缺乏使用该框架的经验)，因为你有这些巨大的键。 EXPLAIN 中的所有键的长度均为 152 和 92 字节。这会产生更大的索引，这意味着更多的磁盘访问，这意味着更慢的查询。理想情况下，主键和外键是 int 或非常短的 varchar 列(例如 varchar(10))。这些键的 varchar(50) 将在您的数据库响应时间上放置一个重要的常数倍数。

关于mysql - 缓慢的 MySQL "INNER JOIN"，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/9062987/

文章推荐： mysql - 如何为所有远程用户要求 SSL

文章推荐： php - 在多维数组上使用 array_intersect

文章推荐： namespaces - PHP 命名空间和内置类的问题，如何解决？

文章推荐： php - 通知 : Array to string conversion in

join - 从一个“join”表到另一个“join”表的SqlAlchemy关系
我正在测试设置SQLAlchemy以映射现有数据库。这个数据库是很久以前自动建立的，它是由我们不再使用的先前的第三方应用程序创建的，因此 undefined 某些预期的事情，例如外键约束。该软件将管理
mysql - INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN 有什么区别？
这个问题在这里已经有了答案: What is the difference between "INNER JOIN" and "OUTER JOIN"? (28 个答案) 关闭 7 年前。 INNE
mysql - INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN 有什么区别？
这个问题在这里已经有了答案: What is the difference between "INNER JOIN" and "OUTER JOIN"? (29 个回答) 关闭7年前. INNER J
join - Hive:LEFT JOIN 与 JOIN 在 ON 子句中使用过滤器给出不同的结果
假设有两个表: table1.c1 table1.c2 1 1 A 2 1 B 3 1 C 4 2
join - Hive:LEFT JOIN 与 JOIN 在 ON 子句中使用过滤器给出不同的结果
假设有两个表: table1.c1 table1.c2 1 1 A 2 1 B 3 1 C 4 2
数据库Left join , Right Join, Inner Join 的相关内容，非常实用
一.先看一些最简单的例子例子 Table A aid adate 1 a1 2&nb
SQL 外链接操作小结 inner join left join right join
数据库操作语句 7. 外连接——交叉查询 7.1 查询 7.2 等值连接 7.3 右外
ruby-on-rails - :joins | change behavior inner join to left join
我有两个表 'users' 和 'lms_users' class LmsUser belongs_to :user end class User has_one :lms_user
ruby-on-rails - 首先使用 `joins()` 进行 INNER JOIN 然后是下一个表的 LEFT JOIN
我试图避免在 Rails 中对我的 joins 进行字符串插值，因为我注意到将查询器链接在一起时灵活性会降低。也就是说，我觉得 joins(:table1) 比 joins('inner join
ruby-on-rails - Rails ActiveRecord :joins with LEFT JOIN instead of INNER JOIN
我有这个代码 User.find(:all, :limit => 10, :joins => :user_points, :select => "users.*, co
join - Doctrine join 绕过延迟加载
我刚刚开始探索 Symfony2，我很惊讶它拥有如此多的强大功能。我开始做博客教程在: http://tutorial.symblog.co.uk/ 但使用的是 2.1 版而不是 2.0 我的问题是我
SQL JOIN 和不同类型的 JOIN
什么是 SQL JOIN什么是不同的类型？最佳答案插图来自 W3schools : 关于SQL JOIN 和不同类型的 JOIN，我们在Stack Overflow上找到一个类似的问题： http
join - Hive Join 返回零记录
我有两个 Hive 表，我正在尝试加入它们。这些表没有被任何字段聚集或分区。尽管表包含公共(public)键字段的记录，但连接查询始终返回 0 条记录。所有数据类型都是“字符串”数据类型。连接查询很
join - solr join - 返回父子文档
我正在使用 Solr 的(4.0.0-beta)连接功能来查询包含具有父/子关系的文档的索引。连接查询效果很好，但我只能在搜索结果中获得父文档。我相信这是预期的行为。但是，是否有可能在搜索结果中同时
join - 三向关联查询/has_many :through/join
我正在使用可用的指南/api/书籍自学 Rails，但我无法理解通过三种方式/嵌套 has_many :through 关联进行的连接。我有用户与组相关联:通过成员(member)资格。我在多对多
SQL JOIN 和不同类型的 JOIN
什么是 SQL JOIN，有哪些不同的类型？最佳答案插图来自 W3schools : 关于SQL JOIN 和不同类型的 JOIN，我们在Stack Overflow上找到一个类似的问题： htt
Mysql join 使所有 join
我正在尝试访问数据库的两个表。在商店里，我保留了一个事件列表，其中包含 Table Event id, name,datei,houri, dateF,Hourf ,capacity, age ,de
mysql - 复杂连接(Joining Joins)
我有 4 个表:booking、address、search_address 和 search_address_log 表:(相关列) 预订:(pickup_address_id, dropoff_a
Joining after join with yq(在与yq连接之后进行连接)
我在YML中有以下结构：。我正试着创造一个这样的结构：。作业名称和脚本用~分隔，作业用；分隔。。我可以使用以下命令使其正常工作。然而，我想知道是否可以用一个yq表达式来完成，而不是通过管道再次使用yq
Joining after join with yq(在与yq连接之后进行连接)
我在YML中有以下结构：。我正试着创造一个这样的结构：。作业名称和脚本用~分隔，作业用；分隔。。我可以使用以下命令使其正常工作。然而，我想知道是否可以用一个yq表达式来完成，而不是通过管道再次使用yq

IT老高

个人简介

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

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

mysql - 缓慢的 MySQL "INNER JOIN"