MySQL常用命令

最新推荐文章于 2025-12-26 14:31:53 发布

原创最新推荐文章于 2025-12-26 14:31:53 发布 · 470 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

本文介绍了MySQL中的几个常用命令，包括统计表数据量、计算数据库大小、SQL语句耗时分析、创建存储过程以及创建定时任务来自动更新效率得分视图。

MySQL常用命令

一、统计表数据量

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 ), 4 ), 'KB' ) AS 'Total' 
FROM
	information_schema.TABLES 
WHERE
	table_schema LIKE 'db_name' and table_name = 'table_name';

统计数据库大小

	SELECT 
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024/1024, 2)) as '数据容量(GB)',
sum(truncate(index_length/1024/1024/1024, 2)) as '索引容量(GB)',
sum(truncate(DATA_FREE/1024/1024/1024, 2)) as '碎片占用(GB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

sql语句具体耗时：

SHOW PROFILES;

show profile cpu,block io for query 92;

#创建存储过程
drop procedure if exists efficiency_object_score_quarter_create_view;
CREATE PROCEDURE efficiency_object_score_quarter_create_view()
		BEGIN
		DROP VIEW IF EXISTS efficiency_object_score_quarter_view;
		#动态变量
		SET @table_name= (SELECT CONCAT('efficiency_object_score_quarter_',MAX(`quarter`))  FROM efficiency_object_score_stat_record);
		set @execSql=CONCAT("CREATE VIEW efficiency_object_score_quarter_view AS  SELECT * FROM ",@table_name) ;
		SELECT @execSql;
		PREPARE pr1 FROM @execSql;
		EXECUTE  pr1; # 最终执行语句
		DEALLOCATE PREPARE pr1 ; # 释放资源
	END;

#创建定时任务
drop event efficiency_object_score_quarter_create_view_event;
	
CREATE EVENT IF NOT EXISTS efficiency_object_score_quarter_create_view_event
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
#ON SCHEDULE EVERY 1 MINUTE
ON COMPLETION PRESERVE
COMMENT '定时重建效能得分视图'
DO call efficiency_object_score_quarter_create_view;