mysql快速查询的办法
发布时间：2022-02-21 16:30:08 所属栏目：MySql教程 来源：互联网
导读：小编给大家分享一下mysql快速查询的方法，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！ mysql快速查询的方法：1、查询正在运行中的事务；2、查看当前连接，并且能够知晓连
|
小编给大家分享一下mysql快速查询的方法，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！ mysql快速查询的方法：1、查询正在运行中的事务；2、查看当前连接，并且能够知晓连接数；3、查看一个表的大小；4、查看某个数据库所有表的大小。 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_idG 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.查看实例中哪些表字段字符校验规则和默认的不一致 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‘); （编辑：晋中站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
