mysqlÔõÑùѸËÙ²éѯ
mysqlѸËÙ²éѯµÄ°ì·¨£º1¡¢²éѯÕýÔÚÔËתÖеÄÊÂÎñ£»2¡¢²é¿´Ä¿Ç°Á¬¹á£¬¶øÇÒÄܹ»ÖªÏþÁ¬¹áÊý£»3¡¢²é¿´Ò»¸ö±íµÄ´óС£»4¡¢²é¿´Ä³¸öÊý¾Ý¿âËùÓбíµÄ´óС¡£
¸ü¶àÏà¸ÉÃâ·Ñѧϰ¾Ù¼ö£ºmysql½Ì³Ì(ÊÓƵ)
mysqlѸËÙ²éѯµÄ°ì·¨£º
1.²éѯÕýÔÚÔËתÖеÄÊÂÎñ
select p.id,p.user,p.host,p.db,p.command,p.time,i.trx_state,i.trx_started,p.info from information_schema.processlist p,information_schema.innodb_trx i where p.id=i.trx_mysql_thread_id;
2.²é¿´Ä¿Ç°Á¬¹á£¬¶øÇÒÄܹ»ÖªÏþÁ¬¹áÊý
select SUBSTRING_INDEX(host,¡®:¡®,1) as ip , count(*) from information_schema.processlist group by ip;
3.²é¿´Ò»¸ö±íµÄ´óС
select concat(round(sum(DATA_LENGTH/1024/1024),2),¡®M¡®) from information_schema.tables where table_schema=¡®Êý¾Ý¿âÃû¡® AND table_name=¡®±íÃû¡®;
4.²é¿´Ä³¸öÊý¾Ý¿âËùÓбíµÄ´óС
select table_name,concat(round(sum(DATA_LENGTH/1024/1024),2),¡®M¡®) from information_schema.tables where table_schema=¡®t1¡® group by table_name;
5.²é¿´¿âµÄ´óС£¬Ê£Óà¿Õ¼äµÄ´óС
select table_schema,round((sum(data_length / 1024 / 1024) + sum(index_length / 1024 / 1024)),2) dbsize,round(sum(DATA_FREE / 1024 / 1024),2) freesize, round((sum(data_length / 1024 / 1024) + sum(index_length / 1024 / 1024)+sum(DATA_FREE / 1024 / 1024)),2) spsize from information_schema.tables where table_schema not in (¡®mysql¡®,¡®information_schema¡®,¡®performance_schema¡®) group by table_schema order by freesize desc;
6.²éÑ°¶ÔÓÚËø
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\G
information_schemaµÄÔËÓÃ
1.²é¿´¸÷¸ö¿âϵıíÊý¾Ý´óС
select table_name,concat(round(sum(DATA_LENGTH/1024/1024),2),¡®M¡®) from information_schema.tables where table_schema=¡®db_name¡® group by table_name;
2.²é¿´¸÷¸öÊý¾Ý¿âµÄÊý¾Ý´óС
select TABLE_SCHEMA, concat(round(sum(data_length)/1024/1024,2),¡® MB¡®) as data_size from information_schema.tables group by table_schema;
3.²é¿´ÊµÀýÓÐûÓÐÖ÷¼ü
select table_schema,table_name from information_schema.tables where (table_schema,table_name) not in(select distinct table_schema,table_name from information_schema.STATISTICS where INDEX_NAME=¡®PRIMARY¡® ) and table_schema not in ( ¡®sys¡®,¡®mysql¡®,¡®information_schema¡®,¡®performance_schema¡®);
4.²é¿´ÊµÀýÖÐÄÄЩ×ֶοÉÈÏΪnull
select TABLE_SCHEMA,TABLE_NAME from COLUMNS where IS_NULLABLE=¡®YES¡® and TABLE_SCHEMA not in (¡®information_schema¡®,¡®performance_schema¡®,¡®mysql¡®, ¡®sys¡®)\G
5.²é¿´ÊµÀýÖÐÊÇʲô´æ´¢Àú³ÌºÍº¯Êý
#´æ´¢Àú³Ì select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE from information_schema.ROUTINES where ROUTINE_TYPE=¡®PROCEDURE¡® and ROUTINE_SCHEMA not in (¡®mysql¡®,¡®sys¡®,¡®information_schema¡®,¡®performance_schema¡®); #º¯Êý select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE from information_schema.ROUTINES where ROUTINE_TYPE=¡®FUNCTION¡® and ROUTINE_SCHEMA not in (¡®mysql¡®,¡®sys¡®,¡®information_schema¡®,¡®performance_schema¡®);
6.²é¿´ÊµÀýÖÐÄÄЩ±í×Ö¶Î×Ö·û¼¯ºÍĬÐí×Ö·û¼¯·×ÆçÖÂ
select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,CHARACTER_SET_NAME from information_schema.COLUMNS where (CHARACTER_SET_NAME is null or CHARACTER_SET_NAME <> ¡®utf8¡®) and TABLE_SCHEMA not in (¡®information_schema¡®,¡®performance_schema¡®,¡®test¡®,¡®mysql¡®,¡®sys¡®);
7.²é¿´ÊµÀýÖÐÄÄЩ±í×Ö¶Î×Ö·ûУÑé·¨ÔòºÍĬÐíµÄ·×ÆçÖÂ
²é¿´Ä¿Ç°×Ö·û¼¯ºÍУ¶Ô·¨ÔòÉ趨
show variables like ¡®collation_%¡®; select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,CHARACTER_SET_NAME,COLLATION_NAME from information_schema.COLUMNS where (COLLATION_NAME is null or COLLATION_NAME <> ¡®utf8_general_ci¡®) and TABLE_SCHEMA not in (¡®information_schema¡®,¡®performance_schema¡®,¡®test¡®,¡®mysql¡®,¡®sys¡®);
8.²é¿´ÄÄЩÕ˺ÅÓгýÁËselect¡¢update¡¢insertÖ®ÍâµÄȨÏÞ
select GRANTEE,PRIVILEGE_TYPE,concat(TABLE_SCHEMA,¡®-¡®,TABLE_NAME,¡®-¡®,COLUMN_NAME) from COLUMN_PRIVILEGES where PRIVILEGE_TYPE not in (¡®select¡®,¡®insert¡®,¡®update¡®) union select GRANTEE,PRIVILEGE_TYPE,TABLE_SCHEMA from SCHEMA_PRIVILEGES where PRIVILEGE_TYPE not in (¡®select¡®,¡®insert¡®,¡®update¡®) union select GRANTEE,PRIVILEGE_TYPE,concat(TABLE_SCHEMA,¡®-¡®,TABLE_NAME) from TABLE_PRIVILEGES where PRIVILEGE_TYPE not in (¡®select¡®,¡®insert¡®,¡®update¡®) union select GRANTEE,PRIVILEGE_TYPE,concat(¡®user¡®) from USER_PRIVILEGES where PRIVILEGE_TYPE not in (¡®select¡®,¡®insert¡®,¡®update¡®);
9.²é¿´ÊµÀýÖÐÄÄЩ±í²»ÊÇĬÐí´æ´¢ÒýÇ棬ÒÔĬÐí´æ´¢ÒýÇæΪinnodbΪÀý
select TABLE_NAME,ENGINE from information_schema.tables where ENGINE!=¡®innodb¡® and TABLE_SCHEMA not in (¡®information_schema¡®,¡®performance_schema¡®,¡®test¡®,¡®mysql¡®, ¡®sys¡®);
10.²é¿´ÊµÀýÖÐÄÄЩ±íÓÐÍâ¼ü
select a.TABLE_SCHEMA,a.TABLE_NAME,a.CONSTRAINT_TYPE,a.CONSTRAINT_NAME,b.REFERENCED_TABLE_NAME,b.REFERENCED_COLUMN_NAME from information_schema.TABLE_CONSTRAINTS a LEFT JOIN information_schema.KEY_COLUMN_USAGE b ON a.CONSTRAINT_NAME=b.CONSTRAINT_NAME where a.CONSTRAINT_TYPE=¡®FOREIGN KEY¡®;
11.²é¿´ÊµÀýÖÐÄÄЩ±í×Ö¶ÎÓм¶Áª¸üÐÂ
select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,REFERENCED_TABLE_SCHEMA,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from information_schema.KEY_COLUMN_USAGE where REFERENCED_TABLE_SCHEMA is not null and REFERENCED_TABLE_NAME is not null and REFERENCED_COLUMN_NAME is not null and table_schema not in (¡®information_schema¡®,¡®performance_schema¡®,¡®test¡®,¡®mysql¡®, ¡®sys¡®);
12.ÔõÑùÒÀ¾Ý»áÔ±Ãû¡¢Á¬¹á¹¤·ò¡¢Ê©ÐеÄsqlµÈ¹ýÂËĿǰʵÀýÖеÄÁ¬¹áÐÅÏ¢
select USER,HOST,DB from processlist where TIME>2;
13.²é¿´Êý¾Ý¿âÖÐûÓÐË÷ÒýµÄ±í
select TABLE_SCHEMA,TABLE_NAME from information_schema.tables where TABLE_NAME not in (select distinct(any_value(TABLE_NAME)) from information_schema.STATISTICS group by INDEX_NAME) and TABLE_SCHEMA not in (¡®sys¡®,¡®mysql¡®,¡®information_schema¡®,¡®performance_schema¡®);
14.²é¿´Êý¾Ý¿âÖÐÓÐË÷ÒýµÄ±í£¬´´Á¢ÁËÄÄЩË÷Òý
ÏÔʾЧ¹û£º¿âÃû¡¢±íÃû¡¢Ë÷ÒýÃû
select TABLE_SCHEMA,TABLE_NAME,group_concat(INDEX_NAME) from information_schema.STATISTICS where TABLE_SCHEMA not in (¡®sys¡®,¡®mysql¡®,¡®information_schema¡®,¡®performance_schema¡®) group by TABLE_NAME ;
ÒÔÉϾÍÊÇmysqlÔõÑùѸËÙ²éѯµÄϸÖÂÄÚÈÝ£¬¸ü¶àÇë¹Ø×¢ °Ù·Ö°ÙÔ´ÂëÍø ÆäËüÏà¸ÉÎÄÕ£¡
¸ÐлÄúµÄÖ§³Ö£¬ÎÒ»á¼ÌÐøŬÁ¦µÄ!
´ò¿ªÖ§¸¶±¦É¨Ò»É¨£¬¼´¿É½øÐÐɨÂë´òÉÍŶ
°Ù·Ö°ÙÔ´ÂëÍø ½¨Òé´òÉÍ1¡«10Ôª£¬ÍÁºÀËæÒ⣬¸ÐлÄúµÄÔĶÁ£¡