>我有表日志,其中有两个字段:action
(VARCHAR 45)和info
(VARCHAR 10000)。
此表中记录了多个内容,其中之一是访问页面时的用户 ip。在这种情况下,action
='ip',info
='IP.ADD.RE.SS'。
因为info
可以记录一些特定内容的大量文本,所以我只想创建仅适用于action
='ip'info
字段的 INDEX,这样我就可以快速搜索 IP,并且没有带有"动作"的过度索引。
我已经尝试为前 15 个字符创建 INDEX,但 IP 条目仍然占所有内容的 1%,这对我来说似乎有点矫枉过正。 整个解决方案是从其他人那里继承的,不幸的是,我现在几乎无法改变整个架构
有什么建议如何正确地做到这一点吗?甚至可能吗?
某些 RDBMS 产品支持您所描述的内容。它被称为部分索引或按不同产品筛选的索引。
- PostgreSQL有部分索引
- Microsoft SQL Server 作为筛选索引
- SQLite 有部分索引
MySQL没有实现这个想法(他们没有义务实现它,因为它是一个非标准功能)。有人要求将其作为新功能:https://bugs.mysql.com/bug.php?id=76631
您可以在MySQL 5.7中执行的一种解决方法来模拟部分索引,方法是创建一个虚拟列,其中值为NULL,除非action
为"ip"。然后为该虚拟列编制索引:
ALTER TABLE logs
ADD COLUMN ip_info VARCHAR(12)
AS (CASE `action` WHEN 'ip' THEN LEFT(info, 12) END),
ADD KEY (ip_info);
严格来说,它仍然索引每一行,但至少它不会在索引中存储任何值,除非操作是"ip"。
PS:我还没有测试过上面的例子,所以如果有语法错误,请道歉。
这似乎属于"EAV"类别。 你有一堆东西(ip,postdel等),每个东西都是可选的。 其中一些需要索引,有些不需要。
我的建议是将键值对放在JSON
字符串中。 并为您想要索引的任何内容(在您的情况下为 IP)创建一个特殊列。 它可以NULLable
以最小化(但不能完全消除"浪费"的空间。
另请参阅我在EAV上的博客。
另请参阅MySQL和MariaDB涉及JSON的实现。 注意:它们需要相对较新的MySQL或MariaDB版本。
无论如何,您都要对操作列进行筛选,因此组合索引是此处的解决方案。在两列上创建索引(action, info(15))
.
不过,索引中列的顺序很重要。不要反过来改变它。