MySQL深入學習--day3

来源:https://www.cnblogs.com/duizhangz/archive/2022/05/21/16295520.html
-Advertisement-
Play Games

B+樹索引的正確使用 索引並不是越多越好,索引創建越多,MySQL維護的代價越高,如果SQL未能完全使用到索引,創建索引的意義是不大的。 適用條件 表x,創建索引a,b,c。主鍵y。 全值匹配 select * from x where a = '' and b = '' and c = '' 當我 ...


B+樹索引的正確使用

索引並不是越多越好,索引創建越多,MySQL維護的代價越高,如果SQL未能完全使用到索引,創建索引的意義是不大的。

適用條件

表x,創建索引a,b,c。主鍵y。

全值匹配

select * from x where a = '' and b = '' and c = ''

當我們創建的索引abc,此時我們有a,b,c欄位的索引是可以匹配到的,不論你a,b,c欄位順序如何,優化器會自動優化為索引的順序。

匹配左邊的列

select * from x where a = '' and d = ''

我們在此情況可以用到a的索引,但是如果第一個為b或c欄位就不行。

匹配列首碼

select * from x where a like 'aaa%' and b like 'bbb%' # 不行'%aaa'或'%aaa%'

我們可以利用建立的索引找到a和b欄位,因為a索引和b索引按照首碼排序的。但是反過來不行

匹配範圍值

select * from x where a between 5 and 10;

因為是索引按照大小排序的,所以可以使用到索引。但是我們不用a直接用b是不能用到索引的。

精準匹配到某一列並範圍匹配到另外一列

select * from x where a = 'aaa' and b between 5 and 10;

當我們是這種情況會找到a,然後根據b的排序找到b的範圍值,是可以用到索引的。

用於排序

select * from x order by a,b,c; # 可以使用索引
select * from x order by b,a,c; # 不能使用索引

創建了a,b,c的索引可以根據a,b,c 排序,否則不能使用。

用於分組

select * from x group by a,b # 可以使用索引,順序不對可以,會自動優化,但是得從左邊開始

回表代價

二級索引最後的最後找到主鍵值需要拿著主鍵值去聚簇索引進行回表查詢。

我們創建索引時可以儘量避免回表的出現,儘量使用索引的欄位,否則回表會導致MySQL的性能下降。當然mysql對於大量數據需要回表的情況會直接優化成順序查找,省的大量回錶帶來的開銷。

這也是為什麼我們不要用select * 的原因,如果我們只需要索引欄位就select對應欄位即可。當所需欄位在索引中存在,會進行覆蓋索引作為結果返回,不需要回表查值。

select * from x where a = '' and b = '' and c = ''; # 如果資料庫中有其他欄位除了abc和主鍵y。
select a,b,c,y from x where a = '' and b = '' and c = ''; # 不需要回表直接覆蓋索引。

索引創建註意事項

  1. 不需要對查詢欄位創建索引,只需要對搜索、排序、分組的欄位進行即可。
  2. 列的基數儘量大,基礎小,即列的重覆值較少的列創建索引
  3. 索引列的類型能小儘量小,int能用tinyint就用。
  4. 索引字元串的首碼,如果只需要首碼創建索引,但是如果首碼重覆多可能會出現問題。
  5. 讓索引列在比較表達式中占獨立的一部分。where a * 2 > 6 是用不了索引的,where a > 6 /2 可以用索引。
  6. 主鍵插入順序,如果主鍵插入不按順序,是需要頁分裂等操作的,所以建議主鍵自增。
  7. 重覆索引。索引重覆只會更多的MySQL性能開銷,且毫無意義。

MySQL的數據目錄

數據存放目錄,與安裝目錄區分開

mysql> show variables like 'datadir';
+---------------+------------------------------------+
| Variable_name | Value                              |
+---------------+------------------------------------+
| datadir       | D:\mysql\mysql-8.0.22-winx64\data\ |
+---------------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

資料庫在文件中就是表現為存放目錄下的一個與資料庫同名的文件夾,系統資料庫會直接存放在數據存放目錄下。

表在文件系統中的表示

InnoDB存儲表數據

描述表結構的文件:表名.frm

描述表數據和索引的文件:表名.ibd

系統表空間:即數據存放目錄下的一個12M的文件,如果系統中資料庫數據多,會更大。即系統資料庫文件ibdata1文件。

獨立表空間:在數據存放目錄下資料庫名的子目錄裡面,表名.frm 和 表名.ibd 。不過現在8.0.22已經只有表名.ibd了。

MyISAM存儲表數據

描述表結構的文件:表名.frm

描述表數據的文件:表名.MYD

描述表索引的文件:表名.MYI

獨立表空間就是由這三個文件組成。

其他文件

伺服器進程文件、日誌文件、SSL和RSA證書和密鑰。

MySQL系統資料庫

  • mysql

存放用戶賬號和許可權,一些存儲過程、事件定義信息、一些運行時日誌,幫助信息,時區信息。

  • information_schema

維護伺服器有哪些表,哪些視圖,哪些觸發器,哪些列,哪些索引

  • performance_schema

維護伺服器運行的狀態信息,對MySQL的監控

  • sys

通過視圖的形式把前兩個表結合起來,讓程式員監控MySQL。

InnoDB 表空間

我們提到了行格式、頁這兩個概念。

行格式規定了每條數據,多條數據形成組,多個組存放在一個頁中。

如果我們需要管理頁的話,我們就需要區和段這個概念。

一個16KB的頁來說,連續64個頁就是一個區,也就是說一個區的大小為1MB。

連續256個區,就形成了一個組,一個組256MB。

區概念

對於每個表空間的第一個組來說,這個組第一個區前三個頁面是不一樣的。

  1. FSP_HDR類型的頁面。用來登記該組256個區的屬性,但是還會存儲表的基本屬性。
  2. IBUF_BITMAP類型的頁。存儲INSERT_BUFFER
  3. INODE類型的頁。存儲INODE entry。

其餘組的第一個區就是最先兩個頁面不一樣。

  1. XDES類型的頁面。用來登記該組256個區的屬性。
  2. IBUF_BITMAP類型的頁面

提問:為什麼要使用區來管理?

因為對於頁來說沒有固定的存儲地點,所以頁是隨意存儲的,但是如果數據量已經很大的情況下,我們插入了一個很小的主鍵值,會建立一個物理存儲位置在很後面的頁,但是頁會被插入到很前面,我們讀頁信息的時候,就會出現什麼情況呢?

就是我們需要IO讀取到最後,然後在回到當前繼續讀,是十分耗時的,也就是隨機IO,與順序IO性能差得多。

段概念

第一遍看到這個概念直接被搞蒙了。

InnoDB 中葉子節點存放的區和非葉子節點存放的區是分開的,這就是段的概念。一個為存放葉子節點區的段,和存放索引頁區的段。

所以捋一下。每個聚簇索引會有兩個段,一個段表示存放葉子頁的區,一個段表示存放非葉子頁的區。

那按照這樣的話,一個表開局就要2M的存儲空間,對於幾條數據的是不是太大了。

所以出現碎片頁的概念,一個區不屬於某個段,而是直接屬於表空間。它可以存儲各個段的頁,防止區的浪費。當一個段已經存儲了32個碎片區,剩下就會直接創建附屬的空閑區來存儲頁,而不是使用碎片頁。

所以區有如下狀態:

  • 空閑區(FREE)、
  • 有剩餘空間的碎片區(FREE_FRAG)、
  • 滿的碎片區(FULL_FRAG)、
  • 附屬於某個段的空閑區(FSEG)。

對於每個區來說都有一個XDES Entry的結構。

  • Segment ID (8位元組):如果狀態為FSEG的話就是段的ID。否則沒有意義
  • List Node(12位元組):用來存儲前一個和後一個區的地址
  • State(4位元組):就是上述四種狀態。
  • Page State Bitmap(16位元組): 描述當前64個頁,每個頁2比特,一比特表示是否空閑,還有一個比特沒什麼用。

尋找最近的有空間的或空閑區

當段中存儲的區小於32時,是會利用隸屬於表空間的碎片區進行存儲的。

流程:

  • 新插入的頁尋找空閑區進行存儲,如何快速尋找到表空間的空閑碎片區呢?
    • 表空間會維護一個FREE狀態的鏈表和FREE_FREG狀態的鏈表以及FULL_FRAG狀態的鏈表。
    • 如果空閑的碎片區還存在就會找出鏈表中取出一個插入,如果滿了就改變其狀態將其放入FULL_FRAG的鏈表中。
    • 如果沒有空閑的碎片區,就會從FREE中取出一個來將其轉變為空閑碎片區狀態放入FREE_FREG狀態的鏈表中。

當段中的碎片區存儲超過32時,就會申請隸屬於該段空間的區進行存儲。

流程和之前差不多,但是段空間也會維護三個鏈表FREE和FULL以及NOT_FULL雖然有點區別就是非碎片區的,不過是申請的專屬的區,所以流程是差不多的。

段的結構

前面我們不是提到了段並不是一個實際的存儲單元,只是區的引用。

所以需要有一個結構來定義段,就是INODE Entry 結構

image

  • Segment ID :就是段的唯一ID
  • NOT_FULL_N_USED:表示已經使用的頁的個數,然後下次直接分配直接找到。
  • 三個鏈表:很熟悉,就是表示空閑,沒滿,和滿了的隸屬於該段的區的鏈表。只會在碎片區分配滿32個的時候才會進行分配。
  • Magic Number魔數
  • 碎片區的引用剛好32個。

所以在段中,碎片區的引用是在最底下,而專屬區的引用是在鏈表中鏈著的。

所以你廢了嗎?

接下來我們可以講解一下INODE Entry放在哪裡呢,就需要介紹之前提到過的每個表空間的第一個區中固定的三個頁面,只介紹倆頁面

FSP_HDR頁面和XDES頁面

FSP_HDR類型的頁面,就是比其他的區的第一個XDES頁多了File Space Header就是記錄當前表空間的一些屬性,其他都是一樣的。

image

  • File Header就是頭中的一些信息還有和File Trailer的校驗
  • File Space Header

image

Space ID 表示表空間的ID

Size 表空間頁的大小

Free Limit 就是當前已經使用的頁到多少了,下次直接從這個地址開始分配頁面

FRAG_N_USED 表示碎片區已經使用的頁

接下來的for FREE List 和for FREE_FRAG List和for FULL_FRAG List 表示表空間維護的三個有關碎片區的鏈表

Next Unuser Segment ID 表示下一個未分配的段ID,方便分配一個新的段ID

for SEG_INODES_FULL 和 for SEG_INODES_FREE 表示已經放滿了INODE Entry 的INODE節點和空閑的INODE節點。(記住是存放INODE Entry也就是段結構的INODE節點)

INODE頁面

image

INODE類型結構就是為了存儲INODE Entry節點的,最多存儲85個段。

結構中List Node for INODE Page List 就是指向上一個INODE節點和下一個INODE節點。

我們就是在這個INODE中存儲段的INODE Entry節點的。

如果該頁存儲滿了,就會在上面提到的List Base Node for SEG_INODES_FREE 就是空閑INODE頁的基節點的鏈表引用,取出一個,空的話從碎片區中申請一個頁來存放。

所以我們知道了段是怎麼存儲的,存儲在哪裡。

同時呢,我們已經知道一個索引會有兩個段,一個葉子段,一個非葉子段。

我們怎麼找到索引的頁呢?

Segment Header結構

在這個結構之前,我們在數據頁是提到過兩個引用,但是沒有具體介紹

在頁結構的Page Header中有如下兩個結構

image

這兩個結構就是Segment Header這個結構

image

Space ID of the INODE Entry 就是INODE對應的表空間

Page Number of the INODE Entry 就是INODE對應的表空間下對應的頁號

Byte Offset of the INODE Entry 就是INODE對應的頁中對應段的偏移量。

我們就可以通過在索引的ROOT節點存儲一個這樣的結構,可以找到對應的段。包括葉子段和非葉子段,就是兩個這個結構,然後去表空間中找到這兩個段的地址即可。

系統表空間

介紹一個概念,數據字典即系統表空間中存放了一些固定的數據,以及資料庫中的表,表名,列,列屬於那個表等等基本信息。

還有一些已經用了的最大的表ID,最大的索引ID,最大的表空間ID,就是方便下次創建表啊索引啊這一些更方便一點,直接將值進行增加等操作進行賦值即可。

image

圖片出自:《MySQL是怎樣運行的:從根兒上理解MySQL》
對其進行總結概括,以及思路重新捋一遍。


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

-Advertisement-
Play Games
更多相關文章
  • 本地化資源文件創建 前期準備 Visual Studio 搜索並安裝擴展插件 ResXManager 在項目內 Properties 文件夾內添加新建項 資源文件 Resource.resx 手動重新編譯項目,然後 Resource.resx 右鍵菜單 → 在 ResX Manager 中打開 打開 ...
  • #Redis簡介 Redis(Remote Dictionary Server)是完全開源的、遵守BSD協議的、高性能的Key-Value資料庫。 Redis與其他Key-Value緩存產品有一下三個特點: Redis支持數據的持久化,可以將記憶體中的數據保存在磁碟中,重啟的時候可以再次載入進行使用。 ...
  • 不久前,為了滿足工作中日常的各種實驗測試需求,終於按需求組裝一臺塔式的server T440。但是沒有多久就出現些問題,以下大概是問題和現象簡單描述: (1). 最開始時,沒幾天就出現自動重啟的問題,當時也正好做了一個比較消耗資源的大集群的實驗,當時初步判斷可能是記憶體資源不夠(實際按監控來看,並沒使 ...
  • 如何實現硬體和軟體的統一? 或者說如何把物理世界與電子學和電腦科學聯繫起來的? 寫在前面: 我一直覺得萬事萬物之間都是有聯繫的,我們現在所說的科學,有個這樣的解釋:科學科學,分科而學。以前的那些偉大的科學家們為了更好的理解這個世界,故而把物理世界進行了多層次、多維度的劃分。 今天在一個講座上,有個 ...
  • head head 命令可用於查看文件的開頭部分的內容,有一個常用的參數 -n 用於顯示行數,預設為 10,即顯示 10 行的內容。 命令格式: head [參數] [文件] 參數: -q或--quiet或--silent 不顯示包含給定文件名的文件頭即隱藏文件名。 -v或--verbose 總是顯 ...
  • 出現mysqld: Can't create directory 'D:\Environment\mysql-5.7.37 \data' (Errcode: 2 - No such file or directory)極大原因是my.ini的配置有問題 my.ini配置 [mysqld] based ...
  • 分享嘉賓:牟娜 阿裡巴巴 高級演算法工程師 編輯整理:孫鍇 內容來源:DataFun AI Talk《定向廣告新一代點擊率預估主模型——深度興趣演化網路》 出品社區:DataFun 導讀: 本次帶給大家分享是阿裡媽媽在2018年做的模型上的創新——深度興趣演化網路(Deep Interest Evol ...
  • 一、概述 Oozie是一個基於工作流引擎的開源框架,依賴於MapReduce來實現,是一個管理 Apache Hadoop 作業的工作流調度系統。是由Cloudera公司貢獻給Apache的,它能夠提供對Hadoop MapReduce和Pig Jobs的任務調度與協調。Oozie需要部署到Java ...
一周排行
    -Advertisement-
    Play Games
  • 什麼是工廠模式 工廠模式是最常用的設計模式之一,屬於創建型模式。 有點: 解耦,可以把對象的創建和過程分開 減少代碼量,易於維護 什麼時候用? 當一個抽象類有多個實現的時候,需要多次實例化的時候,就要考慮使用工廠模式。 比如:登錄的抽象類ILoginBusiness,它有2個實現,一個用用戶名密碼登 ...
  • 這次iNeuOS升級主要升級圖形渲染引擎和增加豐富的圖元信息,可以很快的方案應用。總共增加41個通用和行業領域的圖元應用,增加2154個圖元信息,現在iNeuOS視圖建模功能模塊總共包括5894個行業圖元信息。現在完全支持製作高保真的工藝流程和大屏展示效果。 ...
  • 效果圖先附上: 首先 這是我是參考 教程:使用 SignalR 2 和 MVC 5 實時聊天 | Microsoft Docs 先附上教程: 在“添加新項 - SignalRChat”中,選擇 InstalledVisual> C#>WebSignalR>,然後選擇 SignalR Hub 類 (v ...
  • 一、前言 項目中之前涉及到胎兒心率圖曲線的繪製,最近項目中還需要添加心電曲線和血樣曲線的繪製功能。今天就來分享一下心電曲線的繪製方式; 二、正文 1、胎兒心率曲線的繪製是通過DrawingVisual來實現的,這裡的心電曲線我也是採用差不多相同的方式來實現的,只是兩者曲線的數據有所區別。心電圖的數據 ...
  • 安裝 Redis # 首先安裝依賴gcc, 後面需要使用make編譯redis yum install gcc -y # 進入 /usr/local/src 目錄, 把源碼下載到這裡 cd /usr/local/src # 下載 redis 7.0.2 的源碼,github被牆,可以使用國內的地址 ...
  • Redis 的定義? 百度百科: Redis(Remote Dictionary Server ),即遠程字典服務,是一個開源的使用ANSI C語言編寫、支持網路、可基於記憶體亦可持久化的日誌型、Key-Value資料庫,並提供多種語言的API。 中文官網: Redis是一個開源(BSD許可),記憶體存 ...
  • 事情的起因是收到了一位網友的請求,他的java課設需要設計實現迷宮相關的程式——如標題概括。 我這邊不方便透露相關信息,就只把任務要求寫出來。 演示視頻指路👉: 基於JavaFX圖形界面的迷宮程式演示_嗶哩嗶哩_bilibili 完整代碼鏈接🔎: 網盤:https://pan.baidu.com ...
  • Python中的字典 Python中的字典是另一種可變容器模型,且可存儲任意類型對象。鍵值使用冒號分割,你可以看成是一串json。 常用方法 獲取字典中的值 dict[key] 如果key不存在會報錯,建議使用dict.get(key),不存在返回None 修改和新建字典值 dict[key]=va ...
  • 迎面走來了你的面試官,身穿格子衫,挺著啤酒肚,髮際線嚴重後移的中年男子。 手拿泡著枸杞的保溫杯,胳膊夾著MacBook,MacBook上還貼著公司標語:“加班使我快樂”。 面試官: 看你簡歷上用過MySQL,問你幾個簡單的問題吧。什麼是聚簇索引和非聚簇索引? 這個問題難不住我啊。來之前我看一下一燈M ...
  • tunm二進位協議在python上的實現 tunm是一種對標JSON的二進位協議, 支持JSON的所有類型的動態組合 支持的數據類型 基本支持的類型 "u8", "i8", "u16", "i16", "u32", "i32", "u64", "i64", "varint", "float", "s ...