概述

分享一下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.processlist
GROUP BY ip
ORDER BY COUNT(*) DESC
LIMIT 10;

---

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