目錄
一、MySQL結構體系
二、MySQL Server組成部分
三、儲存引擎
首頁 資料庫 mysql教程 MySql優化之體系結構及儲存引擎(總結分享)

MySql優化之體系結構及儲存引擎(總結分享)

Dec 29, 2021 pm 06:36 PM
mysql

這篇文章為大家帶來了關於mysql體系架構以及儲存引擎總結的相關知識,希望對大家有幫助。

MySql優化之體系結構及儲存引擎(總結分享)

一、MySQL結構體系

總體上, 我們可以把MySQL 分成三層,分層再詳細一點的話可以將儲存引擎層再分為引擎層和儲存層:

# 連接層
最上層是一些客戶端和鏈接服務,包含本機sock 通訊和大多數基於客戶端/服務端工具實現的類似TCP/IP的通訊。主要完成一些類似連線處理、授權認證、及相關的安全方案。在該層上引入了執行緒池的概念,為透過認證安全接入的用戶端提供執行緒。同樣在該層上可以實現基於SSL的安全連結。伺服器也會為安全存取的每個用戶端驗證它所具有的操作權限。

服務層
第二層架構主要完成大多數的核心服務功能,如SQL接口,並完成快取的查詢,SQL的分析和最佳化,部分內建函數的執行。所有跨儲存引擎的功能也在這一層實現,如 流程、函數等。在該層,伺服器會解析查詢並建立對應的內部解析樹,並對其完成對應的最佳化如確定表的查詢的順序,是否利用索引等, 最後產生對應的執行操作。如果是select語句,伺服器也會查詢內部的緩存,如果快取空間夠大,這樣在解決大量讀取操作的環境中能夠很好的提升系統的效能。

引擎層
儲存引擎層, 儲存引擎真正的負責了MySQL中資料的儲存和提取,伺服器透過API和儲存引擎進行通訊。不同的儲存引擎有不同的功能,讓我們可以根據自己的需要,來選取合適的儲存引擎。

儲存層
資料儲存層, 主要是將資料儲存在檔案系統之上,並完成與儲存引擎的交互。和其他資料庫相比,MySQL有點與眾不同,它的架構可以在多種不同場景中應用並發揮良好作用。主要體現在儲存引擎上,插件式的儲存引擎架構,將查詢處理和其他的系統任務以及資料的儲存提取分開。這種架構可以根據業務的需求和實際需求選擇合適的儲存引擎。

二、MySQL Server組成部分

MySQL結構各層又由一個或數個部分構成:

連接層組成部分

Connectors:互動介面

MySQL向外提供的互動元件,如java,.net ,php等語言可以透過此元件來操作SQL語句,實現與SQL的互動

#服務層組成部分

Connection Pool : 連接池元件
用於管理、緩衝使用者的連接,執行緒處理等需要快取的需求

Management Services & Utilities : 管理服務與工具元件
用於系統管理和控制工具,例如備份還原、Mysql複製、叢集、安全管理等

#SQL Interface : SQL介面元件
用於接受使用者的SQL指令,如DML,DDL和預存程序等,並將最終結果傳回給使用者

Parser : 查詢分析器元件
SQL指令傳遞到解析器的時候會被解析器驗證與解析,先分析SQL指令語法的合法性,並嘗試將SQL指令分解成資料結構,若分解失敗,提示SQL語句不合理

Optimizer : 最佳化器元件
SQL語句在查詢之前會使用查詢最佳化器對查詢進行最佳化。
如:select id,name from user where age = 20;

  1. 這個select 查詢先根據where 語句進行選取,而不是先將表全部查詢出來以後再進行age過濾
  2. 這個select查詢先根據id和name進行屬性投影,而不是將屬性全部取出以後再進行過濾
  3. #將這兩個查詢條件聯接起來產生最終查詢結果

Caches & Buffers : 緩衝池元件
如果查詢快取有命中的查詢結果,查詢語句就可以直接去查詢快取中取資料。這個快取機制是由一系列小型快取組成的。例如表格緩存,記錄緩存,key緩存,權限緩存等

引擎層組成部分

Pluggable Storage Engines : 儲存引擎
儲存引擎就是儲存數據,建立索引,更新查詢資料等等技術的實作方式 。儲存引擎是基於表的,而不是基於庫的。所以儲存引擎也可稱為表格型別。對於表格的創建,資料的存儲,檢索,更新等都是由MySQL儲存引擎完成的,這也是MySQL儲存引擎在MySQL中扮演的重要角色。

MYSQL提供了插件式的儲存引擎架構,所以使用者可以根據不同的需求為資料表選擇不同的儲存引擎,使用者也可以依照自己的需求編寫自己的儲存引擎,或是編寫儲存引擎。甚至一個庫中不同的表使用不同的儲存引擎,這些都是允許的。

儲存層組成部分

#File System : 檔案系統

用於實際儲存MySQL資料庫檔案和一些日誌檔案等的系統。常見的如Linux、Windows等。

三、儲存引擎

常用的儲存引擎差異

在MySQL 5.5 版本之前, 預設的儲存引擎是MyISAM, 它是MySQL 隨附的。 5.5 版本之後預設的儲存引擎改成了InnoDB, 它是第三方公司為MySQL開發的。為什麼要改呢?

最主要的原因還是InnoDB支援事務, 支援行層級的鎖定, 對於業務一致性要求高的場景來說更適合。

不同儲存引擎建表語句:

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儲存引擎是如今MySQL的預設儲存引擎。 InnoDB儲存引擎提供了具有提交、回滾、崩潰復原能力的事務安全性。但是比較MyISAM的儲存引擎,InnoDB寫的處理效率差一些,並且會佔用更多的磁碟空間以保留資料和索引。

InnoDB儲存引擎不同於其他儲存引擎的特性:

  1. 交易控制
  2. 外鍵限制

InnoDB 儲存表和索引有以下兩種方式:

  1. 使用共享表空間存儲, 這種方式建立的表的表結構保存在.frm檔案中,資料和索引保存在innodb_data_home_dir 和innodb_data_file_path定義的表空間中,可以是多個文件。
  2. 使用多表空間存儲, 這種方式建立的表的表結構仍然存在 .frm檔案中,但是每個表的資料和索引單獨保存在 .ibd 中。

InnoDB 適合的使用環境:
InnoDB 是MySQL的預設儲存引擎,用於事務處理應用程序,支援外鍵。如果應用程式對交易的完整性有比較高的要求,在並發條件下要求資料的一致性,資料操作除了插入和查詢意外,還包含很多的更新、刪除操作,那麼InnoDB儲存引擎是比較合適的選擇。

InnoDB儲存引擎除了有效的降低由於刪除和更新導致的鎖定, 還可以確保事務的完整提交和回滾,對於類似於計費系統或者財務系統等對數據準確性要求比較高的系統,InnoDB是最適合的選擇。

MyISAM
MyISAM 不支援交易、也不支援外鍵,其優點是存取的速度快,對交易的完整性沒有要求或以SELECT、INSERT為主的應用基本上都可以使用這個引擎來建立表格。

有以下兩個比較重要的特點:

  • 不支援交易
  • 檔案儲存方式

每個MyISAM在磁碟上儲存成3個文件,其檔案名稱都和表名相同,但拓展名分別是:

  • .frm (儲存表定義);
  • .MYD(MYData ,儲存資料);
  • .MYI(MYIndex , 儲存索引);

MyISAM 適合的使用環境:
如果應用是以讀取操作和插入操作為主,只有很少的更新和刪除操作,並且對事務的完整性、並發性要求不是很高,那麼選擇這個儲存引擎是非常合適的。

MEMORY
Memory儲存引擎將資料表的資料存放在記憶體中。每個MEMORY表實際對應一個磁碟文件,格式是.frm ,該文件中只存儲表的結構,而其數據文件,都是存儲在內存中,這樣有利於數據的快速處理,提高整個表的效率。

MEMORY類型的表存取非常地快,因為他的資料是存放在記憶體中的,並且預設使用HASH索引 , 但是服務一旦關閉,表中的資料就會遺失。

MEMORY 適合的使用環境:
將所有資料保存在RAM中,在需要快速定位記錄和其他類似資料環境下,可以提供幾塊的存取。

MEMORY的缺陷就是對錶的大小有限制,太大的表無法緩存在記憶體中,其次是要確保表的資料可以恢復,資料庫異常終止後表中的資料是可以恢復的。 MEMORY表通常用於更新較不頻繁的小表,用以快速取得存取結果。

MERGE
MERGE儲存引擎是一組MyISAM表的組合,這些MyISAM表必須結構完全相同,MERGE表本身並沒有儲存數據,對MERGE類型的表可以進行查詢、更新、刪除操作,這些操作實際上是對內部的MyISAM表進行的。

對於MERGE類型表的插入操作,是透過INSERT_METHOD子句定義插入的表,可以有3個不同的值,使用FIRST 或LAST 值使得插入操作被相應地作用在第一或最後一個表上,不定義這個子句或定義為NO,表示不能對這個MERGE表執行插入操作。

可以對MERGE表進行DROP操作,但這個操作只是刪除MERGE表的定義,對內部的表是沒有任何影響的。


MERGE 適合的使用環境:
用來將一系列等同的MyISAM表以邏輯方式組合在一起,並作為一個物件引用他們。

MERGE表的優點是可以突破單一MyISAM資料表的大小限制,並且透過將不同的表分佈在多個磁碟上,可以有效的改善MERGE表的存取效率。這對於儲存諸如資料倉儲等VLDB環境十分合適。

推薦學習:mysql影片教學

#

以上是MySql優化之體系結構及儲存引擎(總結分享)的詳細內容。更多資訊請關注PHP中文網其他相關文章!

本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

Video Face Swap

Video Face Swap

使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱門文章

<🎜>:泡泡膠模擬器無窮大 - 如何獲取和使用皇家鑰匙
3 週前 By 尊渡假赌尊渡假赌尊渡假赌
北端:融合系統,解釋
3 週前 By 尊渡假赌尊渡假赌尊渡假赌
Mandragora:巫婆樹的耳語 - 如何解鎖抓鉤
3 週前 By 尊渡假赌尊渡假赌尊渡假赌

熱工具

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器

SublimeText3漢化版

SublimeText3漢化版

中文版,非常好用

禪工作室 13.0.1

禪工作室 13.0.1

強大的PHP整合開發環境

Dreamweaver CS6

Dreamweaver CS6

視覺化網頁開發工具

SublimeText3 Mac版

SublimeText3 Mac版

神級程式碼編輯軟體(SublimeText3)

熱門話題

Java教學
1665
14
CakePHP 教程
1423
52
Laravel 教程
1321
25
PHP教程
1269
29
C# 教程
1249
24
laravel入門實例 laravel入門實例 Apr 18, 2025 pm 12:45 PM

Laravel 是一款 PHP 框架,用於輕鬆構建 Web 應用程序。它提供一系列強大的功能,包括:安裝: 使用 Composer 全局安裝 Laravel CLI,並在項目目錄中創建應用程序。路由: 在 routes/web.php 中定義 URL 和處理函數之間的關係。視圖: 在 resources/views 中創建視圖以呈現應用程序的界面。數據庫集成: 提供與 MySQL 等數據庫的開箱即用集成,並使用遷移來創建和修改表。模型和控制器: 模型表示數據庫實體,控制器處理 HTTP 請求。

MySQL和PhpMyAdmin:核心功能和功能 MySQL和PhpMyAdmin:核心功能和功能 Apr 22, 2025 am 12:12 AM

MySQL和phpMyAdmin是強大的數據庫管理工具。 1)MySQL用於創建數據庫和表、執行DML和SQL查詢。 2)phpMyAdmin提供直觀界面進行數據庫管理、表結構管理、數據操作和用戶權限管理。

MySQL與其他編程語言:一種比較 MySQL與其他編程語言:一種比較 Apr 19, 2025 am 12:22 AM

MySQL与其他编程语言相比,主要用于存储和管理数据,而其他语言如Python、Java、C 则用于逻辑处理和应用开发。MySQL以其高性能、可扩展性和跨平台支持著称,适合数据管理需求,而其他语言在各自领域如数据分析、企业应用和系统编程中各有优势。

解決數據庫連接問題:使用minii/db庫的實際案例 解決數據庫連接問題:使用minii/db庫的實際案例 Apr 18, 2025 am 07:09 AM

在開發一個小型應用時,我遇到了一個棘手的問題:需要快速集成一個輕量級的數據庫操作庫。嘗試了多個庫後,我發現它們要么功能過多,要么兼容性不佳。最終,我找到了minii/db,這是一個基於Yii2的簡化版本,完美地解決了我的問題。

laravel框架安裝方法 laravel框架安裝方法 Apr 18, 2025 pm 12:54 PM

文章摘要:本文提供了詳細分步說明,指導讀者如何輕鬆安裝 Laravel 框架。 Laravel 是一個功能強大的 PHP 框架,它 упростил 和加快了 web 應用程序的開發過程。本教程涵蓋了從系統要求到配置數據庫和設置路由等各個方面的安裝過程。通過遵循這些步驟,讀者可以快速高效地為他們的 Laravel 項目打下堅實的基礎。

解決MySQL模式問題:TheliaMySQLModesChecker模塊的使用體驗 解決MySQL模式問題:TheliaMySQLModesChecker模塊的使用體驗 Apr 18, 2025 am 08:42 AM

在使用Thelia開發電商網站時,我遇到了一個棘手的問題:MySQL模式設置不當,導致某些功能無法正常運行。經過一番探索,我找到了一個名為TheliaMySQLModesChecker的模塊,它能夠自動修復Thelia所需的MySQL模式,徹底解決了我的困擾。

MySQL:結構化數據和關係數據庫 MySQL:結構化數據和關係數據庫 Apr 18, 2025 am 12:22 AM

MySQL通過表結構和SQL查詢高效管理結構化數據,並通過外鍵實現表間關係。 1.創建表時定義數據格式和類型。 2.使用外鍵建立表間關係。 3.通過索引和查詢優化提高性能。 4.定期備份和監控數據庫確保數據安全和性能優化。

MySQL:解釋的關鍵功能和功能 MySQL:解釋的關鍵功能和功能 Apr 18, 2025 am 12:17 AM

MySQL是一個開源的關係型數據庫管理系統,廣泛應用於Web開發。它的關鍵特性包括:1.支持多種存儲引擎,如InnoDB和MyISAM,適用於不同場景;2.提供主從復制功能,利於負載均衡和數據備份;3.通過查詢優化和索引使用提高查詢效率。

See all articles