mysql - 为什么添加额外的 'OR' 会使针对 MYSQL View 的查询非常慢

Question

我的 SQL 查询如下所示:

SELECT * FROM `userListView` `t` 
WHERE (((((((email LIKE '%western millwork%') OR 
(firstName LIKE '%western millwork%')) OR 
(lastName LIKE '%western millwork%')) OR 
(name LIKE '%western millwork%')) OR 
(company LIKE '%western millwork%')) OR 
(companyName LIKE '%western millwork%')) OR 
(visualId LIKE '%western millwork%')) 
AND (active='1') 
ORDER BY `t`.`lastName`, `t`.`firstName` LIMIT 30

查询运行良好，直到我添加了有关“companyName”的行。

一旦发生这种情况，时间就会从大约 1 秒变成 15 - 20 秒。

我认为这与 View 的创建方式有关？所以有“company”.“name”，然后“userListView”中有“companyName”，这就是我要查询的内容。

以下是创建 View 的方式:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `3form`@`192.168.%.%` 
    SQL SECURITY DEFINER
VIEW `userListView` AS
    (SELECT 
        `user`.`uid` AS `uid`,
        `user`.`active` AS `active`,
        `user`.`email` AS `email`,
        `user`.`fname` AS `firstName`,
        `user`.`lname` AS `lastName`,
        CONCAT(`user`.`fname`, ' ', `user`.`lname`) AS `name`,
        `user`.`company` AS `company`,
        `user`.`cust_id` AS `visualId`,
        `user`.`repid` AS `repId`,
        `user`.`srepid` AS `studioRepId`,
        `user`.`lightartrepid` AS `lightArtRepId`,
        `user`.`exteriorrepid` AS `exteriorRepId`,
        `user`.`nationalrepid` AS `nationalRepId`,
        `user`.`phone` AS `phone`,
        `user`.`phone_ext` AS `phoneExt`,
        `user`.`fax` AS `fax`,
        `user`.`mobile` AS `mobile`,
        `billingAddress`.`label` AS `billingAddressLabel`,
        `billingAddress`.`addr` AS `billingAddressAddr`,
        `billingAddress`.`addr2` AS `billingAddressAddr2`,
        `billingAddress`.`city` AS `billingAddressCity`,
        `billingAddress`.`state` AS `billingAddressState`,
        `billingAddress`.`zip` AS `billingAddressZip`,
        `billingAddress`.`country` AS `billingAddressCountry`,
        `shippingAddress`.`label` AS `shippingAddressLabel`,
        `shippingAddress`.`addr` AS `shippingAddressAddr`,
        `shippingAddress`.`addr2` AS `shippingAddressAddr2`,
        `shippingAddress`.`city` AS `shippingAddressCity`,
        `shippingAddress`.`state` AS `shippingAddressState`,
        `shippingAddress`.`zip` AS `shippingAddressZip`,
        `shippingAddress`.`country` AS `shippingAddressCountry`,
        `branch`.`bid` AS `bid`,
        `branch`.`description` AS `branchDescription`,
        `branch`.`repid` AS `branchRepId`,
        `branch`.`studiorepid` AS `branchStudioRepId`,
        `branch`.`lightartrepid` AS `branchLightArtRepId`,
        `branch`.`exteriorrepid` AS `branchExteriorRepId`,
        `branch`.`nationalrepid` AS `branchNationalRepId`,
        `branchAddress`.`label` AS `branchAddressLabel`,
        `branchAddress`.`addr` AS `branchAddressAddr`,
        `branchAddress`.`addr2` AS `branchAddressAddr2`,
        `branchAddress`.`city` AS `branchAddressCity`,
        `branchAddress`.`state` AS `branchAddressState`,
        `branchAddress`.`zip` AS `branchAddressZip`,
        `branchAddress`.`country` AS `branchAddressCountry`,
        `company`.`cid` AS `cid`,
        `company`.`name` AS `companyName`,
        `company`.`url` AS `companyUrl`,
        `company`.`visual_id` AS `companyVisualId`,
        `company`.`phone` AS `companyPhone`,
        `company`.`fax` AS `companyFax`,
        `companyAddress`.`label` AS `companyAddressLabel`,
        `companyAddress`.`addr` AS `companyAddressAddr`,
        `companyAddress`.`addr2` AS `companyAddressAddr2`,
        `companyAddress`.`city` AS `companyAddressCity`,
        `companyAddress`.`state` AS `companyAddressState`,
        `companyAddress`.`zip` AS `companyAddressZip`,
        `companyAddress`.`country` AS `companyAddressCountry`
    FROM
        ((((((`users` `user`
        LEFT JOIN `useraddr` `billingAddress` ON (((`user`.`uid` = `billingAddress`.`uid`)
            AND (`user`.`billaddr` = (`billingAddress`.`label` COLLATE utf8_unicode_ci)))))
        LEFT JOIN `useraddr` `shippingAddress` ON (((`user`.`uid` = `shippingAddress`.`uid`)
            AND (`user`.`billaddr` = (`shippingAddress`.`label` COLLATE utf8_unicode_ci)))))
        LEFT JOIN `branch` ON ((`user`.`bid` = `branch`.`bid`)))
        LEFT JOIN `branch_address` `branchAddress` ON (((`branch`.`bid` = `branchAddress`.`bid`)
            AND (`branch`.`shipto` = (`branchAddress`.`label` COLLATE utf8_unicode_ci)))))
        LEFT JOIN `company` ON ((`company`.`cid` = `branch`.`cid`)))
        LEFT JOIN `company_address` `companyAddress` ON (((`company`.`cid` = `companyAddress`.`cid`)
            AND (`company`.`billto` = (`companyAddress`.`label` COLLATE utf8_unicode_ci))))))

这个想法是“companyName”将优先于“company”，但这还没有发生。因此，为什么原始查询会同时查看两者。

知道问题可能是什么吗？

更新:

解释如下:

Answer 1

由于多种原因，此查询及其基础 View 可能会很慢。

1. column LIKE '%value%' 是减慢查询速度的最有效方法。它必须检查列中的每个值，并且不能利用任何索引查找。如果您可以将部分或全部这些项目更改为 LIKE 'value%' 列，您将会看到改进。
2. OR 子句，尤其是与 LIKE '%value%' 结合使用，也会降低查询速度。有时，查询规划器必须对结果集进行多次传递。
3. 您已使用括号将 OR 子句分组，这告诉查询规划器您不信任它来决定 OR 链的哪些部分条款最有可能是最快的。去掉所有嵌套的括号。
4. 您正在对 CONCAT() 操作的结果进行 LIKE 搜索。这保证了查询规划器无法通过优化来消除上述问题。
5. MySQL 没有具体化 View 。每次使用时都会重建 View 。 MySQL 中的 View 主要是为了清晰起见，而不是为了性能。
6. SELECT * FROM ... ORDER BY some LIMITsmall-number 是复杂查询中的经典反模式。它执行整个查询，然后对其进行排序，然后丢弃大部分查询。
7. 您在 JOIN ... ON 子句中使用了 COLLATE 修饰符。如果这些是必要的，那么您就无法在 ON 条件下使用索引:排序规则被烘焙到 MySQL 索引中。如果您的 COLLATE 子句覆盖默认排序规则，则无法使用索引。
8. 但经济大幅放缓的“真正”问题更为隐蔽。首先，您在一个表(users)上进行过滤。当您添加 companyName 时，它不需要还过滤 VIEW 中的另一个表。这意味着它必须保留第一个表中的所有行，以防 companyName 匹配。解决此问题的方法是将 that OR 转换为 UNION。
9. 对于此处列出的许多问题，没有简单的加速方法。

当您尝试解决此问题时，您可能需要将 SQL_NO_CACHE 添加到您的 select 语句中，如下所示

  SELECT SQL_NO_CACHE whatever FROM whatever

这是因为 MySQL 会检测重复查询，如果可以的话(并且如果“查询缓存”打开)，会快速(亚秒级)返回之前的结果。

这种广角站点搜索可能应该分别处理每个主要类别的数据。例如，您需要执行单独的单表查询，以从 user 和 company 表中提取相关 id 值，然后弄清楚如何获取相关数据。