mysql JSON column or nullable fields?

2vuwiymt  于 2022-12-22  发布在  Mysql
关注(0)|答案(1)|浏览(130)

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?

mqkwyuun

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:

mysql> create table mytable (id serial primary key, data json);

mysql> alter table mytable 
  add index ((cast(json_unquote(json_extract(data, '$.x')) as unsigned)));

If you use the exact same expression in your search, then the optimizer will use the index:

mysql> explain select * from mytable 
  where cast(json_unquote(json_extract(data, '$.x')) as unsigned) > 100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mytable
   partitions: NULL
         type: range
possible_keys: functional_index
          key: functional_index
      key_len: 9
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where

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.

相关问题