TiDB 社区智慧合集丨TiDB 相关 SQL 脚本大全

本文作者：PingCAP

非常感谢各位 TiDBer 在之前 【TiDBer 唠嗑茶话会 48】非正式 TiDB 相关 SQL 脚本征集大赛！( https://asktug.com/t/topic/996635 )里提供的各种常用脚本。

在这篇文章中，我们整理了社区同学提供的一系列 TiDB 相关 SQL 脚本，希望能为大家在 TiDB 的使用过程中提供一些帮助和参考。这些脚本涵盖了常见场景下的 SQL 操作，欢迎各位 TiDBer 持续补充更新～

未来，我们也将整理更多 TiDB 相关实用指南，帮助大家更好地了解、运用 TiDB，敬请期待！

TiDB 相关 SQL 脚本大全

1 缓存表

贡献者：@ShawnYan

ALTER TABLE xxx CACHE | NOCACHE;

2 TSO 时间转换

贡献者：@我是咖啡哥

● 方法一：使用函数 TIDB_PARSE_TSO

SELECT TIDB_PARSE_TSO(437447897305317376);

+------------------------------------+
| TIDB_PARSE_TSO(437447897305317376) |
+------------------------------------+
| 2022-11-18 08:28:17.704000        |
+------------------------------------+
1 row in set (0.25 sec)

● 方法二：使用 pd-ctl

tiup ctl:v6.4.0 pd -i -u http://pdip:2379
Starting component `ctl`: /Users/xxx/.tiup/components/ctl/v6.4.0/ctl pd -i -u http://pdip:2379
» tso 437447897305317376
system: 2022-11-18 08:28:17.704 +0800 CST
logic: 0

3 读取历史数据

贡献者：@我是咖啡哥

● 使用 AS OF TIMESTAMP 语法读取历史数据，可以通过以下三种方式使用 AS OF TIMESTAMP 语法：

SELECT … FROM … AS OF TIMESTAMP

START TRANSACTION READ ONLY AS OF TIMESTAMP

SET TRANSACTION READ ONLY AS OF TIMESTAMP

SELECT * FROM t AS OF TIMESTAMP '2021-05-26 16:45:26';
START TRANSACTION READ ONLY AS OF TIMESTAMP '2021-05-26 16:45:26';
SET TRANSACTION READ ONLY AS OF TIMESTAMP '2021-05-26 16:45:26';

● 通过系统变量 tidb_read_staleness 读取历史数据

从 5 秒前至现在的时间范围内选择一个尽可能新的时间戳

SET @@tidb_read_staleness = '-5';

● 通过系统变量 tidb_snapshot 读取历史数据

设置一个特殊的环境变量，这个是一个 session scope 的变量，其意义为读取这个时间之前的最新的一个版本

SET @@tidb_snapshot = '2016-10-08 16:45:26';

清空这个变量后，即可读取最新版本数据

SET @@tidb_snapshot = '';

4 查询 tikv_gc_life_time 和 tikv_gc_safe_point 默认时长

贡献者：@TiDBer_m6V1BalM

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM mysql.tidb
WHERE VARIABLE_NAME LIKE 'tikv_gc%';

5 搜索某个用户的 TopN 慢查询

贡献者：@fanruinet

SELECT query_time, query, user
FROM information_schema.slow_query
WHERE is_internal = false -- 排除 TiDB 内部的慢查询 SQL
  AND user = 'user1' -- 查找的用户名
ORDER BY query_time DESC
LIMIT 2;

6 统计间隔 5 分钟的数据

贡献者：@forever

SELECT CONCAT(DATE_FORMAT(create_time, '%Y-%m-%d %H:'), FLOOR(DATE_FORMAT(create_time, '%i') / 5)), COUNT(*) 
FROM jcxx 
GROUP BY 1;

7 反解析 digest 成 SQL 文本

贡献者：@hey-hoho

SELECT tidb_decode_sql_digests('["xxxxx"]');

8 不涉及分区表用下面的方式查看表的使用情况

贡献者：@xfworld

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       TABLE_ROWS,
       (DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024 AS table_size
FROM tables
ORDER BY table_size DESC
LIMIT 20;

9 partition 表提供了分区表和非分区表的资源使用情况

贡献者：@xfworld

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    PARTITION_NAME,
    TABLE_ROWS,
    (DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024 AS table_size
FROM
    information_schema.PARTITIONS
ORDER BY
    table_size DESC
LIMIT 20;

10 查询分析器中看配置文件参数

贡献者：@Kongdom

show config

SHOW CONFIG 语句用于展示 TiDB 各个组件当前正在应用的配置，请注意，配置与系统变量作用于不同维度，请不要混淆，如果希望获取系统变量信息，请使用 SHOW VARIABLES ( https://docs.pingcap.com/zh/tidb/stable/sql-statement-show-variables ) 语法。

11 查找读流量排名前 10 的热点 region

贡献者：@BraveChen

SELECT DISTINCT region_id
FROM INFORMATION_SCHEMA.tikv_region_status
WHERE READ_BYTES > ?
ORDER BY READ_BYTES DESC
LIMIT 10;

12 查看参数和变量的脚本

贡献者：@buddyyuan

#!/bin/bash

case $1 in
    -pd)
        mysql -uroot -h127.0.0.1 -P4000 -p"" -e "SHOW CONFIG WHERE type ='pd' and name like '%$2%'";;
    -tidb)
        mysql -uroot -h127.0.0.1 -P4000 -p"" -e "SHOW CONFIG WHERE type ='tidb' and name like '%$2%'";;
    -tikv)
        mysql -uroot -h127.0.0.1 -P4000 -p"" -e "SHOW CONFIG WHERE type ='tikv' and name like '%$2%'";;
    -tiflash)
        mysql -uroot -h127.0.0.1 -P4000 -p"" -e "SHOW CONFIG WHERE type ='tiflash' and name like '%$2%'";;
    -var)
        mysql -uroot -h127.0.0.1 -P4000 -p"" -e "show variables like '%$2%';";;
    -h)
        echo "-pd show pd parameters"
        echo "-tidb show tidb parameters"
        echo "-tikv show tikv parameters"
        echo "-tiflash show tiflash parameters"
        echo "-var show itidb variables"
;;
esac

还能用 grep 在过滤一次

sh showparammeter.sh -tikv memory-pool-quota | grep -i "210:29160" tikv 192.16.201.210:29160 server.grpc-memory-pool-quota 9223372036854775807B

13 查找重复记录

贡献者：@ealam_ 小羽

SELECT *
FROM 表
WHERE 重复字段 IN (
    SELECT 重复字段
    FROM 表
    GROUP BY 重复字段
    HAVING COUNT(*) > 1
)

14 查询耗时最高的慢 sql

贡献者：@caiyfc

SELECT
    query AS sql_text,
    sum_query_time,
    mnt AS executions,
    avg_query_time,
    avg_proc_time,
    avg_wait_time,
    max_query_time,
    avg_backoff_time,
    Cop_proc_addr,
    digest,
    (CASE
        WHEN avg_proc_time = 0 THEN 'point_get or commit'
        WHEN (avg_proc_time > avg_wait_time AND avg_proc_time > avg_backoff_time) THEN 'coprocessor_process'
        WHEN (avg_backoff_time > avg_wait_time AND avg_proc_time < avg_backoff_time) THEN 'backoff'
        ELSE 'coprocessor_wait'
    END) AS type
FROM
    (
        SELECT
            SUBSTR(query, 1, 100) AS query,
            COUNT(*) AS mnt,
            AVG(query_time) AS avg_query_time,
            AVG(process_time) AS avg_proc_time,
            AVG(wait_time) AS avg_wait_time,
            MAX(query_time) AS max_query_time,
            SUM(query_time) AS sum_query_time,
            digest,
            Cop_proc_addr,
            AVG(backoff_time) AS avg_backoff_time
        FROM
            information_schema.cluster_slow_query
        WHERE
            time >= '2022-07-14 17:00:00'
            AND time <= '2022-07-15 17:10:00'
            AND DB = 'web'
        GROUP BY
            SUBSTR(query, 1, 100)
    ) t
ORDER BY
    max_query_time DESC
LIMIT 20;

15 日常维护用的最多的 SQL

贡献者：@tracy0984

SELECT * FROM information_schema.cluster_processlist; -- kill id;

16 恢复数据（适用于 drop 与 truncate）

贡献者：@凌云 Cloud

FLASHBACK TABLE target_table_name TO new_table_name;

17 批量修改库名

贡献者：@TiDBer_dog

./bat_rename.sh lihongbao/ dev2_kelun dev2_sinodemo 路径./leo_backup

18 高并发的场景下获取 sql

贡献者：@jiawei

SELECT *
FROM information_schema.processlist
WHERE info IS NOT NULL;

19 查看 schema 下的表都有哪些

贡献者：@Ming

SHOW TABLES IN schema;

20 查看表 leader

贡献者：@TiDBer_wTKU9jv6

SELECT COUNT(1), tss.ADDRESS
FROM INFORMATION_SCHEMA.TIKV_REGION_PEERS trp,
     INFORMATION_SCHEMA.TIKV_REGION_STATUS trs,
     INFORMATION_SCHEMA.TIKV_STORE_STATUS tss
WHERE trp.STORE_ID = tss.STORE_ID
  AND trp.REGION_ID = trs.REGION_ID
  AND trs.DB_NAME = 'test'
  AND trs.TABLE_NAME = 'test'
  AND trp.IS_LEADER = 1
GROUP BY tss.ADDRESS
ORDER BY tss.ADDRESS;

21 shell 的调皮加速脚本

贡献者：@gcworkerishungry

alias ctidb="mysql -u root -ptidb -Dcktest -h S001 -P4000"
alias dtidb="tiup cluster display tidb-test"
alias etidb="tiup cluster edit-config tidb-test"
alias ptidb="tiup cluster prune tidb-test"
alias rtidb="tiup cluster restart tidb-test"

22 恢复数据到新的数据库

贡献者：@TiDBer_ 徐川

./loader -h 192.168.180.3 -u root -p q1w2 -P 4000 -t 32 -d leo_backup/

23 开启 tiflash

贡献者：@TiDBer_pFFcXLgY

ALTER TABLE xxx SET TIFLASH REPLICA 1;

24 表 region 分布语句

贡献者：@秋枫之舞

SELECT trs.db_name,
       trs.table_name,
       trs.index_name,
       trp.store_id,
       COUNT(*),
       SUM(approximate_keys)
FROM information_schema.tikv_region_status trs,
     information_schema.tikv_store_status tss,
     information_schema.tikv_region_peers trp
WHERE trs.db_name = 'prd01'
  AND trs.table_name = 'tab_name'
  AND trp.is_leader = 1
  AND trp.store_id = tss.store_id
  AND trs.region_id = trp.region_id
GROUP BY trs.db_name,
         trs.table_name,
         trs.index_name,
         trp.store_id
ORDER BY trs.index_name;

25 查看列的元数据

贡献者：@张雨齐0720

SHOW STATS_HISTOGRAMS WHERE db_name LIKE 'test' AND table_name LIKE 'test1';

26 表的存储位置（store、peer 信息）

贡献者：@bert

SELECT DISTINCT
    a.TIDB_TABLE_ID,
    b.DB_NAME,
    b.TABLE_NAME,
    b.REGION_ID,
    b.APPROXIMATE_SIZE,
    c.PEER_ID,
    c.STORE_ID,
    c.IS_LEADER,
    c.STATUS,
    d.ADDRESS,
    d.STORE_STATE_NAME,
    d.VERSION,
    d.CAPACITY,
    d.AVAILABLE,
    d.LABEL
FROM
    INFORMATION_SCHEMA.TABLES a
INNER JOIN
    INFORMATION_SCHEMA.TIKV_REGION_STATUS b ON a.TIDB_TABLE_ID = b.TABLE_ID
INNER JOIN
    INFORMATION_SCHEMA.TIKV_REGION_PEERS c ON b.REGION_ID = c.REGION_ID
INNER JOIN
    INFORMATION_SCHEMA.TIKV_STORE_STATUS d ON c.STORE_ID = d.STORE_ID
WHERE
    a.TABLE_SCHEMA = 'test'
    AND a.TABLE_NAME = 't';

27 将集群升级到指定版本 ( 在线升级 )

贡献者：@TiDBer_ 杨龟干外公

tiup cluster upgrade 例如升级到 v4.0.0 版本：
tiup cluster upgrade tidb-test v4.0.0

28 查询表大小

贡献者：@我是咖啡哥

SELECT
    t.TABLE_NAME,
    t.TABLE_ROWS,
    t.TABLE_TYPE,
    ROUND(t.DATA_LENGTH / 1024 / 1024 / 1024, 2) AS data_GB,
    ROUND(t.INDEX_LENGTH / 1024 / 1024 / 1024, 2) AS index_GB,
    t.CREATE_OPTIONS,
    t.TABLE_COMMENT
FROM
    INFORMATION_SCHEMA.`TABLES` t
WHERE
    table_schema = 'test'
    AND t.table_type = 'BASE TABLE'
ORDER BY
    t.TABLE_ROWS DESC;

SELECT
    CONCAT(table_schema, '.', table_name) AS 'Table Name',
    table_rows AS 'Number of Rows',
    CONCAT(ROUND(data_length / (1024 * 1024 * 1024), 4), 'G') AS 'Data Size',
    CONCAT(ROUND(index_length / (1024 * 1024 * 1024), 4), 'G') AS 'Index Size',
    CONCAT(ROUND((data_length + index_length) / (1024 * 1024 * 1024), 4), 'G') AS 'Total'
FROM
    information_schema.TABLES
WHERE
    table_schema LIKE 'test';

29 统计信息

贡献者：@我是咖啡哥

● 查看表的元数据

SHOW STATS_META WHERE db_name LIKE '%sbtest%';

● 查看表的健康状态

SHOW STATS_HEALTHY;

Healthy 字段，一般小于等于 60 的表需要做 analyze

SHOW stats_healthy WHERE table_name = 'xxx';
SHOW stats_healthy WHERE db_name = '' AND table_name = 'orders';
_name LIKE 'sbtest' AND table_name LIKE 'sbtest1';

● 查看直方图信息

SHOW STATS_BUCKETS WHERE db_name = '' AND table_name = '';

● 查看 analyze 状态

SHOW ANALYZE STATUS;

● 分析表、分区

ANALYZE TABLE sbtest1;
ANALYZE TABLE xxx PARTITION P202204;

30 执行计划

贡献者：@我是咖啡哥

绑定执行计划 ● 默认是 session 级别

CREATE BINDING FOR SELECT * FROM t USING SELECT * FROM t USE INDEX();
CREATE BINDING FOR SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.t1_id WHERE t1.int_col = ? USING SELECT /*+ INL_JOIN(t1, t2) */ * FROM t1 INNER JOIN t2 ON t1.id = t2.t1_id WHERE t1.int_col = ?;
EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.t1_id WHERE t1.int_col = 1;
SHOW BINDINGS FOR SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.t1_id WHERE t1.int_col = 1;
SHOW GLOBAL BINDINGS;
SHOW SESSION BINDINGS;
SELECT @@SESSION.last_plan_from_binding;

● 使用 explain format = ‘verbose’ 语句查看 SQL 的执行计划

EXPLAIN FORMAT = 'VERBOSE';
DROP BINDING FOR SQL;

31 查看 regions

贡献者：@我是咖啡哥

SHOW TABLE t_its_unload_priority_intermediate_info regions;
SHOW TABLE t_its_unload_priority_intermediate_info INDEX IDX_UPII_GROUP_BY_COMPOSITE regions;

32 统计读写热点表

贡献者：@我是咖啡哥

USE INFORMATION_SCHEMA;

SELECT 
    db_name, 
    table_name, 
    index_name, 
    type, 
    SUM(flow_bytes), 
    COUNT(1), 
    GROUP_CONCAT(h.region_id), 
    COUNT(DISTINCT p.store_id), 
    GROUP_CONCAT(p.store_id) 
FROM 
    INFORMATION_SCHEMA.tidb_hot_regions h 
JOIN 
    INFORMATION_SCHEMA.tikv_region_peers p 
ON 
    h.region_id = p.region_id AND p.is_leader = 1 
GROUP BY 
    db_name, 
    table_name, 
    index_name, 
    type;

SELECT 
    p.store_id, 
    SUM(flow_bytes), 
    COUNT(1) 
FROM 
    INFORMATION_SCHEMA.tidb_hot_regions h 
JOIN 
    INFORMATION_SCHEMA.tikv_region_peers p 
ON 
    h.region_id = p.region_id AND p.is_leader = 1 
GROUP BY 
    p.store_id 
ORDER BY 
    2 DESC;

SELECT 
    tidb_decode_plan();

33 TiFlash

贡献者：@我是咖啡哥

ALTER TABLE t_test_time_type SET TIFLASH REPLICA 1;
SELECT * FROM information_schema.tiflash_replica;
SELECT * FROM information_schema.CLUSTER_HARDWARE WHERE type = 'tiflash' AND DEVICE_TYPE = 'disk' AND name = 'path';

34 admin 命令

贡献者：@我是咖啡哥

ADMIN SHOW DDL JOBS;
ADMIN CHECK TABLE t_test;
ADMIN SHOW SLOW;
ADMIN SHOW TELEMETRY;

35 修改隔离参数

贡献者：@我是咖啡哥

● session 级别修改 Engine 隔离：默认：[“tikv”, “tidb”, “tiflash”] 由于 TiDB Dashboard 等组件需要读取一些存储于 TiDB 内存表区的系统表，因此建议实例级别 engine 配置中始终加入 “tidb” engine。

SET SESSION tidb_isolation_read_engines = 'tiflash,tidb';

SET @@session.tidb_isolation_read_engines = 'tiflash,tidb';

● 手工 Hint

SELECT /*+ read_from_storage(tiflash[table_name]) */ ... FROM table_name;
SELECT /*+ read_from_storage(tiflash[alias_a, alias_b]) */ ...
FROM table_name_1 AS alias_a, table_name_2 AS alias_b
WHERE alias_a.column_1 = alias_b.column_2;
SET @@tidb_allow_mpp = 1;
SHOW CONFIG WHERE name LIKE '%oom%' AND type = 'tidb';
ADMIN SHOW DDL;

36 排错-查看日志

贡献者：@我是咖啡哥

SELECT *
FROM INFORMATION_SCHEMA.CLUSTER_LOG t
WHERE time > '2022-08-09 00:00:00'
  AND time < '2022-08-10 00:00:00'
  AND TYPE IN ('tikv')
  AND `LEVEL` = 'ERROR'
ORDER BY time DESC;

37 查询所有节点所在 OS 的 CPU 当前使用率

贡献者：@人如其名

SELECT
    b.time,
    a.hostname,
    a.ip,
    a.types,
    b.cpu_used_percent
FROM
    (
        SELECT
            GROUP_CONCAT(TYPE) AS TYPES,
            SUBSTRING_INDEX(instance, ':', 1) AS ip,
            value AS hostname
        FROM
            information_schema.cluster_systeminfo
        WHERE
            name = 'kernel.hostname'
        GROUP BY
            ip, hostname
    ) a,
    (
        SELECT
            time,
            SUBSTRING_INDEX(instance, ':', 1) AS ip,
            (100 - value) AS cpu_used_percent
        FROM
            metrics_schema.node_cpu_usage
        WHERE
            MODE = 'idle'
            AND time = NOW()
    ) b
WHERE
    a.ip = b.ip

输出示例：

SELECT
    time,
    hostname,
    ip,
    types,
    cpu_used_percent
FROM
    your_table_name
WHERE
    time = '2023-01-10 22:40:15.000000';

-- Output:
-- +----------------------------+-----------------------+----------------+----------------------+--------------------+
-- | time                       | hostname              | ip             | types                | cpu_used_percent    |
-- +----------------------------+-----------------------+----------------+----------------------+--------------------+
-- | 2023-01-10 22:40:15.000000 | localhost.localdomain | 192.168.31.201 | tidb,pd,tikv,tiflash | 11.438079153798114 |
-- +----------------------------+-----------------------+----------------+----------------------+--------------------+
-- 1 row in set (0.04 sec)

说明：我这里所有类型组件只创建了有一个而且都在一个 os 上，所以只显示了一行。

38 清理 tidb 大量数据的脚本，实现删除百万级别以上的数据，而且不影响 tidb 正常使用

贡献者：@xingzhenxiang

#!/bin/bash

date1=$(date --date "7 days ago" +"%Y-%m-%d")
delete_db_sql="delete from mysql_table where create_date_time<'$date1' limit 10000"
i=0

while ((++i)); do
    a=$(/bin/mysql -uroot -p123456 -A mysql_database -h127.0.0.1 --comments -e "${delete_db_sql}" -vvv | grep "Query OK" | awk '{print $3}')
    if ((a < 1)); then
        break
    fi
    sleep 1
    printf "%-4d" $((i))
done

✨感谢以上 TiDBer 们贡献的 SQL 脚本～记得点赞收藏，可以随时在你的个人收藏夹里查看到~

未来我们将继续发布更多来自社区的精品内容，希望能为大家提供更多有价值的信息和经验。如果您有任何感兴趣的话题，可以在下方留言，我们会整理相关的资料与大家分享哦！

[tidb-download-button]