MySQL 必知概念

Delete、Drop 和 Truncate

• delete、truncate 僅僅刪除表裡面的資料，drop會把表的結構也刪除
• delete 是 DML 語句，操作完成後，可以回滾，truncate 和 drop 是 DDL 語句，刪除之後立即生效，不能回滾
• 執行效率：drop > truncate > delete

MyISAM 與 InnoDB

• InnoDB 支援事務，MyISAM 不支援
• InnoDB 支援外來鍵，MyISAM 不支援
• InnoDB 是聚集索引，資料檔案是和索引繫結一起的
• MyISAM 是非聚簇索引，索引和資料檔案是分離的，索引儲存的是資料的指標
• InnoDB 不儲存表的具體行數，執行 select count(*) from table 時需要全表掃描
• MyISAM 用一個變數儲存整個表的行數，執行上述語句時只需要讀出改變數即可，速度很快
• InnoDB 支援表、行（預設）級鎖，MyISAM 支援表級鎖

Join 語句

left join、right join、inner join 的區別：

left join（左連線）：

• 返回包括左表中的所有記錄和右表中聯結欄位相等的記錄
• 左表是驅動表，右表是被驅動表

right join（右連線）：

• 返回包括右表中的所有記錄和左表中聯結欄位相等的記錄
• 右表是驅動表，左表是被驅動表

innner join（等值連線）：

• 只返回兩個表中聯結欄位相等的行
• 資料量比較小的表作為驅動表，大表作為被驅動表

---

join 查詢在有索引條件下：

• 驅動表有索引不會使用到索引
• 被驅動表建立索引會使用到索引、

所以在以小表驅動大表的情況下，給大表建立索引會大大提高查詢效率

Join 原理：

Simple Nested-Loop：

• 驅動表中的每一條記錄與被驅動表中的記錄進行比較判斷（笛卡爾積）
• 對於兩表聯結來說，驅動表只會被訪問一遍，但驅動表卻要被訪問到好多遍

Index Nested-Loop：

• 基於索引進行連線的演算法
• 他要求被動表驅動表上有索引，可以透過索引來加速查詢

Block Nested-Loop：

• 它使用 Join Buffer 來減少內部迴圈讀取表的次數
• Join Buffer 用以快取聯接需要的列

選擇 Join 演算法優先順序：

• Index Nested-LoopJoin > Block Nested-Loop Join > Simple Nested-Loop Join

當不使用 Index Nested-Loop Join 的時候，預設使用 Block Nested-Loop Join

分頁查詢最佳化

select * from table 
where 
type = 2 
and level = 9 
order by id asc 
limit 190289,10;

延遲關聯：

• 透過 where 條件提取出主鍵，再將該表與原資料表關聯，透過主鍵 id 提取資料行，而不是透過原來的二級索引提取資料行

• select a.* from table a,
  (
      select id from table 
      where type = 2 
      and level =9 
      order by id asc 
      limit 190289,10
  ) b 
  where a.id = b.id;
  

書籤方式：

• 找到 limit 第一個引數對應的主鍵值， 在根據這個主鍵值再去過濾並 limit

• select * from table 
  where 
  id > (
      select * from table 
      where type = 2 
      and level = 9 
      order by id asc 
      limit 190289, 1
  	) 
  limit 10;
  

事務