MySQL LEFT JOIN 或 WHERE IN SUBQUERY-6ren

MySQL LEFT JOIN 或 WHERE IN SUBQUERY

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

我需要一条建议，现在构建一个应用程序，我需要在相当大的表上运行一些查询，可能以非常频繁的速度运行，所以我正在尝试获得最佳性能明智的方法。

我有以下 2 个表:

专辑:

+---------------+--------------+------+-----+---------+----------------+
|     Field     |     Type     | Null | Key | Default |     Extra      |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| eventid       | int(11)      | NO   | MUL | NULL    |                |
| album         | varchar(200) | NO   |     | NULL    |                |
| filename      | varchar(200) | NO   |     | NULL    |                |
| obstacle_time | time         | NO   |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

和关键词:

+-------------+--------------+------+-----+---------+----------------+
|    Field    |     Type     | Null | Key | Default |     Extra      |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| eventid     | int(11)      | NO   | MUL | NULL    |                |
| filename    | varchar(200) | NO   |     | NULL    |                |
| bibnumbers  | varchar(200) | NO   |     | NULL    |                |
| gender      | varchar(20)  | YES  |     | NULL    |                |
| top_style   | varchar(20)  | YES  |     | NULL    |                |
| pants_style | varchar(20)  | YES  |     | NULL    |                |
| other       | varchar(20)  | YES  |     | NULL    |                |
| cap         | varchar(200) | NO   |     | NULL    |                |
| tshirt      | varchar(200) | NO   |     | NULL    |                |
| pants       | varchar(200) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

两个表都声明了一个 unique_index，它是 eventid+filename 列的约束。

两个表都包含一些图片的信息，但专辑表是即时可用的(只要我有图像)，而关键字表通常在手动标记图像几天后可用

一旦启用标记，我将让人们搜索各种东西，但由于结果可能非常大(最多 10.000 或更多)，我只以小块显示它们，因此浏览器无法获取试图加载大量图像而被杀死，因此我的服务器将被大量查询请求击中(每次访问者滚动到页面底部时，ajax 查询将返回下一个图像 block )。

现在我的问题是，以下哪个查询的性能更好:

SELECT `albums`.`filename`,`basket`.`id`,`albums`.`id`,`obstacle_time`
FROM `albums`
LEFT JOIN `basket`
    ON `basket`.`eventid` = `albums`.`eventid`
        AND `basket`.`fileid` = `albums`.`id`
        AND `basket`.`visitor_id` = 1
LEFT JOIN `keywords`
    ON `keywords`.`eventid` = `albums`.`eventid`
        AND `albums`.`filename` = `keywords`.`filename`
WHERE
    `albums_2015`.`eventid` = 1
    AND `album` LIKE '%string%'
    AND `obstacle_time` >= '08:00:00'
    AND `obstacle_time` <= '14:11:10'
    AND `gender` = 1
    AND `top_style` REGEXP '[[:<:]]0[[:>:]]|[[:<:]]1[[:>:]]'
    AND `cap` = '2'
    AND `tshirt` = '1'
    AND `pants` = '3'
ORDER BY `obstacle_time`
LIMIT X, 10

或者在 WHERE 中使用 IN CLAUSE，例如:

SELECT `albums`.`filename`,`basket`.`id`,`albums`.`id`,`obstacle_time` 
FROM `albums` 
LEFT JOIN `basket` 
    ON `basket`.`eventid` = `albums`.`eventid` 
        AND `basket`.`fileid` = `albums`.`id` 
        AND `basket`.`visitor_id` = 1 
WHERE 
    `albums_2015`.`eventid` = 1 
    AND `album` LIKE '%string%' 
    AND `obstacle_time` >= '08:00:00' 
    AND `obstacle_time` <= '14:11:10' 
    AND `filename` IN (
        SELECT `filename` 
        FROM `keywrods` 
        WHERE
            `eventid` = 1 
            AND `gender` = 1 
            AND `top_style` REGEXP '[[:<:]]0[[:>:]]|[[:<:]]1[[:>:]]' 
            AND `cap` = '2' 
            AND `tshirt` = '1' 
            AND `pants` = '3'
    )
ORDER BY `obstacle_time`
LIMIT X, 10

我曾研究过类似的问题，但无法弄清楚哪个是最佳行动方案。

目前我的理解是:

使用 LEFT JOIN 可以利用 INDEXING，但是!!!如果我使用它，即使我只需要一个小得多的结果集，我也会得到一个完整的表连接，所以连接数千行然后过滤掉其中的大部分几乎是一种浪费。
使用 IN 和子查询没有索引？？？我对此不是 100% 确定，我使用的是 MySQL 5.6，据我所知，因为 5.6 甚至子查询都会自动索引我的 MySQL。我认为这种方法在结果被显着过滤时有好处，不确定如果子查询将返回所有可能的文件名是否会有任何好处。

作为脚注问题:

我是否应该考虑在第一次查询时将整个结果返回给客户端并使用客户端 (HTML) 技术逐渐加载图像而不是每次都重新查询服务器？
我是否应该考虑将 2 个表合并为 1 个表，这会对性能产生多大影响？ (由于各种原因可能会很棘手，在问题中没有位置)

谢谢。

编辑 1

解释 JOIN 查询:

+----+-------------+---------------+--------+---------------+--------------+---------+----------------------------------------+------+----------------------------------------------------+
| id | select_type |     table     |  type  | possible_keys |     key      | key_len |                  ref                   | rows |                       Extra                        |
+----+-------------+---------------+--------+---------------+--------------+---------+----------------------------------------+------+----------------------------------------------------+
|  1 | SIMPLE      | albums_2015   | ref    | unique_index  | unique_index | 4       | const                                  | 6475 | Using where; Using temporary; Using filesort       |
|  1 | SIMPLE      | basket        | ALL    | NULL          | NULL         | NULL    | NULL                                   |    2 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | keywords_2015 | eq_ref | unique_index  | unique_index | 206     | const,mybibnumber.albums_2015.filename |    1 | Using index                                        |
+----+-------------+---------------+--------+---------------+--------------+---------+----------------------------------------+------+----------------------------------------------------+

在何处使用:

+----+-------------+---------------+--------+---------------+--------------+---------+----------------------------------------+------+----------------------------------------------------+--+
| id | select_type |     table     |  type  | possible_keys |     key      | key_len |                  ref                   | rows |                       Extra                        |  |
+----+-------------+---------------+--------+---------------+--------------+---------+----------------------------------------+------+----------------------------------------------------+--+
|  1 | SIMPLE      | albums_2015   | ref    | unique_index  | unique_index | 4       | const                                  | 6475 | Using where; Using temporary; Using filesort       |  |
|  1 | SIMPLE      | keywords_2015 | eq_ref | unique_index  | unique_index | 206     | const,mybibnumber.albums_2015.filename |    1 | Using where                                        |  |
|  1 | SIMPLE      | basket        | ALL    | NULL          | NULL         | NULL    | NULL                                   |    2 | Using where; Using join buffer (Block Nested Loop) |  |
+----+-------------+---------------+--------+---------------+--------------+---------+----------------------------------------+------+----------------------------------------------------+--+

编辑 2

我无法设置 SQL Fiddler(不断收到出错信息)，所以我在我的一台服务器上创建了一个测试数据库。

地址:http://188.165.217.185/phpmyadmin/ ，用户:temp_test，通过:test_temp

我还在构建整个东西，我还没有填写所有的值，比如 top_style、pants_style 等，所以对测试场景更合适的查询是:

在哪里:

SELECT `albums_2015`.`filename`, 
       `albums_2015`.`id`, 
       `obstacle_time` 
FROM   `albums_2015` 
WHERE  `albums_2015`.`eventid` = 1 
       AND `album` LIKE '%' 
       AND `obstacle_time` >= '08:00:00' 
       AND `obstacle_time` <= '14:11:10' 
       AND `filename` IN (SELECT `filename` 
                          FROM   `keywords_2015` 
                          WHERE  eventid = 1 
                                 AND 
               `bibnumbers` REGEXP '[[:<:]]113[[:>:]]|[[:<:]]106[[:>:]]') 
ORDER  BY `obstacle_time` 
LIMIT  0, 10

左连接

SELECT `albums_2015`.`filename`,`albums_2015`.`id`,`obstacle_time`
    FROM `albums_2015`
        LEFT JOIN `keywords_2015`
        ON `keywords_2015`.`eventid` = `albums_2015`.`eventid`
            AND `albums_2015`.`filename` = `keywords_2015`.`filename`
    WHERE
        `albums_2015`.`eventid` = 1
        AND `album` LIKE '%'
        AND `obstacle_time` >= '08:00:00'
        AND `obstacle_time` <= '14:11:10'

        AND `bibnumbers` REGEXP '[[:<:]]113[[:>:]]|[[:<:]]106[[:>:]]'

    ORDER BY `obstacle_time`
    LIMIT 0, 10