MJ Convento

/

MySQL 5.7 JSON columns, and the regrets that followed

1,887 words, about 8 min read

The query returned 41 rows in 8.4 seconds. It was the dullest possible support request: show me everything one staff account touched on the internal admin since the first of April. Forty one rows out of 2.4 million, and the server read all 2.4 million to find them, because the value I was filtering on did not live in a column. It lived inside a JSON document, and I am the one who put it there.

That was three weeks ago. The fix took an afternoon and the lesson took longer, so this is the honest version: what the MySQL 5.7 JSON type is good for, what it cost us, and the rule I now apply before anything goes into a document.

Why we used the JSON type in the first place

The audit table behind the bank’s back office records what changed, who changed it, and the request that caused it. The shape of that last part varies per action, which is the classic argument for a document. In March last year we were on MySQL 5.7.11 and the native type was available, so we took it.

The manual’s pitch is two things, and both are real. Documents in a JSON column are validated on the way in, so invalid text is rejected with an error rather than stored. And they are converted to an internal binary format that lets the server look up a subobject or an array element directly by key or index, without parsing a text representation and without reading the values before or after it in the document. That is better than the TEXT column full of json_encode() output we had in the previous system, where every read was a full decode in PHP.

The type arrived in 5.7.8, the release candidate dated 3 August 2015, and 5.7 went General Availability with 5.7.9 on 21 October 2015. The -> shorthand for JSON_EXTRACT() came in 5.7.9, and the unquoting ->> operator, which is JSON_UNQUOTE(JSON_EXTRACT()) in one token, came in 5.7.13. Those dates matter if you are reading old blog posts: a lot of the JSON advice written in 2015 predates the operator that makes the feature pleasant to use.

Storage is the part I skimmed. The space a document needs is roughly what LONGBLOB or LONGTEXT would need, and the size of any single document is capped by max_allowed_packet. Ours is 16M. Nothing in the schema says a payload should be under a kilobyte, and I will come back to that.

The arithmetic of a full scan

Here is the query, near enough:

SELECT id, created_at, action
FROM audit_entry
WHERE payload->>'$.actor.staffId' = 'S-4471'
  AND created_at >= '2017-04-01'
ORDER BY created_at DESC;

And here is what the planner said about it:

type: ALL
possible_keys: idx_audit_created_at
          key: NULL
         rows: 2385104
        Extra: Using where; Using filesort

The date index was not chosen. The range covers roughly a sixth of the table, and at that selectivity the optimizer decided a straight scan beat 400,000 lookups into the clustered index, which is a defensible guess. Fine. The real problem is the other predicate. JSON columns are not indexed directly, in the same way that other binary types are not, so there is no structure for the server to seek into. It reads every row, evaluates the extraction, compares, discards.

The table is 2.4 million rows with an average document of about 1.6 KB, a bit under 4 GB of data on a box with an 8 GB buffer pool. So the scan is partly disk. 8.4 seconds cold, 3.1 seconds with the table warm, which works out to something like 1.3 microseconds per row for the extraction and comparison. That number is not bad. It is just multiplied by 2.4 million, and it grows every day, and there were four screens in the admin doing the same shape of lookup.

The fix, and the two details the manual buries

The documented workaround is a generated column holding the extracted scalar, with an index on that. The reference manual spells it out, including the EXPLAIN before and after. For an existing table it is two statements, not one:

ALTER TABLE audit_entry
  ADD COLUMN actor_staff_id VARCHAR(16)
    AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.actor.staffId'))) VIRTUAL,
  ALGORITHM=INPLACE, LOCK=NONE;

CREATE INDEX idx_audit_actor_staff ON audit_entry (actor_staff_id);

Two statements because adding a VIRTUAL column is an in place operation on a non-partitioned table, but the online DDL table is explicit that it cannot be combined with other ALTER TABLE actions. Virtual and not STORED because adding a stored generated column is not in place at all: the server has to evaluate the expression, so it copies the table and blocks writes while it does. Adding a secondary index on a virtual column is in place and permits concurrent DML. The whole thing ran in 41 seconds on a live table.

Same query afterwards: type: ref, 43 estimated rows, 0.02 seconds. That is the part everyone writes about. Two details cost me the rest of the afternoon.

The first is JSON_UNQUOTE() inside the column definition. Leave it out and your index contains "S-4471" with the quotes, and your string comparison silently misses. The optimizer page says why: for a direct comparison of a string against the function result the JSON comparator handles quote removal, but that does not happen for index lookups. So the unindexed version of the query works and the indexed version returns nothing, which is the worst way to learn a rule.

The second is how much you can lean on the optimizer recognising expressions. It will use an index on a generated column even when the query never names the column, but only if the expression is identical and has the same result type: 1 + f1 does not match a column defined as f1 + 1. And BETWEEN and IN() are documented as not yet supported for comparisons involving JSON values. Our “show me these five staff accounts” report used IN() and kept scanning while the single account version flew. We stopped relying on the rewrite and changed the reporting queries to name actor_staff_id directly. Uglier in the SQL, obvious in six months.

The index is not free on writes. The manual is upfront that virtual column values are materialised into secondary index records during INSERT and UPDATE, so the work moves rather than disappearing. On our volumes, about 5,000 audit rows a day, it is noise: the insert itself measured 0.9 ms before and 1.0 ms after, sampled over a day. Note also that the generated column page names the real cost of the stored variant: the value ends up kept twice, once in the column and once in the index.

What I regret

Not the type. Four decisions around it.

A JSON column cannot have a default value. That is one line in the manual and it means every insert path has to supply a document. Ours did, except one console command written in December, which left payload as NULL for about 1,900 rows. Extraction from NULL is NULL, so those rows quietly failed every filtered report for five months and nobody noticed, because the reports that would have shown the gap were the reports doing the filtering.

Validation validates syntax, not shape. Documents written before November carry staff_id at the top level; documents after carry actor.staffId. Both are valid JSON, so the column accepted both, and the generated column now reads:

COALESCE(
  JSON_UNQUOTE(JSON_EXTRACT(payload, '$.actor.staffId')),
  JSON_UNQUOTE(JSON_EXTRACT(payload, '$.staff_id'))
)

Which works, and which no query will ever match by expression, so naming the column stops being a style preference and becomes the only option. A column named actor_staff_id VARCHAR(16) could not have drifted like that, because the rename would have been a migration somebody reviewed.

Then the collation. Strings in a JSON context are handled as utf8mb4 with the utf8mb4_bin collation, and utf8mb4_bin is case sensitive, while the rest of our schema is utf8mb4_unicode_ci. A support lookup by email address came back empty because the address had been typed with a capital letter. We added an explicit COLLATE utf8mb4_unicode_ci to that generated column and moved on, but for two days the answer to “is this person in the audit log” was wrong in a way that looked like the audit log was incomplete.

And the size cap that is not really a cap. One import dumped an entire request body into a payload: 6.2 MB, well under our max_allowed_packet, accepted without a murmur. The endpoint that returns the audit feed for a single record has a 30 second timeout, and that one row was enough to spend all of it.

The rule I use now

If a value appears in a WHERE, an ORDER BY, a GROUP BY, a join, or anything finance reads, it is a column. It gets a type, a NOT NULL where it can, and an index if it is filtered. JSON is for the tail: the parts that are read only when a human opens one record, that differ per row, and that nothing aggregates.

The version of that rule I would have believed last year is arithmetical. Every key you end up filtering on becomes a generated column plus an index, which is one ALTER, one CREATE INDEX, one extra write cost, and the value stored in two places. The document was never the cheaper design. It was the same work, moved to a Thursday afternoon when a support ticket made it urgent.

Still open: 2.4 million documents with two shapes in them. A console command rewrites them in batches of 5,000 on weekends, and after three weekends it is at roughly 1.6 million. When that finishes I take the COALESCE out. What I have not decided is whether the payload keeps the raw request body at all, or whether the four fields we actually query become real columns and the document goes back to being what it was supposed to be, which is the part nobody looks at.

Sources

  • The JSON Data Type, MySQL 5.7 Reference Manual: validation on insert, the binary storage format, storage roughly equal to LONGBLOB, the max_allowed_packet limit, no default value, and the statement that JSON columns are not indexed directly.
  • Secondary Indexes and Generated Columns: the documented workaround of indexing a generated column that extracts a scalar from the JSON column, with the EXPLAIN output.
  • Functions That Search JSON Values: JSON_EXTRACT(), the -> operator in 5.7.9 and later, and the unquoting ->> operator in 5.7.13 and later.
  • Optimizer Use of Generated Column Indexes: why JSON_UNQUOTE() belongs in the column definition, the requirement that a query expression be identical to the column expression, and BETWEEN and IN() not being supported for JSON comparisons.
  • CREATE TABLE and Generated Columns: VIRTUAL against STORED, the rule that expressions must be deterministic, and the note that stored generated columns keep the value twice.
  • Online DDL Operations: adding a virtual column is in place but cannot be combined with other ALTER TABLE actions, adding a stored one is not in place, and secondary indexes on virtual columns permit concurrent DML.
  • Changes in MySQL 5.7.8 and Changes in MySQL 5.7.9: the JSON type landing in the 5.7.8 release candidate on 3 August 2015, and 5.7.9 on 21 October 2015.