Database Storage Engines in MySQL

What is Database Storage Engines in MySQL?
Data in MySQL is stored in files (or memory) using a variety of different techniques, and those techniques are call as a Database Storage Engine.

MySQL supported storage engines
Archive, Aria, Berkeley DB, BlitzDB, CONNECT, CSV, Falcon, InnoDB, MyISAM, NDB, InfiniDB, TokuDB, WiredTiger, XtraDB, Federated, FederatedX, CassandraSE, sequence, mroonga, SphinxSE.Database Storage Engines in MySQLMyISAM
•Default storage engine
•Fast storage engine
•Does not support transactions
•Provide table-level locking
•Mostly used in web, data warehousing

InnoDB
•Most widely used storage engine with transaction support
•An ACID compliant storage engine
•Supports row-level locking
•Provides foreign key referential integrity constraint
•Use tablespace for storing the data

File Structure of MyISAM

Database Storage Engines in MySQLFile Structure of InnoDB

Database Storage Engines in MySQLMyISAM v/s InnoDB

Database Storage Engines in MySQLDatabase Storage Engines in MySQL

FeatureMyISAMInnoDB
Storage limits256TB64TB
TransactionsNoYes
Locking granularityTableRow
MVCCNoYes
Geospatial data type supportYesYes
Geospatial indexing supportYesYes
B-tree indexesYesYes
T-tree indexesNoNo
Hash indexesNoNo
Full-text search indexesYesYes
Clustered indexesNoYes
Data cachesNoYes
Index cachesYesYes
Compressed dataYesYes
Encrypted dataYesYes
Cluster database supportNoNo
Replication supportYesYes
Foreign key supportNoYes
Backup / point-in-time recoveryYesYes
Query cache supportYesYes
Update statistics for data dictionaryYesYes

What is Tablespace in MySQL?
A tablespace is a storage location where the actual data underlying database objects can be kept. Tablespaces specify only the database storage locations, not the logical database structure. By using tablespaces, an administrator can control the disk layout of an installation. A common use of tablespaces is to optimize performance. For example, a heavily used index can be placed on a fast SCSI disk. On the other hand, a database table which contains archived data that is rarely accessed could be stored on a less expensive but slower IDE disk.

Click here to see What is Binary Log in MySQL?


<<Click here to see all posts>>

1 comment on “Database Storage Engines in MySQL”

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.