01_MySQL基礎架構

来源:https://www.cnblogs.com/LuoQi11/archive/2023/05/24/01_mysql-infrastructure-1y0uyh.html
-Advertisement-
Play Games

這個資料庫主要包含兩個表,考慮到原破解APP數據就是這樣也就不折分了,一個是有5186條記錄的腦筯急轉彎表,一個是有18326條記錄的謎語表,兩個表中的記錄都有詳細的分類欄位,具體看截圖下的分類統計。 腦筋急轉彎分類統計:燈謎(79)、動物(81)、兒童(190)、搞笑(77)、經典(110)、冷笑 ...


01_MySQL基礎架構

MySQL 45 講Note:

課程專欄名稱:《MySQL實戰45講》課程

筆記參考:MYSQL45 講

01_基礎架構:一條SQL查詢語句是如何執行的?

一條SQL查詢是如何執行的

先看一下下麵這個圖

img

我們首先理解一下 Mysql 的基礎架構,理解如果執行一條簡單的查詢語句,Mysql 進行了哪些操作。

在 MySql 的基礎架構種,他分為了Service 層和存儲引擎;

其中存儲引擎負責存儲和提取數據,Service 層包含了連接器,查詢緩存,優化器和執行層等,蘊含了Mysql 大多數的核心功能。

接下來我們先來瞭解一下他們的基礎概念。

存儲引擎

Mysql 常見的存儲引擎有 InnoDB、MyISAM、Memory 等多種,最常用的存儲引擎是​ InnoDB,它從 MySQL 5.5.5 版本開始成為了預設存儲引擎。

連接器

我們使用客戶端和 Mysql 進行連接的時候,Mysql 連接器就負責管理連接,建立連接,獲取許可權,維持連接

具體的一個連接操作:

連接命令中的 mysql 是客戶端工具,用來跟服務端建立連接。在完成經典的 TCP 握手後,連接器就要開始認證你的身份,這個時候用的就是你輸入的用戶名和密碼。

  • 如果用戶名或密碼不對,你就會收到一個"Access denied for user"的錯誤,然後客戶端程式結束執行。
  • 如果用戶名密碼認證通過,連接器會到許可權表裡面查出你擁有的許可權。之後,這個連接裡面的許可權判斷邏輯,都將依賴於此時讀到的許可權。

這就意味著,一個用戶成功建立連接後,即使你用管理員賬號對這個用戶的許可權做了修改,也不會影響已經存在連接的許可權。修改完成後,只有再新建的連接才會使用新的許可權設置。

客戶端如果太長時間沒動靜,連接器就會自動將它斷開。這個時間是由參數 wait_timeout 控制的,預設值是 8 小時。

建立連接的過程通常是比較複雜的,所以我建議你在使用中要儘量減少建立連接的動作,也就是儘量使用長連接****。

但如果全部使用長連接的話,會出現一個問題:

MySQL 建立連接的時候,每個客戶端連接都會有一個對應的連接對象(Connection Object),這個連接對象會維護連接過程中的一些狀態信息,比如事務狀態、鎖信息、臨時表等。同時,連接對象也會維護一些緩存信息,比如查詢結果緩存、語句緩存等。這些緩存信息會占用一定的記憶體空間。

當MySQL執行查詢語句時,會為查詢分配一些記憶體空間,用於存儲臨時表、排序緩存、哈希表等中間結果。這些記憶體空間是從連接對象中分配的,因此被稱為連接記憶體(Connection Memory)。這些記憶體空間只有在連接斷開的時候才會被釋放,因為它們是綁定在連接對象上的,只有當連接對象被銷毀時,這些記憶體空間才會被系統回收。

如果使用長連接,那麼連接對象會一直存在,連接記憶體也就會一直被占用。如果多個長連接同時存在,那麼這些連接對象和連接記憶體就會累積,導致MySQL占用的記憶體空間越來越大。因此,長連接也需要註意記憶體占用問題,需要在代碼中合理管理連接對象和連接記憶體的生命周期,避免記憶體泄漏和OOM問題的發生。

所以如果長連接累積下來,可能導致記憶體占用太大,被系統強行殺掉(OOM),從現象看就是 MySQL 異常重啟了。

怎麼解決這個問題呢?你可以考慮以下兩種方案。

  1. 定期斷開長連接。使用一段時間,或者程式裡面判斷執行過一個占用記憶體的大查詢後,斷開連接,之後要查詢再重連****。
  2. 如果你用的是 MySQL 5.7 或更新版本,可以在每次執行一個比較大的操作後,通過執行 mysql_reset_connection 來重新初始化連接資源。這個過程不需要重連和重新做許可權驗證,但是會將連接恢復到剛剛創建完時的狀態。

查詢緩存

MySQL 拿到一個查詢請求後,會先到查詢緩存看看,之前是不是執行過這條語句。

之前執行過的語句及其結果可能會以 key-value 對的形式,被直接緩存在記憶體中。key 是查詢的語句,value 是查詢的結果。

如果你的查詢能夠直接在這個緩存中找到 key,那麼這個 value 就會被直接返回給客戶端

但是大多數情況下建議不要使用查詢緩存,查詢緩存往往弊大於利。

查詢緩存的失效非常頻繁,只要有對一個表的更新,這個表上所有的查詢緩存都會被清空。

查詢緩存是以查詢語句作為 key,如果查詢的數據發生了變化,那麼查詢語句所對應的結果也會發生變化,即使查詢語句不變。

因此,當數據發生變化時,MySQL會自動使查詢緩存失效,下次查詢時會重新執行查詢語句並緩存新的結果。這也是為什麼有時候查詢緩存機制反而會降低性能的原因,因為每次數據發生變化時都需要重新查詢並緩存結果,而且查詢緩存本身也會占用一定的記憶體空間。

除非你的業務就是有一張靜態表,很長時間才會更新一次。

比如,一個系統配置表,那這張表上的查詢才適合使用查詢緩存。

MySQL 也提供了這種“按需使用”的方式。可以將參數 query_cache_type 設置成 DEMAND,這樣對於預設的 SQL 語句都不使用查詢緩存。

而對於你確定要使用查詢緩存的語句,可以用 SQL_CACHE 顯式指定,像下麵這個語句一樣:

mysql> select SQL_CACHE * from T where ID=10;

需要註意的是,MySQL 8.0 版本直接將查詢緩存的整塊功能刪掉了,也就是說 8.0 開始徹底沒有這個功能了。

分析器

如果沒有命中查詢緩存,就要開始真正執行語句了。首先語句經歷的第一步就是這個分析器。

對SQL語句進行解析,Mysql 才能知道你要做什麼。首先分析器先會做“詞法分析”。你輸入的是由多個字元串和空格組成的一條 SQL 語句,MySQL 需要識別出裡面的字元串分別是什麼,代表什麼。

MySQL 從你輸入的"select"這個關鍵字識別出來,這是一個查詢語句。它也要把字元串“T”識別成“表名 T”,把字元串“ID”識別成“列 ID”。

做完了這些識別以後,就要做“語法分析”。根據詞法分析的結果,語法分析器會根據語法規則,判斷你輸入的這個 SQL 語句是否滿足 MySQL 語法。

如果你的語句不對,就會收到“You have an error in your SQL syntax”的錯誤提醒,比如下麵這個語句 select 少打了開頭的字母“s”。

mysql> elect * from t where ID=1;
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1

一般語法錯誤會提示第一個出現錯誤的位置,所以你要關註的是緊接“use near”的內容。

優化器

經過了分析器(詞法分析和語法分析),MySQL 就知道你要做什麼了。在開始執行之前,還要先經過優化器的處理

優化器是在表裡面有多個索引的時候,決定使用哪個索引;或者在一個語句有多表關聯(join)的時候,決定各個表的連接順序。

比如你執行下麵這樣的語句,這個語句是執行兩個表的 join:

mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;
  • 既可以先從表 t1 裡面取出 c=10 的記錄的 ID 值,再根據 ID 值關聯到表 t2,再判斷 t2 裡面 d 的值是否等於 20。
  • 也可以先從表 t2 裡面取出 d=20 的記錄的 ID 值,再根據 ID 值關聯到 t1,再判斷 t1 裡面 c 的值是否等於 10。

這兩種執行方法的邏輯結果是一樣的,但是執行的效率會有不同,而優化器的作用就是決定選擇使用哪一個方案。

執行器

MySQL 通過分析器知道了你要做什麼,通過優化器知道了該怎麼做,於是就進入了執行器階段,開始執行語句。

執行器的主要作用是將SQL語句轉換為操作存儲引擎的指令,並將結果返回給客戶端。

在執行器中,會根據SQL語句的類型(SELECT、INSERT、UPDATE、DELETE等)和表的引擎類型,調用相應的存儲引擎介面來執行操作。

同時,在執行查詢SQL的時候,會先判斷一下你對這個表 T 有沒有執行查詢的許可權,如果沒有,就會返回沒有許可權的錯誤(在工程實現上,如果命中查詢緩存,會在查詢緩存返回結果的時候,做許可權驗證。查詢也會在優化器之前調用 precheck 驗證許可權)。

mysql> select * from T where ID=10;
 
ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

precheck 驗證許可權 和 執行器驗證許可權的區別:

  • 預驗證是在執行查詢之前進行的,主要是為了避免無效查詢的開銷。在預驗證中,MySQL會檢查當前用戶是否具有執行該查詢的許可權,如果沒有,就可以直接返回沒有許可權的錯誤,避免了執行查詢的開銷。預驗證只是簡單地檢查當前用戶是否具有執行該查詢的許可權,不會涉及到表的引擎類型、表的結構等因素。
  • 執行器中的許可權驗證是在執行查詢時進行的,主要是為了確保操作的合法性。在執行器中,MySQL會根據SQL語句的類型和表的引擎類型,調用相應的存儲引擎介面來執行操作。在執行操作之前,MySQL會進行許可權驗證,檢查當前用戶是否具有執行該操作的許可權,以及表的引擎類型、表的結構等因素是否符合要求。這樣可以確保操作的合法性,避免了惡意操作或者誤操作。
  • 因此,預驗證和執行器中的許可權驗證雖然都是為了驗證當前用戶是否具有執行查詢的許可權,但它們的目的和方式是不同的。預驗證主要是為了避免無效查詢的開銷,而執行器中的許可權驗證主要是為了確保操作的合法性。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 最近接到一個新項目,需要在項目里添加一個後臺任務,定時去發郵件通知客戶;由於是一個比較小型的項目,不希望引入Quartz.Net、Hangfire等太重的框架,同時也沒持久化要;尋覓了一下發現ASP.NET Core本身帶有托管服務,可以執行定時任務。ASP.NET Core提供了IHostedSe ...
  • ### 一、什麼是NVM NVM是英文“Non-Volatile Memory”的縮寫,中文翻譯為“非易失性存儲器”。它是指一種能夠在斷電情況下依舊保留數據的存儲器件。NVM用於存儲一些不需要頻繁更改的數據,例如汽車電子控制單元(ECU)中的程式代碼、校準數據、配置參數以及歷史故障碼等。 ### 二 ...
  • 目錄 一、pxe概念 二、pxe相關服務 三、pxe裝機流程 四、pxe四大文件 五、無人值守 六、實驗 自動裝機 一、pxe概念 概念:PXE(預啟動執行環境)是由Intel公司開發的網路引導技術,工作在Client/Server模式,允許客戶機通過網路從遠程伺服器下載引導鏡像,並載入安裝文件或者 ...
  • 官網下載 Java 您可以從官方網站下載 Java 的最新穩定版本。 官網地址:https://www.oracle.com/technetwork/java/javase/overview/index.html 安裝 Java 有些電腦可能已經安裝了Java。 要檢查Windows PC上是否安裝 ...
  • 小白一枚,今天來給大家分享一下如何將自己編寫的桌面應用程式進行打包,便於在其他的電腦上進行安裝使用。如有錯誤之處請大家指正,謝謝!好了,廢話不多說開乾。 這裡簡要的介紹一下使用QtCreator編寫的應用程式的打包。 1. 將Debug模式切換稱為ewlease模式進行編譯,編譯後沒有任何錯誤即可( ...
  • 開發板:NanoPC-T4開發板eMMC:16GBLPDDR3:4GB顯示屏:15.6 HDMI介面顯示屏u-boot :2023.04 在前面我們已經介紹了編譯Rockchip官方提供的uboot源碼,並下載到開發板中進行測試運行。這一節我們嘗試下載最新的uboot版本試試,當前最新版本為2023 ...
  • > 本文首發於公眾號:Hunter後端 > 原文鏈接:[es筆記六之聚合操作之指標聚合](https://mp.weixin.qq.com/s/UyiZ2bzFxi7zCGmL1Xf3CQ) 聚合操作,在 es 中的聚合可以分為大概四種聚合: * bucketing(桶聚合) * mertic(指標 ...
  • 原文地址:[https://blog.fanscore.cn/a/53/](https://blog.fanscore.cn/a/53/) # 1. 前言 本文是[與世界分享我剛編的轉發ntunnel_mysql.php的工具](https://blog.fanscore.cn/a/47/)的後續, ...
一周排行
    -Advertisement-
    Play Games
  • Timer是什麼 Timer 是一種用於創建定期粒度行為的機制。 與標準的 .NET System.Threading.Timer 類相似,Orleans 的 Timer 允許在一段時間後執行特定的操作,或者在特定的時間間隔內重覆執行操作。 它在分散式系統中具有重要作用,特別是在處理需要周期性執行的 ...
  • 前言 相信很多做WPF開發的小伙伴都遇到過表格類的需求,雖然現有的Grid控制項也能實現,但是使用起來的體驗感並不好,比如要實現一個Excel中的表格效果,估計你能想到的第一個方法就是套Border控制項,用這種方法你需要控制每個Border的邊框,並且在一堆Bordr中找到Grid.Row,Grid. ...
  • .NET C#程式啟動閃退,目錄導致的問題 這是第2次踩這個坑了,很小的編程細節,容易忽略,所以寫個博客,分享給大家。 1.第一次坑:是windows 系統把程式運行成服務,找不到配置文件,原因是以服務運行它的工作目錄是在C:\Windows\System32 2.本次坑:WPF桌面程式通過註冊表設 ...
  • 在分散式系統中,數據的持久化是至關重要的一環。 Orleans 7 引入了強大的持久化功能,使得在分散式環境下管理數據變得更加輕鬆和可靠。 本文將介紹什麼是 Orleans 7 的持久化,如何設置它以及相應的代碼示例。 什麼是 Orleans 7 的持久化? Orleans 7 的持久化是指將 Or ...
  • 前言 .NET Feature Management 是一個用於管理應用程式功能的庫,它可以幫助開發人員在應用程式中輕鬆地添加、移除和管理功能。使用 Feature Management,開發人員可以根據不同用戶、環境或其他條件來動態地控制應用程式中的功能。這使得開發人員可以更靈活地管理應用程式的功 ...
  • 在 WPF 應用程式中,拖放操作是實現用戶交互的重要組成部分。通過拖放操作,用戶可以輕鬆地將數據從一個位置移動到另一個位置,或者將控制項從一個容器移動到另一個容器。然而,WPF 中預設的拖放操作可能並不是那麼好用。為瞭解決這個問題,我們可以自定義一個 Panel 來實現更簡單的拖拽操作。 自定義 Pa ...
  • 在實際使用中,由於涉及到不同編程語言之間互相調用,導致C++ 中的OpenCV與C#中的OpenCvSharp 圖像數據在不同編程語言之間難以有效傳遞。在本文中我們將結合OpenCvSharp源碼實現原理,探究兩種數據之間的通信方式。 ...
  • 一、前言 這是一篇搭建許可權管理系統的系列文章。 隨著網路的發展,信息安全對應任何企業來說都越發的重要,而本系列文章將和大家一起一步一步搭建一個全新的許可權管理系統。 說明:由於搭建一個全新的項目過於繁瑣,所有作者將挑選核心代碼和核心思路進行分享。 二、技術選擇 三、開始設計 1、自主搭建vue前端和. ...
  • Csharper中的表達式樹 這節課來瞭解一下表示式樹是什麼? 在C#中,表達式樹是一種數據結構,它可以表示一些代碼塊,如Lambda表達式或查詢表達式。表達式樹使你能夠查看和操作數據,就像你可以查看和操作代碼一樣。它們通常用於創建動態查詢和解析表達式。 一、認識表達式樹 為什麼要這樣說?它和委托有 ...
  • 在使用Django等框架來操作MySQL時,實際上底層還是通過Python來操作的,首先需要安裝一個驅動程式,在Python3中,驅動程式有多種選擇,比如有pymysql以及mysqlclient等。使用pip命令安裝mysqlclient失敗應如何解決? 安裝的python版本說明 機器同時安裝了 ...