mysql - 在 mysql 中为读写密集型应用程序存储 url 的最佳方法

Question

对于读写密集型应用程序，在 mysql 中有效存储 url 的最佳方法是什么？

我将存储超过 500,000 个网址(全部以 http://或 https://开头。没有其他协议(protocol))并保存整个 url (http://example.com/path/?variable=a ) 放在一列中似乎在很大程度上是多余的，因为相同的域名和路径将多次保存到 mysql。

因此，最初，我想将它们分解(即域、路径和变量等)以消除冗余。但是看到有帖子说不推荐。对此有什么想法吗？

此外，应用程序通常必须在没有主键的情况下检索 url，这意味着它必须搜索文本以检索 url。 URL 可以被索引，但我想知道如果它们都在 innodb 下被索引(没有全文索引)，那么存储整个 url 和分解的 url 之间会有多大的性能差异。

broken-down-url 必须经过额外的步骤来组合它们。此外，这意味着我必须从不同的表(协议(protocol)、域、路径、变量)中检索数据 4 次，但它也会使每行中存储的数据更短，并且每个表中的行也会更少。这可能会加快这个过程吗？

Answer 1

我已经广泛处理过这个问题，我的一般理念是使用使用频率方法。这很麻烦，但它可以让您对数据进行一些出色的分析:

CREATE TABLE URL (
   ID            integer unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
   DomainPath    integer unsigned NOT NULL,
   QueryString   text
) Engine=MyISAM;

CREATE TABLE DomainPath (   
   ID            integer unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
   Domain        integer unsigned NOT NULL,
   Path          text,
   UNIQUE (Domain,Path)
) Engine=MyISAM;

CREATE TABLE Domain (   
   ID            integer unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
   Protocol      tinyint NOT NULL,
   Domain        varchar(64)
   Port          smallint NULL,
   UNIQUE (Protocol,Domain,Port)
) Engine=MyISAM;

作为一般规则，您将在单个域上具有相似的路径，但每个路径具有不同的查询字符串。

我最初设计它是为了让所有部分都在一个表(协议(protocol)、域、路径、查询字符串)中建立索引，但我认为上面的内容占用的空间更少，更有助于从中获取更好的数据。

text 往往很慢，因此您可以在使用一段时间后将“Path”更改为 varchar。大多数服务器在 URL 大约 1K 后就死机了，但我见过一些大服务器，宁愿不丢失数据也会犯错。

您的检索查询很麻烦，但如果您在代码中将其抽象化，没问题:

SELECT CONCAT(
    IF(D.Protocol=0,'http://','https://'),
    D.Domain,
    IF(D.Port IS NULL,'',CONCAT(':',D.Port)), 
    '/', DP.Path, 
    IF(U.QueryString IS NULL,'',CONCAT('?',U.QueryString))
)
FROM URL U
INNER JOIN DomainPath DP ON U.DomainPath=DP.ID
INNER JOIN Domain D on DP.Domain=D.ID
WHERE U.ID=$DesiredID;

如果端口号不是标准的(http 非 80，https 非 443)，则存储它，否则将其存储为 NULL 以表示不应包含它。 (您可以将逻辑添加到 MySQL，但它会变得更丑陋。)

我总是(或从不)从路径中去掉“/”以及“？”来自 QueryString 以节省空间。只有损失才能区分

http://www.example.com/
http://www.example.com/?

如果它很重要，那么我会改变你的策略，从不剥离它，只包含它。技术上，

http://www.example.com 
http://www.example.com/

都是一样的，所以去除 Path 斜杠总是可以的。

因此，要解析:

http://www.example.com/my/path/to/my/file.php?id=412&crsource=google+adwords

我们将在 PHP 中使用类似 parse_url 的东西来生成:

array(
    [scheme] => 'http',
    [host] => 'www.example.com',
    [path] => '/my/path/to/my/file.php',
    [query] => 'id=412&crsource=google+adwords',
)

然后您将检查/插入(使用适当的锁，未显示):

SELECT D.ID FROM Domain D 
WHERE 
    D.Protocol=0 
    AND D.Domain='www.example.com' 
    AND D.Port IS NULL

(如果不存在)

INSERT INTO Domain ( 
    Protocol, Domain, Port 
) VALUES ( 
    0, 'www.example.com', NULL 
);

然后我们的 $DomainID 继续......

然后插入DomainPath:

SELECT DP.ID FORM DomainPath DP WHERE 
DP.Domain=$DomainID AND Path='/my/path/to/my/file.php';

(如果不存在，同样插入)

然后我们的 $DomainPathID 继续......

SELECT U.ID FROM URL 
WHERE 
    DomainPath=$DomainPathID 
    AND QueryString='id=412&crsource=google+adwords'

并在必要时插入。

现在，让我注意重要，上述方案对于高性能站点来说会很慢。您应该修改所有内容以使用某种散列来加速 SELECT。简而言之，该技术如下:

CREATE TABLE Foo (
     ID integer unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
     Hash varbinary(16) NOT NULL,
     Content text
) Type=MyISAM;

SELECT ID FROM Foo WHERE Hash=UNHEX(MD5('id=412&crsource=google+adwords'));

为了简单起见，我特意从上面删除了它，但是将一个 TEXT 与另一个 TEXT 进行比较以进行选择很慢，并且会中断非常长的查询字符串。也不要使用固定长度的索引，因为那样也会中断。对于精度很重要的任意长度字符串，哈希失败率是可以接受的。

最后，如果可以的话，执行 MD5 哈希客户端以节省将大 blob 发送到服务器以执行 MD5 操作。大多数现代语言都支持内置 MD5:

SELECT ID FROM Foo WHERE Hash=UNHEX('82fd4bcf8b686cffe81e937c43b5bfeb');

但我离题了。