• 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

    1. SELECT TIDB_PARSE_TSO(437447897305317376);
    2. +------------------------------------+
    3. | TIDB_PARSE_TSO(437447897305317376) |
    4. +------------------------------------+
    5. | 2022-11-18 08:28:17.704000         |
    6. +------------------------------------+
    7. 1 row in set (0.25 sec)

    ● 方法二：使用 pd-ctl

    1. ~$ tiup ctl:v6.4.0 pd -i -u http://pdip:2379
    2. Starting component `ctl`: /Users/xxx/.tiup/components/ctl/v6.4.0/ctl pd -i -u http://pdip:2379
    3. » tso 437447897305317376
    4. system:  2022-11-18 08:28:17.704 +0800 CST
    5. 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

    1. select * from t as of timestamp '2021-05-26 16:45:26';
    2. start transaction read only as of timestamp '2021-05-26 16:45:26';
    3. 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

    1. select query_time,query,user
    2. from information_schema.slow_query
    3. where is_internal=false -- 排除 TiDB 内部的慢查询 SQL
    4. and user = "user1" -- 查找的用户名
    5. order by query_time desc
    6. limit 2;

    6 统计间隔 5 分钟的数据

    贡献者：@forever

    1. SELECT concat(date_format(create_time,‘%Y-%m-%d %H:’),floor(date_format(create_time,‘%i’)/5)),count(*)
    2. FROM jcxx
    3. GROUP BY 1;

    7 反解析 digest 成 SQL 文本

    贡献者：@hey-hoho

    select tidb_decode_sql_digests(‘[“xxxxx”]’);

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

    贡献者：@xfworld

    1. select TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS,
    2. (DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 as table_size from tables order by table_size 
    3. desc limit 20;

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

    贡献者：@xfworld

    1. select TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,TABLE_ROWS,
    2. (DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 as table_size from 
    3. 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

    1. SELECT DISTINCT region_id 
    2. FROM INFORMATION_SCHEMA.tikv_region_status 
    3. WHERE READ_BYTES > ?
    4. ORDER BY READ_BYTES DESC 
    5. LIMIT 10

    12 查看参数和变量的脚本

    贡献者：@buddyyuan

    1. #!/bin/bash
    2.  
    3. case $1 in
    4. -pd)
    5. mysql -uroot -h127.0.0.1 -P4000 -p"" -e "SHOW CONFIG WHERE type ='pd' and name like '%$2%'"
    6. ;;
    7. -tidb)
    8. mysql -uroot -h127.0.0.1 -P4000 -p"" -e "SHOW CONFIG WHERE type ='tidb' and name like '%$2%'"
    9. ;;
    10. -tikv)
    11. mysql -uroot -h127.0.0.1 -P4000 -p"" -e "SHOW CONFIG WHERE type ='tikv' and name like '%$2%'"
    12. ;;
    13. -tiflash)
    14. mysql -uroot -h127.0.0.1 -P4000 -p"" -e "SHOW CONFIG WHERE type ='tiflash' and name like '%$2%'"
    15. ;;
    16. -var)
    17. mysql -uroot -h127.0.0.1 -P4000 -p"" -e "show variables like '%$2%';"
    18. ;;
    19. -h)
    20. echo "-pd       show pd parameters" 
    21. echo "-tidb     show tidb parameters"
    22. echo "-tikv     show tikv parameters"
    23. echo "-tiflash  show tiflash parameters"
    24. echo "-var      show itidb variables"
    25. ;;
    26. esac

    还能用 grep 在过滤一次

    1. [root@vm172-16-201-125 ~]# sh showparammeter.sh -tikv memory-pool-quota | grep -i "210:29160"
    2. tikv    192.16.201.210:29160    server.grpc-memory-pool-quota   9223372036854775807B

    13 查找重复记录

    贡献者：@ealam_ 小羽

    1. select *
    2. from 表
    3. where 重复字段 in
    4. (
    5. select 重复字段
    6. from 表
    7. group by 重复字段
    8. having count(*)>1
    9. )

    14 查询耗时最高的慢 sql

    贡献者：@caiyfc

    1. select query sql_text,
    2.        sum_query_time,
    3.        mnt as executions,
    4.        avg_query_time,
    5.        avg_proc_time,
    6.        avg_wait_time,
    7.        max_query_time,
    8.        avg_backoff_time,
    9.        Cop_proc_addr,
    10.        digest,
    11.        (case
    12.          when avg_proc_time = 0 then
    13.           'point_get or commit'
    14.          when (avg_proc_time > avg_wait_time and
    15.               avg_proc_time > avg_backoff_time) then
    16.           'coprocessor_process'
    17.          when (avg_backoff_time > avg_wait_time and
    18.               avg_proc_time < avg_backoff_time) then
    19.           'backoff'
    20.          else
    21.           'coprocessor_wait'
    22.        end) as type
    23.   from (select substr(query, 1, 100) query,
    24.                count(*) mnt,
    25.                avg(query_time) avg_query_time,
    26.                avg(process_time) avg_proc_time,
    27.                avg(wait_time) avg_wait_time,
    28.                max(query_time) max_query_time,
    29.                sum(query_time) sum_query_time,
    30.                digest,
    31.                Cop_proc_addr,
    32.                avg(backoff_time) avg_backoff_time
    33.           from information_schema.cluster_slow_query
    34.          where time >= '2022-07-14 17:00:00'
    35.            and time <= '2022-07-15 17:10:00'
    36.            and DB = 'web'
    37.          group by substr(query, 1, 100)) t
    38.  order by max_query_time desc limit 20;

    15 日常维护用的最多的 SQL

    贡献者：@tracy0984

    1. select * from information_schema.cluster_processlist;
    2.  
    3. – 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

    1. alias ctidb=“mysql -u root -ptidb -Dcktest -h S001 -P4000”
    2.  
    3. alias dtidb=“tiup cluster display tidb-test”
    4.  
    5. alias etidb=“tiup cluster edit-config tidb-test”
    6.  
    7. alias ptidb=“tiup cluster prune tidb-test”
    8.  
    9. 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 分布语句

    贡献者：@秋枫之舞

    1. select
    2. trs.db_name,
    3. trs.table_name,
    4. trs.index_name,
    5. trp.store_id,
    6. count(*),
    7. sum(approximate_keys)
    8. from
    9. information_schema.tikv_region_status trs,
    10. information_schema.tikv_store_status tss,
    11. information_schema.tikv_region_peers trp
    12. where
    13. trs.db_name = ‘prd01’
    14. and trs.table_name = ‘tab_name’
    15. and trp.is_leader = 1
    16. and trp.store_id = tss.store_id
    17. and trs.region_id = trp.region_id
    18. group by
    19. trs.db_name,
    20. trs.table_name,
    21. trs.index_name,
    22. trp.store_id
    23. order by
    24. trs.index_name;

    25 查看列的元数据

    贡献者：@张雨齐0720

    show stats_histograms where db_name like ‘test’ and table_name like ‘test1’ ;

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

    贡献者：@bert

    1. SELECT distinct a.TIDB_TABLE_ID, b.DB_NAME, b.TABLE_NAME, b.REGION_ID, b.APPROXIMATE_SIZE
    2. , c.PEER_ID, c.STORE_ID, c.IS_LEADER, c.STATUS, d.ADDRESS
    3. , d.STORE_STATE_NAME, d.VERSION, d.CAPACITY, d.AVAILABLE, d.LABEL
    4. FROM INFORMATION_SCHEMA.TABLES a
    5. INNER JOIN INFORMATION_SCHEMA.TIKV_REGION_STATUS b
    6. INNER JOIN INFORMATION_SCHEMA.TIKV_REGION_PEERS c
    7. INNER JOIN INFORMATION_SCHEMA.TIKV_STORE_STATUS d
    8. WHERE a.TIDB_TABLE_ID = b.TABLE_ID
    9. AND b.REGION_ID = c.REGION_ID
    10. AND c.STORE_ID = d.STORE_ID
    11. AND a.TABLE_SCHEMA = ‘test’
    12. AND a.TABLE_NAME = ‘t’;

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

    贡献者：@TiDBer_ 杨龟干外公

    tiup cluster upgrade

    例如升级到 v4.0.0 版本：

    tiup cluster upgrade tidb-test v4.0.0

    28 查询表大小

    贡献者：@我是咖啡哥

    1. SELECT
    2. t.TABLE\_NAME,
    3. t.TABLE\_ROWS,
    4. t.TABLE\_TYPE,
    5. round(t.DATA\_LENGTH/1024/1024/1024,2) data\_GB,
    6. round(t.INDEX\_LENGTH/1024/1024/1024,2) index\_GB,
    7. t.CREATE\_OPTIONS,
    8. t.TABLE\_COMMENT
    9. FROM
    10. INFORMATION\_SCHEMA.`TABLES` t
    11. WHERE
    12. table\_schema = 'test'
    13. and t.table\_type='BASE TABLE'
    14. order by t.TABLE\_ROWS desc;
    15.  
    16. 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

    1. show stats\_healthy where table\_name ='xxx';
    2. show stats\_healthy where db\_name='' and table\_name='orders';
    3. _name like ‘sbtest’ and table_name like ‘sbtest1’ ;

    ● 查看直方图信息

    show stats\_buckets where db\_name='' and table\_name='';

    ● 查看 analyze 状态

    show analyze status;

    ● 分析表、分区

    1. analyze table sbtest1;
    2. ANALYZE TABLE xxx PARTITION P202204;

    30 执行计划

    贡献者：@我是咖啡哥

    绑定执行计划

    ● 默认是 session 级别

    1. create binding for  select \* from t  using select \* from t use index()
    2.  
    3. 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 = ?;
    4.  
    5. explain SELECT  \* FROM t1 INNER JOIN t2 ON t1.id = t2.t1\_id WHERE t1.int\_col = 1;
    6.  
    7. show bindings for SELECT  \* FROM t1 INNER JOIN t2 ON t1.id = t2.t1\_id WHERE t1.int\_col = 1;
    8.  
    9. show global bindings;
    10. show session bindings;
    11. SELECT @@SESSION.last\_plan\_from\_binding;

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

    1. explain format = 'verbose';
    2.  
    3. drop binding for sql;

    31 查看 regions

    贡献者：@我是咖啡哥

    1. SHOW TABLE t\_its\_unload\_priority\_intermediate\_info regions;
    2. SHOW TABLE t\_its\_unload\_priority\_intermediate\_info INDEX IDX\_UPII\_GROUP\_BY\_COMPOSITE regions;

    32 统计读写热点表

    贡献者：@我是咖啡哥

    1. use INFORMATION\_SCHEMA;
    2.  
    3. SELECT
    4. db\_name,
    5. table\_name,
    6. index\_name,
    7. type,
    8. sum( flow\_bytes ),
    9. count( 1 ),
    10. group\_concat( h.region\_id ),
    11. count( DISTINCT p.store\_id ),
    12. group\_concat( p.store\_id )
    13. FROM
    14. INFORMATION\_SCHEMA.tidb\_hot\_regions h
    15. JOIN INFORMATION\_SCHEMA.tikv\_region\_peers p ON h.region\_id = p.region\_id
    16. AND p.is\_leader = 1
    17. GROUP BY
    18. db\_name,
    19. table\_name,
    20. index\_name,
    21. type;
    22.  
    23. SELECT
    24. p.store\_id,
    25. sum(flow\_bytes ),
    26. count(1)
    27. FROM
    28. INFORMATION\_SCHEMA.tidb\_hot\_regions h
    29. JOIN INFORMATION\_SCHEMA.tikv\_region\_peers p ON h.region\_id = p.region\_id
    30. AND p.is\_leader = 1
    31. GROUP BY
    32. p.store\_id
    33. ORDER BY
    34. 2 DESC;
    35.  
    36. select tidb\_decode\_plan();

    33 TiFlash

    贡献者：@我是咖啡哥

    1. ALTER TABLE t\_test\_time\_type SET TIFLASH REPLICA 1;
    2. SELECT \* FROM information\_schema.tiflash\_replica;
    3.  
    4. select \* from information\_schema.CLUSTER\_HARDWARE where type='tiflash' and DEVICE\_TYPE='disk' and name='path';

    34 admin 命令

    贡献者：@我是咖啡哥

    1. admin show ddl jobs;
    2. ADMIN CHECK TABLE t_test;
    3. admin show slow 
    4. ADMIN SHOW TELEMETRY;

    35 修改隔离参数

    贡献者：@我是咖啡哥

    ● session 级别修改

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

    1. set session tidb\_isolation\_read\_engines = 'tiflash,tidb';
    2. set @@session.tidb\_isolation\_read\_engines = "tiflash,tidb";

    ● 手工 Hint

    1. select /\*+ read\_from\_storage(tiflash\[table\_name]) */ ... from table\_name;
    2. 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;
    3.  
    4. set @@tidb\_allow\_mpp=1;
    5.  
    6. show  config where name like '%oom%' and type='tidb';
    7.  
    8. admin show ddl;

    36 排错-查看日志

    贡献者：@我是咖啡哥

    1. SELECT \* FROM INFORMATION\_SCHEMA.CLUSTER\_LOG t
    2. WHERE time > '2022-08-09 00:00:00' AND time < '2022-08-10 00:00:00'
    3. AND TYPE in ('tikv')
    4. AND `LEVEL` = 'ERROR'
    5. ORDER BY time desc;

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

    贡献者：@人如其名

    1. SELECT
    2.   b.time,
    3.   a.hostname,
    4.   a.ip,
    5.   a.types,
    6.   b.cpu_used_percent
    7. FROM
    8.   (
    9.     SELECT
    10.       GROUP_CONCAT(TYPE) AS TYPES,
    11.       SUBSTRING_INDEX(instance, ':', 1) AS ip,
    12.       value AS hostname
    13.     FROM
    14.       information_schema.cluster_systeminfo
    15.     WHERE
    16.       name = 'kernel.hostname'
    17.     GROUP BY
    18.       ip,
    19.       hostname
    20.   ) a,
    21.   (
    22.     SELECT
    23.       time,
    24.       SUBSTRING_INDEX(instance, ':', 1) AS ip,
    25. (100 - value) AS cpu_used_percent
    26.     FROM
    27.       metrics_schema.node_cpu_usage
    28.     WHERE
    29.       MODE = 'idle'
    30.       AND time = NOW()
    31.   ) b
    32. WHERE
    33.   a.ip = b.ip

    输出示例：

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

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

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

    贡献者：@xingzhenxiang

    1. date1=`date --date "7 days ago" +"%Y-%m-%d"`
    2. delete_db_sql=“delete from mysql_table where create_date_time<‘$date1’ limit 10000”
    3. ​
    4. i=0
    5. ​
    6. while ((++i)); do
    7. a=`/bin/mysql -uroot -p123456 -A mysql_database -h127.0.0.1 --comments -e "${delete_db_sql}" -vvv|grep "Query OK" |awk '{print $3}'`
    8. if(($a<1)); then
    9. break 1
    10. fi
    11. sleep 1
    12. printf “%-4d” $((i))

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

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

  • 相关阅读:
    【云原生】快出数量级的性能是怎样炼成的？就提升了亿点点
    Xmind 2022精彩体验---什么叫流程图
    面试必问：Redis 如何实现库存扣减操作？
    Linux常用命令
    umich cv-3-2
    关于个人职业的随笔
    Process assessment techniques-1
    WhisperFusion：具有超低延迟无缝对话功能的AI系统
    年龄大了转嵌入式有机会吗？
    APP如何实现「年轻化」的需求？
  • 原文地址：https://blog.csdn.net/TiDB_PingCAP/article/details/136261549