大家好，我是 JiekeXu,江湖人称“强哥”，荣获 Oracle ACE 称号，墨天轮 MVP，墨天轮年度“墨力之星”，拥有 Oracle 11g OCP/OCM 认证,MySQL 5.7/8.0 OCP 认证以及 PCA、PCTA、OBCA、OGCA、KCP 等众多国产数据库认证证书,今天和大家一起来聊聊 MOP 三种主流数据库常用 SQL(二)，欢迎点击最上方蓝字“JiekeXu DBA之路”关注我的微信公众号，然后点击右上方三个点“设为星标”顶，更多干货文章才能第一时间推送，谢谢！

本文 SQL 均是在运维工作中总结整理而成的,非个人独创，部分 SQL 来源于互联网，但现在已经不知道具体是来源哪个网站、哪个人，如有侵权，可联系我及时删除，谢谢！

目  录

MySQL 常用 SQL 大全
    1、基础 SQL
        1）MySQL 查看用户
        2）MySQL 创建用户
        3）MySQL 内存查看
        4）查询数据库大小
        5）查看所有数据库各容量大小
        6）查看所有数据库各表容量大小
        7）查看指定数据库容量大小
        8）查看指定数据库各表容量大小
        9）查看 Top 20 大表信息
        10）查看某个库下表的行数
        11）查看打开表的数量
        12）查看当前正在执行的 SQL
        13）查看连接
        14）批量终止会话
        15）MGR 常用 4 张表
        16）通过以下 SQL 查看主从延迟情况
        17）查看表和索引的统计信息
        18）查看当前 DDL 执行进度
        19）查找非 UTF8 字符集的表
        20）查找无主键唯一键的表
        21）查看自建视图
        22）查看自增主键
        23）查找非 INNODB 存储引擎表
        24）查看存储过程和函数
        25）查看索引信息
        26）查看从未使用过的索引
        27）查看冗余索引
        28）查询锁等待时持续间大于20秒
        29）锁查看
    2、Top 10 SQL
        1）查看长事务（包含未关闭的事务）
        2）查看执行次数前 10 SQL
        3）查看平均响应时间TOP 10 SQL
        4）查看排序次数最多TOP 10 SQL
        5）查看扫描行最多的 TOP 10 SQL
        6）查看使用临时表最多的TOP 10 SQL
    3、性能相关其他查询
    4、Docker 容器相关
        1）查看所有容器，包括未启动的
        2）进入容器

1、基础 SQL

1）MySQL 查看用户

select user,host,plugin from mysql.user;

2）MySQL 创建用户

create database cc_xxl_job;
CREATE USER 'cc_xxl_job'@'%' IDENTIFIED by 'Cck8d90H6L#g';
GRANT ALL PRIVILEGES ON `cc_xxl_job`.* TO `cc_xxl_job`@`%`;
revoke all PRIVILEGES on `cc_xxl_job`.* from `cc_xxl_job`@`%`;


show create user cc_xxl_job;
show grants for cc_xxl_job;
alter user 'cc_xxl_job'@'%' IDENTIFIED by 'Cck8d9H6L#g';

3）MySQL 内存查看

show variables where variable_name in('innodb_buffer_pool_size','innodb_log_buffer_size','innodb_additional_mem_pool_size','key_buffer_size','query_cache_size' );
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| innodb_buffer_pool_size | 33554432 |
| innodb_log_buffer_size  | 8388608  |
| key_buffer_size         | 33554432 |
+-------------------------+----------+
3 rows in set (0.00 sec)

4）查询数据库大小

SELECT table_schema as DB_NAME,
concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as DB_DATA_SIZE,
concat(round(sum(INDEX_LENGTH/1024/1024),2),'MB') as DB_INDEX_SIZE,
concat(round(sum((DATA_LENGTH+INDEX_LENGTH)/1024/1024),2),'MB') as DB_TOTAL_SIZE
from information_schema.TABLES group by table_schema;

5）查看所有数据库各容量大小

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

6）查看所有数据库各表容量大小

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;

7）查看指定数据库容量大小

--例：查看 test库容量大小
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='test';

8）查看指定数据库各表容量大小

例：查看test库各表容量大小
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
where table_schema='test'
order by data_length desc, index_length desc;

9）查看 Top 20 大表信息

select table_schema,table_name,round((data_length+index_length)/1024/1024,2) as 'SIZE_MB',table_rows,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') order by 3 desc limit 20 ;

10）查看某个库下表的行数

select TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS from information_schema.TABLES where TABLE_SCHEMA='test';

11）查看打开表的数量

show global status like 'open%tables%';

12）查看当前正在执行的 SQL

select * from information_schema.PROCESSLIST where info is not null and time > 0;

13）查看连接

show processlist;
show full processlist;
select ID,USER,DB,HOST,COMMAND,STATE,INFO,TIME from information_schema.PROCESSLIST where db='jiekexu';

14）批量终止会话

select concat('kill ',id,';'),host,user,command,time,state,info from information_schema.processlist
where command !='Sleep' and user not in ('repl','system user','event_scheduler')
order by time desc limit 10;

15）MGR 常用 4 张表

performance_schema.replication_group_member_stats
performance_schema.replication_group_members
performance_schema.replication_connection_status
performance_schema.replication_applier_status
select * from performance_schema.replication_group_members;
--查看当前节点是否为主节点
SELECT IF((SELECT @@server_uuid) = (SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME= 'group_replication_primary_member'), 1, 0) as is_primary_node;
--查看主节点信息
SELECT *
FROM performance_schema.replication_group_members
WHERE MEMBER_ID = (
SELECT VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME= 'group_replication_primary_member'
);

16）通过以下 SQL 查看主从延迟情况

select case 
  when min_commit_timestamp is null then 0
  else unix_timestamp(now(6)) - unix_timestamp(min_commit_timestamp)
 end as seconds_behind_master
from (
 select min(applying_transaction_original_commit_timestamp) as min_commit_timestamp
 from performance_schema.replication_applier_status_by_worker
 where applying_transaction <> ''
) t;

17）查看表和索引的统计信息

select * from mysql.innodb_table_stats where database_name='JiekeXu' and table_name='tab_name'; 
--索引统计信息
select * from mysql.innodb_index_stats where database_name='JiekeXu' and table_name='tab_name' and index_name='idx_name';

18）查看当前 DDL 执行进度

select * from performance_schema.setup_instruments where name like 'stage/innodb/alter%';
select * from performance_schema.setup_consumers where name like '%stages%';


--如果上面查询结果为NO，则需要做如下配置：
update set_instrucments set enabled = 'YES' where name like 'stage/innodb/alter%';
update set_consumers set enabled = 'YES' where name like '%stages%';


-- 查询 DDL 执行的进度：
select stmt.sql_text,
       stage.event_name,
       concat(work_completed, '/', work_estimated) as progress,
       concat(round(100 * work_completed / work_estimated, 2), ' %') as processing_pct,
       sys.format_time(stage.timer_wait) as time_costs,
       concat(round((stage.timer_end - stmt.timer_start) / 1e12 *
                    (work_estimated - work_completed) / work_completed,
                    2),
              ' s') as remaining_seconds
  from performance_schema.events_stages_current     stage,
       performance_schema.events_statements_current stmt
 where stage.thread_id = stmt.thread_id
   and stage.nesting_event_id = stmt.event_id\G

19）查找非 UTF8 字符集的表

SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION
 from information_schema.TABLES
WHERE TABLE_COLLATION NOT LIKE 'utf8%'
 AND table_schema NOT IN ('information_schema' ,'mysql','performance_schema', 'sys');

20）查找无主键唯一键的表

SELECT T1.TABLE_SCHEMA,
       T1.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME
WHERE T1.TABLE_SCHEMA NOT IN ('sys','mysql','information_schema','performance_schema')
AND T2.TABLE_TYPE='BASE TABLE'
GROUP BY T1.TABLE_SCHEMA,
         T1.TABLE_NAME HAVING group_concat(COLUMN_KEY) NOT REGEXP 'PRI|UNI';

SELECT  TABLE_SCHEMA , COUNT(TABLE_NAME) 
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA not in ('mysql','information_schema','performance_schema','sys')
GROUP BY TABLE_SCHEMA ;
SELECT TABLE_SCHEMA,TABLE_NAME AS VIEW_NAME
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA not in ('mysql','information_schema','performance_schema','sys')
ORDER BY TABLE_SCHEMA ;

22）查看自增主键

SELECT infotb.TABLE_SCHEMA,
infotb.TABLE_NAME,
infotb.AUTO_INCREMENT,
infocl.COLUMN_TYPE,
infocl.COLUMN_NAME
FROM information_schema.TABLES  as infotb INNER JOIN information_schema.COLUMNS infocl
ON infotb.TABLE_SCHEMA = infocl.TABLE_SCHEMA
AND infotb.TABLE_NAME = infocl.TABLE_NAME
AND infocl.EXTRA='auto_increment';

23）查找非 INNODB 存储引擎表

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       TABLE_COLLATION,
       ENGINE,
       TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,
                           'SYS',
                           'MYSQL',
                           'PERFORMANCE_SCHEMA')
  AND TABLE_TYPE='BASE TABLE'
  AND ENGINE NOT IN ('INNODB')
ORDER BY TABLE_ROWS DESC ;

24）查看存储过程和函数

##MySQL8
SELECT  Routine_schema, Routine_type 
FROM information_schema.Routines
WHERE  Routine_schema not in ('mysql','information_schema','performance_schema','sys') 
AND ROUTINE_TYPE='PROCEDURE'
GROUP BY Routine_schema, Routine_type;

25）查看索引信息

show index from db_name.tab_name;

26）查看从未使用过的索引

select * from sys.schema_unused_indexes where object_schema not in ('performance_schema');

27）查看冗余索引

select * from sys.schema_redundant_indexes;

28）查询锁等待时持续间大于20秒

SELECT trx_mysql_thread_id AS PROCESSLIST_ID,NOW(),TRX_STARTED,
       TO_SECONDS(now()) - TO_SECONDS(trx_started) AS TRX_LAST_TIME ,
       USER,HOST,DB,TRX_QUERY
FROM INFORMATION_SCHEMA.INNODB_TRX trx
JOIN sys.innodb_lock_waits lw ON trx.trx_mysql_thread_id=lw.waiting_pid
JOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.id
WHERE trx_mysql_thread_id != connection_id()
  AND TO_SECONDS(now())-TO_SECONDS(trx_started) >= 20;


select * from sys.innodb_lock_waits\G

29）锁查看

--Waiting for table metadata lock
SELECT
   a.OBJECT_SCHEMA AS locked_schema,
   a.OBJECT_NAME AS locked_table,
   "Metadata Lock" AS locked_type,
   c.PROCESSLIST_ID AS waiting_processlist_id,
   c.PROCESSLIST_TIME AS waiting_age,
   c.PROCESSLIST_INFO AS waiting_query,
   c.PROCESSLIST_STATE AS waiting_state,
   d.PROCESSLIST_ID AS blocking_processlist_id,
   d.PROCESSLIST_TIME AS blocking_age,
   d.PROCESSLIST_INFO AS blocking_query,
   concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
FROM
   performance_schema.metadata_locks a
JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
AND a.OBJECT_NAME = b.OBJECT_NAME
AND a.lock_status = 'PENDING'
AND b.lock_status = 'GRANTED'
AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
AND a.lock_type = 'EXCLUSIVE'
JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID\G

2、Top 10 SQL

1）查看长事务（包含未关闭的事务）

SELECT thr.processlist_id AS mysql_thread_id, concat(PROCESSLIST_USER,'@',PROCESSLIST_HOST) User, Command, FORMAT_PICO_TIME(trx.timer_wait) AS trx_duration, current_statement as `latest_statement` 
FROM performance_schema.events_transactions_current trx
INNER JOIN performance_schema.threads thr USING (thread_id) 
LEFT JOIN sys.processlist p
ON p.thd_id=thread_id WHERE thr.processlist_id IS NOT NULL AND PROCESSLIST_USER IS NOT NULL AND trx.state = 'ACTIVE' GROUP BY thread_id, timer_wait ORDER BY TIMER_WAIT DESC LIMIT 10;

2）查看执行次数 Top 10 SQL

SELECT QUERY_SAMPLE_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest ORDER BY COUNT_STAR DESC LIMIT 10\G

3）查看平均响应时间 TOP 10 SQL

SELECT QUERY_SAMPLE_TEXT,AVG_TIMER_WAIT FROM performance_schema.events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT DESC limit 10\G

4）查看排序次数最多TOP 10 SQL

SELECT QUERY_SAMPLE_TEXT,SUM_SORT_ROWS FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_SORT_ROWS DESC LIMIT 10\G

5）查看扫描行最多的 TOP 10 SQL

SELECT QUERY_SAMPLE_TEXT,SUM_ROWS_EXAMINED FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_ROWS_EXAMINED DESC LIMIT 10\G

6）查看使用临时表最多的 TOP 10 SQL

SELECT QUERY_SAMPLE_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_CREATED_TMP_TABLES DESC LIMIT 10\G

# 查看活动会话
select * from information_schema.processlist where command <> 'Sleep' and id <> connection_id();
select * from performance_schema.processlist where command <> 'Sleep' and id <> connection_id();
# 列出 MySQL 服务器运行各种状态值
show global status;
# 查询 MySQL 服务器配置信息语句
show variables;
# 慢查询
show variables like '%slow%';
# MySQL 服务器最大连接数
show variables like 'max_connections';
# 服务器响应的最大连接数
show global status like 'Max_used_connections';
# 查看试图连接到 MySQL(不管是否连接成功)的连接数
show status like 'connections';
# 创建临时表
show global status like 'created_tmp%';
# MySQL 服务器对临时表的配置
show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');
# 打开表的数量
show global status like 'open%tables%';
# table 高速缓存的数量
show variables like 'table_open_cache';
# 查看 MySQL 服务器的线程信息
show global status like 'Thread%';
# 查看当前运行的 sql
SELECT * FROM `information_schema`.`PROCESSLIST` WHERE `info` IS NOT NULL and TIME > 0;
# 当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)
show variables like 'thread_cache_size';
# 查询缓存
show global status like 'qcache%';
# 查询缓存适用于特定的场景，建议充分测试后，再考虑开启，避免引起性能下降或引入其他问题
show variables like 'query_cache%';
# 排序使用情况
show global status like 'sort%';
# 文件打开数
show global status like 'open_files';
# 表锁情况
show global status like 'table_locks%';
# 表扫描情况
show global status like 'handler_read%';
# 服务器完成的查询请求
show global status like 'com_select';
# 查询当前 MySQL 本次启动后的运行统计时间
show status like 'uptime';
# 查看本次 MySQL 启动后执行的 select 语句的次数
show status like 'com_select';
# 查看本次 MySQL 启动后执行 insert 语句的次数
show global status like 'com_insert';
# 查看本次 MySQL 启动后执行 update 语句的次数
show global status like 'com_update';
# 查看本次 MySQL 启动后执行 delete 语句的次数
show global status like 'com_delete';
# 查看立即获得的表的锁的次数
show status like 'table_locks_immediate';
# 查看不能立即获得的表的锁的次数。如果该值较高，并且有性能问题，你应首先优化查询，然后拆分表或使用复制
show status like 'table_locks_waited';
# 查看查询时间超过 long_query_time 秒的查询的个数
show status like 'slow_queries';
# 通过 mysql 自带 profiling(性能分析)工具可以诊断某个 sql 各个执行阶段消耗的时间，每个执行阶段在 cpu disk io 等方面的消耗情况。
show variables like '%profiling%';
#查看 profiles
show profiles;
show profile for query 2;
show profile cpu, block io for query 2;

4、Docker 容器相关

su - docker
docker ps


CONTAINER ID        IMAGE                                        COMMAND                  CREATED             STATUS              PORTS                     NAMES
d53608bc5053        192.168.26.12/public/mysql:mysql-80-centos7   "container-entrypoin鈥?   2 minutes ago       Up 2 minutes        0.0.0.0:13329->3306/tcp   dtke_dd1
bc45e03f5976        192.168.26.15/public/mysql:mysql-57-centos7   "container-entrypoin鈥?   7 hours ago         Up 7 hours          0.0.0.0:13360->3306/tcp   jie_biz_zjbh

1）查看所有容器，包括未启动的

docker ps -a


启动容器
docker start 容器ID


docker cp 源 目标
容器外执行，在宿主机和容器间拷贝文件
--从容器 59 拷贝到 文件系统
docker cp e3fed7f6ce59:/var/lib/mysql/data/dump13397/FULL_P13397_0526.sql /home/docker/
--从文件系统拷贝到 容器 90 
docker cp /home/docker/FULL_P13397_0526.sql 18836b25bb90:/var/lib/mysql/data/dump

2）进入容器

docker exec -it d53608bc5053 bash
bash-4.2$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.17 Source distribution


Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> show databases;


身份验证插件
alter user dtjieke_dd1 identified  with mysql_native_password by "dtji0#My07";


mysql> select user,host,plugin from mysql.user;

全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~

分享几个数据库备份脚本

一文搞懂 Oracle 统计信息

我的 Oracle ACE 心路历程
MOP 系列|MOP 三种主流数据库索引简介
Oracle 主流版本不同架构下的静默安装指南
关机重启导致 ASM 磁盘丢失数据库无法启动
Oracle SQL 性能分析(SPA)原理与实战演练

Oracle 11g 升级到 19c 需要关注的几个问题
Windows 10 环境下 MySQL 8.0.33 安装指南
SQL 大全（四）|数据库迁移升级时常用 SQL 语句
OGG|使用 OGG19c 迁移 Oracle11g 到 19C(第二版)
Oracle 大数据量导出工具——sqluldr2 的安装与使用
从国产数据库调研报告中你都能了解哪些信息及我的总结建议
使用数据泵利用 rowid 分片导出导入 lob 大表及最佳实践
在归档模式下直接 rm dbf 数据文件并重启数据库还有救吗？

欢迎关注我的公众号【JiekeXu DBA之路】，一起学习新知识！
————————————————————————————
公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
ITPUB：https://blog.itpub.net/69968215
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————