MySQL實戰45講

2020/3月更新

https://www.bbsmax.com/A/D854r3rvzE/

這篇自連接配接的舉例不錯哦

三大範式：

1. 保證資料原子性。反例：位址資訊粒度太粗

2. 保證是完全依賴主鍵。排除完全無關和部分相關。比如天氣出現在使用者的資訊表，比如訂單和産品的聯合主鍵。訂單金額和訂單時間僅與“訂單号”相關，與“産品号”無關

   這樣就不滿足第二範式的要求

3. 保證是之間完全依賴主鍵列。沒有傳遞依賴。如訂單中，下單者的性别，年齡，确實依賴于訂單号。符合第二範式。但明顯傳遞依賴了，直接依賴的是下單者

五大限制

語句優化：

如select 1代替select *,exists代替 in

優化limit和offset,MySQL的limit工作原理就是先讀取n條記錄,然後抛棄前n條,讀m條想要的,是以n越大,性能會越差,代碼如下:

優化前SQL:SELECT * FROM member ORDER BY last_active LIMIT 50,5

優化後SQL:SELECT * FROM member INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50,5) USING (member_id)

分别在于,優化前的SQL需要更多I/O浪費,因為先讀索引,再讀資料,然後抛棄無需的行,而優化後的SQL(子查詢那條)隻讀索引(Cover index)就可以了,然後通過member_id讀取需要的列.

隻傳回需要的資料

合并一些條件相同的操作

學會用執行計劃(explain+語句)和show processlists;

一條sql執行慢的原因：

偶爾慢：資料庫正在同步資料到磁盤，拿不到表鎖或者行鎖（用show process list檢視目前狀态）

一直都慢：

1. 語句寫得不好，字段沒有索引，隻能走全表掃描
2. 字段有索引，但沒用上 如where c-1=100就不行，c=101就可以
3. 函數操作導緻沒用上索引 如where pow(c,2)=100
4. 首字母模糊搜尋也會失去索引走全表掃描

主鍵索引和非主鍵索引:主鍵索引存放整行字段的資料，非主鍵索引存放主鍵字段的值

一個索引上不同的值越多，也就是說基數越大，那走索引查越有優勢

索引系統是通過部分周遊資料，即通過采樣的方式，預測索引的基數的。是以可能由于統計失誤，是以走了全表掃描

采樣：InnoDB預設會選擇N個資料頁，統計這些頁面上的不同值，得到一個平均值，然後乘以這個索引的頁面數，就得到了這個索引的基數。

優化器不止看掃描行數，還看是否回表

可強制走索引：select *from t force index(a) where 也可show index from t查詢索引的基數和實際是否符合，不符合則analyze table t重新統計分析

explain可以檢視SQL如索引是否滿足需求

看慢查詢日志可以優化 analyze table

還有可以使用字首索引alter table add index index(emial(6))

1.從index2索引樹找到滿足索引值是’zhangs’的記錄，找到的第一個是ID1；

2.到主鍵上查到主鍵值是ID1的行，判斷出email的值不是’[email protected]’，這行記錄丢棄；

3.取index2上剛剛查到的位置的下一條記錄，發現仍然是’zhangs’，取出ID2，再到ID索引上取整行然後判斷，這次值對了，将這行記錄加入結果集；

重複上一步，直到在idxe2上取到的值不是’zhangs’時，循環結束

也就是用字首索引，定義好長度，既可以省空間，又可以和全字元串的索引一個效率。

但選得不好增加掃描行數

select count(distict email)as L from table;

但對身份證這種前序索引不适合的，用倒序存儲，因為後幾位區分度高。

innoDB

1. 不僅有表級鎖，還提供行級鎖，鎖粒度小，寫操作不會鎖定全表，是以适合并發度高的場景
2. 提供了資料庫ACID事務的支援
3. 不支援全文搜尋，不儲存表的行數
4. 使用聚簇索引

myISAM:

1. 不支援行鎖
2. 不支援事務（事務也是在引擎層實作的）
3. 但儲存表的行數，讀操作遠遠多于寫操作時，此引擎為首選。
4. 使用非聚簇索引

表鎖和行鎖：前者鎖粒度大，鎖沖突機率高，并發量低，開銷小，加鎖快。 後者全相反。

第1講

分為SERVER層和引擎層

使用者連接配接

查詢緩存（有更新時，表上的所有緩存會失效，不适用于頻繁更新的表）

文法解析器

優化器決定表裡多個索引用哪個，表的連接配接順序啊這些

權限驗證

第2講

一條查詢語句怎麼執行：

一條更新語句怎麼執行：redo log（重做日志）是引擎層的日志，可以保證資料庫重新開機，之前送出的資料不丢失，且是innoDB獨有的； binlog（歸檔日志）是server層的日志，所有引擎可以使用

redolog是實體日志，記錄事務操作對變化，記錄變化後的值，不管事務是否送出都會記下。會用完，寫滿了要持久化然後擦除

binlog是邏輯日志，記錄資料庫執行都所有操作。可以追加

怎麼恢複資料庫到任意一秒：

找一個全量備份，然後取出那個時間後的binlog，一直放到誤操作的那個時刻

第3講

為何不推薦長事務：1長事務使得復原空間要保留，占大量存儲空間。2占用鎖資源

事務隔離的實作：每條記錄在更新的時候都會同時記錄一條復原操作。同一條記錄在系統中可以存在多個版本，這就是資料庫的多版本并發控制（MVCC）

存在視圖的 2 種隔離級别：

1. 讀送出

2. 可重複讀

   讀送出：在每一條 SQL 開始執行時建立視圖，隔離作用域僅限該條 SQL 語句。

可重複讀：事務啟動時建立視圖，是以，在事務任意時刻，對記錄讀取的值都是一樣的。

第4講 索引（也是在引擎層）

索引常見模型：哈希表，有序數組，和搜尋樹。

主鍵索引也叫聚簇索引，葉子節點存整行資料，非主鍵索引葉子節點存的是主鍵的值，再到主鍵索引的樹去查。這個過程叫回表。

是以我們盡量使用主鍵查詢。

主鍵長度越小，普通索引的葉子節點就越小，普通索引占用的空間也就越小

B+樹能夠很好地配合磁盤的讀寫特性，減少單次查詢的磁盤通路次數，讓查詢過程中少通路資料塊。

N叉樹的N可以調整嗎？ ——5.6以後可以通過page大小來間接控制

第5講

覆寫索引：select的資料列從索引中就能獲得，索引覆寫了我們的查詢需求，不用回表。

索引上的資訊足夠滿足查詢請求，不需要再回到主鍵索引上去取資料

聯合索引：通過身份證（主鍵）查姓名時，index(id,name)

有個最左字首原則——如index(a,b,c)。當查詢語句分别包含a，b或a，b，c都是可以走索引的，而a，c隻有其中的a走索引。

mysql5.6引入了索引下推

篩選索引字段中不符合條件的資料，減少回表次數

為什麼可能重建索引——索引可能因為删除，或者頁分裂等原因，導緻資料頁有空洞，重建索引的過程會建立一個新的索引，把資料按順序插入，這樣頁面的使用率最高，也就是索引更緊湊、更省空間。

第6講

全局鎖的典型場景是作全庫邏輯備份：flush tables with read lock

表級鎖：表鎖（限定别人的讀寫，也限定了本線程接下來的操作）和中繼資料鎖(通路表時自動被加上)

讀鎖間不互斥

MDL在事務送出後才釋放

第7講

行鎖（引擎層）

鎖是需要時加上，事務結束時才釋放

如果事務要鎖多個行，要把最有可能鎖沖突的放在最後

死鎖：

1. 等待逾時，太長影響業務，太短容易誤傷，萬一隻是鎖等待呢

2. 一般用這種：設定檢測死鎖參數為on，然後主動復原死鎖鍊中的一條事務。

   但也有緻命問題：這是一個O（N）的操作，可能沒有死鎖但是一直在耗費大量CPU資源檢測

   解決辦法是，并發控制做在資料庫服務端，在更新相同行時，在進入引擎前排隊

第9講

InooDB會将這些更新操作緩存在change buffer中，這樣就不需要從磁盤中讀入這個資料頁了。在下次查詢需要通路這個資料頁的時候，将資料頁讀入記憶體，然後執行change buffer中與這個頁有關的操作。

changeBuffer隻适用于普通索引，不适合唯一索引。以及适用于寫多讀少的場景，如賬單類日志類。

因為唯一索引要判斷key是否唯一，要将資料頁讀入記憶體，不如直接更新記憶體，用不上changebuffer

redo log 主要節省的是随機寫磁盤的IO消耗（轉成順序寫），而change buffer主要節省的則是随機讀磁盤的IO消耗。

第11講

第一種方式是使用倒序存儲。如果你存儲身份證号的時候把它倒過來存，每次查詢的時候，你可以這麼寫：

mysql> select field_list from t where id_card = reverse(‘input_id_card_string’);

由于身份證号的最後6位沒有位址碼這樣的重複邏輯，是以最後這6位很可能就提供了足夠的區分度。當然了，實踐中你不要忘記使用count(distinct)方法去做個驗證。

第二種方式是使用hash字段。你可以在表上再建立一個整數字段，來儲存身份證的校驗碼，同時在這個字段上建立索引。

mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

然後每次插入新記錄的時候，都同時用crc32()這個函數得到校驗碼填到這個新字段。由于校驗碼可能存在沖突，也就是說兩個不同的身份證号通過crc32()函數得到的結果可能是相同的，是以你的查詢語句where部分要判斷id_card的值是否精确相同。

mysql> select field_list from t where id_card_crc=crc32(‘input_id_card_string’) and id_card=‘input_id_card_string’

這樣，索引的長度變成了4個位元組，比原來小了很多。

接下來，我們再一起看看使用倒序存儲和使用hash字段這兩種方法的異同點。

首先，它們的相同點是，都不支援範圍查詢。倒序存儲的字段上建立的索引是按照倒序字元串的方式排序的，已經沒有辦法利用索引方式查出身份證号碼在[ID_X, ID_Y]的所有市民了。同樣地，hash字段的方式也隻能支援等值查詢。

它們的差別，主要展現在以下三個方面：

從占用的額外空間來看，倒序存儲方式在主鍵索引上，不會消耗額外的存儲空間，而hash字段方法需要增加一個字段。當然，倒序存儲方式使用4個位元組的字首長度應該是不夠的，如果再長一點，這個消耗跟額外這個hash字段也差不多抵消了。

在CPU消耗方面，倒序方式每次寫和讀的時候，都需要額外調用一次reverse函數，而hash字段的方式需要額外調用一次crc32()函數。如果隻從這兩個函數的計算複雜度來看的話，reverse函數額外消耗的CPU資源會更小些。

從查詢效率上看，使用hash字段方式的查詢性能相對更穩定一些。因為crc32算出來的值雖然有沖突的機率，但是機率非常小，可以認為每次查詢的平均掃描行數接近1。而倒序存儲方式畢竟還是用的字首索引的方式，也就是說還是會增加掃描行數。

第12講

髒頁比例和read log寫入速度

innodb_io_capacity*R%

第13講

除非drop表，一般delete資料并不會減少存儲空間。隻是把資料頁标為了可複用

新增資料，造成頁分裂，前一個頁的末尾也是會形成資料空擋的。

可以通過重建表收縮表空間。

alter table A engine=InnoDB

Online DDL

但有時候在重建表的時候，InnoDB不會把整張表占滿，每個頁留了1/16給後續的更新用。也就是說，其實重建表之後不是“最”緊湊的。反而空間會大。

第14講

利用事務的原子性和隔離性保證計數準确。

count(*)、count(主鍵id)和count(1) 都表示傳回滿足條件的結果集的總行數；而count(字段），則表示傳回滿足條件的資料行裡面，參數“字段”不為NULL的總個數

對于count(主鍵id)來說，InnoDB引擎會周遊整張表，把每一行的id值都取出來，傳回給server層。server層拿到id後，判斷是不可能為空的，就按行累加。

對于count(1)來說，InnoDB引擎周遊整張表，但不取值。server層對于傳回的每一行，放一個數字“1”進去，判斷是不可能為空的，按行累加。

count()做了優化，不取值

count(字段)<count(主鍵id)<count(1)≈count()，

MySQL系統高可用的基礎，就是binlog複制

第15講

order by

全字段排序：開辟buffer，确定放入的列名。找相應的id,然後去主鍵索引中找到符合條件的select的列，然後放入buffer中，從索引中取下一個主鍵id。重複前兩步，直到不滿足條件，最後把order by的字段排序。

單行長度太長時：

rowid排序——buffer裡隻放排序的字段和主鍵，最後再通過主鍵id把最後一個字段找一遍放入buffer中。從索引中取下一個主鍵id。重複前兩步，直到不滿足條件，最後把order by的字段排序。

group by當聚集函數與非聚集函數在一起時，一定要用group by