Mysql Storage Engines
MySQL supports multiple storage engines. Storage Engines are MySQL components that handle the SQL operations for different table types and helps us to increase the database performance. So it is necessary to know about the MySQL storage engines so that we can use the right storage engines for our tables in the database.MySQL storage engines include both those that handle transaction-safe tables and non-transaction-safe tables.
Below are the MySQL supported Storage Engines :
- MyISAM
- InnoDB
- BLACKHOLE
- CSV
- MEMORY
- ARCHIVE
- FEDERATED
Before MYSQL 5.5.5, MyISAM was the default storage engine for the tables.MyISAM is extended from ISAM storage engine.
The MyISAM table is stored on disk in three files. The filename for these files begins with the table name and have an extension to indicate the file type.
- .frm file stores the table format
- .MYD (MYData) (data file)
- .MYI (MYIndex) (index file)
- MyISAM does not support database transactions so MyISAM tables are not trasaction safe.
- MYISAM does not support foreign key constraint.
- MYISAM occupies less memory sapce for tables and the tables size depends on the operating system.
- MyISAM provides high-speed data storage and retrieval.
- MyISAM supports full text searching.
- Maximum number of indexes per MyISAM table is 64.
- Maximum number of columns per index is 16.
- MyISAM has Table Locking.Permits only one CRUD operation at a time untill then the table will be locked.
- MyISAM is good for simple web applications, simple search engines as it supports full text search, online booking and content management systems.
From MySQL 5.5.5 InnoDB is the default storage engine. InnoDB maintains its own buffer pool for caching data and index in the main memory. Its DML operations follow ACID (Atomicity, Consistency, Isolation, Durability) model, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user data. It stores data in clustered indexes which reduces I/O for queries based on primary keys.
Note :-
- InnoDB supports Transactions so InnoDB tables are transaction safe.
- InnoDB supports commit, rollback, crash-recovery, roll and forward operations operations.
- InnoDB supports Foreign Key constraints.
- InnoDB data file can be stored in more than one file. It takes more disc space.
- InnoDB has row level locking only the row of the table that is being updated is locked.
- InnoDB is good for larger databases as it supports transactions and all like banking applications.
CSV means Comma Separated Value. The CSV storate engines stores the data in text files in comma seperated value format.
- The data file for the CSV starts with tablename and have an extension .csv.
- The .csv format can be easily read, modified by spreadsheet applications like MS Excel, OpenOffice excel, LibreOffice calc etc.,
- For read/search operations it has to read the entire file from top to bottom.
The BLACKHOLE storage engine acts as a “black hole” that accepts data but does not store it. When we try to retrieve the data fromt the BLACKHOLE table it always return an empty result.
ARCHIVE:-
The ARCHIVE storage engine used to store large amount of data without indexes in a very small footprint. It allows only INSERT and SELECT queries.
MEMORY:-
The MEMORY storage engine provides in-memory tables. It is formerly known as HEAP. The memory tables are stored in memory(RAM) and used hash indexes so that they are faster than MyISAM tables.MEMORY tables do not support columns with AUTO_INCREMENT, BLOB and TEXT characteristics.
FEDERATED :-
The FEDERATED storage engine helps to access data from remote MySQL database without using replication or cluster technology. When we query a local FEDERATED table it will automatically pulls the data from the remote (federated) tables. No data will be stored in local tables.
You can use the query below to know about the storage engines in your server.
mysql> show engines;
It will show the list of storage engines supported by your MySQL server as shown below.
Storage Engines Feature Summary :-
Each storage has its own use case. So it is better to have an idea about the advantages and disadvantages of the storage engines.The below image shows shows an overview of the storage engines provided with MySQL which are most commonly used in real time environment.
- InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.
- InnoDB support for FULLTEXT indexes is available in MySQL 5.6.4 and higher.
- Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.
- Compressed InnoDB tables require the InnoDB Barracuda file format.
- Implemented in the server (via encryption functions), rather than in the storage engine.
- Implemented in the server, rather than in the storage engine.
- Implemented in the server, rather than in the storage engine.
CREATE TABLE Test (username varchar(10)) ENGINE = MYISAM;
ALTER TABLE Test ENGINE = InnoDB;
No comments :
Post a Comment