MySQL JSON数据类型是否不利于数据检索的性能? [英] Is the MySQL JSON data type bad for performance for data retrieval?

问题描述

比方说，对于媒体表，我有一个称为custom_properties的MySQL JSON数据类型:

Let's say I have a MySQL JSON data type called custom_properties for a media table:

存储在custom_properties列中的json数据的示例可能是:

An example of the json data stored in the custom_properties column could be:

{
 "company_id": 1, 
 "uploaded_by": "Name", 
 "document_type": "Policy", 
 "policy_signed_date": "04/04/2018"
}

在我的PHP Laravel应用中，我将执行以下操作:

In my PHP Laravel app I would do something like this:

$media = Media::where('custom_properties->company_id', Auth::user()->company_id)->orderBy('created_at', 'DESC')->get();

这将获取属于公司1的所有媒体项目.

This would fetch all media items belonging to company 1.

我的问题是，可以说我们有 100万个媒体记录，从性能方面来说，这是获取记录的一种不好的方法吗?谁能阐明MySQL如何索引JSON数据类型? 如果我们联接单独的表并为列建立索引，性能是否会明显提高?我想知道实际的性能差异是什么.

My question is that lets say we have 1 million media records, would this be a bad way to fetch records in terms of performance? Can anyone shed some light on how MySQL indexes JSON data types? Is the performance significantly better if we joined separate tables and index the columns instead? I'd like to know what the actual performance difference would be.

从 MySQL官方文档:

存储在JSON列中的JSON文档将转换为内部文档 允许快速读取文档元素的格式.当...的时候 服务器稍后必须读取以这种二进制格式存储的JSON值， 不需要从文本表示形式解析值.二进制格式 的结构使服务器能够查找子对象或嵌套 通过键或数组索引直接获取值，而无需读取所有值 在文档中它们之前或之后.

JSON documents stored in JSON columns are converted to an internal format that permits quick read access to document elements. When the server later must read a JSON value stored in this binary format, the value need not be parsed from a text representation. The binary format is structured to enable the server to look up subobjects or nested values directly by key or array index without reading all values before or after them in the document.

推荐答案

当他们说快速读取访问权限"时，它们的意思是比将JSON存储在TEXT列中更好."

When they say "quick read access" they mean "better than if you stored JSON in a TEXT column."

与索引查找相比，它的性能仍然很差.

It's still bad performance compared to an indexed lookup.

使用JSON_EXTRACT()或->运算符与在MySQL中搜索任何其他表达式相同，因为它会导致查询进行表扫描.

Using JSON_EXTRACT() or the -> operator is the same as searching on any other expression in MySQL, in that it causes the query to do a table-scan.

如果要获得更好的性能，则必须在要搜索的字段上创建索引.这需要定义一个生成的列在建立索引之前.

If you want better performance, you must create an index on the field you search for. That requires defining a generated column before you can make an index.

请参见>://://mysqlserverteam.com/indexing-json-documents -via-virtual-columns/

这篇关于MySQL JSON数据类型是否不利于数据检索的性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！