MySQL is an RDBMS owned by Oracle. It’s an widely used database server and was default in LAMP stack until MariaDB.
MySQL is based on tiered architecture, which components that interact with each other to store, read and execute SQL queries.
- Connection Layer
- SQL Engine
- Storage Engine
Connection Layer : Connection layer is responsible for making connection from clients. Client can connect MySQL through local sockets, ODBC or TCP/IP. Each connection is a thread in MySQL and MySQL assign memory to it call buffer.
There are three main buffer in MySQL, and all buffers are dynamically assign by MySQL process.
- read buffer : sequential scan
- sort buffer : shorting
- tmp table : temporary table
SQL Engine : Querying, storing and retrieving data.
Storage Engine : Storage engines is a MySQL components that handle operation on different table types. Storage engine module used to create, read, write and update data on database. storage engine works on table basis provide transaction and locking functionality.
If you want to check supported engines in your MySQL just run query show engines.
mysql> SHOW ENGINES;
Some of popular database engines in MySQL
- MyIsam: A non-transnational based storage engine design and optimized for speed . It’s fast and support table level locking. Ideal for Web applications and Data Warehousing.
- InnoDB: It’s an widely used transnational based and ACID-compliant storage engine support row level locking, crash recovery and foreign key constrains.
- Memory: Fastest storage engin used in memory cache for creating tables support table level locking. Ideal for creating for tmp tables for processing but data get lost if restart the system or service.
- CSV: Storage engin store data in CSV files, ideal for integration with other applications or data transfer.
Table locking : Whole table is get locked until changes are commit
row locking : Only row get locked until changes are committed.
Database structure on file-system
MyISM: In MySQL each database has it’s own directory and tables stored in the format of files in it.
table.frm : Table structure
table.MYD : Indexes of your table
table.MYI : Contain your data (Data file)
InnoDB: InnoDB has only .frm files and it’s own table space. Indexes and data is stored in shared databases call idbdata