背景

上文我们简要介绍了一些关于json的查询语句Mysql5.7学习之json类型（一）

我们也观察到了展示结果和查询其实有双引号的区别。

如果只是这样的查询也太小看mysql了。毕竟随着业务的数量增多 数据极速的膨胀 

能否在json列中使用索引呢？？？

问题

explain select  * from user where json_extract(data,'$.uid')='wang';

查看执行计划

type为all啥索引都没有 如果我有100w数据岂不……

As noted elsewhere, JSON columns cannot be indexed directly. To create an index that references such a column indirectly, you can define a generated column that extracts the information that should be indexed, then create an index on the generated column

解析

如果我们常见的查询条件都是某个对应字段 比如$.name

首先我们可以给对应的列加上虚拟列

虚拟列给我们解决了一个困扰许久的问题

很久之前我们就有需要基于数据处理的索引

比如表如下

CREATE TABLE `ontime` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `FlightDate` date DEFAULT NULL,   `Carrier` char(2) DEFAULT NULL,   `OriginAirportID` int(11) DEFAULT NULL,   `OriginCityName` varchar(100) DEFAULT NULL,   `OriginState` char(2) DEFAULT NULL,   `DestAirportID` int(11) DEFAULT NULL,   `DestCityName` varchar(100) DEFAULT NULL,   `DestState` char(2) DEFAULT NULL,   `DepDelayMinutes` int(11) DEFAULT NULL,   `ArrDelayMinutes` int(11) DEFAULT NULL,   `Cancelled` tinyint(4) DEFAULT NULL,   `CancellationCode` char(1) DEFAULT NULL,   `Diverted` tinyint(4) DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `FlightDate` (`FlightDate`) ) ENGINE=InnoDB

需要统计一些信息的时候如下

EXPLAIN SELECT carrier, count(*) FROM ontime_sm         WHERE dayofweek(FlightDate) = 7 group by carrier

   id: 1   select_type: SIMPLE         table: ontime_sm          type: ALL possible_keys: NULL           key: NULL       key_len: NULL           ref: NULL          rows: 151253427         Extra: Using where; Using temporary; Using filesort Results: 32 rows in set (1 min 57.93 sec)

有个简单的方法可以建立触发器 在做插入或者更新的时候直接更新某个字段 直接使用该字段进行查询

CREATE DEFINER = CURRENT_USER TRIGGER ontime_insert BEFORE INSERT ON ontime_sm_triggers FOR EACH ROW SET NEW.Flight_dayofweek = dayofweek(NEW.FlightDate);

哈哈 当有了虚拟列的时候

直接这样

CREATE TABLE `ontime_sm_virtual` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `FlightDate` date DEFAULT NULL,   `Carrier` char(2) DEFAULT NULL,   `OriginAirportID` int(11) DEFAULT NULL,   `OriginCityName` varchar(100) DEFAULT NULL,   `OriginState` char(2) DEFAULT NULL,   `DestAirportID` int(11) DEFAULT NULL,   `DestCityName` varchar(100) DEFAULT NULL,   `DestState` char(2) DEFAULT NULL,   `DepDelayMinutes` int(11) DEFAULT NULL,   `ArrDelayMinutes` int(11) DEFAULT NULL,   `Cancelled` tinyint(4) DEFAULT NULL,   `CancellationCode` char(1) DEFAULT NULL,   `Diverted` tinyint(4) DEFAULT NULL,   `CRSElapsedTime` int(11) DEFAULT NULL,   `ActualElapsedTime` int(11) DEFAULT NULL,   `AirTime` int(11) DEFAULT NULL,   `Flights` int(11) DEFAULT NULL,   `Distance` int(11) DEFAULT NULL,   `Flight_dayofweek` tinyint(4) GENERATED ALWAYS AS (dayofweek(FlightDate)) VIRTUAL,   PRIMARY KEY (`id`),   KEY `Flight_dayofweek` (`Flight_dayofweek`), ) ENGINE=InnoDB

逆天了===》函数列 并且更重要的是支持索引！！！

这样的话我们完全可以

EXPLAIN SELECT carrier, count(*) FROM ontime_sm_virtual  WHERE Flight_dayofweek = 7 group by carrier

*************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: ontime_sm_virtual    partitions: NULL          type: ref possible_keys: Flight_dayofweek           key: Flight_dayofweek       key_len: 2           ref: const          rows: 165409      filtered: 100.00         Extra: Using where; Using temporary; Using filesort

这种结果真真是太棒了！！！

结论

这个和json结合起来使用真是太棒了吧！！！

以刚才的表为例

我们使用虚拟列创建name_virtual

ALTER TABLE `f6db_20160522`.`user` ADD COLUMN `virtual_name` varchar(20) GENERATED ALWAYS AS (data->>"$.name") VIRTUAL NULL AFTER `data`; 

这样简直完美 

select UID,virtual_name from user where virtual_name is not null

完全看不出来这是个json表呢