查找結構的進化
二分查找
二叉樹
二叉平衡樹
B-TREE ：二叉平衡樹的基礎上，使加載一次節點，可以加載更多路徑資料，同時把查詢范圍縮減到更小
缺點：業務資料的大小可能遠遠超過了索引資料的大小，每次為了查找對比計算，需要把資料加載到記憶體以及 CPU 高速快取中時，都要把索引資料和無關的業務資料全部查出來，本來一次就可以把所有索引資料加載進來，現在卻要多次才能加載完，如果所對比的節點不是所查的資料，那么這些加載進記憶體的業務資料就毫無用處，全部拋棄，
B+TREE：非葉子節點只保存索引資料，葉子節點保存索引資料與業務資料所在的地址
-
B+資料量相同的情況下，非葉子節點可以存放更多的資料，B+樹會更加矮胖，io次數會更少
-
B+樹有大量的冗余節點（非葉子結點），會讓B+樹在插入洗掉時的效率更高
-
B+樹的葉子結點用雙向鏈表連了起來，有益于范圍查詢，而B樹只能遍歷，
聚簇索引和非聚簇索引
如果葉子節點存盤的是實際資料的就是聚簇索引，一個表只能有一個聚簇索引；如果葉子節點存盤的不是實際資料，而是主鍵值則就是二級索引，一個表中可以有多個二級索引，
在使用二級索引進行查找資料時，如果查詢的資料能在二級索引找到，那么就是「索引覆寫」操作，如果查詢的資料不在二級索引里，就需要先在二級索引找到主鍵值，需要去聚簇索引中獲得資料行，這個程序就叫作「回表」
查詢資料的程序
在定位記錄所在哪一個頁時，也是通過二分法快速定位到包含該記錄的頁，定位到該頁后，又會在該頁內進行二分法快速定位記錄所在的分組（槽號），最后在分組內進行遍歷查找，
磁盤IO
磁盤處理太慢太慢了
- 盡量減少 I/O 次數，比如可以使用快取；
- 每次 I/O 盡量獲取更多的資料；
- 每次 I/O 盡量獲取有用的資料，當然相應的也間接減少總 I/O 次數
總結：
- 資料存盤在磁盤（ SSD 跟 CPU 性能也不在一個量級），而磁盤處理資料很慢；
- 提高磁盤性能主要通過減少 I/O 次數，以及單次 I/O 有效資料量；
- 索引通過多階（一個節點保存多個資料，指向多個子節點）使樹的結構更矮胖，從而減少 I/O 次數；
- 索引通過 B+ 樹，把業務資料與索引資料分離，來提高單次 I/O 有效資料量，從而減少 I/O 次數；
- 索引通過樹資料的有序和「二分查找」（多階樹可以假設為多分查找），大大縮小查詢范圍；
- 索引針對的是單個欄位或部分欄位，資料量本身比一條記錄的資料量要少的多，這樣即使通過掃描的方式查詢索引也比掃描資料庫表本身快的多；
事務
- 持久性是通過 redo log （重做日志）來保證的；是物理日志，記錄做了什么修改
- 原子性是通過 undo log（回滾日志） 來保證的；
- 一致性是binlog保證的 ，邏輯日志（有三種格式）statement，row包含操作的具體資料，mixed
- 隔離性是通過 MVCC（多版本并發控制） 或鎖機制來保證的；
死鎖問題
處理訂單業務時，需要用到select…for update用來避免并發導致的幻讀問題，但是這樣的話就容易出現死鎖
處理方法是破壞形成死鎖的條件：打破回圈等待條件
- 設定事務等待鎖的超時時間，當一個事務的等待時間超過該值后，就對這個事務進行回滾，于是鎖就釋放了，另一個事務就可以繼續執行了，在 InnoDB 中，引數
innodb_lock_wait_timeout是用來設定超時時間的，默認值時 50 秒，
- 開啟主動死鎖檢測，主動死鎖檢測在發現死鎖后，主動回滾死鎖鏈條中的某一個事務，讓其他事務得以繼續執行，將引數
innodb_deadlock_detect設定為 on，表示開啟這個邏輯，默認就開啟，
關于count
*count(1)、 count()、 count(主鍵欄位)**在執行的時候，如果表里存在二級索引，優化器就會選擇二級索引進行掃描，
所以，如果要執行 count(1)、 count(*)、 count(主鍵欄位) 時，盡量在資料表上建立二級索引，這樣優化器會自動采用 key_len 最小的二級索引進行掃描，相比于掃描主鍵索引效率會高一些，
再來，就是不要使用 count(欄位) 來統計記錄個數，因為它的效率是最差的，會采用全表掃描的方式來統計，如果你非要統計表中該欄位不為 NULL 的記錄個數，建議給這個欄位建立一個二級索引，
優化count
如果資料量很大，因為要全表掃描，所以也要花費不短的時間
1.使用explain 出現的rows 欄位值就是 explain 命令對表 t_order 記錄的估算值，
2.額外表保存記錄值
索引失效的情況
- 當我們使用左或者左右模糊匹配的時候，也就是
like %xx或者like %xx%這兩種方式都會造成索引失效； - 當我們在查詢條件中對索引列使用函式，就會導致索引失效，
- 當我們在查詢條件中對索引列進行運算式計算，也是無法走索引的，
- MySQL 在遇到字串和數字比較的時候，會自動把字串轉為數字，然后再進行比較，如果字串是索引列，而條件陳述句中的輸入引數是數字的話，那么索引列會發生隱式型別轉換，由于隱式型別轉換是通過 CAST 函式實作的，等同于對索引列使用了函式，所以就會導致索引失效，
- 聯合索引要能正確使用需要遵循最左匹配原則，也就是按照最左優先的方式進行索引的匹配，否則就會導致索引失效，
- 在 WHERE 子句中，如果在 OR 前的條件列是索引列，而在 OR 后的條件列不是索引列，那么索引會失效，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/499555.html
標籤：MySQL
上一篇：Mysql的知識梳理
下一篇：Mysql的知識梳理