我有一个表,存储有关第三方网站上的访问者会话的一些基本数据.这是它的结构：
id,site_id,unixtime,unixtime_last,ip_address,uid
有四个索引：id,site_id / unixtime,site_id / ip_address和site_id / uid
我们查询此表有许多不同类型的方法,并且所有方法都特定于site_id.带有unixtime的索引用于显示给定日期或时间范围的访问者列表.另外两个用于查找来自IP地址或“uid”的所有访问(为每个访问者创建的唯一cookie值),以及确定这是新访问者还是返回访问者.
显然,将site_id存储在3个索引中对于写入速度和存储都是低效的,但我认为没办法,因为我需要能够快速查询给定特定site_id的数据.
有关提高效率的想法吗？
除了一些非常基本的东西之外,我真的不了解B树,但是让索引的最左列是方差最小的那个更有效 – 对吗？因为我认为site_id是ip_address和uid的索引的第二列,但我认为这会使索引效率降低,因为IP和UID的变化将超过站点ID,因为我们只有大约8000每个数据库服务器的唯一站点,但每天在所有~8,000个站点中有数百万个唯一访问者.
我还考虑过完全从IP和UID索引中删除site_id,因为同一个访问者访问共享同一个数据库服务器的多个站点的可能性非常小,但是如果发生这种情况,我担心它可能会相当慢以确定这是否是此site_id的新访问者.查询将是这样的：
select id from sessions where uid = 'value' and site_id = 123 limit 1
…所以如果此访问者之前访问过此站点,则只需要在停止之前找到此site_id的一行.这不一定非常快,但速度可以接受.但是说我们有一个每天有50万访问者的网站,特定的访问者喜欢这个网站,每天去那里10次.现在,他们第一次碰巧遇到了同一个数据库服务器上的另一个站点.上述查询可能需要相当长的时间来搜索此UID的所有可能数千行,这些行分散在整个磁盘上,因为它不会为此站点ID找到一个.
任何有关使这个尽可能高效的见解将不胜感激:)
更新 – 这是一个MysqL 5.0的MyISAM表.我关心的是性能和存储空间.这个表读写都很重.如果我必须在性能和存储之间做出选择,我最关心的是性能 – 但两者都很重要.
I don’t really understand B-trees besides some very basic stuff,but it’s more efficient to have the left-most column of an index be the one with the least variance – correct?
您需要注意B树索引的一个重要属性：搜索完整键的任意前缀(但不是后缀)是可能的(有效).如果你有一个索引site_ip(site_id,ip),并且你要求ip = 1.2.3.4,那么MysqL将不会使用site_ip索引.如果您使用ip_site(ip,site_id),那么MysqL将能够使用ip_site索引.
这是你应该知道的B树索引的第二个属性：它们是有序的. b树索引可以用于诸如site_id<之类的查询. 40. 还要记住磁盘驱动器的一个重要特性：顺序读取是便宜的,而搜索则不是.如果使用的任何列不在索引中,MySQL必须从表数据中读取行.这通常是一种追求,而且很慢.因此,如果MySQL认为它会像这样读取表中的一小部分,那么它将忽略索引.一个大表扫描(顺序读取)通常比表中几乎百分比的行的随机读取更快. 顺便说一下,同样适用于通过索引进行搜索.在B树中查找密钥实际上可能需要一些搜索,因此您将发现WHERE site_id> 800 AND ip =’1.2.3.4’可能不使用site_ip索引,因为每个site_id需要几个索引来寻找该站点的1.2.3.4记录的开头.但是,将使用ip_site索引.
最终,您将不得不自由地使用基准测试和EXPLAIN来找出数据库的最佳索引.请记住,您可以根据需要自由添加和删除索引.非唯一索引不是数据模型的一部分;它们只是一种优化.