MySQL database optimization of the specific methods that

The following article is mainly about the specific method of implementing a simple and practical optimization of the MySQL database, as well as what specific steps in the actual operation are worthy of our attention. The following article describes the MySQL database is a simple practical optimization of the specific methods to achieve, including how to conduct regular table analysis and inspection, and how to properly optimize the table on a regular basis, the following is a description of the specific program, I hope in your future Learning will be helpful.

1, regular analysis of the table and checklist

The syntax of the analysis table is as follows:
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name [, tbl_name] ...

The above statement is used to analyze and store the keyword distribution of the table, and the result of the analysis will enable the system to obtain accurate statistical information so that the SQL can generate the correct execution plan. If the user feels that the actual execution plan is not the intended execution plan, performing an analysis may solve the problem. During analysis, a read lock is used to lock the table. This works for MyISAM, DBD and InnoDB tables.

For example, analyze a data table
analyze table table_name

Checklist syntax is as follows:

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

The role of the checklist is to check for errors in one or more tables. CHECK TABLE has effect on MyISAM and InnoDB tables. For MyISAM tables, keyword statistics are updated

CHECK TABLE can also check the view for 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 you delete a large portion of the table, or if you have made more changes to tables that have variable-length lines (tables that have VARCHAR, BLOB, or TEXT columns), use the OPTIMIZE TABLE command for table optimization. This command merges the space fragmentation in the table and eliminates space waste due to deletion or update, but the OPTIMIZE TABLE command only works on MyISAM, BDB, and InnoDB tables.

For example: optimize table table_name


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

Commonly used SQL optimization

We often used in the development of SQL statements, nothing more than INSERT, GROUPBY and so on. For these SQL statements, how do we optimize?

Insert data in bulk

When using the load command to import data, the appropriate settings can increase the speed of import.
MyISAM storage engine for the table, you can quickly import large amounts of data as follows

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

DISABLE KEYS and ENABLE KEYS used to open or close MyISAM table non-unique index updates. Importing large amounts of data to a non-empty MyISAM table, by setting these two commands, you can improve the import efficiency.
Import large amounts of data to an empty MyISAM table, the default is to import data before creating an index, the index without setting.
load data infile '/ home / mysql / text_txt' into table text

This does not improve the efficiency of importing data for InnoDB-typed tables, but there are several ways to optimize for InnoDB-typed tables.

1. Since InnoDB-type tables are stored in the order of their primary keys, the data to be imported is sorted in the order of primary keys, which can effectively improve the efficiency of importing data.

2. Perform SET UNIQUE_CHECKS = 0 before importing data, turn off the uniqueness check, execute SET UNIQUE_CHECKS = 1 after the import, and restore the uniqueness check to improve the import efficiency.

3.If the application uses automatic submission, it is recommended that you execute SET AUTOCOMMIT = 0 before importing, turn off automatic submission, and execute SET AUTOCOMMIT = 1 after the import, turn on automatic submission, and also improve the import efficiency.

Optimize the INSERT statement

When data INSERT, you can consider the following ways to optimize

1. If you insert a lot of rows from a client, try to use multiple value table INSERT statement, this approach will greatly reduce the client and the MySQL database link, shut down and other consumption, making the efficiency of a separate INSERT statement executed faster.

E.g:
insert into test values ​​(1,2)
insert into test values ​​(3,4)
insert into test values ​​(5,6)

Replace the above three sentences with: insert into test values ​​(1,2), (3,4), (5,6) ...

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

The meaning of DELAYED INSERT statement is executed immediately, in fact, the data is placed in the memory queue, and did not actually write to disk, which is inserted each statement is much faster than each; LOW_PRIORITY On the contrary, in all other users on the table Read and write after the insertion.

3. The index file and data files on a different disk storage

4. If you bulk insert, you can increase the bulk_insert_buffer_size variable value method to speed up, but this can only be used for the MyISAM table.

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.

Here are some other mysql performance optimization needs attention, we combine and give full play to the performance of mysql.