The Storage Engines - MySQLThis chapter covers the MySQL architecture, locking and concurrency, and transactions. It also discusses how to select the right engine and looks at each of MySQL's storage engines in detail. (From the book High Performance MYSQL: Optimization, Backups, Replication and Load Balancing, by Jeremy Zawodny and Derek Balling, ISBN: 0596-003064, O'Reilly Media, 2004.)
print this article SEARCH DEV SHED |
Now it’s time to look at each of MySQL’s storage engines in more detail. Table 2-3 summarizes some of the high-level characteristics of the handlers. The following sections provide some basic highlights and background about each table handler as well as any unusual characteristics and interesting features. Before going further, it’s worth noting that this isn’t an exhaustive discussion of MySQL’s storage engines. We assume that you’ve read (or at least know where to find) the information in the MySQL Reference Manual. Table 2-5: Storage Engine features in MySQL
*Prior to MySQL 4.0, InnoDB was available in MySQL-Max only. Most of MySQL’s disk-based tables have some basic things in common. Each database in MySQL is simply a subdirectory of MySQL’s data directory in the underlying filesystem.* Whenever you create a table, MySQL stores the table definition in a .frm file with the same name as the table. Thus, when you create a table named MyTable, MySQL stores the table definition in MyTable.frm. * In MySQL 5.0, the term “database” will likely morph into “schema.” To determine the type of a table, use the SHOW TABLE STATUS command. For example, to examine the user table in the mysql database, you execute the following: mysql> SHOW TABLE STATUS LIKE 'user' \G****************** 1. row *************************** Notice that it’s a MyISAM table. You might also notice a lot of other information and statistics in the output. Let’s briefly look at what each line means: Name The table’s name. Type The table’s type. Again, in some versions of MySQL, this may say “Engine” rather than “Type.” Row_format Dynamic, Fixed, or Compressed. Dynamic rows vary in length because they contain variable-length fields such as VARCHAR or BLOB. Fixed rows, which are always the same size, are made up of fields that don’t vary in length, such as CHAR and INTEGER. Compressed rows exist only in compressed tables (see the later section “Compressed MyISAM”). Rows The number of rows in the table. For non-transactional tables, this number is always accurate. For transactional tables, it is usually an estimate. Avg_row_length How many bytes the average row contains. Data_length How much data (in bytes) the entire table contains. Max_data_length The maximum amount of data this table can hold. In a MyISAM table with dynamic (variable length) rows, the index file for a table (tablename.MYI) stores row locations using 32-bit pointers into the data file (tablename.MYD). That means it can address only up to 4 GB of space by default. See the next section, “MyISAM Tables” for more details. For MyISAM tables with fixed-length rows, the limit is just under 4.3 billion rows. Index_length How much space is consumed by index data. Data_free The amount of space that has been allocated but is currently unused. Auto_increment The next AUTO_INCREMENT value. Create_time When the table was first created. Update_time When data in the table last changed. Check_time When the table was last checked using CHECK TABLE or myisamchk. Create_options Any other options that were specified when the table was created. Comment The comments, if any, that were set when the table was created.
blog comments powered by Disqus |
|
|
|
|
|
|
|
/ 55 
