Step-by-step explanation of MySQL database performance adjustments

INSERT query speed: the time when a record is inserted is composed of the following: (3) Send a query to the server: (2) analyze the query: (2) INSERT a record: (1x record size) insert index: (1x index) Disabled: (1) the number here is a bit proportional to the overall time. This does not take into account the initial overhead of opening the table (it performs a query for each concurrent operation ).

INSERT query speed: the time when a record is inserted is composed of the following: (3) Send a query to the server: (2) analyze the query: (2) INSERT a record: (1 x record size) insert index: (1 x index) Disabled: (1) the number here is a bit proportional to the overall time. This does not take into account the initial overhead of opening the table (it performs a query for each concurrent operation ).

INSERT query speed:

The time for inserting a record is composed of the following:

Connection: (3)

Send query to server: (2)

Analysis query: (2)

Insert record: (1 x record size)

Insert index: (1 x index)

Close: (1)

The number here is a bit proportional to the overall time. This does not take into account the initial overhead of opening the table (it performs a query for each concurrent operation ).

The table size slows down index insertion at the speed of N log N (B tree.

Some methods to accelerate insertion:

If you INSERT multiple rows from the same customer at the same time, use the INSERT Statement of multiple value tables. This is faster than using separate INSERT statements (several times in some cases ).

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

NOTE: If MyISAM is used, if no row is deleted in the table, the row can be inserted While SELECT: s is running.

When a table is loaded from a text file, load data infile is used. This is usually 20 times faster than using many INSERT statements. When a table has many indexes, it is possible to do more work to make load data infile faster. Use the following process:

You can use create table to CREATE a TABLE. For example, use mysql or Perl-DBI.

Run the flush tables command or the shell command mysqladmin flush-tables.

Use myisamchk -- keys-used = 0-rq/path/to/db/tbl_name. This will delete the use of all indexes from the table.

Use load data infile to insert DATA into the table. This will not update any indexes, so it is very fast.

If you have myisampack and want to compress the table, run myisampack on it.

Use myisamchk-r-q/path/to/db/tbl_name to create an index. This will create an index tree in the memory before writing it to the disk, and it is faster, because it avoids a large number of disk seek. The Results Index Tree is also perfectly balanced.

Run the flush tables command or the shell command mysqladmin flush-tables.

This process will be constructed into a future MySQL version of load data infile.

You can lock your table to accelerate insertion.

Mysql> lock tables a WRITE; mysql> insert into a VALUES (), (), (); mysql> insert into a VALUES ); mysql> unlock tables; the major speed difference is that the index buffer is only cleaned to the disk once, after all INSERT statements are completed. In general, there are different INSERT statements that result from index buffer cleaning. If you can use a single statement to insert all rows, locking is not required. Locking will also reduce the overall time for multi-connection testing, but the maximum wait time for some threads will rise (because they wait for the lock ). For example:

Thread 1 does 1000 insertsthread 2, 3, and 4 does 1 insert

Thread 5 does 1000 inserts if you do not use the lock, 2, 3, and 4 will be completed before 1 and 5. If you use locking, 2, 3, and 4 may not be completed before 1 or 5, but the overall time should be about 40% faster. Because INSERT, UPDATE, and DELETE operations are fast in MySQL, you will get better overall performance by locking more than five consecutive inserts or updates of a row. If you insert many rows, you can create a lock tables, and occasionally create an unlock tables (about every 1000 rows) to allow other threads to access the table. This will still result in good performance. Of course, load data infile is still faster for loading DATA.