慢查询

🪐 TiDB 技术问题
1

【 TiDB 使用环境】生产环境
【 TiDB 版本】
【复现路径】查询慢
【遇到的问题：问题现象及影响】
【资源配置】进入到 TiDB Dashboard -集群信息 (Cluster Info) -主机(Hosts) 截图此页面

image
image1188×831 101 KB

image
image1009×538 43.3 KB

【附件：截图/日志/监控】

查询sql

SELECT
  p.sid,
  p.name,
  p.domain,
  p.timeZone,
  p.isactived AS isActived,
  p.createtime AS createTime,
  p.updateTime,
  p.lp_enabled AS lpEnabled,
  p.ab_enabled AS abEnabled,
  p.ac_enabled AS acEnabled,
  p.pt_cross_domain_setting AS ptCrossDomainSetting,
  p.status,
  ap.type,
  p.is_np_package AS isNpPackage,
  p.is_virtual AS isVirtual,
  p.web_type AS webType,
  p.web_target AS webTarget,
  p.need_function AS needFunction,
  p.is_union_config AS isUnionConfig,
  ap.type AS userRole,
  (
    CASE
      ap.type
      WHEN '0' THEN a.email
      ELSE (
        SELECT
          a1.email
        FROM
          account_profile ap1
          LEFT JOIN account a1 ON a1.id = ap1.aid
        WHERE
          ap1.sid = p.sid
          AND ap1.type = '0'
        LIMIT
          1
      )
    END
  ) AS ownerEmail,
  p.additional_id AS additionalId
FROM
  account_profile ap
  LEFT JOIN account a ON a.id = ap.aid
  INNER JOIN profile p ON ap.sid = p.sid
WHERE
  ap.aid = '7b71d2aa-dc0d-4179-96c0-27330587fb50'
  AND p.status != '-1'
  AND ap.status = '1';

# 查询耗时
300 rows in set (1.44 sec)

索引：

account.id 主键
profile.sid 主键
account_profile.aid 索引

执行时间截图

image
image1555×1281 226 KB

172.20.13.133 的内存是占用最高的

image
image1049×542 55.9 KB

MySQL 查询耗时

300 rows in set (0.02 sec)

请帮忙分析一下sql查询慢的原因，谢谢

2

把 具体的执行计划 以json形式复制出来看看

3

为啥 ELSE (
SELECT
a1.email
FROM
account_profile ap1
LEFT JOIN account a1 ON a1.id = ap1.aid
WHERE
ap1.sid = p.sid
AND ap1.type = ‘0’
LIMIT
1
) 这里写一个子查询呢，这种写法你每查到一条据就是会查一次子查询哦，考虑用join去替代

4

WITH OwnerEmails AS (
SELECT
ap1.sid,
a1.email
FROM
account_profile ap1
LEFT JOIN account a1 ON a1.id = ap1.aid
WHERE
ap1.type = ‘0’
)

SELECT
p.sid,
p.name,
p.domain,
p.timeZone,
p.isactived AS isActived,
p.createtime AS createTime,
p.updateTime,
p.lp_enabled AS lpEnabled,
p.ab_enabled AS abEnabled,
p.ac_enabled AS acEnabled,
p.pt_cross_domain_setting AS ptCrossDomainSetting,
p.status,
ap.type,
p.is_np_package AS isNpPackage,
p.is_virtual AS isVirtual,
p.web_type AS webType,
p.web_target AS webTarget,
p.need_function AS needFunction,
p.is_union_config AS isUnionConfig,
ap.type AS userRole,
COALESCE(
CASE
WHEN ap.type = ‘0’ THEN a.email
END,
oe.email
) AS ownerEmail,
p.additional_id AS additionalId
FROM
account_profile ap
LEFT JOIN account a ON a.id = ap.aid
INNER JOIN profile p ON ap.sid = p.sid
LEFT JOIN OwnerEmails oe ON oe.sid = p.sid
WHERE
ap.aid = ‘7b71d2aa-dc0d-4179-96c0-27330587fb50’
AND p.status != ‘-1’
AND ap.status = ‘1’;

你用explain analyze 分析下看 。

5 
...
  p.is_union_config AS isUnionConfig,
  CASE
    WHEN ap.type = '0' THEN a.email
    ELSE a_owner.email
  END AS ownerEmail,
  p.additional_id AS additionalId
FROM
  account_profile ap
  LEFT JOIN account a ON a.id = ap.aid  -- 当前用户的 email
  INNER JOIN profile p ON ap.sid = p.sid
  LEFT JOIN account_profile ap_owner ON ap_owner.sid = p.sid AND ap_owner.type = '0'  -- 获取主账户的 profile
  LEFT JOIN account a_owner ON a_owner.id = ap_owner.aid  -- 获取主账户的 email
WHERE
  ap.aid = '7b71d2aa-dc0d-4179-96c0-27330587fb50'
  AND p.status != '-1'
  AND ap.status = '1';

其他的不变，这样查询是快了很多

2 个赞
6

改写sql能解决，那说明确实是优化器有点问题。

感觉可以整理一下，去github上提交一个issue。

7

请问你的 TiDB 版本是多少？apply 算子看起来没有走到并行，从 tidb v5 版本起，apply 算子支持了并行，参考：https://docs.pingcap.com/zh/tidb/stable/system-variables#tidb_enable_parallel_apply-从-v50-版本开始引入

另外你的表好像又关联一遍，个人感觉没必要再关联一遍，改写需要了解模型设计与业务逻辑

1 个赞
8

高手呀！mark一下

9

MySQL上就把 account表从函数获取，或者在SELECT上做子查询，或者用WITH语句，可惜TiDB没自定义函数

10

优化器还是存在不足吧这看来，啥时候可以自动改写，就牛逼了

11

对的，从MySQL迁移过来遇到不少sql的问题，好多要改sql，在mysql 3秒，在tidb要20秒，优化起来太麻烦了

13

SELECT
p.sid,
p.name,
p.domain,
p.timeZone,
p.isactived AS isActived,
p.createtime AS createTime,
p.updateTime,
p.lp_enabled AS lpEnabled,
p.ab_enabled AS abEnabled,
p.ac_enabled AS acEnabled,
p.pt_cross_domain_setting AS ptCrossDomainSetting,
p.status,
ap.type,
p.is_np_package AS isNpPackage,
p.is_virtual AS isVirtual,
p.web_type AS webType,
p.web_target AS webTarget,
p.need_function AS needFunction,
p.is_union_config AS isUnionConfig,
COALESCE(
CASE
WHEN ap.type = ‘0’ THEN a.email
ELSE NULL
END,
a1.email
) AS ownerEmail,
p.additional_id AS additionalId
FROM
account_profile ap
LEFT JOIN account a ON a.id = ap.aid
INNER JOIN profile p ON ap.sid = p.sid
LEFT JOIN (
SELECT
ap1.sid,
a1.email
FROM
account_profile ap1
LEFT JOIN account a1 ON a1.id = ap1.aid
WHERE
ap1.type = ‘0’
) a1 ON a1.sid = p.sid
WHERE
ap.aid = ‘7b71d2aa-dc0d-4179-96c0-27330587fb50’
AND p.status != ‘-1’
AND ap.status = ‘1’;
试下一下改写后的sql。

1 个赞