Home arrow MySQL arrow Page 2 - Data Definition Language, Part 1

4.1 General Database and Table Properties - MySQL

Studying for the MySQL Certification exam? This article, the first of two parts, covers roughly 10 percent of the material that will appear on the exam, including general database and table properties, storage engines and table types, and more. It is excerpted from chapter four of the book MySQL Certification Guide written by Paul Dubois et. al. (Sams, 2004, ISBN: 0672326329).

TABLE OF CONTENTS:
  1. Data Definition Language, Part 1
  2. 4.1 General Database and Table Properties
  3. 4.3 Limits on Number and Size of Database Components
  4. 4.4 Identifier Syntax
  5. 4.5 CREATE DATABASE and DROP DATABASE
  6. 4.7 DROP TABLE
  7. 4.9 Creating and Dropping Indexes
  8. 4.10 Column Types
  9. 4.10.2 String Column Types
  10. 4.10.3 Date and Time Column Types
  11. 4.10.4 Column Options
  12. 4.10.5 Using the AUTO_INCREMENT Column Option
  13. 4.10.6 Automatic Type Conversion and Value Clipping
By: Sams Publishing
Rating: starstarstarstarstar / 41
January 19, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Every MySQL server has a data directory under which it manages the contents of databases and tables. The server represents these using directories and files under the data directory as follows:

  • MySQL associates each database with a directory under the data directory. (This means that the data directory is the parent of all database directories.) A database directory has the same name as the database that it represents. For example, a database named world corresponds to a directory named world under the data directory. MySQL uses the database directory to manage the components of the database—that is, its tables and indexes. A database may be empty, or have one or more tables. Databases cannot be nested; one database cannot contain another.

  • Each table in a database consists of rows and columns. A table can be empty (it can have zero rows of data), but it must have at least one column. A table may also be indexed to improve query performance. Every table is associated with a format file in the database directory that contains the definition, or structure, of the table. The format filename is the same as the table name, plus an .frm suffix. For example, the format file for a table named Country in the world database is named Country.frm and is located in the world directory under the server's data directory. Depending on the table type, the storage engine for a table might create additional files for the table. If Country is a MyISAM table, the MyISAM storage engine creates data and index files named Country.MYD and Country.MYI to store data rows and indexes (respectively) for the table. If Country is an InnoDB table, MySQL still creates a Country.frm format file in the database directory, but the InnoDB storage engine stores the table data and index information elsewhere, in the InnoDB tablespace.

4.2 Storage Engines and Table Types

The MySQL server uses storage engines to manage data in tables.
Each storage engine handles a particular table type. Each table type has differing characteristics and features; these are summarized in this section as an overview. Elsewhere, this study guide concentrates primarily on the MyISAM and InnoDB table types, which are also discussed in more detail in the "Professional Study Guide." For
additional information on all table types, see the MySQL Reference Manual.

4.2.1 MyISAM Tables

The MyISAM storage engine manages tables that have the following characteristics:

  • Each MyISAM table is represented on disk by an .frm format file, as well as an .MYD datafile and an .MYI index file. All these files are located in the database directory.

  • MyISAM has the most flexible AUTO_INCREMENT column handling of all the table types.

  • MyISAM tables can be used to set up MERGE tables.

  • MyISAM tables can be converted into fast, compressed, read-only tables.

  • MyISAM supports FULLTEXT searching.

  • MySQL manages contention between queries for MyISAM table access using table-level locking. Query performance is very fast for retrievals. Multiple queries can read the same table simultaneously. For a write query, an exclusive table-level lock is used to prevent use of the table by other read or write queries, leading to reduced performance in environments with a mix of read and write queries. Deadlock cannot occur with table-level locking. (Deadlock occurs when two or more queries are blocked, or stopped from completing, because each is waiting for one of the others to finish.)

4.2.2 InnoDB Tables

The InnoDB storage engine manages tables that have the following characteristics:

  • Each InnoDB table is represented on disk by an .frm format file in the database directory, as well as data and index storage in the InnoDB tablespace. The InnoDB tablespace is a logical single storage area that is made up of one or more files or partitions on disk. The tablespace is shared by all InnoDB tables.

  • InnoDB supports transactions (using the SQL COMMIT and ROLLBACK statements) with full ACID compliance.

  • InnoDB provides auto-recovery after a crash of the MySQL server or the host where the server runs.

  • InnoDB supports foreign keys and referential integrity, including cascaded deletes and updates.

  • MySQL manages query contention for InnoDB tables using multi-versioning and row-level locking. Multi-versioning gives each transaction its own view of the database. This, combined with row-level locking, keeps contention to a minimum. The result is good concurrency even in an environment consisting of mixed reads and writes. However, it's possible for deadlock to occur. Multi-versioning is discussed further in the "Professional Study Guide."

4.2.3 MERGE Tables

The MERGE storage engine manages tables that have the following characteristics:

  • A MERGE table is a collection of identically structured MyISAM tables. Each MERGE table is represented on disk by an .frm format file and an .MRG file that lists the names of the constituent MyISAM files. Both files are located in the database directory.

  • Logically, a query on a MERGE table acts as a query on all the MyISAM tables of which it consists.

  • A MERGE table creates a logical entity that can exceed the maximum MyISAM table size.

4.2.4 BDB (Berkeley DB) Tables

The BDB storage engine manages tables that have the following characteristics:

  • Each BDB table is represented on disk by an .frm format file and a .db file that stores data and index information. Both files are located in the database directory.

  • BDB supports transactions (using the SQL COMMIT and ROLLBACK statements) with full ACID compliance.

  • BDB provides auto-recovery after a crash of the MySQL server or the host where the server runs.

  • MySQL manages query contention for BDB tables using page-level locking. This locking level provides concurrency performance that is intermediate to that of row-level and table-level locking. It's possible for deadlock to occur.

4.2.5 HEAP (MEMORY) Tables

The HEAP storage engine manages tables that have the following characteristics:

  • Each HEAP table is represented on disk by an .frm format file in the database directory. Table data and indexes are stored in memory.

  • In-memory storage results in very fast performance.

  • HEAP table contents do not survive a restart of the server. The table structure itself survives, but the table contains zero data rows after a restart.

  • HEAP tables use up memory, so they should not be used for large tables.

  • MySQL manages query contention for HEAP tables using table-level locking. Deadlock cannot occur.

This chapter excerpt is from MySQL Certification Guide by Paul Dubois et al. (Sams, 2004, ISBN: 0672326329 ). Check it out at your favorite bookstore today. Buy this book now.



 
 
>>> More MySQL Articles          >>> More By Sams Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: