最近讀了《資料庫索引設計與優化》一書，
直呼內行，以下是讀書筆記，
聚簇索引
新插入的表行所在的表頁的索引是聚簇索引，
若索引行的順序和表行的索引具有強關聯性可說這個索引是聚集的，但是不一定是聚簇索引，
一個表只允許有一個聚簇索引，在某個特定時間可能會有多個索引是聚集的，
索引頁和表頁
表和索引行都被存盤在頁中，頁的大小一般為4/8 KB. 緩沖池和IO的活動都是基于頁的，
頁的大小決定了一頁可以存盤多少個索引行，表行，
索引頁和表頁都是在頁記憶體儲資料，差異是一個存盤的是data資料，一個存盤的是索引資料，若索引頁存盤的是非聚簇索引，那么連續的索引頁指向的是非連續的data資料頁，此時全索引掃描是順序IO，對指向的資料的掃描是隨機Io.
聚簇索引頁是連續的所指向的data資料頁也是連續的，通過聚簇索引掃描表行是順序IO.
緩沖區
記憶體快取區，通常非常大，可以存盤成千上萬的頁，MySQL緩沖區的目的是將常用的資料快取起來避免頻繁的磁盤IO帶來的性能損耗，每一個DBMS會根據物件型別(表和索引)及頁的情況擁有多個緩沖區，
磁盤IO
可以分為順序IO和隨機IO
順序IO: 指讀寫操作的訪問地址連續，在順序IO訪問中，HDD所需的磁道搜索時間顯著減少，因為讀/寫磁頭可以以最小的移動訪問下一個塊，資料備份和日志記錄等業務是順序IO業務，
隨機IO:指讀寫操作時間連續，但訪問地址不連續，隨機分布在磁盤的地址空間中，
mysql在處理IO的程序中通常都會伴隨著預讀取，區域預讀原理告訴我們，當計算機訪問一個地址的資料時候，與他相鄰的地址的資料也有較大幾率訪問到所以一次io會把相鄰頁的資料也加載到快取區當中去，
隨機IO耗時估算
對一頁或者多個連續頁一次資料讀取我們認為是一次IO. 一次隨機IO的耗時大概是10ms，
每次讀取資料的時間大致可分為
- 排隊等待時間：可能發生的排隊時間
- 尋道時間： 是指磁盤 磁臂震動到指定磁道所需要的時間，一般在5ms內
- 半圈旋轉：找到指定磁道后還需要旋轉到達目標頁資料，
- 資料傳輸耗時: ，將資料從磁盤傳輸到資料庫緩沖區的時間，耗時相對較小 一般在1ms內，
其中尋道時間和旋轉時間稱為服務時間，同資料庫緩沖區一樣，磁盤也有緩沖區，若資料存在于磁盤緩沖區，尋道時間和旋轉時間均可省略，IO時間將會降低在1ms左右，
順序IO耗時估算
以下都是順序IO
- 全表掃描：一般是按順序讀取資料頁
- 全索引掃描： 按順序掃描存盤索引資料的頁，但是索引頁存盤的資料地址指標，指向的頁可能不連續，索引后對應的資料掃描是隨機IO
- 索引片掃描：同2
- 通過聚簇索引掃描表行： 聚簇索引后直接指向資料，且聚簇索引的增長一般是連續的，聚簇索引所指向的地址頁也是連續的，是順序掃描，
一般DBMS會知道哪些索引和表頁需要被順序的讀取，且能識別出不在緩沖區的頁，然后發出多頁的一次IO請求，對于平均4K的表頁來說在40MB/s的讀取速度下，順序IO的耗時可能為0.1ms.
且通常伴隨著預讀，在需要所需資料前將一部分資料讀取到緩沖區當中去，
增加索引的代價
回應耗時分析
前提：
假設在一個索引上添加一行需要耗時10ms當前情況不考慮異步寫 那么索引的新增需要找到對應的索引頁的插入位置，對于非聚簇索引這個位置通常不是最后一個索引頁末尾，尋找對于的索引頁是一個隨機IO程序，可以認為是估算值10ms
問題：
- 在一個事務中向一張有10條索引的表中插入1行資料，
- 在一個事務中向一張有10條索引的表中插入20行資料，
分析以上情況下隨機IO次數：
- 10條索引包含9非聚簇索引和1聚簇索引，需要10次隨機讀，順序讀的第一次也是隨機讀，需要尋道時間 旋轉才能在后續開啟順序讀，
- 9個非聚簇索引需要9*20=180次隨機讀，聚簇第一次隨機后續都是順序讀，以供需要181次隨機讀，
磁盤負載分析
被修改的葉子頁最終都會落到磁盤上去，由于資料庫的寫是異步的，所以寫不會影響事務時間，但是寫會增加磁盤的負載，如果一張表的插入較高的話，磁盤負載可能會變成限制索引數量的主要問題，
磁盤空間限制
如果一個表中有千萬行以上的資料，索引磁盤空間的成本可能會成為一個限制因素，每一次資料的寫入都需要增加對應索引的空間，
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/296132.html
標籤：其他
上一篇：計算機基礎--01