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

Question

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

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

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

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

Broken-down-url 必须执行额外的步骤来组合它们。另外，这意味着我必须从不同的表（协议、域、路径、变量）检索数据 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;

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

我最初设计它的目的是将所有部分都索引在一个表中（协议、域、路径、查询字符串），但我认为上述内容占用的空间较少，并且可以更好地从中获取更好的数据。

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/

它们是相同的，因此删除路径斜杠始终是可以的。

因此，要解析：

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 
);

然后我们就得到了 < code>$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'));

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

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

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

但我离题了。

Answer 2

这实际上取决于您想对数据做什么。如果您正在对 URL 进行统计，例如查看哪些域最受欢迎，那么通过它进行细分是可行的。但如果您只是存储它们并访问整个 url，则没有理由将它们分开。

我见过一些人对长字符串（例如 md5）进行哈希处理并对其进行搜索，URL 的性能可能会有所提高，但我不确定提高了多少（对于大量文本来说更好）。

无论你做什么 - 不要忘记尽可能使用整数作为主键，因为它们是最快的查找。

如果你真的想分割你的URL，你可能需要考虑保留单独的表，以免锁定你的表（在innoDB中，这并不重要，因为表不会被锁定），但是使用单独的表，你可以使用foreign/primary_keys/ints 来引用您需要的行。

值得一读的是 friendfeed 的博客文章 - 这可能会给你一些想法出色地：