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

2023-01-13
北京
本文字数：7234 字
阅读完需：约 24 分钟

作者： TiDB 社区小助手原文来源：https://tidb.net/blog/295c778f

简介：

来自社区，回归社区。非常感谢各位 TiDBer 在之前【TiDBer 唠嗑茶话会 48】非正式 TiDB 相关 SQL 脚本征集大赛！里提供的各种常用脚本。这篇专栏文章收集整理了大家推荐的 TiDB 相关 SQL 脚本大全。迎各位 TiDBer 持续补充更新～

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:2379Starting component `ctl`: /Users/xxx/.tiup/components/ctl/v6.4.0/ctl pd -i -u http://pdip:2379» tso 437447897305317376system:  2022-11-18 08:28:17.704 +0800 CSTlogic:   0

复制代码

3. 读取历史数据

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

SELECT … FROM … AS OF TIMESTAMPSTART TRANSACTION READ ONLY AS OF TIMESTAMPSET 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 默认时长

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

贡献者：@fanruinet

select query_time,query,userfrom information_schema.slow_querywhere is_internal=false -- 排除 TiDB 内部的慢查询 SQLand user = "user1" -- 查找的用户名order by query_time desclimit 2;

复制代码

6. 统计间隔 5 分钟的数据

贡献者：@forever

SELECT concat(date_format(create_time,‘%Y-%m-%d %H:’),floor(date_format(create_time,‘%i’)/5)),count(*)FROM jcxxGROUP 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 语法。

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

贡献者： @BraveChen

SELECT DISTINCT region_id FROM INFORMATION_SCHEMA.tikv_region_status WHERE 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. 查找重复记录

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 < avg_backoff_time) then          'backoff'         else          'coprocessor_wait'       end) as type  from (select substr(query, 1, 100) query,               count(*) mnt,               avg(query_time) avg_query_time,               avg(process_time) avg_proc_time,               avg(wait_time) avg_wait_time,               max(query_time) max_query_time,               sum(query_time) sum_query_time,               digest,               Cop_proc_addr,               avg(backoff_time) 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）

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

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 分布语句：

贡献者：@秋枫之舞

selecttrs.db_name,trs.table_name,trs.index_name,trp.store_id,count(*),sum(approximate_keys)frominformation_schema.tikv_region_status trs,information_schema.tikv_store_status tss,information_schema.tikv_region_peers trpwheretrs.db_name = ‘prd01’and trs.table_name = ‘tab_name’and trp.is_leader = 1and trp.store_id = tss.store_idand trs.region_id = trp.region_idgroup bytrs.db_name,trs.table_name,trs.index_name,trp.store_idorder bytrs.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.LABELFROM INFORMATION_SCHEMA.TABLES aINNER JOIN INFORMATION_SCHEMA.TIKV_REGION_STATUS bINNER JOIN INFORMATION_SCHEMA.TIKV_REGION_PEERS cINNER JOIN INFORMATION_SCHEMA.TIKV_STORE_STATUS dWHERE a.TIDB_TABLE_ID = b.TABLE_IDAND b.REGION_ID = c.REGION_IDAND c.STORE_ID = d.STORE_IDAND 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. 查询表大小

贡献者：@我是咖啡哥

SELECTt.TABLE\_NAME,t.TABLE\_ROWS,t.TABLE\_TYPE,round(t.DATA\_LENGTH/1024/1024/1024,2) data\_GB,round(t.INDEX\_LENGTH/1024/1024/1024,2) index\_GB,t.CREATE\_OPTIONS,t.TABLE\_COMMENTFROMINFORMATION\_SCHEMA.`TABLES` tWHEREtable\_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;
SELECTdb\_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 )FROMINFORMATION\_SCHEMA.tidb\_hot\_regions hJOIN INFORMATION\_SCHEMA.tikv\_region\_peers p ON h.region\_id = p.region\_idAND p.is\_leader = 1GROUP BYdb\_name,table\_name,index\_name,type;
SELECTp.store\_id,sum(flow\_bytes ),count(1)FROMINFORMATION\_SCHEMA.tidb\_hot\_regions hJOIN INFORMATION\_SCHEMA.tikv\_region\_peers p ON h.region\_id = p.region\_idAND p.is\_leader = 1GROUP BYp.store\_idORDER BY2 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 tWHERE 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_percentFROM  (    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()  ) bWHERE  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<‘$date1’ limit 10000”
i=0
while ((++i)); doa=`/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)); thenbreak 1fisleep 1printf “%-4d” $((i))

复制代码

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

ps: 如何收藏主题？

发布于: 刚刚阅读数: 2

原文链接:【http://xie.infoq.cn/article/847513dd05b4a1c63e5a52618】。文章转载请联系作者。

TiDB 社区干货传送门

关注

TiDB 社区官网:https://tidb.net/ 2021-12-15 加入

TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目，旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/

发布

暂无评论

创作场景

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

1. 缓存表：

贡献者：@ShawnYan

2. TSO 时间转换：

贡献者： @我是咖啡哥

方法一：使用函数 TIDB_PARSE_TSO

方法二：使用 pd-ctl

3. 读取历史数据

贡献者： @我是咖啡哥

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

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

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

4. 查询 tikv_gc_life_time 和 tikv_gc_safe_point 默认时长

贡献者： @TiDBer_m6V1BalM

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

贡献者：@fanruinet

6. 统计间隔 5 分钟的数据

贡献者：@forever

7. 反解析 digest 成 SQL 文本

贡献者：@hey-hoho

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

贡献者：@xfworld

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

贡献者：@xfworld

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

贡献者：@Kongdom

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

贡献者： @BraveChen

12. 查看参数和变量的脚本

贡献者： @buddyyuan

13. 查找重复记录

贡献者：@ealam_ 小羽

14. 查询耗时最高的慢 sql

贡献者：@caiyfc

15. 日常维护用的最多的 SQL

贡献者：@tracy0984

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

贡献者：@凌云 Cloud

17. 批量修改库名

贡献者：@TiDBer_dog

18. 高并发的场景下 获取 sql

贡献者：@jiawei

19. 查看 schema 下的表都有哪些

贡献者：@Ming

20. 查看表 leader

贡献者： @TiDBer_wTKU9jv6

21. shell 的调皮加速脚本

贡献者：@gcworkerishungry

22. 恢复数据到新的数据库

贡献者：@TiDBer_ 徐川

23. 开启 tiflash

贡献者：@TiDBer_pFFcXLgY

24. 表 region 分布语句：

贡献者：@秋枫之舞

25. 查看列的元数据

贡献者：@张雨齐 0720

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

贡献者： @bert

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

贡献者：@TiDBer_ 杨龟干外公

28. 查询表大小

贡献者：@我是咖啡哥

29. 统计信息

贡献者：@我是咖啡哥

查看表的元数据

查看表的健康状态

查看直方图信息

查看 analyze 状态

分析表、分区

30. 执行计划

贡献者：@我是咖啡哥

绑定执行计划

默认是 session 级别

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

31. 查看 regions

贡献者：@我是咖啡哥

32. 统计读写热点表

贡献者：@我是咖啡哥

33. TiFlash

18. 高并发的场景下获取 sql