用户画像设计
某些业务需要做用户画像（也就是对用户打标签），然后根据用户的标签，通过数据挖掘技术，进行相应的产品推荐。比如：
- 在电商行业中，根据用户的穿搭喜好，推荐相应的商品；
- 在音乐行业中，根据用户喜欢的音乐风格和常听的歌手，推荐相应的歌曲；
- 在金融行业，根据用户的风险喜好和投资经验，推荐相应的理财产品。
在这，我强烈推荐你用 JSON 类型在数据库中存储用户画像信息，并结合 JSON 数组类型和多值索引的特点进行高效查询。假设有张画像定义表：
CREATE TABLE Tags ( tagId bigint auto_increment, tagName varchar(255) NOT NULL, primary key(tagId) ); SELECT * FROM Tags; +-------+--------------+ | tagId | tagName | +-------+--------------+ | 1 | 70后 | | 2 | 80后 | | 3 | 90后 | | 4 | 00后 | | 5 | 爱运动 | | 6 | 高学历 | | 7 | 小资 | | 8 | 有房 | | 9 | 有车 | | 10 | 常看电影 | | 11 | 爱网购 | | 12 | 爱外卖 | +-------+--------------+
可以看到，表 Tags 是一张画像定义表，用于描述当前定义有多少个标签，接着给每个用户打标签，比如用户 David，他的标签是 80 后、高学历、小资、有房、常看电影；用户 Tom，90 后、常看电影、爱外卖。
若不用 JSON 数据类型进行标签存储，通常会将用户标签通过字符串，加上分割符的方式，在一个字段中存取用户所有的标签：
+-------+---------------------------------------+ |用户 |标签 | +-------+---------------------------------------+ |David |80后 ； 高学历 ； 小资 ； 有房 ；常看电影 | |Tom |90后 ；常看电影 ； 爱外卖 | +-------+---------------------------------------
这样做的缺点是：不好搜索特定画像的用户，另外分隔符也是一种自我约定，在数据库中其实可以任意存储其他数据，最终产生脏数据。
用 JSON 数据类型就能很好解决这个问题：
DROP TABLE IF EXISTS UserTag; CREATE TABLE UserTag ( userId bigint NOT NULL, userTags JSON, PRIMARY KEY (userId) ); INSERT INTO UserTag VALUES (1,'[2,6,8,10]'); INSERT INTO UserTag VALUES (2,'[3,10,12]');
其中，userTags 存储的标签就是表 Tags 已定义的那些标签值，只是使用 JSON 数组类型进行存储。
MySQL 8.0.17 版本开始支持 Multi-Valued Indexes，用于在 JSON 数组上创建索引，并通过函数 member of、json_contains、json_overlaps 来快速检索索引数据。所以你可以在表 UserTag 上创建 Multi-Valued Indexes：
ALTER TABLE UserTag ADD INDEX idx_user_tags ((cast((userTags->"$") as unsigned array)));• 1
如果想要查询用户画像为常看电影的用户，可以使用函数 MEMBER OF：
EXPLAIN SELECT * FROM UserTag WHERE 10 MEMBER OF(userTags->"$")\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: UserTag partitions: NULL type: ref possible_keys: idx_user_tags key: idx_user_tags key_len: 9 ref: const rows: 1 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) SELECT * FROM UserTag WHERE 10 MEMBER OF(userTags->"$"); +--------+---------------+ | userId | userTags | +--------+---------------+ | 1 | [2, 6, 8, 10] | | 2 | [3, 10, 12] | +--------+---------------+ 2 rows in set (0.00 sec)
如果想要查询画像为 80 后，且常看电影的用户，可以使用函数 JSON_CONTAINS：
EXPLAIN SELECT * FROM UserTag WHERE JSON_CONTAINS(userTags->"$", '[2,10]')\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: UserTag partitions: NULL type: range possible_keys: idx_user_tags key: idx_user_tags key_len: 9 ref: NULL rows: 3 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) SELECT * FROM UserTag WHERE JSON_CONTAINS(userTags->"$", '[2,10]'); +--------+---------------+ | userId | userTags | +--------+---------------+ | 1 | [2, 6, 8, 10] | +--------+---------------+ 1 row in set (0.00 sec)
如果想要查询画像为 80 后、90 后，且常看电影的用户，则可以使用函数 JSON_OVERLAP：
EXPLAIN SELECT * FROM UserTag WHERE JSON_OVERLAPS(userTags->"$", '[2,3,10]')\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: UserTag partitions: NULL type: range possible_keys: idx_user_tags key: idx_user_tags key_len: 9 ref: NULL rows: 4 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) SELECT * FROM UserTag WHERE JSON_OVERLAPS(userTags->"$", '[2,3,10]'); +--------+---------------+ | userId | userTags | +--------+---------------+ | 1 | [2, 6, 8, 10] | | 2 | [3, 10, 12] | +--------+---------------+ 2 rows in set (0.01 sec)
总结
JSON 类型是 MySQL 5.7 版本新增的数据类型，用好 JSON 数据类型可以有效解决很多业务中实际问题。最后，我总结下今天的重点内容：
- 使用 JSON 数据类型，推荐用 MySQL 8.0.17 以上的版本，性能更好，同时也支持 Multi-Valued Indexes；
- JSON 数据类型的好处是无须预先定义列，数据本身就具有很好的描述性；
- 不要将有明显关系型的数据用 JSON 存储，如用户余额、用户姓名、用户身份证等，这些都是每个用户必须包含的数据；
- JSON 数据类型推荐使用在不经常更新的静态数据存储。
