mysql - 如何在使用like搜索时使mysql查询速度更快

Question

我有三个表，我必须使用 like 匹配来搜索它们。该查询运行超过 10,000 条记录。它工作正常，但需要 4 秒才能给出结果。我怎样才能提高速度并将其降低到 1 秒？

profile_category_table
----------------------
restaurant
sea food restaurant

profile_keywords_table
----------------------
rest
restroom
r.s.t

company_profile_table
---------------------
maha restaurants
indian restaurants

查询:

SELECT name
FROM (
        (SELECT PC_name AS name
         FROM profile_category_table
         WHERE PC_status=1
           AND PC_parentid!=0
           AND (regex_replace('[^a-zA-Z0-9\-]','',remove_specialCharacter(PC_name)) LIKE '%rest%')
         GROUP BY PC_name)
      UNION
        (SELECT PROFKEY_name AS name
         FROM profile_keywords_table
         WHERE PROFKEY_status=1
           AND (regex_replace('[^a-zA-Z0-9\-]','',remove_specialCharacter(PROFKEY_name)) LIKE '%rest%')
         GROUP BY PROFKEY_name)
      UNION
        (SELECT COM_name AS name
         FROM company_profile_table
         WHERE COM_status=1
           AND (regex_replace('[^a-zA-Z0-9\-]','',remove_specialCharacter(COM_name)) LIKE '%rest%')
         GROUP BY COM_name))a
ORDER BY IF(name LIKE '%rest%',1,0) DESC LIMIT 0, 2

我也为该列添加索引。

如果用户在文本框中使用文本进行搜索..自动建议结果应该是..

结果

restaurant
sea food restaurant
maha restaurants
indian restaurants
rest
restroom
r.s.t

我使用 regex_replace('[^a-zA-Z0-9-]','',remove_specialCharacter(COM_name) 从字段值中删除特殊字符并使用该关键字进行数学计算..

Answer 1

您可以考虑很多事情:

这里性能的主要 killer 可能是regex_replace() ...比如'%FOO%'。鉴于您正在对列应用函数，索引将不会生效，从而导致您进行几次全表扫描。更不用说正则表达式替换将是很重要的。为了优化，您可以

1. 保留一个单独的列，其中存储“已清理”的数据，您可以为其创建索引，并保留您的查询，如 where pc_name_sanitized like '%FOO%'
2. 我不确定MySql中是否可用，但是在很多DMBS中，有一个称为基于函数的索引的功能。您可以考虑使用它来索引正则表达式替换函数

但是即使经过上述改变，你也会发现性能并不是很吸引人。在大多数情况下，在前面使用 like 和通配符可以避免使用索引。如果可能，尝试进行精确匹配，或者提供字符串的开头，例如其中 pc_name_sanitized 就像“FOO%”

正如其他用户提到的，使用 UNION 也是性能 killer 。如果可能的话，尝试使用UNION ALL。