代码之家 › 专栏 › 技术社区 › Sean

对具有多个索引的表进行mysql索引优化,这些索引索引某些相同的列

indexing mysql

Sean · 技术社区 · 16 年前

我有一个表,其中存储了第三方网站上访问者会话的一些基本数据。这是它的结构:

id, site_id, unixtime, unixtime_last, ip_address, uid

有四个索引: id , site_id/unixtime , site_id/ip_address 和 site_id/uid

我们查询此表的方式有很多种,而且都是特定于站点id的。unixtime索引用于显示给定日期或时间范围内的访问者列表。另外两个用于查找来自IP地址或“uid”(为每个访问者创建的唯一cookie值)的所有访问,以及确定这是新访问者还是返回访问者。

显然,在3个索引中存储site_id对于写入速度和存储都是低效的,但我看不到解决方法,因为我需要能够快速查询给定特定site_id的数据。

有什么办法让这更有效吗?

除了一些非常基本的东西之外,我真的不了解b-树,但是让索引中最左边的列是方差最小的列更有效-对吗?因为我认为站点ID是IP地址和uid索引的第二列,但是我认为这会降低索引的效率,因为IP和uid的变化比站点ID的变化要大,因为每个数据库服务器只有大约8000个唯一的站点,但是mill每天约8000个站点的独特访客。

我还考虑过完全从IP和uid索引中删除站点ID,因为同一个访问者访问共享同一数据库服务器的多个站点的机会非常小,但是在这种情况下,我担心确定这是否是一个新的访问者可能会非常慢。o此站点是否有ID。查询如下:

select id from sessions where uid = 'value' and site_id = 123 limit 1

…因此,如果此访问者以前访问过此网站,则在停止之前,只需要找到一行具有此网站ID的行。这不一定是超级快,但可以接受的快。但是假设我们有一个网站,每天有50万的访客,而一个特定的访客喜欢这个网站,每天去那里10次。现在他们碰巧第一次访问同一数据库服务器上的另一个站点。上面的查询可能需要相当长的时间来搜索这个uid的所有潜在的数千行,这些行散落在磁盘上,因为它找不到这个站点id的行。

如果您有任何关于尽可能提高效率的见解,我们将不胜感激:)

更新-这是mysql 5.0的myisam表。我关心的是性能和存储空间。这个表读写都很重。如果我必须在性能和存储之间做出选择,我最担心的是性能—但两者都很重要。

我们在所有服务领域都大量使用memcached,但这并不是不关心数据库设计的借口。我希望数据库尽可能高效。

3 回复 | 直到 16 年前

derobert 16 年前

除了一些非常基本的东西之外,我真的不了解b-树,但是让索引中最左边的列是方差最小的列更有效-对吗?

B-树索引有一个重要的特性需要注意:搜索任意的前缀全部钥匙,但不是后缀 . 如果你有索引 site_ip(site_id, ip) ,而你要求 where ip = 1.2.3.4 ,MySQL将不使用站点IP索引。如果你有 ip_site(ip, site_id) ,那么mysql将能够使用ip_站点索引。

这是b树索引的第二个特性,您也应该知道:它们是排序的。B树索引可用于 where site_id < 40 .

还要记住磁盘驱动器的一个重要特性:顺序读取很便宜,查找不便宜。如果使用的列不在索引中,mysql必须从表数据中读取该行。一般来说,这是一种缓慢的探索。因此,如果mysql认为它最终会像这样读取表的一小部分,那么它将忽略索引。一次大表扫描(顺序读取)通常比随机读取一个表中的几%的行要快。

顺便说一句,这同样适用于通过索引进行搜索。在B树中找到一个键实际上可能需要一些查找,所以您会发现 WHERE site_id > 800 AND ip = '1.2.3.4' 不能使用 site_ip 索引,因为每个站点需要几个索引来查找该站点1.2.3.4记录的开头。这个 ip_site 不过,将使用索引。

最终,你必须充分利用基准测试和 EXPLAIN 找出数据库的最佳索引。记住,您可以根据需要自由添加和删除索引。非唯一索引不是数据模型的一部分;它们只是优化。

ps:benchmark innodb也有,它通常有更好的并发性能。与PostgreSQL相同。

Veer 16 年前

首先,如果使用IP作为字符串,请将其更改为int unsigned column,并使用inet_aton(expr)和inet_ntoa(expr)函数处理此问题。对整数值进行索引比对可变长度字符串进行索引更有效。

NeuroScr 16 年前

油井指数是用储存来换取性能的。如果你两者都想要,那就很难了。如果不知道您运行的所有查询及其每个间隔的数量,就很难进一步优化这个问题。

你所拥有的将起作用。如果遇到瓶颈,您需要找出它的CPU、RAM、磁盘和/或网络,并进行相应的调整。过早优化是困难的,也是错误的。

如果有任何更新,您可能想切换到innodb,其他的wise myisam适合插入/选择。另外,由于行的大小很小,您可以查看mysql cluster(nbd)。还有一个归档引擎可以帮助满足存储需求,但是5.1中的分区可能是一个更好的选择。

如果索引的顺序已经在所有查询中使用,则翻转索引的顺序没有任何意义。

但是让索引中最左边的列是方差最小的列更有效-对吗?

不确定,但我以前没听说过。在我看来,这项申请并不真实。索引顺序对于排序很重要,并且通过具有多个唯一的最早索引字段,允许更多可能的查询使用索引。