MyISAM против InnoDB

Question

Я работаю над проектами, которые включают много записей в базу данных, я бы сказал (70% вставок и 30% чтений). Это соотношение также включает в себя обновления, которые я считаю одним чтением и одной записью. Чтения могут быть грязными (например, мне не нужна 100% точная информация на момент чтения).
Рассматриваемая задача будет выполнять более 1 миллиона транзакций базы данных в час.

Я прочитал кучу материалов в Интернете о различиях между MyISAM и InnoDB, и MyISAM кажется мне очевидным выбором для конкретной базы данных / таблиц, которые я буду использовать для этой задачи. Из того, что я, похоже, читаю, InnoDB хорош, если нужны транзакции, поскольку поддерживается блокировка на уровне строк.

У кого-нибудь есть опыт работы с этим типом нагрузки (или выше)? MyISAM - это путь?

Answer 1

Я кратко обсудил этот вопрос в таблице, чтобы вы могли прийти к выводу, стоит ли идти с InnoDB или MyISAM.

Вот небольшой обзор того, какой механизм хранения БД вы должны использовать в какой ситуации:

                                                 MyISAM InnoDB
-------------------------------------------------- --------------
Требуется полнотекстовый поиск Да 5.6.4
----------------------------------------------------------------
Требовать транзакции Да
-------------------------------------------------- --------------
Частые запросы выбора Да      
-------------------------------------------------- --------------
Частая вставка, обновление, удаление Да
-------------------------------------------------- --------------
Блокировка строк (многократная обработка на одном столе) Да
-------------------------------------------------- --------------
Реляционный базовый дизайн Да

Подвести итоги:

Частое чтение, почти нет записи => MyISAM
Полнотекстовый поиск в MySQL <= 5.5      => MyISAM

При любых других обстоятельствах InnoDB обычно является наилучшим способом.

Answer 2

Я не эксперт по базам данных, и я не говорю из опыта. Тем не мение:

Таблицы MyISAM используют блокировку на уровне таблиц. Исходя из ваших оценок трафика, у вас есть около 200 записей в секунду. С MyISAM только один из них может быть запущен в любое время. Вы должны убедиться, что ваше оборудование может справиться с этой транзакцией, чтобы избежать перегрузки, т. Е. Один запрос может занять не более 5 мс.

Это говорит о том, что вам нужен механизм хранения, который поддерживает блокировку на уровне строк, то есть InnoDB.

С другой стороны, было бы довольно тривиально написать несколько простых сценариев для имитации нагрузки с каждым механизмом хранения, а затем сравнить результаты.

Answer 3

Люди часто говорят о производительности, чтениях и записи, внешних ключах и т. Д., Но, на мой взгляд, есть еще одна обязательная функция для механизма хранения: атомарные обновления.

Попробуй это:

1. Введите UPDATE для своей таблицы MyISAM, что займет 5 секунд.
2. Пока выполняется ОБНОВЛЕНИЕ, скажем, через 2,5 секунды, нажмите Ctrl-C, чтобы прервать его.
3. Обратите внимание на эффекты на столе. Сколько строк было обновлено? Сколько не было обновлено? Является ли таблица удобочитаемой, или она была повреждена при нажатии Ctrl-C?
4. Попробуйте тот же эксперимент с UPDATE для таблицы InnoDB, прервав выполняемый запрос.
5. Соблюдайте таблицу InnoDB. Нулевые строки были обновлены. InnoDB заверил, что у вас есть атомарные обновления, и если полное обновление не может быть зафиксировано, оно откатывает все изменения. Также таблица не повреждена. Это работает, даже если вы используете killall -9 mysqld симулировать аварию.

Производительность желательна, конечно, но не потеря данных должна превзойти это.

Answer 4

Я работал над системой большого объема, используя MySQL, и я пробовал и MyISAM, и InnoDB.

Я обнаружил, что блокировка на уровне таблицы в MyISAM вызывает серьезные проблемы с производительностью для нашей рабочей нагрузки, которая звучит похоже на вашу. К сожалению, я также обнаружил, что производительность в InnoDB также оказалась хуже, чем я надеялся.

В конце концов, я решил проблему с конфликтами, разбив данные таким образом, что вставки помещались в "горячую" таблицу и выбирали никогда не запрашиваемую горячую таблицу.

Это также позволило удалять (данные были чувствительны ко времени, и мы сохранили только X дней) в "устаревших" таблицах, которые снова не были затронуты запросами select. InnoDB, по-видимому, имеет низкую производительность при массовом удалении, поэтому, если вы планируете очистить данные, вы можете захотеть структурировать их таким образом, чтобы старые данные находились в устаревшей таблице, которую можно просто удалить вместо выполнения удалений в ней.

Конечно, я понятия не имею, что представляет собой ваше приложение, но, надеюсь, это даст вам некоторое представление о некоторых проблемах с MyISAM и InnoDB.

Answer 5

Немного опоздал к игре... но вот довольно обширный пост, который я написал несколько месяцев назад и подробно описывающий основные различия между MYISAM и InnoDB. Возьмите чашку чаю (и, возможно, печенье), и наслаждайтесь.

---

Основное различие между MyISAM и InnoDB заключается в ссылочной целостности и транзакциях. Есть и другие отличия, такие как блокировка, откат и полнотекстовый поиск.

Ссылочная целостность

Ссылочная целостность гарантирует, что отношения между таблицами остаются согласованными. Более конкретно, это означает, что когда таблица (например, листинги) имеет внешний ключ (например, идентификатор продукта), указывающий на другую таблицу (например, продукты), когда обновления или удаления происходят в указанной таблице, эти изменения каскадно связаны со связыванием Таблица. В нашем примере, если продукт переименован, внешние ключи таблицы связывания также обновятся; если продукт удален из таблицы "Продукты", любые списки, которые указывают на удаленную запись, также будут удалены. Кроме того, любой новый листинг должен иметь этот внешний ключ, указывающий на действующую существующую запись.

InnoDB является реляционной СУБД (RDBMS) и, следовательно, имеет ссылочную целостность, а MyISAM - нет.

Транзакции и атомарность

Управление данными в таблице осуществляется с помощью операторов языка манипулирования данными (DML), таких как SELECT, INSERT, UPDATE и DELETE. Группа транзакций объединяет два или более операторов DML в одну единицу работы, поэтому применяется либо весь блок, либо ни один из них.

MyISAM не поддерживает транзакции, тогда как InnoDB.

Если операция прерывается во время использования таблицы MyISAM, операция немедленно прерывается, и затрагиваемые строки (или даже данные в каждой строке) остаются затронутыми, даже если операция не была завершена.

Если операция прервана во время использования таблицы InnoDB, поскольку она использует транзакции, которые имеют атомарность, любая транзакция, которая не была завершена, не вступит в силу, так как никакая фиксация не выполняется.

Блокировка стола против блокировки ряда

Когда запрос выполняется к таблице MyISAM, вся таблица, к которой он обращается, будет заблокирована. Это означает, что последующие запросы будут выполняться только после завершения текущего. Если вы читаете большую таблицу и / или часто выполняются операции чтения и записи, это может привести к огромному отставанию в запросах.

Когда запрос выполняется к таблице InnoDB, блокируются только участвующие строки, остальная часть таблицы остается доступной для операций CRUD. Это означает, что запросы могут выполняться одновременно для одной и той же таблицы, если они не используют одну и ту же строку.

Эта функция в InnoDB известна как параллелизм. Как бы ни был параллелизм, есть существенный недостаток, который применяется к выбранному диапазону таблиц, заключающийся в том, что при переключении между потоками ядра возникают накладные расходы, и вы должны установить ограничение на потоки ядра, чтобы предотвратить остановку сервера.,

Транзакции и откаты

Когда вы запускаете операцию в MyISAM, изменения устанавливаются; в InnoDB эти изменения можно откатить. Наиболее распространенными командами, используемыми для управления транзакциями, являются COMMIT, ROLLBACK и SAVEPOINT. 1. COMMIT - вы можете написать несколько операций DML, но изменения будут сохранены только после выполнения COMMIT. 2. ROLLBACK - вы можете отменить все операции, которые еще не были совершены. 3. SAVEPOINT - устанавливает точку в списке операции, на которые операция ROLLBACK может выполнить откат

надежность

MyISAM не обеспечивает целостности данных - аппаратные сбои, нечистое завершение работы и отмененные операции могут привести к повреждению данных. Это потребует полного восстановления или перестроения индексов и таблиц.

InnoDB, с другой стороны, использует журнал транзакций, буфер двойной записи и автоматическое контрольное суммирование и проверку для предотвращения повреждения. Прежде чем InnoDB вносит какие-либо изменения, он записывает данные перед транзакциями в системный файл табличного пространства с именем ibdata1. Если происходит сбой, InnoDB будет автоматически восстанавливать через воспроизведение этих журналов.

ПОЛНАЯ ТЕКСТОВАЯ индексация

InnoDB не поддерживает индексацию FULLTEXT до версии MySQL 5.6.4. На момент написания этого поста версия MySQL многих провайдеров виртуального хостинга по-прежнему была ниже 5.6.4, что означает, что индексирование FULLTEXT не поддерживается для таблиц InnoDB.

Однако это не является веской причиной для использования MyISAM. Лучше всего перейти на хостинг-провайдера, который поддерживает современные версии MySQL. Не то чтобы таблица MyISAM, использующая индексирование FULLTEXT, не могла быть преобразована в таблицу InnoDB.

Заключение

В заключение, InnoDB должен быть выбранным вами хранилищем данных по умолчанию. Выберите MyISAM или другие типы данных, когда они удовлетворяют конкретной потребности.

Answer 6

Для загрузки с большим количеством операций записи и чтения вы получите выгоду от InnoDB. Поскольку InnoDB обеспечивает блокировку строк, а не блокировку таблиц, ваш SELECTмогут быть параллельными, не только друг с другом, но и со многими INSERTs. Однако, если вы не планируете использовать транзакции SQL, установите для параметра InnoDB commit flush значение 2 ( innodb_flush_log_at_trx_commit). Это возвращает вам большую необработанную производительность, которую вы в противном случае потеряли бы при перемещении таблиц из MyISAM в InnoDB.

Также рассмотрите возможность добавления репликации. Это дает вам некоторое масштабирование чтения, и, поскольку вы заявили, что ваши чтения не должны быть актуальными, вы можете позволить репликации немного отстать. Просто будьте уверены, что он может догнать что угодно, кроме самого интенсивного трафика, или он всегда будет позади и никогда не догонит. Однако, если вы пойдете по этому пути, я настоятельно рекомендую изолировать чтение от ведомых устройств и управление задержками репликации в обработчике базы данных. Это намного проще, если код приложения не знает об этом.

Наконец, следует помнить о различных нагрузках на таблицы. Вы не будете иметь одинаковое соотношение чтения / записи во всех таблицах. Некоторые таблицы меньшего размера с почти 100% чтением могут позволить себе остаться MyISAM. Аналогично, если у вас есть несколько таблиц, которые пишутся почти на 100%, вы можете воспользоваться INSERT DELAYED, но это поддерживается только в MyISAM (DELAYED предложение игнорируется для таблицы InnoDB).

Но ориентир безусловно.

Answer 7

В дополнение к широкому выбору ответов, касающихся механических различий между двумя двигателями, я приведу эмпирическое исследование сравнения скорости.

С точки зрения чистой скорости, MyISAM не всегда работает быстрее, чем InnoDB, но, по моему опыту, в рабочих средах PURE READ он работает быстрее примерно в 2,0-2,5 раза. Очевидно, что это не подходит для всех сред - как написали другие, в MyISAM отсутствуют такие вещи, как транзакции и внешние ключи.

Ниже я провел несколько сравнительных тестов - я использовал python для циклов и библиотеку timeit для сравнений по времени. Для интереса я также включил механизм памяти, это дает лучшую производительность по всем направлениям, хотя он подходит только для небольших таблиц (вы постоянно сталкиваетесь The table 'tbl' is full когда вы превышаете предел памяти MySQL). Я смотрю на четыре типа выбора:

1. ваниль ВЫБИРАЕТ
2. счетчики
3. условный ВЫБОР
4. индексированные и неиндексированные субвыборы

Во-первых, я создал три таблицы, используя следующий 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

с 'MyISAM', замененным 'InnoDB' и 'memory' во второй и третьей таблицах.

1) Ваниль выбирает

Запрос: SELECT * FROM tbl WHERE index_col = xx

Результат: ничья

Скорость их в целом одинакова и, как и ожидалось, является линейной по количеству столбцов, которые будут выбраны. InnoDB кажется немного быстрее, чем MyISAM, но это действительно незначительно.

Код:

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) Считает

Запрос: SELECT count(*) FROM tbl

Результат: MyISAM выигрывает

Этот демонстрирует большую разницу между MyISAM и InnoDB - MyISAM (и память) отслеживает количество записей в таблице, поэтому эта транзакция быстрая и O(1). Количество времени, необходимое для подсчета InnoDB, увеличивается сверхлинейно с размером таблицы в диапазоне, который я исследовал. Я подозреваю, что многие из ускорений от запросов MyISAM, которые наблюдаются на практике, связаны с подобными эффектами.

Код:

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) Условный выбор

Запрос: SELECT * FROM tbl WHERE value1<0.5 AND value2<0.5 AND value3<0.5 AND value4<0.5

Результат: MyISAM выигрывает

Здесь MyISAM и память работают примерно одинаково и бьют InnoDB примерно на 50% для больших таблиц. Это тот тип запроса, для которого преимущества MyISAM кажутся максимальными.

Код:

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) Подвыбирает

Результат: InnoDB выигрывает

Для этого запроса я создал дополнительный набор таблиц для дополнительного выбора. Каждый из них представляет собой просто два столбца BIGINT, один с индексом первичного ключа, а другой без индекса. Из-за большого размера таблицы я не тестировал движок памяти. Команда создания таблицы SQL была

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

где снова "MyISAM" заменяется на "InnoDB" во второй таблице.

В этом запросе я оставляю размер таблицы выбора равным 1000000 и вместо этого изменяю размер выбранных столбцов.

Здесь InnoDB выигрывает легко. После того, как мы доберемся до таблицы разумных размеров, оба двигателя масштабируются линейно с размером суб-выбора. Индекс ускоряет команду MyISAM, но, что интересно, мало влияет на скорость InnoDB. subSelect.png

Код:

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

Я думаю, что основная идея всего этого заключается в том, что если вы действительно беспокоитесь о скорости, вам нужно сравнивать запросы, которые вы делаете, а не делать какие-либо предположения о том, какой механизм будет более подходящим.

Answer 8

Немного не по теме, но для целей документирования и полноты я хотел бы добавить следующее.

В целом, использование InnoDB приведет к гораздо МЕНЬШЕ сложному приложению, вероятно, также более безглючному. Поскольку вы можете поместить всю ссылочную целостность (ограничения внешнего ключа) в модель данных, вам не нужно располагать столько кода приложения, сколько потребуется для MyISAM.

Каждый раз, когда вы вставляете, удаляете или заменяете запись, вы ДОЛЖНЫ проверять и поддерживать отношения. Например, если вы удалите родителя, все дети должны быть удалены тоже. Например, даже в простой системе ведения блогов, если вы удалите запись публикации блога, вам придется удалить записи комментариев, лайки и т. Д. В InnoDB это выполняется автоматически механизмом базы данных (если вы указали ограничения в модели) и не требует кода приложения. В MyISAM это должно быть закодировано в приложении, что очень сложно для веб-серверов. Веб-серверы по своей природе очень параллельны / параллельны, и поскольку эти действия должны быть атомарными, а MyISAM не поддерживает реальных транзакций, использование MyISAM для веб-серверов сопряжено с риском / подвержено ошибкам.

Также в большинстве общих случаев InnoDB будет работать намного лучше, по множеству причин, одна из которых - возможность использовать блокировку на уровне записи, а не блокировку на уровне таблицы. Не только в ситуации, когда записи выполняются чаще, чем чтения, а также в ситуациях со сложными объединениями в больших наборах данных. Мы заметили 3-х кратное увеличение производительности только за счет использования таблиц InnoDB над таблицами MyISAM для очень больших объединений (занимающих несколько минут).

Я бы сказал, что в общем случае InnoDB (использующий модель данных 3NF с полной ссылочной целостностью) должен быть выбором по умолчанию при использовании MySQL. MyISAM следует использовать только в очень конкретных случаях. Скорее всего, он будет выполнять меньше, что приведет к большему и более глючному приложению.

Сказав это. Datamodelling - это искусство, редко встречающееся среди веб-дизайнеров / программистов. Без обид, но это объясняет, что MyISAM так часто используется.

Answer 9

InnoDB предлагает:

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

В InnoDB все данные подряд, кроме TEXT и BLOB, могут занимать не более 8000 байт. Нет полнотекстовой индексации для InnoDB. В InnoDB COUNT(*) (когда WHERE, GROUP BY или JOIN не используются) выполняются медленнее, чем в MyISAM, поскольку счетчик строк не хранится внутри. InnoDB хранит данные и индексы в одном файле. InnoDB использует пул буферов для кэширования данных и индексов.

MyISAM предлагает:

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 имеет блокировку на уровне таблицы, но не блокировку на уровне строки. Нет транзакций. Нет автоматического восстановления после сбоя, но он предлагает функциональность таблицы восстановления. Нет ограничений по внешнему ключу. Таблицы MyISAM обычно более компактны по размеру на диске по сравнению с таблицами InnoDB. Таблицы MyISAM могут быть значительно уменьшены в размерах путем сжатия с помощью myisampack, если это необходимо, но становятся доступными только для чтения. MyISAM хранит индексы в одном файле и данные в другом. MyISAM использует ключевые буферы для кэширования индексов и оставляет управление кэшированием данных операционной системе.

В целом, я бы порекомендовал InnoDB для большинства целей и MyISAM только для специализированных целей. InnoDB - теперь движок по умолчанию в новых версиях MySQL.

Answer 10

Вопрос и большинство ответов устарели.

Да, это история старых жен, что MyISAM работает быстрее, чем InnoDB. обратите внимание на дату Вопроса: 2008; сейчас почти десятилетие спустя. С тех пор InnoDB добился значительных успехов.

Драматический график был для одного случая, когда MyISAM выигрывает: COUNT(*) без WHERE пункт. Но разве это то, чем ты занимаешься?

Если вы запустите тест на параллелизм, InnoDB, скорее всего, победит, даже противMEMORY,

Если вы делаете какие-либо записи во время бенчмаркинга SELECTs, MyISAM и MEMORY могут потерять из-за блокировки на уровне таблицы.

На самом деле, Oracle настолько уверен, что InnoDB лучше, что они почти удалили MyISAM из 8.0.

Вопрос был написан в начале 5.1 дней. С тех пор эти основные версии были помечены как "общедоступные":

• 2010: 5,5 (0,8 в декабре)
• 2013: 5,6 (0,10 в феврале)
• 2015: 5,7 (0,9 в октябре)
• 2018: 8,0 (0,11 в апреле)

Итог: не используйте MyISAM

Answer 11

Если вы используете MyISAM, вы не будете делать никаких транзакций в час, если не будете считать каждый оператор DML транзакцией (которая в любом случае не будет долговременной или атомарной в случае сбоя).

Поэтому я думаю, что вы должны использовать InnoDB.

300 транзакций в секунду звучит довольно много. Если вам абсолютно необходимо, чтобы эти транзакции были долговечными при сбое питания, убедитесь, что ваша подсистема ввода-вывода может легко обрабатывать столько операций записи в секунду. Вам понадобится как минимум RAID-контроллер с кэш-памятью на батарейках.

Если вы можете нанести небольшой удар по долговечности, вы можете использовать InnoDB с innodb_flush_log_at_trx_commit, установленным в 0 или 2 (подробности см. В документации), вы можете повысить производительность.

Существует ряд патчей, которые могут увеличить параллелизм от Google и других - они могут быть интересны, если вы все еще не можете получить достаточную производительность без них.

Answer 12

MYISAM:

1. MYISAM поддерживает блокировку на уровне таблицы

2. MyISAM рассчитан на скорость

3. MyISAM не поддерживает внешние ключи, поэтому мы называем MySQL с MYISAM СУБД
4. MyISAM хранит свои таблицы, данные и индексы в дисковом пространстве, используя три разных файла. (tablename.FRM, tablename.MYD, tablename.MYI)
5. MYISAM не поддерживает транзакции. Вы не можете совершать и выполнять откат с MYISAM. Как только вы даете команду, все готово.

INNODB:

1. InnoDB поддерживает блокировку на уровне строк
2. InnoDB рассчитан на максимальную производительность при обработке большого объема данных
3. InnoDB поддерживает внешние ключи, поэтому мы называем MySQL с InnoDB RDBMS
4. InnoDB хранит свои таблицы и индексы в табличном пространстве
5. InnoDB поддерживает транзакции. Вы можете зафиксировать и откатить с InnoDB

Answer 13

Обратите внимание, что мое официальное образование и опыт работы с Oracle, в то время как моя работа с MySQL была полностью личной и в мое свободное время, поэтому, если я скажу то, что верно для Oracle, но не верно для MySQL, я приношу свои извинения. В то время как две системы имеют много общего, реляционная теория / алгебра одинаковы, а реляционные базы данных по-прежнему являются реляционными базами данных, все еще существует множество отличий!!

Мне особенно нравится (а также блокировка на уровне строк), что InnoDB основан на транзакциях, что означает, что вы можете обновлять / вставлять / создавать / изменять / удалять / и т.д. несколько раз для одной "операции" вашего веб-приложения. Проблема, которая возникает, заключается в том, что если только некоторые из этих изменений / операций в конечном итоге будут зафиксированы, а другие - нет, в большинстве случаев (в зависимости от конкретной структуры базы данных) вы получите базу данных с конфликтующими данными / структурой.

Примечание. В Oracle операторы create / alter / drop называются операторами "DDL" (определение данных) и неявно инициируют коммит. Операторы вставки / обновления / удаления, называемые "DML" (манипулирование данными), не фиксируются автоматически, а только когда выполняется DDL, фиксация или выход / выход (или если для сеанса установлено значение "автоматическая фиксация", или если ваш клиент автоматически фиксирует). Об этом необходимо знать при работе с Oracle, но я не уверен, как MySQL обрабатывает два типа операторов. Из-за этого я хочу прояснить, что я не уверен в этом, когда дело доходит до MySQL; только с Oracle.

Пример того, когда движки на основе транзакций превосходят:

Допустим, я или вы находитесь на веб-странице, чтобы зарегистрироваться для участия в бесплатном мероприятии, и одна из основных целей системы состоит в том, чтобы зарегистрировать до 100 человек, поскольку это ограничивает количество мест. для мероприятия. По достижении 100 регистраций система отключит дальнейшие регистрации, по крайней мере, до тех пор, пока другие не будут отменены.

В этом случае может быть таблица для гостей (имя, телефон, электронная почта и т. Д.) И вторая таблица, которая отслеживает количество гостей, которые зарегистрировались. Таким образом, у нас есть две операции для одной "транзакции". Теперь предположим, что после добавления информации о госте в таблицу GUESTS происходит потеря соединения или ошибка с тем же воздействием. Таблица GUESTS была обновлена ​​(вставлена ​​в), но соединение было потеряно до того, как "доступные места" могли быть обновлены.

Теперь к гостевой таблице добавлен гость, но количество свободных мест теперь неверно (например, значение равно 85, тогда как на самом деле это 84).

Конечно, есть много способов справиться с этим, например, отслеживание доступных мест с помощью "100 минус количество строк в таблице гостей" или некоторый код, который проверяет, что информация согласована и т. Д. Но с базой данных на основе транзакций Движок, такой как InnoDB, либо ВСЕ операции совершены, либо НЕТ. Это может быть полезно во многих случаях, но, как я уже сказал, это не ЕДИНСТВЕННЫЙ способ быть безопасным, нет (однако, это хороший способ, который обрабатывает база данных, а не программист / автор сценариев).

Все это "на основе транзакций" по сути означает в этом контексте, если я что-то упустил - либо вся транзакция завершится успешно, либо ничего не изменится, поскольку внесение только частичных изменений может привести к незначительному и серьезному беспорядку база данных, возможно, даже портит ее...

Но я скажу это еще раз, это не единственный способ избежать беспорядка. Но это один из методов, который обрабатывает сам движок, оставляя вам код / ​​сценарий, о котором нужно только беспокоиться, "была ли транзакция успешной или нет, и что мне делать, если нет (например, повторная попытка)", а не вручную написание кода для проверки его "вручную" из-за пределов базы данных и много работы для таких событий.

Наконец, примечание о блокировке таблиц и блокировках строк:

ОТКАЗ ОТ ОТВЕТСТВЕННОСТИ: Я могу ошибаться во всем, что следует в отношении MySQL, и гипотетические / примеры ситуаций - это вещи, на которые стоит обратить внимание, но я могу ошибаться в том , что именно может привести к повреждению в MySQL. Однако эти примеры очень реальны в общем программировании, даже если в MySQL есть больше механизмов, позволяющих избежать подобных вещей...

В любом случае, я вполне уверен, что согласен с теми, кто утверждал, что количество соединений, разрешенных за один раз , не работает за закрытым столом. Фактически, множественные соединения - это единственная цель блокировки таблицы! Так что другие процессы / пользователи / приложения не могут повредить базу данных, одновременно внося изменения.

Как два или более соединения, работающие в одном ряду, сделают ДЕЙСТВИТЕЛЬНО ПЛОХОЙ ДЕНЬ для вас?? Предположим, что есть два процесса, которые хотят / должны обновить одно и то же значение в одной и той же строке, скажем, потому что строка является записью автобусного тура, и каждый из двух процессов одновременно хочет обновить "riders" или "available_seats" поле как "текущее значение плюс 1"

Давайте сделаем это гипотетически, шаг за шагом:

1. Первый процесс считывает текущее значение, скажем, оно пустое, поэтому пока '0'.
2. Второй процесс также читает текущее значение, которое по-прежнему равно 0.
3. Процесс один пишет (текущий + 1), который равен 1.
4. Процесс два должен записывать 2, но поскольку он читает текущее значение, а процесс 1 записывает новое значение, он также записывает 1 в таблицу.

Я не уверен, что два соединения могут так смешиваться, оба читают, прежде чем первое пишет... Но если нет, то я все равно вижу проблему с:

1. Процесс один читает текущее значение, которое равно 0.
2. Процесс один пишет (текущий + 1), который равен 1.
3. Процесс два читает текущее значение сейчас. Но пока обрабатывается одна запись (обновление) DID, он не зафиксировал данные, поэтому только этот же процесс может прочитать новое значение, которое он обновил, в то время как все остальные видят старое значение, пока не будет зафиксирован коммит.

Кроме того, по крайней мере в базах данных Oracle существуют уровни изоляции, которые я не буду тратить впустую, пытаясь перефразировать. Вот хорошая статья на эту тему, и у каждого уровня изоляции есть свои плюсы и минусы, которые будут соответствовать тому, насколько важными могут быть механизмы на основе транзакций в базе данных...

Наконец, в MyISAM, вероятно, могут быть другие средства защиты вместо внешних ключей и взаимодействия на основе транзакций. Ну, во-первых, есть факт, что вся таблица заблокирована, что делает менее вероятным, что транзакции /FKs необходимы.

И, увы, если вы знаете об этих проблемах параллелизма, да, вы можете играть в них менее безопасно и просто писать свои приложения, настроить свои системы так, чтобы такие ошибки были невозможны (ваш код ответственен, а не сама база данных). Однако, по моему мнению, я бы сказал, что всегда лучше использовать как можно больше защитных мер, программируя в обороне и всегда осознавая, что человеческую ошибку невозможно полностью избежать. Это случается со всеми, и любой, кто говорит, что он неуязвим к этому, должен лгать или делать больше, чем просто написать приложение / скрипт "Hello World".;-)

Я надеюсь, что НЕКОТОРЫЕ из этого будут полезны кому-то, и даже более того, я надеюсь, что я не только сейчас был виновником предположений и человеком по ошибке!! Мои извинения, если да, но примеры, о которых стоит подумать, исследовать риск и так далее, даже если они не являются потенциальными в этом конкретном контексте.

Не стесняйтесь поправлять меня, редактируйте этот "ответ", даже проголосуйте за него. Просто, пожалуйста, попробуйте улучшить, а не исправлять мое неверное предположение другим.;-)

Это мой первый ответ, поэтому, пожалуйста, извините за длину из-за всех заявлений об отказе от ответственности и т.д... Я просто не хочу звучать высокомерно, когда я не совсем уверен!

Answer 14

Также проверьте некоторые вставные замены для самого MySQL:

MariaDB

http://mariadb.org/

MariaDB - это сервер базы данных, который предлагает функциональные возможности замены для MySQL. MariaDB построен некоторыми из первоначальных авторов MySQL при поддержке более широкого сообщества разработчиков свободного и открытого программного обеспечения. В дополнение к основной функциональности MySQL, MariaDB предлагает богатый набор улучшений функций, включая альтернативные механизмы хранения, оптимизацию серверов и исправления.

Percona Server

https://launchpad.net/percona-server

Усовершенствованная замена MySQL в виде вставки с улучшенной производительностью, улучшенной диагностикой и дополнительными функциями.

Answer 15

Я думаю, что это отличная статья для объяснения различий и того, когда вы должны использовать одно над другим: http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB

Answer 16

MyISAM

MyISAM engine это двигатель по умолчанию в большинстве MySQL Установки и является производной от исходного типа механизма ISAM, поддерживаемого в ранних версиях системы MySQL. Движок обеспечивает наилучшее сочетание производительности и функциональности, хотя ему не хватает транзакционных возможностей (используйте InnoDB или же BDB двигатели) и использует table-level locking,

FlashMAX и FlashMAX Connect: ведущая трансформация платформы Flash Загрузить сейчас Если вам не нужны транзакции, есть несколько баз данных и приложений, которые не могут быть эффективно сохранены с использованием механизма MyISAM. Однако очень высокопроизводительные приложения, в которых имеется большое количество вставок / обновлений данных по сравнению с количеством операций чтения, могут вызвать повышение производительности для механизма MyISAM. Первоначально он был разработан с идеей, что более 90% доступа базы данных к таблице MyISAM будут считывать, а не записывать.

При блокировке на уровне таблицы база данных с большим количеством вставок или обновлений строк становится узким местом производительности, поскольку таблица блокируется во время добавления данных. К счастью, это ограничение также хорошо работает в рамках базы данных без транзакций.

MyISAM Summary

Имя -МыСАМ

Введено -v3.23

Установка по умолчанию -Да

Ограничения данных -Нет

Ограничения индекса -64 индекса на таблицу (32 до 4.1.2); Макс 16 столбцов на индекс

Поддержка транзакций -Нет

Уровень блокировки

---

InnoDB

InnoDB Engine обеспечивается Innobase Oy и поддерживает все функции базы данных (и более) движка MyISAM, а также добавляет полные возможности транзакций (с полной ACID (Атомарность, согласованность, изоляция и долговечность) и блокировка данных на уровне строк.

Ключом к системе InnoDB является база данных, структура кэширования и индексации, где индексы и данные кэшируются в памяти, а также хранятся на диске. Это обеспечивает очень быстрое восстановление и работает даже с очень большими наборами данных. Поддерживая блокировку на уровне строк, вы можете добавлять данные в таблицу InnoDB без механизма блокировки таблицы при каждой вставке, что ускоряет как восстановление, так и хранение информации в базе данных.

Как с MyISAMСуществует несколько типов данных, которые невозможно эффективно сохранить в базе данных InnoDB. На самом деле, нет существенных причин, по которым вы не всегда должны использовать базу данных InnoDB. Накладные расходы на управление InnoDB это немного более обременительно, и на первый взгляд может оказаться сложным подобрать оптимальную оптимизацию для размеров оперативной памяти, дисковых кешей и файлов базы данных. Однако это также означает, что вы получаете большую гибкость по отношению к этим значениям, и после установки преимущества в производительности могут легко перевесить первоначальное затраченное время. Кроме того, вы можете позволить MySQL управлять этим автоматически для вас.

Если вы хотите (и можете) настроить параметры InnoDB для своего сервера, я бы порекомендовал вам потратить время на оптимизацию конфигурации вашего сервера, а затем использовать механизм InnoDB по умолчанию.

InnoDB Summary

Имя -InnoDB

Введено -v3.23 (только исходный код), v4.0 (исходный и двоичный код)

Установка по умолчанию -Нет

Ограничения данных -Нет

Ограничения индекса - Нет

Поддержка транзакций -Да (ACID-совместимый)

Уровень блокировки - ряд

Answer 17

По моему опыту, MyISAM был лучшим выбором, если вы не выполняете УДАЛЕНИЯ, ОБНОВЛЕНИЯ, множество отдельных INSERT, транзакций и полнотекстовой индексации. Кстати, проверить таблицу ужасно. Поскольку таблица стареет с точки зрения количества строк, вы не знаете, когда она закончится.

Answer 18

Я выяснил, что, хотя Myisam и ведет борьбу за блокировку, она по-прежнему быстрее, чем InnoDb, в большинстве сценариев из-за используемой схемы быстрого захвата блокировки. Я несколько раз пробовал Innodb и всегда возвращался к MyIsam по той или иной причине. Также InnoDB может сильно загружать процессор при больших нагрузках записи.

Answer 19

Я попытался запустить вставку случайных данных в таблицы MyISAM и InnoDB. Результат был довольно шокирующим. MyISAM потребовалось на несколько секунд меньше, чтобы вставить 1 миллион строк, чем InnoDB всего за 10 тысяч!

Answer 20

Каждое приложение имеет свой собственный профиль производительности для использования базы данных, и есть вероятность, что он со временем изменится.

Лучшее, что вы можете сделать, это проверить свои возможности. Переключение между MyISAM и InnoDB тривиально, поэтому загрузите некоторые тестовые данные и запустите Jmeter для своего сайта и посмотрите, что произойдет.

Answer 21

myisam является NOGO для такого типа рабочей нагрузки (записи с высокой степенью параллелизма), у меня нет такого большого опыта работы с innodb (тестировал его 3 раза и в каждом случае обнаруживал, что производительность отстойная, но прошло некоторое время с момента последнего теста), если вы Вы не обязаны запускать MySQL, попробуйте попробовать Postgres, поскольку он обрабатывает одновременные записи намного лучше

Answer 22

Короче говоря, InnoDB хорош, если вы работаете над чем-то, что требует надежной базы данных, которая может обрабатывать множество инструкций INSERT и UPDATE.

и MyISAM хорош, если вам нужна база данных, которая в основном будет выполнять много операций чтения (SELECT), а не записи (INSERT и UPDATES), учитывая ее недостаток в блокировке таблиц.

вы можете проверить;
Плюсы и минусы InnoDB
Плюсы и минусы MyISAM

Answer 23

Я знаю, что это не будет популярно, но здесь идет:

В myISAM отсутствует поддержка основ базы данных, таких как транзакции и ссылочная целостность, что часто приводит к сбоям в работе приложений с ошибками. Вы не можете не изучить правильные основы проектирования баз данных, если они даже не поддерживаются вашим механизмом БД.

Не использовать ссылочную целостность или транзакции в мире баз данных все равно, что не использовать объектно-ориентированное программирование в мире программного обеспечения.

InnoDB существует сейчас, используйте это вместо этого! Даже разработчики MySQL наконец-то признали, что в более новых версиях этот механизм должен быть заменен на движок по умолчанию, несмотря на то, что myISAM был исходным движком, который был по умолчанию во всех старых системах.

Нет, это не имеет значения, если вы читаете или пишете или какие у вас соображения по поводу производительности, использование myISAM может привести к целому ряду проблем, с которыми я только что столкнулся: я выполнял синхронизацию базы данных, и в то же время кто-то другой Доступ к приложению, которое получило доступ к таблице, установленной на myISAM. Из-за отсутствия поддержки транзакций и в целом низкой надежности этого движка это привело к сбою всей базы данных, и мне пришлось вручную перезапустить mysql!

За последние 15 лет разработки я использовал много баз данных и движков. MyISAM обрушился на меня около десятка раз за этот период, другие базы данных, только один раз! И это была база данных Microsoft SQL, где какой-то разработчик написал ошибочный код CLR (общеязыковая среда выполнения - в основном код C#, который выполняется внутри базы данных), кстати, это не было ошибкой ядра базы данных.

Я согласен с другими ответами, в которых говорится, что качественные высокодоступные и высокопроизводительные приложения не должны использовать myISAM, так как он не будет работать, он не будет надежным или достаточно стабильным, чтобы обеспечить бесперебойную работу. Смотрите ответ Билла Карвина для более подробной информации.

PS Должен любить, когда фанаты myISAM понижают голос, но не могут сказать, какая часть этого ответа неверна.

Answer 24

Для такого отношения чтения / записи я бы предположил, что InnoDB будет работать лучше. Так как вы хорошо справляетесь с грязным чтением, вы можете (если вы позволите) копировать его на подчиненное устройство и позволить всем своим прочтениям перейти на подчиненное устройство. Кроме того, рассмотрите возможность вставки навалом, а не по одной записи за раз.

Answer 25

Почти каждый раз, когда я начинаю новый проект, я задаю один и тот же вопрос в Google, чтобы узнать, получу ли я какие-нибудь новые ответы.

Это в конечном итоге сводится к - я беру последнюю версию MySQL и запускаю тесты.

У меня есть таблицы, где я хочу сделать поиск ключа / значения... и все. Мне нужно получить значение (0-512 байт) для хеш-ключа. На этой БД не так много транзакций. Таблица периодически получает обновления (полностью), но 0 транзакций.

Таким образом, мы не говорим о сложной системе, мы говорим о простом поиске... и о том, как (кроме создания резидентной ОЗУ таблицы) мы можем оптимизировать производительность.

Я также делаю тесты на других базах данных (например, NoSQL), чтобы узнать, есть ли где-нибудь, где я могу получить преимущество. Самое большое преимущество, которое я обнаружил, заключается в сопоставлении клавиш, но в плане поиска MyISAM в настоящее время возглавляет их все.

Хотя я не буду выполнять финансовые транзакции с таблицами MyISAM, но для простых поисков вы должны проверить это... обычно от 2 до 5 раз запросов / сек.

Проверьте это, я приветствую дебаты.

Answer 26

Если это 70% вставок и 30% чтения, то это больше похоже на сторону InnoDB.

Answer 27

Итог: если вы работаете в автономном режиме с выборками на больших порциях данных, MyISAM, вероятно, даст вам лучшую (намного лучшую) скорость.

Есть некоторые ситуации, когда MyISAM бесконечно более эффективен, чем InnoDB: при работе с большими дампами данных в автономном режиме (из-за блокировки таблицы).

Пример: я конвертировал CSV-файл (15M записей) из NOAA, в котором в качестве ключей используются поля VARCHAR. InnoDB работал вечно, даже с большими кусками доступной памяти.

это пример csv (первое и третье поля являются ключами).

USC00178998,20130101,TMAX,-22,,,7,0700
USC00178998,20130101,TMIN,-117,,,7,0700
USC00178998,20130101,TOBS,-28,,,7,0700
USC00178998,20130101,PRCP,0,T,,7,0700
USC00178998,20130101,SNOW,0,T,,7,

так как мне нужно запустить пакетное автономное обновление наблюдаемых погодных явлений, я использую таблицу MyISAM для получения данных и запускаю JOINS на ключах, чтобы я мог очистить входящий файл и заменить поля VARCHAR на ключи INT (которые связаны с внешние таблицы, в которых хранятся исходные значения VARCHAR).