當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL 体系结构详细介绍

發布時間：2025/3/21 数据库 25 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL 体系结构详细介绍小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

2019獨角獸企業重金招聘Python工程師標準>>>

一、MySQL軟件架構

? ? MySQL體系結構圖（邏輯結構）

1、Connectors

指的是不同語言中與MySQL的連接交互層；

2、Management Serveices & Utilities

系統管理和控制工具，例如備份恢復、MySQL復制、集群等；

3、Connection Pool

連接池，管理緩沖用戶連接、用戶名、密碼、權限校驗、線程處理等需要緩存的信息；
MySQL服務器對每一個連接產生一個線程，而這個線程獨自為該連接服務。因此，MySQL服務器中的并行是指并行執行許多個查詢而非一次查詢內的并行。也由此原因致使MySQL對多核支持不夠好，MySQL服務器是一組線程的集合。

4、SQL Interface

SQL接口，接受用戶的SQL命令，并且返回用戶需要查詢的結果。比如DML就是調用SQL Interface；

5、Parser

解析器，SQL命令傳遞到解析器的時候會被解析器驗證和解析。解析器是由Lex和YACC實現的，是一個很長的腳本；
主要功能：?

將SQL語句分解成數據結構，并將這個結構傳遞到后續步驟，以后SQL語句的傳遞和處理就是基于這個結構的；
如果在分解構成中遇到錯誤，那么就說明這個SQL語句是不合理的；
Lex：Lexical Analyzer是一種生成掃描器的工具。掃描器是一種識別文本中的詞匯模式的程序；
Yacc：Yet Another Compiler Compiler是一種工具，將任何一種編程語言的所有語法翻譯成針對此種語言的 Yacc 語法解析器；

6、Optimizer

查詢優化器，SQL語句在查詢之前會使用查詢優化器對查詢進行優化。他使用的是“選取-投影-聯接”策略進行查詢；
優化 select uid,name from user where gender = 1; SQL語句執行的過程如下：

這個select 查詢先根據where 語句進行記錄選取，而不是先將表全部記錄查詢出來以后再進行gender過濾；
這個select查詢先根據uid和name進行屬性投影（字段選擇），而不是將屬性全部取出以后再進行過濾；
將這兩個查詢條件聯接起來生成最終查詢結果；

7、Cache&Buffer

高速緩存區，查詢緩存，如果查詢緩存有命中的查詢結果，查詢語句就可以直接去查詢緩存中取數據。通過LRU算法將數據的冷端溢出，未來得及刷新到磁盤的數據頁，叫臟頁。這個緩存機制是由一系列小緩存組成的。比如表緩存，記錄緩存，key緩存，權限緩存等；?

8、Engine

存儲引擎，存儲引擎是MySQL與文件打交道的子系統。也是MySQL最具有特色的一個地方。MySQL的存儲引擎是插件式的,它根據MySQL AB公司提供的文件訪問層的一個抽象接口來定制一種文件訪問機制（這種訪問機制就叫存儲引擎）現在有很多種存儲引擎，各個存儲引擎的優勢各不一樣。MySQL也支持自定義制存儲引擎，甚至一個庫中不同的表使用不同的存儲引擎，這些都是允許的。

二、數據庫與數據庫實例

數據庫：物理操作系統文件或其他形式文件類型的集合；
數據庫實例：數據庫后臺進程或線程及一個共享內存區域組成，數據庫實例是用來操作數據庫文件的；
MySQL是一個單進程多線程架構的數據庫，與SQL Server類似。Oracle數據庫在Linux OS下是多進程，在Windows下也是單進程多線程。MySQL數據庫實例在系統上的表現就是一個進程。

三、MySQL常用存儲引擎

存儲引擎其實就是如何存儲數據，如何為存儲的數據建立索引以及如何更新、查詢數據等技術實現的方法。
MySQL中的數據用各種不同的技術存儲在文件（或內存）中，這些技術中的每一種技術都使用不同的存儲機制、索引技巧、鎖定水平，并且最終提供廣泛的不同功能和能力。在MySQL中將這些不同的技術及配套的相關功能稱為存儲引擎。

1、存儲引擎查詢

查看MySQL服務器支持的存儲引擎及默認的存儲引擎。存儲引擎是針對數據表的，不是針對數據庫的，通過如下命令查看具體表指定的存儲引擎。

mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ mysql> show variables like '%engine%'; +----------------------------------+--------+ | Variable_name | Value | +----------------------------------+--------+ | default_storage_engine | InnoDB | | default_tmp_storage_engine | InnoDB | | disabled_storage_engines | | | internal_tmp_disk_storage_engine | InnoDB | +----------------------------------+--------+ 4 rows in set (0.01 sec)

2、常用存儲引擎

Innodb是MySQL數據庫5.5版本后的默認存儲引擎，默認所說的MySQL即指Innodb存儲引擎的MySQL，所以關于Innodb的體系結構專門梳理介紹。

2.1、InnoDB
2.1.1、InnoDB體系結構圖

InnoDB是MySQL5.5以后的默認存儲引擎，支持事務ACID，回滾，系統崩潰恢復能力及多版本并發控制的事務安全，主要用于OLTP數據庫業務場景；支持自增長列（auto_increment）；支持外鍵約束（foreign key）；支持MVCC的行級鎖；使用Btree索引；
Innodb存儲引擎主要包括多個內存池以及后臺線程。
內存池：由Buffer Pool包括（undo page、Change buffer page、Adaptive Hash Index、Lock info、data dictionary），Doublewrite Buffer，Additional Memory pool、Redo Log Buffer 組成，主要維護進程/線程的內部數據、緩存磁盤數據，修改文件前先在內存中修改；
MySQL5.7官方文檔中關于Innodb存儲引擎的體系結構講解包括
Buffer Pool
Change Buffer
Adaptive Hash Index
Redo Log Buffer
System Tablespace
InnoDB Data Dictionary
Doublewrite Buffer
Undo Logs
File-Per-Table Tablespaces
General Tablespaces
Undo Tablespace
Temporary Tablespace
Redo Log
InnoDB有表空間的概念，包括共享表空間和獨立表空間（獨立表空間的模式中也要有系統表空間ibdataX，來用于存儲內部的數據字典、Undo日志等，通過innodb_file_per_table參數設置啟用獨立表空間），默認獨立表空間模式下會在數據目錄下創建tablename.ibd、tablename.frm文件。
InnoDB支持記錄Redo文件，記錄對所有頁面的修改（頁面物理結構的變更）操作，可以通過相關的參數進行自定義設置Redo文件存儲路徑；
InnoDB支持記錄Undo文件，記錄數據修改前的備份，存儲在共享表空間中。用戶保證事務的原子性（恢復）和實現MVCC多版本并發控制，輔助完成事務的持久化（Undo信息會持久化）。可通過相關參數進行自定義設置；
更改緩沖（change buffer），基于聚集索引的操作是順序的，不會造成數據庫隨機讀取，但修改非聚集索引時就會產生大量的隨機讀寫。當修改非聚集索引的數據時，修改操作并非實時更新索引的葉子頁,而是把若干對同一頁面的更新緩存起來做合并（merge）將一次性更新操作,轉化隨機IO為順序IO,這樣可以避免隨機IO帶來性能損耗，提高數據庫的寫性能。
寫兩次（double write），（重做日志記錄的是頁的物理操作，如果頁本身損壞，對其重做就沒有意義了，在應用重做日志前，需要一個頁的副本。先通過頁的副本還原該頁，再應用重做日志進行恢復）當mysql將臟數據flush到data file的時候，先使用memcopy將臟數據復制到內存中的double write buffer,之后通過double wirte buffer再分2次，每次寫入1M到共享表空間，然后馬上調用fsync函數，同步到磁盤上；將數據頁（page）加載到內存（innodb buffer）->更新數據產生臟頁（dirty page）->使用memcopy將臟數據復制到內存中的double write buffer（size=2M）->double wirte buffer再分2次，每次寫入1M到共享表空間（ibdata文件）->調用fsync函數，同步到磁盤；使用memcopy將臟數據復制到內存中的double write buffer（size=2M）->double wirte buffer再分2次，每次寫入1M到共享表空間（ibdata文件）就是double的過程。
自適應hash索引（adaptive hash index）,Innodb會監控表上索引的查找情況，如果通過建立Hash索引能帶來性能的提升，則會自動建立hash索引，該過程只能由MySQL Server自行控制，無法人工干預且只適用于等值索引查詢；

后臺線程：包括（Mater Thread、IO Thread、Lock Monitor Thread、Error Monitor Thread、Purge Thread、Page Cleaner Thread）刷新內存池中的過程數據，管理維護InnoDB存儲引擎正常工作；
InnoDB主要的后臺線程

Master Thread是一個非常核心的后臺線程，主要負責將緩沖池中的數據異步刷新到磁盤，保證數據的一致性，包括：臟頁（dirty page）的刷新、合并插入緩沖（insert buffer merge）、回滾頁回收（undo purge）等。innodb_max_dirty_pages_pct臟頁刷盤的配置參數，新版默認75，google默認90。

2.1.2、InnoDB表

InnoDB表邏輯結構

表空間：InnoDB存儲引擎邏輯結構的最高層
段：組成表空間，數據段，索引段，回滾段等
區：由64個連續的頁組成，每個區大小1MB
頁：每個頁16KB且不能修改，數據頁，索引頁，系統頁等
行：每頁最多允許存放7992行數據
行記錄格式：常見兩種行記錄格式Compact和Redundant，MySQL5.1版本后，主要是Compact行記錄格式。對于Compact，不管是char型還是varchar型，null型都是不占用存儲空間的；對于Redundant,varchar的null不占用空間，char的null型是占用存儲空間的。
Varchar類型的長度是65535,但實際一般除開其他開銷大概65530左右，同時這個限制是一整行數據的長度。
create table tbl_varchar(a varchar(22000), b varchar(22000), cvarchar(22000)) charset=latin1 engine=innodb; 因為一行總長大于了65535

數據頁結構

File Header(文件頭)：記錄頁的頭信息，固定長度38字節；
Page Header(頁頭)：記錄頁的狀態信息，固定長度65字節；
Infimum+Supremum Records：兩個虛擬的行記錄，用于限定記錄的邊界；
User Records：用戶記錄，實際記錄的內容，InnoDB采用B+樹索引組織存儲表；
Free Space：空閑空間，鏈表數據結構，記錄刪除后會被加入空閑空間；
Page Director：頁目錄，存放記錄的相對位置，B+索引不能找到具體的一條記錄，只能找到該記錄所在的頁，數據庫把頁載入內存，再通過Page Director查找具體記錄行；
File Trailer：文件結尾信息，固定長度8字節；

2.2、MyISAM

MyISAM是MySQL5.5之前的默認存儲引擎，不支持事務，不支持行級鎖，只支持并發插入的表鎖，主要用于高負載的查詢。
MySQL的系統表大部分都是MyISAM存儲引擎的，支持對只讀表進行壓縮，單壓縮后不能對該表進行修改只能查詢；
MyISAM按照插入的順序在磁盤上存儲數據，并為每行生成自然順序行號(row number)，從0開始。因為元組的大小固定，所以MyISAM可以很容易的從表的開始位置找到某一字節的位置。
MyISAM不支持聚簇索引，使用Btree索引，但實現細節與InnoDB不同，索引中每一個葉子節點僅僅包含索引字段關鍵字和行號(row number)，且葉子節點按照索引字段關鍵字的順序存儲，實際上，在MyISAM中，primary key和其它索引沒有什么區別。Primary key僅僅只是一個叫做PRIMARY的唯一，非空的索引而已。
創建MyISAM表，MySQL5.7的MyISAM表已經支持text,blob類型了。
mysql> create table tbl_myisam(id int,name varchar(20),describle text,image blob) engine=myisam;
mysql> create table table_myisam_compressed engine=myisam as select * from information_schema.columns;
Query OK, 3132 rows affected (0.23 sec)
Records: 3132 ?Duplicates: 0 ?Warnings: 0
MyISAM表默認在數據目錄下創建tablename.MYD、tablename.MYI、tablename.frm文件

[root@mysqlnode02 test1212]# ll -h tbl_myisam.* -rw-r----- 1 mysql mysql 8.5K Dec 12 09:28 tbl_myisam.frm -rw-r----- 1 mysql mysql 0 Dec 12 09:28 tbl_myisam.MYD -rw-r----- 1 mysql mysql 1.0K Dec 12 09:28 tbl_myisam.MYI

查看數據表的狀態信息，MyISAM表存儲格式，三種存儲格式(Row_format)：靜態（Fixed，char）、動態（Dynamic,varchar\blob\text）、壓縮（先創建表再通過myisampack工具壓縮處理）
mysql> show table status like 'tbl_myisam'\G
MyISAM表容易損壞，可以通過工具進行檢查和修復，如果有問題Msg_type里會有warning或error。沒有問題的話出現的結果Status是OK。可以使用repair命令修復損壞的MyISAM表，MySQL5.7修復后不生成*.old文件

2.3、Memory

Memory存儲引擎的表都是內存表。實際的數據存儲在內存中，磁盤中只有表結構定義文件tablename.frm，有利于快速處理，提高整個表的處理能力，重啟或關機數據會丟失，可提供極快的訪問。Mysql服務單獨給memory存儲引擎的表分配內存空間，而且表一直持有這些內存空間(即使刪除數據也不釋放內存)，直到有drop、alter、create等重建對象才能釋放內存。內存的占用空間由max_heap_table_size參數控制，默認16M，Mysql服務重啟后，所有表會自動繼承全局的max_heap_table_size參數的值；
創建memory存儲引擎表，不支持BLOB/TEXT類型

mysql> create table tbl_memory(id int,name varchar(20),describle text,image blob) engine=memory; ERROR 1163 (42000): The used table type doesn't support BLOB/TEXT columns mysql> create table tbl_memory(id int,name varchar(20)) engine=memory; Query OK, 0 rows affected (0.01 sec) mysql> show variables like 'max_heap_table_size'; +---------------------+----------+ | Variable_name | Value | +---------------------+----------+ | max_heap_table_size | 16777216 | +---------------------+----------+ 1 row in set (0.01 sec)

默認使用hash索引，對應等值查詢效率很高，對應范圍查詢性能不高；支持Btree索引，如要使用Btree索引需要明確指定創建索引類型；

mysql> create index idx_id on tbl_memory(id); mysql> create index idx_name using btree on tbl_memory(name); mysql> show index from tbl_memory\G

Memory存儲引擎的表只有一個tablename.frm文件

[root@mysqlnode02 test1212]# ll -h tbl_memory.* -rw-r----- 1 mysql mysql 8.4K Dec 12 09:46 tbl_memory.frm

2.4、CSV

CSV存儲引擎是基于CSV格式文件存儲數據的。該存儲引擎的表將數據以CSV文本方式存儲，可以手動直接編輯數據文件，也可通過DML語句操作CSV表，適用于進行小數據量的中間數據交換場景。

-- 創建CSV存儲引擎表，要求創建時指定各列非空約束。 mysql> create table tbl_csv(id int,name varchar(30)) engine=csv; ERROR 1178 (42000): The storage engine for the table doesn't support nullable columns mysql> create table tbl_csv(id int not null,name varchar(30) not null) engine=csv; Query OK, 0 rows affected (0.00 sec) -- CSV存儲引擎的表包括.frm是表結構定義文件；.CSV是存放數據文件；.CSM是表狀態及表的數據量文件 [root@mysqlnode02 test1212]# ll -h tbl_csv.* -rw-r----- 1 mysql mysql 35 Dec 12 09:54 tbl_csv.CSM -rw-r----- 1 mysql mysql 0 Dec 12 09:54 tbl_csv.CSV -rw-r----- 1 mysql mysql 8.4K Dec 12 09:54 tbl_csv.frm -- 手動編輯.CSV數據文件，刷新數據表，DML操作數據表 [root@mysqlnode02 test1212]# vi tbl_csv.CSV [root@mysqlnode02 test1212]# cat tbl_csv.CSV 1,'AAAAA' 2,BBBBB mysql> flush table tbl_csv; Query OK, 0 rows affected (0.00 sec) mysql> select * from tbl_csv; +----+---------+ | id | name | +----+---------+ | 1 | 'AAAAA' | | 2 | BBBBB | +----+---------+ 2 rows in set (0.00 sec) mysql> delete from tbl_csv where id=2; mysql> insert into tbl_csv values (id,'BBBBB');

2.5、BLACKHOLE

支持事務，而且支持mvcc的行級鎖，主要用于日志記錄或同步歸檔，這個存儲引擎除非有特別目的，否則不適合使用。

2.6、FEDERATED

能夠將多個分離的MySQL服務器鏈接起來，從多個物理服務器創建一個邏輯數據庫。十分適合于分布式環境或數據集市環境。

2.7、ARCHIVE

歸檔(適用于存放大量數據的存儲引擎)；支持select、insert等操作；不支持delete 、update、索引等操作；使用zlib無損數據壓縮算法，節省空間；適用于大量歷史數據(可查詢但不能刪除)的保存。
使用ARCHIVE存儲的空間大小是MyISAM存儲大小的1/7，大大減少了空間的占用。

mysql> create table tbl_archive engine=archive as select * from information_schema.columns; Query OK, 3153 rows affected (0.19 sec) Records: 3153 Duplicates: 0 Warnings: 0 -- .ARZ是數據壓縮文件；.frm是表結構定義文件 [root@mysqlnode02 test1212]# ll -h tbl_archive.* -rw-r----- 1 mysql mysql 30K Dec 12 10:20 tbl_archive.ARZ -rw-r----- 1 mysql mysql 14K Dec 12 10:20 tbl_archive.frm

四、物理結構（物理文件）

1、參數文件

告訴MySQL實例啟動時在哪找到數據庫文件，指定某些初始化參數，這些參數定義了某種內存結構的大小等設置。用文件存儲，可編輯，加載不到則不能成功啟動。參數有動態和靜態之分，靜態只讀，動態可以通過set命令設置。修改時有作用域的區分，通過session或global設置相應的有效域。

# 查看MySQL的參數文件讀取順序 [root@mysqlnode02 data]# mysql --help | grep 'my.cnf'order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

2、錯誤日志文件

記錄MySQL的啟動、運行、關閉過程進行了記錄，MySQL使用過程中的錯誤信息，還記錄了一些警告信息以及正確信息，可通過show variables like ‘log_error’查看具體的文件路徑。

3、慢查詢日志文件

記錄SQL語句執行較慢的語句，可通過slow_query_log參數控制是否開啟，slow_query_log_file參數控制對應的日志文件路徑，可通過mysqldumpslow工具查看慢日志。long_query_time參數設置慢查詢閥值，等于0記錄全部SQL，log_output 參數設置日志記錄文件或表中（file/Table）log_queries_not_using_indexes參數設置未使用索引的查詢也算慢查詢。

4、全查日志文件

記錄MySQL的所有請求信息（操作命令），通過general_log，general_log_file兩個參數控制開啟與路徑設置。

5、二進制日志文件

不記錄查詢，只記錄對數據庫所有的修改操作，用于恢復和復制。log_bin參數設置是否開啟（開啟時需要設置server_id），max_binlog_size 設置二進制日志的大小，64MB比較合適，log_bin_basename參數設置二進制日志文件的路徑和名稱前綴，binlog_format參數設置日志格式（statement，row,mixed）三種格式，可通過mysqlbinlog工具查看二進制日志文件內容。

6、Socket文件

當用UNIX套接字方式進行連接時需要指定的文件。修改了socket文件的目錄時，通過MySQL客戶端工具連接MySQL時需要-S指定socket文件。socket參數設置文件的存儲目錄和文件名，可以在my.cnf的[client]下增加socket配置后不用每次都指定。
[client]
socket = /MySQLdb/sockandpid/MySQL.sock

7、pid文件

MySQL實例的進程文件，MySQL實例啟動的時候，會將自己的進程id寫入一個文件中，pid_file參數設置pid文件的路徑和名稱，rpm默認安裝后會放在默認目錄中，重新初始化時需要在my.cnf文件中具體配置（自定義設置的路徑不起作用/var/run/mysqld/mysqld.pid）。
[root@mysqlnode02 data]# cat /var/run/mysqld/mysqld.pid
1403

8、表結構文件

存放MySQL表結構定義的文件，每個表都有一個.frm后綴的文件，存放在datadir對應的數據庫目錄里。

9、數據文件

存儲數據的文件，每個存儲引擎的數據文件后綴有一定的差異，MyISAM的數據文件是.MYD,InnoDB的數據文件是.ibd或.ibdata*。

10、索引文件

存儲索引的文件，每個存儲引擎的索引文件后綴會有一定的差異，MyISAM的索引文件是.MYI,InnoDB的索引文件存儲在.ibd文件中。

11、Innodb特有的文件

11.1、表空間文件

默認的表空間文件是%datadir%/ibdata1為統一表空間文件，可以通過innodb_file_per_table參數設單獨表空間文件，存儲各表對應的數據、索引、插入緩存等信息，其余信息還是存放在默認的表空間.ibdata1中；

11.2、Redo日志文件

記錄innodb存儲引擎對應數據所在的頁的更改物理情況，用于保證實例或介質失敗后的數據完整性恢復，先與數據持久化，innodb_log_file_size參數設置redo文件的大小，innodb_log_files_in_group參數設置redo文件組的文件數量，innodb_log_group_home_dir參數設置redo文件的路徑，ib_logfile*。
二進制日志與重做日志區別

11.3、Undo日志文件

存在于共享表空間ibdata1里面，有一個回滾段地址，里面存放了頭信息，配置頭信息，段的頭信息，里面存儲了與redo相反的數據更新操作，如果rollback的話，就把undo段里面數據回寫到數據文件里面。undo log用來完成事務的回滾以及MVCC的功能

五、MySQL內存結構

MySQL內存分配規則是，用多少給多少，最高到配置的值，不是立即分配；MySQL中內存大致分為全局內存（Global buffer）、線程內存（Thread buffer）兩大部分。

1、Global Buffer

innodb_buffer_pool_size(IBP)
InnoDB高速緩存data page和index page,是對InnoDB引擎影響最大的參數，建議設置為內存的50%-80%；合并插入緩存也是其中的一部分；
通過show global status like 'innodb_buffer_pool_%'\G;查看IBP的狀態，單位是page(16kb),如果Innodb_buffer_pool_wait_free較大需要增加IBP；
通過show status like 'innodb_data_read%';查詢的值計算IBP的各種效率，Innodb_data_read總共讀入的字節數，Innodb_data_reads發起請求的次數，每次讀取可能需要讀取多個頁；
IBP利用率：（1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests）*100%，Innodb_buffer_pool_reads（從物理磁盤讀取的頁數）Innodb_buffer_pool_read_requests（從緩沖池中讀取的頁數）；
IBP命中率：Innodb_buffer_pool_read_requests/(Innodb_buffer_pool_read_
requests+Innodb_buffer_pool_reads+Innodb_buffer_pool_read_ahead);
平均每次讀取的字節數：Innodb_data_read/Innodb_data_reads
innodb_log_buffer_size
InnoDB redo日志緩沖區，提高redo寫入效率，如果show global status like '%Innodb%'\G;查出Innodb_log_waits大于0則需要增加該區域大小；
query_cache_size
查詢高速緩沖，緩存結果，減少硬解析，建議關閉，通過專業的緩存redis等處理；

2、Thread Buffer

每個連接到MySQL服務器的線程都需要自己的緩沖，連接時大概會立即分配256k,甚至在現場空閑時他們使用默認的線程堆棧網絡緩存等。當需要進行復雜操作時會給分配相應的內存空間，操作完成后即釋放。
read_buffer_size：對表進行順序掃描的請求分配的讀入緩沖區；
read_rnd_buffer_size：對表進行任意行讀取時分配隨機讀緩存區；
sort_buffer_size：對查詢數據進行排序使用的緩存大小；
join_buffer_size：減少參與join的“被驅動表”的讀取次數以提高性能，當空間不足時會分批在join_buffer中進行；
binlog_cache_size：二進制日志緩存大小，提高二進制日志寫入效率，可以通過如下狀態信息查看Binlog_cache_disk_use，Binlog_cache_use；
thread_cache_size：默認是9需要根據業務進行比較的修改，如1024；

六、MySQL查詢執行過程

查詢緩存，判斷sql語句是否完全匹配，再判斷是否有權限，兩個判斷為假則到解析器解析語句，為真則提取數據結果返回給用戶；
解析器解析。解析器先詞法分析，語法分析，檢查錯誤比如引號有沒閉合等，然后生成解析樹；
預處理。預處理解決解析器無法決解的語義，如檢查表和列是否存在，別名是否有錯，生成新的解析樹；
優化器做大量的優化操作；
生成執行計劃；
查詢執行引擎，負責調度存儲引擎獲取相應數據；
返回結果

七、MVCC機制

MVCC是一種多版本并發控制機制，行級鎖對系統的開銷較大，通過保存數據在某個時間點的快照來實現MVCC可以替代行鎖，降低系統并發控制的開銷。
一致性的非鎖定讀：InnoDB存儲引擎通過行多版本控制的方式來讀取當前執行時間數據庫中行的數據。如果讀取的行正在執行Delete、update操作，這時讀取操作不會因此而會等待行上鎖的釋放，相反，InnoDB存儲引擎會去讀取行的一個快照數據。快照數據是指該行之前版本的數據，該實現是通過Undo段來實現。而Undo用來事務中回滾數據，因此快照本身是沒有額外開銷的。此外，快照數據是不需要上鎖的，因為沒有必要對歷史的數據進行修改。一個行可能有不止一個快照數據，所以稱這種技術為行多版本技術。由此帶來并發控制，稱之為多版本并發控制(Multi VersionConcurrency Control, MVCC)。

1、InnoDB的MVCC

MVCC (Multiversion Concurrency Control)，即多版本并發控制技術,它使得大部分支持行鎖的事務引擎，不再單純的使用行鎖來進行數據庫的并發控制，取而代之的是把數據庫的行鎖與行的多個版本結合起來，只需要很小的開銷,就可以實現非鎖定讀，從而大大提高數據庫系統的并發性能，MVCC是通過保存數據在某個時間點的快照來實現的。
讀鎖，S鎖，若當前事務T對數據對象A加S鎖，則T可以讀取A，其他事務可以對A加S鎖，但T和其他事務都不能修改A；
寫鎖，X鎖，若當前事務T對數據對象A加X鎖，則T可以讀也可以修改A，其他事務不能對A加任何鎖（即不能讀取或修改A）；
表鎖，操作對象是數據表，若當前事務T對表A加讀鎖，其他事務可讀不可寫；若加寫鎖，其他事務增刪改都不行；
行級鎖，操作對象是數據表中的一行數據，行級鎖是MySQL存儲引擎實現的，不是MySQL服務器實現的，行級鎖開銷大，但并發性好。
InnoDB的MVCC主要是為Repeatable-Read事務隔離級別做的，簡單的理解InnoDB是通過在每行記錄后面保存兩個隱藏的列來實現MVCC，這兩個列分別保存了這個行的創建時間和刪除時間（系統版本號，也可以理解為事務的ID），每開始一個事務，系統版本號會自動增加。通過這兩時間（系統版本號），在不同的事務隔離級別下操作相應的數據行。
Select：只會檢索創建時間版本早于當前系統版本號的數據行（系統版本號小于或等于當前事務對應的系統版本號）且行的刪除版本要么未定義，要么大于當前版本號；
Insert：InnoDB會為每個新增的行（創建時間）記錄當前系統版本號；
Delete：InnoDB會為每個刪除的行（刪除時間）記錄當前系統版本號；
Update：InnoDB會為新增的一行數據的（創建時間）記錄當前系統版本號，同時也會把被修改的數據行的（刪除時間）記錄當前系統版本號；
MVCC應有的特點，每行數據都存在一個版本，每次數據更新時都更新的該版本；修改是copy當前版本隨意修改，各事務之間無干擾；保存時比較版本號，如果成功（commit）,則覆蓋原記錄，失敗則放棄copy（rollback）。
InnoDB的實現方式，事務以排他鎖的方式修改原數據；把修改前的數據放入Undo log通過回滾指針與主數據關聯；修改成功則（commit）,失敗則利用undo log（rollback）。具體執行過程：begin->用排他鎖鎖定該行->記錄redo log->記錄undo log->修改當前行的值，寫事務編號，回滾指針指向undo log中的修改前的行->end。
mvcc中update步驟：
記錄事務中修改行數據的相應字段和值（包括舊版本事務id）在undo-log中記錄；
修改相應數據；
在redo-log中保存要修改的相應（新版本事務id）數據寫入；
假如update不能正常運行則根據undo-log redo-log 來恢復；
當然如果當前版本事務沒有commit的話則通過undo-log信息恢復原始數據狀態。
所以InnoDB的實現真算不上MVCC，因為并沒有實現核心的多版本并存，undo log中的內容只是串行化的結果，記錄了多個事務的過程，不屬于多版本共存。MVCC是通過版本號比對進行事務的提交或回滾，類似于通過樂觀鎖替代兩階段提交，兩階段提交是保證多行數據修改一致性的唯一手段，其本職是鎖定；樂觀鎖本質是消除鎖定，二者矛盾，所以理想的MVCC難以實現實際應用，InnoDB只是在Repeatable-Read事務隔離級別下實現的一種非阻塞讀而已。
InnoDB的MVCC是行鎖的一個補充，主要實現了“Repeatable-Read”模式下的非鎖定讀（讀非阻塞）。
在Read Committed和Repeatable Read下，InnoDB存儲引擎使用非鎖定一致性讀。然而，對于快照的定義卻不同。在Read Committed事務隔離級別下，對于快照數據，非鎖定一致性讀總是讀取被鎖定行的最新一份快照數據。在Repeatable事務隔離級別下，對于快照數據，非鎖定一致性讀總是讀取事務開始時的行數據版本。

2、mvcc優缺點

優點：在讀取數據時,innodb幾乎不用獲取任何鎖,在每個查詢通過版本檢查,只獲取需要的數據版本,提高系統并發度；
缺點:為了實現多版本,innodb必須對每行增加相應字段來存儲版本信息,同時需要維護每一行的版本信息,而且在檢索行的時候,需要進行版本的比較,因而減低了查詢效率;innodb還需要定期清理不再需要的行版本,及時回收空間,這也增加開銷；

八、MySQL的事務

1、事務隔離級別

MySQL的事務是存儲引擎層實現的，本部分主要討論事務存儲引擎InnoDB相關事務原理。SQL標準定義了4類隔離級別，包括了一些具體規則，用來限定事務內外的哪些改變是可見的，哪些是不可見的。低級別的隔離級一般支持更高的并發處理，并擁有更低的系統開銷。
Read Uncommitted（讀取未提交內容）
在該隔離級別，所有事務都可以看到其他未提交事務的執行結果。本隔離級別很少用于實際應用，因為它的性能也不比其他級別好多少。讀取未提交的數據，也被稱之為臟讀（Dirty Read）。
Read Committed（讀取提交內容）
這是大多數數據庫系統的默認隔離級別（但不是MySQL默認的）。它滿足了隔離的簡單定義：一個事務只能看見已經提交事務所做的改變。這種隔離級別也支持所謂的不可重復讀（Nonrepeatable Read），因為同一事務的其他實例在該實例處理其間可能會有新的commit，所以同一select可能返回不同結果。
Repeatable Read（可重讀）
這是mysql的默認事務隔離級別，它確保同一事務的多個實例在并發讀取數據時，會看到同樣的數據行。不過理論上，這會導致另一個棘手的問題：幻讀（Phantom Read）。簡單的說，幻讀指當用戶讀取某一范圍的數據行時，另一個事務又在該范圍內插入了新行，當用戶再讀取該范圍的數據行時，會發現有新的“幻影” 行。InnoDB和Falcon存儲引擎通過多版本并發控制（MVCC，Multiversion Concurrency Control）機制解決了該問題。
Serializable（可串行化）?
這是最高的隔離級別，它通過強制事務排序，使之不可能相互沖突，從而解決幻讀問題。簡言之，它是在每個讀的數據行上加上共享鎖。在這個級別，可能導致大量的超時現象和鎖競爭。serializable完全鎖定字段，若一個事務來查詢同一份數據就必須等待，直到前一個事務完成并解除鎖定為止。是完整的隔離級別，會鎖定對應的數據表格，因而會有效率的問題。
這四種隔離級別采取不同的鎖類型來實現，若讀取的是同一個數據的話，就容易發生問題。例如：
臟讀(Drity Read)：某個事務已更新一份數據，另一個事務在此時讀取了同一份數據，由于某些原因，前一個RollBack了操作，則后一個事務所讀取的數據就會是不正確的。
不可重復讀(Non-repeatable read):在一個事務的兩次查詢之中數據不一致，這可能是兩次查詢過程中間插入了一個事務更新了原有的數據。
幻讀(Phantom Read):在一個事務的兩次查詢中數據筆數不一致，例如有一個事務查詢了幾列(Row)數據，而另一個事務卻在此時插入了新的幾列數據，先前的事務在接下來的查詢中，就會發現有幾列數據是它先前所沒有的。
在MySQL中，實現了這四種隔離級別，分別有可能產生問題如下所示:

2、InnoDB事務

通過在log buffer中快速記錄SQL語句對數據頁的修改，并持久化至redo log文件，需要恢復時利用Redo log恢復相應數據；
通過拷貝修改前的數據至undo buffer，并持久化至undo log。rollback時利用undo log還原數據；
通過行級鎖，和MVCC機制提高數據庫并發能力的同時降低了系統資源開銷；
事務的四個特性：原子性、一致性、隔離性、持久性?
隔離性通過鎖實現，原子性、一致性、持久性通過數據庫的redo和undo來完成。?
重做日志記錄了事務的行為，通過redo實現，保證了事務的完整性，但事務有時還需要撤銷，這時就需要產生undo。undo和redo正好相反，對于數據庫進行修改時，數據庫不但會產生redo，而且還會產生一定的undo，即使執行的事務或語句由于某種原因失敗了，或者如果用一條rollback語句請求回滾，就可以用這些undo信息將數據回滾到修改之前的樣子。

3、InnoDB鎖信息查詢

在INFORMATION_SCHEMA架構下添加了INNODB_TRX、INNODB_LOCKS、InnoDB_LOCK_WAITS。通過這三張表，可以更簡單地監控當前的事務并分析可能存在的鎖的問題。
INNODB_TRX由8個字段組成：?
trx_id:InnoDB存儲引擎內部唯一的事務ID?
trx_state:當前事務的狀態。?
trx_started:事務的開始時間。?
trx_requested_lock_id:等待事務的鎖ID。如trx_state的狀態為LOCK WAIT,那么該值代表當前的等待之前事務占用鎖資源的ID.若trx_state不是LOCK WAIT,則該值為NULL。?
trx_wait_started:事務等待開始的時間。?
trx_weight:事務的權重，反映了一個事務修改和鎖住的行數。在InnoDB存儲引擎中，當發生死鎖需要回滾時，InnoDB存儲會選擇該值最小的進行回滾。?
trx_mysql_thread_id:Mysql中的線程ID,SHOW PROCESSLIST顯示的結果。?
trx_query:事務運行的sql語句。?
通過select * from infomation_schema.INNODB_TRX;可查看?
INNODB_LOCKS表，該表由如下字段組成：?
lock_id:鎖的ID。?
lock_trx_id:事務ID。?
lock_mode:鎖的模式。?
lock_type:鎖的類型，表鎖還是行鎖。?
lock_table:要加鎖的表。?
lock_index:鎖的索引。?
lock_space:InnoDB存儲引擎表空間的ID號。?
lock_page:被鎖住的頁的數量。若是表鎖，則該值為NULL。?
lock_rec:被鎖住的行的數量。若是表鎖，則該值為NULL。?
lock_data:被鎖住的行的主鍵值。當是表鎖時，該值為NULL。?
通過select * from information_schema.INNODB_LOCK;可查看?
INNODB_LOCK_WAIT由4個字段組成：?
requesting_trx_id:申請鎖資源的事務ID。?
requesting_lock_id:申請的鎖的ID。?
blocking_trx_id:阻塞的鎖的ID。?
通過select * from information_schema.INNODB_LOCK_WAITS;可查看。?

4、鎖的算法

Record Lock:單行記錄上的鎖?
Gap Lock:間隙鎖，鎖定一個范圍，但不包含記錄本身?
Next-Key Lock:Gap Lock + Record Lock，鎖定一個范圍，并且鎖定記錄本身。

5、鎖的問題

丟失更新：經典的數據庫問題，當兩個或多個事務選擇同一行，然后基于最初選定的值更新該行時，會發生丟失更新問題。每個事務都不知道其它事務的存在。最后的更新將重寫由其它事務所做的更新，這將導致數據丟失。　　
例:?
? ? 事務A和事務B同時修改某行的值，?
? ? ?1.事務A將數值改為1并提交?
? ? ?2.事務B將數值改為2并提交。?
? ? ?這時數據的值為2，事務A所做的更新將會丟失。?
? ? ?解決辦法：事務并行變串行操作，對更新操作加排他鎖。?
臟讀：一個事務讀到另一個事務未提交的更新數據，即讀到臟數據。?
例:?
? ? 1.Mary的原工資為1000, 財務人員將Mary的工資改為了8000(但未提交事務) ? ? ? ?
? ? 2.Mary讀取自己的工資 ,發現自己的工資變為了8000，歡天喜地！?
? ? 3.而財務發現操作有誤，回滾了事務,Mary的工資又變為了1000, 像這樣,Mary記取的工資數8000是一個臟數據。?
? ? 解決辦法：臟讀只有在事務隔離級別是Read Uncommitted的情況下才會出現，innoDB默認隔離級別是Repeatable Read，所以生產環境下不會出現臟讀。

不可重復讀：在同一個事務中,多次讀取同一數據,返回的結果有所不同。換句話說就是,后續讀取可以讀到另一個事務已提交的更新數據。相反"可重復讀"在同一事務多次讀取數據時,能夠保證所讀數據一樣,也就是后續讀取不能讀到另一事務已提交的更新數據。臟讀和不可重復讀的主要區別在于，臟讀是讀到未提交的數據，不可重復讀是讀到已提交的數據。
例:?
? ? ? 1.在事務1中，Mary 讀取了自己的工資為1000,操作并沒有完成?
? ? ? 2.在事務2中，這時財務人員修改了Mary的工資為2000,并提交了事務.?
? ? ? 3.在事務1中，Mary 再次讀取自己的工資時，工資變為了2000?
? ? ? 解決辦法：讀到已提交的數據，一般數據庫是可接受的，因此事務隔離級別一般設為Read Committed。Mysql InnoDB通過Next-Key Lock算法避免不可重復讀，默認隔離級別為Repeatable Read。
幻讀，事務中兩次讀取同一個范圍的數據，讀到的記錄條數不一致。

轉載于:https://my.oschina.net/peakfang/blog/2240253

總結

以上是生活随笔為你收集整理的MySQL 体系结构详细介绍的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：一个后端开发人员的node.js学习笔记
下一篇： SharePoint PowerShel