分享一下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方面的内容,感兴趣的朋友可以关注一下~
,