Mysql默認搜索引擎

Mysql5.5以后默認使用InnoDB為搜索引擎

MyISAM是表鎖，不支持事務和主外鍵

InnoDB默認可以創建16個索引

• InnoDB支持事務，MyIsam不支持事務，對于InnoDB每一條SQL語言都默認封裝成事務，自動提交，這樣會影響速度，所以最好把多條SQL語言放到begin 和 commit之間，組成一個事務；
• InnoDB支持外鍵，而MyIsam不支持，對一個包含外鍵的InnoDB表轉成MyIsam表會失敗
• InnoDB是聚集索引，數據文件和索引綁定在一塊，必須要有主鍵，通過主鍵索引效率很高，但是輔助索引需要兩次查詢，先查詢到主鍵，然后在通過主鍵查詢到數據。因此主鍵不應該過大。主鍵過大的時候，其它索引也會很大。而MyIsam是非聚集索引，數據和文件是分離的，索引保存的是數據文件的指針，主鍵索引和輔助索引是獨立的。
• InnoDB不支持全文檢索，而MyIsam支持全文檢索，查詢效率上MyIsam要高

硬盤

Mysql是存儲在硬盤上，因此Redis比Mysql快

索引(mysql必問)

Mysql官方對索引的定位為：索引是能快速排序的數據結構。

可以簡單的理解為：排好序的快速查找B+樹數據結構，B+樹中的B代表平衡(balance)

聚簇索引和非聚簇索引

• 聚簇索引：將數據存儲與索引放到一塊，索引結構的葉子節點保存了行數據
• 非聚簇索引：將數據與索引分開，索引結構的葉子節點指向了數據對應的位置

在InnoDB中，在聚簇索引之上創建的索引被稱為輔助索引，非聚簇索引都是輔助索引，像復合索引，前綴索引，唯一索引。輔助索引葉子節點存儲不再是行的物理位置，而是主鍵值，輔助索引訪問數據總是需要二次查找，這個就被稱為 回表操作

InnoDB使用的是聚簇索引，將主鍵組織到一棵B+樹中，而行數據就儲存在葉子節點上，若使用"where id = 14"這樣的條件查找主鍵，則按照B+樹的檢索算法即可查找到對應的葉節點，之后獲得行數據。

若對Name列進行條件搜索，則需要兩個步驟：第一步在輔助索引B+樹中檢索Name，到達其葉子節點獲取對應的主鍵。第二步使用主鍵在主索引B+樹種再執行一次B+樹檢索操作，最終到達葉子節點即可獲取整行數據。（重點在于通過其他鍵需要建立輔助索引）

MyISAM使用的是非聚簇索引，非聚簇索引的兩棵B+樹看上去沒什么不同，節點的結構完全一致只是存儲的內容不同而已，主鍵索引B+樹的節點存儲了主鍵，輔助鍵索引B+樹存儲了輔助鍵。表數據存儲在獨立的地方，這兩顆B+樹的葉子節點都使用一個地址指向真正的表數據，對于表數據來說，這兩個鍵沒有任何差別。由于索引樹是獨立的，通過輔助鍵檢索無需訪問主鍵的索引樹。（MylSAM目前沒有問到，僅了解就行）

使用聚簇索引的優勢（了解）

**每次使用輔助索引檢索都要經過兩次B+樹查找，**看上去聚簇索引的效率明顯要低于非聚簇索引，這不是多此一舉嗎？聚簇索引的優勢在哪？

1.由于行數據和聚簇索引的葉子節點存儲在一起，同一頁中會有多條行數據，訪問同一數據頁不同行記錄時，已經把頁加載到了Buffer中（緩存器），再次訪問時，會在內存中完成訪問，不必訪問磁盤。這樣主鍵和行數據是一起被載入內存的，找到葉子節點就可以立刻將行數據返回了，如果按照主鍵Id來組織數據，獲得數據更快。

2.輔助索引的葉子節點，存儲主鍵值，而不是數據的存放地址。好處是當行數據放生變化時，索引樹的節點也需要分裂變化；或者是我們需要查找的數據，在上一次IO讀寫的緩存中沒有，需要發生一次新的IO操作時，可以避免對輔助索引的維護工作，只需要維護聚簇索引樹就好了。另一個好處是，因為輔助索引存放的是主鍵值，減少了輔助索引占用的存儲空間大小。

注：我們知道一次io讀寫，可以獲取到16K大小的資源，我們稱之為讀取到的數據區域為Page。而我們的B樹，B+樹的索引結構，葉子節點上存放好多個關鍵字（索引值）和對應的數據，都會在一次IO操作中被讀取到緩存中，所以在訪問同一個頁中的不同記錄時，會在內存里操作，而不用再次進行IO操作了。除非發生了頁的分裂，即要查詢的行數據不在上次IO操作的換村里，才會觸發新的IO操作。

3.因為MyISAM的主索引并非聚簇索引，那么他的數據的物理地址必然是凌亂的，拿到這些物理地址，按照合適的算法進行I/O讀取，于是開始不停的尋道不停的旋轉。聚簇索引則只需一次I/O。（強烈的對比）

4.不過，如果涉及到大數據量的排序、全表掃描、count之類的操作的話，還是MyISAM占優勢些，因為索引所占空間小，這些操作是需要在內存中完成的。

聚簇索引需要注意的地方（容易問到）

當使用主鍵為聚簇索引時，主鍵最好不要使用uuid，因為uuid的值太過離散，不適合排序且可能出線新增加記錄的uuid，會插入在索引樹中間的位置，導致索引樹調整復雜度變大，消耗更多的時間和資源。

建議使用int類型的自增，方便排序并且默認會在索引樹的末尾增加主鍵值，對索引樹的結構影響最小。而且，主鍵值占用的存儲空間越大，輔助索引中保存的主鍵值也會跟著變大，占用存儲空間，也會影響到IO操作讀取到的數據量。

為什么主鍵通常建議使用自增id

聚簇索引的數據的物理存放順序與索引順序是一致的，即：只要索引是相鄰的，那么對應的數據一定也是相鄰地存放在磁盤上的。如果主鍵不是自增id，那么可以想 象，它會干些什么，不斷地調整數據的物理地址、分頁，當然也有其他一些措施來減少這些操作，但卻無法徹底避免。但，如果是自增的，那就簡單了，它只需要一 頁一頁地寫，索引結構相對緊湊，磁盤碎片少，效率也高。

Mysql為什么是B+樹

B+樹中，所有數據記錄節點都是按照鍵值大小順序存放在同一層葉子節點上，而非葉子節點上只存儲key值信息，這樣可以大大加大每個節點存儲的key值數量，降低B+樹的高度。

• InnoDB存儲引擎的最小存儲單元是頁，頁可以用于存放數據，也可以用于存放鍵值+指針，在B+樹中葉子節點存放數據，而非葉子節點存放鍵值+指針
• 索引組織表通過非葉子節點的二分查找法以及指針確定數據在哪個頁中，首先找到根頁進而去數據頁查找到需要的數據

B+樹算法：通過集成B樹的特征，B+樹相比B樹，新增葉子節點與非葉子節點關系，葉子節點包含了鍵值和數據，非葉子節點只是包含鍵值和子節點引用，不包含數據。

通過非葉子節點查詢葉子節點獲取相應的數據，所有相鄰的葉子節點包含非葉子節點使用鏈表進行結合，葉子節點是順序并且相鄰節點有順序引用關系。

支持范圍查詢的原因是因為底部是雙向鏈表

底層原理

數據庫索引是存儲在磁盤上的，如果數據很大，必然導致索引的大小也會很大，超過幾個G（好比新華字典字數多必然導致目錄厚）

當我們利用索引查詢時，是不可能將全部幾個G的索引都加載進內存的，我們能做的只能是：

逐一加載每一個磁盤頁，因為磁盤頁對應著索引樹的節點。

InnoDB的 page_size

SHOW GLOBAL STATUS LIKE 'Innodb_page_size';

系統從磁盤讀取數據到內存時是以磁盤塊（block）為單位的，位于同一磁盤塊中的數據會被一次性讀取出來，而不是需要什么取什么

InnoDB存儲引擎中有頁(Page)的概念，頁是其磁盤管理的最小單位。

系統一個磁盤塊的存儲空間往往沒有這么大，因此InnoDB每次申請磁盤空間時都會是若干地址連續磁盤塊來達到頁的大小16KB。InnoDB在把磁盤數據讀入到磁盤時會以頁為基本單位，在查詢數據時如果一個頁中每條數據都有助于定位數據記錄的位置，這將會減少磁盤I/O次數，提高效率。

一句話說：就是多個塊填充到一頁大小

最左前綴原則(重要)

最左前綴原則主要使用在聯合索引中

給a,b,c加上索引，那么a,b可以用到索引，a,c也可以用到索引，但b,c是用不到的。包括這個a必須要在用到的第一個索引處。但是如果一個表中只有a,b,c三個字段，給他們加上聯合索引（a,b,c）。

索引失效

1、組合索引，不是使用第一列索引，索引失效

2、like 已通配符開頭（%abc）導致索引失效 （解決方法：使用覆蓋索引）

3、or語句前后沒有同時使用索引。當or左右查詢字段只有一個是索引，該索引失效，只有當or左右查詢字段均為索引時，才會生效

4、使用 is null 或者 is not null 也不能使用索引

5、使用不等于（！= 或者<>）不能使用索引

6、不要在索引列上做任何操作

7、如果在索引上加上一些sql的操作會使索引失效,前提是其中一個索引不是id,如果是的話是可以做一些簡單的操作的

索引調優

使用explain+sql語句進行調優

explain 包含的信息包含： 主要從 id、type、key、rows、Extra 分析。

id 表示執行的先后順序，id 值大的先執行，小的后執行，id 值相同的從上到下執行。type

訪問類型，結果值從好到壞依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

　　　建議盡量達到 range 級別，常見類型介紹如下：

const：通過索引一次找到，通常用于主鍵或唯一性索引。

eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵 或 唯一索引掃描。

ref：非唯一性索引掃描，返回匹配某個單獨值的所有行。

range：只檢索給定范圍的行，使用一個索引來選擇行。key列顯示使用了那個索引。一般就是在where語句中出現了bettween、<、>、in等的查詢。

index：Index與ALL雖然都是讀全表，但index是從索引中讀取，而ALL是從硬盤讀取。

ALL: Full Table Scan，全表掃描 。

key

　　　　實際使用的索引，如果為NULL，則沒有使用索引。查詢中如果使用了覆蓋索引，則該索引僅出現在key列表中 。

rows

　　　　根據表統計信息及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數。

Extra

Using index： 表示相應的 select 操作中使用了覆蓋索引（Covering Index），避免了訪問表的數據行，效率高 。Using where，表明索引被用來執行索引鍵值的查找，如果沒用同時出現 Using where，表明索引用來讀取數據而非執行查找動作。

Using where：表示使用了where條件過濾。

Convering Index：覆蓋索引表示直接從索引中讀取數據，sql中查詢字段、where條件等涉及的字段都在覆蓋索引包含的字段里面。

Using Index Condition：優化器在索引存在情況下通過符合range范圍的條數和總數比例來確定進行索引還是全表遍歷。

Using filesort：無法利用索引完成的排序操作。

Using temporary：使用臨時表保存中建結果，如order by和group by，出現臨時表需要優化sql。

數據庫調優

mysql庫主從讀寫分離。

SQL語句及索引的優化

使用連接(JOIN)來代替子查詢

適用聯合(UNION)來代替手動創建的臨時表

數據庫表結構的優化

找規律分表，減少單表中的數據量提高查詢速度。

總結

以上是生活随笔為你收集整理的新华字典mysql_JAVA面试（1）Mysql的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。