MySQL database Optimization Methods

The following Article This section describes the implementation of simple and practical Optimization Methods for MySQL databases, including how to regularly analyze and check tables and how to optimize tables on a regular basis, the following is a description of the specific solution. I hope it will help you in your future study.

1. Regular analysis table and checklist

The analysis table syntax is as follows:

CopyCode The Code is as follows: Analyze [local | no_write_to_binlog] Table tb1_name [, tbl_name]...

The preceding statement is used to analyze and store the table's keyword distribution. The analysis result will allow the system to obtain accurate statistical information, so that the SQL statement can generate a correct execution plan. If the user feels that the actual execution plan is not the expected execution plan, executing an analysis table may solve the problem. During the analysis, a read lock is used to lock the table. This applies to MyISAM, DBD, and InnoDB tables.

For example, to analyze a data table

Copy code The Code is as follows: Analyze table table_name

The syntax of the checklist is as follows:

Copy codeThe Code is as follows: Check table tb1_name [, tbl_name]... [Option]... option = {quick | fast | medium | extended | changed}

The purpose of the checklist is to check whether one or more tables are incorrect. Check table is useful for MyISAM and InnoDB tables. For MyISAM tables, keyword statistics are updated.

Check table can also check whether the view has an error. For example, the referenced table in the view definition does not exist.

2. Regular table Optimization

The table optimization syntax is as follows:

Copy code The Code is as follows: optimize [local | no_write_to_binlog] Table tb1_name [, tbl_name]...

If you delete a majority of tables, or if you have made more changes to a table that contains variable-length rows (tables that contain varchar, blob, or text columns, the optimize table command should be used for table optimization. This command can merge the space fragments in the table and eliminate space waste caused by deletion or update. However, the optimize table command only applies to MyISAM, bdb, and InnoDB tables.

Example: optimize table table_name

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

Common SQL Optimization

The SQL statements we often use during development are nothing more than insert and groupby. How can we optimize these SQL statements?

1. Insert data in large batches

When using the load command to import data, appropriate settings can improve the import speed.
For tables of the MyISAM storage engine, you can import a large amount of data in the following way:

Copy code 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 enable or disable the update of non-unique indexes in the MyISAM table. When importing a large amount of data to a non-empty MyISAM table, you can improve the import efficiency by setting these two commands.
When a large amount of data is imported to an empty MyISAM table, the index is created only after the data is imported first by default. You do not need to set the index.

Copy code The Code is as follows: load data infile '/home/MySQL/text_txt' into Table text

For InnoDB tables, this method cannot improve the efficiency of data import, but there are also several Optimization Methods for InnoDB tables.

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

2. Run set unique_checks = 0 before the data is imported, disable the uniqueness check, and run set unique_checks = 1 after the import to restore the uniqueness check, which improves the import efficiency.

3. if the application uses the automatic submission method, we recommend that you execute set autocommit = 0 before import, disable automatic submission, and execute set autocommit = 1 after import. Enable automatic submission, it can also improve the import efficiency.

Optimize insert statements

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

1. if you insert many rows from one customer at the same time, try to use the insert Statement of multiple value tables. This method will greatly shorten the link between the client and the MySQL database, close, and other consumption, the efficiency is faster than that of a single insert statement.

For example:Copy codeThe Code is as follows: insert into test values (1, 2)
Insert into test values (3, 4)
Insert into test values (5, 6)

Change the preceding three sentences to insert into test values )......

2. If you insert many rows from different customers, you can use the insert delayed statement to get a higher speed.

The meaning of delayed is to let the insert statement be executed immediately. In fact, the data is put in the memory queue and is not actually written to the disk, which is much faster than inserting each statement separately; low_priority, on the contrary, is inserted only after all other users read the table.

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

4. For batch insertion, you can increase the speed by adding the value of the bulk_insert_buffer_size variable. 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 following are some other considerations for MySQL performance optimization. You can combine them to make full use of MySQL performance.
MySQL DataBase Query Optimization
MySQL Optimization-related database commands
Optimization of my. ini in MySQL database 2 GB memory for multi-site and multi-compression settings