在MySQL中为BIGINT列添加索引会有帮助吗？ [英] Would it help to add index to BIGINT column in MySQL?

问题描述

我有一个包含数百万个条目的表，以及一个具有 BIGINT（20）值的列，这些值对每行都是唯一的。它们不是主键，但在某些操作中，在 WHERE 中使用此列有数千个 SELECT s条款。

I have a table that will have millions of entries, and a column that has BIGINT(20) values that are unique to each row. They are not the primary key, but during certain operations, there are thousands of SELECTs using this column in the WHERE clause.

问：当条目数量增加到数百万时，是否会向此列添加索引？我知道它会用于文本值，但我不熟悉索引对 INT 或 BIGINT 的作用。

Q: Would adding an index to this column help when the amount of entries grows to the millions? I know it would for a text value, but I'm unfamiliar with what an index would do for INT or BIGINT.

样本 SELECT 会发生数千次与此类似：

A sample SELECT that would happen thousands of times is similar to this:

`SELECT * FROM table1 WHERE my_big_number=19287319283784

推荐答案

如果您有一个非常大的表，那么搜索未编制索引的值可能会非常慢。在MySQL术语中，这种查询最终是一个表扫描，这是一种说法必须依次对表中的每一行进行测试的方式。这显然不是最好的方法。

If you have a very large table, then searching against values that aren't indexed can be extremely slow. In MySQL terms this kind of query ends up being a "table scan" which is a way of saying it must test against each row in the table sequentially. This is obviously not the best way to do it.

添加索引有助于读取速度，但是你支付的价格会稍微慢一点写速度。在进行优化时总是需要权衡，但在你的情况下，读取时间的缩短将是巨大的，而写入时间的增加将是微不足道的。

Adding an index will help with read speeds, but the price you pay is slightly slower write speeds. There's always a trade-off when making an optimization, but in your case the reduction in read time would be immense while the increase in write time would be marginal.

保持在请注意，向大型表添加索引可能需要相当长的时间，因此在将生成数据应用到生产系统之前，请对其进行测试。该表可能会在 ALTER TABLE 语句的持续时间内被锁定。

Keep in mind that adding an index to a large table can take a considerable amount of time so do test this against production data before applying it to your production system. The table will likely be locked for the duration of the ALTER TABLE statement.

与往常一样，使用 EXPLAIN 查询您的查询以确定其执行策略。在你的情况下，它类似于：

As always, use EXPLAIN on your queries to determine their execution strategy. In your case it'd be something like:

EXPLAIN SELECT * FROM table1 WHERE my_big_number=19287319283784

这篇关于在MySQL中为BIGINT列添加索引会有帮助吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！