搜尋
首頁資料庫mysql教程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中文網其他相關文章!

陳述
本文轉載於:CSDN。如有侵權,請聯絡admin@php.cn刪除
您什麼時候應該使用複合索引與多個單列索引?您什麼時候應該使用複合索引與多個單列索引?Apr 11, 2025 am 12:06 AM

在數據庫優化中,應根據查詢需求選擇索引策略:1.當查詢涉及多個列且條件順序固定時,使用複合索引;2.當查詢涉及多個列但條件順序不固定時,使用多個單列索引。複合索引適用於優化多列查詢,單列索引則適合單列查詢。

如何識別和優化MySQL中的慢速查詢? (慢查詢日誌,performance_schema)如何識別和優化MySQL中的慢速查詢? (慢查詢日誌,performance_schema)Apr 10, 2025 am 09:36 AM

要優化MySQL慢查詢,需使用slowquerylog和performance_schema:1.啟用slowquerylog並設置閾值,記錄慢查詢;2.利用performance_schema分析查詢執行細節,找出性能瓶頸並優化。

MySQL和SQL:開發人員的基本技能MySQL和SQL:開發人員的基本技能Apr 10, 2025 am 09:30 AM

MySQL和SQL是開發者必備技能。 1.MySQL是開源的關係型數據庫管理系統,SQL是用於管理和操作數據庫的標準語言。 2.MySQL通過高效的數據存儲和檢索功能支持多種存儲引擎,SQL通過簡單語句完成複雜數據操作。 3.使用示例包括基本查詢和高級查詢,如按條件過濾和排序。 4.常見錯誤包括語法錯誤和性能問題,可通過檢查SQL語句和使用EXPLAIN命令優化。 5.性能優化技巧包括使用索引、避免全表掃描、優化JOIN操作和提升代碼可讀性。

描述MySQL異步主奴隸複製過程。描述MySQL異步主奴隸複製過程。Apr 10, 2025 am 09:30 AM

MySQL異步主從復制通過binlog實現數據同步,提升讀性能和高可用性。 1)主服務器記錄變更到binlog;2)從服務器通過I/O線程讀取binlog;3)從服務器的SQL線程應用binlog同步數據。

mysql:簡單的概念,用於輕鬆學習mysql:簡單的概念,用於輕鬆學習Apr 10, 2025 am 09:29 AM

MySQL是一個開源的關係型數據庫管理系統。 1)創建數據庫和表:使用CREATEDATABASE和CREATETABLE命令。 2)基本操作:INSERT、UPDATE、DELETE和SELECT。 3)高級操作:JOIN、子查詢和事務處理。 4)調試技巧:檢查語法、數據類型和權限。 5)優化建議:使用索引、避免SELECT*和使用事務。

MySQL:數據庫的用戶友好介紹MySQL:數據庫的用戶友好介紹Apr 10, 2025 am 09:27 AM

MySQL的安裝和基本操作包括:1.下載並安裝MySQL,設置根用戶密碼;2.使用SQL命令創建數據庫和表,如CREATEDATABASE和CREATETABLE;3.執行CRUD操作,使用INSERT,SELECT,UPDATE,DELETE命令;4.創建索引和存儲過程以優化性能和實現複雜邏輯。通過這些步驟,你可以從零開始構建和管理MySQL數據庫。

InnoDB緩衝池如何工作,為什麼對性能至關重要?InnoDB緩衝池如何工作,為什麼對性能至關重要?Apr 09, 2025 am 12:12 AM

InnoDBBufferPool通過將數據和索引頁加載到內存中來提升MySQL數據庫的性能。 1)數據頁加載到BufferPool中,減少磁盤I/O。 2)臟頁被標記並定期刷新到磁盤。 3)LRU算法管理數據頁淘汰。 4)預讀機制提前加載可能需要的數據頁。

MySQL:初學者的數據管理易用性MySQL:初學者的數據管理易用性Apr 09, 2025 am 12:07 AM

MySQL適合初學者使用,因為它安裝簡單、功能強大且易於管理數據。 1.安裝和配置簡單,適用於多種操作系統。 2.支持基本操作如創建數據庫和表、插入、查詢、更新和刪除數據。 3.提供高級功能如JOIN操作和子查詢。 4.可以通過索引、查詢優化和分錶分區來提升性能。 5.支持備份、恢復和安全措施,確保數據的安全和一致性。

See all articles

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

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

AI Clothes Remover

AI Clothes Remover

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

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

AI Hentai Generator

AI Hentai Generator

免費產生 AI 無盡。

熱門文章

R.E.P.O.能量晶體解釋及其做什麼(黃色晶體)
3 週前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.最佳圖形設置
3 週前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.如果您聽不到任何人,如何修復音頻
3 週前By尊渡假赌尊渡假赌尊渡假赌
WWE 2K25:如何解鎖Myrise中的所有內容
3 週前By尊渡假赌尊渡假赌尊渡假赌

熱工具

MantisBT

MantisBT

Mantis是一個易於部署的基於Web的缺陷追蹤工具,用於幫助產品缺陷追蹤。它需要PHP、MySQL和一個Web伺服器。請查看我們的演示和託管服務。

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

強大的PHP整合開發環境

SublimeText3漢化版

SublimeText3漢化版

中文版,非常好用

PhpStorm Mac 版本

PhpStorm Mac 版本

最新(2018.2.1 )專業的PHP整合開發工具

SecLists

SecLists

SecLists是最終安全測試人員的伙伴。它是一個包含各種類型清單的集合,這些清單在安全評估過程中經常使用,而且都在一個地方。 SecLists透過方便地提供安全測試人員可能需要的所有列表,幫助提高安全測試的效率和生產力。清單類型包括使用者名稱、密碼、URL、模糊測試有效載荷、敏感資料模式、Web shell等等。測試人員只需將此儲存庫拉到新的測試機上,他就可以存取所需的每種類型的清單。