我对多个LIKE语句和REGEXP的查询是否更有效？

Question

我正在构建一个动态查询，以从我的数据库中选择删除的域名。目前有十几行，但我很快就会得到数据，它将有多达50万行的记录。

模式只是一个包含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. 以‘Start’
2. 结束，‘ends’
3. 包含域名中任何位置的containsThis 'containsThis‘，
4. 包含域名

H 113中的'ContainsThisToo’一词，包括至少一个数字H 214H 115，域名必须至少为49个字符。多字节需要计算为一个字符(我使用了CHAR_LENGTH )。

1. 域名必须至少少于65个字符。TLD必须是‘org’2009-11-01

• DropDate需要比

晚一点

到目前为止，我的查询如下：

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'

这是我的问题

考虑到我将有50万行，如果我将列作为自己的表，并将TLD列作为该表的外键，那么

1. 会极大地提高性能吗？只有5个TLDs ( com，net，org，info，biz )。我意识到现实世界中有更多的TLD，但是这个应用程序只有5个。用户不能指定自己的TLD。
2. 我知道REGEXP和50万行可能会导致灾难。无论如何，我可以避免REGEXP吗？
3. ，对我可以做的查询还有其他优化吗？比如合并LIKE或者使用其他函数，比如INSTR？我应该实现任何特定的缓存机制吗？

Answer 1

当您有一个以常量前缀开头的相似模式，并且在该字段上有一个索引时，则可以使用该索引很快地找到以前缀开头的行。幸运的是，这里确实存在这样的情况：

AND DomainName LIKE 'starts%ends'

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

Answer 2

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

• 如果您将有只由DropDate筛选的查询，那么DropDate上的索引将是有用的。如果您将按TLD进行分组查询，则使用TLD上的索引将是有用的。如果您将有只按DomainName长度搜索的查询，则可以考虑添加一个字段DomainNameLength，该字段的长度与此完全相同(以及此索引)，因此在每次运行查询时都不会计算长度。如果您将通过两个字段(例如TLD和DropDate)进行搜索(过滤器)，则
• ，那么您可能需要这些fields.
• etc...

上的2列索引。

如果您将使用的唯一查询是您提到的复杂查询，那么Mark的建议(关于DomainName上的索引)是最好的。

关于TLD字段的问题1：

如果您真的只有少量的选项(比如5)，并且您不打算使用所有可用的tlds，那么您可以使用ENUM type。

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