TSO时间转换

SELECT TIDB_PARSE_TSO(437447897305317376);

查询表大小

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

统计信息

查看表的元数据

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 xxx regions;
SHOW TABLE xxx INDEX idx_xxx 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;