国产化数据库tidb数据库常用运维sql总结--延续更新

数据库记录数、数据容量、索引容器统计：

select 
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

tidb大表前20统计：

select concat_ws (',',now(),table_schema,table_name,table_rows,truncate(data_length/1024/1024/1024, 2),truncate(index_length/1024/1024/1024, 2)) as tidb_table_head from  INFORMATION_SCHEMA.TABLES  order by data_length desc, index_length desc limit 20;

数据条数超1千万且索引小于3：

select  concat_ws (',',now(),c.table_schema,c.tb,table_rows,num)  as tidb_table_head from (select * from (select table_schema,table_name as tb,table_rows from  INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA ='数据库名' and table_rows > 10000000) a ,(SELECT TABLE_NAME,COUNT(1) as num FROM (SELECT TABLE_NAME ,KEY_NAME  FROM INFORMATION_SCHEMA.TIDB_INDEXES      WHERE TABLE_SCHEMA ='数据库名' GROUP BY TABLE_NAME ,KEY_NAME ) T GROUP BY  TABLE_NAME HAVING COUNT(1)<3) b where a.tb = b.TABLE_NAME) c;  

收集超10秒慢sql：

select concat_ws('|@|',now(),time,Query_time,Parse_time,Compile_time,Rewrite_time,Optimize_time,Total_keys,Process_keys,Mem_max,Plan,Query) as tidb_table_head from INFORMATION_SCHEMA.CLUSTER_SLOW_QUERY where Query_time > 10;  

批量生成表分析sql：

select  concat ( 'analyze table ',table_name,';') FROM information_schema.tables WHERE table_schema = '数据库名' ;

批量生成删表sql：

select  concat ( 'drop table ',table_name,';') FROM information_schema.tables WHERE table_schema = '数据库名' ;

执行sql语句：

mysql_config_editor set --login-path=test --user=test --host=127.0.0.1 --port=3306 --password
##使用mysql --login-path，需要先set
mysql --login-path=test -Dtest -vvv -A < ddl_xxx_20240529_001.sql  > ddl_xxx_20240529_001.sql

查tidb版本：

select version();

查无主键或非空唯一索引：

SELECT 
'无主键或者非空唯一索引',
concat(table_schema, '.', table_name)
FROM 
INFORMATION_SCHEMA.TABLES b
WHERE 
b.TABLE_SCHEMA NOT IN ('METRICS_SCHEMA', 'mysql', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA', 'test')
AND (table_schema, table_name) NOT IN 
(
SELECT 
TABLE_SCHEMA, TABLE_NAME 
FROM
(
SELECT 
TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, GROUP_CONCAT(COLUMN_NAME) AS c, GROUP_CONCAT(NULLABLE) AS n
FROM 
INFORMATION_SCHEMA.STATISTICS
WHERE 
NON_UNIQUE = 0 
AND TABLE_SCHEMA NOT IN ('METRICS_SCHEMA', 'mysql', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA', 'test')
GROUP BY 
TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
) AS tmp
WHERE 
tmp.n NOT LIKE '%YES%'
) and table_type='BASE TABLE' ;

查序列：

select table_schema,table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type='SEQUENCE' and
TABLE_SCHEMA not in ('METRICS_SCHEMA','mysql','INFORMATION_SCHEMA','PERFORMANCE_SCHEMA','test');