Почему не стоит хранить файлы в бд
Как лучше хранить файлы в sql базе данных? Хранить сами файлы(картинки, текстовые файлы, аудио файлы) или хранить в базе сслыку на эти файлы в системе? Каким способом лучше реализовать тот или иной способ?
Зависит от конкретной СУБД. Например, в Sql Server есть и третий способ: FILESTREAM - сочетает в себе преимущества обоих.
1 ответ 1
В SqlServer вы можете использовать следующие варианты (некоторые из них применимы и к другим СУБД).
Вариант 1
В БД хранится "заголовок" файла (например, путь к файлу плюс, возможно, какой-то набор атрибутов):
а данные хранятся отдельно в файловой системе. Размер БД меньше, чем если хранить в БД также и данные. Но нужно следить за ситуациями "файл есть, заголовка нет" или "заголовок есть, файла нет". На мой взгляд, если файлы являются логически важной частью данных БД (не кэш, не какие-то временные данные), то лучше посмотреть на другие варианты.
Вариант 2
В БД хранится также и содержимое файла (в столбце типа varbinary(max) ).
Здесь две опции - с FILESTREAM и без.
- данные хранятся в БД (в т.н. LOB pages)
- размер данных одного элемента ограничен 2Gb
- данные хранятся в файловой системе (именно как файлы)
- нет ограничения в 2Gb на элемент
- данные FILESTREAM не участвуют при подсчёте лимита на макс. размер БД (к чему чувствительны Express Edition)
- к данным можно получить доступ через соотв. API со стороны файловой системы
- (SqlServer 2014 и далее) запрашиваемые данные не отъедают из buffer pool, оставляя больше памяти для обработки запросов
И с FILESTREAM и без поддерживаются транзакции. С FILESTREAM при доступе через Transact-SQL поддержка полная, при доступе через файловую систему есть ограничения (смотреть здесь).
Вариант 3
Использование таблиц специального типа FileTable.
Их функционал основан на использовании FILESTREAM . Таблица представляет иерархию хранящихся файлов/директорий, их данные и атрибуты. В варианте 2, чтобы создать/удалить файл, нужно создать/удалить соотв. запись в таблице. В данном варианте это можно делать напрямую через файловую систему. Например зайти в соответствующую директорию (SqlServer создаёт для этого соответствующую UNC share), создать какой-то файл/директорию, удалить/изменить, потом сделать запрос select * from FileTableName и увидеть соответствующие изменения. И наоборот - при вставке записи в таблицу через SQL в директории появится соответствующий файл или директория.
Какой вариант когда лучше использовать - думаю, зависит от конкретной задачи. В документации более детальное описание и сравнение вариантов 2 и 3.
4 ответа 4
Не касаясь вопроса а_на_фига_это_вообще_надо отвечу на прямой вопрос:
Какие существуют способы хранения файлов в sql базах данных?
За все способы не скажу, но я лично использовал такой способ:
- Заголовочная таблица с метаданными файла, поля типа:
- Идентификатор файла
- Название файла
- mime тип файла
- размер файла
- timestamp'ы lastmodified/created
- checksum файла
- список тегов
- Ссылка 1 ко многим на таблицу с контентом файла с полями
- Первичный ключ
- Идентификатор файла
- порядковый номер куска/chunk'а
- BLOB поле
Обращаю внимание, что поле BLOB является стандартным типом поддерживаемым практически любой SQL СУБД.
Работает это так:
- Берем файл
- Определяем его метаданные и пишем в заголовочную таблицу
- Открываем файл делим его на куски и куски пишем в список BLOB полей
P.S. Для любителей говорить о том, что типа страдает скорость приведу маленькую справочку - файл в файловой системе любой ОС организован как БД. То есть заголовочек и есть списочек контента файла на которые хранятся ссылки
Да если данные нужны в базе данных то храните в блоб. Если нет то чем плох вариант не хранить их вовсе?
Давай попробую ответить.
Смысл хранить данные в базе имеется только если с ними работать на прямую.
Например: у вас есть модуль/функция который который может найти, определить, сгруппировать файлы по содержимому по типу плагият или нет. Или какой нибудь по типу google картинок который находит схожие изображения. Короче говоря вы будите использовать содержимое.
Если нет то есть смысл хранить только ссылки, поскольку разрастание базы не ведёт к улучшению производительности. Сохранение и считывание таких данных будет отнимать всегда лишнее время, когда как ссылки могут спокойно существовать и без самих файлов, при обращении к которым будет вежливо указывается идти лесом.
Споры по данной теме мне приходилось наблюдать на нескольких форумах, порой даже по нескольку раз. В основном эти темы касались хранения изображений, реже текстовых файлов. Сам я отношусь к противникам данного метода и в статье попытаюсь привести обоснованные доказательства того, что хранить файлы в БД неудобно и негативно влияет на скорость работы системы в целом. Поскольку в основном я работаю с MySQL, то и рассматривать данный вопрос буду с точки зрения хранения файлов в его базах при разработке под WEB.
1. ОЗУ. Самым главным аргументом против, на мой взгляд, является тот факт, что даже когда файл нужно просто отдать пользователю (например отобразить изображение) его все равно придется загружать в ОЗУ, т.к. все данные выбранные запросом из БД загружаются в оперативную память. На момент написания данной статьи самым дорогим, после процессора, на выделенных серверах является ОЗУ. При этом я уже не говорю про обычный хостинг где в подавляющем большинстве случаев под каждый аккаунт выделяется определенное количество оперативной памяти, превышение которой приводит к «Fatal error: Allowed memory size of X bytes exhausted (tried to allocate Y bytes)» либо к гневным письмам из саппорта.
2. Отдача файлов. Если файлы хранятся в БД, то для их отдачи в любом случае придется использовать скрипт написанный на том или ином языке, который должен сделать следующее:
2.1 Открыть новое соединение с БД, количество которых далеко не бесконечно, либо занять уже существующее но свободное соединение, что плохо по той же причине;
2.2 Запросом выбрать содержимое файла из таблицы. Тут возникает сразу 2-е проблемы:
2.2.1 Загрузка содержимого файла в ОЗУ (см. п. 1);
2.2.2 По какому параметру искать файл в БД. Логично предположить что самым быстрым будет поиск по целочисленному ID, но в таком случае в ссылке на скрипт выдачи файла так же нужно будет использовать этот ID (например: ), что в случае формирования таких ссылок вручную затруднит работу.
2.3 Отдать необходимые хидеры и содержимое файла.
И все эти шаги будут проделываться для каждого файла, а если их на странице выводиться штук 20, причем загрузка идет одновременно, то есть риск не увидеть ни одного.
Так же такой способ хранения файлов практически лишает вас следующих возможностей:
— распределить файлы по нескольким серверам и для скачки давать ссылки непосредственно на эти сервера;
— установить reverse proxy server для ускорения «медленных клиентов»
— использовать CDN.
3. Дампы. Одним из аргументов «за» часто приводят следующее: «Если мне понадобится перенести сайт на другой хостинг, то мне нужно только сделать дамп и скопировать код». Лично я не считаю этот аргумент весомым по следующим причинам:
3.1 Давайте наконец начнем писать сайты так, чтобы они быстро работали, а не только чтобы они легко переносились;
3.2 Хранение любых, а особенно бинарных файлов в БД приводит к следующему:
3.2.1 т.к. увеличивается БД то соответственно увеличивается время для создания ее дампа и его размер;
3.2.2 наличие в дампе содержимого бинарного файла очень ухудшает его читабельность, а так ж, что немаловажно, далеко не все консольные редакторы могут открыть большой дам для редактирования, например MCEDIT не может;
3.2.3 с большой вероятностью возникнут проблемы с заливкой такаих дампов:
— во-первых, это будет достаточно долгий процесс;
— во-вторых, это может оказаться достаточно сложно, особенно если сайт располагается на хостинге, который не позволяет конектиться к БД извне и не дает доступа к серверу по ssh для того, чтобы можно было воспользоваться тулзой «mysql». В этом случае Вам придется использовать скрипты вроде phpMyAdmin (хотя по своему опыту работы с ним могу с почти 100% уверенностью сказать, что через него залить дамп больших размеров 50 100 мб задача почти нереальная) или Sypex Dumper.
4. Разграничение доступа к файлам. Еще одним аргументом «за» во многих дискуссиях выступает то, что при хранении данных в БД легче организовать разграничение доступа к файлов для разных пользователей сайта. С этим аргументом я косвенно согласен, т.к. это действительно проще, но во-первых, это не перевешивает первые три пункта, а во-вторых для разграничения доступа можно использовать способы, которые я описал в этой статье.
5. Централизованное хранение. Этот пункт так же пытаются записать в актив хранения файлов в БД, обосновывая тем, что если серверов, которые работают с одними и теми же файлами много, то намного удобнее хранить их в одном месте. На мой взгляд это так же не является аргументом, т.к. даже если файлы физически будут храниться на разных серверах, то ничто не мешает их примаунтить на все сервера где они должны использоваться, при чем создав одинаковую структуру каталогов на всех серверах.
Вот вроде и все, но хочу еще раз напомнить, что я рассматривал данный вопрос со стороны программирования под web, а именно для MySQL. При этом я осознаю, что для других областей программирования и других СУБД, таких как MSSQL и Oracle все или часть моих доводов могут оказаться неверны.
Споры по данной теме мне приходилось наблюдать на нескольких форумах, порой даже по нескольку раз. В основном эти темы касались хранения изображений, реже текстовых файлов. Сам я отношусь к противникам данного метода и в статье попытаюсь привести обоснованные доказательства того, что хранить файлы в БД неудобно и негативно влияет на скорость работы системы в целом. Поскольку в основном я работаю с MySQL, то и рассматривать данный вопрос буду с точки зрения хранения файлов в его базах при разработке под WEB.
1. ОЗУ. Самым главным аргументом против, на мой взгляд, является тот факт, что даже когда файл нужно просто отдать пользователю (например отобразить изображение) его все равно придется загружать в ОЗУ, т.к. все данные выбранные запросом из БД загружаются в оперативную память. На момент написания данной статьи самым дорогим, после процессора, на выделенных серверах является ОЗУ. При этом я уже не говорю про обычный хостинг где в подавляющем большинстве случаев под каждый аккаунт выделяется определенное количество оперативной памяти, превышение которой приводит к «Fatal error: Allowed memory size of X bytes exhausted (tried to allocate Y bytes)» либо к гневным письмам из саппорта.
2. Отдача файлов. Если файлы хранятся в БД, то для их отдачи в любом случае придется использовать скрипт написанный на том или ином языке, который должен сделать следующее:
2.1 Открыть новое соединение с БД, количество которых далеко не бесконечно, либо занять уже существующее но свободное соединение, что плохо по той же причине;
2.2 Запросом выбрать содержимое файла из таблицы. Тут возникает сразу 2-е проблемы:
2.2.1 Загрузка содержимого файла в ОЗУ (см. п. 1);
2.2.2 По какому параметру искать файл в БД. Логично предположить что самым быстрым будет поиск по целочисленному ID, но в таком случае в ссылке на скрипт выдачи файла так же нужно будет использовать этот ID (например: ), что в случае формирования таких ссылок вручную затруднит работу.
2.3 Отдать необходимые хидеры и содержимое файла.
И все эти шаги будут проделываться для каждого файла, а если их на странице выводиться штук 20, причем загрузка идет одновременно, то есть риск не увидеть ни одного.
Так же такой способ хранения файлов практически лишает вас следующих возможностей:
— распределить файлы по нескольким серверам и для скачки давать ссылки непосредственно на эти сервера;
— установить reverse proxy server для ускорения «медленных клиентов»
— использовать CDN.
3. Дампы. Одним из аргументов «за» часто приводят следующее: «Если мне понадобится перенести сайт на другой хостинг, то мне нужно только сделать дамп и скопировать код». Лично я не считаю этот аргумент весомым по следующим причинам:
3.1 Давайте наконец начнем писать сайты так, чтобы они быстро работали, а не только чтобы они легко переносились;
3.2 Хранение любых, а особенно бинарных файлов в БД приводит к следующему:
3.2.1 т.к. увеличивается БД то соответственно увеличивается время для создания ее дампа и его размер;
3.2.2 наличие в дампе содержимого бинарного файла очень ухудшает его читабельность, а так ж, что немаловажно, далеко не все консольные редакторы могут открыть большой дам для редактирования, например MCEDIT не может;
3.2.3 с большой вероятностью возникнут проблемы с заливкой такаих дампов:
— во-первых, это будет достаточно долгий процесс;
— во-вторых, это может оказаться достаточно сложно, особенно если сайт располагается на хостинге, который не позволяет конектиться к БД извне и не дает доступа к серверу по ssh для того, чтобы можно было воспользоваться тулзой «mysql». В этом случае Вам придется использовать скрипты вроде phpMyAdmin (хотя по своему опыту работы с ним могу с почти 100% уверенностью сказать, что через него залить дамп больших размеров 50 100 мб задача почти нереальная) или Sypex Dumper.
4. Разграничение доступа к файлам. Еще одним аргументом «за» во многих дискуссиях выступает то, что при хранении данных в БД легче организовать разграничение доступа к файлов для разных пользователей сайта. С этим аргументом я косвенно согласен, т.к. это действительно проще, но во-первых, это не перевешивает первые три пункта, а во-вторых для разграничения доступа можно использовать способы, которые я описал в этой статье.
5. Централизованное хранение. Этот пункт так же пытаются записать в актив хранения файлов в БД, обосновывая тем, что если серверов, которые работают с одними и теми же файлами много, то намного удобнее хранить их в одном месте. На мой взгляд это так же не является аргументом, т.к. даже если файлы физически будут храниться на разных серверах, то ничто не мешает их примаунтить на все сервера где они должны использоваться, при чем создав одинаковую структуру каталогов на всех серверах.
Вот вроде и все, но хочу еще раз напомнить, что я рассматривал данный вопрос со стороны программирования под web, а именно для MySQL. При этом я осознаю, что для других областей программирования и других СУБД, таких как MSSQL и Oracle все или часть моих доводов могут оказаться неверны.
Делаю сервис, будет храниться несколько версий файлов, плюс, таких изначальных файлов предполагается что будет немало. На данный момент я рассчитываю ориентировочно на пару тысяч файлов (в итоге), но в перспективе количество файлов может быть и больше.
— где лучше хранить все эти файлы — в виде Blob/text в MySql или же в виде отдельных файлов на диске? Сами файлы напрямую клиентам отдаваться не будут — сначала они обрабатываются специальным php скриптом и только потом результат обработки отдается. Для каждого клиента результат может быть разным (а может быть и таким же)
— как лучше все это потом бэкапить? Есть подозрение что все эти тысячи файлов бэкапить будет тяжко, если не хранить их в базе данных…
На самом деле оба варианта по реализации настолько минимально отличаются, что хорошим советом будет «сделайте сейчас оба варианта, используйте сначала файлы, а когда файлов будет много померяете производительность».
В 99% случаев файлы лучше, т.к. к ним есть прямой и очевидный доступ без всяких баз, а база в любом случае прослойка.
По бакапам ситуация двоякая, с одной стороны файлы удобнее бакапить тем же инкриментом и обычными файловыми средствами, с другой стороны разовый бакап базы сделать можно сделать просто скопировав файл с таблицей и не надо собирать кучу файлов.
По скорости/нагрузке, безусловно если это один сервер, то файлы будут побыстрее (только бейте на папки, не пихайте больше 1000 в одну в любом случае), но если у Вас несколько серверов, то отдельный сервак с базой под файлы может иметь определённые преимущества, доступ к базе по сети чуть более очевиден (хотя если у Вас есть админ, то не принципиально).
Файлы при прочих равных однозначно лучше попадают в кэш, с другой стороны засирание кэша базой проще контроллировать.
Не совсем. На БД проще сделать HA и репликацию — абстракция от файлов же. Если делать на файлах то надо думать как сделать их доступными (если надо) и реплицию. Однако, если принять во внимание, что ФС это тоже БД то различия почти минимальны.
Может автору лучше вообще сам себе Amazon S3 сделать? или Luwak + Riak или CouchDB или Riak CS.
Единственное преимущество хранения файлов в БД, на которое нужно обратить внимание — это конкурентный доступ. То есть СУБД корректно обрабатывает одновременный доступ на изменение к одной и той же записи. Если конкурентный доступ маловероятен, то лучше использовать файлы.
Преимущество файлов:
— Бэкапить файлы не намного дольше, но восстанавливать можно по отдельным файлам, а не заливать весь дамп.
— Обращение к файлам и считывание быстрее, чем получение записи из БД (даже если используются сокеты).
— Кэширование файлов осуществляет автоматически ОС и сервер (можно использовать и опкэшер для контроля). А у СУБД кэширование больших файлов вызывает потерю производительности из-за вытеснения простых запросов из кэша.
— Количество файлов может быть очень большим без потери мощности сервера. У меня один раз хранилось около 12000 файлов в одной директории и ничего — сервер считывал без всяких задержек. Конечно вручную открыть эту папку было проблемно.
— Sphinx — свободно ищет по файлам.
Но при всех преимуществах файлов конкурентный доступ может испортить всю «малину», так что отталкивайтесь от него.
Когда у меня встала задача хранить 2млн HTML файлов, много чего перепробовал. Только у меня архив один раз формировался а потом в режиме read-only раздавался с помощью веб-сервера Tornado.
Остановился на SQLite + gzip. Т.е. создал таблицу с полями (name, blob) и каждый HTML сжимал в gzip.
В SQLite отключил синхронную запись для ускорения заполнения.
Успел попробовать bsd btree, bsd hash, gdbm, json-lines, csv ну и просто иерархия файлов на диске. Хотел попробовать tokyo cabinet, но не нашел драйверов для Python.
bsd btree в принципе сравнима с SQLite по скорости, но занимает больше места на диске и менее гибкое. json-lines занимает гораздо больше места, csv (и json тоже) нельзя упаковать в gzip и не поддерживает доступ по ключу.
Просто набор файлов на ФС — крайне неудобно для бэкапов и трудно с ними работать, например рекурсивное удаление занимает несколько часов.
gzip vs не gzip — однозначно gzip! Мало того, что «сжать в gzip и записать на диск» — быстрее чем просто «записать на диск», так ещё и место сэкономите.
— Странно, весь интернет хранит файлы на дисках и ничего. Если есть вариант, что MySQL будет хранить их не_на_диске, а где-то в другом месте, то, возможно, будет некий бонус. В остальном это лишние накладные расходы при каждом запросе.
— Никогда небыло проблемы с бекапом файлов. Есть туева хуча готовых утилит. И, кроме того, если вы планируете бекапить базу не методом бекапа её файлов, а поднимая слейв, то это еще больший гемор.
ну, мне кажется, что базу забэкапить на хостинге и потом скачать получившийся файл намного быстрее чем бэкапить кучу файлов размером до мегабайта каждый. Даже если их там же на хостинге добавлять в архив
p.s.
посмотрите в сторону разбития этого табуна файлов на папки. Многие хостеры решают эту проблему создавая три папки по первым трем буквам в наименовании файла. Например, файл readme хранится в папке /temp/r/e/a/readme
Вы, кроме того, можете сделать папки для версий, и так далее.
Хотелось бы увидеть + и - различных видов хранения, и когда какой лучше использовать.
С файловыми таблицами, я не работал, но я предполагаю, что там меньше головной боли с файловыми операциями, например файл не может быть блокироваться процессом, наверное есть транзакции(Т.е нельзя убить файл, если вдруг при добавлении его в таблицу, клиент отвалится). Поправьте если я не прав.
UPD: Enttity Framework дружит с файловыми таблицами?
И собственно что такое "файловая таблица" ? А большинство написанных вами предположений неверно. 1. Процесс может блокировать файл. 2. нет транзакций. 3. как писать файл и игнорировать при этом отваливание клиента или нет решать вам. Если машина неожиданно перезагрузится или произойдет другой сбой недописанный файл может остаться на диске
Читайте также: