Как исправить долгое выполнение запросов на большой таблице?

Question

[Kumatoz48]

Есть сервер MySQL 8 версии. В простой БД два поля - id и data. У поля data тип JSON. В нем хранятся данные, получаемые из нескольких форм. Т. е. данные из формы преобразуются в JSON и сохраняются в БД.

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

SELECT * FROM table WHERE data = "{'field1':'value', 'field2':'value','field3':'value'.....}"

в случае, когда записей в таблице порядка 10 миллионов время выполнения запроса на локальной машине составляет в районе 30 секунд. Есть ли способы ускорить или посоветуйте варианты, возможно вся логика неправильная и можно сделать более грамотно.

Comments

[Айнур Бирдин]

ALTER TABLE `table` ENGINE=InnoDB;
ALTER TABLE `table` ADD FULLTEXT INDEX `idx_data_fulltext` (`data`);
SELECT * FROM `table` WHERE MATCH (`data`) AGAINST ('value');
Так не сойдет?

[Kumatoz48]

Айнур Бирдин, я правильно понял, что в Вашем примере будет искать только конкретное значение внутри JSON? Задача избежать дублирования данных их формы в БД. Т. е. если в БД уже есть точно такие же данные, как введены в форму, то не создавать новую запись. Поэтому нужно чтобы все значения внутри JSON в БД соответствовали JSON из формы. Если я понял неправильно, поправьте, пожалуйста.

Upd.
Строка
ALTER TABLE `table` ADD FULLTEXT INDEX `idx_data_fulltext` (`data`);
выдает ошибку
#3152 - JSON column 'data' supports indexing only via generated columns on a specified JSON path.

[Rsa97]

Kumatoz48, Для начала, вам придётся гарантировать, что все JSON'ы отформатированы абсолютно одинаково. Вот вам два JSON'а, содержащих одно и то же:

{"field1":"ааа","field2":"ббб"}

{
  "field2": "\u0431\u0431\u0431",
  "field1": "\u0430\u0430\u0430"
}

[Kumatoz48]

Rsa97, это подразумевается по-умолчанию.

Answer 1

[Ипатьев]

возможно вся логика неправильная и можно сделать более грамотно.

Штирлиц шел по Берлину, и не мог понять, что выдавало в нем русского шпиона - то ли рация за спиной, то ли волочащийся сзади парашют, то ли болтающийся на груди ППШ.

когда записей в таблице порядка 10 миллионов время выполнения запроса

ВНЕЗАПНО оказалось, что если использовать микроскоп для забивания гвоздей, то в него становится почему-то плохо видно.

В простой БД два поля - id и data. У поля data тип JSON. В нем хранятся данные, получаемые из нескольких форм.

Но вы же, когда эту, с позволения сказать, "базу данных" проектировали, ведь радовались своему остроумию и хитрости? Зачем проектировать структуру базы данных, делать какие-то таблицы, между таблицами связи. потом сложные запросы писать? Если можно хопа, и в джейсон все кучей навалить!

Ну вот и продолжайте радоваться дальше.

Сама по себе дурацкая проблема проверки уникальности длинного текста решается элементарно, добавляется колонка с md5 от содержимого, и поиск делается по ней.
Но ведь в таблицу эти данные складываются не только чтобы проверять их на уникальность. И собственно какая-то работа с этими данными все равно превратится в боль

Comments

[Kumatoz48]

Очень конструктивно. Не стоило тратить время на написание такого ответа.

[Ипатьев]

А вот на написание вопроса как раз стоило бы потратить время. Потому что его заголовок не имеет никакого отношения к реальной проблеме.

[Kumatoz48]

Ипатьев, за идею с хэшем спасибо.

Answer 2

[Akina]

Фактически выполняется сравнение двух длинных BINARY значений, причём при полнейшем отсутствии какого-никакого индекса. Чё б ему не тормозить?

В качестве решения предлагаю изменить тип поля на TEXT и добавить CHECK, а также индекс по значению.

CREATE TABLE test (
    id INT PRIMARY KEY,
    data TEXT CHECK (JSON_VALID(data)), 
    INDEX idx_data (data(100))   -- подогнать до разумного
);

DEMO (см. время выполнения запросов).

Answer 3

[Adamos]

Только вчера чистил битриксовскую b_form_result_answer, где за пять лет набежало 10 миллионов записей.
Как это сделано в Битриксе:
- таблица result, где пишется, кто заполнил форму, и ее идентификатор
- таблица списка полей формы по ее идентификатору
- таблица заполненных полей с идентификатором поля и ответа
Да, тоже неторопливо, когда нужно поискать что-то, потому что постоянно приходится джойнить.
Но уж не JSON.

А задача повторов, действительно, решается просто хэшем, о чем вам сразу специалист и сказал.

Comments

[Ипатьев]

Справедливости ради, про уникальность я дописал не сразу, а позже, когда обратил внимание на сам запрос.
Который, как оказалось, никакого отношения к "выполнению запросов MySQL к полю JSON" не имеет.

Answer 4

[sharpsss]

Согласен с Ипатьевым, вам надо сносить JSON и делать таблицы и связи по человечески и всё будет норм

Comments

[Kumatoz48]

В формах разное число полей. Вы предлагаете делать для каждого поля формы свое поле в БД? Т. е. сделать в таблице поля field1, field2, field3...? А если появится новая форма - нужно будет в БД добавлять поля? Я не прошу готовое решение, я прошу направление, в котором нужно копать, чтобы эту задачу решить.

[sharpsss]

Kumatoz48, у каждого поля разные типы данных или как? Если да то под каждый тип данных сделать свои поля и добавлять в них инфу, а инфа ещё на формы делится должна ? Я без понятия зачем вам это, но если так нужно можете сделать ещё поле id формы, то есть айди формы и данные, если же хотите именно с JSON что-то доработать то делайте более точную выборку , это чуть улучшит ситуацию, погугли json_table / json_extract

[Сергей delphinpro]

Kumatoz48,

В формах разное число полей.

ну и что?

forms
id | name

fields
id | form_id | name | value

вот вам любое количество полей в любой форме