“This is the 13th day of my participation in the First Challenge 2022. For details: First Challenge 2022”

Processlist in MySQL shows which threads are running in MySQL. Run the following command in MySQL, and the result is as follows:

show full processlist;
Copy the code

The column names and their values are as follows:

  • id: thread flag, system assignedconnection_id, you can useselect connection_id()Kill + id to kill the thread, usemysql > kill id;;
  • User: Displays the current user, if not currently in userootQuery, this command displays only SQL statements within the user’s permission range;
  • Host: The statement is the source, i.e. from which IP address and port the statement is accessed.
  • db: Displays the database to which the current process is connected
  • Command: Type of command executed, usually sleep (sleep), query (query), connect (connect);
  • Time: Duration of the SQL statement, in seconds.
  • State: Statement execution status, yescopying to tmp table.Sorting result.Sending dataSuch as state;
  • info: displays executed SQL statements.

State Indicates the status of the column

  • Checking table: Checklist;
  • Closing tables: Flush the changed data in the table to disk and close the used table.
  • Copying to tmp table on disk: Memory storage is converted to hard disk storage.
  • Creating tmp table Create temporary table;
  • deleting from main table : the first step in deleting multiple tables;
  • deleting from reference tables : The second step in deleting multiple tables;
  • Flushing tables :FLUSH TABLESWait for another thread to close the table
  • Locked: Lock query;
  • Sending data: Performing a SELECT query and sending the results to the client;
  • Sorting for group: Sorting groups;
  • Sorting for order: Sorting

The show processList query results are from the ProcessList table at information_SCHEMA and can be replaced with the following query:

select * from information_schema.processlist
Copy the code

Details can be found in the official manual.

This blog mainly optimize the MySQL insert operation, the core of the insert optimization task.

Load data infile Import data Using the preceding commands, you can insert data in batches. If you want to use this command, you can test it with the show command

show variables like '%infile%'
Copy the code

If you want to insert multiple rows of data at once, you can use an INSERT statement and concatenate multiple rows of data.

insert intoThe name of the tablevalues(...). , (...). , (...).Copy the code

Disable Unique check When inserting data, you can temporarily cancel uniqueness check by running the following command before inserting data

set unique_checks = 0
Copy the code

Open it after insertion

set unique_checks = 1
Copy the code

Disabling foreign key check is the same as the preceding logic.

set foreign_key_checks = 0
set foreign_key_checks = 1
Copy the code

Disable Automatic commit Disables automatic commit of a transaction

set autocommit = 0
set autocommit = 1
Copy the code

Disable Index Temporarily closes an index

alter tableThe table name disable keysCopy the code

Open the index

alter tableThe table name enable keysCopy the code

Modify it from a configuration perspective

Bulk_insert_buffer_size Cache size. The default value is 8 meters, which can be increased.

show variables like 'bulk_insert%'
Copy the code

This parameter can only be trueMyISAMUse,innodbIs invalid.

Max_allowed_packet Specifies the size of the packet accepted. The default value is 16 MB, which can be increased

show variables like 'max_all%'
Copy the code

Net_buffer_length Size of cached data during communication. The value ranges from 4k to 16 MB. The default value is 1 MB.

show variables like 'net_buffer_length'
Copy the code

By default, MySQL creates a transaction every time it conducts an INSERT operation, so we can put the bulk insert operation into the transaction in advance to improve efficiency.

START TRANSACTION; 
insert intoThe name of the tablevalues(...). , (...). , (...). ;insert intoThe name of the tablevalues(...). , (...). , (...). ;insert intoThe name of the tablevalues(...). , (...). , (...). ;COMMIT;
Copy the code