理解Engine in Database

• Overview

  Clickhouse，DolphinDB都对引擎做了专门介绍。

  那什么是引擎，引擎的作用原理又是什么？

  From Dr. Richard Hipp, the creator of SQLite, SQL is a peculiar programming language rather a query language, each SQL statement is a program, and SQL descibes what instead of how.

  An sql database engine consists of two parts:

  • Compiler

    The compiler takes your program which is a statement of sql and compiles it down into procedures they’re going to be run;

  • Virtual Machine

    The Virtual Machine actually runs the procedures.

• 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.

  The term “database engine” is frequently used interchangeably with “database server” or “database management system”.

  The storage engine (or database engine) is a software component of a database management system responsible for storing, retrieving, and managing data in memory and on disk, designed to capture a persistent, long-term memory of each node.

• Design considerations

  Information in a database is stored as bits laid out as data structure in storage that take can be efficiently read from and written to given the properties of hardware.

• Data structures

  A data structure is an abstract construct that embeds data in a well defined manner.

  Common data structures:

  1. Orderd/unordered flat files
  2. hash tables
  3. B+ trees
  4. ISAM
  5. heaps

• MySQL engine

  Database engine provide the underlying functionality for MySQL to work with and process data.

• References

1. Dr. Richard Hipp: How SQL Database Engines Work
2. Working with MySQL database engines
3. What is a good book on implementing a Relational Database?