我需要一条建议，现在构建一个应用程序，我需要在相当大的表上运行一些查询，可能以非常频繁的速度运行，所以我正在尝试获得最佳性能明智的方法。
我有以下 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
最佳答案
更多提示:
不要介意添加一些索引来加快查询速度(索引占用空间，但在 INT 字段上它什么都不是，你得到的远远超过你失去的)。
TAG_table 上的插入触发器将显示的部分缓存在远程表中(例如相册概览的标签名称)可以帮助您将连接查询保持在下降频率。
REGEX，它会严重损害性能。添加一个新表来拆分数据是一个更好的主意(并使用 native 优化的索引)WHERE 子句中的每个字段，您都应该在其上建立索引。如果你不能放一个，那么你的数据库模型就完蛋了，需要改变。关于MySQL LEFT JOIN 或 WHERE IN SUBQUERY，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/35433628/