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

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

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

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

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 在过滤一次

[root@vm172-16-201-125 ~]# 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 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 = '2022-07-14 17:00:00'
           and time  '2022-08-09 00:00:00' AND time  
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
 
输出示例：
 
+----------------------------+-----------------------+----------------+----------------------+--------------------+
| 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
 
date1=`date --date "7 days ago" +"%Y-%m-%d"`
delete_db_sql=“delete from mysql_table where create_date_time