問題描述

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

問題解答

當(dāng)然會(huì)有影響，插入數(shù)據(jù)的時(shí)候會(huì)導(dǎo)致索性的更新。索性越多，插入會(huì)越慢。可以看文檔描述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.

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

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