【MySQL】才 10W 条数据就查询超时了！！！

前言

基于 MySQL 8.X 讨论

在实际的项目中，有一个查询功能，查询加入了 A、B 组织并且没有加入 X、Y 组织的用户，该功能在关系表中只有 10W 条数据的情况下超时了！！！

超时的 SQL 居然是 COUNT ！！！出问题的 SQL 如下：

SELECT COUNT(*)
FROM t_user u
WHERE u.id IN (
        SELECT r1.user_id
        FROM t_rel r1
        WHERE r1.dept_id = 94
    )
    AND u.id NOT IN (
        SELECT r2.user_id
        FROM t_rel r2
        WHERE r2.dept_id = 1595998427392180224
    );

解决办法：使用 EXISTS 替换 IN

SELECT COUNT(*)
FROM t_user u
WHERE EXISTS (
        SELECT 1
        FROM t_rel r1
        WHERE u.id = r1.user_id
            AND r1.dept_id = 94
    )
    AND NOT EXISTS (
        SELECT 1
        FROM t_rel r2
        WHERE u.id = r2.user_id
            AND r2.dept_id = 1595998427392180224
    );

COUNT 更容易暴露 SQL 问题

---

接下来对比一下常见的 “歪门邪道” 表结构和正常的表结构的性能差异

---

表结构

V1

CREATE TABLE `t_rel_v1` (
  `user_id` bigint unsigned NOT NULL COMMENT '用户ID',
  `dept_id` bigint unsigned NOT NULL COMMENT '部门ID',
  PRIMARY KEY (`user_id`, `dept_id`) USING BTREE,
  KEY `idx_user_id` (`user_id`) USING BTREE,
  KEY `idx_dept_id` (`dept_id`) USING BTREE
) ENGINE = InnoDB COMMENT = '用户部门关联表V1';

t_rel_v1 存储的数据就像下面的样子：

user_id	dept_id
123	101
123	102
…	…
155	999

V2

CREATE TABLE `t_rel_v2` (
  `user_id` bigint unsigned NOT NULL COMMENT '用户ID',
  `dept_ids` varchar(600) NOT NULL COMMENT '部门ID，逗号分隔',
  PRIMARY KEY (`user_id`) USING BTREE,
  KEY `idx_dept_ids` (`dept_ids`) USING BTREE
) ENGINE = InnoDB COMMENT = '用户部门关联表V2';

t_rel_v2 存储的数据就像下面的样子：

user_id	dept_ids
123	101, 102
…	…
155	999

查询测试

MySQL workbench visual explain 图例解释

查询部门

查询指定用户加入的所有部门

V1

SELECT *
FROM t_rel_v1
WHERE user_id = 1603578317913591808;

visual explain 结果如下：

V2

SELECT dept_ids
FROM t_rel_v2
WHERE user_id = 1603578317913591808;

visual explain 结果如下：

统计用户数

统计指定部门下用户总数

V1

SELECT COUNT(*)
FROM t_rel_v1
WHERE dept_id = 94;

visual explain 结果如下：

V2

SELECT COUNT(*)
FROM t_rel_v2
WHERE FIND_IN_SET(94, dept_ids) > 0;

visual explain 结果如下：

统计用户数（求并集）

统计指定的多个部门下用户总数

V1

SELECT COUNT(distinct user_id)
FROM t_rel_v1
WHERE dept_id IN (94, 1595998427392180224);

visual explain 结果如下：

V2

SELECT COUNT(distinct user_id)
FROM t_rel_v2
WHERE FIND_IN_SET(94, dept_ids) > 0
    OR FIND_IN_SET(1595998427392180224, dept_ids) > 0;

visual explain 结果如下：

统计用户数（求差集）

统计在 94 部门，但不在 1595998427392180224 部门的用户数

V1

SELECT COUNT(*)
FROM t_rel_v1 r1
WHERE r1.dept_id = 94
    AND NOT EXISTS(
        SELECT 1
        FROM t_rel_v1 r2
        WHERE r1.user_id = r2.user_id
            AND r2.dept_id = 1595998427392180224
    );

visual explain 结果如下：

V2

SELECT COUNT(*)
FROM t_rel_v2
WHERE FIND_IN_SET(94, dept_ids) > 0
    AND FIND_IN_SET(1595998427392180224, dept_ids) < 1;

visual explain 结果如下：

---

总结

V1 表结构在稍微复杂一点的查询中表现更优，SQL 编写也更加简单，V1 表结构是更好的选择。