Mysql InnoDB性能优化和索引

Question

我有2个数据库，我需要链接两个大表之间的信息(每个超过3M个条目，不断增长)。第一个数据库有一个表' pages‘，它存储了关于网页的各种信息，并包括每个网页的URL。列“URL”是varchar(512)，没有索引。

第二个数据库有一个表'urlHops‘，定义如下：

CREATE TABLE urlHops ( dest varchar(512) NOT NULL，src varchar(512) DEFAULT NULL，timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP，KEY dest_key (dest)，KEY src_key (src) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

现在，我基本上需要发出(有效地)这样的查询: select p.id、p.URL from db1.pages p、db2.urlHops u where u.src=p.URL和u.dest=？

起初，我想在页面上添加一个索引(URL)。但是它是一个非常长的列，并且我已经在同一个表上执行了大量的插入和更新(远远超过我使用这个索引执行的SELECT操作的数量)。

我认为其他可能的解决方案是:将列-adding到页面，存储URL的md5散列并对其进行索引；通过这种方式，我可以使用URL的md5进行查询，这样做的优点是索引位于较小的列上。-adding另一个只包含页面id和页面URL的表，对这两列都进行索引。但这可能是对空间的浪费，唯一的好处是不会减慢我在“页面”上执行的插入和更新。

我不想减慢插入和更新的速度，但同时我将能够高效地对URL执行查询。有什么建议吗？我主要关心的是性能；如果需要，浪费一些磁盘空间不是问题。

谢谢，致以问候

戴维德

Answer 1

如果页面到URL是一对一的关系，且该表具有唯一的id (主键？)，则可以将该id值存储在urlHops表的src和dest域中，而不是存储完整的URL。

这将使索引和连接更加高效。

Answer 2

你的MD5散列建议非常好-它记录在高性能MySQL第二版中。有几个技巧可以让它工作：

创建表url ( id NOT NULL主键auto_increment，url varchar(255) not null，url_crc32 INT UNSIGNED not null，INDEX (url_crc32) )；

Select查询必须如下所示：

SELECT * FROM url= url='http://stackoverflow.com‘和url_crc32=crc32('http://stackoverflow.com')；

url_crc32是为使用索引而设计的，其中WHERE子句中的url旨在防止散列冲突。

我可能会推荐crc32而不是md5。将会有更多的冲突，但您有更高的机会在内存中拟合所有索引。

Answer 3

我将创建一个page_url表，其中包含auto-inc整数主键和URL值。然后更新Pages和urlHops以使用page_url.id。

您的urlHops将变成(dest int，src int，...)

您的Pages表将用pageid替换url。

索引page_url.url字段，您应该就可以开始使用了。