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

Question

Как я могу эффективно искать данные 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 * 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? Или мое заявление о выборе неэффективно написано?

Answer 1

На самом деле во время исполнения

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. Имхо, лучшая ставка в этом случае - оптимизированный UDF.

Answer 2

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

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

Answer 3

Я думаю, что если вы сделаете EXPLAIN для вашего запроса, вы увидите, что MySQL выполняет полное сканирование таблицы просто потому, что ваш запрос выполняется по термину, который не проиндексирован.