有时候大家在做电商商品推广的时候会涉及到一些json串的存储,同时在检索的时候会通过json中里面的段就进行相关检索,这样的话就可能会引入虚拟列这个概念。下面用一个简单的例子来介绍一下虚拟列的使用。
MySQL 5.7.8开始支持JSON类型,JSON类型支持存储json格式的字符串列,拥有以下特性:
查看mysql 版本必须在5.7.8及以上,查看命令参考:
show variables like '%version%';
create table t_data_json (
id int not null auto_increment comment '自增ID',
data_json JSON,
primary key (id)
)ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;
这里为了测试,我这边写了一个简单python脚本进行数据的插入,参考如下
# coding: UTF-8
#批量插入数据到mysql数据库中
import mysql.connector
mydb = mysql.connector.connect(
host="127.0.0.1", # 数据库主机地址
user="root", # 数据库用户名
passwd="12!Qaz@Wsx", # 数据库密码
database="study"
)
mycursor = mydb.cursor()
for i in range (1000000):
commission_amount = i
insert_sql = 'insert into t_data_json (data_json) value (\'{ "commission_amount": '+str(commission_amount)+', "commission_amount_after_coupon": 3.74, "commission_rate": 17.0, "promote_status": 1.0, "start_time": 1.61156347065E12, "end_time": 1.61156347066E12 }\')'
mycursor.execute(insert_sql)
mydb.commit() # 数据表内容有更新,必须使用到该语句
print(mycursor.rowcount, "记录插入成功。")
数据量如下图:
数据磁盘大小如下图:
数据索引大小如下图:
数据大小查案的命令如下:
use information_schema;
查看数据量:select TABLE_ROWS from TABLES where table_schema='study' and table_name='t_data_json';
查看表数据文件大小:select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='study' and table_name='t_data_json';
查看表索引文件大小:select concat(round(sum(INDEX_LENGTH/1024/1024),2),'MB') as index_data from TABLES where table_schema='study' and table_name='t_data_json';
这里使用data_json.commission_amount 查询commission_amount大于30的总数,发现效果并不理想,耗时1.48s,效果图如下:
那么我们是不是可以考虑把commission_amount作为一个虚拟列加上索引这样会不会效果好一点呢?那么接下来看看效果如何。
alter table t_data_json add v_commission_amount double(10,2) generated always as (JSON_EXTRACT(data_json,'$.commission_amount'));
alter table t_data_json add index v_commission_amount_idx (v_commission_amount);
合理的利用MySQL的虚拟字段可以有效的提升查询效果,如果由于数据量太大导致查询效果还是不太理想,那么就应该考虑合理分表来存储数据了。
MySQL 文档: https://dev.mysql.com/doc/refman/5.7/en/json.html
RFC 7159:https://datatracker.ietf.org/doc/html/rfc7159
MySQL中文文档:https://www.docs4dev.com/docs/zh/mysql/5.7/reference/json-search-functions.html
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。