mysql - 优化具有许多左连接的简单查询-6ren

mysql - 优化具有许多左连接的简单查询

转载作者：行者123 更新时间：2023-11-29 05:59:01

我有一个相当大的查询，用于用户搜索“map_item”。

SELECT map_item_name
FROM map_item
LEFT JOIN map_section_item ON map_section_item_item_id = map_item_id
LEFT JOIN map_section ON map_section_id = map_section_item_section_id
LEFT JOIN map_item_flag ON map_item_flag_item_id = map_item_id
LEFT JOIN flag ON flag_id = map_item_flag_flag_id
LEFT JOIN map ON map_id = map_section_map_id
LEFT JOIN place_map ON place_map_map_id = map_id
LEFT JOIN place ON place_id = place_map_place_id
LEFT JOIN place_category ON place_category_place_id = place_id
LEFT JOIN category ON category_id = place_category_category_id
LEFT JOIN review ON review_map_item_id = map_item_id
LEFT JOIN map_price ON map_price_item_id = map_item_id
LEFT JOIN county_list ON place_address_county = county_id

'map_item' 总共有 5399 条记录，所有连接的表都没有太多数据。

如果我在没有左连接的情况下运行此查询 (SELECT map_item_name FROM map_item)，它会按预期在 0.00 秒内返回，但上述带有连接的查询大约需要 10.00 秒。

由于用户可以对搜索应用不同的过滤器，因此查询中需要所有左连接，但是原始查询需要很长时间才能运行(20 秒左右)，并且在去除大部分内容之后上面的部分查询(这只是左连接)甚至需要 18 秒才能运行。

这是查询的解释语句:

+----+-------------+-------------------+--------+----------------------------------+----------------------------------+---------+-----------------------------------------------------------+------+-----------------------------------------------------------------+
| id | select_type | table             | type   | possible_keys                    | key                              | key_len | ref                                                       | rows | Extra                                                           |
+----+-------------+-------------------+--------+----------------------------------+----------------------------------+---------+-----------------------------------------------------------+------+-----------------------------------------------------------------+
|  1 | SIMPLE      | map_item          | ALL    | NULL                             | NULL                             | NULL    | NULL                                                      | 5455 | NULL                                                            |
|  1 | SIMPLE      | map_section_item  | index  | NULL                             | map_section_item_section_id      | 8       | NULL                                                      | 5330 | Using where; Using index; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | map_section       | eq_ref | PRIMARY                          | PRIMARY                          | 4       | bestmeal.map_section_item.map_section_item_section_id     |    1 | NULL                                                            |
|  1 | SIMPLE      | map_item_flag     | ALL    | NULL                             | NULL                             | NULL    | NULL                                                      | 1509 | Using where; Using join buffer (Block Nested Loop)              |
|  1 | SIMPLE      | flag              | eq_ref | PRIMARY                          | PRIMARY                          | 4       | bestmeal.map_item_flag.map_item_flag_flag_id              |    1 | Using index                                                     |
|  1 | SIMPLE      | map               | eq_ref | PRIMARY                          | PRIMARY                          | 4       | bestmeal.map_section.map_section_map_id                   |    1 | Using index                                                     |
|  1 | SIMPLE      | place_map         | index  | NULL                             | branch_map_branch_id             | 8       | NULL                                                      | 1275 | Using where; Using index; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | place             | eq_ref | PRIMARY                          | PRIMARY                          | 4       | bestmeal.place_map.place_map_place_id                     |    1 | NULL                                                            |
|  1 | SIMPLE      | place_category    | ref    | place_category_place_id          | place_category_place_id          | 4       | bestmeal.place.place_id                                   |    1 | Using index                                                     |
|  1 | SIMPLE      | category          | eq_ref | PRIMARY                          | PRIMARY                          | 4       | bestmeal.place_category.place_category_category_id        |    1 | Using index                                                     |
|  1 | SIMPLE      | review            | ref    | review_map_item_id               | review_map_item_id               | 4       | bestmeal.map_item.map_item_id                             |    1 | Using index                                                     |
|  1 | SIMPLE      | map_price         | ref    | map_price_item_id                | map_price_item_id                | 4       | bestmeal.map_item.map_item_id                             |    1 | Using index                                                     |
|  1 | SIMPLE      | county_list       | eq_ref | PRIMARY                          | PRIMARY                          | 4       | bestmeal.place.place_address_county                       |    1 | Using index                                                     |
+----+-------------+-------------------+--------+----------------------------------+----------------------------------+---------+-----------------------------------------------------------+------+-----------------------------------------------------------------+

所有这些连接都是针对索引字段进行的，并且连接的表中没有任何不必要的索引可以用来代替预期的索引。

在优化查询方面我不是专家，但我正在努力找出我可以做些什么来加快查询速度同时保持左连接。我也想不出任何替代解决方案可以在不使用连接的情况下返回相同的结果。

有没有人有任何想法可以帮助我提高此查询的性能或使用不同的、更快的方法完成用户搜索？

编辑要求的表结构:

CREATE TABLE `map_item` (
  `map_item_id` int(11) NOT NULL AUTO_INCREMENT,
  `map_item_account_id` int(11) NOT NULL DEFAULT '0',
  `map_item_category_id` int(11) NOT NULL,
  `map_item_name` varchar(255) DEFAULT NULL,
  `map_item_description` text,
  `map_item_tags` varchar(255) DEFAULT NULL,
  `map_item_type` set('d','f') DEFAULT NULL,
  PRIMARY KEY (`map_item_id`),
  KEY `map_item_account_id` (`map_item_account_id`),
  KEY `map_item_tags` (`map_item_tags`),
  KEY `map_item_category_id` (`map_item_category_id`),
  FULLTEXT KEY `map_item_keyword_search` (`map_item_name`,`map_item_description`,`map_item_tags`),
  FULLTEXT KEY `map_item_name` (`map_item_name`),
  FULLTEXT KEY `map_item_description` (`map_item_description`),
  FULLTEXT KEY `map_item_tags_2` (`map_item_tags`)
) ENGINE=InnoDB AUTO_INCREMENT=5420 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT 

CREATE TABLE `map_section_item` (
  `map_section_item_id` int(11) NOT NULL AUTO_INCREMENT,
  `map_section_item_section_id` int(11) NOT NULL DEFAULT '0',
  `map_section_item_item_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`map_section_item_id`),
  KEY `map_section_item_section_id` (`map_section_item_section_id`,`map_section_item_item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=24410 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT

CREATE TABLE `map_section` (
  `map_section_id` int(11) NOT NULL AUTO_INCREMENT,
  `map_section_map_id` int(11) NOT NULL DEFAULT '0',
  `map_section_map_draft_id` int(11) NOT NULL DEFAULT '0',
  `map_section_column` tinyint(1) NOT NULL DEFAULT '1',
  `map_section_name` varchar(255) DEFAULT NULL,
  `map_section_description` text,
  PRIMARY KEY (`map_section_id`),
  KEY `map_section_map_draft_id` (`map_section_map_draft_id`),
  KEY `map_section_map_id` (`map_section_map_id`),
  FULLTEXT KEY `index_name` (`map_section_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4254 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT

CREATE TABLE `map_item_flag` (
  `map_item_flag_id` int(11) NOT NULL AUTO_INCREMENT,
  `map_item_flag_item_id` int(11) NOT NULL DEFAULT '0',
  `map_item_flag_flag_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`map_item_flag_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1547 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT

CREATE TABLE `flag` (
  `flag_id` int(11) NOT NULL AUTO_INCREMENT,
  `flag_category_id` int(11) NOT NULL DEFAULT '0',
  `flag_name` varchar(255) DEFAULT NULL,
  `flag_description` varchar(255) DEFAULT NULL,
  `flag_img` varchar(255) DEFAULT NULL,
  `flag_order` tinyint(2) NOT NULL DEFAULT '0',
  PRIMARY KEY (`flag_id`),
  KEY `flag_category_id` (`flag_category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT

CREATE TABLE `map` (
  `map_id` int(11) NOT NULL AUTO_INCREMENT,
  `map_account_id` int(11) NOT NULL DEFAULT '0',
  `map_name` varchar(255) DEFAULT NULL,
  `map_description` text,
  `map_type` set('d','f') DEFAULT NULL,
  `map_layout` set('columns','tabs','collapsed') DEFAULT NULL,
  PRIMARY KEY (`map_id`),
  KEY `map_account_id` (`map_account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=138 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT

CREATE TABLE `place_map` (
  `place_map_id` int(11) NOT NULL AUTO_INCREMENT,
  `place_map_place_id` int(11) NOT NULL DEFAULT '0',
  `place_map_map_id` int(11) NOT NULL DEFAULT '0',
  `place_map_active` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`place_map_id`),
  KEY `branch_map_branch_id` (`place_map_place_id`,`place_map_map_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2176 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT

CREATE TABLE `place` (
  `place_id` int(11) NOT NULL AUTO_INCREMENT,
  `place_account_id` int(11) NOT NULL DEFAULT '0',
  `place_name` varchar(120) DEFAULT NULL,
  `place_alias` varchar(255) DEFAULT NULL,
  `place_description` text,
  `place_address_line_one` varchar(100) DEFAULT NULL,
  `place_address_line_two` varchar(100) DEFAULT NULL,
  `place_address_line_three` varchar(100) DEFAULT NULL,
  `place_address_town` varchar(100) DEFAULT NULL,
  `place_address_county` int(11) NOT NULL DEFAULT '0',
  `place_address_postcode` varchar(10) DEFAULT NULL,
  `place_address_latitude` decimal(11,8) DEFAULT NULL,
  `place_address_longitude` decimal(11,8) DEFAULT NULL,
  `place_phone` varchar(20) DEFAULT NULL,
  `place_email` varchar(255) DEFAULT NULL,
  `place_website` varchar(120) DEFAULT NULL,
  `place_flag_initial_email` tinyint(1) NOT NULL DEFAULT '0',
  `place_audit_admin_id` int(11) NOT NULL DEFAULT '0',
  `place_last_audit_datetime` datetime DEFAULT NULL,
  `place_created_by_admin_id` int(11) NOT NULL DEFAULT '0',
  `place_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `place_tried_google` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`place_id`),
  KEY `place_account_id` (`place_account_id`),
  KEY `place_address_county` (`place_address_county`),
  KEY `place_alias` (`place_alias`),
  KEY `place_audit_admin_id` (`place_audit_admin_id`),
  KEY `place_created_by_admin_id` (`place_created_by_admin_id`),
  FULLTEXT KEY `place_name` (`place_name`),
  FULLTEXT KEY `place_keyword_search` (`place_name`,`place_address_town`),
  FULLTEXT KEY `place_address_town` (`place_address_town`)
) ENGINE=InnoDB AUTO_INCREMENT=135167 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT

CREATE TABLE `place_category` (
  `place_category_id` int(11) NOT NULL AUTO_INCREMENT,
  `place_category_place_id` int(11) NOT NULL DEFAULT '0',
  `place_category_category_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`place_category_id`),
  UNIQUE KEY `place_category_place_id` (`place_category_place_id`,`place_category_category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=208987 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT

CREATE TABLE `category` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=168 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT

CREATE TABLE `review` (
  `review_id` int(11) NOT NULL AUTO_INCREMENT,
  `review_user_id` int(11) NOT NULL DEFAULT '0',
  `review_place_id` int(11) NOT NULL DEFAULT '0',
  `review_map_item_id` int(11) NOT NULL DEFAULT '0',
  `review_otm_item_name` varchar(156) DEFAULT NULL,
  `review_headline` varchar(255) DEFAULT NULL,
  `review_message` text,
  `review_rating` tinyint(1) NOT NULL DEFAULT '0',
  `review_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `review_edited_datetime` datetime DEFAULT NULL,
  `review_hidden` tinyint(1) NOT NULL DEFAULT '0',
  `review_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `review_status` set('pending','published','hidden','deleted') NOT NULL,
  PRIMARY KEY (`review_id`),
  KEY `review_map_item_id` (`review_map_item_id`),
  KEY `review_place_id` (`review_place_id`),
  KEY `review_user_id` (`review_user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT

CREATE TABLE `map_price` (
  `map_price_id` int(11) NOT NULL AUTO_INCREMENT,
  `map_price_item_id` int(11) NOT NULL DEFAULT '0',
  `map_price_label` varchar(50) DEFAULT NULL,
  `map_price_value` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`map_price_id`),
  KEY `map_price_item_id` (`map_price_item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5872 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT

CREATE TABLE `county_list` (
  `county_id` int(11) NOT NULL AUTO_INCREMENT,
  `county_country_id` int(11) NOT NULL DEFAULT '0',
  `county_name` varchar(120) DEFAULT NULL,
  `county_alias` varchar(120) DEFAULT NULL,
  PRIMARY KEY (`county_id`),
  KEY `county_alias` (`county_alias`),
  KEY `county_country_id` (`county_country_id`)
) ENGINE=InnoDB AUTO_INCREMENT=142 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT

最佳答案

看看这些行:

LEFT JOIN map_section_item ON map_section_item_item_id = map_item_id

| 1 |简单 | map_section_item |索引 |空 | map_section_item_section_id | 8 |空 | 5330 |在哪里使用；使用索引；使用连接缓冲区( block 嵌套循环)||

通知“5330”。这意味着它必须搜索大约 5330 个项目才能找到所需的行。

使用简单的 INDEX(map_section_item_item_id)，它会直接转到所需的一行(或几行)。这将使查询运行得更快。

彼此重复 JOIN，至少对于那些“行”> 1 的人。

为什么LEFT？每个“正确”的表是否有选择地缺失数据？

附带问题:不要在所有内容前加上表名；太困惑了。

关于mysql - 优化具有许多左连接的简单查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/46667939/

文章推荐： mysql - SQL 联接性能注意事项

文章推荐： java - XACML 标识符是什么意思？

文章推荐： python - 需要 mysqlclient 1.3.3 或更新版本；你有 0.7.11.None

Java正则表达式，简单
我正在努力实现以下目标，假设我有字符串: ( z ) ( A ( z ) ( A ( z ) ( A ( z ) ( A ( z ) ( A ) ) ) ) ) 我想编写一个正则
CSS水平滚动(简单)
给定: 1 2 3 4 5 6
MySQL填充样例数据(简单)
很难说出这里要问什么。这个问题模棱两可、含糊不清、不完整、过于宽泛或夸夸其谈，无法以目前的形式得到合理的回答。如需帮助澄清此问题以便重新打开，visit the help center . 关闭 1
简单、好懂的Svelte实现原理
大家好，我卡颂。 Svelte问世很久了，一直想写一篇好懂的原理分析文章，拖了这么久终于写了。本文会围绕一张流程图和两个Demo讲解，正确的食用方式是用电脑打开本文，跟着流程图、Demo一
Javascript使用正则验证身份证号(简单)
身份证为15位或者18位，15位的全为数字，18位的前17位为数字，最后一位为数字或者大写字母”X“。与之匹配的正则表达式： ?
强大的jquery插件jqeuryUI做网页对话框效果！简单
我们先来最简单的，网页的登录窗口；不过开始之前，大家先下载jquery的插件本人习惯用了vs2008来做网页了，先添加一个空白页这是最简单的的做法。。。先在body里面插入 <
简单、易用的MySQL官方压力测试工具
1、MySQL自带的压力测试工具 Mysqlslap mysqlslap是mysql自带的基准测试工具,该工具查询数据,语法简单,灵活容易使用.该工具可以模拟多个客户端同时并发的向服务器发出
.NET开源、简单、实用的数据库文档生成工具
前言今天大姚给大家分享一款.NET开源（MIT License）、免费、简单、实用的数据库文档（字典）生成工具，该工具支持CHM、Word、Excel、PDF、Html、XML、Markdown等
【Go基础入门教程】Go语言代码风格清晰、简单
Go语言语法类似于C语言，因此熟悉C语言及其派生语言（ C++、 C#、Objective-C 等）的人都会迅速熟悉这门语言。 C语言的有些语法会让代码可读性降低甚至发生歧义。Go语言在C语言的
FFMpeg 简单/快速转换视频文件不适用于任何视频
我正在使用快速将 mkv 转换为 mp4 ffmpeg 命令 ffmpeg -i test.mkv -vcodec copy -acodec copy new.mp4 但不适用于任何 mkv 文件，当
VBA 计算具有特定名称的工作表数(简单)
我想计算我的工作簿中的工作表数量，然后从总数中减去特定的工作表。我错过了什么？这给了我一个对象错误: wsCount = ThisWorkbook.Sheets.Count - ThisWorkboo
Perl 配置::简单
我有一个 perl 文件，用于查看文件夹中是否存在 ini。如果是，它会从中读取，如果不是，它会根据我为它制作的模板创建一个。我在 ini 部分使用 Config::Simple。我的问题是，如果
ios - 如何访问iOS通知中传递的数据(简单)？
尝试让一个 ViewController 通过标准 Cocoa 通知与另一个 ViewController 进行通信。编写了一个简单的测试用例。在我最初的 VC 中，我将以下内容添加到 viewDi
optimization - (简单？)折线图的标签放置
我正在绘制高程剖面图，显示沿路径的高程增益/损失，类似于下面的: Sample Elevation Profile with hand-placed labels http://img38.image
javascript - 使用JS隐藏div(简单)
嗨，所以我需要做的是最终让 regStart 和 regPage 根据点击事件交替可见性，我不太担心编写 JavaScript 函数，但我根本无法让我的 regPage 首先隐藏。这是我的代码。请简单
c++ - 简单 for 循环中的大量时间损失
我有一个非常简单的程序来测量一个函数花费了多少时间。 #include #include #include struct Foo { void addSample(uint64_t s)
JavaScript 简单 BitConverter
我需要为 JavaScript 制作简单的 C# BitConverter。我做了一个简单的BitConverter class BitConverter{ constructor(){} GetBy
javascript - 简单 for 循环出现意外标记错误？
已关闭。这个问题是 not reproducible or was caused by typos 。目前不接受答案。这个问题是由拼写错误或无法再重现的问题引起的。虽然类似的问题可能是 on-top
group-by - 简单.数据分组依据
我是 Simple.Data 的新手。但我很难找到如何进行“分组依据”。我想要的是非常基本的。表格看起来像: +________+ | cards | +________+ | id |
Javascript 简单 UDF
我现在正在开发一个 JS UDF，它看起来遵循编码。通常情况下，由于循环计数为 2，Alert Msg 会出现两次。我想要的是即使循环计数为 3，Alert Msg 也只会出现一次。任何想法都

行者123

个人简介

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

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

mysql - 优化具有许多左连接的简单查询