mysql performance optimization guide

The main goal of this article is to introduce how to optimize MySQL database. By regularly analyzing tables and checking tables, regularly optimizing tables, and optimizing some common SQL statements, it can help you learn to write more efficient SQL in MySQL development.

1. Regular analysis table and check list

The syntax of the analysis table is as follows:

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name[, tbl_name]...

The above statements are used for analysis and storage The keyword distribution of the table and the analysis results will enable the system to obtain accurate statistical information, allowing SQL to generate a correct execution plan. If the user feels that the actual execution plan is not the expected execution plan, running a parse table may solve the problem. During analysis, the table is locked with a read lock. This works for MyISAM, DBD and InnoDB tables.

For example, to analyze a data table

analyze table table_name

The syntax of the check table is as follows:

CHECK TABLE tb1_name[,tbl_name]...[option]...option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

The function of the check table is to check whether one or more tables have errors. CHECK TABLE works on MyISAM and InnoDB tables. For MyISAM tables, keyword statistics are updated.

CHECK TABLE can also check whether the view has errors, such as the table referenced in the view definition does not exist.

2. Regularly optimize the table

The syntax of the optimized table is as follows:

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name [,tbl_name]...

If a large part of the table is deleted, or If you have made more changes to a table with variable length rows (a table with VARCHAR, BLOB, or TEXT columns), you should use the OPTIMIZE TABLE command for table optimization. This command can merge the space fragments in the table and eliminate the waste of space caused by deletion or update, but the OPTIMIZE TABLE command only works on MyISAM, BDB and InnoDB tables.

For example: optimize table table_name

Note: The table will be locked during the execution of analyze, check, and optimize, so be sure to perform related operations when the MySQL database is not busy.

Commonly used SQL optimization

The SQL statements we often use during development are nothing more than INSERT, GROUPBY, etc. How do we optimize these SQL statements?

1. Insert data in large batches

When using the load command to import data, appropriate settings can increase the import speed.

For the MyISAM storage engine table, you can quickly import a large amount of data in the following way

The code is as follows:

ALTER TABLE tb1_name DISABLE KEYS;
loading the data
ALTER TABLE tb1_name ENABLE KEYS;

DISABLE KEYS and ENABLE KEYS are used to turn on or off the update of non-unique indexes of the MyISAM table. When importing a large amount of data into a non-empty MyISAM table, setting these two commands can improve the efficiency of the import.

When importing a large amount of data into an empty MyISAM table, the default is to import the data first and then create the index. The index does not need to be set.

load data infile '/home/mysql/text_txt' into table text

For InnoDB type tables, this method cannot improve the efficiency of importing data, but there are several methods for InnoDB type tables. Optimized way.

1. Because InnoDB type tables are saved in the order of primary keys, sorting the imported data in the order of primary keys can effectively improve the efficiency of imported data.

2. Execute SET UNIQUE_CHECKS=0 before importing data to turn off uniqueness verification. After the import is completed, execute SET UNIQUE_CHECKS=1 to restore uniqueness verification, which can improve the efficiency of import.

3. If the application uses automatic submission, it is recommended to execute SET AUTOCOMMIT=0 before importing to turn off automatic submission. After the import is completed, execute SET AUTOCOMMIT=1 to turn on automatic submission, which can also improve import efficiency.

Optimize INSERT statements

When performing data INSERT, you can consider the following methods for optimization

1. If many rows are inserted from a customer at the same time, try to use INSERT statements of multiple value tables. This method will It greatly shortens the connection, closing and other consumption between the client and the MySQL database, making the efficiency faster than a single INSERT statement executed separately.

For example:

i

nsert into test values(1,2)
insert into test values(3,4)
insert into test values(5,6)

Change the above three sentences to: insert into test values( 1,2),(3,4),(5,6)...

2. If you insert many rows from different clients, you can get higher speed by using INSERT DELAYED statement.

DELAYED means to execute the INSERT statement immediately. In fact, the data is placed in the memory queue and is not actually written to the disk. This is much faster than inserting each statement separately; LOW_PRIORITY is just the opposite. Insertion is performed after reading and writing to the table.

3. Store the index files and data files on different disks

4. If you perform batch insertion, you can increase the value of the bulk_insert_buffer_size variable to increase the speed. However, this can only be used for MyISAM tables.

5. When loading a table from a text file, use LOAD DATA INFILE. This is usually about 20 times faster than using many insert statements.

The above are some other things that need attention in MySQL performance optimization. Let's combine them to give full play to the performance of mysql.