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, "记录插入成功。")
  • 查看数据

数据量如下图:

那么我们是不是可以考虑把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

本站文章资源均来源自网络,除非特别声明,否则均不代表站方观点,并仅供查阅,不作为任何参考依据!
如有侵权请及时跟我们联系,本站将及时删除!
如遇版权问题,请查看 本站版权声明
THE END
分享
二维码
海报
MySQL虚拟列在电商场景下的应用
有时候大家在做电商商品推广的时候会涉及到一些json串的存储,同时在检索的时候会通过json中里面的段就进行相关检索,这样的话就可能会引入虚拟列这个概念。下面用...
<<上一篇
下一篇>>