[TOC]  # 索引 ## **什么是索引?** 索引是一種數據結構，可以幫助我們快速的進行數據查找。 ## **MySQL 索引有哪幾種** 常見的有普通索引（Index）、主鍵索引（Primary Key）、唯一索引（Unique）、全文索引（Fulltext） ## **索引是個什么樣的數據結構** 索引的數據結構和具體的存儲引擎有關，在MySQL中使用較多的索引有Hash索引,B+樹索引。InnoDB 存儲引擎的默認索引實現為：B+樹索引。 ## **Hash索引和B+樹索引的區別** Hash 索引底層及 Hash表，查找時，調用一次 hash 函數就可以獲取相應的鍵值，之后進行**回表**查詢獲取實際數據。 B+Tree 底層實現是**多路平衡查找樹**，對于每次查詢都是從根節點出發，查找到葉子節點就可以獲得所查鍵值，然后根據查詢判斷是否需要回表查詢數據。 Hash 索引經過hash函數建立索引之后，索引的順序與原順序無法保持一致，不能支持范圍查詢。 B+Tree 的所有節點遵循 **左節點小于父節點，右節點大于父節點**，支持范圍操作。 * Hash索引進行等值查詢更快（一般情況下），無法進行范圍查詢 * Hash索引不支持使用索引進行排序 * Hash索引不支持模糊查詢及多列索引的**最左前綴匹配** * Hash索引每次查詢都要**回表**，而B+樹在符合某些條件(**聚簇索引**,**覆蓋索引**等)的時候可以只通過索引完成查詢。 * Hash索引不穩定，性能不可預測，當某個鍵值在大量重復的時候，發生 **Hash碰撞**，此時效率極差。而B+樹的查詢效率比較穩定,對于所有的查詢都是從根節點到葉子節點,且樹的高度較低. ## **什么是聚簇索引、非聚簇索引** **聚簇索引**：索引的葉節點指向數據。**非聚簇索引**：索引的葉節點指向數據的引用。 InnoDB 中，主鍵索引就是聚簇索引，如果沒有主鍵，則挑選一個唯一鍵建立聚簇索引，如果沒有唯一鍵則隱式的生成一個鍵來建立聚簇索引。 當查詢使用聚簇索引時，在對應的葉子節點，可以獲取到整行數據，因此不用再次進行回表查詢。 ## **非聚簇索引一定會回表查詢嗎** 不一定。 如果查詢語句中要求的字段全部命中了索引，則不需要**回表查詢**。 ## **索引使用原則** * 列獨立 保證索引包含的字段獨立在查詢語句中，不能是在表達式中 * 左前綴 like:：匹配模式左邊不能以通配符開始，才能使用索引 * 復合索引有左到右失效 最左原則，要同時考慮列查詢的頻率和列的區分度。 * 不濫用索引，多余索引會降低讀寫性能 **即使滿足了上述原則，mysql還是可能會棄用索引，因為有些查詢即使使用索引，也會出現大量的隨機io，相對于從數據記錄中的順序io開銷更大** ## **索引哪些情況會失效** * 查詢條件包含 `or`。 * 隱式類型轉換。例：`age` 字段類型為 **int**，`where age = '1'` 就會觸發隱式類型轉換。 * `like` 通配符在左邊。`%a` * 聯合索引條件不符合最左前綴原則 * 對索引字段進行函數運算 * 對索引列運算 * 索引字段上使用 `!=`、`<>`、`not in` 時 * 索引字段上使用 `is null`，`is not null` * 相 `join`的兩個表字符編碼不同，會導致笛卡爾積的循環計算 * MySQL 認為使用全表掃描比使用索引快時。 * 查詢的數據超過總數據行數30% ## **索引不適合哪些場景** * 數據量少的不適合 * 更新比較頻繁的不合適 * 離散型低的字段不合適 # 鎖 ## **都有哪些鎖** **共享鎖**（S鎖、讀鎖）：： 允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖。即多個客戶可以同時讀取同一個資源，但不允許其他客戶修改。 **排他鎖**（X鎖、寫鎖）： 允許獲得排他鎖的事務更新數據，阻止其他事務取得相同數據集的讀鎖和寫鎖。寫鎖是排他的，寫鎖會阻塞其他的寫鎖和讀鎖。 鎖的粒度取決于具體的存儲引擎,InnoDB實現了**行級鎖,頁級鎖,表級鎖**。 **讀讀不阻塞，讀寫阻塞，寫寫阻塞** # 表結構設計 ## **為什么要盡量設定一個主鍵?** 主鍵是數據庫確保數據行在整張表唯一性的保障，即使業務上本張表沒有主鍵，也建議添加一個自增長的 ID 列作為主鍵，設定了主鍵之后，在后續的刪改查的時候可能更加快速，以及確保操作數據范圍安全。 ## **主鍵使用自增ID還是UUID** 使用自增ID,不要使用UUID 因為在 InnoDB存儲引擎中，主鍵索引是作為聚簇索引存在的，也就是說，主鍵索引的 B+Tree 葉子節點上存儲了主鍵索引以及全部的數據（按照順序） 如果主鍵索引的自增ID，那么只需要不斷向后排列即可，如果是UUID，由于到老的ID與原來的大小不確定，會造成非常多的數據插入，數據移動，然后產生很多的內存碎片，造成插入性能下降。 ## **字段為什么要求定義為not null** null 值會占用更多的字節,且會在程序中造成很多與預期不符的情況. ## **如果要存儲用戶的密碼散列,應該使用什么字段進行存儲** 密碼散列,鹽,用戶身份證號等固定長度的字符串應該使用char而不是varchar來存儲,這樣可以節省空間且提高檢索效率. ## **varchar和char有什么區別.** `char`是一個定長字段,假如申請了`char(10)`的空間,那么無論實際存儲多少內容.該字段都占用10個字符,而varchar是變長的,也就是說申請的只是最大長度,占用的空間為實際字符長度+1,最后一個字符存儲使用了多長的空間. 在檢索效率上來講,`char `> `varchar`,因此在使用中,如果確定某個字段的值的長度,可以使用char,否則應該盡量使用varchar.例如存儲用戶MD5加密后的密碼,則應該使用char. ## **MySQL的binlog有有幾種錄入格式?分別有什么區別** * Statement：每一條會修改數據的sql都會記錄到master的bin-log中。 * Row：記錄每一行數據被修改的形式。 * Mixed：根據執行的每一條具體的sql語句來區分對待記錄的日志格式，也就是在Statement和Row之間選擇一種。 ## ****關心過業務系統里面的sql耗時嗎?統計過慢查詢嗎?對慢查詢都怎么優化過?**** 在業務系統中,除了使用主鍵進行的查詢,其他的我都會在測試庫上測試其耗時,慢查詢的統計主要由運維在做,會定期將業務中的慢查詢反饋給我們. 慢查詢的優化首先要搞明白慢的原因是什么? 是查詢條件沒有命中索引?是load了不需要的數據列?還是數據量太大? * 首先分析語句,看看是否load了額外的數據,可能是查詢了多余的行并且拋棄掉了,可能是加載了許多結果中并不需要的列,對語句進行分析以及重寫. * 分析語句的執行計劃,然后獲得其使用索引的情況,之后修改語句或者修改索引,使得語句可以盡可能的命中索引. * 如果對語句的優化已經無法進行,可以考慮表中的數據量是否太大,如果是的話可以進行橫向或者縱向的分表. ## **橫向分表和縱向分表** **橫向分表**是按行分表，假設我們有一張用戶表，主鍵是自增 id 且同時是用戶的 id，數據量較大，有1億多條，那么此時放在一張表里查詢效率就不理想，我們可以根據主鍵 ID 分表，無論是按尾號分，或者按 id 的區間分都可以。假設按照尾號0-99分為 100 個表，那么每張表中的數據就僅有100W。 **縱向分表**是按列分表，假設我們現有一張文章表，包含字段`id-摘要-內容`。而系統中的展示形式是刷新出一個列表，列表中僅包含標題和摘要，當用戶點擊謀篇文章詳情時才無需要正文內容，此時如果數據量大，將內容這個很大且不經常使用的列放在一起會拖慢原表的查詢速度，可以將上面表分為兩張`id-摘要`，`id-內容` ，當用戶點擊詳情時按照id 取出內容，而增加的存儲量只是很小的主鍵字段.代價很小. ## **三大范式** * 每個列都不可以在拆分 * 非主鍵列完全依賴于主鍵，而不能是依賴于主鍵的一部分 * 非主鍵列只能依賴于主鍵，不依賴于其他主鍵。