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

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

Disadvantages of Creating Many Tables in the Same Database
717
example, by joining the table to itself). Each concurrent open requires an entry in the table cache. The
first open of any
MyISAM
table takes two file descriptors: one for the data file and one for the index file.
Each additional use of the table takes only one file descriptor for the data file. The index file descriptor
is shared among all threads.
If you are opening a table with the
HANDLER tbl_name OPEN
statement, a dedicated table object
is allocated for the thread. This table object is not shared by other threads and is not closed until the
thread calls
HANDLER tbl_name CLOSE
or the thread terminates. When this happens, the table is put
back in the table cache (if the cache is not full). See Section 13.2.4, “
HANDLER
Syntax” .
You can determine whether your table cache is too small by checking the
mysqld
status variable
Opened_tables
[530] , which indicates the number of table-opening operations since the server
started:
mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 2741 |
+---------------+-------+
If the value is very large or increases rapidly, even when you have not issued many
FLUSH TABLES
statements, you should increase the table cache size. See Section 5.1.4, “Server System Variables” ,
and Section 5.1.6, “Server Status Variables” .
8.8.4. Disadvantages of Creating Many Tables in the Same Database
If you have many
MyISAM
tables in the same database directory, open, close, and create operations
are slow. If you execute
SELECT
statements on many different tables, there is a little overhead when
the table cache is full, because for every table that has to be opened, another must be closed. You can
reduce this overhead by increasing the number of entries permitted in the table cache.
8.8.5. How MySQL Uses Internal Temporary Tables
In some cases, the server creates internal temporary tables while processing queries. Such a table can
be held in memory and processed by the
MEMORY
storage engine, or stored on disk and processed by
the
MyISAM
storage engine. The server may create a temporary table initially as an in-memory table,
then convert it to an on-disk table if it becomes too large. Users have no direct control over when the
server creates an internal temporary table or which storage engine the server uses to manage it.
Temporary tables can be created under conditions such as these:
• If there is an
ORDER BY
clause and a different
GROUP BY
clause, or if the
ORDER BY
or
GROUP BY
contains columns from tables other than the first table in the join queue, a temporary table is created.
•
DISTINCT
combined with
ORDER BY
may require a temporary table.
• If you use the
SQL_SMALL_RESULT
option, MySQL uses an in-memory temporary table, unless the
query also contains elements (described later) that require on-disk storage.
To determine whether a query requires a temporary table, use
EXPLAIN
and check the
Extra
column
to see whether it says
Using temporary
. See Section 8.2.1, “Optimizing Queries with
EXPLAIN
” .
Some conditions prevent the use of an in-memory temporary table, in which case the server uses an
on-disk table instead:
• Presence of a
BLOB
or
TEXT
column in the table
• Presence of any column in a
GROUP BY
or
DISTINCT
clause larger than 512 bytes
• Presence of any column larger than 512 bytes in the
SELECT
list, if
UNION
or
UNION ALL
is used