افزايش سرعت insert در ديتابيس mysql - تفاوت موتورهاي InnoDB و MyISAM و عمليات Bulk Insert (اختصاصي)

[دانه کولانه]

تفاوت MyISAM vs INnoDB موتور ديتابيس MYSQL - دستور Bulk insert و خواندن اطلاعات از فايل LOAD DATA INFILE




اول خيلي سريع بگم كه در موتور Innodb بنده اعداد 1 تا 1000 رو توسط php در جدول درج كردم از 30 ثانيه بيشتر طول كشيد و خطاي تايم اوت 30 ثانيه رو داد و حدود 650 تاشو درج كرده بود


در حالي كه با دستور

LOAD DATA IN FILE


100 هزار عدد رو در كمتر از 1 ثانيه به داخل ديتابيس فرستادم ! (در همون innodb).

LOAD DATA INFILE 'c:\\1.txt' INTO TABLE `num`

در ضمن توليد اون 100000 عدد در پي اچ پي حدود 1 دقيقه طول كشيد اما در كمتر از 1 ثانيه ديتابيس تونست اونو لود كنه در همون Innodb كه ميخوايم بگيم نسبت به myisam كندتر هست .

همين تست رو براي Myisam گرفتم حدود 10 برابر سريعتر بود !:

اعداد رو دو برابر كردم يعني 200 هزار عدد رو طي :

199999 rows inserted. ( Query took 0.1062 sec )

به داخل جدولي از نوع myisam فرستادم ! . (يك دهم ثانيه 200 هزار اينسرت انجام داد )




و اين 200 هزار تا با innodb :

199999 rows inserted. ( Query took 1.9816 sec )

----------------------------
يك نكته : تا اونجايي كه من اطلاع دارم Bulk insert با insert معمولي متفاوت هست حداقل در اس كيو ال سرور مايكروسافت خود من بارها از دستور بالك كپي و BCP.exe
براي خواندن اطلاعات از فايلها استفاده كرده ام


براي مثال دستور در MS SQL SERVER (لااقل بعد از 2005 ) استفاده ميشه (BCP.exe هم فوق العاده س )

BULK INSERT myDatabase.MyTable FROM 'C:\MyTextFile.txt' WITH  FIELDTERMINATOR = ','

اما ايشون در اينجا بالك اينسرت رو با اينسرت معمولي يكي گرفته !
و تازه بر خلاف ادعاي اوليه خودش كه لود از فايل در ماي اس كيو ال بسيار (اكسترملي) سريع هست در بنچماركش اين نتيجه رو گرفته كه فرقي نكرده اند چندان و حتي اينسرت معمولي براش بهتر بوده .


با اين حال براي خود بنده اين دو خيلي متفاوت بود




داكيومنت MYSQL :

11.3. Bulk Insert




The logic behind bulk insert optimization is simple.
Instead of writing each key value to B-tree (that is, to the key cache, although the bulk insert code doesn't know about the key cache), we store keys in a balanced binary (red-black) tree, in memory. When this tree reaches its memory limit, we write all keys to disk (to key cache, that is). But since the key stream coming from the binary tree is already sorted, inserting goes much faster, all the necessary pages are already in cache, disk access is minimized, and so forth

براي بالا بردن ظرفيت bulk insert mysql :

Go ahead and add this to my.cnf

[mysqld]
bulk_inset_buffer_size=256M




You could also set it just for your session before launching extended INSERTs

SET bulk_insert_buffer_size= 1024 * 1024 * 256;

استفاده از بالك اينسرت و خواندن اطلاعات از فايل در ديتابيس MYSQL وآدرس دهي فايل مورد نظر در دستور LOAD DATA INFILE

LOAD DATA INFILE 'C:/DataSet.csv'
INTO TABLE test.LargeDataSet S
TERMINATED BY '\r\n';
FIELDS TERMINATED BY ',' LINE

حتما حواستون باشه كه ادرس فايل رو وقتي وارد ميكنيد بايد از / استفاده كنيد و نه \ (مطابق مثال بالا)

تيبلتون رو بايد مناسب محتويات فايل بسازيد دستور بالا رو نگاه كنيد متوجه ميشيد .