1. 首頁 > 生活 > >

Mysql單表訪問方法，索引合并，多表連接原理，基于規則的優化，子查詢優化

生活百科

參考書籍《mysql是怎樣運行的》非常推薦這本書 ， 通俗易懂 ， 但是沒有講mysql主從等內容書中還講解了本文沒有提到的子查詢優化內容 ， 本文只總結了常見的子查詢是如何優化的系列文章目錄和關于我
一丶單表訪問方法mysql執行查詢語句的方法叫做訪問方法 ， 同一語句使用不同的訪問方法執行 ， 查詢結果都是一樣的 ， 但是不同的查詢方法效率差距很大 ， mysql優化器會選擇成本最低的訪問方法 ， 理解訪問方法對我們理解索引有益處
1.const查詢可以通過主鍵或者唯一索引與常數進行等值比較來定位一條記錄 ， 這種訪問方法被定位為const ， 如果唯一索引存在多列 ， 那么需要多列都進行等值比較 。（唯一索引不限制null元素的個數 ， 所以is null并不會使用const訪問方法）
2.ref搜索條件為二級索引與常數進行等值比較的 ， 形成的掃描區間為單點掃描區間（key='a' key是二級索引 ， 需要掃描的區間是[a,a],這稱為單點掃描區間）只需要定位到滿足條件的第一條記錄 ， 然后沿著B+樹葉子節點的指針向右查找直到不滿足條件即可（也許需要回表 ， 并不是將所有滿足的數據從二級索引上拿到主鍵然后一起回表 ， 而是每獲取一條便立即回表） 。這種訪問方法稱為ref.
同樣二級索引允許存儲null值 ， 且不限制個數（唯一二級索引也不限制）但是為null的值放在B+樹的最左側 ， 查找的流程任然一致 ， 即使是key is null 也必須要進行單點掃描、
如果二級索引存在多列 ， 并不需要多列都進行等值比較 ， 但是要求最左連續的列進行等值比較（比如聯合索引a,b,c,a=1 and c=2 可以使用ref ， 找到a=1向右并且索引下推過濾掉不滿足c=2的記錄 ， 減少回表 ， 但是如果是b=1 and c=2 這時候是無法使用ref的 ， 因為聯合索引是先按照a排序 ， 再依次b ， c 。）
3.ref_or_null查詢條件是二級索引等值查詢 or 二級索引 is null,可以使用ref_or_null,這其實涉及到兩個掃描區間[null,null],[等值,等值]執行流程和ref一樣 。
4.range使用索引執行查詢時 ， 對應的掃描區間是若干單點區間 ， 或者范圍掃描區間 ， 那么可以使用range(全表掃描不能算作range ， 單個單點掃描區間是ref而不是range）
5.index我們知道二級索引需要存儲索引列和主鍵 ， 聚簇索引需要存儲所有列和主鍵（以及隱藏列）所以二級索引大小遠小于聚簇索引 ， 且如果一個查詢不需要進行回表 ， 那么將直接利用二級索引進行全表掃描（索引小 ， 意味著IO次數小）這種訪問方法叫index
比如select 主鍵 from table where 無法走索引的條件 ， 那么這時候不如掃描二級索引 ， 其B+樹葉子節點保存的是主鍵和索引列 ， 每一頁可以存放更多數據 ， 減少IO次數 ， 其中的主鍵也可以覆蓋需要查詢的主鍵
6.all直接掃描所有的聚簇索引記錄
二丶多范圍讀取MRR上面我們說到回表 ， 是每從二級索引中獲取一條符合的數據都會到聚簇索引根據主鍵進行回表 ， 但是二級索引中的主鍵是無需的 ， 這導致每次執行回表操作都是隨機IO ， 導致性能開銷巨大 ， mysql為了優化這種隨機IO ， 使用了MRR多范圍讀取 ， 即先讀取一部分二級索引 ， 然后將主鍵值排序后再統一執行回標 ， 將隨機IO優化為順序IO 。

經驗總結擴展閱讀