MySQL：**** 备用SQL ***

1、 查看mysql数据库表使用情况（information_schema 库中）。根据占用大小降序。

SELECT * FROM `TABLES` order by DATA_LENGTH desc ;

TABLE_SCHEMA : 数据库名

TABLE_NAME：表名

ENGINE：所使用的存储引擎

TABLES_ROWS：记录数

DATA_LENGTH：数据大小

INDEX_LENGTH：索引大小

 

2、查看mysql数据文件位置。

show global variables like "%datadir%";

 

3、查询重复记录(可以以多个列为组)

select column_2 ,column3,count(*)  from table_name where column4 = 'x'  group by column_2 ,column3 having count(*) > 1

4、查询版本信息

select version();

5、查看数据库连接情况

show full processlist;  

6、查看缓存配置

show variables like '%query_cache%'

have_query_cache：是否在安装时已配置告诉缓存

query_cache_size：缓存区大小，单位MB

query_cache_type:0或者off缓存关闭，1或者on缓存打开，2或者demand（带有SQL_CACHE的select语句提供高速缓存）

 

-- 一、了解各种SQL的执行频率
show global status like 'Com_select'; -- 执行select操作的次数
show global status like 'Com_insert';
show global status like 'Com_update';
show global status like 'Com_delete';

show global status like 'Innodb_rows_read'; -- select 查询返回的行数
show global status like 'Innodb_rows_inserted'; 
show global status like 'Innodb_rows_updated';
show global status like 'Innodb_rows_deleted';

show global status like 'Connections'; -- 试图连接MySQL服务器的次数
show global status like 'Uptime';	-- 服务器工作时间
show global status like 'Slow_queries'; -- 慢查询的次数


show variables like '%slow%';	-- 查看慢查询日志的相关信息
show variables like 'long_query_time'; -- 指定记录慢查询日志SQL执行时间得伐值（单位：秒，默认10秒）
show variables like 'slow_query_log';	-- 慢查询日志的开关
show variables like 'slow_query_log_file'; -- 指定慢查询日志得存储路径及文件（默认和数据文件放一起）
show variables like 'log_queries_not_using_indexes'; -- 是否记录未使用***索引***的SQL
show variables like 'log_output'; --  日志存放的地方【TABLE】【FILE】【FILE,TABLE】（文件 和/或 表）
-- set global show_query_log=on; -- 开启慢查询日志

-- show processlist
select * from information_schema.processlist ; -- 查询当前MySQL在进行的线程


-- 二、通过执行计划查看SQL效率
EXPLAIN select id from bs_demo where id = 5;	-- 查看执行计划
EXPLAIN select * from bs_demo; 



-- 三、通过profile分析SQL。
select @@have_profiling ; 	-- 查看是否支持profile
select @@profiling;	-- 查看profile的开启状态
-- set profiling =1; -- 开启profile
select * from bs_demo;	-- 1、执行SQL
show profiles; -- 2、查看当前的profile内容和编号（找到第一步执行的SQL的编号）。
show profile for query 74; -- 3、查看指定编号的profile内容（每个状态和耗时）。
show profile cpu for query 28; -- 3、查看指定编号的profile在 cpu 资源上的耗时 (cpu可替换：all/block/io/context/switch/page faults等)
-- show profile all for query 59;

 

-- 四、trace分析优化器

SELECT * FROM information_schema.OPTIMIZER_TRACE;

7、查询、删除重复的记录

假设表user 存在2个列， id ,name 。 2列应为唯一且1对1 关系。 若存在重复的名字，则认为数据重复。

首先查询重复的记录，即记录超过1条。
 

select  name from  user group by name having count(*) > 1 -- 查询重复的名称

select * from user where name in (select name from user group by name having count(*) having count(*) > 1) -- 查询重复名称的记录行

删除重复的记录，这里的策略是，保留最小的id，将其余的都删除。
 

select min(id) from user group by name having count(*) > 1 -- 查询重复名称的记录并从中筛选出最小id的id值。

select name  from user group by name having count(*) > 1  -- 查询所有名称重复的记录

delete from user where name in (select name from user group by name having count(*) > 1) 
and id not in (select min(id) from user group by name having count(*) > 1) -- 删除除了最小id以外的所有名称重复的记录。


-- 这条与上一条效果一样，逻辑很精妙
delete from user A where id > (select min(id) from user B where A.name = B.name) -- 这是个自连接，当name重复时，会删除除最小id以外的记录，当name不重复时，id = min(id) 故不会删除。