1. Turn on general_log

general_log It’s a query log , It will record all your operations on the database

Turn on

# Inquire about general_log Log on 
show global variables like '%general%';
# Output log to mysql Database based general_log In the table 
set global log_output='table';
# Output the log to a file 
set global log_output='/temp/general_log.log';
# Turn on general_log
set global general_log=on;
# close general_log 
set global general_log=off;

2. Close long Affairs

mysql Except for read-only transactions , Information about other transactions will be recorded in information_schema.Innodb_trx Inside the watch , According to this table TRX_MYSQL_THREAD_ID Field gets the… Of the deadlock transaction mysql Threads ID,kill Just drop this thread . Here are the field comments in this table

trx_id： The only thing id Number , Read only transactions and unlocked transactions are not created id Of .
TRX_WEIGHT： The height of affairs , Represents the number of lines modified （ Not necessarily accurate ） And the number of rows locked by the transaction . In order to solve the deadlock ,innodb A transaction with the smallest height will be selected as the victim for rollback . The transaction weight of the non transactional table that has been changed is higher than other transactions , Even if the changed and locked rows are lower than other transactions .
TRX_STATE： The execution state of the transaction , Value is generally divided into ：RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING.
TRX_STARTED： The start time of the transaction
TRX_REQUESTED_LOCK_ID: If trx_state yes lockwait, Display the current transaction waiting for lock id, If not, it's empty . Want to get lock information , According to the lock_id, With innodb_locks In the table lock_id Query by column matching criteria , Get relevant information .
TRX_WAIT_STARTED： If trx_state yes lockwait, This value represents the time the transaction started waiting for the lock ; Otherwise it's empty .
TRX_MYSQL_THREAD_ID：mysql Threads id. Want to get information about this thread , According to the thread_id, With INFORMATION_SCHEMA.PROCESSLIST Tabular id List as matching criteria to query .
TRX_QUERY： The transaction is executing sql sentence .
TRX_OPERATION_STATE： The current operation state of the transaction , No is null .
TRX_TABLES_IN_USE： The transaction is processing the current sql Statements use innodb Number of engine tables .
TRX_TABLES_LOCKED： At present sql The statement has a line lock innodb Table number .（ Because it's just a line lock , It's not a watch lock , Tables can still be read and written by multiple transactions ）
TRX_LOCK_STRUCTS： Number of transaction reservation locks .
TRX_LOCK_MEMORY_BYTES： The amount of space occupied by transaction cable structure in memory .
TRX_ROWS_LOCKED： The most accurate number of transaction line locks . This value may include the physical presence of transactions , Actually invisible delete marked lines .
TRX_ROWS_MODIFIED： Number of rows modified and inserted by transaction
TRX_CONCURRENCY_TICKETS： This value represents how much work the current transaction can do before it is cleared , from innodb_concurrency_tickets The value of the system variable specifies .
TRX_ISOLATION_LEVEL： Transaction isolation level .
TRX_UNIQUE_CHECKS： Whether the current transaction uniqueness check is enabled or disabled . When bulk data is imported , This parameter is off .
TRX_FOREIGN_KEY_CHECKS： Whether the foreign key of the current transaction is enabled or disabled . When bulk data is imported , This parameter is off .
TRX_LAST_FOREIGN_KEY_ERROR： The latest foreign key error message , No is null .
TRX_ADAPTIVE_HASH_LATCHED： Whether the adaptive hash index is blocked by the current transaction . When adaptive hash index searches system partition , A single transaction does not block all the adaptations hash Indexes . The adaptive hash Index partition through innodb_adaptive_hash_index_parts Parameter control , The default value is 8.
TRX_ADAPTIVE_HASH_TIMEOUT： Is it for adaptation hash The index immediately relinquishes the query lock , Or by calling mysql Function to keep it . When there is no adaption hash Index conflict , The value is 0 And the statement holds the lock until the end . In the course of the conflict , The value is counted as 0, Release the latch immediately after each query . When adaptive hash Index query system is partitioned （ from innodb_adaptive_hash_index_parts Parameter control ）, The value remains 0.
TRX_IS_READ_ONLY： The value is 1 Indicates that the transaction is read only.
TRX_AUTOCOMMIT_NON_LOCKING： The value is 1 Indicates that the transaction is a select sentence , The statement does not use for update perhaps shared mode lock , And the execution turns on autocommit, So the transaction contains only one statement . When TRX_AUTOCOMMIT_NON_LOCKING and TRX_IS_READ_ONLY Also for 1,innodb Optimize transactions by reducing transaction overhead and changing the table database .

3. Database backup

3.1 Use mysqldump

 # export pibigstar database 
mysqldump -uroot -p -B pibigstar --single-transaction –master-data=2 > backup.sql
# Restore data 
mysql -uroot -p pibigstar < backup.sql

Common parameters

-u: Specify the user name of the connection ;
-p: Specify the user's password , You can enter passwords interactively ;
-S: Appoint socket The file , Only when you log in locally .
-h: Specify the server name of the connection or IP.
-P: Connect to the port on which the database is listening .
-A: Export all databases . However, by default, it will not export information_schema library .
-B: Export a specified / Or some database , All name parameters after the parameter are treated as database names , contain CREATE DATABASE Create a library statement .
--single-transaction: A transaction will be started before importing data , To ensure a consistent view

3.2 Manual backup

If the database engine is MYISAM It doesn’t support transactions , You can’t use mysqldump To start a transaction to backup , If you want to back up the database at this time , You need to make the database read-only , That is to add a database global lock , Only in this way can we ensure the consistency of the data .

# Lock the database globally 
flush tables with read lock;

You can also use the following statement to set the database to read-only state , However, it is not recommended , because readonly Whether the master library or the slave database is commonly used , So it’s easy not to change this value .

set global readonly=true;

4. Safely add fields

There are two kinds of table level locks , One is watch lock , One is metadata lock （MDL）, When executed DML Statement, the database will automatically add a MDL Read the lock , And perform DDL When the sentence is , It will automatically add a MDL Write lock , Read write locks are mutually exclusive , and MDL Lock release will not be released until the transaction is committed , So when we add fields to the table , To be more secure , To prevent constant blockage , You can add a waiting time .

ALTER TABLE user WAIT 10 add column age int(2) NOT NULL DEFAULT 0;

5. Use Explain analysis SQL

Explain Return result field introduction

Let’s analyze one SQL When the sentence is , Focus on key type Extra The information of these three fields , First of all to see key Whether the index used in is what we expect , And then through type Judge our article sql The scope of the statement scan , Finally through Extra Determine which way the statement is used ,Extra You can directly see the quality of the sentence .

5.1 type

type value , The order of performance from poor to good is , If the value is ALL or index when , It’s time to consider optimizing the next statement

5.2 Extra

The following is a detailed explanation of common parameters , Performance is ranked from good to poor , When you meet Using temporary,Using Flesort when , We can consider optimizing SQL 了 .