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

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