MYSQL常用SQL汇总

1、查看当前应用连接,连接数突增排查

select user,SUBSTRING_INDEX(host,':',1) as ip , count(*) as count,db from information_schema.processlist where host not in ('localhost') and user not in ('replicater') group by ip order by count;

2、查看表所属及大概行数,一般加字段索引时做参考

select TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS from information_schema.tables where TABLE_NAME='表名';

3、查看表碎片,是否需要整理表释放物理空间

select table_name,table_rows,concat(round(DATA_LENGTH/1024/1024, 2), 'MB') as size,DATA_FREE/1024/1024 AS data_free_MB from information_schema.TABLES where table_schema='库名' order by DATA_LENGTH desc;

4、当前有没有锁

select * from information_schema.innodb_locks;

5、当前锁堵塞情况

select * from information_schema.innodb_lock_waits;

6、当前锁等待详细信息

select it.trx_mysql_thread_id, il.lock_id, il.lock_table, il.lock_mode, il.lock_type, it.trx_state, pl.USER||'@'||pl.HOST as user_host, pl.db, pl.command, pl.info, it.trx_started, it.trx_wait_started, now()-trx_wait_started as wait_seconds, il.lock_index, it.trx_weight, it.trx_rows_locked, it.trx_rows_modified from information_schema.INNODB_TRX it,information_schema.innodb_locks il,information_schema.processlist pl where it.trx_id = il.lock_trx_id and it.trx_mysql_thread_id = pl.id\G

7、最近一次死锁、未提交事物、CHECKPIONT、BUFFER POOL等

show engine innodb status\G

8、过滤无用线程信息可用pager

pager grep -v Sleep;show processlist;

9、查看当前运行的详细SQL

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info is not null\G

10、查看某条sql各阶段执行时间,可开启profiling功能

set global profiling=on;

11、查看用户信息

select user,host,password from mysql.user group by user;

12、分表时批量生成sql语句

select concat("select IP as ",TABLE_NAME," from ",TABLE_SCHEMA,".",TABLE_NAME," group by id;") from information_schema.TABLES where TABLE_NAME like 'table_%';

(注意:PERFORMANCE_SCHEMA是在5.5开始新增一个数据库,MySQL5.5默认是关闭的,需要手动开启,5.6默认开启)

[mysqld]

performance_schema=ON

13、查看哪些sql执行最多

SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema' ORDER BY COUNT_STAR desc LIMIT 1;

14、哪个SQL平均响应时间最多

SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema' ORDER BY AVG_TIMER_WAIT desc LIMIT 1\G

15、哪个SQL扫描的行数最多(IO消耗)

SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema' ORDER BY SUM_ROWS_EXAMINED desc LIMIT 1\G

16、哪个SQL使用的临时表最多

SELECT SCHEMA_NAME,DIGEST_TEXT,SUM_CREATED_TMP_DISK_TABLES,SUM_CREATED_TMP_TABLES,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema' ORDER BY SUM_CREATED_TMP_DISK_TABLES desc LIMIT 1\G

17、哪个SQL返回的结果集最多(net消耗)

SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_SENT,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema' ORDER BY SUM_ROWS_SENT desc LIMIT 1\G

18、哪个SQL排序数最多(CPU消耗)

SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_SORT_ROWS,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema' ORDER BY SUM_SORT_ROWS desc LIMIT 1\G

19、哪个表、文件逻辑IO最多(热数据)

SELECT FILE_NAME,EVENT_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ,COUNT_WRITE,SUM_NUMBER_OF_BYTES_WRITE FROM performance_schema.file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE DESC LIMIT 2\G

20、哪个索引使用最多

SELECT OBJECT_NAME, INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE FROM performance_schema.table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC limit 1;

21、哪个索引没有使用过

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME FROM performance_schema.table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME;

22、哪个等待事件消耗的时间最多

SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name != 'idle' ORDER BY SUM_TIMER_WAIT DESC LIMIT 1;

23、通过performance_schema库得到数据库运行的统计信息,更好分析定位问题和完善监控信息

打开标准的innodb监控:

CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

打开innodb的锁监控:

CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;

打开innodb表空间监控:

CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE=INNODB;

打开innodb表监控:

CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;

24、添加主键

alter table xxx add constraint primary key(id);

25、删除外键

alter table test drop foreign key FK_XXX;

26、QPS

SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Uptime';
Questions/Uptime

27、TPS

SHOW GLOBAL STATUS LIKE 'Com_commit';
SHOW GLOBAL STATUS LIKE 'Com_rollback';
SHOW GLOBAL STATUS LIKE 'Uptime';
(Com_commit + Com_rollback)/Uptime

27、导出

select * from MacConfig into outfile '/tmp/MacConfig.sql' fields terminated by ',' lines terminated by '\n';

28、导入

load data infile '/tmp/MacConfig.sql' into table MacConfig fields terminated by ',' lines terminated by '\n';

29、清理binlog

PURGE BINARY LOGS TO 'XXX';

PURGE BINARY LOGS BEFORE '2017-11-09 00:00:00';

30、外键隔离级别等信息

select @@FOREIGN_KEY_CHECKS;
select @@global.tx_isolation,@@tx_isolation;  
select @@character_set_database;
select @@GLOBAL.sql_mode;

原文发布于微信公众号 - MYSQL轻松学(learnmysql)

原文发表时间:2017-11-16

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

关于dual表的破坏性测试(r3笔记第60天)

关于dual表的破坏性测试,既然是破坏性测试,就需要确定这个测试仅限于测试或者个人学习所用,可能有些sql看似极为简单,但是一旦运行就会导致整个业务系统崩溃。 ...

34813
来自专栏Netkiller

数据库记录安全解决方案

数据库记录安全解决方案 http://netkiller.github.io/journal/mysql.security.html 摘要 数据库记录防删除,放...

3506
来自专栏PhpZendo

使用 MySQL Scheduler 和 Event 周期性创建数据表

使用 MySQL Scheduler 和 Event 周期性创建数据表,下面提供的是按月建表计划任务及事件通过 ON SCHEDULE EVERY 1 MINU...

402
来自专栏数据库

采用Opserver来监控你的ASP.NET项目系列

前言 之前有过2篇关于如何监控ASP.NET core项目的文章,有兴趣的也可以看看. 今天我们主要来介绍一下,如何使用Opserver监控我们的SQL Ser...

1748
来自专栏Netkiller

数据库记录安全解决方案

目录 1. 什么是防删除,防撰改 2. 为什么要做防删除,防撰改限制 3. 何时做防删除,防撰改限制 4. 在哪里做防删除,防撰改限制 5. 谁去做防删除,防撰...

2725
来自专栏数据和云

循序渐进:Oracle 12.2的Sharding基础概念解读

张大朋(Lunar)Oracle 工程师 Lunar 拥有超过十年的 ORACLE SUPPORT 从业经验,曾经服务于ORACLE ACS部门,现就职于 O...

2644
来自专栏杨建荣的学习笔记

一条sql语句“导致”的数据库宕机问题及分析 (38天)

最近测试环境需要做一些变更,把测试环境切分成两套环境,存储空间也需要压缩压缩和整理。 unix组的人已经开始做空间划分了,然后我们需要在此基础上重建一套环境。 ...

3254
来自专栏数据库新发现

Oracle9i新特性-索引监视及注意事项[修正版]

Last Updated: Saturday, 2004-12-04 10:28 Eygle

553
来自专栏杨建荣的学习笔记

mysqldump简单解析

MySQL中的mysqldump,真是一个经典而有效的工具,经常用,但是不知道后台运行的过程中到底在干些什么,其实如果想得到这些基本的信息不一定要去看代码...

3387
来自专栏Java学习123

ORACLE数据库日常维护

3378

扫描关注云+社区