MYSQL: Handler_read_%引數說明

G8bao7發表於2014-01-28
MySql
環境：
表t_feed_idx(user_id bigint, feed_id bigint, KEY (`user_id`,`feed_id`)) engine=innodb;
表t_feed_info(feed_id bigint, PRIMARY KEY (`feed_id`), '其他列') engine=innodb;
feed 總數 : 100000
使用者(ID:20000001) feed 數 : 200

查詢

#########  單表
> 全部掃描
    EXPLAIN SELECT * FROM `t_feed_info` AS i ORDER BY NULL LIMIT 0, 10;

點選(此處)摺疊或開啟

  1. id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
  2. 1    SIMPLE    i    ALL    \\N    \\N    \\N    \\N    98734
    Handler_read_first + 1 : 從(主)鍵的第一個位置開始讀取
    Handler_read_key + 1 : 根據第一個位置的KEY讀1行，其他9行是根據葉節點的連結串列依次讀取
    Handler_read_rnd_next +10 : 從主鍵的葉節點(行資料)中順序讀取10行

> 索引掃描
    EXPLAIN SELECT * FROM `t_feed_info` AS i ORDER BY feed_id LIMIT 0, 10;

點選(此處)摺疊或開啟

  1. id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
  2. 1    SIMPLE    i    index    \\N    PRIMARY    8    \\N    10
    Handler_read_first + 1 : 從(主)鍵的第一個位置開始讀取
    Handler_read_key + 1 : 根據第一個位置的KEY讀1行
    Handler_read_next + 9 : 按(主)鍵順序依次讀取之後的9行


    EXPLAIN SELECT * FROM `t_feed_info` AS i ORDER BY feed_id DESC LIMIT 0, 10;

點選(此處)摺疊或開啟

  1. id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
  2. 1    SIMPLE    i    index    \\N    PRIMARY    8    \\N    10
    Handler_read_key + 1 : 根據第一個位置的KEY讀1行
    Handler_read_last + 1 : 從(主)鍵的最後一個位置開始讀取
    Handler_read_prev + 9 : 按(主)鍵順序依次讀取之前的9行


> 索引覆蓋掃描
    EXPLAIN SELECT feed_id FROM `t_feed_info` AS i ORDER BY feed_id LIMIT 0, 10;

點選(此處)摺疊或開啟

  1. id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
  2. 1    SIMPLE    i    index    \\N    PRIMARY    8    \\N    10    Using index
    Handler_read_first + 1 : 從(主)鍵的第一個位置開始讀取
    Handler_read_key + 1 : 根據第一個位置的KEY讀1行
    Handler_read_next + 9 : 按(主)鍵順序依次讀取之後的9行

    EXPLAIN SELECT feed_id FROM `t_feed_info` AS i ORDER BY feed_id DESC LIMIT 0, 10;

點選(此處)摺疊或開啟

  1. id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
  2. 1    SIMPLE    i    index    \\N    PRIMARY    8    \\N    10    Using index
    Handler_read_key + 1 : 根據第一個位置的KEY讀1行
    Handler_read_last + 1 : 從(主)鍵的最後一個位置開始讀取
    Handler_read_prev + 9 : 按(主)鍵倒序依次讀取之後的9行


############### join
# set @uid := '20000001';
> WHERE + 排序
    # asc
    EXPLAIN SELECT * FROM `t_feed_idx` AS i
    LEFT JOIN `t_feed_info` AS f ON f.`feed_id`=i.`feed_id`
    WHERE i.user_id=@uid ORDER BY i.feed_id LIMIT 0, 10;

點選(此處)摺疊或開啟

  1. id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
  2. 1    SIMPLE    i    ref    user_id    user_id    8    const    200    Using where; Using index
  3. 1    SIMPLE    f    eq_ref    PRIMARY    PRIMARY    8    z3.i.feed_id    1
    Handler_read_key + 11 : t_feed_idx 根據user_id讀1次，t_feed_info根據feed_id讀10次
    Handler_read_next + 9 : t_feed_idx按鍵順序依次向後讀取9個feed_id

    # desc
    EXPLAIN SELECT * FROM `t_feed_idx` AS i
    LEFT JOIN `t_feed_info` AS f ON f.`feed_id`=i.`feed_id`
    WHERE i.user_id=@uid ORDER BY i.feed_id desc LIMIT 0, 10;

點選(此處)摺疊或開啟

  1. id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
  2. 1    SIMPLE    i    ref    user_id    user_id    8    const    200    Using where; Using index
  3. 1    SIMPLE    f    eq_ref    PRIMARY    PRIMARY    8    z3.i.feed_id    1
    Handler_read_key + 11 : t_feed_idx 根據user_id讀1次，t_feed_info根據feed_id讀10次
    Handler_read_prev + 9 : t_feed_idx按鍵倒序依次向前讀取9個feed_id

# 不好的查詢方式: 
> 使用被關聯表t_feed_info的feed_id列進行排序
    EXPLAIN SELECT * FROM `t_feed_idx` AS i
    LEFT JOIN `t_feed_info` AS f ON f.`feed_id`=i.`feed_id`
    WHERE i.user_id=@uid ORDER BY f.feed_id LIMIT 0, 10;  

點選(此處)摺疊或開啟

  1. id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
  2. 1    SIMPLE    i    ref    user_id    user_id    8    const    200    Using index; Using temporary; Using filesort
  3. 1    SIMPLE    f    eq_ref    PRIMARY    PRIMARY    8    z3.i.feed_id    1
    Handler_read_key + 201 : t_feed_idx 根據user_id讀1次，t_feed_info根據feed_id讀200次
    Handler_read_next + 200 : t_feed_idx按鍵順序依次讀取199個feed_id. 額外的1次是？？
    Handler_read_rnd + 10 : filesort後每行位置都是固定的,limit 10取10行
    Handler_read_rnd_next + 201: filesort全表遍歷讀取temporary表中的200行，進行排序; 額外的1是EOF標誌位;

    Using temporary; Using filesort 原因: 無法使用t_feed_idx表的索引
            1>先查詢表t_feed_idx中滿足@uid的200行與表t_feed_info進行join,將結果儲存在temporary表
                Handler_read_key + 201, Handler_read_next + 200
            2>然後對臨時表排序;
                Handler_read_rnd_next + 201
            3>取前10個。
                 Handler_read_rnd + 10

> where條件放在join中
    EXPLAIN SELECT * FROM `t_feed_idx` AS i
    LEFT JOIN `t_feed_info` AS f ON f.`feed_id`=i.`feed_id` AND i.user_id=@uid 
    ORDER BY i.`feed_id` DESC LIMIT 0, 10;  

點選(此處)摺疊或開啟

  1. id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
  2. 1    SIMPLE    i    index    \\N    user_id    16    \\N    100396    Using index; Using filesort
  3. 1    SIMPLE    f    eq_ref    PRIMARY    PRIMARY    8    z3.i.feed_id    1
    Handler_read_first + 1 : 從表t_feed_idx 中user_id索引開始位置進行讀取
    Handler_read_key + 11 : t_feed_idx 根據user_id讀1次，t_feed_info根據feed_id讀10次
    Handler_read_rnd_next + 100001: filesort全表遍歷讀取表t_feed_idx 中user_id索引的100000行，進行排序; 額外的1是EOF標誌位;

    沒有 Using temporary 是因為先對t_feed_idx的索引user_id進行排序，然後再join
    Using filesort 原因:
            無法使用t_feed_idx表的索引
            1>先對錶t_feed_idx排序，取10個feed_id
                Handler_read_first + 1, Handler_read_key + 1, Handler_read_rnd_next + 100001
            2>然後根據feed_id與表t_feed_info進行join
                Handler_read_key + 10


參考: http://www.mysqlperformanceblog.com/2010/06/15/what-does-handler_read_rnd-mean/


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26250550/viewspace-1076292/，如需轉載，請註明出處，否則將追究法律責任。

相關文章