Database engine

A database engine (or storage engine) is the underlying software component that a database management system (DBMS) uses to create, read, update and delete (CRUD) data from a database. Most database management systems include their own application programming interface (API) that allows the user to interact with their underlying engine without going through the user interface of the DBMS.

The term "database engine" is frequently used interchangeably with "database server" or "database management system". A "database instance" refers to the processes and memory structures of the running database engine.

Storage engines

See also: Comparison of MySQL database engines

Many of the modern DBMS support multiple storage engines within the same database. For example, MySQL supports InnoDB as well as MyISAM.

Some storage engines are transactional.

Name	License	Transactional	Compatibility	Notes
Aria	GPL	No	MariaDB and MySQL
Falcon	GPL	Yes	MySQL	Discontinued
InnoDB	GPL	Yes	MySQL and MariaDB	The default for MySQL and MariaDB
Memory	GPL	No	MySQL and MariaDB
MyISAM	GPL	No	MySQL	Was the default for MySQL
InfiniDB	GPL	No
TokuDB	GPL	Yes	MySQL and MariaDB	Uses fractal tree index
WiredTiger	GPL	Yes	MongoDB | Default for MongoDB
XtraDB	GPL	Yes	MariaDB and Percona Server for MySQL
RocksDB / MyRocks	GPL v2 or Apache 2.0	Yes	ArangoDB, Cassandra, MariaDB, MongoDB, MySQL, SurrealDB

Additional engine types include:

• Embedded database engines
• In-memory database engines

Design considerations

Information in a database is stored in the form of bits, laid out into data structures on storage hardware. These data structures are designed for efficient reads and writes to and from the storage hardware. Typically the storage hardware itself is designed to meet the requirements of various systems, including databases, that extensively utilize storage. An operating DBMS always utilizes several storage types simultaneously. These different storage types, such as flash memory and external disk storage, each require different data layout methods.

In principle, database storage can be viewed as a linear address space where every bit of data has a unique address. In practice, only a very small percentage of addresses are kept as initial reference points, which also require storage. Most data is accessed instead by indirection using displacement calculations (distance in bits from the reference points) and data structures which define access paths (using pointers) to all needed data in an effective manner, optimized for the needed data access operations.

Database storage hierarchy

A database, while in operation, resides simultaneously in several types of storage, forming a storage hierarchy. Inside of a contemporary computer hosting a DBMS, most of the "database" part resides, partially replicated, in volatile storage. Data that are actively being processed and manipulated reside inside the processor, possibly in processor's caches. These data are read from and written to memory, typically through a computer bus, which is usually a volatile storage component. Computer memory communicates data to and from external storage, typically through standard storage interfaces or networks (e.g., fibre channel, iSCSI).

A storage array, a common external storage unit, typically has storage hierarchy of its own. A fast cache, typically consisting of volatile and fast DRAM, is connected (via standard interfaces) to drives. These drives may have different speeds, like flash drives and non-volatile magnetic disk drives. Speed and price are generally correlated. The drives may be connected to magnetic tapes, on which the least active parts of a large database may reside. This may also be where backups are located.

Data structures

Main article: Database storage structures

A data structure is an abstract construct that embeds data in a well defined manner. An efficient data structure allows manipulation of the data in efficient ways. The data manipulation may include data insertion, deletion, updating and retrieval in various modes. A certain data structure type may be very effective in certain operations, and very ineffective in others. A data structure type is selected upon DBMS development to best meet the operations needed for the types of data it contains. Type of data structure selected for a certain task typically also takes into consideration the type of storage it resides in (e.g., speed of access, minimal size of storage chunk accessed, etc.). In some DBMSs database administrators have the flexibility to select among options of data structures to contain user data for performance reasons. Sometimes the data structures have selectable parameters to tune the database performance.

Databases may store data in many data structure types. Common examples are the following:

• ordered/unordered flat files
• hash tables
• B+ trees
• ISAM
• heaps

Data orientation and clustering

In contrast to conventional row-orientation, relational databases can also be column-oriented or correlational in the way they store data in any particular structure.

In general, substantial performance improvement is gained if different types of database objects that are usually utilized together are laid in storage in proximity, being "clustered". This usually allows to retrieve needed related objects from storage in minimum number of input operations (each sometimes substantially time-consuming). Even for in-memory databases clustering provides performance advantage due to common utilization of large caches for input-output operations in memory, with similar resulting behavior.

For example, it may be beneficial to cluster a record of an "item" in stock with all its respective "order" records. The decision of whether to cluster certain objects or not depends on the objects' utilization statistics, object sizes, caches sizes, storage types, etc.

Database indexing

Main article: Database index

Indexing is a technique some storage engines use for improving database performance. The many types of indexes share the common property that they reduce the need to examine every entry when running a query. In large databases, this can reduce query time/cost by orders of magnitude. The simplest form of index is a sorted list of values that can be searched using a binary search with an adjacent reference to the location of the entry, analogous to the index in the back of a book. The same data can have multiple indexes (an employee database could be indexed by last name and hire date).

Indexes affect performance, but not results. Database designers can add or remove indexes without changing application logic, reducing maintenance costs as the database grows and database usage evolves. Indexes can speed up data access, but they consume space in the database, and must be updated each time the data is altered. Indexes therefore can speed data access but slow data maintenance. These two properties determine whether a given index is worth the cost.

External links

• https://web.archive.org/web/20100330045149/http://dev.mysql.com/tech-resources/articles/storage-engine/part_3.html
• MySQL Administrator's Bible Chapter 11 "Storage Engines"