問題描述

在數(shù)據(jù)庫批量插入數(shù)據(jù)時，插入數(shù)據(jù)的速度是否與創(chuàng)建的索引有關(guān)？如果有關(guān)請給出指相關(guān)文檔，謝謝！

問題解答

當然會有影響，插入數(shù)據(jù)的時候會導(dǎo)致索性的更新。索性越多，插入會越慢。可以看文檔描述Although it can be tempting to create an indexes for every possible column used in a query, unnecessary indexes waste space and waste time for MySQL to determine which indexes to use. Indexes also add to the cost of inserts, updates, and deletes because each index must be updated. You must find the right balance to achieve fast queries using the optimal set of indexes.

索引對批量插入數(shù)據(jù)的影響非常大，道理很簡單，一方面是寫表的時候需要同時寫索引，另外就對于唯一索引需要檢查數(shù)據(jù)是否有重復(fù)。

對于大批量的數(shù)據(jù)導(dǎo)入，一般都是先把索引去掉，等數(shù)據(jù)導(dǎo)入完成后再重建索引。