logo标签列表

SQL查询未正确使用索引

mysqlsqljoinindexing
5

我有一个SQL查询的问题。这是我的查询语句:

explain 
SELECT DISTINCT profiles.hoofdrubriek, profiles.plaats, profiles.bedrijfsnaam, profiles.gemeente, profiles.bedrijfsslogan, profiles.straatnaam, profiles.huisnummer, profiles.postcode, profiles.telefoonnummer, profiles.fax, profiles.email, profiles.website, profiles.bedrijfslogo 
FROM profiles 
LEFT JOIN profile_subrubriek ON profiles.ID=profile_subrubriek.profile_id 
LEFT JOIN rubrieken ON profile_subrubriek.subrubriek_id=rubrieken.ID  
WHERE (
    rubrieken.rubriek = 'Pedicurepraktijken' OR 
    profiles.hoofdrubriek = 'Pedicurepraktijken'
) 
ORDER BY profiles.grade DESC, profiles.bedrijfsnaam

这个查询中的“OR”运算符会引起问题:

rubrieken.rubriek = 'Pedicurepraktijken' OR profiles.hoofdrubriek = 'Pedicurepraktijken'

我在所有表格上都应用了索引，如果我删除上面代码中的其中一个部分，它们可以正常工作。但是，如果将它们与OR运算符组合起来，它会崩溃，并且拒绝使用我在profiles表中' hoofdrubriek '列上应用的索引。以下是我相关表格的布局：

CREATE TABLE `profiles` (
 `ID` varchar(255) NOT NULL DEFAULT '',
 ......
 `hoofdrubriek` varchar(255) DEFAULT NULL,
...


 `timestamp` datetime DEFAULT NULL,
 `meerderevestigingen` varchar(255) NOT NULL,
 `grade` int(5) NOT NULL,
 PRIMARY KEY (`ID`),
 KEY `IDX_TIMESTAMP` (`timestamp`),
 KEY `IDX_NIEUW` (`nieuw`),
 KEY `IDX_HOOFDRUBRIEK` (`hoofdrubriek`),
 KEY `bedrijfsnaam` (`bedrijfsnaam`),
 KEY `grade` (`grade`),
 KEY `gemeente` (`gemeente`),
 KEY `plaats` (`plaats`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8


CREATE TABLE `rubrieken` (
 `ID` mediumint(9) NOT NULL AUTO_INCREMENT,
 `rubriek` varchar(255) NOT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `rubriek` (`rubriek`)
) ENGINE=MyISAM AUTO_INCREMENT=1905 DEFAULT CHARSET=utf8


CREATE TABLE `profile_subrubriek` (
 `profile_id` varchar(20) NOT NULL,
 `subrubriek_id` mediumint(9) NOT NULL,
 PRIMARY KEY (`subrubriek_id`,`profile_id`),
 KEY `profile_id` (`profile_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

当然我可以用UNION DISTICT来解决这个问题，将两个不同的查询结合起来，但我认为这不应该是解决问题的方式。

有性能问题吗？有时候SQL不使用索引是因为它没有必要。 - dbarnes
你使用的是哪个数据库？MYSQL吗？ - Twelfth
我确实在使用MYSQL。不使用索引意味着它要扫描大约600000+行，这使得它变得非常缓慢并且可能使用了太多的资源。 - user2704687
你尝试过使用USE INDEX提示吗？我仍然认为UNION是正确的方法。 - GarethD
@GarethD，是的，我做了。没有错误消息，所以提示应该没问题，但仍然没有使用索引。联合将解决这个问题，但难道不应该有更简洁的解决方案吗？我错过了什么根本原因.. - user2704687
我认为如果你想特别了解优化器确定表扫描比使用索引更有效的步骤，你可能需要在http://dba.stackexchange.com/上进行提问。 - GarethD
3个回答
1
如果“or”引起了问题，那么最简单的解决方法就是将查询分成两部分，并使用“union”将它们组合在一起（在您的情况下，由于“distinct”）。修正“where”子句使用索引可能会很难，因为它引用了两个不同的列。
SELECT p.hoofdrubriek, p.plaats, p.bedrijfsnaam, p.gemeente, p.bedrijfsslogan, profiles.straatnaam, 
       p.huisnummer, profiles.postcode, p.telefoonnummer, p.fax, p.email, p.website, p.bedrijfslogo, 
       p.grade
FROM profiles p 
LEFT JOIN profile_subrubriek ON p.ID=profile_subrubriek.profile_id 
LEFT JOIN rubrieken ON profile_subrubriek.subrubriek_id=rubrieken.ID  
WHERE rubrieken.rubriek = 'Pedicurepraktijken' 
union 
SELECT p.hoofdrubriek, p.plaats, p.bedrijfsnaam, p.gemeente, p.bedrijfsslogan, profiles.straatnaam, 
       p.huisnummer, profiles.postcode, p.telefoonnummer, p.fax, p.email, p.website, p.bedrijfslogo, 
       p.grade
FROM profiles p 
LEFT JOIN profile_subrubriek ON p.ID=profile_subrubriek.profile_id 
LEFT JOIN rubrieken ON profile_subrubriek.subrubriek_id=rubrieken.ID  
WHERE p.hoofdrubriek = 'Pedicurepraktijken'
ORDER BY grade DESC, bedrijfsnaam;

我在select子句中添加了grade，以便可以被order by使用。

我认为OP是在问是否有除了“UNION”之外的其他方法。 - Vatev
感谢您的时间和努力，Gordon。然而，Vatev是正确的。在表的“creat-code”下，我声明应该有比UNION更好的方法，不是吗？ - user2704687
@Gordon，经过一段时间的搜索和试错，我决定使用UNION。我希望能找到更简洁的方法，但这个方法很有效！感谢你的帮助。 - user2704687
1
我认为Gordon在使用UNION方面是正确的，但你可以让UNION更加高效:
在下面的第一个查询中，由于您仅参考了profiles表，因此可以删除连接，它们只会导致需要随后删除的重复项。然后，在第二个查询中，您可以将JOIN从OUTER更改为INNER，因为您在where子句中引用了外部表中的字段，所以必须有匹配项。然后通过添加一个条款来删除由联合的第一部分获取的值，您将有较少的记录要排序和删除重复项。
SELECT  profiles.hoofdrubriek, 
        profiles.plaats, 
        profiles.bedrijfsnaam, 
        profiles.gemeente, 
        profiles.bedrijfsslogan, 
        profiles.straatnaam, 
        profiles.huisnummer, 
        profiles.postcode, 
        profiles.telefoonnummer, 
        profiles.fax, 
        profiles.email, 
        profiles.website, 
        profiles.bedrijfslogo,
        profiles.grade
FROM    profiles   
WHERE   profiles.hoofdrubriek = 'Pedicurepraktijken'
UNION
SELECT  profiles.hoofdrubriek, 
        profiles.plaats, 
        profiles.bedrijfsnaam, 
        profiles.gemeente, 
        profiles.bedrijfsslogan, 
        profiles.straatnaam, 
        profiles.huisnummer, 
        profiles.postcode, 
        profiles.telefoonnummer, 
        profiles.fax, 
        profiles.email, 
        profiles.website, 
        profiles.bedrijfslogo,
        profiles.grade
FROM    profiles 
        INNER JOIN profile_subrubriek 
            ON profiles.ID=profile_subrubriek.profile_id 
        INNER JOIN rubrieken 
            ON profile_subrubriek.subrubriek_id=rubrieken.ID  
WHERE   rubrieken.rubriek = 'Pedicurepraktijken' 
AND     profiles.hoofdrubriek != 'Pedicurepraktijken'
ORDER BY grade DESC, bedrijfsnaam;

我的MySQL优化器内部工作知识非常模糊，但我理解的原因是MySQL不使用索引，因为它需要扫描整个表以检查其他谓词(rubrieken.rubriek = 'Pedicurepraktijken')。我认为您期望优化器隐式执行的内容是UNION明确执行的内容。(我认为)由于外连接和OR，优化器无法准确确定它将不得不读取多少行profiles才能在rubrieken或profiles中找到匹配项，它无法准确确定索引搜索是否比表扫描更有效，并选择表扫描。这不仅适用于MySQL，在所有DMBS中，使用UNION而不是OR更有效率是很常见的。
像我这样重新排列查询给了优化器更好的机会来使用正确的索引(我猜测没有USE INDEX提示也可以，但我没有测试过)。
0

我建议尝试切换到InnoDB，因为它们是索引组织表。

当使用InnoDB时，您链接表profile_subrubriek的所有数据都将在聚簇索引中。

同样，当您从代理主键切换到自然主键rubrieken时，表rubrieken也是如此。由于这是一个单列表，因此其存在至少值得怀疑。

所以我建议删除表rubrieken。

我会这样做：

CREATE TABLE `profiles` (
 `ID` varchar(255) NOT NULL DEFAULT '',
 ......
 `hoofdrubriek` varchar(255) DEFAULT NULL,
...


 `timestamp` datetime DEFAULT NULL,
 `meerderevestigingen` varchar(255) NOT NULL,
 `grade` int(5) NOT NULL,
 PRIMARY KEY (`ID`),
 KEY `IDX_TIMESTAMP` (`timestamp`),
 KEY `IDX_NIEUW` (`nieuw`),
 KEY `IDX_HOOFDRUBRIEK` (`hoofdrubriek`),
 KEY `bedrijfsnaam` (`bedrijfsnaam`),
 KEY `grade` (`grade`),
 KEY `gemeente` (`gemeente`),
 KEY `plaats` (`plaats`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `profile_rubriek` (
 `profile_id` varchar(20) NOT NULL,
 `rubriek` varchar(255) NOT NULL,
 PRIMARY KEY (`profile_id`,`rubriek`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

查询语句将会是：

SELECT DISTINCT profiles.hoofdrubriek, profiles.plaats, profiles.bedrijfsnaam, profiles.gemeente, profiles.bedrijfsslogan, profiles.straatnaam, profiles.huisnummer, profiles.postcode, profiles.telefoonnummer, profiles.fax, profiles.email, profiles.website, profiles.bedrijfslogo 
FROM profiles 
LEFT JOIN profile_rubriek ON profiles.ID=profile_rubriek.profile_id 
WHERE (
    profile_rubriek.rubriek = 'Pedicurepraktijken' OR 
    profiles.hoofdrubriek = 'Pedicurepraktijken'
) 
ORDER BY profiles.grade DESC, profiles.bedrijfsnaam
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接