我有一个多语种网站,实现了游戏搜索和排序功能(我删除了最后一个问题,因为里面有很多不必要的信息;是的,现在的“游戏”代替了“书籍”,因为我创建了一个新的测试数据库)。通过以下查询执行搜索:
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搜索游戏的所有属性的文本记录),但这个选项也不适合,因为没有速度上的增益,并且仍然需要按字母排序,所以没有组合表在这里肯定不够。请告诉我什么是最好的方法,我做/哪种架构是更好地使用,以摆脱不良的性能?我不敢想象一百万张唱片上会发生什么
1条答案
按热度按时间0wi1tuuw1#
-->
这里有一个实现转换的方法: