MySQL’s most important feature is its storage-engine architecture, whose design separates query processing, optimization etc.. from data storage and retrieval. This feature let users to choose data storage and performance features and other characteristics.
Main functionality related to the processing of SQL, managing caches, Parsing and optimization lays in the third layer in the above picture.
Bottom two layers consists of storage engines and files system where physical storage of the data is kept. The server communicates with storage engine using storage engine APIs.
Optimization and Execution
MySQL parses queries and creates parse tree then applies various optimization techniques. Optimization includes rewriting the query, order of execution of table access and decide which index to use. Optimizations and storage engines are independent, optimizer does not care what is the storage engines of a particular tables. Often times the different tables in and SQL might be using different storage engines.
The server consults the query cache, which can store only SELECT statements, along with their result sets. If anyone issues a query that’s identical to one already in the cache, the server doesn’t need to parse, optimize, or execute the query at all—it can simply pass back the stored result set.
Most of the RDBMS provide some kind of logging to satisfy key features ACID (Atomicity, Consistence, Isolation and Durability). If there any system crash, MySQL has to recover to a consistent stage when the system start back on. During such a restart MySQL do recovery by doing rollforward or rollback using transaction log. Transaction logging also helps make transactions more efficient. Instead of updating the tables on disk each time a change occurs, the storage engine can change its in-memory copy of the data. MySQL provides two transactional storage engines: InnoDB and NDB Cluster. There are other third party storage engines that support transactions.
MySQL Storage Engines
We will see some of the MySQL storage engines and how they store data.
MyISAM storage engine store tables under data directory pointed by datadir. When you create a table, MyISAM stores the table definition in a .frm file with the same name as the table. Thus, when you create a table named Example, MyISAM stores the table definition in Example.frm and index information in .myi. Because MyISAM uses the filesystem to store database names and table definitions, case sensitivity depends on the platform.
InnoDB storage engine also store tables under data directory pointed by datadir. InnoDB stores data and index information in .ibd (ibdata) files in the database subdirectories. innodb_file_per_table option plays big role at storage level of InnoDB tables. By default, all InnoDB tables and indexes are stored in the system tablespace. As an alternative, you can store each InnoDB table and its indexes in its own file. This feature is called “multiple tablespaces” because each table that is created when this setting is in effect has its own tablespace.
To be continued …