В самом начале своей программистской деятельности довольно часто пытался найти для себя ответ на весьма популярный вопрос. Хранить ли файлы в БД или же не стоит и почему, а если хранить то как? Не вдаваясь в подробности тернистого пути, скажу, что для себя я однозначно решил, что в MySQL(и ему подобных) этого лучше не делать. Как быть если программа-клиент подключается к удаленному серверу с MySQL, как тогда избежать хранения файлов в таблицах?
Однажды мне пришлось задаться этим вопросом, когда заказчик выкатил дополнительные требования к программе в виде незадачливого – «Ах, да! Нужно еще и файлы прикреплять». Поиск ничего интересного мне не выдал, разве что поведал о мифическом типе таблиц FileDB (для MySQL).А, ну еще там было очень много про архитектуру приложения из трех составляющих так называемую трехзвенку. Данная работа находилась тогда уже ближе к завершающему этапу и по этому, включать в архитектуру третье звено, было равносильно начинанию работы с начала. Честно говоря, абсолютно не рассчитывая на положительный результат стал пробовать решить задачу средствами самой СУБД. И так по порядку.
1) Как сохранить файл через MySQL?
Здесь приходит нам на помощь функция DUMPFILE. Попросту больше ничего из функций MySQL на диск не пишет. Данная функция, в первую очередь, предназначена для записи результатов запросов в файл. А что если мы подставим уже готовый результат в виде содержимого из нашего файла?
SELECT ‘наш_контент’ INTO DUMPFILE ‘filedb/my_file’И да это работает! Если путь указан относительный MySQL запишет файл относительно своего каталога «data», так же функция поддерживает указание полного пути к файлу. Вот только одно но, MySQL не может самостоятельно создавать каталоги, но об этом ниже.
2) Как сохранить связь?
Через MySQL мы не сможем получить ни размер, ни дату создания файла. Нужна вспомогательная таблица где мы будем хранить эти данные, а так же присвоим каждому файлу свой идентификатор. Я добавил таблицу ‘vfile’ и в нее такие поля: id, nm-оригинальное имя файла, len-размер,dt-дата добавления, del-метка на удаление. Зачем нужна метка на удаление, спросите Вы? Ну конечно же MySQL не умеет самостоятельно удалять файлы. Чуть позже мы с этим разберемся. Теперь для сохранения файла мы предварительно создаем запись в нашей таблице, после чего берем сгенерированный ID и подставляем его в наш запрос с DUMPFILE.
3) Организуем файлы.
Записывать все файлы в один каталог, по моему мнению, не желательно из-за ограничений файловой системы. Ограничения эти кажутся недостижимыми, но поверьте, моему опыту это только кажется и лучше быть предусмотрительным, чем воткнуться рогом в землю. По этому добавляя файл, необходимо ID последней записи из таблицы преобразовать к виду ‘filedb/1/2/3/f’ такой путь к файлу будет для записи с ID 123. Так же нам потребуется заранее подготовить каталоги и как я уже сказал, MySQL не может самостоятельно их создать. Доверим это скрипту:
#!/usr/bin/php
<?
include("/var/www/html/cor/cfg.php");
include("/var/www/html/cor/sql.php");
include("/var/www/html/cor/suit.php");
$mysqpath='/var/lib/mysql/';
$res=$mysqli->query('SELECT max(id) as mid FROM vfile');
if($row = mysqli_fetch_assoc($res))
for($i=$row['mid'];$i<=$row['mid']+1000;$i++){
$fpath=substr(GetFilePath($i), 0, -1);
if(!file_exists($mysqpath.$fpath)) mkdir($mysqpath.$fpath, 0777, true);
}
?>
И под покровом ночи наш cron будет запускать этот скрипт обеспечивая 1000 пустых каталогов на день. Здесь же мы видим функцию преобразования ID в путь к файлу GetFilePath(‘ID’). Код самой функции думаю приводить смысла нет, он достаточно прост.
4) Читаем файл.
В MySQL есть редко используемая функция LOAD _ FILE (file_name) позволяющая получить содержимое файла в наш запрос. Так же зная ID файла преобразуем его в путь и получаем содержимое запросом SELECT LOAD _FILE(file_name). Для удобства добавим пару пользовательских функций:
DELIMITER $$
CREATE FUNCTION `get_file_path`(fid INT(11)) RETURNS varchar(255) CHARSET utf8
BEGIN
DECLARE path,pathid VARCHAR(22);
DECLARE x,len INT(4);
SET path='filedb/';
SET x=1;
SET pathid=fid+'';
SET len=LENGTH(pathid);
WHILE x<=len DO
SET path=CONCAT(path,SUBSTRING(pathid,x,1),'/');
SET x=x+1;
END WHILE;
RETURN concat(schema(),'/',path,'f');
END$$
DELIMITER ;
DELIMITER $$
CREATE FUNCTION `get_file_cont`(fid INT(11)) RETURNS longblob
BEGIN
RETURN load_file(get_file_path(fid));
END$$
DELIMITER ;
Теперь можем сразу получать содержимое файла по его ID, например:
SELECT get_file_cont(123)5) Удаляем файлы.
У нас уже есть скрипт обслуживающий нашу структуру каталогов, просто добавим в него необходимый код.
//Пометка сирот на удаление
$mysqli->query('UPDATE vfile v SET del=1 WHERE
NOT EXISTS(SELECT * FROM event_rep WHERE v.id=f) and
NOT EXISTS(SELECT * FROM doc WHERE v.id=f) and
NOT EXISTS(SELECT * FROM docs WHERE v.id=f) and
NOT EXISTS(SELECT * FROM foto WHERE v.id=f) and
NOT EXISTS(SELECT * FROM pozdrav WHERE v.id=foto) and
NOT EXISTS(SELECT * FROM maket WHERE v.id=pdf or v.id=jpg or v.id=dop)');
//Удаление отмеченых файлов
$res=$mysqli->query('SELECT id FROM vfile WHERE del>0');
while($row = mysqli_fetch_assoc($res)) unlink($mysqpath.GetFilePath($row['id']));
$mysqli->query('DELETE FROM vfile WHERE del>0');
Т.е. чтобы удалить файл нам достаточно выставить атрибут del равным 1. Хоть удаление происходит не сразу, но и этого, на мой взгляд, вполне достаточно. Тут же, я привел пример как я удаляю «сирот» у которых нет записей в связанных таблицах. Как вариант при ручном удалении можно на время до запуска скрипта записать в файл пустое содержание уменьшив занимаемое им место.
Подводные камни
Их оказалось не много. Здесь нужно упомянуть о необходимых привилегиях пользователям СУБД. За доступ к вышеупомянутым функциям отвечает привилегия «FILE». Те кому приходилось работать с бэкапами MySQL, конечно же, наверняка узнали о директиве max_allowed_packet которая и определит максимальный размер файла который мы сможем прогнать через СУБД и положить на сервер. Изначально планировалось завернуть DUMPFILE в пользовательскую функцию, как это было сделано с LOAD_FILE(), но дампфаел наотрез отказался принимать путь, переданный в виде переменной, говорит: «Подавай мне только в кавычках сразу как есть, ни о каких переменных в качестве параметра я не слышал». «Ну и ладно!»- подумал я.
Комментарии (44)
AlexLeonov
22.08.2017 14:58+4Хранить ли файлы в БД
Ответ: нет, файлы следует хранить в файловой системе.
На этом статью можно было бы заканчивать.
$res=$mysqli->query('SELECT id FROM vfile WHERE del>0');
Пожалуйста, если вы решите оставить статью в публичном доступе, перепишите на PDO. Не подавайте начинающим дурной пример.
И, если можно, проверьте соответствие вашего кода стандартам оформления PSR. Есть места, где код слегка не соответствует.
Спасибо.
Ni_san
22.08.2017 15:56-1"Ответ: нет, файлы следует хранить в файловой системе." — такой ответ внушает сомнения. Например, автор книги "SQL Antipatterns: Avoiding the Pitfalls of Database Programming" Билл Карвин в 12 главе говорит об обратном: все достаточно важные файлы зачастую следует хранить в базе данных, а не в файловой системе. На чем основывается ваша точка зрения?
procm Автор
22.08.2017 16:10Иногда теория расходится с практикой. И это на мой взгляд и порождает разногласия среди населения. Более того, я ЗА подход предложенный автором книги и многие думаю ЗА. Было бы идеально если Oracle допилит движок таблиц FileDB. А пока что, могу предложить попробовать ради интереса создать файл 1Мб и несколько раз его записать в базу, Вы увидите как не пропорционально количеству записей убывает место на диске. Когда файлы большие и их много, создание дампа БД и дальнейшее его использование становится несколько затруднительным. Гораздо проще эти файлы архивировать или копировать отдельно от остальных данных.
AlexLeonov
22.08.2017 16:50+2У меня нет точки зрения или мнения. Не могу себе позволить такую роскошь. Есть лишь точное знание по этому вопросу.
Файлы нужно хранить в файловой системе по множеству причин, каждой из которых достаточно, чтобы закрыть вопрос раз и навсегда:
- Файлы в ФС имеют метаданные — владелец, права доступа, mtime, ctime, atime.
Всё это либо затруднительно, либо невозможно организовать в БД - ФС «из коробки» имеет иерархию директорий
- Файлы из ФС отдаются на порядок быстрее, чем из БД
- ФС имеет множество приятных «плюшек» — монтирование по разным протоколам, шифрование, журналирование, которые вы устанете реализовывать в БД
- И я уж не говорю о том, что веб-сервер работает напрямую с ФС, а не с БД
- Популярные системы контроля версий работают с файловой системой, а не с базами данных
- ...
Я мог бы продолжать и дальше. Но достаточно вспомнить незабвенного Фейнмана: «эксперименты новичков следует проводить в специальной лаборатории для новичков» :)procm Автор
22.08.2017 17:02Да спасибо, забыл про пункт 3 упомянуть. Все остальное зачастую не является существенно важным в связке с БД.
AlexLeonov
22.08.2017 17:16Что тогда вообще для вас существенно важное в задаче хранения файлов и доступа к ним, позвольте спросить?
procm Автор
22.08.2017 18:24Когда мы начинаем связывать файлы БД. У нас как правило теряется необходимость в плюшках ФС. Так как права мы уже скорее всего будем ограничивать нашим приложением, нам все равно в каком каталоге файл лежит т.к. «иерархию» уже обеспечивает БД, ну честно, сложно представить задачу с журналированием или контролем версий где файлы еще и надо с БД связать, в последнем случае масло-масленное. Мы же не собираемся хранить свои личные файлы и плоды своего творчества в таблицах СУБД? По этому я бы для себя предпочел не делать выбор в пользу красивого трактора. Все что нужно клиенту БД это содержание файла +- какая то инфа по ситуации
AlexLeonov
22.08.2017 20:00Просто ответьте на вопрос: «Как расшифровывается аббревиатура URL?»
Если всему миру хватает этого самого Locator и он действительно Uniform, а вам — нет, не хватает, может быть у вас проблемы с архитектурой?
pansa
23.08.2017 00:29-2> Файлы в ФС имеют метаданные — владелец, права доступа, mtime, ctime, atime.
> Всё это либо затруднительно, либо невозможно организовать в БД
Нет, это как раз элементарно организуется в БД. Причем, в совершенно любых позах.
И вот не надо про права. В unix-ах система прав — не вершина элегантности, скажем честно. Набор хитрых костылей в виде ACL, построенных на сомнительном фундаменте extended attributes… Но, увы, другого особо не дано.
> ФС «из коробки» имеет иерархию директорий
Далеко не во всех задачах требуется. И там, где она не требуется — зачастую только мешает. Но поскольку она «из коробки» — приходится извращаться, да.
> Файлы из ФС отдаются на порядок быстрее, чем из БД
В общем случае — совсем нет.
> ФС имеет множество приятных «плюшек» — монтирование по разным протоколам, шифрование, журналирование, которые вы устанете реализовывать в БД
Монтирование по разным протоколом — не совсем понятно, почему это должно быть приятно. Далеко не везде.
Шифрование — да ладно, какая ФС умеет штатно шифровать, кроме специализированных? А надстроки — так и в БД не проблема шифровать записи.
Журналирование — общий термин. В БД тоже есть журналирование.
> И я уж не говорю о том, что веб-сервер работает напрямую с ФС, а не с БД
Опять же, смотря какой вебсервер. Смотря что надо.
> Популярные системы контроля версий работают с файловой системой, а не с базами данных
Очень странный аргумент. При чем здесь vcs? Ну и посмотрите под капот CVS/SVN/git — всё равно в служебных каталогах в рабочих копиях навелосипежены аналоги БД.
Мне не нравится решение автора статьи, но ваше утверждение слишком категорично. Всё очень сильно зависит от задачи. «Убить» любую ФС, в общем, не большая проблема.
- Файлы в ФС имеют метаданные — владелец, права доступа, mtime, ctime, atime.
ilyaplot
22.08.2017 15:13+4cor/suit
У меня один глаз лопнул от такого.
В самом начале своей программистской деятельности
— вы все еще там
включать в архитектуру третье звено, было равносильно начинанию работы с начала
С такой «архитектурой» далеко не уедешь.
К сожалению, ваш пост не принес абсолютно никакой пользы.procm Автор
22.08.2017 16:29очень жаль за ваше потраченное время и за глаз ))
ilyaplot
22.08.2017 16:57+1Плохой опыт — тоже опыт.
Вообще я всегда использую хэш или UUID для хранения файлов.
Для предотвращения накопления критического множества файлов в одной директории, я использую подпапки. Допустим, md5 хэш файла = 1BC29B36F623BA82AAF6724FD3B16718.
Путь к файлу будет таким: /path/to/storage/1/B/1BC29B36F623BA82AAF6724FD3B16718, Где 1 — первый символ хэша, B — второй.
Таким образом удобно и контролировать целостность файлов, сверяя md5 хэш с именем. Для этого даже существуют готовые функции в PHP.
P.S. добро пожаловать на хабр.pansa
23.08.2017 00:00True. Хотя мы давно используем sha1, а будем переходить на sha256. Ну и по две буквы в имени каталога.
Еще слышал мнение весьма опытного разработчика, что лучше для адресации использовать последние буквы из хэша, т.е в приведенном выше примере файл должен лежать в
/8/1/1BC29B36F623BA82AAF6724FD3B16718
Таким образом в одном каталоге имена файлов не будут начинаться на одинаковую последовательность символов, поэтому дерево индекса будет сбалансировано и поиск по нему будет быстрее.
Правда, практические тесты не выявили сколько-нибудь заметной разницы. Но тут еще целый пласт интересностей вроде dentry cache'ей и пр, поэтому всё неоднозначно.
NickyX3
24.08.2017 08:11Для нескольких миллионов файлов с соответствием id(int) -> md5(id) -> разделение по двум символам из начала хеша и двумя уровнями папок (1BC29B36F623BA82AAF6724FD3B16718 = /1B/C2/1BC29B36F623BA82AAF6724FD3B16718 ) выявлено практически абсолютно равномерное распределение, разница в десяток файлов в папке.
pansa
24.08.2017 11:06Любой вменяемый хэш даст нормальную равномерность, sha выбраны с т.з. минимизации коллизий — в т.ч. намерянных, тк мы связаны с безопасностью.
yar3333
22.08.2017 15:50+1Очень странный подход… Зачем вообще гонять файлы через MySQL, если они всё равно лежат на диске? Не проще ли банально читать их напрямую ф-ией PHP? Да и записывать — также, а перед записью создавать нужный каталог для файла, если этого каталога ещё нет…
procm Автор
22.08.2017 15:55Прога-клиент и СУБД на разных хостах
AlexLeonov
22.08.2017 16:50+1Откройте для себя распределенные ФС.
procm Автор
23.08.2017 05:45-5Это хорошо что Вы знаете множество технологий и обладаете умением их применить. Постарайтесь их ВСЕ применять в каждом из своих проектов, а то ненароком люди подумают что вы о них не слышали ))
michael_vostrikov
22.08.2017 17:22У вас все пользователи коннектятся напрямую в БД и вы каждому раздаете права доступа к таблицам?
procm Автор
22.08.2017 17:36да так и есть, у разных пользователей различные права. но к чему вопрос? что-то в этом есть опасного о чем я не догадываюсь?
michael_vostrikov
22.08.2017 17:49А как решаете вопрос с доступом на уровне строк — например, доступ пользователя к файлам, которые он не загружал? Вдруг он скачает MySQL-клиент и захочет базу подредактировать?
procm Автор
22.08.2017 18:37Ну собственно для этого есть прога-клиент ничего качать не придется. Но вопрос довольно интересный было бы не плохо это реализовать
michael_vostrikov
22.08.2017 20:13Не, в проге-клиенте все ограничено интерфейсом. Если нет кнопки «Удалить», то и удалить нельзя. А если я скачаю графический клиент к БД и зайду под своим логином, который вы мне выдали, то у меня будет полный доступ ко всем таблицам, где я могу что-то менять через прогу-клиент.
VolCh
27.08.2017 00:11Как миниму, так гораздо проще обеспечивать целостность, по крайней мере если СУБД полноценно поддерживает файловые хранилища (с мускулем тут может быть плохо), а так все прелести распределенных транзакций. Во-вторых, все данные приложения можно получать одним способом.
x893
22.08.2017 16:01Такую же фигню делал, но на ASP.NET + MS SQL.
Вообще никаких проблем.
За 4 года уже 170 000 файлов болтается.
По 100 файлов в каталоге. Права и атрибуты в базе. Файлы напрямую в IIS отдаются (с нужным content-type). Наверное с MySQL сложно, но с IIS+ASP.NET+MSSQL день делал.
grossws
22.08.2017 21:39За 4 года уже 170 000 файлов болтается.
Это кагбэ не тот объём на котором есть вообще какие-либо серьёзные проблемы. Если у вас счёт на десятки-сотни миллионов файлов и единицы-десятки терабайт на хост, тогда проблемы ощутимы.
procm Автор
23.08.2017 05:36IIS и MSSQL на одном сервере? По моему это немного не то. Тут я описываю схему как не хранить в базе файл и при этом использовать СУБД как посредника между клиентом и файловой системой.
x893
23.08.2017 10:33Да как угодно можно сделать.
100 x IIS и поменять 100xMSSQL (или Oracle RACов).
И файловые сервера на SSD.
И F5 на баласировку.
Главное, что бы бабки остались на пиво.
procm Автор
23.08.2017 10:54Ну я к тому, что если клиент имеет и так доступ к целевой ФС, то смысл моего подхода сходит на нет. По этому уточнил.
vtvz_ru
22.08.2017 23:11Вообще не хватает возможности нормально хранить файлы в БД. ФС это хорошо. Но транзакции, fk с каскадным удалением и п.р. плюшек очень не хватает. К тому же актуальность данных приходится проверять, чтобы ссылка на файл была живая или файл ещё числился в БД. А проблемы частых запросов в бд можно было бы решить через кеш.
michael_vostrikov
22.08.2017 23:58Для fk и транзакций надо делать табличку file, где хранить инфу по всем загруженным файлам. А контент файла хранить на диске с доступом через скрипт для проверки прав. Удаление удаленных в БД файлов с диска через сравнение списков путей в таблице и в корневой папке.
vtvz_ru
23.08.2017 09:16Так и делается обычно. Но это все вручную, по большому счету. Да и, говоря о целостности данных, если запись в таблице была удалена, а файлик почему-то остался на диске. Что с этим делать? Вот и приходится шаманить с cron и сравнением путей. А если удаляется запись, допустим, поста в блоге, а вместе с ним и все прикрепления? Я не могу рассчитывать на базу, что она вместе со сроками в БД удалит и файлы. А всю эту логику приходится писать руками. Не говорю, что выхода нет. Я говорю об удобстве
michael_vostrikov
23.08.2017 13:11Ну это если только для маленьких проектов, где место ограничено. На больших и крон есть, и места больше, на очень больших говорят даже не удаляют, чтобы фрагментацию не создавать.
NickyX3
24.08.2017 08:14А если удаляется запись, допустим, поста в блоге, а вместе с ним и все прикрепления?
Ну это то не проблема, десяток файлов зачистить можно даже онлайн, без всяких кронов.
В моем случае — я проверяю после удаления файлов из конкретной папки еще и наличие других файлов в ней, и если она пустая — удаляю саму папку :-)
michael_vostrikov
22.08.2017 23:51Если у вас есть PHP на сервере, можно было бы просто сделать веб-страничку и загружать файлы через нее. Мне кажется, отправлять HTTP-запрос вручную через сокет было бы гораздо проще. Есть вопрос с авторизацией, но это тоже решаемо.
procm Автор
23.08.2017 05:27Более того и Apache там есть. Но зачем использовать в проекте звенья без которых можно обойтись? Например, если нам потребуется обмен сообщениями, как вариант можно поставить Jaber сервер или использовать сторонний SMTP. Но зачем?
michael_vostrikov
23.08.2017 13:02Затем, что обойтись без них сложнее? Вы вон целую статью написали про это, с кроном и хранимыми процедурами. А отдельная страничка загружает по запросу, удаляет по запросу, читает по запросу, да еще и права доступа проверяет. Тем более что это звено и так есть, просто используется по-другому.
VolCh
27.08.2017 00:13Хранить ли файлы в БД или же не стоит и почему, а если хранить то как?
Плохая формулировка вопроса, повлекшая, видимо, минусы посту. Лучше "хранить ли большие бинарные данные в базе, в файлах или в какой-то их комбинации, в том числе в файлах но с доступом через СУБД?"
oxidmod
1. А если заатачат больше 1к файлов? Почему нельзя вставить метаинфу в vfile и по сгенерированному айдишнику генерировать каталог?
2. Другой путь. почему не создавать по каталогу на день? Уж 1к файлов в одном каталоге проблем для ФС не составят.
3. Как вы контролируете доступ к удаленным файлам?
procm Автор
1. Число взято с огромным запасом. Вторую часть вопроса не совсем кажется понял но попробую ответить, у меня на самом деле есть еще поле описания к файлу я его опустил т.к. естественно набор полей может быть любым, в том числе и метаинфу можно добавить, в моем случае это не было необходимостью.
2. Да почему бы и нет, но мне не кажется мой подход более простым и наглядным. Как вариант при получении содержимого можно взять день из той же даты добавления.
3. Программа-клиент имеет полный доступ к файлам как и к самой таблице тут сильно не по контролируешь но подумать можно…