Mysql優化（出自官方文件） - 第三篇

seancheer發表於2019-07-26
MySql優化

目錄

Mysql優化（出自官方文件） - 第三篇

1 Multi-Range Read Optimization(MRR)

當使用二級索引進行多範圍查詢的時候，往往會導致對磁碟大量的隨機訪問，這就導致了區域性性非常差，因此，Mysql在遇到這種場景的時候，首先會利用二級索引查詢出所有的唯一索引，然後進行排序，在進行訪問，這樣子，就能大大的減少隨機訪問，眾所周知，對於傳統機械硬碟，順序讀的效率比隨機讀的效率要高很多。

需要注意的是：

  • MRR優化不支援建立在virtual generated columns上的二級索引，InnoDB支援virtual generated columns

  • 如果Mysql使用了這種優化，那麼在EXPLAIN輸出結果的Extra列會顯示Using MRR

  • 如果查詢的結果完全可以通過index產生（covering index， 覆蓋索引：指部分列資訊儲存在索引裡面）那麼將不會使用MRR，

2 Block Nested-Loop(BNL) and Batched Key Access Joins(BKA)

BKA演算法可用於對joined table的索引訪問和join buffer，BKA支援inner join, outer join, semijoin，以及nested outer join，BKA可以大大提高table scanning的效能。

  • Join Buffer Management for Block Nested-Loop and Batched Key Access Algorithms

    在Mysql中，Join buffer分為regular buffer和incremental buffer，regular可以理解為我們通常理解的buffer，incremental則是建立在regular的前提下，儲存的部分列不是真正的資料，而是對上一個buffer的引用。假設有一個場景，三表join，t1 join t2 join t3，Mysql使用B1作為t1和t2的join臨時結果，B2作為和t3的結果，那麼通常來講：

    • B1是一個regular buffer，Mysql會把t1和t2的匹配結果儲存在B1中，B1中只會儲存匹配到的行，不會有任何多餘的行，並且，如果某一列是NULL，Mysql也不會為該列分配任何空間。
    • B2是一個incremental buffer，Mysql會把t3中匹配的行存入到B2中，然後再把B1中t1和t2的連結儲存到B2中，這樣子就避免了資料無意義的拷貝操作。

    可以看到incremental buffer不是獨立的，一般來講，需要多個buffer，第一個buffer往往是regular buffer，後面的buffer才是incremental buffer。

  • Block Nested-Loop Algorithm for Outer Joins and Semijoins

    當前的Mysql BNL演算法已經支援outer join和semi join了，當join操作發生在join buffer上的時候，通過將row放到buffer裡面會同時放入一個match flag，表示join的匹配情況。

    如果Mysql使用了BNL，那麼將會在EXPLAIN中體現如下，在Extra列，結果為： Using join buffer (Block Nested Loop) ， 在Type列，為： ALL, index, 或者range.

  • Batched Key Access Joins

    BKA通常用在有索引訪問的第二個join上，在BNL join演算法中，當第一個join使用join buffer產生了多個列，此時BKA會把第一個join結果的多個key批量發給MRR（第一節）介面，MRR engine會使用更優化的方式進行table scan，可以大大提升效率。

    在EXPLAIN中，如果使用BKA，那麼Extra列的結果為：Using join buffer (Batched Key Access)， type列的結果為：ref or eq_ref.

相關文章