Хранение файлов в 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
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 99

      +7
      Решить перечисленные проблемы с файлами гораздо проще, чем решить, например, проблемы с производительностью, когда файлы в бд.
      1-пункт из вашего «мозгового штурма» + скрипт сборки мусора.
        0
        Именно так сейчас и сделано в нашем проекте, но хотелось бы сделать более прозрачный и удобный механизм хранения файлов, так как задача очень распространенная, и для каждого хранилища писать сборщики мусора как то не очень хочется.
          0
          Это от проекта зависит.
          Для не очень посещаемого ресурса, для сайта-визитки (если речь идёт о ЦМС), для ресурса, работающего где-нибудь в пределах локальной сети общежития, кафедры или школы например, такой подход вполне приемлим.
            0
            вел проект соц.сеть, хранили все фотки в базе, как написано выше. никаких проблем с производительностью не чувствовали.
            +1
            > файлы не удаляются при удалении соответствующей записи БД
            Это почему? Все спокойно удаляется, или может мы вас не поняли?

            >проблемы при одновременной попытке обновления файла
            Разъясните, что вы имеете ввиду «обновление файла»?

            >нарушение синхронизации между БД и файловой системой при откате транзакции
            Кто вам мешает удаленный файл «ложить» в корзину?

            >при резервном копировании и восстановлении информации в БД может возникнуть рассинхронизация с файловой системой
            Спорный вопрос

            >файлы не подчиняются ограничениям доступа, наложенным с помощью БД
            Ну это другое дело :)

            Ну а хранить файлы в БД — это извините имхо бред. Пару фильмов и ваша БД будет необъятных размеров.

            Плюс разные «преобразования» до добра не доводят — это еще гораздо худшее зло.
              +2
              Почитайте презентацию SQL Antipatterns, там более подробно расписано и даже нарисовано про проблемы храниения файлов отдельно от БД.
              А по поводу хранения файлов в БД — абсолютно согласен с вами, это огромный удар по производительности. И цель этого поста была — именно рассмотреть способы устранения недостатоков хранения файлов «отдельнно» и проблем с производительностью при хранении «в БД».
              • UFO just landed and posted this here
                • UFO just landed and posted this here
                    +1
                    Для MySQL требуется скрипт, который будет выбирать файл из базы и отдавать http-серверу.
                    Пока файл качается — скрипт занимает память. Файлы могут качаться долго и в несколько потоков. В итоге, память тратится нерационально.
                      +1
                      потому что nginx ипользует sendfile, а при запросе его из бд будет несколько дополнительных копирований данных
                    • UFO just landed and posted this here
                        0
                        Спасибо за ссылку, обязательно поставлю на досуге и посмотрю как оно работает. Это в принципе усложненная версия того, что я хочу сделать (специализированный storage engine для хранения файлов, причем извлечение файлов идет мимо MySQL).
                        0
                        Есть замеры. Производительность начинает заметно сильнее просидать после того как фаил переваливает около 5 мб.

                        Замеры были на оракле и mssql про mysql мне кажется тоже можно нарыть. Исследование проводилось каким-то институтом американским. Резюме было такое, что хранить до 5 мб файлы можно со свистом в БД, после 5 лучше в файловой системе, именно по этому в оракле и mssql есть на этот счет специальный функционал (filestreaming и т.д.) это когда база сама следит за целостностью файлов, которые хранятся в файловой системе.
                        • UFO just landed and posted this here
                          • UFO just landed and posted this here
                              0
                              ага это новая фича в 2008 mssql, в данном релизи там функционал у нее правда не весь успели сделать.
                            0
                            и будет к mysql вместо 10-ти, как обычно, соединений — 1000 (тысяча), ибо тпштч или нет — а большой файл будет долго тянуться именно с mysql
                            • UFO just landed and posted this here
                                0
                                одновременных скачиваний много, если медленные клиенты. Причём либо постоянно выкачиваем файлы из базы в дисковый кэш, либо как Вам и хочется, читаем blob'ы как поток — и получаем эти 1000 соединений с mysql.

                                Да и с масштабированием есть проблемы — файлов на терабайты придумать легко, а вот сервер mysql на терабайты мне как-то не представляется.
                                • UFO just landed and posted this here
                                    0
                                    а если сервера в разных датацентрах, что при раздаче файлов и получается? файлы перегнать через rsync — легко, а реплицировать базу?

                                    И мы получаем ещё одну возможную проблему. Длительность транзакций резко увеличивается при хранении файлов, наверняка будут проблемы с целостностью базы.
                                    • UFO just landed and posted this here
                                        0
                                        >Э-э-э-э… Мы про что сейчас говорим? Теряю нить рассуждения. Про
                                        >blobstreaming для mysql из ссылки выше?

                                        Нет, про вообще хранение в базе при любом алгоритме. Обычно скрипт отрабатывает за доли секунды, и на вероятность, что что-то случится за время работы скрипта (перезагрузка компьютера) и не все требуемые операции с базой завершатся мы как-бы не обращаем внимания — mysql транзакций не имеет и на риск приходится идти. А в случае хранения файла размером 30 мегабайт время выполнения скрипта будет уже секунды. Вероятность аварийного завершения, соответственно, на порядок или на порядки выше.
                                        • UFO just landed and posted this here
                                            0
                                            в MySQL транзакции есть только в мёртвой уже InnoDB, а на практике речь идёт о myisam. То есть реально транзакций нет.
                                              0
                                              Скорее MyISAM уже мертв.
                                              А InnoDB как раз на нагруженных проектах показывает себя очень неплохо.
                                      0
                                      >Ну вот я и интересуюсь — в чём проблема в 1000 соединений с MySQL? Ну, пусть 20 килобайт занимает каждое соединение

                                      У Вас очень скромные настройки mysql :)

                                      memory = key_buffer+(sort_buffer_size+read_buffer_size)*max_connections

                                      У меня сейчас довольно скромные настройки среднего сервера (едва за сотню sql-запросов в секунду). Так при 1000 соединений оно сожрёт в моём случае:

                                      memory = 384M + (3M+3M)*1000 = 6384M. А на сервере всего 2,5Гб :)
                                0
                                >Такой ли уж «огромный удар по производительности»?

                                1. Разница в скорости отдачи статики и динамики — уже порядок или даже более. Независимо от бэкенда динамики.

                                2. Проблемы при параллельной раздаче с модификациями — mysql будет периодически лочить всю базу.

                                3. Наконец, засовывать 30-50-100Гб в одну базу — это страшно :D
                              +1
                              Если я правильно понял автора, то последний пункт реализует целостность данных на уровне БД, а не на программном, как во всех других случаях.
                              Идея вобщем-то хорошая, по-моему.
                            • UFO just landed and posted this here
                                0
                                Можно даже не по определенным параметрам отдавать файл, а просто используя eTag т.к. так и так образутся уникальное значение.
                                  0
                                  а как бы с тысячей пользователей запросивших файл в первый раз?
                                    0
                                    им просто будет посылаться это значение и при следующим запросом файла будет высылать хедер об изменении файла. В чем проблема собстна?)
                                  +10
                                  Вобщем-то давно пришли к мнению, что зло рассинхронизации БД с ФС сервера намного меньше, чем зло хранения файлов в БД.
                                    0
                                    Меньшее зло, но почему бы не объеденить преимущества обоих подходов? Файлы и как бы в БД и при этом лежат просто на ФС. Осталось самая малость — написать storage engine для MySQL :-)
                                      +4
                                      Наверное потому, что это неизбежно вызовет оверхед использования ФС.
                                      Зачем городить надстройку над MySQL, которая бы повторяла функционал ФС? Если есть просто ФС?
                                        +1
                                        Абсолютно согласен. Связь между ФС и БД должен реализовывать класс Модели файлов, который и будет обеспечивать целостность записи, изменения и удаления.

                                        Обычно Мускул подключается через сокеты в PHP скрипт.

                                        И что же мы имеем если файлы у нас в БД: дополнительную нагрузку на сокетах (а чтение с диска как было так и останется, за исключение закешированных файлов самим мускулом(хотя мускул по-моему блобы не кеширует))
                                          0
                                          Вы не совсем поняли идею — файлами управляет MySQL — создает, удаляет, переименовывует и т.д., а пишем и читаем мы их просто через файловую систему, без всяких MySQL, сокетов и блобов…
                                            +2
                                            Эм. Я конечно задам дурацкий вопрос, но зачем тут СУБД?
                                              0
                                              Для поддержания целостности…
                                                0
                                                Для этого достаточно использовать транзакции.
                                                  0
                                                  Т.е. например, при чистке мусора запросить из базы файлы которые необходимо удалить, затем средствами php удалить их, сообщить базе по завершении, что всё удалено и удалить соответствующие записи. Я правильно понял?
                                                    0
                                                    Да. Для ускорения процесса, лучше делать двухфазное удаление. В первой транзакции помечаем, что файлы удалены (ставим флажок), потом переодически запускаем сервис уборщика мусора, который выбирает такие записи, откывает транзакцию, удаляет файлы, затем удаляет записи и закрывает транзакцию. В случае если транзакция откатилась, повторить транзакцию.
                                                      0
                                                      И того по два запроса на каждый цикл уборки + необходимость писать уборщик. Вместо того чтобы просто удаляя строку из бд автоматически удалить все связанные с ней файлы средствами mysql.
                                                        +1
                                                        Все верно, но вы не думали, что операция удаления файлов может отнимать время? К примеру файлы большие или их много. В предложенной мной схеме это не критично.
                                                          0
                                                          Удаление средствами PHP + MySQL или только MySQL при наличии встроенных возможностей. Приемущество явно на стороне второго.
                                                          На текущий момент способ с примененем внешнего синхронизатора единственный (если исключить RitmarkFS) из возможных. Поэтому предлагаю завершить дискусию.
                                          0
                                          а если в файловом хранилище более одного узла?
                                      +3
                                      Обычно в БД хранят не картинки интерфейса, которые дергаются один раз и остаются в кеше, а скажем фотография к новости (пользователь посмотрел новость и ушел дальше к следующей), архивы, фильмы (скачал один раз и все). Тоесть кешированием тут не решишь проблему производительности.
                                        +3
                                        Я бы предложил не новый Engine, а новый тип поля — FILE

                                        Типа такого:

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

                                        или

                                        SELECT * FROM `data_storage`.`files`WHERE FILE_SIZE(`data`) > 10000

                                        При этом mySQL сам создает отдельный файл для каждой записи таблицы. И есть дополнительные функции для работы с полем типа FILE.

                                        Основной недостаток такого подхода (как в Вашей, так и в моей модификации), особенно при больших файлах — это пожирание оперативки и дикий траффик между клиентом (например, php-скриптом) и mySQL-сервером, который в общем случае может быть сетевым.

                                          0
                                          Как раз сегодня та же мысль пришла в голову! Однако подумав, посчтилали что сделать свой storage engine куда проще, чем реализовать новый тип данных…
                                          +5
                                          Лично мое мнение — оставьте файлы файловым системам.

                                          Вышеупомянутые проблемы решаются настолько элементарно, что выглядят притянутыми за уши.
                                          • UFO just landed and posted this here
                                              0
                                              Производительность от этого не упадёт! Поддержание целостности данных и работа сборщика мусора требует дополнительных запросов и ресурсов. Просто полноценных средств для работы с файлами с помощью mysql ещё нет.
                                            +2
                                            Однажды мой знакомый сказал: «Храни файлы в БД лишь тогда, когда иначе ну совсем нельзя»
                                              0
                                              Дело в том что на самом деле такой подход нарушает суть самой базы данных! Ведь та же аватарка — это данные о пользователе. И как бы там нибыло файлы ничем не отличаются от описания пола пользователя, просто они больше!
                                              Суть БД в том, что она хранит и централизованно обрабатывает данные. И то что существующие БД не поддерживают полноценную работу с файлами — признак того, что на самом деле эти БД неполноценны, как таковые, т.к. не соответствуют своей сути. Вам никогда не приходило в голову: почему каждый раз как дело касается работы с файлами программисту приходится самостоятельно создавать систему синхронизации и поддержания целостности данных?
                                                0
                                                Данные в БД не только для хранения, но и для последующего поиска. Например — фильтр по тому или иному параметру.
                                                Кстати, другой мой знакомый говорил (это было во времена, когда форумы и гостевые использовали в качестве БД текстовые файлы): использование текстовых файлов в качестве БД на порядок лучше в плане производительности, нежели реляционные БД. Но лишь до тех пор, пока нам не потребовалось что-то искать.
                                                Искать что-либо в BLOB будет несколько странно, а значит одно из самых серьёзных преимуществ БД теряет смысл.
                                                Кроме того лично очень осторожно отношусь к действиям, которые увеличивают размер БД. Больше размер — меньше производительность. Аватарки в БД для форума на 100 человек — ерунда. Коллекция фотографий каждого пользователя многомиллионной соцсети — неразумное раздутие архитектуры проекта.
                                                  0
                                                  БД должна управлять файлами и содержать на них ссылки (не будете же вы индексировать jpg или mp3 файлы по содержимому) а хранить в ФС и в результате запроса указывать откуда эти файлы взять вот и всё. А когда удаляется запись из БД, то удалять файл. Т.о. БД не раздувается, время программистов экономится, нагрузка на сервер снижается!
                                              +2
                                              Можно использовать UDF (user defined function), вызываемую из триггера, удаляющую или обновляющую файл (локально или на удалённом сервере) при изменении записей в таблице. Это возможно.
                                                0
                                                Спасибо! Не знал о наличии такой функцинальности у триггеров, это сильно упрощает все!
                                                  0
                                                  Надеюсь вы отдаете отчет в том насколько это небезопасно… пропущенная SQL-инъекция сможет прибить не только вашу БД, но и файлы.
                                                    0
                                                    Если допускать возможность SQL инъекции, то хранить данные в БД тоже очень небезопасно выходит… Инъекция может прибить данные в таблице (связь имен файлов с статьями например) и тогда хоть файлы и будут нетронутыми, они будут бесполезные, так как удалены данные, с которыми они связаны.
                                                      0
                                                      Ну БД можно и нужно бэкапить → можно восстановить, как правило на больших ресурсах эта задача гораздо легче выполнима, чем бэкап файлов (которых могут быть терабайты, при базе в десятки-сотни мегабайт)
                                                        0
                                                        Да вы правы, это может быть дополнительной дырой в системе, но ее ведь можно закрыть как на уровне ОС (MySQL сервер выполянется под непривелигированым пользователем) так и средствами MySQL — забрать у пользователя привилегию Execute.
                                                          0
                                                          Правильнее дыр все таки не создавать.

                                                          Может я не уловил, но вроде как высказана идея, привязать триггер с UDF к удалению/обновлению записи, после чего UDF сделает exec и прибьет соответствующий записи файл? Если я таки понял правильно, то причем тут ОС, пользователи и права?

                                                          Приходит нехороший парень, инджектит вам запрос типа DELETE * FROM… после чего SQL-сервер, совершенно законно, от своего имени, с данными ему вами правами — прибивает все ассоциированные с записями ресурсы.

                                                          Понятно, что кое-какие меры можно предусмотреть, но моя консервативная точка зрения подсказывает мне, что лучше не создавать себе дополнительных проблем без надобности, а надобности я если честно пока не увидел.

                                                      0
                                                      Эм. Вы точно знаете как работают триггеры и хранимые процедуры? :) Туда можно запередаваться SQL-иньекцию в параметрах. Она не будет работать. Просто потому что в отличии от обычных запросов, что триггеры, что хранимые процедуры компилируются один раз. При создании. Далее никаких изменений не происходит не взирая на то, что вы там передали.
                                                        +2
                                                        Если нехороший человек найдет способ сделать delete from, то дальше за него все сделает сам сервер, никаких параметров никуда передавать не надо будет. Опять же если я правильно понял то, для чего предлагается использовать UDF.

                                                        Как работают триггеры я знаю :)
                                                          –2
                                                          Ну давайте расскажите мне как из хранимки или триггера вызывать delete from если их там нет. Тоже самое можно сказать про UDF. Ни то ни то не является динамически компилируемым в отличии от обычных запросов.
                                                            +1
                                                            Блин, может я торможу конечно… или вы, не знаю :)

                                                            злоумышленник делает простой зпрос DELETE FROM users WHERE 1=1
                                                            на удаление записи висит триггер, в котором происходит вызов функции с передачей ей OLD.id или там OLD.file_path или по какой то другой логике заложенной в триггере (выборка из соседней таблицы, обработка ее результатов в цикле). Как раз с UDF я дела не имел, каким образом там происходит exex(), последствием которого будет удаление файла я не знаю — я лишь верю исходному комментарию в этой ветке, что это возможно. Значит результатом будет удаление всех файлов ассоциированных с каждой записью в таблице users. Штатным, заложенным в систему способом

                                                            не? если я не правильно понял что-то — прошу мне объяснить тогда, что написано в первом каменте в этой ветке.
                                                              –1
                                                              А теперь давайте представим, что злоумышленник передает SQL иньекцию в строке, которая не вклеивается в обычный SQL запрос, а передается в хранимую процедуру. Т.е запрос будет иметь вид UPDATE_USERS(«DELETE FROM users WHERE 1=1;»,2); к примеру. Как видим такой запрос не воспримется как SQL-запрос. Как результат, она не будет выполнена. Я про это. А про то что вы рассказываете никакого отношения к собственно опасности триггеров, как сами понимаете не имеет и является возможным дополнением к механизмам целостности СУБД.
                                                                +2
                                                                Ммм… а я где то писал про опасность использования триггеров?

                                                                Я лишь сказал, что небезопасно вешать удаление файлов на удаление записи в БД ибо в случае ошибки в скриптах, злоумышленник сможет грохнуть не только записи в таблицах БД (которую можно бэкапить), но и всё файло с ней связанное. По моему вы изначально меня неправильно поняли.

                                                                  0
                                                                  Первоначальный диалог
                                                                  viperet:
                                                                  Спасибо! Не знал о наличии такой функцинальности у триггеров, это сильно упрощает все!

                                                                  maxshopen:
                                                                  Надеюсь вы отдаете отчет в том насколько это небезопасно… пропущенная SQL-инъекция сможет прибить не только вашу БД, но и файлы.

                                                                  Первый автор говорит про триггеры вы говорите что это не безопасно. Вот и все.
                                                                    0
                                                                    Первый автор говорит не это, а вот это:
                                                                    Можно использовать UDF (user defined function), вызываемую из триггера, удаляющую или обновляющую файл (локально или на удалённом сервере) при изменении записей в таблице. Это возможно.

                                                                    вся ветка — обсуждение данной возможности, с учетом собственно темы топика. Вы же зацепились не понятно зачем, конкретно за триггеры, и продолжаете упорствовать :)
                                                                  0
                                                                  ну если у тебя параметр на входе процедуры строка, и ты ее подставляешь в свой запрос внутри, то вот такой вот запрос тебя может повалить:

                                                                  процедура(param1,param2){
                                                                  select * from masterdb where id like '%param1%'
                                                                  }
                                                                  а значение параметра сделаем вот таким param1= 'union drop table abvgd; '

                                                                  сорри мог где нить с синтаксисом напутать, но смысл должен быть понятен.
                                                                    0
                                                                    Не павалит :] Дело в том что запрос будет видеть как:
                                                                    select * from masterdb where id like 'union drop table abvgd;'
                                                                    Т.е. не будет исполняемым. И он не будет компилироваться. Потому что хранимки и триггеры компилируются один раз. При создании.
                                                          +1
                                                          Угроз безопасности, исходящих от SQL-инъекций, при использовании UDF ровно столько же, сколько и без них. Если грамотно организовано поддержание целостности данных, и строки таблицы, и файлы (в каком бы виде они ни хранились) должны удалиться, если выполняется соответствующий DELETE FROM. Борьба с SQL-инъекциями должна выполняться до передачи SQL-запроса СУБД.
                                                            0
                                                            Не совсем так. Отправка SQL-иньекции в UDF или хранимую процедуру ни к чему летальному не приведет.
                                                              0
                                                              Да никто и не говорит про инъекции в триггеры. Речь идёт о том, что триггер повешенный на удаление будет выполнен в результате соответствующего запроса. И если придёт запрос: DELETE * FROM tablename то будут удалены все записи и все файлы с ними связанные.
                                                                0
                                                                И что? В случае внешних ключей и включенного каскадного удаления, то произойдет удаление из других таблиц. Вам не кажется, что сдесь путают мягкое с теплым? Мне вот кажется.
                                                      0
                                                      Есть некий демон. Он раз в n делает SELECT из базы на на получение списка «файлов на удаление». Далее удаляя файл делает UPDATE базы, добавляя флаг, что файл был удален. По окончаню просто удаляются записи с этим лагом, или переносяться в историю…
                                                      так делал я… кажется логичным )
                                                        0
                                                        Это как раз и есть «способ 2».
                                                        «к тому же у нас в проекте записи на удаление отбираются достаточно сложным SELECT, которые нельзя переделать в один UPDATE»
                                                        • UFO just landed and posted this here
                                                      • UFO just landed and posted this here
                                                          0
                                                          Как мне представляется, в пятом пункте вы решили излишне усложнить задачу. При определенных ограничениях и допущениях, например, файл в БД не может быть изменен ранее некоторого времени. Файл из БД можно отдать скриптом, а Nginx фронтенд кэширует его на необходимое время.
                                                          Думаю можно придумать механизм форсированного обновления кэша Nginx при изменении записи в БД, но в этом моменте нужно разобраться…
                                                            +1
                                                            Вот какое решение в своих проектах используем мы (правда не MySQL а Oracle у нас, но это значения не имеет в данной ситуации).

                                                            Хранить файлы в ФС действительно неудобно. У нас есть специальная папочка temp. При обращении к файлу приложение пишет его в temp с именем {имя_таблицы}_{id_файла}.{расширение} или {имя_таблицы}_{имя_файла_из_таблицы} (для второго случая на поле с именем файла необходимо наложить unique). После этого, при каждом обращении к файлу, функция проверяет, существует ли данный файл? Если существует — просто отдает его имя, если нет — записывает по приведенному выше алгоритму.

                                                            Сама папочка temp чистится раз в сутки, причем не удаляются файлы созданные в последний час. На всякий случай, в друг их кто то еще использует. Файлы которые созданны раньше наврятли кто-то будет использовать.

                                                            Вот собственно и все. Одновременно мы боремся и с висящими файлами (я думаю если файл проживет несколько лишних часов это не особо принципиально), и с повышенной нагрузкой связанной с регулярной перезаписью файлов.

                                                            Безусловно, подходит не для всего, но нам такого хватает. Если есть более повышенные требования к файлам думаю данный метод не подойдет. Но проблемы заявленные в топике решает.
                                                              +1
                                                              Да я вот тоже подумал сразу про такое решение. Только вот обсуждается именно вывод файла минуя скрипты. Причем это актуально при высокой посещаемости. Если большое количество картинок будет из php выдаваться то на apache возникает не слабая нагрузка. Причем проверено лично мной на практике.
                                                              0
                                                              файл в бд — это как минимум запрос к backend, в случае когда можно обойтись статикой.

                                                              ps. Интересно было бы посмотреть на сравнение быстродействия
                                                                +1
                                                                микроскопом гвозди забиваете.
                                                                какие именно файлы там хранить?

                                                                вот у меня допустим 300 дистрибутивов по 30-80 мегабайт.
                                                                обновляются они раз в неделю. Сейчас они заливаются по FTP. потоком, все сразу.

                                                                как это через MySQL реализовать? через php механизмы?
                                                                зачем усложнять если эффективности от этого нет.
                                                                  +1
                                                                  если имеем 300 файлов, обновляющихся раз в неделю, городить что то смысла нет, но например у нас в проекте каждый день добавляется несколько тысяч файлов и каждую ночь происходит чистка и так же несколько тысяч удаляется.
                                                                  +1
                                                                  Считаю, что однозначно файл не должен быть в БД. Файл в БД — это лишний запрос к бекенду, потом лиший коннект к БД-серверу, который сам по себе не бесплатен, а если БД-сервер находится на отдельном сервере, то коннект будет еще более дорогим. Причем сам запрос файла будет подразумеват парсинг запроса мускулем и передача результата через сокеты. Далее, бекапить БД с гигобайтными файлами будет, наверное, сложнее.

                                                                  Мое мнение — если можно обойтись без обращения к БД (использовать expires, memcached и т.п.), то нужно обходиться.
                                                                    +1
                                                                    цитата из топика
                                                                    «Таким образом к данным, сохраненным в такой таблице можно обращаться как к простым файлам»
                                                                    Т.е. мы как раз и избегаем обраащений к БД при отдаче файлов. Но при этом сохраняем удобства управления файлами при помощи средств БД.
                                                                    +2
                                                                    на вот этой www.blobstreaming.org/ странице был найден вот этот www.blobstreaming.org/ storage-engine для mysql, который как раз и можно использовать для хранения и раздачи файлов.
                                                                      +2
                                                                      кстати, там же есть RitmarkFS — позволяет использовать MySQL для манипуляций с файловой системой
                                                                        +1
                                                                        Спасибо за ссылку
                                                                        0
                                                                        извините, первая ссылка неправильная, должно быть dev.mysql.com/doc/refman/5.1/en/storage-engines-other.html
                                                                        –2
                                                                        Оужас, этоже BLOB!
                                                                        Как страшно жить…
                                                                          –5
                                                                          Парсер — лох, тег irony не понимает.
                                                                          0
                                                                          Где-то в мануале mysql натыкался на совет: не используйте BLOB для хранения файлов :)
                                                                            +1
                                                                            Мне кажется, вы решаете разные задачи: «правильно» хранить файлы(картинки) и быстро отдавать.
                                                                            Правильно хранить в вашем случае, чтобы не было проблем с целостностью, это в БД.
                                                                            Быстро отдавать — прокси в режиме кеширования, например, Squid, ну и есть много других вариантов. Это конечно, если у вас большая нагрузка.(Иначе тут вообще нет проблемы)
                                                                            Научите сквид кешировать на диск, если памяти в серверах мало :)
                                                                            Да тут, конечно, есть проблема со временем обновления кеша. Но тут можно эксперементировать с заголовками, например отдавать из базы(приложения) не картинку, а только признак менялась она или нет, на основании заголовка прокси либо перезапросит ее, либо отдаст из кеша.
                                                                            P.S. Могу соврать, но вроде бы фликр большинство популярных картинок держит в кеширующих проксях причем в памяти.
                                                                              +1
                                                                              не совсем в тему, а в качестве факультативного материала ;)

                                                                              в SQL Server 2008 реализована технология Filestream как раз для решения подобных задача.

                                                                              msdn.microsoft.com/en-us/library/cc949109.aspx

                                                                              думаю, что скоро и в mysql сделают нечто подобное.

                                                                              Only users with full accounts can post comments. Log in, please.