MySQL relational database

Why use relational database?

• Transaction support

• Structured data

Architecture

Data model

• one .frm file per table: store table metadata, e.g. table definition

• table data is stored as a file in disk

  • in InnoDB engine, multiple tales can share a file(.ibdata), user can also config one table per file(.ibd).

Index

• store a file in disk, supported in Storage Engine layer

• B+ Tree

• Single and multiple columns index

Index in different storage engine

MyISAM stores address to data, i.e. index file and data file stored separately.

InnoDB stores data as index file. Secondary index refers to primary index.

Primary index

Secondary index

Single column index

Multi-column index

Transaction

ACID

Redo/undo log

Reference

• https://juejin.cn/post/6850037271233331208#heading-0

• MySQL的日志 - redo log