mysql udf json_extract в предложении where - как повысить производительность

Как я могу эффективно искать данные json в базе данных mysql?

Я установил udf extract_json с labs.mysql.com и поигрался с тестовой таблицей с 2.750.000 записями.

CREATE TABLE `testdb`.`JSON_TEST_TABLE` (
   `AUTO_ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
   `OP_ID` INT NULL,
   `JSON` LONGTEXT NULL,
PRIMARY KEY (`AUTO_ID`)) $$

Пример поля JSON будет выглядеть так:

{"ts": "2014-10-30 15:08:56 (9400.223725848107) ", "operation": "1846922"}

Я обнаружил, что включение json_extract в оператор select практически не влияет на производительность. Т.е. следующие селекты (почти) имеют одинаковую производительность:

SELECT * FROM JSON_TEST_TABLE where OP_ID=2000000 LIMIT 10;

SELECT OP_ID, json_extract(JSON, "ts") ts, json_extract(JSON, "operation") operation FROM JSON_TEST_TABLE where OP_ID=2000000 LIMIT 10;

Однако, как только я помещаю выражение json_extract в предложение where, время выполнения увеличивается в 10 или более раз (я перешел с 2,5 до 30 секунд):

SELECT OP_ID, json_extract(JSON, "ts") ts, json_extract(JSON, "operation") operation FROM JSON_TEST_TABLE where json_extract(JSON, "operation")=2000000 LIMIT 10;

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


mysql json where-clause database-performance mysql-udf
person edr    schedule 31.10.2014    source источник
comment
Я думаю, если вы выполните EXPLAIN в своем запросе, вы увидите, что MySQL выполняет полное сканирование таблицы просто потому, что ваш запрос относится к термину, который не индексируется.   -  person user4556269    schedule 11.02.2015

Ответы (2)


arrow_upward
2
arrow_downward

Фактически во время исполнения

SELECT * FROM JSON_TEST_TABLE where OP_ID=2000000 LIMIT 10;

json_extract () будет выполняться не более 10 раз.

Во время этого

SELECT OP_ID, json_extract(JSON, "ts") ts, json_extract(JSON, "operation") operation FROM JSON_TEST_TABLE where json_extract(JSON, "operation")=2000000 LIMIT 10;

json_extract () будет выполняться для каждой строки, и результат будет ограничен 10 записями, отсюда и потеря скорости. Индексирование тоже не поможет, поскольку время обработки уходит скорее на внешний код, чем на MySQL. Imho, лучшим вариантом в этом случае будет оптимизированный UDF.

person Radacina    schedule 28.06.2017
comment
Спасибо за разъяснения, но ... этому вопросу почти 3 года! ;-) Я закинул данные в экземпляр эластичного поиска ... Проблема решена. - person edr; 30.06.2017

arrow_upward
1
arrow_downward

Вы можете попробовать следующее: http://www.percona.com/blog/2015/02/17/indexing-json-documents-for-efficient-mysql-queries-over-json-data/

Материализованные представления Flexviews для MySQL используются для извлечения данных из JSON с помощью JSON_EXTRACT в другую таблицу, которая может быть проиндексирована.

person Justin Swanhart    schedule 02.03.2015