用于存储域名的推荐MySQL索引
我试图在MySQL数据库中存储大约1亿个域名，但是我无法找到在域名上使用的正确的索引方法。
问题是，类似的查询也将被执行：
SELECT id FROM domains WHERE domain LIKE '%.example.com'或
SELECT id FROM domains WHERE domain LIKE 'example.%'如果这样做更容易，那么‘%示例%’并不是一个要求，但最好是拥有/能够这样做是件好事。
正确的索引是什么？从左到右(例如.%)应该是直接向前的，但是从右到左(%.example.com)是有问题的，但是最常见的查询。
我在Linux上使用MariaDB 10.3。在PCI-e SSD上运行的DB，查找时间超过10秒的时间应该是“不可接受的”。
回答 2
Stack Overflow用户
发布于 2020-02-16 17:27:44
您可以在表中使用一个虚拟永久列( rdomain )，其中虚拟函数以与REVERSE(domain).相反的顺序存储域名，因此可以从字符串的开头进行搜索，即搜索'%.mydomain.com‘->，其中rdomain类似于反向(’%.mydomain.com‘)。
表
CREATE TABLE `myreverse` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`domain` varchar(64) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_domain` (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;添加列
ALTER TABLE myreverse
ADD COLUMN rdomain VARCHAR(64) AS (REVERSE(domain)),
ADD KEY idx_rdomain (rdomain);插入一些数据
INSERT INTO `myreverse` (`id`, `domain`)
VALUES
(2, 'img.google.com'),
(3, 'w3.google.com'),
(1, 'www.coogle.com'),
(4, 'www.google.de'),
(5, 'www.mydomain.com');参见数据
mysql> SELECT * from myreverse;
+----+------------------+------------------+
| id | domain | rdomain |
+----+------------------+------------------+
| 1 | www.google.com | moc.elgoog.www |
| 2 | img.google.com | moc.elgoog.gmi |
| 3 | w3.coogle.com | moc.elgooc.3w |
| 4 | www.google.de | ed.elgoog.www |
| 5 | www.mydomain.com | moc.niamodym.www |
+----+------------------+------------------+
5 rows in set (0.01 sec)
mysql> 现在您可以使用反向顺序进行查询，MySQL可以使用索引。
查询
mysql> select * from myreverse WHERE rdomain like REVERSE('%.google.com');
+----+----------------+----------------+
| id | domain | rdomain |
+----+----------------+----------------+
| 3 | w3.google.com | moc.elgoog.3w |
| 2 | img.google.com | moc.elgoog.gmi |
+----+----------------+----------------+
2 rows in set (0.00 sec)
mysql> 在这里，您可以看到优化器使用索引。
mysql> EXPLAIN select * from myreverse WHERE rdomain like REVERSE('%.google.com');
+----+-------------+-----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | myreverse | NULL | range | idx_rdomain | idx_rdomain | 195 | NULL | 2 | 100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
mysql> Stack Overflow用户
发布于 2020-02-16 13:12:50
我不确定什么索引能帮到你。如果无法更改数据库，则选项似乎有限。您可以做的一件事是，如果您要连续运行子域和域查询，那么首先运行子域查询。这将有助于减少域查询必须覆盖的行数。
如果您将URL在子域和域之间拆分为数据库中的不同列，肯定会有所帮助。都有索引。然后您可以只查询子域和域。它应该会加快速度的。如果有大量的重复值，您应该对这些字段进行规范化，以便删除重复并加快查询速度。
https://stackoverflow.com/questions/60248716
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号