mysql 使用左连接/子查询的慢速SQL(100.000条记录)

jv4diomz  于 2023-06-28  发布在  Mysql
关注(0)|答案(1)|浏览(137)

我有一个多语种网站，实现了游戏搜索和排序功能（我删除了最后一个问题，因为里面有很多不必要的信息;是的，现在的“游戏”代替了“书籍”，因为我创建了一个新的测试数据库）。通过以下查询执行搜索：

SELECT games.id AS id 
FROM games 
LEFT JOIN games_titles ON games.id = games_titles.game_id 
WHERE 1
  AND EXISTS (SELECT games_devs.game_id 
              FROM games_devs
              WHERE games_devs.game_id = games.id 
                AND games_devs.dev IN (...))
  AND EXISTS (SELECT games_publishers.game_id 
              FROM games_publishers 
              WHERE games_publishers.game_id = games.id 
                AND games_publishers.publisher IN (...))
  AND games_titles.lang=1
ORDER BY games_titles.title

请求被触发超过2.5秒，没有ORDER BY -超过1秒。搜索条件中可能还有几个参数，因为我从post请求中接受它们并将它们转换为字符串。但本质是一样的。现在数据库中有100，000条记录，过滤器的工作速度非常慢。仅当没有子查询和联接时，才快速执行该操作。以下是更详细的信息（ON和WHERE中使用的所有字段都使用BTREE进行索引）：
解释（不按字母排序）：

+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+
| id | select_type  | table            | type   | possible_keys            | key          | key_len | ref                                | rows  |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+
|  1 | PRIMARY      | games_titles     | ref    | game_id_index,lang_index | lang_index   | 4       | const                              | 98995 |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+
|  1 | PRIMARY      | games            | eq_ref | PRIMARY                  | PRIMARY      | 4       | test_db.games_titles.game_id       | 1     |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+
|  1 | PRIMARY      | <subquery2>      | eq_ref | distinct_key             | distinct_key | 4       | func                               | 1     |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+
|  1 | PRIMARY      | <subquery3>      | eq_ref | distinct_key             | distinct_key | 4       | func                               | 1     |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+
|  2 | MATERIALIZED | games_devs       | ALL    | game_id_index            | NULL         | NULL    | NULL                               | 98437 |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+
|  3 | MATERIALIZED | games_publishers | ALL    | game_id_index            | NULL         | NULL    | NULL                               | 98437 |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+

分析（不按字母排序）：

+----+------------------------+--------+
| 1  | Starting               | 168 µs |
+----+------------------------+--------+
| 2  | Checking Permissions   |  21 µs |
+----+------------------------+--------+
| 3  | Opening Tables         |  81 µs |
+----+------------------------+--------+
| 4  | After Opening Tables   |  25 µs |
+----+------------------------+--------+
| 5  | System Lock            |  15 µs |
+----+------------------------+--------+
| 6  | Table Lock             |  17 µs |
+----+------------------------+--------+
| 7  | Init                   |  78 µs |
+----+------------------------+--------+
| 8  | Optimizing             |  93 µs |
+----+------------------------+--------+
| 9  | Statistics             | 174 µs |
+----+------------------------+--------+
| 10 | Preparing              | 132 µs |
+----+------------------------+--------+
| 11 | Executing              |  15 µs |
+----+------------------------+--------+
| 12 | Sending Data           |  1,4 s |
+----+------------------------+--------+
| 13 | End Of Update Loop     |  26 µs |
+----+------------------------+--------+
| 14 | Removing Tmp Table     |  15 µs |
+----+------------------------+--------+
| 15 | End Of Update Loop     |   9 µs |
+----+------------------------+--------+
| 16 | Removing Tmp Table     |  11 µs |
+----+------------------------+--------+
| 17 | End Of Update Loop     |  10 µs |
+----+------------------------+--------+
| 18 | Query End              |   9 µs |
+----+------------------------+--------+
| 19 | Commit                 |  11 µs |
+----+------------------------+--------+
| 20 | Closing Tables         |  10 µs |
+----+------------------------+--------+
| 21 | Unlocking Tables       |   9 µs |
+----+------------------------+--------+
| 22 | Closing Tables         |  18 µs |
+----+------------------------+--------+
| 23 | Starting Cleanup       |   9 µs |
+----+------------------------+--------+
| 24 | Freeing Items          |  21 µs |
+----+------------------------+--------+
| 25 | Updating Status        |  69 µs |
+----+------------------------+--------+
| 26 | Reset For Next Command |  19 µs |
+----+------------------------+--------+

解释（带排序）：

+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+-----------------------------+
| id | select_type  | table            | type   | possible_keys            | key          | key_len | ref                                | rows  | Extra                       |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+-----------------------------+
|  1 | PRIMARY      | games_titles     | ref    | game_id_index,lang_index | lang_index   | 4       | const                              | 98995 | Using where; Using filesort |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+-----------------------------+
|  1 | PRIMARY      | games            | eq_ref | PRIMARY                  | PRIMARY      | 4       | test_db.games_titles.game_id       | 1     | Using where; Using index    |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+-----------------------------+
|  1 | PRIMARY      | <subquery2>      | eq_ref | distinct_key             | distinct_key | 4       | func                               | 1     | Using where                 |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+-----------------------------+
|  1 | PRIMARY      | <subquery3>      | eq_ref | distinct_key             | distinct_key | 4       | func                               | 1     | Using where                 |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+-----------------------------+
|  2 | MATERIALIZED | games_devs       | ALL    | game_id_index            | NULL         | NULL    | NULL                               | 98437 | Using where                 |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+-----------------------------+
|  3 | MATERIALIZED | games_publishers | ALL    | game_id_index            | NULL         | NULL    | NULL                               | 98437 | Using where                 |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+-----------------------------+

配置文件（带排序）：

+----+------------------------+--------+
| 1  | Starting               | 199 µs |
+----+------------------------+--------+
| 2  | Checking Permissions   | 101 µs |
+----+------------------------+--------+
| 3  | Opening Tables         |  56 µs |
+----+------------------------+--------+
| 4  | After Opening Tables   |  18 µs |
+----+------------------------+--------+
| 5  | System Lock            |  27 µs |
+----+------------------------+--------+
| 6  | Table Lock             |  18 µs |
+----+------------------------+--------+
| 7  | Init                   |  94 µs |
+----+------------------------+--------+
| 8  | Optimizing             | 211 µs |
+----+------------------------+--------+
| 9  | Statistics             | 417 µs |
+----+------------------------+--------+
| 10 | Preparing              | 138 µs |
+----+------------------------+--------+
| 11 | Sorting Result         |  35 µs |
+----+------------------------+--------+
| 12 | Executing              |  13 µs |
+----+------------------------+--------+
| 13 | Sending Data           |  18 µs |
+----+------------------------+--------+
| 14 | Creating Sort Index    |  2,4 s |
+----+------------------------+--------+
| 15 | End Of Update Loop     |  35 µs |
+----+------------------------+--------+
| 16 | Removing Tmp Table     |  23 µs |
+----+------------------------+--------+
| 17 | End Of Update Loop     |  12 µs |
+----+------------------------+--------+
| 18 | Removing Tmp Table     |  15 µs |
+----+------------------------+--------+
| 19 | End Of Update Loop     |  14 µs |
+----+------------------------+--------+
| 20 | Query End              |  12 µs |
+----+------------------------+--------+
| 21 | Commit                 |  15 µs |
+----+------------------------+--------+
| 22 | Closing Tables         |  14 µs |
+----+------------------------+--------+
| 23 | Unlocking Tables       |  12 µs |
+----+------------------------+--------+
| 24 | Closing Tables         |  30 µs |
+----+------------------------+--------+
| 25 | Starting Cleanup       |  17 µs |
+----+------------------------+--------+
| 26 | Freeing Items          |  32 µs |
+----+------------------------+--------+
| 27 | Updating Status        | 185 µs |
+----+------------------------+--------+
| 28 | Reset For Next Command |  48 µs |
+----+------------------------+--------+
CREATE TABLE `games` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `rating` double NOT NULL DEFAULT 0,
  `date` date DEFAULT NULL,
  `date_type` int(1) NOT NULL DEFAULT 1,
  `img` varchar(500) DEFAULT NULL,
  `img_type` int(10) NOT NULL,
  `url` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `rating_index` (`rating`) USING BTREE,
  KEY `date_index` (`date`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=326678 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

CREATE TABLE `games_titles` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `game_id` int(50) NOT NULL,
  `title` varchar(150) DEFAULT NULL,
  `lang` int(10) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `game_id_index` (`game_id`) USING BTREE,
  KEY `lang_index` (`lang`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=652865 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

CREATE TABLE `games_descriptions` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `game_id` int(50) NOT NULL,
  `description` varchar(5000) DEFAULT NULL,
  `lang` int(10) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `desc_index` (`description`(1024)) USING BTREE,
  KEY `lang_index` (`lang`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=253401 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

CREATE TABLE `games_devs` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `game_id` int(50) NOT NULL,
  `dev` varchar(150) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `game_id_index` (`game_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=126699 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

CREATE TABLE `games_publishers` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `game_id` int(50) NOT NULL,
  `publisher` varchar(150) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `game_id_index` (`game_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=126696 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

CREATE TABLE `games_genres` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `game_id` int(50) NOT NULL,
  `genre_id` int(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `game_id_index` (`game_id`) USING BTREE,
  KEY `genre_id_index` (`genre_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=121601 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

CREATE TABLE `games_times` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `game_id` int(50) NOT NULL,
  `time_id` int(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `game_id_index` (`game_id`) USING BTREE,
  KEY `time_id_index` (`time_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=126684 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

CREATE TABLE `games_statuses` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `game_id` int(50) NOT NULL,
  `status_id` int(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `game_id_index` (`game_id`) USING BTREE,
  KEY `status_id_index` (`status_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=326387 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

我试着做了一个辅助表“games_search”，其中有game_id，attributes（通过LIKE搜索游戏的所有属性的文本记录），但这个选项也不适合，因为没有速度上的增益，并且仍然需要按字母排序，所以没有组合表在这里肯定不够。请告诉我什么是最好的方法，我做/哪种架构是更好地使用，以摆脱不良的性能？我不敢想象一百万张唱片上会发生什么

mysql

1条答案

按热度按时间
0wi1tuuw

0wi1tuuw1#

CREATE TABLE `games_devs` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `game_id` int(50) NOT NULL,
  `dev` varchar(150) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `game_id_index` (`game_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=126699 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

-->

CREATE TABLE `NEW_games_devs` (
  `game_id` int(50) NOT NULL,
  `dev` varchar(150) NOT NULL,
  PRIMARY KEY (game_id, dev),
  KEY  (dev, `game_id`)
) ENGINE=InnoDB AUTO_INCREMENT=126699 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

这里有一个实现转换的方法：

INSERT INTO NEW_games_devs
    SELECT game_id, dev
        FROM games_devs;

RENAME TABLE games_devs TO OLD_games_devs,
             NEW_games_devs TO games_devs ;

DROP TABLE OLD_games_devs;
赞(0）分享  回复(0）举报  2023-06-28
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com