MySQL二进制(16)索引的大小是多少？

Question

我有一张超过 660 万行的表格。

我有一个名为 trip_id 的字段，其类型为 BINARY(16)。我觉得查询太慢了（0.2 秒）。这个查询大约每 3 秒运行一次。

在做出任何愚蠢的操作之前，我想知道如果将 trip_id 索引的大小从 full 减小到 12，是否会有所改善？

如果我尝试进一步调整我的查询，是否会有所改善？

谢谢

编辑：

查询：

SELECT      stop_times.stop_id
FROM        trips
LEFT JOIN   stop_times ON trips.trip_id = stop_times.trip_id
WHERE       trips.route_id  = '141'
GROUP BY    stop_times.stop_id
ORDER BY    trips.trip_headsign ASC,
            stop_times.stop_sequence ASC

trip_id BINARY(16)

行程ID是一个长度为16个字节的二进制数据。

route_id SMALLINT(3)

路线ID是一个3位数的短整型数据。

trip_headsign VARCHAR(50)

行程标记是一个最大长度为50个字符的可变长字符串。

stop_sequence SMALLINT(3)

停靠站序是一个3位数的短整型数据。

查询的解释:

- David Bélanger

---

660万条数据只用0.2秒？哎呀，这也太慢了吧，我差点就眨了一下眼睛...减小索引大小会降低性能——它将使用索引的12个字节，然后进行表扫描以找出其他4个字节。 - Marc B

@dystroy 我更新了，包括我的查询。Marc链接需要6/100，0.2可能可以接受，但当这个查询在Android应用程序下运行时，速度会更慢。我知道这可以更快。 - David Bélanger

@MarcB 取决于ID是什么。如果像GUID一样是随机的，那么第一个（或任何）12个字节通常也是唯一的机会相当大。 - millimoose

@millimoose 在 MD5 哈希中有 660 万行...所以是的，前12个字节会重复出现。 - David Bélanger

Answer 1

经过调查研究，我发现问题确实存在，因为0.2秒是相对较慢的。

SELECT      t.trip_headsign, st.stop_sequence, s.stop_code, s.stop_name
FROM        stop_times AS st
JOIN        stops AS s USING (stop_id)
JOIN        (   SELECT  trip_id,
                        route_id,
                        trip_headsign
                FROM    trips
                WHERE   route_id = '141'
                LIMIT   2
            ) AS t
WHERE       t.trip_id = st.trip_id
GROUP BY    st.stop_id

首先，不要使用LEFT JOIN，在这里使用JOIN更快。但是重要的是，在WHERE语句中匹配所有旅行结果。

然而，由于公交车只有两个方向，我只需要将结果限制为2个。现在，我的结果接近0.018。提高了1000%。

- David Bélanger

Answer 2

你的“Extra”列中出现了“Using temporary”和“Using filesort”。

这些都是你可以改进的明显迹象。它们出现的原因是由于你的GROUP和ORDER子句。

第一步：它们是否真的必要？你可能会发现，从头到尾，用消耗这些数据的语言进行排序更便宜。

第二步：如果你仍然需要ORDER BY，那么请查看MySQL文档中的ORDER BY Optimization。这里没有使用索引进行排序的原因是不同的GROUP BY和ORDER BY子句。

打破常规思维。你没有做任何聚合，所以也许分组并不是必要的。也许只需提取所有行，然后忽略重复的ID。

- Dancrumb

Answer 3

尝试在“route”索引中添加trip_headsign。因为您在ORDER BY中使用它，MySQL需要到实际表中获取与route_id匹配的索引中找到的每个记录的信息。如果您在解释的Extra列中没有看到“Using index”，那就意味着MySQL被迫返回实际表以获取其他信息。

- bobwienholt