(三十三) MySQL中的分頁查詢及執行計劃

文章目錄

• MySQL中的分頁查詢
• • MySQL分頁查詢原則
  • LiMIT字句
  • • 文法格式:
    • 示例
  • LiMIT OFFSET 字句
  • • 文法格式
    • 示例:
• 執行計劃
• • MySQL執行計劃
  • MySQL整個查詢執行過程
  • 啟動執行計劃
  • EXPLAIN列的解釋
  • • ID
    • select_type
    • table
    • * type
    • Possible_keys
    • Key
    • Key_len
    • ref
    • rows
    • Fitered
    • extra
• 上一章:[MySQL全文索引及使用者管理](https://blog.csdn.net/qq_36835560/article/details/98623362)
• 下一章: [MySQL存儲引擎](https://blog.csdn.net/qq_36835560/article/details/98623860)

MySQL中的分頁查詢

MySQL分頁查詢原則

• 在MySQL資料庫中使用limit字句進行分頁查詢
• MySQL分頁中開始位置為0
• 分頁字句在查詢語句的最後側

LiMIT字句

文法格式:

select 投影列 from 表名 where 條件 order by limit 開始位置, 查詢數量
           

示例

查詢雇員表中所有資料按ID排序, 實作分頁查詢, 每次傳回兩條結果

select * from emp
ORDER BY employee_id
limit 0, 2;
           

LiMIT OFFSET 字句

文法格式

SELECT 投影列 FROM 表名 WHERE 條件 ORDER BY LIMIT 查詢數量 OFFSET
開始位置。 
           

示例:

查詢雇員表中所有資料按 id 排序， 使用 LIMIT OFFSET 實作分頁查詢， 每次傳回兩條

結果。

select * from emp
ORDER BY employee_id
limit 2 OFFSET 0;
           

執行計劃

MySQL執行計劃

在MySQL中可以通過explain關鍵字來模拟優化器執行SQL語句, 進而知道MySQL是如何處理SQL語句的

MySQL整個查詢執行過程

1. 用戶端向MySQL伺服器發送一條查詢請求
2. 伺服器首先檢查查詢緩存, 如果命中緩存, 則立即傳回存儲在緩存中的結果. 否則進入下一階段
3. 伺服器進行SQL解析. 預處理, 再由優化器生成相應的執行計劃
4. MySQL根據執行計劃, 調用存儲引擎的API來執行查詢
5. 将結果傳回給用戶端, 同時緩存查詢結果

啟動執行計劃

explain select 投影列 from 表名 where 條件

EXPLAIN列的解釋

ID

查詢執行順序

id值相同時表示從上向下執行

id值相同被視為一組

如果是子查詢, id值會遞增, id值越高, 優先級越高

select_type

• simple: 表示查詢中不包含子查詢或者union
• primary: 當查詢中包含任何複雜的子查詢, 最外層查詢被标記為primary
• derived: 在from的清單中包含的子查詢被标記為derived
• subquery: 在select或where清單中包含了子查詢, 則子查詢被标記為subquery
• union: 兩個select查詢時前一個标記為primary, 後一個标記為union. union出現在from從句子查詢中, 外層select标記為primary, union中第一個查詢為derived. 第二個子查詢标記為union
• unionresult: 從union表擷取結果的select被标記為union result

table

顯示這一行的資料是關于哪張表的

* type

這是重要的列, 顯示連接配接使用了何種類型. 從最好到最差的連接配接類型為: system、const、eq_reg、ref、range、index和ALL

• System： 表中隻有一行資料。屬于const的特例。如果實體表中隻有一行資料，那麼連接配接類型為ALL
• const：查詢結果最多有一個比對行，是以可以被視為常量。const的查詢速度非常快，因為隻讀一次。一般情況下把主鍵或唯一索引作為唯一條件的查詢都是const
• eq_ref：查詢時查詢外鍵表全部資料。且隻能查詢主鍵列或關聯列。且外鍵表中外鍵列中資料不能有重複資料，且這些資料都必須在主鍵表中有對應資料。（主鍵表中資料可以有沒有用到的）
• ref：相比eq_ref，不對外鍵列有強制要求，裡邊的資料可以重複，隻要出現重複的資料取值就是ref。也可能時索引查詢
• range：把這個列當作條件隻檢查其中一個範圍。常見where從句中出現between、<、in等。主要應用在具有索引的列中
• index：這個連接配接類型對前面的表中每一個記錄聯合進行完全掃描（比ALL更好，因為索引一般小于表資料。）
• All：這個連接配接類型對于前面的每一個記錄聯合進行完全掃描，這一般比較糟糕。應該盡量避免。

Possible_keys

查詢條件字段涉及到的索引，可能沒有使用。

Key

實際使用的索引。如果為NULL，則沒有使用索引

Key_len

表示索引中使用的位元組數，查詢中使用的索引長度（最大可能長度），并非實際使用長度，理論上長度越短越好。key_len是根據表定義計算而得的，不是通過表檢索出的。

ref

顯示做因的那一列被使用了，如果可能的化，是一個常量const

rows

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

Fitered

顯示了通過條件過濾出的行數的百分比過濾值。

extra

MySQL如何解析查詢的額外資訊

• Distinct：MySQL發現第一個比對行後，停止為目前的行組合搜尋更多的行。
• Not exists：MySQL能夠對查詢進行Left join優化，發現一個比對LEFT JOIN标準的行後，不再為前面的行組合在該表内檢查更多的行。
• range checked for each record（index map: #)：MySQL沒有發現好的可以使用的索引，但發現如果來自前面的表的列值已知，可能部分索引可是使用。
• Using filesort：MySQL需要額外的一次傳遞，以找出如何按照排序順序檢索行
• Using index：從隻使用索引樹中的資訊而不需要進一步搜尋讀取實際的行來檢索表中的列資訊。
• Using temporary:為了解決查詢,MySQL 需要建立一個臨時表來容納結果。
• Using where:WHERE 子句用于限制哪一個行比對下一個表或發送到客戶。

• Using sort_union(…), Using union(…), Using intersect(…): 這 些 函 數 說 明 如 何 為

  index_merge 聯接類型合并索引掃描。

• Using index for group-by:類似于通路表的 Using index 方式,Using index for group-by 表示

  MySQL 發現了一個索引,可以用來查 詢 GROUP BY 或 DISTINCT 查詢的所有列,而不要

  額外搜尋硬碟通路實際的表

上一章:MySQL全文索引及使用者管理

下一章: MySQL存儲引擎