将索引添加到 MySQL 中的 BIGINT 列是否有帮助? [英] Would it help to add index to BIGINT column in MySQL?

问题描述

我有一个包含数百万个条目的表，以及一个包含每行唯一的 BIGINT(20) 值的列.它们不是主键，但在某些操作过程中，在 WHERE 子句中，有数千个 SELECT 使用此列.

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屋！