How to query quickly in mysql-Mysql Tutorial-php.cn

How to query quickly in mysql

coldplay.xixi

Release： 2020-11-13 10:09:25

Original

3542 people have browsed it

Mysql quick query method: 1. Query the running transaction; 2. Check the current connection and know the number of connections; 3. Check the size of a table; 4. Check the size of all tables in a database size.

How to query quickly in mysql

More related free learning recommendations: mysql tutorial(video)

mysql quick query method:

1. Query the running transaction

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;

Copy after login

2. View the current connection and know the number of connections

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

Copy after login

3 .Check the size of a table

select concat(round(sum(DATA_LENGTH/1024/1024),2),‘M‘) from information_schema.tables where table_schema=‘数据库名‘ AND table_name=‘表名‘;

Copy after login

4.Check the size of all tables in a database

select table_name,concat(round(sum(DATA_LENGTH/1024/1024),2),‘M‘)  from information_schema.tables where table_schema=‘t1‘ group by table_name;

Copy after login

5.Check the size of the library and the remaining space

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;

Copy after login

6. Find out about the use of lock

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

Copy after login

information_schema

1. Check the table data size under each library

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;

Copy after login

2. Check the data size of each database

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

Copy after login

3. Check whether the instance has a primary key

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‘);

Copy after login

4. Check which fields in the instance can be null

select TABLE_SCHEMA,TABLE_NAME from COLUMNS where IS_NULLABLE=‘YES‘ and TABLE_SCHEMA not in (‘information_schema‘,‘performance_schema‘,‘mysql‘, ‘sys‘)\G

Copy after login

5. Check what stored procedures and functions are in the instance

#存储过程
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‘);

Copy after login

6. Check which table field character sets in the instance are inconsistent with the default character set

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‘);

Copy after login

7. Check which table field character verification rules in the instance are inconsistent with the default

Check the current character set and collation rule settings

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‘);

Copy after login

8. Check which accounts have permissions other than select, update, and 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‘);

Copy after login

9. Check which tables in the instance are not the default storage engine to The default storage engine is innodb as an example

select TABLE_NAME,ENGINE 
from information_schema.tables 
where ENGINE!=‘innodb‘ and TABLE_SCHEMA not in (‘information_schema‘,‘performance_schema‘,‘test‘,‘mysql‘, ‘sys‘);

Copy after login

10. Check which tables in the instance have foreign keys

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

Copy after login

11. Check which table fields in the instance have cascading updates

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‘);

Copy after login

12. How to filter the connection information in the current instance based on user name, connection time, executed sql, etc.

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

Copy after login

13. View tables without indexes in the database

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‘);

Copy after login

14. View the tables in the database Indexed tables, which indexes have been established

Display results: library name, table name, index name

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 ;

Copy after login

The above is the detailed content of How to query quickly in mysql. For more information, please follow other related articles on the PHP Chinese website!