起因是我有一张表，里面有json字段后，而当mysql表中有200w数据的时候，走非索引查询性能变得非常糟糕需要3到5s。

先说建表建议

对于大宽主表的建表的建议是，拆成2个表，把需要用来查询的字段放到主表，其他的字段全部放在扩展表。varchar类型的字段，长度尽可能短

一、JSON 在硬盘中的存储原理

MySQL采用二进制格式 存储的 JSON 值，在磁盘存的是doc对象 ，内含type & value。

doc ::= type value
type ::=
  0x00 |       // small JSON object
  0x01 |       // large JSON object
  0x02 |       // small JSON array
  0x03 |       // large JSON array
  0x04 |       // literal (true/false/null)
  0x05 |       // int16
  0x06 |       // uint16
  0x07 |       // int32
  0x08 |       // uint32
  0x09 |       // int64
  0x0a |       // uint64
  0x0b |       // double
  0x0c |       // utf8mb4 string
  0x0f         // custom data (any MySQL data type)
value ::=
  object  |
  array   |
  literal |
  number  |
  string  |
  custom-data

  • MySQL对JSON对象存储是分段的，存储的最前面为存放当前对象的元素个数，以及整体占的大小

  • type主要是标识类型（大json对象、小json对象、大json数组、小json数组、literal、int16、uint16、int32、uint32、int64、uint64、double、string、custom自定义类型）；

  • value包含object、array、literal、number、string、custom-data（与type类型对应）；

  • 当需要读取JSON值的时候，二进制格式的结构使服务器能够直接通过键或数组索引查找子对象或嵌套值，而无需读取文档中它们之前或之后的所有值。

  • 当需要写JSON值的时候，从二进制形式转换到内存中的结构化DOM，并使用JSON值的递归树表示与解析树紧密对应；

二、JSON在内存中的占据空间原理

对于varchar(255) 类型的字段，硬盘上是按照真实空间存储，而加载到内存中后，内存中的长度是varchar定义的长度255存储

JSON在内存中占用的空间资料没查到，但是应该是根据实际空间占用，因为json中存了实际长度

三、JSON类型最大长度和溢出页的概念

JSON最大存储长度为4G，但是实际能存多少还取决于mysql设置的一次更新最大包的大小（默认1M）,

思考下Innodb聚簇索引 的特征， 会建立一个主键索引并把整行数据放到一起。那么如果有一个字段是JSON类型或者Text或者varchar（1000）这种长字段的存在，是否会应为一行数据太大，mysql一页16K会不会放不下？

即使放下了 走主键索引是否会太慢？

先说结论，

第一、mysql一页16K至少可以放了两条数据

第二、mysql有一个行溢出的概念， 5.7之后的默认行格式为dynamic，特点是对于VARCHAR(M)、Text、JSON类型的数据，只在聚簇索引行存放真实数据的地址。而真实的数据放到溢出页里面。这样就能保证16K一页能尽可能密集，进而提升索引查询效率

看到这应该能理解为何用非索引查询很慢，原因是要跳转寻找真实数据

四、JSON索引

MySQL 5.7 针对JSON的索引做了优化，具体方式就是通过生成列来实现JSON某个字段的索引。通俗的来说就是针对JSON指定的列抽取出来，通过冗余该字段的方式来实现索引

目前支持两种生成列形式，即Virtual Generated Column （虚拟生成列）和Stored Generated Column （存储生成列），支持在生成列上定义二级索引（不能与普通列定义联合索引），仅支持本表的非生成列上定义生成列。

  • Virtual Generated Column不会将这一生成列的数据持久化到磁盘上（仅将虚拟列的元数据信息存在于相关系统表中），不支持针对虚拟列进行Update & Insert 的操作。在对应普通列Insert和Update操作时会消耗额外的写负载，因为更新虚拟生成列索引时需要将衍生列值计算出来，并写到索引里；这样就避免了每次读取数据行时都需要进行一次衍生计算。

  • Stored Generated Column 会将数据持久化到磁盘上，在存储生成列上定义索引其实和普通列上定义索引无区别，性能上也不如虚拟索引，会导致聚簇索引变得更大更占空间。

-- 定义虚拟生成列
ALTER TABLE `user` ADD COLUMN `v_sign_time` BIGINT ( 20 ) 
GENERATED ALWAYS AS ( attachment -> '$.sign_time' ) Virtual NULL AFTER attachment;
-- 定义索引
ALTER TABLE `user` ADD INDEX `idx_sign_time` (`v_sign_time`);


来源：https://zhuanlan.zhihu.com/p/666888332