具有多个共享列的 MySql 索引策略

Question

我们有一个数据库表，用于存储访问者的浏览器数据，并按多个不同的子类型进行分割。为简单起见，我们使用下面的表架构。查询基本上将针对任何单个 id 列、指标列、时间戳列(存储为自纪元以来的秒数)以及设备、浏览器或操作系统列之一。

我们将对星型与雪花架构进行性能测试(其中所有 id 都进入单个列，但随后添加一个附加列 id_type 以确定它是哪种类型的标识符)对于这个表，但只要星型模式(现在就是这样)在雪花性能的 80% 以内，我们就会保留它，因为它将使我们的加载过程变得更加容易。不过，在此之前，我想确保索引在星型模式上进行了优化。

create table browser_data (
id_1 int,
id_2 int,
id_3 int,
id_4 int,
metric varchar(20),
browser varchar(20),
device varchar(20),
os varchar(20),
timestamp bigint
)

仅在 id 列上创建单独的索引更好，还是在这些索引中也包含 metric 和 timestamp 列？

Answer 1

不要标准化“连续”值，例如DATETIME、FLOAT、INT。 一定将值保留在主表中。

当您将值移动到其他表(尤其是“雪花”)时，它会使基于值的查询速度变慢一点到慢很多。当您需要过滤主表中不存在的多个指标时，尤其会发生这种情况。由于“雪花”或“过度标准化”，其中任何一个都表现得很差:

WHERE a.x = 123 AND b.y = 345

ORDER BY a.x, b.y

至于创建什么索引——这完全取决于您需要执行的查询。因此，我强烈建议您根据暂定的CREATE TABLEs勾勒出可能的SELECTs。

INT 是 4 个字节。 TIMESTAMP 是 5，FLOAT 是 4，等等。也就是说，标准化这些东西在空间上也是低效的。

更多

在执行JOIN时，优化器几乎总是从一个表开始，然后移至另一个表，依此类推。(请参阅“嵌套循环联接”。)

例如(基于上面的“代码”)，当 2 列标准化时，并且您正在测试这些值，您手中没有两个 id，您只有两个值(value)观。这使得查询执行效率非常低。对于

SELECT ...
    FROM main
    JOIN a  USING(a_id)
    JOIN b  USING(b_id)
    WHERE a.x = 123 AND b.y = 345

以下很可能是“执行计划”:

1. 进入 a 查找 x=123 的行；获取这些行的id。这可能包括许多尚未由 b.y 过滤的行。 a 需要 INDEX(x)
2. 返回主表，查找具有这些 ID 的行。 main 需要 INDEX(a_id)。同样，可能会拖拽超出必要数量的行。
3. 只有现在，您才能访问 b(使用 b_id)来检查 y=345；扔掉你拖来拖去的不必要的行。 b 需要 INDEX(b_id)

请注意我关于“四处走动”的评论。盲目使用 * (在 SELECT * 中)会增加问题 - 在执行步骤时所有列都会被拖拽。

另一方面...如果x和y位于main表中，那么代码的工作方式如下:

WHERE main.x = 123
  AND main.y = 345

只需要INDEX(x,y)(无论顺序)。它可以快速准确地定位所需的行。

对于ORDER BY a.x, b.y，它不能在任何表上使用任何索引。因此，查询必须创建一个临时表，对其进行排序，然后按所需的顺序传递行。

但如果 x 和 y 位于同一个表中，则 INDEX(x,y) (按该顺序)可能对 ORDER BY x,y 有用，并避免使用 tmp 表和排序。

对于单个表，优化器可能会使用 WHERE 索引，也可能会使用 ORDER BY 索引，具体取决于月相。在某些情况下，一个索引可以同时用于两者——这是最佳的。

另一个注意事项:如果您还有 LIMIT 10，...如果避免 sort，则只需查看 10 行，而不是整个集合来自WHERE。