MOP 系列|MOP 三种主流数据库常用 SQL(二)

前 言

MOP 不用多说，指的就是 MySQL、Oracle、PostgreSQL 三种目前最主流的数据库，MOP 系列打算更新 MOP 三种数据库的索引知识、高可用架构及常用 SQL 语句等等，上面已经更新了 MOP 索引相关的文章，今天打算整理一下这三种数据库的常用 SQL 知识，但由于文章过长，今天更新中间的一篇之 MySQL 篇。第一篇 Oracle 相关的详见下方链接：MOP 系列|MOP 三种主流数据库常用 SQL(一)。

Oracle 常用 SQL 大全

作者：JiekeXu
来源 | JiekeXu DBA 之路（ID: JiekeXu_IT）
转载请联系授权 | (微信 ID：JiekeXu_DBA)

SQL 大全一 https://www.modb.pro/doc/22598
SQL 大全二 https://www.modb.pro/db/45337
SQL 大全三 https://www.modb.pro/doc/91589
SQL 大全四 https://www.modb.pro/doc/103483
Oracle DBA 日常维护 SQL 脚本大全（收藏版） https://www.modb.pro/db/44364

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

MySQL 常用 SQL 大全

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%';

-- 查询 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';


21）查看自建视图

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）查看执行次数前 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


3、性能相关其他查询

# 查看活动会话
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 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

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


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

❤️ 欢迎关注我的公众号【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
————————————————————————————