作者： 我是咖啡哥原文来源：https://tidb.net/blog/fcf10009

查询表大小

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

统计信息

查看表的元数据

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_querywhere is_internal = false and user = ”user1” order by query_time desc limit 2;

select query_time, query, digest from information_schema.slow_querywhere 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;

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

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

欢迎大家补充。