MySQL虚拟列在电商场景下的应用
原创MySQL虚拟列在电商场景下的应用
原创
引言
有时候大家在做电商商品推广的时候会涉及到一些json串的存储，同时在检索的时候会通过json中里面的段就进行相关检索，这样的话就可能会引入虚拟列这个概念。下面用一个简单的例子来介绍一下虚拟列的使用。
JSON字段类型
MySQL 5.7.8开始支持JSON类型，JSON类型支持存储json格式的字符串列，拥有以下特性：
- 自动校验存储JSON格式数据
- 优化json存储格式，存储在 JSON 列中的 JSON 文档被转换为允许对文档元素进行快速读取访问的内部格式
虚拟列的实践
数据准备
- 确认MySQL版本
查看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';JSON字段查询
这里使用data_json.commission_amount 查询commission_amount大于30的总数，发现效果并不理想，耗时1.48s，效果图如下：
那么我们是不是可以考虑把commission_amount作为一个虚拟列加上索引这样会不会效果好一点呢？那么接下来看看效果如何。
虚拟字段
- 添加虚拟字段v_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);
- 通过v_commission_amount来查询commission_amount大于30的总数,发现查询时间只有0.27s，发现效率提升5倍
总结
合理的利用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 删除。