添加虚拟列

ALTER TABLE erp_new_source_reports add client_name VARCHAR(80)  generated always as (clients->"$.name"); ALTER TABLE erp_new_source_reports add client_type VARCHAR(20)  generated always as (clients->"$.type"); ALTER TABLE erp_new_source_reports add client_relation VARCHAR(80)  generated always as (clients->"$.relation"); ALTER TABLE erp_new_source_reports add status_report_check  VARCHAR(80)  generated always as (status->"$.report_check");  解释：       erp_new_source_reports 表      status_report_check  虚拟列名字              status->"$.report_check（status是json字段，report_check是字段里面的数据

MySQL 5.7.7 labs版本开始InnoDB存储引擎已经原生支持JSON格式，该格式不是简单的BLOB类似的替换。原生的JSON格式支持有以下的优势![]

JSON数据有效性检查：BLOB类型无法在数据库层做这样的约束性检查查询性能的提升：查询不需要遍历所有字符串才能找到数据支持索引：通过虚拟列的功能可以对JSON中的部分数据进行索引

例如创建数据库：

CREATE TABLE `erp_new_source_reports` (

id int(10) unsigned NOT NULL AUTO_INCREMENT,

agent_id int(11) DEFAULT NULL COMMENT ,from varchar(191) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'self' COMMENT ,company_id int(11) NOT NULL COMMENT ,company_name varchar(191) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT ,client_id int(11) DEFAULT NULL COMMENT ,community_id int(11) NOT NULL COMMENT ,status json NOT NULL COMMENT '状态',is_invalid tinyint(4) NOT NULL DEFAULT '0',created_at timestamp NULL DEFAULT NULL,company_id int(11) NOT NULL COMMENT '公司ID',deleted_at timestamp NULL DEFAULT NULL,clients json DEFAULT NULL,client_mobile varchar(128) COLLATE utf8_unicode_ci GENERATED ALWAYS AS (json_extract(clients,'$.mobile')) VIRTUAL,status_report_check varchar(80) COLLATE utf8_unicode_ci GENERATED ALWAYS AS (json_extract(status,'$.report_check')) VIRTUAL,PRIMARY KEY (id),KEY idx_reports_sup (deleted_at,is_invalid,from,client_mobile,company_id,created_at) USING BTREE,KEY reports_community_id_index1 (is_invalid,from,status_report_check,community_id,company_id,created_at) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=19126 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

查询语句：

SELECT COUNT(`id`) AS aggregateFROM `erp_new_source_reports`WHERE (`from` = 'self')    AND (`created_at` >= '2019-04-07 00:00:00'        AND `created_at` <= '2019-04-08 23:59:59')    AND (`status` -> '$."report_check"' = 'verify')    AND (`is_invalid` = 0)    AND `erp_new_source_reports`.`deleted_at` IS NULL    AND `erp_new_source_reports`.`company_id` = 1

初期是直接使用json直接查询的 查询速度：1.8s

执行计划：

索引数据：

重点：

添加虚拟列 并添加进索引

ALTER TABLE erp_new_source_reports add status_report_check  VARCHAR(80)  generated always as (status->"$.report_check"); ##### 添加进索引

修改sql为

SELECT COUNT(`id`) AS aggregateFROM `erp_new_source_reports`WHERE (`from` = 'self')    AND (`created_at` >= '2019-04-07 00:00:00'        AND `created_at` <= '2019-04-08 23:59:59')    AND status_report_check ='verify'    AND (`is_invalid` = 0)    AND `erp_new_source_reports`.`deleted_at` IS NULL    AND `erp_new_source_reports`.`company_id` = 1;

再次执行 执行计划

查询速度