В недрах документации MySQL на dev.mysql.com я как-то обнаружил упоминание о том, что в случае, если используется MyISAM, можно получить прирост в скорости чтения из таблицы в 5-7 раз, если читать данные из таблицы самостоятельно. Мне довольно долго хотелось проверить этот факт и вот, наконец, у меня дошли руки до того, чтобы это попробовать. Что из этого вышло, читайте под катом
Формат MyISAM — это, на самом деле, несколько форматов (а именно — несколько форматов хранения индексов и несколько типов хранения данных, для данных наиболее распространенные — это fixed и dynamic). Мне было интересно рассмотреть самый-самый простой случай: когда таблица имеет фиксированную длину записи, не содержит NULL полей и тот тип запросов, который мы собираемся тестировать — это простой SELECT * FROM tbl WHERE (условие) без участия индексов и других таблиц (то, что называется full scan).
В основном, мною двигал спортивный интерес. Тем не менее, результаты получились неплохими, поэтому, возможно, кто-нибудь, кроме меня, захочет попробовать применить этот метод на практике.
Изначально я рассматривал довольно много вариантов, как можно читать данные из MyISAM, в том числе и с использованием индексов. Для чтения напрямую из индексных файлов есть довольно хорошее описание в файле myisam.txt, который раньше распространялся с исходными кодами MySQL, а теперь его можно найти, например, в MariaDB. Для программы, которая хочет читать MyISAM вместе с индексными структурами, вероятно, потребуется собирать ощутимую часть MySQL. Хорошо, что это нужно сделать только один раз :).
Я же буду рассматривать standalone приложение, которое не зависит от исходных кодов MySQL и не использует индексы, а только лишь читает .MYD файлы напрямую, в самом простом, fixed, формате строк.
Формат хранения для разных структур MyISAM очень неплохо описан здесь: forge.mysql.com/wiki/MySQL_Internals_MyISAM. Формат же для fixed строк настолько прост, что его можно привести прямо в тексте статьи:
1) .MYD состоит из последовательно идущих строк, и только из них. Никакой служебной информации в .MYD нет
2) У каждой строки есть заголовок, в котором записана информация о NULL-полях и флаг, сигнализирующий о том, что строка удалена. Если NULL-полей нет, то длина заголовка составляет 1 байт
3) В строке поля идут в двоичном формате, в том порядке, в котором определены поля таблицы, с обратным порядком байт (little endian), без пропусков
Для разных типов MySQL двоичное представление разное, и оно прекрасно описано по ссылке выше
Предполагая, что вы собираетесь читать данные из .MYD из программы на языке C, вот несколько интересных моментов из моей реализации, которые касаются производительности:
1) если длина одной строки невелика (скажем, 10 байт), то даже при использовании fread() стоит читать не по одной строке, а, скажем, по 100 — для моей программы это ускорило чтение из файла где-то в 2 раза
2) скорее всего, доступ к отдельным полям будет невыровненным, поэтому, если ваша архитектура отличается от x86 (например, PPC), то не стоит использовать следующую простую конструкцию для того, чтобы вытащить какое-то конкретное поле: *(int*)(ptr) (ptr — указатель на начало поля, само поле, при этом, имеет тип int)
Помимо этого, необходимо озаботиться вопросами блокировки таблицы при начале чтения (это можно сделать с помощью LOCK TABLES tbl READ). Если этого не сделать, то есть шанс нарваться на «грязные» данные при чтении (вплоть до перезаписанных наполовину строк, если не повезло). С другой стороны, именно при таком сценарии чтения из MyISAM можно вполне безопасно игнорировать блокировки и читать из таблицы в тот момент, когда туда кто-то пишет, имея минимум побочных эффектов.
Да, MyISAM с fixed форматом строк настолько прост, что мы можем сами создавать таблицы для MySQL и наполнять их данными. Разве что, без индексов. Но они нужны далеко не всегда, а если они очень нужны, то можно впоследствии попросить MySQL создать их через ALTER TABLE.
Как известно, таблицы MyISAM состоят из 3 файлов: .frm, .MYI и .MYD. Мы знаем, как сгенерировать .MYD. Осталось сгенерировать .frm и .MYI. Вы, вероятно, спросите: а зачем нам вообще .MYI? Разве там не хранятся индексы (которых у нас не планируется)? На самом деле, там хранятся не только индексы. Об этом чуть позже.
Самый простой способ сгенерировать .frm и .MYI — это взять эти файлы у какого-нибудь «донора», то есть, другой таблицы. У этой таблицы не должно быть индексов, и эта таблица должна иметь структуру, которая соответствует нашему .MYD файлу, иначе MySQL прочитать такую таблицу не сможет :).
Если вы просто скопируете .frm и .MYI файлы у готовой таблицы и запишете в .MYD нужные строки, то, скорее всего, вы увидите, что MySQL считает, что в новоиспеченной таблице содержится 0 строк. Это должно навести нас на мысли, что в MyISAM всё не настолько просто :). На самом же деле, есть один довольно известный факт — в MyISAM таблицах есть информация о количестве строк, которая находится в одном из полей структуры этой таблицы. Осталось найти это поле и дело в шляпе :)! Как ни странно, это поле находится не в .frm файле, а в .MYI. Да, в том самом файле, в котором лежат «индексы». Там же по совместительству лежит другая мета-информация, которая является специфичной для MyISAM и в .frm файл не пишется (.frm являются общими для нескольких видов таблиц).
В приведенном мной выше описании формата MyISAM есть описание MYI файлов. На самом деле, нам достаточно переписать лишь 2 поля в этом файле (для версии MySQL 5.1):
1) state->state.records (смещение относительно начала файла — 0x1C, длина 8 байт, прямой порядок байт (big endian), содержит общее количество строк за вычетом удаленных)
2) state->state.data_file_length (смещение относительно начала файла — 0x44, длина 8 байт, прямой порядок байт (big endian), содержит размер файла .MYD в байтах).
При возникновении проблем с чтением «новоиспеченных» таблиц стоит сначала проверить, что вы не переписываете существующую таблицу, а создаете новую. Если вы хотите записать таблицу вместо старой — сначала нужно сделать DROP TABLE этой таблицы непосредственно из MySQL, и только потом создавать эту таблицу заново (чтобы MySQL случайно не заюзал кеш открытых файлов). Другой вариант — сделать FLUSH TABLE для этой таблицы и после этого записывать туда новые данные (пригодится, если вы делаете APPEND к таблице).
Итак, если вы прочли статью до конца (и если вы знаете С), то у вас должно быть достаточно знаний, чтобы самому написать на С программу, которая читает и пишет простенькие MyISAM таблицы. В зависимости от сложности запросов, которые вы хотите исполнять с помощью вашей программы, прирост скорости может быть до 5-6 раз. У меня получилось написать программу, которая делала что-то полезное, читая целиком таблицу MyISAM, за время, которое составляло где-то 1/4 от выполнения того же самого запроса непосредственно в MySQL. По-моему, неплохо. Кстати, программа получилась всего на ~5 Кб и ~150 строк кода на Си.
Так что, если вам вдруг очень нужна супер-высокая производительность full scan, то попробуйте MyISAM + свою программу на С для чтения из таблицы. Вы будете приятно удивлены, насколько просто это реализовать, и какой малой кровью вы сможете поднять производительность чтения в несколько раз.
UPD
Вы просили цифры? Вот вам немножко:
1. Цифры будут очень сильно зависеть от реализации. Я уже написал, что у меня получилось ускорить нужный мне запрос в 4 раза. Итого, скорость чтения составила 1 Гб/сек при размере записи в 25 байт
2. Различия при FULL SCAN при использовании HANDLER или SELECT я не обнаружил.
FULL SCAN в MyISAM дает скорость чтения, по моим оценкам, как минимум в 3-4 раза больше, чем FULL SCAN в InnoDB.
3. Сам по себе FULL SCAN в MyISAM работает в 5-10 раз быстрее, чем скан по индексу с таким же числом записей (при некотором желании, отставание можно сократить до где-то 2-3 раз).
4. В InnoDB скан по PRIMARY KEY работает также быстро, как FULL SCAN, скан по любому другому индексу работает где-то в 3-4 раза медленней.
Сравнения с NoSQL не имеют особого смысла, если делается FULL SCAN для большой таблицы:
1. Memcached не умеет делать FULL SCAN памяти.
2. HandlerSocket хоть и умеет FULL SCAN, но по скорости от SELECT/HANDLER не отличается (если много строк). К тому же, в MySQL 5.1 он не поддерживается
3. Mongo, Redis, etc занимают на порядок больше памяти/стораджа, что во многих случаях абсолютно неприемлимо. Они работают быстро, спору нет. Но предназначение у них различается
Какого типа данные мы будем читать из MyISAM?
Формат MyISAM — это, на самом деле, несколько форматов (а именно — несколько форматов хранения индексов и несколько типов хранения данных, для данных наиболее распространенные — это fixed и dynamic). Мне было интересно рассмотреть самый-самый простой случай: когда таблица имеет фиксированную длину записи, не содержит NULL полей и тот тип запросов, который мы собираемся тестировать — это простой SELECT * FROM tbl WHERE (условие) без участия индексов и других таблиц (то, что называется full scan).
Зачем?
В основном, мною двигал спортивный интерес. Тем не менее, результаты получились неплохими, поэтому, возможно, кто-нибудь, кроме меня, захочет попробовать применить этот метод на практике.
Как будем читать?
Изначально я рассматривал довольно много вариантов, как можно читать данные из MyISAM, в том числе и с использованием индексов. Для чтения напрямую из индексных файлов есть довольно хорошее описание в файле myisam.txt, который раньше распространялся с исходными кодами MySQL, а теперь его можно найти, например, в MariaDB. Для программы, которая хочет читать MyISAM вместе с индексными структурами, вероятно, потребуется собирать ощутимую часть MySQL. Хорошо, что это нужно сделать только один раз :).
Я же буду рассматривать standalone приложение, которое не зависит от исходных кодов MySQL и не использует индексы, а только лишь читает .MYD файлы напрямую, в самом простом, fixed, формате строк.
Формат хранения в .MYD
Формат хранения для разных структур MyISAM очень неплохо описан здесь: forge.mysql.com/wiki/MySQL_Internals_MyISAM. Формат же для fixed строк настолько прост, что его можно привести прямо в тексте статьи:
1) .MYD состоит из последовательно идущих строк, и только из них. Никакой служебной информации в .MYD нет
2) У каждой строки есть заголовок, в котором записана информация о NULL-полях и флаг, сигнализирующий о том, что строка удалена. Если NULL-полей нет, то длина заголовка составляет 1 байт
3) В строке поля идут в двоичном формате, в том порядке, в котором определены поля таблицы, с обратным порядком байт (little endian), без пропусков
Для разных типов MySQL двоичное представление разное, и оно прекрасно описано по ссылке выше
Как читать из .MYD
Предполагая, что вы собираетесь читать данные из .MYD из программы на языке C, вот несколько интересных моментов из моей реализации, которые касаются производительности:
1) если длина одной строки невелика (скажем, 10 байт), то даже при использовании fread() стоит читать не по одной строке, а, скажем, по 100 — для моей программы это ускорило чтение из файла где-то в 2 раза
2) скорее всего, доступ к отдельным полям будет невыровненным, поэтому, если ваша архитектура отличается от x86 (например, PPC), то не стоит использовать следующую простую конструкцию для того, чтобы вытащить какое-то конкретное поле: *(int*)(ptr) (ptr — указатель на начало поля, само поле, при этом, имеет тип int)
Помимо этого, необходимо озаботиться вопросами блокировки таблицы при начале чтения (это можно сделать с помощью LOCK TABLES tbl READ). Если этого не сделать, то есть шанс нарваться на «грязные» данные при чтении (вплоть до перезаписанных наполовину строк, если не повезло). С другой стороны, именно при таком сценарии чтения из MyISAM можно вполне безопасно игнорировать блокировки и читать из таблицы в тот момент, когда туда кто-то пишет, имея минимум побочных эффектов.
Вы что-то говорили про запись?
Да, MyISAM с fixed форматом строк настолько прост, что мы можем сами создавать таблицы для MySQL и наполнять их данными. Разве что, без индексов. Но они нужны далеко не всегда, а если они очень нужны, то можно впоследствии попросить MySQL создать их через ALTER TABLE.
Что нам потребуется
Как известно, таблицы MyISAM состоят из 3 файлов: .frm, .MYI и .MYD. Мы знаем, как сгенерировать .MYD. Осталось сгенерировать .frm и .MYI. Вы, вероятно, спросите: а зачем нам вообще .MYI? Разве там не хранятся индексы (которых у нас не планируется)? На самом деле, там хранятся не только индексы. Об этом чуть позже.
Самый простой способ сгенерировать .frm и .MYI — это взять эти файлы у какого-нибудь «донора», то есть, другой таблицы. У этой таблицы не должно быть индексов, и эта таблица должна иметь структуру, которая соответствует нашему .MYD файлу, иначе MySQL прочитать такую таблицу не сможет :).
Если вы просто скопируете .frm и .MYI файлы у готовой таблицы и запишете в .MYD нужные строки, то, скорее всего, вы увидите, что MySQL считает, что в новоиспеченной таблице содержится 0 строк. Это должно навести нас на мысли, что в MyISAM всё не настолько просто :). На самом же деле, есть один довольно известный факт — в MyISAM таблицах есть информация о количестве строк, которая находится в одном из полей структуры этой таблицы. Осталось найти это поле и дело в шляпе :)! Как ни странно, это поле находится не в .frm файле, а в .MYI. Да, в том самом файле, в котором лежат «индексы». Там же по совместительству лежит другая мета-информация, которая является специфичной для MyISAM и в .frm файл не пишется (.frm являются общими для нескольких видов таблиц).
В приведенном мной выше описании формата MyISAM есть описание MYI файлов. На самом деле, нам достаточно переписать лишь 2 поля в этом файле (для версии MySQL 5.1):
1) state->state.records (смещение относительно начала файла — 0x1C, длина 8 байт, прямой порядок байт (big endian), содержит общее количество строк за вычетом удаленных)
2) state->state.data_file_length (смещение относительно начала файла — 0x44, длина 8 байт, прямой порядок байт (big endian), содержит размер файла .MYD в байтах).
При возникновении проблем с чтением «новоиспеченных» таблиц стоит сначала проверить, что вы не переписываете существующую таблицу, а создаете новую. Если вы хотите записать таблицу вместо старой — сначала нужно сделать DROP TABLE этой таблицы непосредственно из MySQL, и только потом создавать эту таблицу заново (чтобы MySQL случайно не заюзал кеш открытых файлов). Другой вариант — сделать FLUSH TABLE для этой таблицы и после этого записывать туда новые данные (пригодится, если вы делаете APPEND к таблице).
Итог
Итак, если вы прочли статью до конца (и если вы знаете С), то у вас должно быть достаточно знаний, чтобы самому написать на С программу, которая читает и пишет простенькие MyISAM таблицы. В зависимости от сложности запросов, которые вы хотите исполнять с помощью вашей программы, прирост скорости может быть до 5-6 раз. У меня получилось написать программу, которая делала что-то полезное, читая целиком таблицу MyISAM, за время, которое составляло где-то 1/4 от выполнения того же самого запроса непосредственно в MySQL. По-моему, неплохо. Кстати, программа получилась всего на ~5 Кб и ~150 строк кода на Си.
Так что, если вам вдруг очень нужна супер-высокая производительность full scan, то попробуйте MyISAM + свою программу на С для чтения из таблицы. Вы будете приятно удивлены, насколько просто это реализовать, и какой малой кровью вы сможете поднять производительность чтения в несколько раз.
UPD
Вы просили цифры? Вот вам немножко:
1. Цифры будут очень сильно зависеть от реализации. Я уже написал, что у меня получилось ускорить нужный мне запрос в 4 раза. Итого, скорость чтения составила 1 Гб/сек при размере записи в 25 байт
2. Различия при FULL SCAN при использовании HANDLER или SELECT я не обнаружил.
FULL SCAN в MyISAM дает скорость чтения, по моим оценкам, как минимум в 3-4 раза больше, чем FULL SCAN в InnoDB.
3. Сам по себе FULL SCAN в MyISAM работает в 5-10 раз быстрее, чем скан по индексу с таким же числом записей (при некотором желании, отставание можно сократить до где-то 2-3 раз).
4. В InnoDB скан по PRIMARY KEY работает также быстро, как FULL SCAN, скан по любому другому индексу работает где-то в 3-4 раза медленней.
Сравнения с NoSQL не имеют особого смысла, если делается FULL SCAN для большой таблицы:
1. Memcached не умеет делать FULL SCAN памяти.
2. HandlerSocket хоть и умеет FULL SCAN, но по скорости от SELECT/HANDLER не отличается (если много строк). К тому же, в MySQL 5.1 он не поддерживается
3. Mongo, Redis, etc занимают на порядок больше памяти/стораджа, что во многих случаях абсолютно неприемлимо. Они работают быстро, спору нет. Но предназначение у них различается
