Хранение файлов в базе vs хранение в файловой системе
Хотелось бы увидеть + и — различных видов хранения, и когда какой лучше использовать. С файловыми таблицами, я не работал, но я предполагаю, что там меньше головной боли с файловыми операциями, например файл не может быть блокироваться процессом, наверное есть транзакции(Т.е нельзя убить файл, если вдруг при добавлении его в таблицу, клиент отвалится). Поправьте если я не прав. UPD: Enttity Framework дружит с файловыми таблицами?
Отслеживать
задан 26 мая 2016 в 17:50
24.8k 12 12 золотых знаков 64 64 серебряных знака 157 157 бронзовых знаков
А вы тут на SO в поиске не вводили «файлы в базе», подобные вопросы примерно раз в месяц проходят .
26 мая 2016 в 17:58
И собственно что такое «файловая таблица» ? А большинство написанных вами предположений неверно. 1. Процесс может блокировать файл. 2. нет транзакций. 3. как писать файл и игнорировать при этом отваливание клиента или нет решать вам. Если машина неожиданно перезагрузится или произойдет другой сбой недописанный файл может остаться на диске
26 мая 2016 в 18:02
1 ответ 1
Сортировка: Сброс на вариант по умолчанию
В SqlServer вы можете использовать следующие варианты (некоторые из них применимы и к другим СУБД).
Вариант 1
В БД хранится «заголовок» файла (например, путь к файлу плюс, возможно, какой-то набор атрибутов):
create table [TableName] ( . FilePath nvarchar(4000) not NULL, . ) а данные хранятся отдельно в файловой системе. Размер БД меньше, чем если хранить в БД также и данные. Но нужно следить за ситуациями «файл есть, заголовка нет» или «заголовок есть, файла нет». На мой взгляд, если файлы являются логически важной частью данных БД (не кэш, не какие-то временные данные), то лучше посмотреть на другие варианты.
Вариант 2
В БД хранится также и содержимое файла (в столбце типа varbinary(max) ).
create table [TableName] ( . FileData varbinary(max) FILESTREAM not NULL, --либо --FileData varbinary(max) not NULL, . ) Здесь две опции — с FILESTREAM и без.
- данные хранятся в БД (в т.н. LOB pages)
- размер данных одного элемента ограничен 2Gb
- данные хранятся в файловой системе (именно как файлы)
- нет ограничения в 2Gb на элемент
- данные FILESTREAM не участвуют при подсчёте лимита на макс. размер БД (к чему чувствительны Express Edition)
- к данным можно получить доступ через соотв. API со стороны файловой системы
- (SqlServer 2014 и далее) запрашиваемые данные не отъедают из buffer pool, оставляя больше памяти для обработки запросов
И с FILESTREAM и без поддерживаются транзакции. С FILESTREAM при доступе через Transact-SQL поддержка полная, при доступе через файловую систему есть ограничения (смотреть здесь).
Вариант 3
Использование таблиц специального типа FileTable.
create table [FileTableName] as filetable Их функционал основан на использовании FILESTREAM . Таблица представляет иерархию хранящихся файлов/директорий, их данные и атрибуты. В варианте 2, чтобы создать/удалить файл, нужно создать/удалить соотв. запись в таблице. В данном варианте это можно делать напрямую через файловую систему. Например зайти в соответствующую директорию (SqlServer создаёт для этого соответствующую UNC share), создать какой-то файл/директорию, удалить/изменить, потом сделать запрос select * from FileTableName и увидеть соответствующие изменения. И наоборот — при вставке записи в таблицу через SQL в директории появится соответствующий файл или директория.
Какой вариант когда лучше использовать — думаю, зависит от конкретной задачи. В документации более детальное описание и сравнение вариантов 2 и 3.
Как хранить файлы в базе данных
Рассмотрим, как мы можем сохранять файлы, в частности, файлы изображений в базу данных. Для этого вначале определим новую базу данных и в ней новую таблицу Files с четырьмя столбцами:
- _id — первичный ключ и идентификатор, имеет тип INTEGER)
- FileName будет хранить имя файла и имеет тип TEXT
- Title будет хранить заголовок файла и также имеет тип TEXT
- ImageData будет содержать бинарные данные файла и имеет тип BLOB
Для создания базы данных и таблицы для хранения файлов определим следующую программу:
using System; using Microsoft.Data.Sqlite; namespace HelloApp < class Program < static void Main(string[] args) < // выражение SQL для добавления данных string sqlExpression = @"CREATE TABLE Files (_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, Title TEXT NOT NULL, FileName TEXT NOT NULL, ImageData BLOB)"; using (var connection = new SqliteConnection("Data Source=filesdata.db")) < connection.Open(); SqliteCommand command = new SqliteCommand(sqlExpression, connection); command.ExecuteNonQuery(); Console.WriteLine("Таблица Files создана"); >Console.Read(); > > >
Сохранение файлов
Определим код, в котором будут загружаться данные в таблицу:
using System; using System.Collections.Generic; using System.IO; using Microsoft.Data.Sqlite; namespace HelloApp < class Program < static void Main(string[] args) < // метод в качестве параметров получает полный путь к файлу и его название SaveFile("D:\forest.jpg", "Лес"); Console.Read(); >private static void SaveFile(string filename, string title) < // сначала считываем файл из файловой системы // получаем короткое имя файла для сохранения в бд string shortFileName = filename.Substring(filename.LastIndexOf('\\') + 1); // forest.jpg // массив для хранения бинарных данных файла byte[] imageData; using (FileStream fs = new FileStream(filename, FileMode.Open)) < imageData = new byte[fs.Length]; fs.Read(imageData, 0, imageData.Length); >using (var connection = new SqliteConnection("Data Source=filesdata.db")) < connection.Open(); SqliteCommand command = new SqliteCommand(); command.Connection = connection; command.CommandText = @"INSERT INTO Files (Title, FileName, ImageData) VALUES (@FileName, @Title, @ImageData)"; command.Parameters.Add(new SqliteParameter("@FileName", shortFileName)); command.Parameters.Add(new SqliteParameter("@Title", title)); command.Parameters.Add(new SqliteParameter("@ImageData", imageData)); int number = command.ExecuteNonQuery(); Console.WriteLine($"Добавлено объектов: "); > > > >
В данном случае весь код сохранения файла вынесен в отдельный метод SaveFile() , который в качестве параметров получает полный путь к файлу и его название. Вначале он считывает данные с помощью класса FileStream в массив байтов. Считанный мссив байтов собственно и будет представлять данные файла.
using (FileStream fs = new FileStream(filename, FileMode.Open))
Затем для сохранения в бд массив байтов передается в строку запроса SQL через один из параметров:
command.Parameters.Add(new SqliteParameter("@ImageData", imageData));
Если после выполнения этой программы мы откроем базу данных через DB Browser for SQLite , то при просмотре добавленной записи мы даже можем увидеть загруженное изображение:
Хотя в данном случае загружается изображение, но это частный случай, в принципе можно использовать и другие типы файлов.
Извлечение файлов из базы данных
Теперь произведем обратную операцию — получим файл из БД. Вначале определим класс файла, который упростит работу с данными:
public class Image < public Image(int id, string filename, string title, byte[] data) < FileName = filename; Title = title; Data = data; >public int Id < get; private set; >public string FileName < get; private set; >public string Title < get; private set; >public byte[] Data < get; private set; >>
Теперь применим этот класс для считывания данных:
using System; using System.Collections.Generic; using System.IO; using Microsoft.Data.Sqlite; namespace HelloApp < class Program < static void Main(string[] args) < GetFiles(); Console.Read(); >private static void GetFiles() < Listimages = new List(); string sql = "SELECT * FROM Files"; using (var connection = new SqliteConnection("Data Source=filesdata.db")) < connection.Open(); SqliteCommand command = new SqliteCommand(sql, connection); using (SqliteDataReader reader = command.ExecuteReader()) < if (reader.HasRows) // если есть данные < while (reader.Read()) // построчно считываем данные < int string filename = reader.GetString(1); string title = reader.GetString(2); byte[] data = (byte[])reader.GetValue(3); Image image = new Image(id, filename, title, data); images.Add(image); >> Console.WriteLine($"Считано объектов: "); > // для примера сохраним первый файл из списка в папку приложения if (images.Count > 0) < using (FileStream fs = new FileStream(images[0].FileName, FileMode.OpenOrCreate)) < fs.Write(images[0].Data, 0, images[0].Data.Length); Console.WriteLine($"Файл сохранен"); > > > > > public class Image < public Image(int id, string filename, string title, byte[] data) < FileName = filename; Title = title; Data = data; >public int Id < get; private set; >public string FileName < get; private set; >public string Title < get; private set; >public byte[] Data < get; private set; >> >
Весь код считывания вынесен в отдельный метод GetFiles() . В данном случае мы считываем все файлы из БД. Также, как и в общем случае, с помощью объекта SqliteDataReader получаем значения из БД и по ним создаем объект Image, который потом добавляется в список. Чтобы получить непосредственно данные файла, мы можем просто преобразовать соответствующее значение к массиву байт:
byte[] data = (byte[])reader.GetValue(3);
И в конце смотрим, если в списке есть элементы, то берем первый элемент и сохраняем его на локальный компьютер. И после сохранения в папке нашей программы появится загруженный из базы данных файл.
Организация хранилища файлов внутри базы данных 1C+MS-SQL
Довольно часто в различных вариантах учетных задач возникает проблема размещения различной информации (файлов) с привязкой их к базе данных. Например, построение на базе 1С системы учета документооборота с хранением файлов в формате Word , Excel . Если организовывать такое хранилище в виде каталогов файлов, то очень велика вероятность нарушения работы системы в результате ряда факторов, вызванных, как правило, неумелыми, либо наоборот, «слишком умелыми» действиями пользователя. Другими словами система хранения файлов в каталоге базы 1С , ввиду своей общедоступности является существенной прорехой в безопасности. Файлы легко переименовать, перезаписать, испортить или удалить.
Таким образом, мы постепенно подходим к мысли хранения файлов непосредственно в базе данных MS SQL . Остается вопрос, каким образом всё это можно организовать? В своей статье, я постараюсь дать как можно более подробный ответ на этот вопрос. Все исследования будут происходить на платформе MS SQL 2000 , далее просто SQL .
Итак, первый вопрос на повестке дня – создание таблицы для хранения файлов. Как известно, SQL позволяет создавать таблицы, содержащие поля с типом IMAGE (в переводе на русский «образ»). В поле такого типа мы и будем хранить свой файл. Для обеспечения уникальности данных и возможности в дальнейшем получения доступа к файлу нам необходимо создать колонку с типом int , которая у нас будет иметь свойство « Identity ». Это свойство обеспечит нам автоматическую нумерацию строк таблицы средствами SQL . Первоначальный номер у нас будет выставлен в « 1 », приращение номера будет тоже идти по « 1 ».
Второй вопрос на повестке дня, – в какой базе создавать таблицу? Требуется ли для ее хранения отдельная база данных, или можно воспользоваться уже существующей базой данной 1С . Как показали опыты, 1С при операциях, затрагивающих базу данных, таких как выгрузка, загрузка, реструктуризация, работает только со «своими» таблицами не затрагивая те, которые отсутствуют в ее файле DDS . Это дает нам возможность поместить нашу таблицу непосредственно в базе данных 1С . При этом, однако, следует иметь в виду, что раз 1С не трогает «не свои» таблицы, то при выгрузке данных наша таблица выгружаться не будет. Поэтому, для обеспечения резервного копирования или переноса данных придется использовать средства SQL . С другой стороны, если мы помещаем нашу таблицу в отдельную базу данных, то получаем более гибкие возможности работы, в частности легче можно будет наладить репликацию данной таблицы, что немаловажно. В моем примере таблица будет расположена непосредственно в базе данных 1С .
Теперь нам остается создать необходимый скрипт. Сразу определимся, что работать с хранимыми в базе данных файлами мы будем с использованием технологии ADO . Отсюда вытекает необходимость работы в нормальном рабочем режиме только в разделенном режиме работы 1С -Предприятия, так как в монопольном соединение ADO не сможет получить доступ к базе данных.
Сделаем так, чтобы процедура проверки наличия необходимой таблицы в базе данных запускалась в из процедуры ПриНачалеРаботыСистемы () когда режим работы пользователя разделенный. Текст процедуры можно посмотреть здесь.
Теперь у нас есть в базе необходимая таблица. Следующая задача — научиться записывать в нее необходимые данные. Для этого мы воспользуемся такой возможностью SQL-сервера , как « BULK » операции. Это операции манипулирования большими объемами данных, точнее сказать это операции обмена данными между SQL-сервером и файлом определенной структуры, который может содержать как одну строку данных для SQL-сервера , так и большое их количество. Определение структуры данных, содержащихся в файле, происходит с помощью специального файла-формы.
Итак, мы определлись, что будем записывать файл в базу данных с помощью операции « BULK INSERT ». В процедуре записи файла в базу данных, мы должны выполнить следующие действия:
- получить последний номер строки и сохранить его в переменной;
- создать и заполнить содержимым файл формы;
- выполнить архивацию исходного файла для уменьшения его объема на сервере (не обязательно — у меня делается ввиду большого объема данных);
- создать файл исходных данных;
- выполнить запрос на размещение на сервере;
- получить последний номер строки и сравнить его с сохраненным ранее значением;
- если номер увеличился на «1» — значит операция прошла успешно, иначе — была ошибка.
Важное примечание: так как результирующий запрос будет выполнять сервер, необходимо, чтобы файлы, используемые сервером были доступны для него. Соответственно, при формировании путей к файлам мы должны «смотреть на них» со стороны сервера. То есть, получив запрос на размещение файла «C:myfile.doc» сервер будет искать его именно на своем диске, а не на компьютере-клиенте. Лучше всего сохранять файлы в сетевых папках доступных серверу и сразу указывать сетевые пути .
Текст процедуры глобального модуля осуществляющей размещение файла в таблице базы данных SQL можно посмотреть здесь.
Следующий вопрос, который нам предстоит решить — каким образом можно извлечь файл из базы данных. Тут дело обстоит сложнее. Придется воспользоваться утилитой BCP , входящей в состав поставки MS SQL сервера . Эта утилита служит для пакетного обмена данными с SQL сервером . В принципе, мы могли бы воспользоваться этой утилитой и для загрузки файла на сервер, но, думается, что, чем меньше будет мелькать дополнительных окошек, тем лучше. Принцип использования утилиты такой же как у команды « BULK INSERT », с тем различием, что нам нужно получить данные с сервера, а не загрузить на него.
Таки образом список действий, который нам надо выполнить становится намного короче:
- создать файл формы;
- выполнить утилиту BCP с параметрами;
- разархивировать полученный файл (если использовалось архивирование).
Функцию глобального модуля, служащую для извлечения файла с сервера можно посмотреть здесь.
Как лучше хранить много файлов — в базе данных или в виде файлов?
Делаю сервис, будет храниться несколько версий файлов, плюс, таких изначальных файлов предполагается что будет немало. На данный момент я рассчитываю ориентировочно на пару тысяч файлов (в итоге), но в перспективе количество файлов может быть и больше.
— где лучше хранить все эти файлы — в виде Blob/text в MySql или же в виде отдельных файлов на диске? Сами файлы напрямую клиентам отдаваться не будут — сначала они обрабатываются специальным php скриптом и только потом результат обработки отдается. Для каждого клиента результат может быть разным (а может быть и таким же)
— как лучше все это потом бэкапить? Есть подозрение что все эти тысячи файлов бэкапить будет тяжко, если не хранить их в базе данных…
- Вопрос задан более трёх лет назад
- 47383 просмотра
Комментировать
Решения вопроса 1
На самом деле оба варианта по реализации настолько минимально отличаются, что хорошим советом будет «сделайте сейчас оба варианта, используйте сначала файлы, а когда файлов будет много померяете производительность».
В 99% случаев файлы лучше, т.к. к ним есть прямой и очевидный доступ без всяких баз, а база в любом случае прослойка.
По бакапам ситуация двоякая, с одной стороны файлы удобнее бакапить тем же инкриментом и обычными файловыми средствами, с другой стороны разовый бакап базы сделать можно сделать просто скопировав файл с таблицей и не надо собирать кучу файлов.
По скорости/нагрузке, безусловно если это один сервер, то файлы будут побыстрее (только бейте на папки, не пихайте больше 1000 в одну в любом случае), но если у Вас несколько серверов, то отдельный сервак с базой под файлы может иметь определённые преимущества, доступ к базе по сети чуть более очевиден (хотя если у Вас есть админ, то не принципиально).
Файлы при прочих равных однозначно лучше попадают в кэш, с другой стороны засирание кэша базой проще контроллировать.
Ответ написан более трёх лет назад
Нравится 7 1 комментарий
Не совсем. На БД проще сделать HA и репликацию — абстракция от файлов же. Если делать на файлах то надо думать как сделать их доступными (если надо) и реплицию. Однако, если принять во внимание, что ФС это тоже БД то различия почти минимальны.
Может автору лучше вообще сам себе Amazon S3 сделать? или Luwak + Riak или CouchDB или Riak CS.
Ответы на вопрос 12
Виталий Желтяков @VitaZheltyakov
Единственное преимущество хранения файлов в БД, на которое нужно обратить внимание — это конкурентный доступ. То есть СУБД корректно обрабатывает одновременный доступ на изменение к одной и той же записи. Если конкурентный доступ маловероятен, то лучше использовать файлы.
Преимущество файлов:
— Бэкапить файлы не намного дольше, но восстанавливать можно по отдельным файлам, а не заливать весь дамп.
— Обращение к файлам и считывание быстрее, чем получение записи из БД (даже если используются сокеты).
— Кэширование файлов осуществляет автоматически ОС и сервер (можно использовать и опкэшер для контроля). А у СУБД кэширование больших файлов вызывает потерю производительности из-за вытеснения простых запросов из кэша.
— Количество файлов может быть очень большим без потери мощности сервера. У меня один раз хранилось около 12000 файлов в одной директории и ничего — сервер считывал без всяких задержек. Конечно вручную открыть эту папку было проблемно.
— Sphinx — свободно ищет по файлам.
Но при всех преимуществах файлов конкурентный доступ может испортить всю «малину», так что отталкивайтесь от него.
Ответ написан более трёх лет назад
Комментировать
Нравится 6 Комментировать
Когда у меня встала задача хранить 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 и записать на диск» — быстрее чем просто «записать на диск», так ещё и место сэкономите.
Ответ написан более трёх лет назад
Комментировать
Нравится 3 Комментировать
— Странно, весь интернет хранит файлы на дисках и ничего. Если есть вариант, что MySQL будет хранить их не_на_диске, а где-то в другом месте, то, возможно, будет некий бонус. В остальном это лишние накладные расходы при каждом запросе.
— Никогда небыло проблемы с бекапом файлов. Есть туева хуча готовых утилит. И, кроме того, если вы планируете бекапить базу не методом бекапа её файлов, а поднимая слейв, то это еще больший гемор.
Ответ написан более трёх лет назад
Нравится 2 2 комментария
Fahrain @Fahrain Автор вопроса
ну, мне кажется, что базу забэкапить на хостинге и потом скачать получившийся файл намного быстрее чем бэкапить кучу файлов размером до мегабайта каждый. Даже если их там же на хостинге добавлять в архив
p.s.
посмотрите в сторону разбития этого табуна файлов на папки. Многие хостеры решают эту проблему создавая три папки по первым трем буквам в наименовании файла. Например, файл readme хранится в папке /temp/r/e/a/readme
Вы, кроме того, можете сделать папки для версий, и так далее.
БД все-равно сделает это (сохранит на диск).