mysql 获取 表说明mysql实用脚本--获取trace

导读概述分享一下mysql常用的一些统计语句，仅供参考。常用统计语句1、查询指定SQL的trace信息mysql-uroot-p-e"\uselcpdb1;\s....概述

分享一下mysql常用的一些统计语句，仅供参考。

---

常用统计语句

1、查询指定SQL的trace信息

mysql -uroot -p -e "\use lcpdb1; \set session optimizer_trace=enabled=on; \set optimizer_trace_max_mem_size = 1638400; \explain select * from sys_user; \select * from information_schema.optimizer_trace\G;" > trace_log.log

2、查看表空间的使用情况

select table_name, (data_length index_length) /1024/1024 as total_mb, table_rows from information_schema.tables where table_schema= lcpdb1 ;//不写就是全部

3、MySQL查询阻塞语句

select r.trx_id waiting_trx_id, r.trx_mysql_thread_Id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query from information_schema.innodb_lock_waits w inner join information_schema.innodb_trx b on b.trx_id = w.blocking_trx_id inner join information_schema.innodb_trx r on r.trx_id = w.requesting_trx_id;

4、统计数据库中访问量前10的IP

SELECT SUBSTRING_INDEX(host, :, 1) AS ip, COUNT(*)FROM information_schema.processlistGROUP BY ipORDER BY COUNT(*) DESCLIMIT 10;

---

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~