【 TiDB 发现现象】
select 上的列做子查询 tidb 发现都很慢
【概述】 场景 + 问题概述
相同的sql
SELECT
COUNT( 1 )
FROM
(
SELECT
a.id,
a.db_name AS dbName,
a.table_name AS tableName,
a.table_alias AS tableAlias,
a.format,
a.delimit_id AS delimitId,
a.desc,
a.create_per AS createPer,
u.username AS createPerName,
a.create_time AS createTime,
(
SELECT
ap.id
FROM
data_application ap
WHERE
ap.table_id = a.id
AND ap.user_id = 72
AND ap.apply_type = 1
ORDER BY
ap.id DESC
LIMIT 1
) AS applyId
FROM
bdp_table_info a
LEFT JOIN sys_user AS u ON a.create_per = u.user_id
WHERE
1 = 1
AND (
EXISTS ( SELECT data_id FROM sys_tenant_data_permission WHERE tenant_id = 1 AND data_type = 2 AND query = 1 AND data_id=a.id )
OR a.tenant_id = 1
)
) t
【问题】 当前遇到的问题
tidb上跑 需要15s
mysql上跑只需要0.356s
【TiDB 版本】
5.7.25-TiDB-v5.2.1