小編給大家分享一下MySQL快速查詢(xún)的方法，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

讓客戶(hù)滿(mǎn)意是我們工作的目標(biāo)，不斷超越客戶(hù)的期望值來(lái)自于我們對(duì)這個(gè)行業(yè)的熱愛(ài)。我們立志把好的技術(shù)通過(guò)有效、簡(jiǎn)單的方式提供給客戶(hù)，將通過(guò)不懈努力成為客戶(hù)在信息化領(lǐng)域值得信任、有價(jià)值的長(zhǎng)期合作伙伴，公司提供的服務(wù)項(xiàng)目有：域名申請(qǐng)、雅安服務(wù)器托管、營(yíng)銷(xiāo)軟件、網(wǎng)站建設(shè)、桐柏網(wǎng)站維護(hù)、網(wǎng)站推廣。

mysql快速查詢(xún)的方法：1、查詢(xún)正在運(yùn)行中的事務(wù)；2、查看當(dāng)前連接，并且能夠知曉連接數(shù)；3、查看一個(gè)表的大??；4、查看某個(gè)數(shù)據(jù)庫(kù)所有表的大小。

mysql快速查詢(xún)的方法：

1.查詢(xún)正在運(yùn)行中的事務(wù)

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.查看當(dāng)前連接，并且能夠知曉連接數(shù)

select SUBSTRING_INDEX(host,‘:‘,1) as ip , count(*) from information_schema.processlist group by ip;

3.查看一個(gè)表的大小

select concat(round(sum(DATA_LENGTH/1024/1024),2),‘M‘) from information_schema.tables where table_schema=‘?dāng)?shù)據(jù)庫(kù)名‘ AND table_name=‘表名‘;

4.查看某個(gè)數(shù)據(jù)庫(kù)所有表的大小

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.查看庫(kù)的大小，剩余空間的大小

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.查找關(guān)于鎖

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.查看各個(gè)庫(kù)下的表數(shù)據(jù)大小

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.查看各個(gè)數(shù)據(jù)庫(kù)的數(shù)據(jù)大小

select TABLE_SCHEMA, concat(round(sum(data_length)/1024/1024,2),‘ MB‘) as data_size  from information_schema.tables group by table_schema;

3.查看實(shí)例有沒(méi)有主鍵

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.查看實(shí)例中哪些字段可以為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.查看實(shí)例中有哪些存儲(chǔ)過(guò)程和函數(shù)

#存儲(chǔ)過(guò)程
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‘);
#函數(shù)
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.查看實(shí)例中哪些表字段字符集和默認(rèn)字符集不一致

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.查看實(shí)例中哪些表字段字符校驗(yàn)規(guī)則和默認(rèn)的不一致

查看當(dāng)前字符集和校對(duì)規(guī)則設(shè)置

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.查看哪些賬號(hào)有除了select、update、insert以外的權(quán)限

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.查看實(shí)例中哪些表不是默認(rèn)存儲(chǔ)引擎，以默認(rèn)存儲(chǔ)引擎為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.查看實(shí)例中哪些表有外鍵

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.查看實(shí)例中哪些表字段有級(jí)聯(lián)更新

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.如何根據(jù)用戶(hù)名、連接時(shí)間、執(zhí)行的sql等過(guò)濾當(dāng)前實(shí)例中的連接信息

select USER,HOST,DB from processlist where TIME>2;

13.查看數(shù)據(jù)庫(kù)中沒(méi)有索引的表

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.查看數(shù)據(jù)庫(kù)中有索引的表，建立了哪些索引

顯示結(jié)果：庫(kù)名、表名、索引名

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快速查詢(xún)的方法的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！

當(dāng)前名稱(chēng)：mysql快速查詢(xún)的方法
URL標(biāo)題：http://aaarwkj.com/article8/jjheop.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供自適應(yīng)網(wǎng)站、手機(jī)網(wǎng)站建設(shè)、關(guān)鍵詞優(yōu)化、網(wǎng)站建設(shè)、、小程序開(kāi)發(fā)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話(huà)：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)