I have a table with 1,000,000 records and I'm running a statement that's deleting ~700k rows. The auto-increment-index is of course still at 1,000,001. The highest primary key afterwards for example 40,000.
我有一个包含1,000,000条记录的表，我正在运行一个正在删除~700k行的语句。自动增量索引当然仍然是1000001。最高的主键，比如40000。
After such a huge deletion of rows should I manually set the index back to 40,001 or optimize the table in any way? Or doesn't MySQL care for this huge gap when inserting new rows and using the index in select statements afterwards (in terms of speed)?
在删除了这么多行之后，我应该手动将索引设置为40,001还是以任何方式对表进行优化?或者MySQL在插入新行并在select语句中使用索引时(在速度方面)不关心这个巨大的差距吗?
2 个解决方案
#1
2
The MySQL manual says:
MySQL手册说:
OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows
如果您已经删除了表的很大一部分，或者您已经对具有可变长度行的表进行了许多更改，那么应该使用优化表。
But don't reset the primary key, it can mess things up. The INT datatype (presumably) has a lot of room to grow from 1M.
但是不要重置主键，它会把事情搞砸。INT数据类型(大概)有很大的空间从1M增长。
In terms of query speed, it doesn't matter whether the index value is at 1 000 000 or at 1 000 000 000.
在查询速度方面，索引值是否为1 000 000或1 000 000是无关紧要的。
#2
1
Using OPTIMIZE TABLE afterwards helps MySQL determine how best/fastest to peform queries, e.g. use index or just table scan? It uses what are called Statistics to do so, and so calling OPTIMIZE TABLE is basically giving it a hint that you've made a significant change.
之后使用优化表可以帮助MySQL确定如何最好/最快地进行peform查询，例如使用索引或仅仅进行表扫描?它使用所谓的统计数据来做，所以调用优化表基本上是在暗示你已经做出了重大的改变。
#1
2
The MySQL manual says:
MySQL手册说:
OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows
如果您已经删除了表的很大一部分，或者您已经对具有可变长度行的表进行了许多更改，那么应该使用优化表。
But don't reset the primary key, it can mess things up. The INT datatype (presumably) has a lot of room to grow from 1M.
但是不要重置主键，它会把事情搞砸。INT数据类型(大概)有很大的空间从1M增长。
In terms of query speed, it doesn't matter whether the index value is at 1 000 000 or at 1 000 000 000.
在查询速度方面，索引值是否为1 000 000或1 000 000是无关紧要的。
#2
1
Using OPTIMIZE TABLE afterwards helps MySQL determine how best/fastest to peform queries, e.g. use index or just table scan? It uses what are called Statistics to do so, and so calling OPTIMIZE TABLE is basically giving it a hint that you've made a significant change.
之后使用优化表可以帮助MySQL确定如何最好/最快地进行peform查询，例如使用索引或仅仅进行表扫描?它使用所谓的统计数据来做，所以调用优化表基本上是在暗示你已经做出了重大的改变。