5-1 MySQL常用统计语句
最后更新于：2022-04-02 07:44:11
### 查询指定SQL的trace信息
```sql
mysql -uUSER -pPWD -hHOST -e "\
use ins_tc_prd; \
set session optimizer_trace='enabled=on'; \
set optimizer_trace_max_mem_size = 1638400; \
explain SQL; \
select * from information_schema.optimizer_trace\G;" > trace_log.log
```
### 查询某张表大小
```sql
table_schema = 'testdb_monitor'
ORDER BY (data_length + index_length) DESC;
```
### 查看表空间的使用情况
```sql
select table_name,
(data_length+ index_length) /1024/1024 as total_mb,
table_rows
from information_schema.tables
where table_schema= 'Database' ;//不写就是全部
```
### MySQL查询阻塞语句
```sql
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;
```
### 查询表的数据量大小
```sql
select table_name,(data_length + index_length)/1024/1024 as table_mb,table_rows
from information_schema.tables
where table_schema='zabbix';
```
### 统计数据库中访问量前10的IP
```sql
SELECT
SUBSTRING_INDEX(host, ':', 1) AS ip, COUNT(*)
FROM
information_schema.processlist
GROUP BY ip
ORDER BY COUNT(*) DESC
LIMIT 10;
```
';