mysql udf json_extract在where子句中-如何提高性能 [英] mysql udf json_extract in where clause - how to improve performance

问题描述

如何有效地在mysql数据库中搜索json数据?

How can I efficiently search json data in a mysql database?

我从labs.mysql.com安装了extract_json udf，并使用了具有2.750.000条目的测试表.

I installed the extract_json udf from labs.mysql.com and played around with a test table with 2.750.000 entries.

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字段如下所示:

An example JSON field would look like so:

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

我发现将json_extract放入选择语句实际上对性能没有影响. IE.以下选择(几乎)具有相同的性能:

I found that putting json_extract into a select statement has virtually no performance impact. I.e. the following selects (almost) have the same performance:

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秒):

However, as soon as I put a json_extract expression into the where clause the execution time increases by a factor of 10 or more (I went from 2,5 to 30 secs):

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语句编写效率低下?

At this point I am thinking that I need to extract all info that I want to search into separate columns at insert time, and that if I really have to search in the json data I need to first narrow down the number of rows to be searched by other criteria, but I would like to make sure that I am not missing anything obvious. E.g. can I somehow index the json fields? Or is my select statement inefficiently written?

推荐答案

实际上是在

SELECT * FROM JSON_TEST_TABLE where OP_ID=2000000 LIMIT 10;

json_extract()将最多执行10次.

json_extract() will be executed at most 10 times.

在此期间

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.

json_extract() will be executed for each row and the result limited to 10 records, hence the speed loss. Indexing won't help either since the processing time is used up rather tby the external code than MySQL's. Imho, the best bet in this case would be an optimized UDF.

这篇关于mysql udf json_extract在where子句中-如何提高性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！