Mysql запрос с SUM(OCTET_LENGTH( LONGBLOB )) очень доло выполняется. Есть способ оптимизации?
Всем привет.
Делаю агрегатный запрос к таблице в которой хранится много разной инфы и файлов (результаты расчётов, чертежи DWG и разные логи). Сама таблица 45ГБ, записей около 1млн. Размер BLOB от 1KB до 20МБ. Любые аргегатные запросы по суммам длин других столбцов отрабатываются вполне себе быстро, но вот определение сумм длин BLOB-полей по всей таблице идёт неприлично долго - 12 минут. Можно ли как-то ускорить расчёт?
Комментарии:
1. У меня сложилось такое впечатление, что при таких агрегатных запросах база перечитывается целиком (но я могу ошибаться).
2. (Статически сделать колонку с длинами не предлагать, нужна приемлемая скорость работы с живыми данными, если такая возможна?)
3. Может это в принципе стандартное поведение MySQL?
У тебя таблица - 45Гб. И ты хочешь мерять длину в charasters (в символах). Насколько тебе сильно важна точность?
Ну даже если ты к примеру промахнулся на 100Мб. Насколько это важно и критично для твоего отчета?
В качестве аналогии. Если ты собрался лететь с Земли на Марс то тебе безразлично какое расстояние в метрах. Никто такую точность не промеряет. Скорее будут сотни километров. Бешеной собаке... как говорицца и 100 верст - не крюк.
mayton2019, Этот вариант я бы согласился принять, если бы точно знал, что он недостижим из-за п.3 (типа - особенность MySQL). Но не хотелось бы видеть ошибку в 100МБ, просто это значение всё-равно ведь надо как-то посчитать? Желательно не просто общую длину (Я могу и по размеру файла понять, сколько эта таблица занимает), а хотелось бы группировку по типам записей (что-то файл логов, что-то файл чертежа).
Так что такой вариант я не отбрасываю, но хотелось бы узнать есть ли вариант без него? По большому счёту я хотел собрать статистику по разным группировкам и был удивлён, что запросы по размерам так долго выполняются.
первое что приходит в голову - вынести размер поля в отдельное поле.
Опять же - mayton2019 верно заметил что прочие метаданные занимают пренебрежимо малый процент, и, не считая структурной разметки, бОльшая часть данных таблице собственно содержимое блобов.
Статически сделать колонку с длинами не предлагать, нужна приемлемая скорость работы с живыми данными
Что за бредятина? Вычисление длины LONGBLOB по сравнению с записью его на диск - плюнуть и растереть, разница по скорости работы будет меньше точности измерения. Так что stored generated column - самое что ни на есть правильное решение.
У меня сложилось такое впечатление, что при таких агрегатных запросах база перечитывается целиком
Правильно сложилось. Ты сам, своими руками, заставляешь сервер перечитывать данные и считать размер. Поинтересуйся, что именно хранится в структуре поля, какая длина - вот именно её и надо было брать, так хотя бы серверу не надо было читать само значение. Впрочем, всё равно придётся жевать всё подряд, пусть и прыжками, но большого профита не жди.
PS. Сервер хранит статистику данных, которую можно запросить из INFORMATION_SCHEMA и PERFORMANCE_SCHEMA. Да, точность до байта не гарантируется, но по скорости разница будет не на один порядок.
Akina, Но ведь когда вы спрашиваете у файловой системы она же не перечитывает файлы целиком? Неужели BLOB сам не знает, какая у него длина? Хотя можно предположить, что функция LENGTH действительно может не знать где взять это число?
насколько его корректно делать именно так - я не знаю.
Вполне корректно. Более того, если получение суммы - основная, а не побочная функция запроса, это поле желательно индексировать (совместно с полями, которые будут использованы в отборе - т.е. покрывающим индексом). Это может дополнительно ускорить выборку, а само поле можно сделать виртуальным, чтобы не кушало диск. Конечно, индекс желательно построить так, чтобы он обслуживал и другие запросы.
mayton2019, ThunderCat, Akina, Дмитрий Спасибо, что обсудили со мной этот вопрос. Я никогда раньше не работал с базами такого размера. Тут два момента:
1. В настройках при памяти в 64ГБ у меня настройка innodb_buffer_pool_size=2.5ГБ, что собственно маловато, я сделал 20 (чтобы другие программы тоже могли работать). Стало ощутимо быстрее, но всё ещё не как как я привык на меньших размерах. Запросы шли уже не 20 минут, а 5.
2. Надо набраться терпения.
В итоге я всё посчитал в этой базе статистику и удалил часть записей, которые оказались уже слишком устаревшими, чтобы статистика по ним имела значение (там были промежуточные логи расчётов, которые разработчикам самих расчётых программ уже никогда не буду интересны, т.к. версии этих программ уже сильно поменялись). Почистил базу и уменьшил её с 55 до 11ГБ. При кэше в 20ГБ теперь всё летает.
Нормальное решение, только надо понять необходимость сделать именно так. Я не очень силён в способах хранения инфы mysql-ем, чтобы считать именно такое решение необходимым. Но вполне возможно, что именно его и применю. Спасибо за ссылку на статью.
Средний
