Shulou(Shulou.com)06/03 Report--

The speed of the INSERT statement

The time it takes to insert a record consists of the following factors: connection: (3) send query to server: (2) analyze query: (2) insert record: (1x record size) insert index: (1x index) off: (1) this does not take into account the initial cost of opening the table, each query that runs concurrently is opened.

The size of the table slows down index insertion at the speed of logN (B-tree).

Some ways to speed up insertion:

If you insert many rows from the same client at the same time, insert several rows at the same time using an INSERT statement with multiple VALUE. This is faster than using a single-line INSERT statement (several times faster in some cases). If you are adding data to a non-empty table, you can adjust the bulk_insert_buffer_size variable to make the data insert faster. See section 5.3.3, "Server system variables".

If you insert many rows from different clients, you can speed up with INSERT DELAYED statements. See Section 13.2.4, "INSERT Grammar".

With MyISAM, if there are no deleted rows in the table, you can insert rows while the SELECT statement is running.

Use LOAD DATA INFILE when loading a table from a text file. This is usually 20 times faster than using many INSERT statements. See Section 13.2.5, "LOAD DATA INFILE Grammar".

When a table has many indexes, it is possible to do more to make LOAD DATA INFILE faster. Use the following procedure:

Create tables selectively with CREATE TABLE. Execute a FLUSH TABLES statement or command mysqladmin flush-tables. Use myisamchk-- keys-used=0-rq / path/to/db/tbl_name. This removes the use of all indexes from the table. Insert data into the table with LOAD DATA INFILE, which is fast because no indexes are updated. If you only want to read the table later, use myisampack to compress it. See section 15.1.3.3, "Compression Table Properties". Recreate the index with myisamchk-r-Q / path/to/db/tbl_name. This creates an index tree in memory before being written to disk, and it is faster because it avoids a large number of disk searches. As a result, the index tree is also perfectly balanced. Execute a FLUSH TABLES statement or mysqladmin flush-tables command. Note that if you insert an empty MyISAM table, LOAD DATA INFILE can also perform the previous optimization; the main difference is that it allows myisamchk to allocate more temporary memory for index creation than it does for the server to re-create the index when the LOAD DATA INFILE statement is executed.

You can also use ALTER TABLE tbl_name DISABLE KEYS instead of myisamchk-- keys-used=0-rq/path/to/db/tbl_name, and ALTER TABLE tbl_name ENABLE KEYS instead of myisamchk-r-q/path/to/db/tbl_name. In this way, you can also skip FLUSH TABLES.

Locking tables can speed up INSERT operations performed with multiple statements:

LOCK TABLES a WRITE;INSERT INTO a VALUES (1Magol 23), (2jue 34), (4jue 33); INSERT INTO a VALUES (8d26), (6jue 29); UNLOCK TABLES; improves performance because the index cache is flushed to disk only once after all INSERT statements have been completed. Generally, there are as many INSERT statements as there are index cache refreshes. If you can insert all rows with one statement, you don't need to lock.

For transaction tables, BEGIN and COMMIT should be used instead of LOCK TABLES to speed up insertion.

Locking will also reduce the overall time for multi-connection testing, although the maximum wait time for them to wait for locking will increase. For example:

Connection 1 does 1000 inserts

Connections 2, 3, and 4 do 1 insert

Connection 5 does 1000 inserts

If locking is not used, 2, 3, and 4 will be completed before 1 and 5. If locking is used, 2, 3, and 4 may not be completed before 1 or 5, but the overall time should be about 40% faster.

INSERT, UPDATE, and DELETE operations are fast in MySQL, and better overall performance can be achieved by locking operations that are inserted or updated more than five times continuously in a row. If you insert multiple times in a row, you can execute LOCK TABLES, followed immediately by UNLOCK TABLES (about every 1000 rows) to allow other threads to access the table. This will also get good performance.

INSERT loads data much more slowly than LOAD DATA INFILE, even using the above strategy.

In order to get faster speed for LOAD DATA INFILE and INSERT in the MyISAM table, expand the key high-speed buffer by adding the key_buffer_size system variable. See section 7.5.2, "tuning server parameters".

INSERT syntax

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...), (...),... [ON DUPLICATE KEY UPDATE col_name=expr,...] or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_nameSET col_name= {expr | DEFAULT},... [ON DUPLICATE KEY UPDATE col_name=expr,...] or:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT... [ON DUPLICATE KEY UPDATE col_name=expr,...]

I. the use of DELAYED

Use the deferred insert operation DELAYED adjuster to apply to INSERT and REPLACE statements. When the DELAYED insert operation arrives

The server puts the data rows in a queue and immediately returns a status message to the client, so that the client

The client can continue to operate before the data table is actually inserted into the record. If the reader derives from this data

The data is read in the table, and the data in the queue is held until there is no reader. Then the server.

Starts inserting data rows in the deferred data row (delayed-row) queue. While inserting the operation, the server

Also check to see if any new read requests arrive and wait. If so, the delayed data row queue is suspended

Allow the reader to continue. When there is no reader, the server starts inserting delayed rows again.

This process continues until the queue is empty. A few points to pay attention to:

INSERT DELAYED should be used only for INSERT statements that specify a list of values. The server ignores the DELAYED used for INSERT DELAYED...SELECT statements.

The server ignores the DELAYED used for INSERT DELAYED...ON DUPLICATE UPDATE statements.

Because the statement returns immediately before the row is inserted, you cannot use LAST_INSERT_ID () to get the AUTO_ index value. The AUTO_ index value may be generated by a statement.

For SELECT statements, DELAYED rows are not visible until they are actually inserted.

DELAYED is ignored in the secondary replication server because DELAYED does not produce data in the secondary server that is different from the primary server. Note that currently the rows in the queue are only stored in memory until they are inserted into the table. This means that if you forcibly abort mysqld (for example, using kill-9)

Or if mysqld stops unexpectedly, all rows that have not been written to disk will be lost.

Second, the use of IGNORE IGNORE is an extension of MySQL relative to the standard SQL. If there are duplicate keywords in the new table

Or when there is a warning after STRICT mode is started, use IGNORE to control the operation of ALTER TABLE.

If no IGNORE is specified, when a repeat keyword error occurs, the copy operation is abandoned and returns to the previous step.

If IGNORE is specified, for rows with duplicate keywords, only the first line is used, and other conflicting lines are deleted.

In addition, the error value is corrected to make it as close as possible to the correct value. Insert ignore into tb (...) Value (...) If you specify ON DUPLICATE KEY UPDATE, and if you insert a row, it will cause duplicate values in an UNIQUE index or PRIMARY KEY, then execute the old row UPDATE. For example, if column an is defined as UNIQUE and contains a value of 1, the following two statements have the same effect:

Mysql > INSERT INTO table (a dint bjorn c) VALUES (1m 2pm 3)

-> ON DUPLICATE KEY UPDATE c=c+1

Mysql > UPDATE table SET c=c+1 WHERE axi1

If the row is inserted as a new record, the value of the affected row is 1; if the original record is updated, the value of the affected row is 2.

Note: if column b is also the only column, INSERT is equivalent to this UPDATE statement:

Mysql > UPDATE table SET c=c+1 WHERE axi1 OR bread2 LIMIT 1

If axi1 OR bread2 matches more than one row direction, only one row is updated. In general, you should try to avoid using the ON DUPLICATE KEY clause on tables with multiple unique keywords.

You can use the VALUES (col_name) function in the UPDATE clause from the INSERT... The INSERT section of the UPDATE statement refers to the column value. In other words, if there is no duplicate keyword conflict, the VALUES (col_name) in the UPDATE clause can refer to the value of the inserted col_name. This function is especially suitable for multi-line inserts. The VALUES () function is only used in INSERT... There is meaning in the UPDATE statement, and NULL is returned at other times.

Example:

Mysql > INSERT INTO table (a recorder bpenary c) VALUES (1pm 2m 3), (4m 5pm 6)

-> ON DUPLICATE KEY UPDATE c=VALUES (a) + VALUES (b)

This statement has the same effect as the following two statements:

Mysql > INSERT INTO table (a dint bjorn c) VALUES (1m 2pm 3)

-> ON DUPLICATE KEY UPDATE Centro 3

Mysql > INSERT INTO table (a _ c _

-> ON DUPLICATE KEY UPDATE censor 9

When you use ON DUPLICATE KEY UPDATE, the DELAYED option is ignored.

More java,java study, java interview questions http://techfoxbbs.com

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.