【MySQL 數據庫】MySQL索引特性（一）磁盤存儲定位扇區InnoDB頁

請添加圖片描述

文章目錄

沒有索引，可能會有什么問題
二、認識磁盤
- 2.1 MySQL與存儲
- 2.2 磁盤：
- 2.3 扇區
- 2.4 定位扇區
- 2.5 結論
三、三者作用流程（磁盤，塊，InnoDB頁）
四、MySQL與磁盤交互基本單位
五、建立共識
🚩總結

沒有索引，可能會有什么問題

索引：提高數據庫的性能，索引是物美價廉的東西了。不用加內存，不用改程序，不用調sql，只要執行正確的create index，查詢速度就可能提高成百上千倍。但是天下沒有免費的午餐，查詢速度的提高是以插入、更新、刪除的速度為代價的，這些寫操作，增加了大量的IO。所以它的價值，在于提高一個海量數據的檢索速度。

常見索引分為：

主鍵索引(primary key)
唯一索引(unique)
普通索引(index)
全文索引(fulltext) --解決中子文索引問題。
案例：
先整一個海量表，在查詢的時候，看看沒有索引時有什么問題？

--構建一個8000000條記錄的數據
--構建的海量表數據需要有差異性，所以使用存儲過程來創建， 拷貝下面代碼就可以了，暫時不用理解mysql> -- 在SqlIndex數據庫中創建EMP表
mysql> CREATE TABLE EMP (->     empno INT(10) ,  -- 員工編號（與插入的start+i對應）->     ename VARCHAR(20) NOT NULL, -- 員工姓名（與rand_string(6)對應）->     job VARCHAR(20) NOT NULL,   -- 職位（與'SALESMAN'對應）->     mgr INT(10),                -- 經理編號（與0001對應）->     hiredate DATE NOT NULL,     -- 入職日期（與curdate()對應）->     sal DECIMAL(10,2) NOT NULL, -- 工資（與2000對應）->     comm DECIMAL(10,2),         -- 傭金（與400對應，允許為NULL）->     deptno INT(5)               -- 部門編號（與rand_num()對應）-> );
Query OK, 0 rows affected, 3 warnings (0.07 sec)

– 產生隨機字符串

delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin 
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do 
set return_str = concat(return_str, substring(chars_str, floor(1 + rand()*52), 1));
set i = i + 1;
end while;
return return_str;
end $$
delimiter ;

–產生隨機數字

delimiter $$create function rand_num()returns int(5)begin 
declare i int default 0;set i = floor(10 + rand() * 500);return i;end $$delimiter ;

–創建存儲過程，向雇員表添加海量數據

delimiter $$create procedure insert_emp(in start int(10),in max_num int(10))
begindeclare i int default 0; 
set autocommit = 0;  -- 關閉自動提交，提升插入效率
repeatset i = i + 1;-- 插入數據到EMP表，字段值通過變量和函數生成insert into EMP values ((start+i),  -- 員工編號：基于start參數遞增rand_string(6),  -- 員工姓名：調用之前創建的隨機字符串函數生成6位名稱'SALESMAN',  -- 職位固定為SALESMAN0001,  -- 經理編號固定為0001curdate(),  -- 入職日期為當前日期2000,  -- 工資固定為2000400,  -- 傭金固定為400rand_num()  -- 部門編號：調用之前創建的隨機數函數生成);until i = max_num  -- 循環條件：當i等于max_num時停止end repeat;commit;  -- 批量插入完成后手動提交事務end $$delimiter ;

– 執行存儲過程，添加8000000條記錄

call insert_emp(100001, 8000000);

到此，已經創建出了海量數據的表了。

查詢員工編號為998877的員工

select * from EMP where empno=998877;

可以看到耗時4.93秒，這還是在本機一個人來操作，在實際項目中，如果放在公網中，假如同時有
1000個人并發查詢，那很可能就死機。

解決方法，創建索引

alter table EMP add index(empno);

換一個員工編號，測試看看查詢時間

select * from EMP where empno=123456;

在這里插入圖片描述
索引可以加快查找速率

二、認識磁盤

2.1 MySQL與存儲

MySQL 給用戶提供存儲服務，而存儲的都是數據，數據在磁盤這個外設當中。磁盤是計算機中的一個機
械設備，相比于計算機其他電子元件，磁盤效率是比較低的，在加上IO本身的特征，可以知道，如何提
交效率，是MySQL 的一個重要話題。

先來研究一下

2.2 磁盤：

在這里插入圖片描述

2.3 扇區

數據庫文件，本質其實就是保存在磁盤的盤片當中。也就是上面的一個個小格子中，就是我們經常所說的扇區。當然，數據庫文件很大，也很多，一定需要占據多個扇區。

題外話：

從上圖可以看出來，在半徑方向上，距離圓心越近，扇區越小，距離圓心越遠，扇區越大
那么，所有扇區都是默認512字節嗎？目前是的，我們也這樣認為。因為保證一個扇區多大，是由比特位密度決定的。
不過最新的磁盤技術，已經慢慢的讓扇區大小不同了，不過我們現在暫時不考慮。

我們在使用Linux，所看到的大部分目錄或者文件，其實就是保存在硬盤當中的。(當然，有一些內存文
件系統，如：proc ，sys 之類，我們不考慮)

#數據庫文件，本質其實就是保存在磁盤的盤片當中，就是一個一個的文件
#我們目前MySQL中的文件

所以，最基本的，找到一個文件的全部，本質，就是在磁盤找到所有保存文件的扇區。

而我們能夠定位任何一個扇區，那么便能找到所有扇區，因為查找方式是一樣的。

2.4 定位扇區

在這里插入圖片描述

柱面(磁道): 多盤磁盤，每盤都是雙面，大小完全相等。那么同半徑的磁道，整體上便構成了一個柱面CHS 。
每個盤面都有一個磁頭，那么磁頭和盤面的對應關系便是1對1的

所以，我們只需要知道，磁頭（Heads）、柱面(Cylinder)(等價于磁道)、扇區(Sector)對應的編號。即可在磁盤上定位所要訪問的扇區。這種磁盤數據定位方式叫做CHS（但是硬件是），不過實際系統軟件使用的并不是CHS（但是硬件是），而是
LBA ，一種線性地址，可以想象成虛擬地址與物理地址。系統將LBA地址最后會轉化成為CHS ，交給磁盤去進行數據讀取。不過，我們現在不關心轉化細節，知道這個東西，讓我們邏輯自洽起來即可。

2.5 結論

我們現在已經能夠在硬件層面定位，任何一個基本數據塊了(扇區)。那么在系統軟件上，就直接按照扇區(512字節，部分4096字節),進行IO交互嗎？不是

如果操作系統直接使用硬件提供的數據大小進行交互，那么系統的IO代碼，就和硬件強相關，換言之，如果硬件發生變化，系統必須跟著變化
從目前來看，單次IO 512字節，還是太小了。IO單位小，意味著讀取同樣的數據內容，需要進行多次磁盤訪問，會帶來效率的降低。
之前學習文件系統，就是在磁盤的基本結構下建立的，文件系統讀取基本單位，就不是扇區，而是數據塊。

故，系統讀取磁盤，是以塊為單位的，基本單位是4KB 。

磁盤隨機訪問(Random Access)與連續訪問(Sequential Access)
隨機訪問：本次IO所給出的扇區地址和上次IO給出扇區地址不連續，這樣的話磁頭在兩次IO操作之間需
要作比較大的移動動作才能重新開始讀/寫數據。
連續訪問：如果當次IO給出的扇區地址與上次IO結束的扇區地址是連續的，那磁頭就能很快的開始這次
IO操作，這樣的多個IO操作稱為連續訪問。
因此盡管相鄰的兩次IO操作在同一時刻發出，但如果它們的請求的扇區地址相差很大的話也只能稱為隨
機訪問，而非連續訪問。
磁盤是通過機械運動進行尋址的，隨機訪問不需要過多的定位，故效率比較高。

三、三者作用流程（磁盤，塊，InnoDB頁）

磁盤的512字節（扇區）、操作系統的塊（如4KB）、MySQL的InnoDB頁（16KB），這三者是不同層級的“數據交互單位”，通過“向上整合”的方式協同工作，最終實現高效的數據讀寫。

具體作用流程如下：

最底層：磁盤的“扇區”（512字節）
磁盤硬件的物理結構中，最小讀寫單位是扇區（Sector），傳統機械硬盤和固態硬盤（SSD）的扇區大小通常為512字節（少數新硬盤可能為4096字節，但仍兼容512字節模擬）。

磁盤無法直接讀寫“小于1個扇區”的數據，哪怕只需要1個字節，也必須讀取整個扇區（512字節）。
但扇區太小，如果每次IO都只操作1個扇區，會導致磁盤讀寫效率極低（頻繁尋址、機械運動耗時）。

中間層：操作系統的“塊”（如4KB）
操作系統為了優化磁盤交互，會將多個扇區“打包”成更大的塊（Block），作為操作系統與磁盤交互的基本單位。

塊的大小由操作系統決定（如Linux默認4KB，即8個512字節扇區）。
當操作系統需要讀寫數據時，會以“塊”為單位向磁盤發起請求：例如讀取4KB數據，實際是一次性讀取8個連續的512字節扇區。
這樣可以減少與磁盤的交互次數（1次4KB讀寫 = 8次512字節讀寫的效率），降低磁盤尋址開銷。

應用層：MySQL InnoDB的“頁”（16KB）
MySQL的InnoDB引擎在操作系統之上，進一步將多個操作系統塊“整合”為頁（Page，16KB），作為數據庫層面的基本交互單位。

16KB的InnoDB頁，對應操作系統的4個4KB塊（16KB = 4×4KB），或32個512字節扇區（16KB = 32×512字節）。
當InnoDB需要讀寫數據（如用戶查詢一條記錄）時，會以“頁”為單位請求數據：哪怕只需要頁中的一條記錄（可能只有幾十字節），也會一次性讀取整個16KB的頁到內存中。
這樣做的目的是：利用“局部性原理”（相鄰數據大概率被連續訪問），減少與操作系統的交互次數（1次16KB讀寫 = 4次4KB讀寫的效率），進一步降低IO開銷。

三者協作流程示例
當MySQL查詢一條記錄時：

InnoDB確定該記錄所在的16KB頁，向操作系統請求讀取這個頁。
操作系統將16KB的請求分解為4個4KB的塊（假設OS塊為4KB），向磁盤發起4次塊讀取請求。
磁盤將每個4KB塊分解為8個512字節扇區，依次讀取這些扇區的數據，返回給操作系統。
操作系統將4個4KB塊整合為16KB數據，返回給InnoDB。
InnoDB從16KB頁中提取所需的記錄，返回給用戶。

四、MySQL與磁盤交互基本單位

而MySQL作為一款應用軟件，可以想象成一種特殊的文件系統。它有著更高的IO場景，所以，為了提高
基本的IO效率，MySQL 進行IO的基本單位是16KB (后面統一使用InnoDB 存儲引擎講解)

mysql> show global status like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |    - 16*1024=16384
+------------------+-------+
1 row in set (0.05 sec)

也就是說，磁盤這個硬件設備的基本單位是512字節，而MysQL InnoDB引擎使用16KB進行IO交互。即，MysQL和磁盤進行數據交互的基本單位是16KB。這個基本數據單元，在MysQL這里叫做page(注意和系統的page區分)

五、建立共識

MySQL 中的數據文件，是以page為單位保存在磁盤當中的。
MySQL 的CURD 操作，都需要通過計算，找到對應的插入位置，或者找到對應要修改或者查詢的數據。
而只要涉及計算，就需要CPU參與，而為了便于CPU參與，一定要能夠先將數據移動到內存當中。所以在特定時間內，數據一定是磁盤中有，內存中也有。后續操作完內存數據之后，以特定的刷新策略，刷新到磁盤。而這時，就涉及到磁盤和內存的數據交互，也就是IO了。而此時IO的基本單位就是Page。
為了更好的進行上面的操作， MySQL 服務器在內存中運行的時候，在服務器內部，就申請了被稱 Buffer Pool的的大內存空間，來進行各種緩存。其實就是很大的內存空間，來和磁盤數據進行IO交互。
為何更高的效率，一定要盡可能的減少系統和磁盤IO的次數