Pertanyaan MyISAM versus InnoDB [tutup]


Saya sedang mengerjakan proyek yang melibatkan banyak penulisan basis data, saya akan mengatakan (70% sisipan dan 30% dibaca). Rasio ini juga akan mencakup pembaruan yang saya anggap sebagai satu baca dan satu tulis. Pembacaan dapat menjadi kotor (misalnya saya tidak membutuhkan informasi akurat 100% pada saat membaca).
Tugas tersebut akan melakukan lebih dari 1 juta transaksi basis data satu jam.

Saya telah membaca banyak hal di web tentang perbedaan antara MyISAM dan InnoDB, dan MyISAM tampaknya pilihan yang jelas bagi saya untuk database / tabel tertentu yang akan saya gunakan untuk tugas ini. Dari apa yang sepertinya saya baca, InnoDB bagus jika transaksi diperlukan karena penguncian tingkat baris didukung.

Apakah ada yang punya pengalaman dengan jenis beban ini (atau lebih tinggi)? Apakah MyISAM cara untuk pergi?


807
2017-08-21 14:50


asal


Jawaban:


Saya punya waktu singkat didiskusikan pertanyaan ini dalam sebuah tabel sehingga Anda dapat menyimpulkan apakah harus pergi bersama InnoDB atau MyISAM.

Berikut ini adalah ikhtisar kecil tentang mesin penyimpanan db mana yang harus Anda gunakan dalam situasi apa:

                                                 MyISAM InnoDB
-------------------------------------------------- --------------
Diperlukan pencarian teks lengkap Ya 5.6.4
-------------------------------------------------- --------------
Perlu transaksi Ya
-------------------------------------------------- --------------
Pertanyaan yang sering dipilih Ya
-------------------------------------------------- --------------
Sering memasukkan, memperbarui, menghapus Ya
-------------------------------------------------- --------------
Penguncian baris (multi processing pada satu tabel) Ya
-------------------------------------------------- --------------
Desain dasar relasional Ya

Untuk meringkas:

Sering membaca, hampir tidak ada tulisan => MyISAM
Pencarian teks lengkap di MySQL <= 5.5 => MyISAM

Dalam semua keadaan lain, InnoDB biasanya adalah cara terbaik untuk pergi.


496
2017-07-22 22:01



Saya bukan ahli database, dan saya tidak berbicara berdasarkan pengalaman. Namun:

Tabel MyISAM menggunakan penguncian tingkat tabel. Berdasarkan perkiraan lalu lintas Anda, Anda memiliki hampir 200 tulis per detik. Dengan MyISAM, hanya satu yang bisa berlangsung kapan saja. Anda harus memastikan bahwa perangkat keras Anda dapat mengikuti transaksi ini untuk menghindari overrun, yaitu, satu kueri tidak boleh lebih dari 5 ms.

Itu menunjukkan kepada saya Anda akan membutuhkan mesin penyimpanan yang mendukung penguncian tingkat baris, yaitu InnoDB.

Di sisi lain, seharusnya cukup sepele untuk menulis beberapa skrip sederhana untuk mensimulasikan beban dengan setiap mesin penyimpanan, lalu bandingkan hasilnya.


263
2017-08-22 16:03



Orang sering berbicara tentang kinerja, membaca vs menulis, kunci asing, dll. Tetapi ada satu fitur must-have lain untuk mesin penyimpanan menurut saya: pembaruan atom.

Coba ini:

  1. Terbitkan UPDATE terhadap tabel MyISAM Anda yang membutuhkan waktu 5 detik.
  2. Saat UPDATE sedang berlangsung, misalnya 2,5 detik, tekan Ctrl-C untuk menginterupsi.
  3. Amati efeknya di atas meja. Berapa banyak baris yang diperbarui? Berapa banyak yang tidak diperbarui? Apakah tabelnya dapat dibaca, atau apakah rusak saat Anda menekan Ctrl-C?
  4. Coba eksperimen yang sama dengan UPDATE terhadap tabel InnoDB, mengganggu kueri yang sedang berlangsung.
  5. Amati tabel InnoDB. Nol baris diperbarui. InnoDB telah meyakinkan Anda memiliki pembaruan atom, dan jika pembaruan penuh tidak dapat dilakukan, itu menggulung kembali seluruh perubahan. Juga, meja tidak rusak. Ini berfungsi bahkan jika Anda menggunakannya killall -9 mysqld untuk mensimulasikan kecelakaan.

Kinerja diinginkan tentu saja, tetapi tidak kehilangan data harus mengalahkan itu.


172
2017-07-17 17:47



Saya telah bekerja pada sistem bervolume tinggi menggunakan MySQL dan saya sudah mencoba MyISAM dan InnoDB.

Saya menemukan bahwa penguncian tingkat tabel di MyISAM menyebabkan masalah kinerja yang serius untuk beban kerja kami yang terdengar serupa dengan Anda. Sayangnya saya juga menemukan bahwa kinerja di bawah InnoDB juga lebih buruk dari yang saya harapkan.

Pada akhirnya saya menyelesaikan masalah pertentangan dengan memecah-mecah data sedemikian rupa sehingga sisipan masuk ke tabel "panas" dan memilih tidak pernah menanyakan tabel panas.

Ini juga memungkinkan penghapusan (data sensitif terhadap waktu dan kami hanya mempertahankan nilai X hari) untuk terjadi pada tabel "basi" yang lagi-lagi tidak tersentuh oleh kueri pemilihan. InnoDB tampaknya memiliki kinerja yang buruk pada penghapusan massal jadi jika Anda berencana untuk membersihkan data, Anda mungkin ingin membuat struktur sedemikian rupa sehingga data lama berada dalam tabel basi yang dapat dengan mudah dihapus daripada menjalankan penghapusan di atasnya.

Tentu saja saya tidak tahu apa aplikasi Anda tetapi semoga ini memberi Anda beberapa wawasan ke dalam beberapa masalah dengan MyISAM dan InnoDB.


135
2017-09-16 21:57



Untuk beban dengan lebih banyak menulis dan membaca, Anda akan mendapat manfaat dari InnoDB. Karena InnoDB menyediakan penguncian baris daripada mengunci-tabel, Anda SELECTs dapat bersamaan, tidak hanya dengan satu sama lain tetapi juga dengan banyak INSERTs. Namun, kecuali Anda berniat untuk menggunakan transaksi SQL, atur komitmen InnoDB flush ke 2 (innodb_flush_log_at_trx_commit). Ini memberi Anda kembali banyak kinerja mentah yang Anda akan kehilangan ketika memindahkan tabel dari MyISAM ke InnoDB.

Juga, pertimbangkan untuk menambahkan replikasi. Ini memberi Anda beberapa skala pembacaan dan karena Anda menyatakan bacaan Anda tidak harus mutakhir, Anda dapat membiarkan replikasi tertinggal sedikit. Pastikan saja bahwa itu dapat mengejar di bawah apa pun kecuali lalu lintas terberat atau akan selalu ada di belakang dan tidak akan pernah menyusul. Namun, jika Anda pergi dengan cara ini, saya dengan kuat sarankan Anda mengisolasi membaca dari slave dan manajemen lag replikasi ke handler database Anda. Ini jauh lebih mudah jika kode aplikasi tidak tahu tentang ini.

Akhirnya, perhatikan beban tabel yang berbeda. Anda tidak akan memiliki rasio baca / tulis yang sama di semua tabel. Beberapa tabel yang lebih kecil dengan hampir 100% bacaan mampu bertahan MyISAM. Demikian juga, jika Anda memiliki beberapa tabel yang mendekati 100%, Anda dapat memanfaatkannya INSERT DELAYED, tetapi itu hanya didukung di MyISAM (yang DELAYED klausa diabaikan untuk tabel InnoDB).

Tetapi patokan untuk memastikan.


61
2018-01-05 23:39



Sedikit terlambat ke gim ... tapi ini cukup komprehensif posting saya menulis beberapa bulan yang lalu, merinci perbedaan utama antara MYISAM dan InnoDB. Ambil secangkir teh (dan mungkin biskuit), dan nikmati.


Perbedaan utama antara MyISAM dan InnoDB adalah dalam integritas referensial dan transaksi. Ada juga perbedaan lain seperti penguncian, rollback, dan pencarian teks lengkap.

Integritas referensial

Integritas referensial memastikan bahwa hubungan antar tabel tetap konsisten. Lebih khusus, ini berarti ketika sebuah tabel (misalnya Daftar) memiliki kunci asing (misalnya ID Produk) yang menunjuk ke tabel yang berbeda (misalnya Produk), ketika pembaruan atau penghapusan terjadi pada tabel runcing, perubahan ini mengalir ke tautan meja. Dalam contoh kami, jika suatu produk diganti namanya, kunci asing tabel penautan juga akan diperbarui; jika suatu produk dihapus dari tabel ‘Produk’, setiap cantuman yang mengarah ke entri yang dihapus juga akan dihapus. Selanjutnya, setiap daftar baru harus memiliki kunci asing yang menunjuk ke entri yang valid dan sudah ada.

InnoDB adalah DBMS relasional (RDBMS) dan dengan demikian memiliki integritas referensial, sementara MyISAM tidak.

Transaksi & Atomicity

Data dalam tabel dikelola menggunakan pernyataan Manipulasi Data Bahasa (DML), seperti SELECT, INSERT, UPDATE, dan DELETE. Grup transaksi dua atau lebih pernyataan DML bersama-sama menjadi satu unit kerja, jadi seluruh unit diterapkan, atau tidak satu pun dari itu.

MyISAM tidak mendukung transaksi sedangkan InnoDB melakukannya.

Jika suatu operasi terganggu ketika menggunakan tabel MyISAM, operasi dibatalkan segera, dan baris (atau bahkan data dalam setiap baris) yang terpengaruh tetap terpengaruh, bahkan jika operasi tidak selesai.

Jika suatu operasi terganggu saat menggunakan tabel InnoDB, karena menggunakan transaksi, yang memiliki atomicity, setiap transaksi yang tidak selesai tidak akan berlaku, karena tidak ada commit yang dibuat.

Table-locking vs Penguncian-baris

Ketika sebuah query berjalan melawan tabel MyISAM, seluruh tabel di mana itu query akan dikunci. Ini berarti permintaan berikutnya hanya akan dieksekusi setelah yang sekarang selesai. Jika Anda membaca tabel besar, dan / atau sering ada operasi baca dan tulis, ini dapat berarti banyak sekali permintaan.

Ketika sebuah query dijalankan terhadap sebuah tabel InnoDB, hanya baris (s) yang terlibat terkunci, sisa meja tetap tersedia untuk operasi CRUD. Ini berarti kueri dapat berjalan secara bersamaan di tabel yang sama, asalkan mereka tidak menggunakan baris yang sama.

Fitur ini di InnoDB dikenal sebagai konkurensi. Sama halnya dengan konkurensi, ada kelemahan utama yang berlaku untuk rentang pilih tabel, di mana ada overhead dalam beralih antara utas kernel, dan Anda harus menetapkan batas pada utas kernel untuk mencegah server terhenti .

Transaksi & Pengembalian

Ketika Anda menjalankan operasi di MyISAM, perubahan ditetapkan; di InnoDB, perubahan tersebut dapat dibatalkan. Perintah yang paling umum digunakan untuk mengontrol transaksi adalah COMMIT, ROLLBACK dan SAVEPOINT. 1. COMMIT - Anda dapat menulis beberapa operasi DML, tetapi perubahan hanya akan disimpan ketika COMMIT dibuat 2. ROLLBACK - Anda dapat membuang semua operasi yang belum dilakukan 3. SAVEPOINT - menyetel titik di daftar operasi operasi ROLLBACK dapat dikembalikan

Keandalan

MyISAM tidak menawarkan integritas data - Kegagalan hardware, shutdown yang tidak jelas dan operasi yang dibatalkan dapat menyebabkan data menjadi rusak. Ini akan membutuhkan perbaikan penuh atau membangun kembali indeks dan tabel.

InnoDB, di sisi lain, menggunakan log transaksional, buffer double-write dan checksumming otomatis dan validasi untuk mencegah korupsi. Sebelum InnoDB membuat perubahan, ia mencatat data sebelum transaksi ke dalam file tablespace sistem yang disebut ibdata1. Jika ada crash, InnoDB akan melakukan autorecover melalui replay dari log tersebut.

Pengindeksan FULLTEXT

InnoDB tidak mendukung pengindeksan FULLTEXT hingga MySQL versi 5.6.4. Pada saat penulisan posting ini, banyak versi shared hosting provider MySQL masih di bawah 5.6.4, yang berarti pengindeksan FULLTEXT tidak didukung untuk tabel InnoDB.

Namun, ini bukan alasan yang sah untuk menggunakan MyISAM. Sebaiknya ubah ke penyedia hosting yang mendukung versi MySQL terkini. Bukan berarti tabel MyISAM yang menggunakan pengindeksan FULLTEXT tidak dapat dikonversi ke tabel InnoDB.

Kesimpulan

Kesimpulannya, InnoDB harus menjadi mesin penyimpanan default pilihan Anda. Pilih MyISAM atau tipe data lainnya ketika mereka melayani kebutuhan spesifik.


61
2018-01-21 15:32



Untuk menambah berbagai pilihan tanggapan di sini yang mencakup perbedaan mekanis antara kedua mesin, saya menyajikan studi perbandingan kecepatan empiris.

Dalam hal kecepatan murni, tidak selalu kasus bahwa MyISAM lebih cepat daripada InnoDB tetapi dalam pengalaman saya cenderung lebih cepat untuk lingkungan kerja PURE READ dengan faktor sekitar 2,0-2,5 kali. Jelas ini tidak sesuai untuk semua lingkungan - seperti yang telah ditulis orang lain, MyISAM tidak memiliki hal-hal seperti transaksi dan kunci asing.

Saya telah melakukan sedikit pembandingan di bawah - Saya telah menggunakan python untuk looping dan pustaka timeit untuk perbandingan waktu. Untuk minat saya juga menyertakan mesin memori, ini memberikan kinerja terbaik di seluruh papan meskipun hanya cocok untuk tabel yang lebih kecil (Anda terus-menerus menghadapi The table 'tbl' is full ketika Anda melebihi batas memori MySQL). Empat jenis pilihan yang saya lihat adalah:

  1. PILIHAN vanili
  2. penting
  3. SELECT bersyarat
  4. sub-pilihan yang diindeks dan tidak diindeks

Pertama, saya membuat tiga tabel menggunakan SQL berikut

CREATE TABLE
    data_interrogation.test_table_myisam
    (
        index_col BIGINT NOT NULL AUTO_INCREMENT,
        value1 DOUBLE,
        value2 DOUBLE,
        value3 DOUBLE,
        value4 DOUBLE,
        PRIMARY KEY (index_col)
    )
    ENGINE=MyISAM DEFAULT CHARSET=utf8

dengan 'MyISAM' diganti untuk 'InnoDB' dan 'memori' di tabel kedua dan ketiga.

1) Vanila memilih

Pertanyaan: SELECT * FROM tbl WHERE index_col = xx

Hasil: seri

Comparison of vanilla selects by different database engines

Kecepatan ini secara umum sama, dan seperti yang diharapkan adalah linier dalam jumlah kolom yang akan dipilih. Tampaknya InnoDB sedikit lebih cepat dari MyISAM tetapi ini benar-benar marjinal.

Kode:

import timeit
import MySQLdb
import MySQLdb.cursors
import random
from random import randint

db = MySQLdb.connect(host="...", user="...", passwd="...", db="...", cursorclass=MySQLdb.cursors.DictCursor)
cur = db.cursor()

lengthOfTable = 100000

# Fill up the tables with random data
for x in xrange(lengthOfTable):
    rand1 = random.random()
    rand2 = random.random()
    rand3 = random.random()
    rand4 = random.random()

    insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
    insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
    insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

    cur.execute(insertString)
    cur.execute(insertString2)
    cur.execute(insertString3)

db.commit()

# Define a function to pull a certain number of records from these tables
def selectRandomRecords(testTable,numberOfRecords):

    for x in xrange(numberOfRecords):
        rand1 = randint(0,lengthOfTable)

        selectString = "SELECT * FROM " + testTable + " WHERE index_col = " + str(rand1)
        cur.execute(selectString)

setupString = "from __main__ import selectRandomRecords"

# Test time taken using timeit
myisam_times = []
innodb_times = []
memory_times = []

for theLength in [3,10,30,100,300,1000,3000,10000]:

    innodb_times.append( timeit.timeit('selectRandomRecords("test_table_innodb",' + str(theLength) + ')', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('selectRandomRecords("test_table_myisam",' + str(theLength) + ')', number=100, setup=setupString) )
    memory_times.append( timeit.timeit('selectRandomRecords("test_table_memory",' + str(theLength) + ')', number=100, setup=setupString) )

2) Hitungan

Pertanyaan: SELECT count(*) FROM tbl

Hasil: MyISAM menang

Comparison of counts by different database engines

Yang ini menunjukkan perbedaan besar antara MyISAM dan InnoDB - MyISAM (dan memori) melacak jumlah catatan dalam tabel, jadi transaksi ini cepat dan O (1). Jumlah waktu yang diperlukan untuk InnoDB untuk menghitung meningkat secara super-linear dengan ukuran tabel dalam rentang yang saya selidiki. Saya menduga banyak dari speed-up dari query MyISAM yang diamati dalam praktek adalah karena efek yang serupa.

Kode:

myisam_times = []
innodb_times = []
memory_times = []

# Define a function to count the records
def countRecords(testTable):

    selectString = "SELECT count(*) FROM " + testTable
    cur.execute(selectString)

setupString = "from __main__ import countRecords"

# Truncate the tables and re-fill with a set amount of data
for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]:

    truncateString = "TRUNCATE test_table_innodb"
    truncateString2 = "TRUNCATE test_table_myisam"
    truncateString3 = "TRUNCATE test_table_memory"

    cur.execute(truncateString)
    cur.execute(truncateString2)
    cur.execute(truncateString3)

    for x in xrange(theLength):
        rand1 = random.random()
        rand2 = random.random()
        rand3 = random.random()
        rand4 = random.random()

        insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

        cur.execute(insertString)
        cur.execute(insertString2)
        cur.execute(insertString3)

    db.commit()

    # Count and time the query
    innodb_times.append( timeit.timeit('countRecords("test_table_innodb")', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('countRecords("test_table_myisam")', number=100, setup=setupString) )
    memory_times.append( timeit.timeit('countRecords("test_table_memory")', number=100, setup=setupString) )

3) Pilihan bersyarat

Pertanyaan: SELECT * FROM tbl WHERE value1<0.5 AND value2<0.5 AND value3<0.5 AND value4<0.5

Hasil: MyISAM menang

Comparison of conditional selects by different database engines

Di sini, MyISAM dan memori melakukan kurang lebih sama, dan mengalahkan InnoDB sekitar 50% untuk tabel yang lebih besar. Ini adalah jenis kueri yang manfaat dari MyISAM tampaknya dimaksimalkan.

Kode:

myisam_times = []
innodb_times = []
memory_times = []

# Define a function to perform conditional selects
def conditionalSelect(testTable):
    selectString = "SELECT * FROM " + testTable + " WHERE value1 < 0.5 AND value2 < 0.5 AND value3 < 0.5 AND value4 < 0.5"
    cur.execute(selectString)

setupString = "from __main__ import conditionalSelect"

# Truncate the tables and re-fill with a set amount of data
for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]:

    truncateString = "TRUNCATE test_table_innodb"
    truncateString2 = "TRUNCATE test_table_myisam"
    truncateString3 = "TRUNCATE test_table_memory"

    cur.execute(truncateString)
    cur.execute(truncateString2)
    cur.execute(truncateString3)

    for x in xrange(theLength):
        rand1 = random.random()
        rand2 = random.random()
        rand3 = random.random()
        rand4 = random.random()

        insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

        cur.execute(insertString)
        cur.execute(insertString2)
        cur.execute(insertString3)

    db.commit()

    # Count and time the query
    innodb_times.append( timeit.timeit('conditionalSelect("test_table_innodb")', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('conditionalSelect("test_table_myisam")', number=100, setup=setupString) )
    memory_times.append( timeit.timeit('conditionalSelect("test_table_memory")', number=100, setup=setupString) )

4) Sub-pilih

Hasil: InnoDB menang

Untuk kueri ini, saya membuat kumpulan tabel tambahan untuk sub-pilih. Masing-masing hanya dua kolom BIGINT, satu dengan indeks kunci primer dan satu tanpa indeks. Karena ukuran meja yang besar, saya tidak menguji mesin memori. Perintah pembuatan tabel SQL adalah

CREATE TABLE
    subselect_myisam
    (
        index_col bigint NOT NULL,
        non_index_col bigint,
        PRIMARY KEY (index_col)
    )
    ENGINE=MyISAM DEFAULT CHARSET=utf8;

di mana sekali lagi, 'MyISAM' diganti dengan 'InnoDB' di tabel kedua.

Dalam kueri ini, saya membiarkan ukuran tabel pilihan pada 1000000 dan sebagai gantinya mengubah ukuran kolom yang dipilih.

Comparison of sub-selects by different database engines

Di sini, InnoDB menang dengan mudah. Setelah kita masuk ke tabel ukuran yang masuk akal, kedua skala mesin sejajar dengan ukuran sub-pilih. Indeks mempercepat perintah MyISAM tetapi menarik memiliki sedikit pengaruh pada kecepatan InnoDB. subSelect.png

Kode:

myisam_times = []
innodb_times = []
myisam_times_2 = []
innodb_times_2 = []

def subSelectRecordsIndexed(testTable,testSubSelect):
    selectString = "SELECT * FROM " + testTable + " WHERE index_col in ( SELECT index_col FROM " + testSubSelect + " )"
    cur.execute(selectString)

setupString = "from __main__ import subSelectRecordsIndexed"

def subSelectRecordsNotIndexed(testTable,testSubSelect):
    selectString = "SELECT * FROM " + testTable + " WHERE index_col in ( SELECT non_index_col FROM " + testSubSelect + " )"
    cur.execute(selectString)

setupString2 = "from __main__ import subSelectRecordsNotIndexed"

# Truncate the old tables, and re-fill with 1000000 records
truncateString = "TRUNCATE test_table_innodb"
truncateString2 = "TRUNCATE test_table_myisam"

cur.execute(truncateString)
cur.execute(truncateString2)

lengthOfTable = 1000000

# Fill up the tables with random data
for x in xrange(lengthOfTable):
    rand1 = random.random()
    rand2 = random.random()
    rand3 = random.random()
    rand4 = random.random()

    insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
    insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

    cur.execute(insertString)
    cur.execute(insertString2)

for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]:

    truncateString = "TRUNCATE subselect_innodb"
    truncateString2 = "TRUNCATE subselect_myisam"

    cur.execute(truncateString)
    cur.execute(truncateString2)

    # For each length, empty the table and re-fill it with random data
    rand_sample = sorted(random.sample(xrange(lengthOfTable), theLength))
    rand_sample_2 = random.sample(xrange(lengthOfTable), theLength)

    for (the_value_1,the_value_2) in zip(rand_sample,rand_sample_2):
        insertString = "INSERT INTO subselect_innodb (index_col,non_index_col) VALUES (" + str(the_value_1) + "," + str(the_value_2) + ")"
        insertString2 = "INSERT INTO subselect_myisam (index_col,non_index_col) VALUES (" + str(the_value_1) + "," + str(the_value_2) + ")"

        cur.execute(insertString)
        cur.execute(insertString2)

    db.commit()

    # Finally, time the queries
    innodb_times.append( timeit.timeit('subSelectRecordsIndexed("test_table_innodb","subselect_innodb")', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('subSelectRecordsIndexed("test_table_myisam","subselect_myisam")', number=100, setup=setupString) )

    innodb_times_2.append( timeit.timeit('subSelectRecordsNotIndexed("test_table_innodb","subselect_innodb")', number=100, setup=setupString2) )
    myisam_times_2.append( timeit.timeit('subSelectRecordsNotIndexed("test_table_myisam","subselect_myisam")', number=100, setup=setupString2) )

Saya pikir pesan yang dibawa pulang dari semua ini adalah bahwa jika Anda sangat prihatin tentang kecepatan, Anda perlu membandingkan pertanyaan yang Anda lakukan daripada membuat asumsi tentang mesin mana yang akan lebih cocok.


51
2018-06-11 09:15



Sedikit di luar topik, tetapi untuk tujuan dokumentasi dan kelengkapan, saya ingin menambahkan yang berikut.

Secara umum menggunakan InnoDB akan menghasilkan banyak aplikasi yang kompleks, mungkin juga lebih bebas bug. Karena Anda dapat menempatkan semua integritas referensial (Kunci Asing-kendala) ke dalam datamodel, Anda tidak perlu mendekati sebanyak kode aplikasi yang Anda perlukan dengan MyISAM.

Setiap kali Anda memasukkan, menghapus, atau mengganti rekaman, Anda HARUS memeriksa dan menjaga hubungan. Misalnya. jika Anda menghapus orang tua, semua anak juga harus dihapus. Misalnya, bahkan dalam sistem blogging sederhana, jika Anda menghapus catatan blogposting, Anda harus menghapus catatan komentar, suka, dll. Di InnoDB ini dilakukan secara otomatis oleh mesin database (jika Anda menetapkan contraints dalam model ) dan tidak memerlukan kode aplikasi. Dalam MyISAM ini harus dikodekan ke dalam aplikasi, yang sangat sulit di web-server. Web-server secara alami sangat bersamaan / paralel dan karena tindakan ini harus bersifat atom dan MyISAM tidak mendukung transaksi nyata, menggunakan MyISAM untuk web-server berisiko / rawan kesalahan.

Juga dalam kebanyakan kasus umum, InnoDB akan tampil jauh lebih baik, karena banyak alasan, salah satunya mampu menggunakan penguncian tingkat catatan sebagai lawan penguncian tingkat tabel. Tidak hanya dalam situasi di mana menulis lebih sering daripada membaca, juga dalam situasi dengan gabungan kompleks pada dataset besar. Kami melihat peningkatan kinerja 3 kali lipat hanya dengan menggunakan tabel InnoDB di atas tabel MyISAM untuk bergabung sangat besar (mengambil beberapa menit).

Saya akan mengatakan bahwa pada umumnya InnoDB (menggunakan datamodel 3NF lengkap dengan integritas referensial) harus menjadi pilihan default ketika menggunakan MySQL. MyISAM hanya boleh digunakan dalam kasus-kasus yang sangat spesifik. Kemungkinan besar akan berkinerja lebih sedikit, menghasilkan aplikasi yang lebih besar dan lebih buggy.

Setelah mengatakan ini. Datamodelling adalah seni yang jarang ditemukan di antara webdesigners / -programmers. Jangan tersinggung, tetapi itu menjelaskan MyISAM digunakan begitu banyak.


32
2017-08-26 12:18



InnoDB menawarkan:

ACID transactions
row-level locking
foreign key constraints
automatic crash recovery
table compression (read/write)
spatial data types (no spatial indexes)

Di InnoDB semua data dalam satu baris kecuali TEXT dan BLOB dapat menempati maksimal 8.000 byte. Tidak ada pengindeksan teks lengkap tersedia untuk InnoDB. Di InnoDB, COUNT (*) s (ketika WHERE, GROUP BY, atau JOIN tidak digunakan) mengeksekusi lebih lambat daripada di MyISAM karena jumlah baris tidak disimpan secara internal. InnoDB menyimpan data dan indeks dalam satu file. InnoDB menggunakan buffer pool untuk menyimpan data dan indeks ke cache.

MyISAM menawarkan:

fast COUNT(*)s (when WHERE, GROUP BY, or JOIN is not used)
full text indexing
smaller disk footprint
very high table compression (read only)
spatial data types and indexes (R-tree)

MyISAM memiliki penguncian tingkat tabel, tetapi tidak ada penguncian tingkat baris. Tidak ada transaksi. Tidak ada pemulihan kerusakan otomatis, tetapi tidak menawarkan fungsi meja perbaikan. Tidak ada batasan kunci asing. Tabel MyISAM umumnya lebih kompak dalam ukuran pada disk jika dibandingkan dengan tabel InnoDB. Tabel MyISAM dapat lebih jauh dikurangi ukurannya dengan mengompresi dengan myisampack jika diperlukan, tetapi menjadi hanya-baca. MyISAM menyimpan indeks dalam satu file dan data di file lain. MyISAM menggunakan buffer kunci untuk indeks caching dan meninggalkan manajemen cache data ke sistem operasi.

Secara keseluruhan saya akan merekomendasikan InnoDB untuk sebagian besar tujuan dan MyISAM untuk penggunaan khusus saja. InnoDB sekarang adalah mesin default dalam versi MySQL baru.


29
2018-05-28 07:03