mysql的find_in_set效率探讨

mysql的find_in_set效率探讨

在新公司同事不少数据表设计的时候使用一个字段来存储多对多关系，比如 表 user中有一个字段叫 category, category存储的是 "1,3,9" 这样的类型的数据，实际上是category的id 用逗号分隔开来的。今天就实际探讨一下多对多关系 用逗号分隔关联id与用关联表的差距。

用户表：

CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `category_ids` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10001 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

分类表：

CREATE TABLE `category`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10001 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

用户分类关联表：

CREATE TABLE `user_category`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL,
  `category_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `u`(`user_id`) USING BTREE,
  INDEX `c`(`category_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 30001 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

分类表没啥用暂时没啥用 准备下次关联测试

场景：每一个用户有三个分类；

每个表添加1W数据 中间表3W；

sql为找出某个分类的用户id

以下是1W数据的测试结果：

SELECT `id` FROM `user` WHERE  (  FIND_IN_SET(1234,category_ids) ) [ RunTime:0.008363s ]
SELECT `user_id` FROM `user_category` WHERE  `category_id` = 1234 [ RunTime:0.001166s ]

耗时差：7.17倍

SELECT `id` FROM `user` WHERE  (  FIND_IN_SET(2345,category_ids) ) [ RunTime:0.010182s ]
SELECT `user_id` FROM `user_category` WHERE  `category_id` = 2345 [ RunTime:0.000936s ]

耗时差：10.87倍

SELECT `id` FROM `user` WHERE  (  FIND_IN_SET(3456,category_ids) ) [ RunTime:0.005645s ]
SELECT `user_id` FROM `user_category` WHERE  `category_id` = 3456 [ RunTime:0.000983s ]

耗时差：5.74倍

SELECT `id` FROM `user` WHERE  (  FIND_IN_SET(4567,category_ids) ) [ RunTime:0.007514s ]
SELECT `user_id` FROM `user_category` WHERE  `category_id` = 4567 [ RunTime:0.001098s ]

耗时差：6.84倍

SELECT `id` FROM `user` WHERE  (  FIND_IN_SET(5678,category_ids) ) [ RunTime:0.013825s ]
SELECT `user_id` FROM `user_category` WHERE  `category_id` = 5678 [ RunTime:0.001037s ]

耗时差：13.33倍

SELECT `id` FROM `user` WHERE  (  FIND_IN_SET(6789,category_ids) ) [ RunTime:0.008469s ]
SELECT `user_id` FROM `user_category` WHERE  `category_id` = 6789 [ RunTime:0.000770s ]

耗时差：10.99倍

SELECT `id` FROM `user` WHERE  (  FIND_IN_SET(7890,category_ids) ) [ RunTime:0.010756s ]
SELECT `user_id` FROM `user_category` WHERE  `category_id` = 7890 [ RunTime:0.000905s ]

耗时差：11.88倍

有些时候贪方便直接将多对多关联的字段放在一个字段中，没错可以方便书写与加速开发，但此次数据仅仅1W

##2021-01-26 添加10W数据的测试

以下是10W数据的测试结果：

SELECT `id` FROM `user` WHERE  (  FIND_IN_SET(1234,category_ids) ) [ RunTime:0.038604s ]
SELECT `user_id` FROM `user_category` WHERE  `category_id` = 1234 [ RunTime:0.000846s ]

耗时差：45.63倍

SELECT `id` FROM `user` WHERE  (  FIND_IN_SET(2345,category_ids) ) [ RunTime:0.045461s ]
SELECT `user_id` FROM `user_category` WHERE  `category_id` = 2345 [ RunTime:0.001124s ]

耗时差：40.44倍

SELECT `id` FROM `user` WHERE  (  FIND_IN_SET(3456,category_ids) ) [ RunTime:0.042181s ]
SELECT `user_id` FROM `user_category` WHERE  `category_id` = 3456 [ RunTime:0.000702s ]

耗时差：60.08倍

SELECT `id` FROM `user` WHERE  (  FIND_IN_SET(4567,category_ids) ) [ RunTime:0.086859s ]
SELECT `user_id` FROM `user_category` WHERE  `category_id` = 4567 [ RunTime:0.001231s ]

耗时差：70.55倍

SELECT `id` FROM `user` WHERE  (  FIND_IN_SET(5678,category_ids) ) [ RunTime:0.061878s ]
SELECT `user_id` FROM `user_category` WHERE  `category_id` = 5678 [ RunTime:0.000901s ]

耗时差：68.67倍