我有以下左外连接查询:
SELECT table_left.pk_id, table_left.name
FROM table_left left outer join table_right on table_right.fk_id = table_left.pk_id
WHERE table_right.name like '%entered search value%'
我遇到的问题是table_right有超过1,000,000行和超过60列 . 查询需要大约1分钟,我认为这是因为它在所有列上进行完全外连接 . 我不需要所有列 . 我只需要使用一个列(table_right.fk_id),这样我就可以在WHERE子句中连接两个表和另一个列(table_right.name) .
我使用外连接,因为我需要在table_left中包含没有行的结果 .
任何有助于提高上述查询速度的建议都将不胜感激 .
以下是我拥有的两个表的示例:
+-------------------+
| table_left |
+-------------------+
| pk_id | name |
+-------+-----------+
| 1 | IBM |
+-------+-----------+
| 2 | Facebook |
+-------+-----------+
| 3 | Google |
+-------+-----------+
| 4 | Microsoft |
+-------+-----------+
+--------------------------------------------+
| table_right |
+--------------------------------------------+
| table_right_pk_id | fk_id | job_details |
+-------------------+-------+----------------+
| 1 | 1 | Tester |
+-------------------+-------+----------------+
| 2 | 2 | Toilet Cleaner |
+-------------------+-------+----------------+
| 3 | 2 | Secretary |
+-------------------+-------+----------------+
| 4 | 3 | Developer |
+-------------------+-------+----------------+
我希望能够搜索'name'(在table_left中)和'job_details'(在table_right中)但是使用table_left列 . 这是我提出的查询,在查询下是一些预期的结果:
SELECT table_left.pk_id, table_left.name
FROM table_left left outer join table_right on table_right.fk_id = table_left.pk_id
WHERE table_right.name LIKE '%searchTerm%' OR table_left.name LIKE '%searchTerm%'
EXAMPLE 1
searchTerm ='IBM'
结果:
+-------------------+
| result |
+-------------------+
| pk_id | name |
+-------+-----------+
| 1 | IBM |
+-------+-----------+
EXAMPLE 2
searchTerm ='测试员'
结果:
+-------------------+
| result |
+-------------------+
| pk_id | name |
+-------+-----------+
| 1 | IBM |
+-------+-----------+
EXAMPLE 3
searchTerm ='微软'
结果:(即使table_right中没有记录,仍应返回Microsoft)
+-------------------+
| result |
+-------------------+
| pk_id | name |
+-------+-----------+
| 4 | Microsoft |
+-------+-----------+
EXAMPLE 4
searchTerm ='开发者'
结果:
+-------------------+
| result |
+-------------------+
| pk_id | name |
+-------+-----------+
| 2 | Facebook |
+-------+-----------+
3 回答
如果你需要返回table_left中的所有结果(无论它是否匹配),那么左连接是正确的,就像你正在做的那样,所以不要担心尝试切换它 .
让我们明确一点:连接仅在您需要其余列的列上运行,最好在进行任何连接之前将它们排除,因为输出表的宽度会更小(这意味着返回时的速度会提高)输出表) .
在尝试提高连接性能时,MySQL的经验法则是使用索引 . 通俗地说,索引基本上告诉数据库使用特定列(或列)作为表上的查找 . 在添加索引后,我被速度提升多次吹走了 .
我强烈建议在这种情况下使用索引 . 这是一个great tutorial用于设置它们 . G'luck!
左连接很好:
该性能问题与连接类型无关 .
很可能是“双端”通配符导致查询时间过长 . 使用索引不会改进这些 . 您的经验法则:
优先使用LIKE等于
优先使用LIKE 'something%' LIKE '%something'
使用LIKE '%something'优先于LIKE'%something%
如果使用LIKE '%something% don' t,则需要快速响应时间
说真的,您需要重新考虑使用通配符来提高性能 . 您可以使用尾随通配符获得对查询的索引支持,如果您在列的反向构建索引,则可以获得对前导通配符的索引支持,但是您需要全文索引(和不同的查询)才能获得任何索引支持(相当于)双端通配符 .
先前:
如果你只是通过
WHERE table_right.name like '%entered search value%'
进行过滤,这根本没有意义只有从table_right到table_left匹配的行才能从该where子句返回 .
我认为你的解释过于苛刻,你想要得到什么 . 如果我在解释你的问题时是正确的,你想要一个LEFT JOIN . 你想要从右边的LEFT表REGARDLESS,但如果正确的条件符合你的条件,那么根据需要获得条目 .
您的查询将条件应用于相同的左表字段而不是左侧和右侧 .
为了在加入包含60(或更多)列的RIGHT边表时帮助优化查询,但您只是在FK_ID及其“Job_Details”(根据您的Tester和Developer查询示例)进行比较,我将构建一个复合索引只有这两列,所以它可以优化连接和搜索...索引(FK_ID,Job_Details) . 通过这种方式,引擎可以直接从INDEX获得答案,而不必返回到存在所有60列的原始数据页面 . Table_Left还应该在(PK_ID,Name)上有一个综合索引用于优化 .
现在,您的搜索基于'%someValue%',您需要全文搜索,因为前导'%'暗示您不知道字符串前有多少个字符,也不知道有多少字符,只要搜索字符串在查询中是SOMEWHERE . 除非你需要,我建议不要使用前导'%',但那是你的电话 .
因此,这将基于具有“Tester”名称的左表或具有“Tester”作业详细信息的相应右表来为您提供 . 但我不认为这是最佳的 .
但是,我实际上建议提供一个不同的索引(Job_Details,FK_ID),然后使用DISTINCT执行UNION查询 .
这样,当通过名称查找时,左表针对其不同进行了优化,并且通过搜索其JobDetails并获得相应的左表名称信息来优化右表 .