Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 是否支持暂时让索引失效
|
username: vincentLi
[TiDB Usage Environment] Production Environment
[TiDB Version] 6.5.3
Migrating a table with 500 million rows is too inefficient, only 20,000 rows per minute. Is it possible to temporarily disable the index and re-enable it after the data is migrated to the new table?
What’s the difference between this and deleting the index first and then creating it later… Anyway, the index data needs to be regenerated…
Creating an index is quite fast.
Index invalidation is generally used in business scenarios where it is difficult to determine whether the index is still being used.
In large-scale operations, the difference is significant. To invalidate an index, I only need to remember the index name and change it. For re-keying, I need to unload the re-keying statement as it is and execute it later.
The experience with ORACLE shows that there are still significant differences in scenarios involving large-scale data insertion.
Delete and recreate the index. Increasing the concurrency parameters when creating the index will make it much faster.
“Invisibility” is only for the optimizer; invisible indexes can still be modified or deleted. The manual also doesn’t mention whether invisible indexes will be updated when a large amount of data is written. If they are updated, then the operation method mentioned by the original poster does not achieve the intended purpose. Has anyone tested this operation method?
Index failure, inserting data does not maintain the index, and finally rebuilding the index is the same as deleting the index.
Index invisibility, as the previous poster mentioned, it is just invisible, but inserting data still maintains the index simultaneously.
For the business, it can be temporarily disabled, but in your case, it is best to delete it first, then create a new index after the migration is complete.
Disabling the index temporarily only makes the optimizer not use the index. The index is still maintained during actual data insertion, so the insertion efficiency does not change. It is recommended to delete the index first, insert the data, and then rebuild the index, which is quite fast.
Delete and rebuild it. Since you are using version 6.5, the rebuilding process will be slower.
Invisible indexes are only unavailable for execution plans, the indexes are still there. It’s more appropriate to delete and rebuild them.
First delete the new table index, then create it after data migration.
Thank you, everyone. I found that the low efficiency was not actually caused by the index, but probably because batch insert was not used. After optimization, achieving 20,000 to 50,000 in under 10 seconds is still acceptable. Thanks, everyone. I also learned about invisible indexes, which should have significant differences from Oracle invalid indexes. Oracle invalid indexes can be rebuilt online, which is quite convenient.
Creating an index for 500 million records probably won’t be very fast either.
Why not create the index on the target end after the data migration is complete?
I learned something new, there’s also the usage of “invisible”.
Does TIDB have an index monitoring feature that periodically identifies unused indexes and then deletes them?
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: