I have a number of fields that are applicable to some rows but not others. Instead of making individual fields and making them null for particular rows, I am considering making a JSON field and storing the data in there.
Would there be a performance hit if I wanted to search this data held in JSON? Would there be any other pitfuls I should watch out for or should I just make fields for every piece of data and make them nullable?
1条答案
按热度按时间mqkwyuun1#
You can use expression indexes to extract values from a JSON document, and then the search will be as optimized as if the column were a normal column.
Here's an example:
If you use the exact same expression in your search, then the optimizer will use the index:
The downside of this is that you must hard-code the specific field you want in the index definition.
Also using expression indexes in this way requires MySQL 8.0.
Not all types of JSON searches can be indexed this way.
I've seen a lot of JSON questions on Stack Overflow, and I have to comment that searching JSON is seldom a good idea. It's harder to write the queries, it's harder to optimize the queries (and some cannot be optimized), and harder for some developers to read or debug.
Besides performance, JSON takes a lot more space (200-300% in my tests) than normal columns to store equivalent data.
You might like to read my presentation How to Use JSON in MySQL Wrong , or my past answers on Stack Overflow regarding MySQL and JSON . Prepare yourself for some of the crazy ways many people try to use JSON. It's almost always simpler to code and easier to optimize if one uses normal rows and columns.