Jak MyISAM może być „szybszy” niż InnoDB, jeśli

• MyISAM musi dokonać odczytu dysku dla danych?
• InnoDB używa puli buforów do indeksów i danych, a MyISAM tylko do indeksu?

Jedynym sposobem, w jaki MyISAM może być szybszy niż InnoDB, byłby w tych wyjątkowych okolicznościach

MyISAM

Podczas czytania, indeksy stole MyISAM można przeczytać raz z pliku .myi i ładowane w MyISAM Key Cache (jak sortowano key_buffer_size ). Jak można szybciej czytać tabelę MyISAM .MYD? Z tym:

ALTER TABLE mytable ROW_FORMAT=Fixed;

Pisałem o tym w moich poprzednich postach

• Najlepsze z MyISAM i InnoDB (proszę przeczytać najpierw)
• Jaki jest wpływ na wydajność używania CHAR vs VARCHAR na polu o stałym rozmiarze? (TRADEOFF # 2)
• Zoptymalizowany my.cnf dla wysokiej klasy i zajętego serwera (pod nagłówkiem Replikacja )
• Który DBMS jest dobry do superszybkich odczytów i prostej struktury danych? (Ust. 3)

InnoDB

OK, a co z InnoDB? Czy InnoDB wykonuje jakieś dyskowe operacje wejścia / wyjścia dla zapytań? Zaskakujące, że tak !! Prawdopodobnie myślisz, że jestem szalony, że to mówię, ale to absolutna prawda, nawet w przypadku zapytań SELECT . W tym momencie prawdopodobnie zastanawiasz się: „Jak, na świecie, InnoDB robi dyskowe operacje we / wy dla zapytań?”

Wszystko to wraca do tego, że InnoDB jest silnikiem transakcyjnym do przechowywania danych na podstawie ACID . Aby InnoDB bycia transakcyjna, to musi wspierać Iin ACID, który jest izolacja. Technika utrzymywania izolacji dla transakcji odbywa się za pośrednictwem MVCC, Multiversion Concurrency Control . Mówiąc najprościej, InnoDB rejestruje wygląd danych przed próbą ich zmiany przez transakcje. Gdzie to się rejestruje? W systemowym pliku obszaru tabel, lepiej znanym jako ibdata1. To wymaga dysku I / O .

PORÓWNANIE

Ponieważ zarówno InnoDB, jak i MyISAM robią dyskowe operacje we / wy, jakie losowe czynniki decydują o tym, kto jest szybszy?

• Rozmiar kolumn
• Format kolumny
• Zestawy znaków
• Zakres wartości liczbowych (wymagające wystarczająco dużych liczb całkowitych)
• Rzędy dzielone na bloki (łączenie rzędów)
• Fragmentacja danych spowodowana przez DELETEsiUPDATEs
• Rozmiar klucza podstawowego (InnoDB ma indeks klastrowany, wymagający dwóch odnośników)
• Rozmiar wpisów indeksu
• i tak dalej...

W związku z tym w środowisku o wysokim stopniu odczytu tabela MyISAM ze stałym formatem wiersza może przewyższać odczyt InnoDB z puli buforów InnoDB, jeśli w dziennikach cofania zawartych w ibdata1 zapisywana jest wystarczająca ilość danych do obsługi zachowania transakcyjnego nałożone na dane InnoDB.

WNIOSEK

Zaplanuj dokładnie typy danych, zapytania i silnik pamięci masowej. Gdy dane rosną, przenoszenie danych może być bardzo trudne. Zapytaj Facebooka ...

W prostym świecie MyISAM jest szybszy do odczytu, a InnoDB do szybszego zapisu.

Gdy zaczniesz wprowadzać mieszane operacje odczytu / zapisu, InnoDB będzie również szybsze w przypadku odczytu, dzięki mechanizmowi blokowania wierszy.

Kilka lat temu napisałem porównanie silników pamięci masowej MySQL , które do dziś są prawdziwe, przedstawiając wyjątkowe różnice między MyISAM a InnoDB.

Z mojego doświadczenia wynika, że ​​powinieneś używać InnoDB do wszystkiego oprócz tabel obciążających pamięć podręczną, w których utrata danych z powodu uszkodzenia nie jest tak istotna.

Aby dodać tutaj odpowiedzi na różnice mechaniczne między dwoma silnikami, przedstawiam empiryczne badanie porównania prędkości.

Jeśli chodzi o czystą prędkość, nie zawsze jest tak, że MyISAM jest szybszy niż InnoDB, ale z mojego doświadczenia wynika, że ​​jest on szybszy w środowiskach roboczych PURE READ około 2,0-2,5 razy. Oczywiście nie jest to odpowiednie dla wszystkich środowisk - jak napisali inni, MyISAM nie ma takich transakcji, jak klucze obce.

Poniżej przeprowadziłem trochę testów porównawczych - użyłem Pythona do zapętlenia i biblioteki timeit do porównań czasowych. Dla zainteresowania załączyłem również silnik pamięci, który zapewnia najlepszą wydajność na całej płycie, chociaż jest odpowiedni tylko dla mniejszych tabel (ciągle napotykasz, The table 'tbl' is fullgdy przekroczysz limit pamięci MySQL). Cztery typy wybranych, na które patrzę, to:

1. wanilia WYBIERA
2. liczy się
3. WYBORY warunkowe
4. indeksowane i nieindeksowane podselekcje

Po pierwsze, utworzyłem trzy tabele, używając następującego SQL

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

z „MyISAM” zamienionym na „InnoDB” i „memory” w drugiej i trzeciej tabeli.

1) Wanilia wybiera

Pytanie: SELECT * FROM tbl WHERE index_col = xx

Wynik: remis

Ich szybkość jest zasadniczo taka sama i zgodnie z oczekiwaniami jest liniowa w liczbie kolumn do wyboru. InnoDB wydaje się nieco szybszy niż MyISAM, ale jest to naprawdę marginalne.

Kod:

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) Liczy się

Pytanie: SELECT count(*) FROM tbl

Wynik: MyISAM wygrywa

Ten pokazuje dużą różnicę między MyISAM a InnoDB - MyISAM (i pamięć) śledzi liczbę rekordów w tabeli, więc transakcja jest szybka i O (1). Ilość czasu potrzebna do zliczenia InnoDB wzrasta super-liniowo wraz z rozmiarem stołu w badanym zakresie. Podejrzewam, że wiele przyspieszeń z zapytań MyISAM, które są obserwowane w praktyce, wynika z podobnych efektów.

Kod:

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) Wybór warunkowy

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

Wynik: MyISAM wygrywa

Tutaj MyISAM i pamięć działają mniej więcej tak samo i pokonały InnoDB o około 50% w przypadku większych tabel. Jest to rodzaj zapytania, dla którego korzyści MyISAM wydają się zmaksymalizowane.

Kod:

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) Podselekcja

Wynik: InnoDB wygrywa

Dla tego zapytania utworzyłem dodatkowy zestaw tabel dla podselekcji. Każda z nich to po prostu dwie kolumny BIGINT, jedna z indeksem klucza podstawowego i jedna bez indeksu. Ze względu na duży rozmiar stołu nie testowałem silnika pamięci. Poleceniem tworzenia tabeli SQL było

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

gdzie ponownie „MyISAM” zastępuje się „InnoDB” w drugiej tabeli.

W tym zapytaniu zostawiam rozmiar tabeli wyboru na 1000000 i zamiast tego zmieniam rozmiar subselekcji kolumn.

Tutaj InnoDB wygrywa łatwo. Po przejściu do rozsądnej tabeli rozmiarów oba silniki skalują się liniowo z rozmiarem podselekcji. Indeks przyspiesza polecenie MyISAM, ale co ciekawe, ma niewielki wpływ na szybkość InnoDB. subSelect.png

Kod:

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) )

Myślę, że przesłaniem tego wszystkiego jest to, że jeśli naprawdę martwisz się szybkością, musisz przeprowadzić analizę porównawczą zapytań, a nie założyć, który silnik będzie bardziej odpowiedni.

Który jest szybszy? Albo może być szybszy. YMMV.

Którego powinieneś użyć? InnoDB - odporny na awarie itp.