搜尋
首頁資料庫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刪除
图文详解mysql架构原理图文详解mysql架构原理May 17, 2022 pm 05:54 PM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于架构原理的相关内容,MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层,下面一起来看一下,希望对大家有帮助。

mysql怎么替换换行符mysql怎么替换换行符Apr 18, 2022 pm 03:14 PM

在mysql中,可以利用char()和REPLACE()函数来替换换行符;REPLACE()函数可以用新字符串替换列中的换行符,而换行符可使用“char(13)”来表示,语法为“replace(字段名,char(13),'新字符串') ”。

mysql的msi与zip版本有什么区别mysql的msi与zip版本有什么区别May 16, 2022 pm 04:33 PM

mysql的msi与zip版本的区别:1、zip包含的安装程序是一种主动安装,而msi包含的是被installer所用的安装文件以提交请求的方式安装;2、zip是一种数据压缩和文档存储的文件格式,msi是微软格式的安装包。

mysql怎么去掉第一个字符mysql怎么去掉第一个字符May 19, 2022 am 10:21 AM

方法:1、利用right函数,语法为“update 表名 set 指定字段 = right(指定字段, length(指定字段)-1)...”;2、利用substring函数,语法为“select substring(指定字段,2)..”。

mysql怎么将varchar转换为int类型mysql怎么将varchar转换为int类型May 12, 2022 pm 04:51 PM

转换方法:1、利用cast函数,语法“select * from 表名 order by cast(字段名 as SIGNED)”;2、利用“select * from 表名 order by CONVERT(字段名,SIGNED)”语句。

MySQL复制技术之异步复制和半同步复制MySQL复制技术之异步复制和半同步复制Apr 25, 2022 pm 07:21 PM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于MySQL复制技术的相关问题,包括了异步复制、半同步复制等等内容,下面一起来看一下,希望对大家有帮助。

带你把MySQL索引吃透了带你把MySQL索引吃透了Apr 22, 2022 am 11:48 AM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了mysql高级篇的一些问题,包括了索引是什么、索引底层实现等等问题,下面一起来看一下,希望对大家有帮助。

mysql怎么判断是否是数字类型mysql怎么判断是否是数字类型May 16, 2022 am 10:09 AM

在mysql中,可以利用REGEXP运算符判断数据是否是数字类型,语法为“String REGEXP '[^0-9.]'”;该运算符是正则表达式的缩写,若数据字符中含有数字时,返回的结果是true,反之返回的结果是false。

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.能量晶體解釋及其做什麼(黃色晶體)
2 週前By尊渡假赌尊渡假赌尊渡假赌
倉庫:如何復興隊友
4 週前By尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒險:如何獲得巨型種子
4 週前By尊渡假赌尊渡假赌尊渡假赌

熱工具

Dreamweaver CS6

Dreamweaver CS6

視覺化網頁開發工具

SecLists

SecLists

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

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser是一個安全的瀏覽器環境,安全地進行線上考試。該軟體將任何電腦變成一個安全的工作站。它控制對任何實用工具的訪問,並防止學生使用未經授權的資源。

EditPlus 中文破解版

EditPlus 中文破解版

體積小,語法高亮,不支援程式碼提示功能

mPDF

mPDF

mPDF是一個PHP庫,可以從UTF-8編碼的HTML產生PDF檔案。原作者Ian Back編寫mPDF以從他的網站上「即時」輸出PDF文件,並處理不同的語言。與原始腳本如HTML2FPDF相比,它的速度較慢,並且在使用Unicode字體時產生的檔案較大,但支援CSS樣式等,並進行了大量增強。支援幾乎所有語言,包括RTL(阿拉伯語和希伯來語)和CJK(中日韓)。支援嵌套的區塊級元素(如P、DIV),