23.3. The SQL Parser and Storage Engine Tiers

A client retrieves data from tables or changes data in tables by sending requests to the server in the form of SQL statements such as SELECT, INSERT, or DELETE. The server executes each statement using a two-tier processing model:

• The upper tier includes the SQL parser and optimizer. The server parses each statement to see what kind of request it is, then uses its optimizer to determine how most efficiently to execute the statement. However, this tier does not interact directly with tables named by the statement.

• The lower tier comprises a set of storage engines. The server uses a modular architecture: Each storage engine is a software module to be used for managing tables of a particular type. The storage engine associated with a table directly accesses it to store or retrieve data. MyISAM, MEMORY, and InnoDB are some of the available engines. The use of this modular approach allows storage engines to be easily selected for inclusion in the server at configuration time. New engines also can be added relatively easily.

For the most part, the SQL tier is free of dependencies on which storage engine manages any given table. This means that clients normally need not be concerned about which engines are involved in processing SQL statements, and can access and manipulate tables using statements that are the same no matter which engine manages them. Exceptions to this engine-independence of SQL statements include the following:

• CREATE TABLE has an ENGINE option that enables you to specify which storage engine to use on a per-table basis. ALTER TABLE has an ENGINE option that enables you to convert a table to use a different storage engine.

• Some index types are available only for particular storage engines. For example, only the MyISAM engine supports full-text or spatial indexes.

• COMMIT and ROLLBACK have an effect only for tables managed by transactional storage engines such as InnoDB.