# MySQL relational database

## Why use relational database?

* Transaction support
* Structured data

## Architecture

![](/files/-Mk_7wyhnPsIMKY2x_2F)

## 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`).

![](/files/-MlI29gnmoo7XU24bQlz)

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

![](/files/-Mksu0AfW4sIYPvz8BeQ)

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

![Primary index](/files/-Ml3HvxIc8iJwXToHwsR)

![Secondary index](/files/-MktOrXrayYNsOBTH55m)

![Single column index](/files/-MlIibdjKjs5VeumObam)

![Multi-column index](/files/-MkorJWwl5UL4nbSf4aR)

## Transaction

### ACID

### Redo/undo log

![](/files/-Mko8JIP9tWhtRIr5S4-)

## Reference

* <https://juejin.cn/post/6850037271233331208#heading-0>
* [MySQL的日志 - redo log](https://blog.csdn.net/javaanddonet/article/details/112596210?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522163304698816780357262491%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D\&request_id=163304698816780357262491\&biz_id=0\&utm_medium=distribute.pc_search_result.none-task-blog-2~all~first_rank_ecpm_v1~rank_v31_ecpm-1-112596210.pc_search_result_cache\&utm_term=redolog\&spm=1018.2226.3001.4187)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://jaywin.gitbook.io/leetcode/system-design/database-and-file-system/mysql-relational-database.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.