数据库记录数、数据容量、索引容器统计:
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();