mysql udf json_extract в клауза where - как да подобрим производителността

Как мога ефективно да търся json данни в mysql база данни?

Инсталирах extract_json udf от 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 полетата? Или моята команда за избор е написана неефективно?


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. Имхо, най-добрият залог в този случай би бил оптимизиран 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