Что быстрее файл или mysql
Хотите улучшить этот вопрос? Переформулируйте вопрос так, чтобы он был сосредоточен только на одной проблеме.
Закрыт 4 года назад .
Как эффективно поступать,выполнять определённые процедуры на стороне sql либо же на стороне php? Например,эффективнее вытащить значения из базы, а затем применять к ним нужные операции посредством языка, или же эффективнее выполнить различные триггеры и функции на стороне sql, а потом просто вытащить результат?
Что быстрее ?
На этот вопрос нет однозначного ответа. Какие-то задачи быстрее и удобнее решать на уровне БД, какие то на уровне php. Все зависит от того, какие именно манипуляции над данными вы производите. И зачастую сервер БД может вертеться на одном железе, а сервер приложений на другом.
@RuslanSemenov это очень сильное утверждение. С помощью sql можно например решать судоку, но чаще всего это менее удобнее и менее быстро, чем на другом ЯП. Или например задачи с большим количеством вычислений над данным, их бывает быстрее решить на php
Мы сейчас рассуждаем о сферических конях в вакуме. Все зависит от той задачи, которую вы решаете. Где то большая часть нагрузки приходится на сервер БД, где то на сервер приложения. Повторюсь, на Ваш вопрос нет однозначного ответа. Ответ на такой вопрос можно получить только в разрезе конкретной задачи
@RuslanSemenov существуют запросы сложнее, чем select * from table where >. Иногда бывает, что проще вытащить все данные и разобрать на сервере приложений, чем написать 400 строчный запрос заставить БД построить эффективный план этого запроса. Вопрос слишком общий, и на него нельзя отвечать, что sql всегда быстрее. Видел систему, в которой таблички справочники один раз вытаскиваются на сервер приложений, и потом СП для всех сессий использует ранее выгруженные массивы вместо дополнительных запросов в БД по справочникам. И это очень эффективно в конкретной задаче
CRC32
Я решил уменьшить длину индекса. Текст словоформы заменил на его контрольную сумму (crc32).
Поскольку словоформ около 2уъ миллионов, а 2^32 = 4 млрд, мы получаем вероятность коллизии 0.05%. Всего по известным словам около 1.000 коллизий, исключить их можно просто указав все в ассоциативном массиве с их настоящими id:
if(isset($Collisions[$wordStr])) return $Collisions[$wordStr];
Но это не исключает ложноположительного срабатывания, если словоформы нет в базе, то в 1 из 2000 случаев возвращается левый id. Но это критично только для поиску по огромным разнотематическим массивам данных (вроде википедии).
Словарный запас человека крайне редко выше 10.000 слов. Например, у Льва Толстого 20.000. Слов в базе 200.000, если организовать поиск по полному собранию сочинений Толстого, то вероятность того, что из-за коллизии он найдет что-то лишнее, будет 1 к 10 * 2000. Т.е. 1 к 20.000.
Другими словами, вероятность глюков из-за использования crc32 крайне мала. И это с лихвой компенсируется увеличением производительности и уменьшением объема данных.
Однако, это мало помогло. Скорость была около 500 слов в секунду. Я начал смотреть в сторону файлов.
Выводы
Я не призываю заменять БД на файлы всегда и везде. БД имеет намного больше функций. Но есть несколько случаев, когда эта замена будет оправдана. Например, это статические базы (города по IP, морфология). В этом случае переход на файлы, кроме всего прочего, облегчит жизнь, для установки на другой сервер достаточно будет простого копирования файлов.
Основная фишка БД это избавления от конфликтов между потоками. Такое довольно сложно организовать при работе с файлами. БД блокируют чтение, когда идет запись. Но на больших нагрузках это рождает свои проблемы.
Сколько у вас не было серверов и потоков, все они блокируются, когда один из них пишет данные в базу. Здесь все зависит от типа хранилища, некоторые ставят блокировку на уровне строк, но и это не всегда помогает.
Поэтому часто разделяют базы. Одна используется для чтения, вторая для записи. Они синхронизируются время от времени, например ночью. Если, в большинстве запросов чтения у вас выборка происходит оператором “=”, то вы можете многократно увеличить скорость, делая копии таблиц в файлах и используя хеш.
Если тормозит база записи, то, в некоторых случаях, быстрее будет записывать что-то вроде лога с изменениями, а при синхронизации переводить этот лог в нормальную базу. Но такой вариант помогает далеко не всегда.
Нужно средствами php дёргать время от времени информацию из базы, которая в данный момент храниться в файле. Файл обновляется раз в неделю.
Как будет быстрее: Заносить данные из файла в БД и уже дёргать оттуда, хранить данные в xml, хранить данные в csv.
Было бы интересно узнать какой метод более оптимален и почему.
Беглое гугление не дало ничего конкретного.
- Вопрос задан более трёх лет назад
- 4093 просмотра
FanatPHP: скорее наоборот, игнорировать профайлер - не самая здравая (взрослая) мысль. Или бежать от рефакторинга, как от огня является признаком хорошего кодера?
Совет. если не понял моего ответа - почитай ответ Ihor Kalashnikov - там другими словами то же самое написано
FanatPHP: За как раз такого рода высказывания PHP-разработчиков и поливают. А про рефакторинг - это не просто умное слово, вычитанное в книжке, а то, что позволило мне не утонуть в паре проектов. А что касается "Работает - не трожь!" - это наиболее актуально только с очень сложной логикой (например, сложные иерархии на основе деревьев или бизнесс-правило, которое слабо граничит со здравым смыслом)
Кирилл Саксин: подсказка: не только с очень сложной, но и с очень простой. Тыв не умеешь прочесть вопрос глазами автора. Ты думаешь не о нем, а о себе. Это эгоизм. Отвечая человеку на вопрос, надо отвечать ему, а не себе. А ты отвечаешь себе. А это значит, что дзена ты еще не достиг. Совершенствуйся.
FanatPHP: Ошибочка, я был на месте автора, с ровно такой же проблемой, разве что цикл обновления был в 7 раз чаще, тогда и изучил все возможные решения проблемы, которые и перечислил в ответе. Много мелкого неотрефактореного кода, хм, процедурщину не напоминает?
Или по вашему такое:
рефакторить не надо?
Ваша главная проблема, что вы не правильно ставите вопрос.
Смотря что за данные вы хотите хранить:
В каком виде приходят данные?
Структурированные?
Объем?
Смотря что вы с этими данными хотите делать:
Различные выборки?
Десериализовать в объект?
Просто вывести пользователю?
Думаю после ответа на эти вопросы вы поймете что за способ хранения вас устроит.
Способы решения:
1. читаем из файла всегда
2. читаем 1 раз, храним где либо:
2.1. в БД
2.2. в xml или csv
2.4. в json
2.5. в виде пхп кода
Для того чтобы ответить на вопрос, нужно знать следующее:
1. как часто читается файл после обновления, насколько это критично чтобы работало быстро ?
2. Размер файла
3. Как именно читается - нужно читать все строки из него, или всего несколько?
4. Что с файлом дальше делается ? Например если после чтения выводим просто хтмл реестр, то почему бы его сразу не готовить, и не хранить его?
5. Ссколько много времени на разработку.
Простой пример кейса:
система уже работает, файл читается 1-2 раза в неделю.
В данный момент из оригинального файла читается около секунды
Особых лагов нет.
На разботку метода конвертация уйдёт 1-2 дня.
=> Выгоды нет по замене
Другой пример:
1. файл читается каждый раз раз в секунду.
2. из файла нужно 1-10 строк всего, по номеру
3. В файле миллионы строк
=> лучше хранить в базе обычно.
третий пример:
1. файл читается каждый раз раз в секунду.
2. файл нужен всегда весь
3. В файле миллионы строк
=> лучше хранить в пхп коде
Т.е. способы хранения данных таковы:
1. БД
+ быстрая выборка, когда нужно несколько элементов из миллиона
+ можно использвоать в качестве данных для других запросов (джойны и тп)
- временные издержки
2. XML/CSV
+ можно использовать как есть, если нужен этот формат для чего либо другого.
- очень "дорого" парсить файл каджый раз.
Не рекомендую если нет особых причин
3. JSON
+ Относительно быстро
+ удобно хранить
+ на некоторых данных быстрее чем ПХП (что странно)
- надо парсить, опкешеры не помогают
- невозможно брать только часть элементов. Если нужно 10 строк из миллиона, то придётся брать весь миллион в память.
4. PHP
Тут я имею в виду: хранить через var_export и подключать через require/include
Обязательно: чтобы был APC/opcache
+ быстро
+ удобно хранить. обрабатывать. Получаешь сразу же массив
- невозможно брать только часть элементов. Если нужно 10 строк из миллиона, то придётся брать весь миллион в память.
- иногда JSON быстрее
Что вам выбрать лучше - сложно сказать, вы не предоставили достаточно данных.
Я бы взял PHP или БД, в зависимости от ситуации.
Таблица из 100 000 записей
или
100 000 файлов в папке.
выбрать/записать один файл или выбрать одну запись из таблицы.
Как вы считаете, что будет быстрее для чтения/записи ?
- Вопрос задан более трёх лет назад
- 842 просмотра
Простой 4 комментария
Pavel Denisov,
Что забавно, человеку с опытом будет проще протестировать оба вариант под требуетмой нагрузкой на требуемом стеке на реальном железе и займет все не больше рабочего дня
Выбор сильно зависит от того что вы собираетесь делать с этими данными. Если вам нужно (и, главное, будет нужно и в дальнейшем) только читать / писать строго определённые одиночные записи - то можно использовать и файловую систему, хотя хранить все файлы в одной папке - очень плохая идея, нужен sharding чтобы оптимизировать работу (вспоминаем как работает поиск по файлам в каталоге). Кроме того нужно помнить что открытие файла - довольно дорогая операция.
Если же вам сейчас или в дальшейшем потребуются операции затрагивающие множество элементов, поиск в них или, к примеру, агрегация - то ответ однозначный - база данных.
Конечно есть много нюансов которые могут склонить чашу в ту или иную сторону и про которые вы не упомянули. К примеру если у вас файлы размерами в гигабайты и вам нужно выбирать оттуда какие-то мелкие части - то лучше использовать файлы, а не базу данных. Если у вас предполагается доступ к данным с нескольких компьютеров - то однозначно база данных.
В целом ваш запрос сводится к "использовать ли мне программу, специально предназначенную для нужной мне функции или написать свой велосипед". Уверен что в подобной формулировке вам легче будет самому ответить на свой вопрос.
Решение с MySQL будет куда проще и в реализации и в масштабировании.
только хранить или еще производить поиск по данным внутри JSON?
В MySQL есть тип JSON (начиная с 5.7.8). Если поиск по JSON не нужен, то тогда достаточно BLOB.
На моей практике интеграция делала считывание реестров. Однажды стала падать уже на загрузке. Потом оказалось, что в папках было 10-20к файликов. Один заход в директорию занимал 5 минут минимум. Интеграция падала при попытках одновременного обращения к файлам всего нескольких процессов.
Само собой, все придется познавать методом экспериментов, НО.
1. Есть база данных. Таблица простая, но огромная, состоящая из двух полей, ID (int), TEXT (text) с максимальным размером в несколько килобайт. Записей может быть 10-20 миллионов, т.е. не мало.
2. Есть задача поиска по содержимому этого текстового поля.
Как думаете, что сработает быстрее: родной мускульный SELECT FROM where TEXT like '%что ищем%', или быстрее будет PHP-шным скриптом читать все 10 миллионов записей последовательно, на ходу парся какой-нибудь PHP-шной функцией навроде substr_count ?
В данном случае ставлю на БД. Но узнать результат все равно интересно.
Ставлю на эластик.
БД без вариантов, только не родной мускульный where TEXT like %term%, а родной мускульный fulltext search
ставлю на php, в частности 7.3
Кстати, а откуда данные читаем? php скрипт будет читать из mysql, или у него данные уже в памяти?
лайк очень медленный, проиндексируй эту базу сфинксом, на 20 лямах строк поиск будет мгновенным. И еще получишь возможность морфологического поиска.
Мне кажется это очевидно вопрос был в скорости БД vs PHP.
Отвечая на вопрос: PHP в обоих случаях будет читать из мускуля, только в первом варианте where обрабатывается логикой самого мускуля, а во втором варианте - логикой пыха.
лайк очень медленный, проиндексируй эту базу сфинксом, на 20 лямах строк поиск будет мгновенным. И еще получишь возможность морфологического поиска.
За совет спасибо, надо почитать.
Субд уделает пых без вариантов. Чтобы сделать substr_count нужно сперва данные получить пыхом, потом распарсить пыхом в массив. А в mysql еще есть fulltext index и всё такое.
. SELECT id from table where content like '%test123%' .
PHP в обоих случаях будет читать из мускуля, только в первом варианте where обрабатывается логикой самого мускуля, а во втором варианте - логикой пыха.
Тогда логичнее использовать СУБД-шный запрос - будет экономия за счет меньшей передачи данных по соединению между MySQL и PHP.
Но поскольку критерий выборки like '%test123%' - поиск по подстроке в серединке поля - то внутри СУБД-шки по идее будет использоваться последовательное сканирование таблицы. Просто посмотри план выполнения SQL-запроса - и всё станет ясно.
Если реально ты будешь искать слова, а не произвольные подстрочки, то можно задействоавать СУБД-шный полнотекстовый поиск - тогда поиск внутри СУБД-шки будет пошустрее. Но это требует создания соответствующих индексов и использования других SQL-конструкций.
Омонимия и нормализация
- Дели. “Дели — столица Индии”.
- Делить. “Смело дели на два”.
- Деть. “Куда вы дели мои носки?”
Такая многозначность нам сильно мешает. Намного было удобнее, если бы каждая слово-строка имела бы не более одной интерпретации. В этом случае кода у нас было бы намного меньше и возросла бы скорость работы.
Но научить разрешать омонимию машину сложная задача. Она требует много времени на реализацию, а также может негативно сказаться на производительности.
Решение этой проблемы я скопировал у Яндекс.Директ. Он объединяет слова имеющие омонимию в одно. Т.е. они получают один ID. Иногда это приводит к глюкам, когда по запросу находиться не искомое слово, а склеенное с ним, но это происходит довольно редко. В противном случае Директ не использовал бы подобное решение.
База выглядит так: в ней есть наборы окончаний, приставки и слова. Слово имеет псевдооснову (грубо говоря корень слова), номер приставки (если она есть), номер набора окончаний. Чтобы сгенерировать все варианты слова нужно в цикле перебрать окончания, и спереди к каждому из них прибавить приставку и псевдооснову.
Вид базы когда окончания отделены от слов называется сжатым. В этом случае, база весит мало, но поиск по ней крайне медленный. Поэтому словарь нужно “разжать” — записать каждую словоформу отдельно. Но словоформ в базе несколько миллионов, поэтому на это требуется несколько десятков мегабайт. Первый делом я посмотрел в сторону баз данных.
1 ответ 1
По собственному опыту - в большинстве случаев обработка на стороне БД быстрее по простой причине - для того, чтобы обработать данные на стороне приложения их надо сначало прочитать и обычно это одна из самых "дорогих" операций. Плюс при обработке данных на клиенте надо прибавить время получения/записи из/в БД.
Но иногда надо решать задачи, для которых пока не существует аппарата на стороне БД. Хороший пример задачи из разряда машинного обучения и искусственного интеллекта. Можно конечно попробовать реализовать весь функционал на SQL, но это никому ненужное "пере-изобретение колеса". В таких случаях БД используются просто как хранилища данных.
Т.е., в общем случае, если одну задачу можно эффективно решить и на стороне БД и на стороне приложения, то я бы выбрал решение на стороне БД, чтобы не гонять данные туда-сюда. Иначе ответ очевиден - на той стороне, которая больше для этого подходит.
PS бывают политические/стратегические решения когда по определенному ряду причин решают использовать БД только как хранилище - это тоже нормальная практика.
Выбор - куда ставить более мощное железо (для БД или для сервера приложений) будет очень сильно зависеть от того как используются данные сервера. Очень часто IO (быстрый storage system) и объем RAM для сервера БД важнее чем мощность процессоров. Конкретно - надо смотреть где узкое место.
Первоначальная версия была на MySQL, но перевод ее на файлы мне удалось добиться стократного увеличения производительности. О том когда и почему файлы быстрее MySQL я и расскажу в статье.
Но для начала, чтобы не томить тех кому интересна моя реализация. Вот ссылка на нее в GitHub. Там исходник для PHP. Для других языков написать аналог довольно легко, будет не более 100 строк кода. Основная проблема конвертировать базу в приемлемый для поиска вид мною уже выполнена. Вам просто нужно портировать поиск на свой язык.
MySQL
Я записал все словоформы в одну таблицу с двумя столбцами, текст и id слова. Добавил индекс по тексту словоформы, но скорость оказалась крайне низкой. Запуск процедур оптимизации таблицы не помог.
Чтобы разобраться в чем-же причина, я стал искать сразу по 10 слов, через IN. Скорость увеличилась незначительно, следовательно,. дело было не в парсинге SQL и NO-SQL бы мне не помог.
Игры с типом индекса и хранилищем тоже почти ничего не дали.
Также я изучил несколько тестов производительности сравнивающие MySQL с другими движками, например, с Mongo. Однако, заметного превосходства скорости чтения перед MySQL в них не демонстрирует ни один движок.
Файлы
Когда все способы оптимизации MySQL были испробованы, я решил записать морфологический словарь в файл и искать по нему бинарным поиском. Результаты превзошли мои ожидания. Скорость возросла где-то втрое.
Для меня эта цифра была неожиданной, поскольку я писал на PHP, а скриптовые языки несут свои накладные расходы (вроде маршалинга). При этом MySQL и моя реализация использует аналогичные алгоритмы — бинарные деревья и быстрый поиск.
В обоих случаях сложность log(n). Т.е. происходит log2(2.000.000) ~ 21 операция считывания из файла. PHP должно 21 раз строку из сишного вида преобразовать в свой внутренний. В MySQL подобного не происходит. В теории, PHP должен заметно проигрывать в скорость.
Чтобы разобраться в чем-же дело я добавил блокирование файлов на чтение. Сама блокировка здесь не нужна, ведь чтение с чтением не конфликтует, да и тестировал я в один поток. Были интересны накладные расходы. Скорость упала раза в 1.5. Вероятно, что одна из основных причин в том, что MySQL предотвращает конфликты записи в несколько потоков, а в нашей задаче это не нужно.
Но это не объясняет и половины разницы в скорости. Откуда взялись остальные тормоза, мне не понятно. Хотя деревья и драйвер могут нести свои накладные расходы, но не такие большие. Интересно ваше мнение в комментариях.
Задача
Все кто хоть немного в теме контекстной рекламы используют Яндекс.WordStat. Это сервис для подбора ключевых слов. Однако, он содержит много шума.
На скриншоте вычеркнуты все запросы, которые дублируют прошлые. Зеленым выделена разница между основным запросом и уточнением. Т.е. все эти 60 слов со скриншота можно записать 6тью:
Т.е. 90% информации в WordStat — шум. Но основная проблема в том, что нужно копировать данные, вставлять их в блокнот и редактировать. Для пользователей было удобнее использовать галочки. Вот, например, сравнение WordStat тем, что получилось у меня в итоге:
На скриншоте Яндекс.Вордстат и HTraffic. Данные одни, разная визуализация.
В общем, это позволяет в 10-20 раз ускорить подбор ключевых слов. Однако, все это требует морфологии. При этом вордстат по API может выдать до 200 запросов, в каждом из них в среднем 5 слов. Это уже 1000 слов. К тому-же, вместе с запросом мы пробиваем еще и его синонимы, их может быть до 10ти. Т.е. 10.000 слов вполне реальная ситуация.
Конечно можно использовать кеширование и запоминать ранее найденные словоформы. Тогда число поисков сократиться раз в 10. Но это не полностью избавляет от проблем со скоростью.
Хеширование
Деревья не оптимальная структура для поиска данных. Хеш намного быстрее. У Хеша константное время. В нашем случае вместо 21 операции мы потратим 1-2. При этом чтение будет происходить последовательно, т.е. лучше будет работать кеш диска и считывающей головке не придется туда-сюда прыгать.
Хеш используется, например, в PHP для реализации асоциативных массивов. В БД используются бинарные деревья, поскольку они позволяют организовать сортировку и операторы больше и меньше. Но когда нужно выбрать элементы равные какому-то значению, то здесь деревья проигрывают хешу на больших таблицах в десятки раз.
Таким образом мне удалось ускорить библиотеку в 10-15 раз. Но на этом я не остановился, я начал хранить CRC и id слова в разных файлах. Таким образом чтение стало полностью последовательным. Также я стал сохранять файловый указатель открытым между несколькими запусками поиска.
Итоговый выигрыш в производительности перед MySQL составил 80-100 раз. Разброс оценки обусловлен тем, что я тестировал на разном соотношении ошибок и правильных словоформ.
Читайте также: