Хранение файлов в MySQL и их быстрая раздача

Думаю у многих возникала необходимость хранить файлы, связанные с записью в таблице. Это может быть картинка к новости, аватар, загруженный пользователем файл — да все, что угодно. Обычно в этому случае поступают просто — файл ложится в файловую систему, а ссылка на него — в запись БД.
Но у такого классического похода множество недостатков:

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

Больше о проблемах, возникающих при хранении файлов отдельно от БД можно почитать в презентации SQL Antipatterns, раздел Phantom Files, страница 60. Кстати, автор презентации предлагает решение — хранить файлы прямо в БД, в поле типа BLOB. Правда следует замечание, что это должно быть взвешенное решение в каждом конкретном случае. Ведь при таком способе хранения файлов вебсервер должен при каждом запросе вызывать некий скрипт, который будет извлекать файл из БД и отдавать пользователю, что неминуемо отрицательно скажется на производительности.
Для поиска решения данной проблемы был проведен мозговой штурм и придумано несколько вариантов решения проблемы:

1. Перед удалением записи делать SELECT с тем же условием и получать имена файлов, которые надо удалить. Проблема в том, что если удаляемых файлов много, эта операция может занять некоторое время и по хорошему на это время надо блокировать таблицу на чтение и запись, а во многих случаях это недопустимо.
2. Перед удалением устанавливать у удаляемых записей метку «подлежит удалению», получить все записи с этой меткой и удалить файлы, связанные с этими записями, и наконец удалить все записи с этой меткой. Запросы, работающие с этой таблицей следует доработать, чтобы они не выбирали записи с установленным флагом. Недостатки — необходимость правки множества запросов, к тому же у нас в проекте записи на удаление отбираются достаточно сложным SELECT, которые нельзя переделать в один UPDATE.
3. Первые два способа пытаются решить проблему «потерянных» файлов при удалении записей в БД, которая возникает при «классическом» способе хранения файлов, однако они не решают остальных проблем такого подхода, поэтому мы попытались придумать решения, использующие положительные моменты хранения файлов прямо в БД и избавиться от недостатков, присущих этому подходу.
4. Использовать триггеры. К сожалению, MySQL не имеет в своем языке поддержки команд работы с файлами, такие команды пришлось бы реализовывать самостоятельно, ковыряясь в исходниках MySQL. Из минусов — файлы должны храниться на том же хосте, что и БД, необходимость доработки MySQL, таких готовых решений мы не нашли.
5. Хранить файлы в БД, но отдавать их напрямую вебсервером, без участия PHP. Реализовать это можно, написав модуль к вебсерверу (nginx например) который позволял бы отдавать файлы напрямую из MySQL или применив драйвер файловой системы MySQLfs. Такой подход решает все перечисленные выше проблемы, но его недостаток — дополнительные накладные расходы на хранение файлов в MySQL.
6. Специализированный Storage Engine для MySQL, хранящий записи как файлы.

Остановимся более подробно а последнем пункте. Ведь что собой представляет файловая система — это специализированная БД, которая по ключу «имя файла» позволяет получить запись — его содержимое. То есть можно реализовать свой механизм хранения данных для MySQL, в котором каждая запись будет иметь три поля:

CREATE TABLE `data_storage`.`files` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`path` VARCHAR( 255 ) ,
`data` BLOB
) ENGINE = FILES


Вставлять данные в такую таблицу можно только в поле `data`, при этом они просто сохраняются в файл, уникальное имя ему при этом генерируется автоматически (используя в качестве префикса поле `id`) — например 764533, а в поле `path` автоматически подставляется правильный путь, по которому MySQL положил наши данные — например '/mnt/storage/mysqldata/76/45/33/764533_myfile.jpg'. Таким образом к данным, сохраненным в такой таблице можно обращаться как к простым файлам, и при этом MySQL будет поддерживать целостность данных. Таким образом этот способ хранения файлов лишен практически всех недостатков классического подхода (кроме ограничения доступа, но и его можно сделать используя простой скрипт и заголовок X-Accel-Redirect nginx) и в то же время никак не уменьшает производительность при отдаче файлов клиентам.
Проблема за малым — не удалось найти готовой реализации такого движка хранения данных для MySQL, хотя идея общем то простая. Возможно кто-то из хабролюдей подскажет ссылку на готовую реализацию такого storage engine, идея ведь плавает на поверхности, и ее точно кто-то уже мог реализовать.

Статья написана в соавторстве с viperet

P.S. перенес в блог MySQL