Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "how to obtain the key information in the DB operation system by MYSQL". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Database management is bound to be asked about a lot of things, and he has to know something in advance to prevent something preventable. Today, we will summarize some commonly used query sql to facilitate dealing with all kinds of asking.

1 count the fields in the user table, (asked how many fields there are in a table, whether there are abnormal field types or lengths, which one has a unique primary key, etc.)

SELECT

INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA

INFORMATION_SCHEMA.COLUMNS.TABLE_NAME

INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME

INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION

INFORMATION_SCHEMA.COLUMNS.DATA_TYPE

INFORMATION_SCHEMA.COLUMNS.COLUMN_KEY

INFORMATION_SCHEMA.COLUMNS.EXTRA

FROM INFORMATION_SCHEMA.COLUMNS

WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA not in ('information_schema','mysql','sys','performance_schema')

2 check whether there is any use of foreign keys in the user's database

SELECT

INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME

INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME

INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME, INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME

INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

Where INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME is not null

3 View the capacity of all databases in the current database instance

SELECT table_schema "Database Name"

SUM (data_length + index_length) / 1024 + 1024 "Database Size (MB)"

FROM information_schema.TABLES

Group by table_schema

4 employees the space occupied by all tables in the database

SELECT table_name "Table Name", table_rows "Rows Count"

Round ((data_length + index_length) / 1024 + index_length), 2) "Table Size (MB)"

FROM information_schema.TABLES WHERE table_schema = "employees"

5 View the current client connection to the database

Select host, current_connections,statements from sys.host_summary

6 View the SQL that is currently connected to the database and runs the most times

Select * from sys.statement_analysis order by exec_count desc limit 10

7 View the top ten files that are most used in the current system Iamp O

Select * from sys.io_global_by_file_by_bytes limit 10

8 check the ones with serious average delay

Select * from sys.statement_analysis order by avg_latency desc limit 1

9 check the statements with serious average delay TOP 10

Select * from sys.statement_analysis order by avg_latency desc limit

10 View indexes that have never been used in the system

Select * from sys.schema_unused_indexes

11 View redundant indexes in the system

Select table_schema,table_name,redundant_index_name,redundant_index_columns,dominant_index_name,dominant_index_columns from sys.schema_redundant_indexes

12 those watches use temporary tables

Select db, query, tmp_tables,tmp_disk_tables from sys.statement_analysis where tmp_tables > 0 or tmp_disk_tables > 0 order by (tmp_tables+tmp_disk_tables) desc limit 20

13 those tables take up the most buffer pool

Select * from sys.innodb_buffer_stats_by_table order by pages desc limit 10

14 View the memory consumed by each link

Select b.user, current_count_used,current_allocated, current_avg_alloc, current_max_alloc,total_allocated,current_statement from sys.memory_by_thread_by_current_bytes a dint sys. Session b where a.thread_id = b.thd_id

15 View the number of connection threads within the MYSQL

Select user, count (*) from sys.processlist group by user

16 View the self-increment ID usage of each table

Select * from sys.schema_auto_increment_columns limit 10

17 check whether there is a full table scan statement in the current database, and reflect the specific situation where the index is not used

Select query,db,exec_count,total_latency,no_index_used_count,no_good_index_used_count,last_seen from sys.statements_with_full_table_scans

18 View the 10 slowest SQL execution in the current system

Select query,db,full_scan,exec_count,avg_latency,rows_sent from sys.statements_with_runtimes_in_95th_percentile

19 events in the system are delayed and sorted according to the average delay time

Select * from sys.wait_classes_global_by_latency

20 currently, if there is a lock, the lock information will be displayed in this table

Select * from sys.schema_table_lock_waits

This is the end of the content of "how to get the key information in the DB operation system by MYSQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.