解析sql执行过程
show VARIABLES like '%profil%' //查看是否开启了剖析 如没开启setprofiling=1; 启用
show profiles;
set @query_id=37; //sql_id
select STATE,sum(duration) as Total_R,ROUND(100*SUM(DURATION)/(SELECT SUM(DURATION) FROM INFORMATiON_SCHEMA.PROFILING WHERE QUERY_ID=@query_id),2) as Pct_R,
count(*) as calls,sum(duration)/count(*) as "R/Call" from information_schema.profiling
where query_id=@query_id
group by state
order by Total_R desc;
查询数据库中表的数据量最大
select sum(DATA_LENGTH)+sum(INDEX_LENGTH) from information_schema.tables
where table_schema='database-name'
查看数据库中有哪些表(后面的条件是没有数据的表)
SELECT TABLE_NAME TABLES FROM information_schema.TABLES
where table_schema = 'database-name' and table_rows = 0
导出数据库
mysqldump -u username -p target_database > new_sql_filename.sql
导出的表结构sql文件(加d参数)
mysql -u username -p -d database_name < sql_file_name.sql
导出某张表结构
mysqldump -u username -p -d target_database table_name > new_sql_filename.sql
导出多张表结构
mysqldump -u username -p -d target_database tab1_name tab2_name > new_sql_filename.sql
查询表中某字段重复记录
select field_name,count(*) as countfrom table_namegroup by field_name having count>1;
删除重复记录中的一条
deletefromtable_name whereid in(selecta.id from(selectmax(id)id fromtable_name a groupbyfield_name HAVING count(field_name)>1)a )
