久久综合狠狠综合久久,久久久久久精品无码人妻,情人伊人久久综合亚洲

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

mysql如何快速查詢

更多相關(guān)免費(fèi)學(xué)習(xí)推薦：mysql教程(視頻)

mysql快速查詢的方法：

1.查詢正在運(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.查看一個表的大小

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

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

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.查找關(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_idG

information_schema的使用

1.查看各個庫下的表數(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.查看各個數(shù)據(jù)庫的數(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í)例有沒有主鍵

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í)例中有哪些存儲過程和函數(shù)

#存儲過程 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)前字符集和校對規(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.查看哪些賬號有除了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)存儲引擎，以默認(rèn)存儲引擎為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í)例中哪些表字段有級聯(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ù)用戶名、連接時間、執(zhí)行的sql等過濾當(dāng)前實(shí)例中的連接信息

select?USER,HOST,DB?from?processlist?where?TIME&gt;2;

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

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ù)庫中有索引的表，建立了哪些索引

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

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?;

以上就是

? 版權(quán)聲明

文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載。

THE END