查询表大小

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/(102410241024),4),'G') AS 'Data Size', CONCAT(ROUND(index_length/(102410241024),4),'G') AS 'Index Size', CONCAT(ROUND((data_length+index_length)/(102410241024),4),'G') AS'Total' FROM information_schema.TABLES WHERE table_schema LIKE 'test';

统计信息

查看表的元数据

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

查看列的元数据

show stats_histograms where db_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;

执行计划

绑定执行计划

-- 默认是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;

查看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;

热点表问题

PRE_SPLIT_REGIONS 的值必须小于或等于 SHARD_ROW_ID_BITS。

SHARD_ROW_ID_BITS = 4,PRE_SPLIT_REGIONS = 4

--tidb_scatter_region：该变量用于控制建表完成后是否等待预切分和打散 Region 完成后再返回结果。如果建表后有大批量写入，需要设置该变量值为 1， --表示等待所有 Region 都切分和打散完成后再返回结果给客户端。否则未打散完成就进行写入会对写入性能影响有较大的影响。

SHOW VARIABLES LIKE '%tidb_scatter_region%';

慢查询

SELECT * FROM INFORMATION_SCHEMA.CLUSTER_SLOW_QUERY WHERE time > '2022-08-09 00:00:00' ;

select query_time, query from information_schema.slow_query where is_internal = false and user = ”user1” order by query_time desc limit 2;

select query_time, query, digest from information_schema.slow_query where is_internal = false and time between ’2021−09−21’ and ’2021−09−02’ order by query_time desc limit 1;

select query, query_time from information_schema.slow_query where digest = "4751cb6008fda383e22dacb . . . bafb46a6fa";

统计读写热点表

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();

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

admin命令

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

修改隔离参数

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;

排错

查看日志

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;

欢迎大家补充。

TiDB常用SQL