当前位置：首页 > news >正文

mysql基本信息查询

news 2025/8/12 5:45:51

1.查看mysql表的数据量

select 
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

2.Metadata Lock

MDL出现的初衷就是为了保护一个处于事务中的表的结构不被修改
关于MDL的补充：
MDL的最大等待时间由lock_wait_timeout参数决定，其默认值为31536000（365天）。在使用工具进行DDL操作时，这个值就不太合理。事实上，pt-online-schema-change和gh-ost对其就进行了相应的调整，其中，前者60s，后者3s。

mysql> show processlist;
+----+------+--------------------+--------------------+---------+------+----------+------------------+
| Id | User | Host               | db                 | Command | Time | State    | Info             |
+----+------+--------------------+--------------------+---------+------+----------+------------------+
|  3 | root | 192.168.56.1:53734 | information_schema | Sleep   |  503 |          | NULL             |
|  8 | root | localhost          | NULL               | Query   |    0 | starting | show processlist |
| 10 | root | 192.168.56.1:54074 | information_schema | Sleep   |  396 |          | NULL             |
| 11 | root | 192.168.56.1:54281 | information_schema | Sleep   |  283 |          | NULL             |
| 12 | root | 192.168.56.1:54347 | NULL               | Sleep   |  183 |          | NULL             |
+----+------+--------------------+--------------------+---------+------+----------+------------------+select object_type,object_schema,object_name,lock_type,lock_duration,lock_status,owner_thread_id from performance_schema.metadata_locks;
lock_status，"PENDING"代表线程在等待MDL，而"GRANTED"则代表线程持有MDL。--查看元数据等待关系
SELECTa.OBJECT_SCHEMA AS locked_schema,a.OBJECT_NAME AS locked_table,"Metadata Lock" AS locked_type,c.PROCESSLIST_ID AS waiting_processlist_id,c.PROCESSLIST_TIME AS waiting_age,c.PROCESSLIST_INFO AS waiting_query,c.PROCESSLIST_STATE AS waiting_state,d.PROCESSLIST_ID AS blocking_processlist_id,d.PROCESSLIST_TIME AS blocking_age,d.PROCESSLIST_INFO AS blocking_query,concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
FROMperformance_schema.metadata_locks a
JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
AND a.OBJECT_NAME = b.OBJECT_NAME
AND a.lock_status = 'PENDING'
AND b.lock_status = 'GRANTED'
AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
AND a.lock_type = 'EXCLUSIVE'
JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID\Gselect * from sys.schema_table_lock_waits\G--查看阻塞会话已经执行过的操作
SELECTlocked_schema,locked_table,locked_type,waiting_processlist_id,waiting_age,waiting_query,waiting_state,blocking_processlist_id,blocking_age,substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query,sql_kill_blocking_connection
FROM(SELECTb.OWNER_THREAD_ID AS granted_thread_id,a.OBJECT_SCHEMA AS locked_schema,a.OBJECT_NAME AS locked_table,"Metadata Lock" AS locked_type,c.PROCESSLIST_ID AS waiting_processlist_id,c.PROCESSLIST_TIME AS waiting_age,c.PROCESSLIST_INFO AS waiting_query,c.PROCESSLIST_STATE AS waiting_state,d.PROCESSLIST_ID AS blocking_processlist_id,d.PROCESSLIST_TIME AS blocking_age,d.PROCESSLIST_INFO AS blocking_query,concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connectionFROMperformance_schema.metadata_locks aJOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMAAND a.OBJECT_NAME = b.OBJECT_NAMEAND a.lock_status = 'PENDING'AND b.lock_status = 'GRANTED'AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_IDAND a.lock_type = 'EXCLUSIVE'JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_IDJOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID) t1,(SELECTthread_id,group_concat(   CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_textFROMperformance_schema.events_statements_historyGROUP BY thread_id) t2
WHEREt1.granted_thread_id = t2.thread_id \GSELECTnow(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP( a.trx_started )) diff_sec,b.id,b.USER,b.HOST,b.db,d.SQL_TEXT 
FROMinformation_schema.innodb_trx aINNER JOIN information_schema.PROCESSLIST b ON a.TRX_MYSQL_THREAD_ID = b.id AND b.COMMAND <> 'Binlog Dump'INNER JOIN PERFORMANCE_SCHEMA.threads c ON b.id = c.PROCESSLIST_IDINNER JOIN PERFORMANCE_SCHEMA.events_statements_current d ON d.THREAD_ID = c.THREAD_ID 
WHERE(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP( a.trx_started ))> 10 AND db = 'netcentre' 
ORDER BY(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP( a.trx_started )) DESC;mysql> select * from information_schema.innodb_trx\G
ps:其中trx_mysql_thread_id是线程id，结合performance_schema.threads，可以知道当前哪些连接上存在着活跃事务，这样就进一步缩小了可被kill的线程范围。 但从影响程度上，和kill所有Command为Sleep的连接没太大区别，毕竟，kill真正的空闲连接对业务的影响不大。 此时，依然可以借助performance_schema. events_statements_history表。SELECTnow(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP( a.trx_started )) AS 'exectime',b.id,b.USER,b.HOST,b.db,d.SQL_TEXT 
FROMinformation_schema.innodb_trx aINNER JOIN information_schema.PROCESSLIST b ON a.TRX_MYSQL_THREAD_ID = b.id AND b.COMMAND <> 'Binlog Dump'INNER JOIN PERFORMANCE_SCHEMA.threads c ON b.id = c.PROCESSLIST_IDINNER JOIN PERFORMANCE_SCHEMA.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;Select now(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,b.id,b.user,b.host,b.db,d.SQL_TEXT 
from information_schema.innodb_trx a inner join
information_schema.PROCESSLIST b 
on a.TRX_MYSQL_THREAD_ID=b.id and b.COMMAND<>'Binlog Dump'
inner join performance_schema.threads c ON b.id = c.PROCESSLIST_ID
inner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID
where (UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started))>60
and db='test'