使用多个 LIKE 语句和 REGEXP 的查询是否可以更高效？

Question

我正在构建一个动态查询来从我的数据库中选择已删除的域名。目前有十几行，但我很快就会获取数据，其中记录将达到 500,000 行。

该架构只是一张包含 4 列的表：

CREATE TABLE `DroppedDomains` (
  `domainID` int(11) NOT NULL AUTO_INCREMENT,
  `DomainName` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `DropDate` date DEFAULT NULL,
  `TLD` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`domainID`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

我没有创建该架构，这是实时数据库架构。以下是示例数据：

我构建了下面可能是最复杂的查询类型。标准如下：

选择任意数量的域

1. 以“开始”一词开头
2. 以“结束”一词结尾
3. 在域名中的任意位置包含“containsThis”一词
4. 在域名中的任意位置包含“ContainsThisToo”一词
5. 至少包含一位数字
6. 域名必须至少包含 49 个字符。需要计算多字节 作为一个字符（我使用 CHAR_LENGTH ）。
7. 域名必须至少少于 65 个字符。
8. TLD 必须是“org”
9. DropDate 必须晚于 2009-11-01

这是到目前为止我的查询：

SELECT
*
FROM
DroppedDomains

WHERE

1=1

AND DomainName LIKE 'starts%ends'
AND DomainName LIKE '%containsThis%'
AND DomainName LIKE '%containsThisToo%'
AND DomainName LIKE '%-%'
AND DomainName REGEXP '[0-9]'
AND CHAR_LENGTH(DomainName) > 49
AND CHAR_LENGTH(DomainName) < 65
AND TLD = 'org'
AND DropDate > '2009-11-01'

这是我的问题

1. 考虑到我将有50 万行，如果我将 TLD 列设为其自己的表，并将 TLD 列设为该列的外键？只有 5 个 TLD（com、net、org、info、biz）。我意识到现实世界中有更多的 TLD，但该应用程序只有 5 个。用户无法指定自己的 TLD。

2. 我知道 REGEXP 和 500,000 行可能会导致灾难。无论如何我可以避免使用REGEXP吗？

3. 我还可以对查询进行其他优化吗？喜欢合并 LIKE 或使用其他功能，例如 INSTR？我应该实现任何特定类型的缓存机制吗？

Answer 1

当您有一个以常量前缀开头的 LIKE 模式并且您在该字段上有一个索引时，那么该索引可用于非常快速地查找以该前缀开头的行。幸运的是，您确实遇到了这种情况：

AND DomainName LIKE 'starts%ends'

如果只有少数值以 starts 开头，那么将很快找到这些行，并且仅针对这些行测试其他表达式。您可以通过运行EXPLAIN SELECT ...来检查索引是否已使用。

Answer 2

您应该根据您计划使用的查询来计划要创建的索引。

• 如果您有过滤查询
  仅通过 DropDate，然后是索引
  DropDate 将会很有用。
• 如果您有按以下分组的查询
  TLD，那么 TLD 上的索引将是
  有用。
• 如果您有搜索查询
  仅通过 DomainName 的长度，然后
  您可以考虑添加一个字段 DomainNameLength
  正是如此（以及对此的索引）所以
  长度不是每次都计算的
  运行查询的时间。
• 如果您有通过两个字段（例如 TLD 和 DropDate）搜索（过滤）的查询，那么您可能需要在这些字段上使用 2 列索引。
• 等等...

如果您将使用的唯一查询是您提到的复杂查询，那么马克的建议（关于域名上的索引）是最好的。

关于关于 TLD 字段的问题 1：

如果您确实只有少量（例如 5 个）选项，并且您不打算使用所有可用的 tld，则可以使用 ENUM 类型。

CREATE TABLE(
   ....
   tld ENUM('com', 'net', 'org', 'info', 'biz')
)