MySQL统计查询语句

概述

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

常用统计语句

1、查询指定SQL的trace信息

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

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

ｓｅｌｅｃｔ table_name, (data_length+ index_length) /1024/1024 as total_mb, table_rows from information_schema.tables where table_schema= ‘lcpdb1‘ ;//不写就是全部

3、MySQL查询阻塞语句

ｓｅｌｅｃｔ 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

ｓｅｌｅｃｔ SUBSTRING_INDEX(host, ‘:‘, 1) AS ip, COUNT(*)FROM information_schema.processlistGROUP BY ipORDER BY COUNT(*) DESCLIMIT 10;

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