Oracle 5.0, Справочное руководство

Страница: 707 / 3232

Optimizing Non-
SELECT
Statements
687
To obtain faster insertions for transactional tables, you should use
START TRANSACTION
and
COMMIT
instead of
LOCK TABLES
.
Locking also lowers the total time for multiple-connection tests, although the maximum wait time for
individual connections might go up because they wait for locks. Suppose that five clients attempt to
perform inserts simultaneously as follows:
• Connection 1 does 1000 inserts
• Connections 2, 3, and 4 do 1 insert
• Connection 5 does 1000 inserts
If you do not use locking, connections 2, 3, and 4 finish before 1 and 5. If you use locking,
connections 2, 3, and 4 probably do not finish before 1 or 5, but the total time should be about 40%
faster.
INSERT
,
UPDATE
, and
DELETE
operations are very fast in MySQL, but you can obtain better overall
performance by adding locks around everything that does more than about five successive inserts
or updates. If you do very many successive inserts, you could do a
LOCK TABLES
followed by an
UNLOCK TABLES
once in a while (each 1,000 rows or so) to permit other threads to access the table.
This would still result in a nice performance gain.
INSERT
is still much slower for loading data than
LOAD DATA INFILE
, even when using the
strategies just outlined.
• To increase performance for
MyISAM
tables, for both
LOAD DATA INFILE
and
INSERT
, enlarge the
key cache by increasing the
key_buffer_size
[458] system variable. See Section 8.9.2, “Tuning
Server Parameters” .
8.3.2.2. Speed of
UPDATE
Statements
An update statement is optimized like a
SELECT
query with the additional overhead of a write. The
speed of the write depends on the amount of data being updated and the number of indexes that are
updated. Indexes that are not changed do not get updated.
Another way to get fast updates is to delay updates and then do many updates in a row later.
Performing multiple updates together is much quicker than doing one at a time if you lock the table.
For a
MyISAM
table that uses dynamic row format, updating a row to a longer total length may
split the row. If you do this often, it is very important to use
OPTIMIZE TABLE
occasionally. See
Section 13.7.2.5, “
OPTIMIZE TABLE
Syntax” .
8.3.2.3. Speed of
DELETE
Statements
The time required to delete individual rows is exactly proportional to the number of indexes.
To delete rows more quickly, you can increase the size of the key cache by increasing the
key_buffer_size
[458] system variable. See Section 8.9.2, “Tuning Server Parameters” .
To delete all rows from a table,
TRUNCATE TABLE tbl_name
is faster than than
DELETE FROM
tbl_name
. Truncate operations are not transaction-safe; an error occurs when attempting one in the
course of an active transaction or active table lock. See Section 13.1.21, “
TRUNCATE TABLE
Syntax” .
8.3.2.4. Speed of
REPAIR TABLE
Statements
REPAIR TABLE
for
MyISAM
tables is similar to using
myisamchk
for repair operations, and some of
the same performance optimizations apply:
•
myisamchk
has variables that control memory allocation. You may be able to its improve
performance by setting these variables, as described in Section 4.6.3.6, “
myisamchk
Memory
Usage” .