Other Optimization Tips
690
This is very important when you use MySQL storage engines such as
MyISAM
that has only table-
level locking (multiple readers with single writers). This also gives better performance with most
database systems, because the row locking manager in this case has less to do.
• If you need to collect statistics from large log tables, use summary tables instead of scanning the
entire log table. Maintaining the summaries should be much faster than trying to calculate statistics
“live.” Regenerating new summary tables from the logs when things change (depending on business
decisions) is faster than changing the running application.
• If possible, you should classify reports as “live” or as “statistical,” where data needed for statistical
reports is created only from summary tables that are generated periodically from the live data.
• Take advantage of the fact that columns have default values. Insert values explicitly only when the
value to be inserted differs from the default. This reduces the parsing that MySQL must do and
improves the insert speed.
• In some cases, it is convenient to pack and store data into a
BLOB
column. In this case, you must
provide code in your application to pack and unpack information, but this may save a lot of accesses
at some stage. This is practical when you have data that does not conform well to a rows-and-
columns table structure.
• Normally, you should try to keep all data nonredundant (observing what is referred to in database
theory as third normal form). However, there may be situations in which it can be advantageous to
duplicate information or create summary tables to gain more speed.
• Stored routines or UDFs (user-defined functions) may be a good way to gain performance for some
tasks. See
Section 18.2, “Using Stored Routines (Procedures and Functions)”
, and
Section 21.2,
“Adding New Functions to MySQL”
, for more information.
• You can increase performance by caching queries or answers in your application and then executing
many inserts or updates together. If your database system supports table locks, this should help to
ensure that the index cache is only flushed once after all updates. You can also take advantage of
MySQL's query cache to achieve similar results; see
Section 8.6.3, “The MySQL Query Cache”
.
• Use
INSERT DELAYED
when you do not need to know when your data is written. This reduces the
overall insertion impact because many rows can be written with a single disk write.
• Use
INSERT LOW_PRIORITY
when you want to give
SELECT
statements higher priority than your
inserts.
Use
SELECT HIGH_PRIORITY
to get retrievals that jump the queue. That is, the
SELECT
is
executed even if there is another client waiting to do a write.
LOW_PRIORITY
and
HIGH_PRIORITY
have an effect only for storage engines that use only table-
level locking (such as
MyISAM
,
MEMORY
, and
MERGE
).
• Use multiple-row
INSERT
statements to store many rows with one SQL statement. Many SQL
servers support this, including MySQL.
• Use
LOAD DATA INFILE
to load large amounts of data. This is faster than using
INSERT
statements.
• Use
AUTO_INCREMENT
columns so that each row in a table can be identified by a single unique
value. unique values.
• Use
OPTIMIZE TABLE
once in a while to avoid fragmentation with dynamic-format
MyISAM
tables.
See
Section 14.1.3, “
MyISAM
Table Storage Formats”
.
• Use
MEMORY
(
HEAP
) tables when possible to get more speed. See
Section 14.4, “The
MEMORY
(
HEAP
) Storage Engine”
.
MEMORY
tables are useful for noncritical data that is accessed often, such
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...