[TiDB Community Wisdom Collection] Comprehensive Guide to TiDB SQL Scripts

translator_bot · June 22, 2024, 7:51pm

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

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

| username: 社区小助手

Thank you very much to all the TiDBers for providing various commonly used scripts in the previous 【TiDBer Chat Tea Party 48】Informal TiDB-related SQL Script Collection Contest!.
This post collects and organizes the TiDB-related SQL script collection recommended by everyone.
TiDBers are welcome to continue to supplement and update~

1. Cache Table:

Contributor: @ShawnYan

alter table xxx cache|nocache;

2. TSO Time Conversion:

Contributor: @我是咖啡哥

Method 1: Using the function 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)

Method 2: Using 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. Read Historical Data

Contributor: @我是咖啡哥

Use AS OF TIMESTAMP syntax to read historical data, which can be used in the following three ways:

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';

Read historical data through the system variable tidb_read_staleness

Select a timestamp as new as possible within the range from 5 seconds ago to now

set @@tidb_read_staleness="-5";

Read historical data through the system variable tidb_snapshot

Set a special environment variable, which is a session scope variable, meaning to read the latest version before this time

set @@tidb_snapshot="2016-10-08 16:45:26";

Clear this variable to read the latest version of the data

set @@tidb_snapshot="";

4. Query the default duration of tikv_gc_life_time and tikv_gc_safe_point

Contributor: @TiDBer_m6V1BalM

select VARIABLE_NAME, VARIABLE_VALUE from mysql.tidb where VARIABLE_NAME like "tikv_gc%";

5. Search for TopN slow queries of a user

Contributor: @fanruinet

select query_time,query,user
from information_schema.slow_query
where is_internal=false -- Exclude TiDB internal slow query SQL
and user = "user1" -- Username to search for
order by query_time desc
limit 2;

6. Count data at 5-minute intervals

Contributor: @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. Reverse parse digest into SQL text

Contributor: @hey-hoho

select tidb_decode_sql_digests('[“xxxxx”]');

8. View table usage without involving partition tables:

Contributor: @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 table provides resource usage for partitioned and non-partitioned tables:

Contributor: @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. View configuration file parameters in the query analyzer:

Contributor: @Kongdom

show config

The SHOW CONFIG statement is used to display the current configuration applied by each component of TiDB. Please note that configuration and system variables act on different dimensions, do not confuse them. If you want to get system variable information, please use the SHOW VARIABLES syntax.

11. Find the top 10 hot regions by read traffic

Contributor: @BraveChen

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

12. Script to view parameters and variables

Contributor: @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

You can also use grep to filter once more

[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. Find duplicate records

Contributor: @ealam_小羽

select *
from table
where duplicate_field in
(
select duplicate_field
from table
group by duplicate_field
having count(*)>1
)

14. Query the slowest SQL

Contributor: @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. The most commonly used SQL for daily maintenance

Contributor: @tracy0984

select * from information_schema.cluster_processlist;

– kill id;

16. Restore data (suitable for drop and truncate)

Contributor: @凌云Cloud

FLASHBACK TABLE target_table_name[TO new_table_name]

17. Batch modify database names

Contributor: @TiDBer_dog

./bat_rename.sh lihongbao/ dev2_kelun dev2_sinodemo path./leo_backup

18. Get SQL in high concurrency scenarios

Contributor: @jiawei

select * from information_schema.processlist where info is not null

19. View the tables under the schema

Contributor: @Ming

show tables in schema;

20. View table leader

Contributor: @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. Naughty shell acceleration script

Contributor: @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. Restore data to a new database

Contributor: @TiDBer_徐川

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

23. Enable TiFlash

Contributor: @TiDBer_pFFcXLgY

alter table xxx set tiflash replica 1

24. Table region distribution statement:

Contributor: @秋枫之舞

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. View column metadata

Contributor: @张雨齐0720

show stats_histograms where db_name like 'test' and table_name like 'test1' ;

26. Table storage location (store, peer information)

Contributor: @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
INNER JOIN INFORMATION_SCHEMA.TIKV_REGION_PEERS c
INNER JOIN INFORMATION_SCHEMA.TIKV_STORE_STATUS d
WHERE a.TIDB_TABLE_ID = b.TABLE_ID
AND b.REGION_ID = c.REGION_ID
AND c.STORE_ID = d.STORE_ID
AND a.TABLE_SCHEMA = 'test'
AND a.TABLE_NAME = 't';

27. Upgrade the cluster to a specified version (online upgrade):

Contributor: @TiDBer_杨龟干外公

tiup cluster upgrade

For example, upgrade to version v4.0.0:
tiup cluster upgrade tidb-test v4.0.0

28. Query table size

Contributor: @我是咖啡哥

SELECT
t.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_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. Statistics

Contributor: @我是咖啡哥

View table metadata

show stats_meta where db_name like '%sbtest%';

View table health status

show stats_healthy;

The Healthy field, generally tables with a value less than or equal to 60 need to be analyzed

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’ ;

View histogram information

show stats_buckets where db_name='' and table_name='';

View analyze status

show analyze status;

Analyze table, partition

analyze table sbtest1;
ANALYZE TABLE xxx PARTITION P202204;

30. Execution Plan

Contributor: @我是咖啡哥

Bind execution plan

Default is session level

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;

Use explain format = ‘verbose’ statement to view the execution plan of SQL

explain format = 'verbose';

drop binding for sql;

31. View regions

Contributor: @我是咖啡哥

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. Count read and write hot tables

Contributor: @我是咖啡哥

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

Contributor: @我是咖啡哥

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 commands

Contributor: @我是咖啡哥

admin show ddl jobs;
ADMIN CHECK TABLE t_test;
admin show slow 
ADMIN SHOW TELEMETRY;

35. Modify isolation parameters

Contributor: @我是咖啡哥

Modify at session level

Engine isolation: Default: [“tikv”, “tidb”, “tiflash”]
Since components such as TiDB Dashboard need to read some system tables stored in the TiDB memory table area, it is recommended to always include the “tidb” engine in the instance-level engine configuration.

set session tidb_isolation_read_engines = 'tiflash,tidb';

set @@session.tidb_isolation_read_engines = "tiflash,tidb";

Manual 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 @@