MySql優(yōu)化之體系結(jié)構(gòu)及存儲(chǔ)引擎的示例分析

這篇文章給大家分享的是有關(guān)MySql優(yōu)化之體系結(jié)構(gòu)及存儲(chǔ)引擎的示例分析的內(nèi)容。小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨小編過來看看吧。

一、MySQL結(jié)構(gòu)體系

總體上， 我們可以把 MySQL 分成三層，分層再詳細(xì)一點(diǎn)的話可以將存儲(chǔ)引擎層再分為引擎層和存儲(chǔ)層：

連接層
最上層是一些客戶端和鏈接服務(wù)，包含本地sock 通信和大多數(shù)基于客戶端/服務(wù)端工具實(shí)現(xiàn)的類似于 TCP/IP的通信。主要完成一些類似于連接處理、授權(quán)認(rèn)證、及相關(guān)的安全方案。在該層上引入了線程池的概念，為通過認(rèn)證安全接入的客戶端提供線程。同樣在該層上可以實(shí)現(xiàn)基于SSL的安全鏈接。服務(wù)器也會(huì)為安全接入的每個(gè)客戶端驗(yàn)證它所具有的操作權(quán)限。

服務(wù)層
第二層架構(gòu)主要完成大多數(shù)的核心服務(wù)功能，如SQL接口，并完成緩存的查詢，SQL的分析和優(yōu)化，部分內(nèi)置函數(shù)的執(zhí)行。所有跨存儲(chǔ)引擎的功能也在這一層實(shí)現(xiàn)，如 過程、函數(shù)等。在該層，服務(wù)器會(huì)解析查詢并創(chuàng)建相應(yīng)的內(nèi)部解析樹，并對(duì)其完成相應(yīng)的優(yōu)化如確定表的查詢的順序，是否利用索引等， 最后生成相應(yīng)的執(zhí)行操作。如果是select語句，服務(wù)器還會(huì)查詢內(nèi)部的緩存，如果緩存空間足夠大，這樣在解決大量讀操作的環(huán)境中能夠很好的提升系統(tǒng)的性能。

引擎層
存儲(chǔ)引擎層， 存儲(chǔ)引擎真正的負(fù)責(zé)了MySQL中數(shù)據(jù)的存儲(chǔ)和提取，服務(wù)器通過API和存儲(chǔ)引擎進(jìn)行通信。不同的存儲(chǔ)引擎具有不同的功能，這樣我們可以根據(jù)自己的需要，來選取合適的存儲(chǔ)引擎。

存儲(chǔ)層
數(shù)據(jù)存儲(chǔ)層， 主要是將數(shù)據(jù)存儲(chǔ)在文件系統(tǒng)之上，并完成與存儲(chǔ)引擎的交互。和其他數(shù)據(jù)庫相比，MySQL有點(diǎn)與眾不同，它的架構(gòu)可以在多種不同場(chǎng)景中應(yīng)用并發(fā)揮良好作用。主要體現(xiàn)在存儲(chǔ)引擎上，插件式的存儲(chǔ)引擎架構(gòu)，將查詢處理和其他的系統(tǒng)任務(wù)以及數(shù)據(jù)的存儲(chǔ)提取分離。這種架構(gòu)可以根據(jù)業(yè)務(wù)的需求和實(shí)際需要選擇合適的存儲(chǔ)引擎。

二、MySQL Server組成部分

MySQL結(jié)構(gòu)各層又由一個(gè)或數(shù)個(gè)部分構(gòu)成：

連接層組成部分

Connectors：交互接口

MySQL向外提供的交互組件，如java,.net,php等語言可以通過該組件來操作SQL語句，實(shí)現(xiàn)與SQL的交互

服務(wù)層組成部分

Connection Pool : 連接池組件
用于管理、緩沖用戶的連接，線程處理等需要緩存的需求

Management Services & Utilities : 管理服務(wù)和工具組件
用于系統(tǒng)管理和控制工具，例如備份恢復(fù)、Mysql復(fù)制、集群、安全管理等

SQL Interface : SQL接口組件
用于接受用戶的SQL命令，如DML,DDL和存儲(chǔ)過程等，并將最終結(jié)果返回給用戶

Parser : 查詢分析器組件
SQL命令傳遞到解析器的時(shí)候會(huì)被解析器驗(yàn)證和解析，首先分析SQL命令語法的合法性，并嘗試將SQL命令分解成數(shù)據(jù)結(jié)構(gòu)，若分解失敗，則提示SQL語句不合理

Optimizer : 優(yōu)化器組件
SQL語句在查詢之前會(huì)使用查詢優(yōu)化器對(duì)查詢進(jìn)行優(yōu)化。
如：select id,name from user where age = 20;

1. 這個(gè)select 查詢先根據(jù) where 語句進(jìn)行選取，而不是先將表全部查詢出來以后再進(jìn)行age過濾

2. 這個(gè)select查詢先根據(jù)id和name進(jìn)行屬性投影，而不是將屬性全部取出以后再進(jìn)行過濾

3. 將這兩個(gè)查詢條件聯(lián)接起來生成最終查詢結(jié)果

Caches & Buffers : 緩沖池組件
如果查詢緩存有命中的查詢結(jié)果，查詢語句就可以直接去查詢緩存中取數(shù)據(jù)。這個(gè)緩存機(jī)制是由一系列小緩存組成的。比如表緩存，記錄緩存，key緩存，權(quán)限緩存等

引擎層組成部分

Pluggable Storage Engines : 存儲(chǔ)引擎
存儲(chǔ)引擎就是存儲(chǔ)數(shù)據(jù)，建立索引，更新查詢數(shù)據(jù)等等技術(shù)的實(shí)現(xiàn)方式 。存儲(chǔ)引擎是基于表的，而不是基于庫的。所以存儲(chǔ)引擎也可被稱為表類型。對(duì)于表的創(chuàng)建，數(shù)據(jù)的存儲(chǔ)，檢索，更新等都是由MySQL存儲(chǔ)引擎完成的，這也是MySQL存儲(chǔ)引擎在MySQL中扮演的重要角色。

MYSQL提供了插件式的存儲(chǔ)引擎架構(gòu)，所以用戶可以根據(jù)不同的需求為數(shù)據(jù)表選擇不同的存儲(chǔ)引擎，用戶也可以根據(jù)自己的需要編寫自己的存儲(chǔ)引擎，或者編寫存儲(chǔ)引擎。甚至一個(gè)庫中不同的表使用不同的存儲(chǔ)引擎，這些都是允許的。

存儲(chǔ)層組成部分

File System : 文件系統(tǒng)

用于實(shí)際存儲(chǔ)MySQL 數(shù)據(jù)庫文件和一些日志文件等的系統(tǒng)。常見的如Linux、Windows等。

三、存儲(chǔ)引擎

常用的存儲(chǔ)引擎區(qū)別

在 MySQL 5.5 版本之前， 默認(rèn)的存儲(chǔ)引擎是 MyISAM， 它是 MySQL 自帶的。 5.5 版本之后默認(rèn)的存儲(chǔ)引擎改成了InnoDB， 它是第三方公司為MySQL開發(fā)的。為什么要改呢？

最主要的原因還是InnoDB支持事務(wù)， 支持行級(jí)別的鎖， 對(duì)于業(yè)務(wù)一致性要求高的場(chǎng)景來說更適合。

不同存儲(chǔ)引擎建表語句：

CREATE TABLE `user_innodb` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_myisam` (
  `id` int(11) PRIMARY KEY  AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_memory` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_archive` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=Archive DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_csv` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `gender` tinyint(1) NOT NULL,
  `phone` varchar(11) NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8mb4;

InnoDB
InnoDB存儲(chǔ)引擎是如今MySQL的默認(rèn)存儲(chǔ)引擎。InnoDB存儲(chǔ)引擎提供了具有提交、回滾、崩潰恢復(fù)能力的事務(wù)安全。但是對(duì)比MyISAM的存儲(chǔ)引擎，InnoDB寫的處理效率差一些，并且會(huì)占用更多的磁盤空間以保留數(shù)據(jù)和索引。

InnoDB存儲(chǔ)引擎不同于其他存儲(chǔ)引擎的特點(diǎn) ：

1. 事務(wù)控制

2. 外鍵約束

InnoDB 存儲(chǔ)表和索引有以下兩種方式 ：

1. 使用共享表空間存儲(chǔ)， 這種方式創(chuàng)建的表的表結(jié)構(gòu)保存在.frm文件中， 數(shù)據(jù)和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定義的表空間中，可以是多個(gè)文件。

2. 使用多表空間存儲(chǔ)， 這種方式創(chuàng)建的表的表結(jié)構(gòu)仍然存在 .frm文件中，但是每個(gè)表的數(shù)據(jù)和索引單獨(dú)保存在 .ibd 中。

InnoDB 適合的使用環(huán)境：
InnoDB 是MySQL的默認(rèn)存儲(chǔ)引擎，用于事務(wù)處理應(yīng)用程序，支持外鍵。如果應(yīng)用對(duì)事務(wù)的完整性有比較高的要求，在并發(fā)條件下要求數(shù)據(jù)的一致性，數(shù)據(jù)操作除了插入和查詢意外，還包含很多的更新、刪除操作，那么InnoDB存儲(chǔ)引擎是比較合適的選擇。

InnoDB存儲(chǔ)引擎除了有效的降低由于刪除和更新導(dǎo)致的鎖定， 還可以確保事務(wù)的完整提交和回滾，對(duì)于類似于計(jì)費(fèi)系統(tǒng)或者財(cái)務(wù)系統(tǒng)等對(duì)數(shù)據(jù)準(zhǔn)確性要求比較高的系統(tǒng)，InnoDB是最合適的選擇。

MyISAM
MyISAM 不支持事務(wù)、也不支持外鍵，其優(yōu)勢(shì)是訪問的速度快，對(duì)事務(wù)的完整性沒有要求或者以SELECT、INSERT為主的應(yīng)用基本上都可以使用這個(gè)引擎來創(chuàng)建表 。

有以下兩個(gè)比較重要的特點(diǎn)：

• 不支持事務(wù)

• 文件存儲(chǔ)方式

每個(gè)MyISAM在磁盤上存儲(chǔ)成3個(gè)文件，其文件名都和表名相同，但拓展名分別是：

• .frm (存儲(chǔ)表定義)；

• .MYD(MYData ,存儲(chǔ)數(shù)據(jù))；

• .MYI(MYIndex , 存儲(chǔ)索引)；

MyISAM 適合的使用環(huán)境：
如果應(yīng)用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對(duì)事務(wù)的完整性、并發(fā)性要求不是很高，那么選擇這個(gè)存儲(chǔ)引擎是非常合適的。

MEMORY
Memory存儲(chǔ)引擎將表的數(shù)據(jù)存放在內(nèi)存中。每個(gè)MEMORY表實(shí)際對(duì)應(yīng)一個(gè)磁盤文件，格式是.frm ，該文件中只存儲(chǔ)表的結(jié)構(gòu)，而其數(shù)據(jù)文件，都是存儲(chǔ)在內(nèi)存中，這樣有利于數(shù)據(jù)的快速處理，提高整個(gè)表的效率。

MEMORY類型的表訪問非常地快，因?yàn)樗臄?shù)據(jù)是存放在內(nèi)存中的，并且默認(rèn)使用HASH索引 ， 但是服務(wù)一旦關(guān)閉，表中的數(shù)據(jù)就會(huì)丟失。

MEMORY 適合的使用環(huán)境：
將所有數(shù)據(jù)保存在RAM中，在需要快速定位記錄和其他類似數(shù)據(jù)環(huán)境下，可以提供幾塊的訪問。

MEMORY的缺陷就是對(duì)表的大小有限制，太大的表無法緩存在內(nèi)存中，其次是要確保表的數(shù)據(jù)可以恢復(fù)，數(shù)據(jù)庫異常終止后表中的數(shù)據(jù)是可以恢復(fù)的。MEMORY表通常用于更新不太頻繁的小表，用以快速得到訪問結(jié)果。

MERGE
MERGE存儲(chǔ)引擎是一組MyISAM表的組合，這些MyISAM表必須結(jié)構(gòu)完全相同，MERGE表本身并沒有存儲(chǔ)數(shù)據(jù)，對(duì)MERGE類型的表可以進(jìn)行查詢、更新、刪除操作，這些操作實(shí)際上是對(duì)內(nèi)部的MyISAM表進(jìn)行的。

對(duì)于MERGE類型表的插入操作，是通過INSERT_METHOD子句定義插入的表，可以有3個(gè)不同的值，使用FIRST 或 LAST 值使得插入操作被相應(yīng)地作用在第一或者最后一個(gè)表上，不定義這個(gè)子句或者定義為NO，表示不能對(duì)這個(gè)MERGE表執(zhí)行插入操作。

可以對(duì)MERGE表進(jìn)行DROP操作，但是這個(gè)操作只是刪除MERGE表的定義，對(duì)內(nèi)部的表是沒有任何影響的。

MERGE 適合的使用環(huán)境：
用于將一系列等同的MyISAM表以邏輯方式組合在一起，并作為一個(gè)對(duì)象引用他們。

MERGE表的優(yōu)點(diǎn)在于可以突破對(duì)單個(gè)MyISAM表的大小限制，并且通過將不同的表分布在多個(gè)磁盤上，可以有效的改善MERGE表的訪問效率。這對(duì)于存儲(chǔ)諸如數(shù)據(jù)倉儲(chǔ)等VLDB環(huán)境十分合適。

感謝各位的閱讀！關(guān)于“MySql優(yōu)化之體系結(jié)構(gòu)及存儲(chǔ)引擎的示例分析”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，讓大家可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！