mysql:handler_read_% parameter Description

http://blog.itpub.net/26250550/viewspace-1076292/Environment:

table T_feed_idx (user_id bigint, feed_id bigint, KEY (' user_id ', ' feed_id ')) Engine=innodb;
Table T_feed_info (feed_id bigint, PRIMARY KEY (' feed_id '), ' other columns ') engine=innodb;feed total: 100000 users (id:20000001) Number of feeds: 200

Inquire

######### Single Table
> Full Scan
EXPLAIN SELECT * from ' t_feed_info ' as I ORDER by NULL LIMIT 0, 10;

Click ( here) to collapse or open

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: Start reading from the first position of the (primary) key
Handler_read_key + 1: Reads 1 rows according to key in the first position, and the other 9 rows are read sequentially according to the list of leaf nodes
Handler_read_rnd_next +10: Sequentially reads 10 rows from the leaf node (row data) of the primary key

> Index Scan
EXPLAIN SELECT * from ' t_feed_info ' as I ORDER by feed_id LIMIT 0, 10;

Click ( here) to collapse or open

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: Start reading from the first position of the (primary) key
Handler_read_key + 1: Read 1 lines based on key in the first position
Handler_read_next + 9:9 rows after reading by (primary) key order


EXPLAIN SELECT * from ' t_feed_info ' as I ORDER by feed_id DESC LIMIT 0, 10;

Click ( here) to collapse or open

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: Read 1 lines based on key in the first position
Handler_read_last + 1: Start reading from the last position of the (primary) key
Handler_read_prev + 9: Read the previous 9 lines in the (primary) key order


> Index Overlay Scan
EXPLAIN SELECT feed_id from ' t_feed_info ' as I ORDER by feed_id LIMIT 0, 10;

Click ( here) to collapse or open

1. ID select_type table Type possible_keys key Key_len ref rows Extra
2. 1 Simple I index \\n PRIMARY 8 \\n Using Index

Handler_read_first + 1: Start reading from the first position of the (primary) key
Handler_read_key + 1: Read 1 lines based on key in the first position
Handler_read_next + 9:9 rows after reading by (primary) key order

EXPLAIN SELECT feed_id from ' t_feed_info ' as I ORDER by feed_id DESC LIMIT 0, 10;

Click ( here) to collapse or open

1. ID select_type table Type possible_keys key Key_len ref rows Extra
2. 1 Simple I index \\n PRIMARY 8 \\n Using Index

Handler_read_key + 1: Read 1 lines based on key in the first position
Handler_read_last + 1: Start reading from the last position of the (primary) key
Handler_read_prev + 9:9 rows After reading in reverse order (primary) key


############### Join
# set @uid: = ' 20000001 ';
> WHERE + Sort
# ASC
EXPLAIN SELECT * from ' t_feed_idx ' as I left joins ' T_feed_info ' as F on f. ' feed_id ' =i. ' feed_id '
WHERE [email protected] ORDER by i.feed_id LIMIT 0, 10;

Click ( here) to collapse or open

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-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 read 1 times according to USER_ID, T_feed_info read 10 times according to FEED_ID
Handler_read_next + 9:T_FEED_IDX keys sequentially reads back 9 feed_id

# desc
EXPLAIN SELECT * from ' t_feed_idx ' as I left joins ' T_feed_info ' as F on f. ' feed_id ' =i. ' feed_id '
WHERE [email protected] ORDER by i.feed_id desc LIMIT 0, 10;

Click (here) to collapse or open

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-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 read 1 times according to USER_ID, T_feed_info read 10 times according to FEED_ID
Handler_read_prev + 9:T_FEED_IDX keys sequentially reads 9 feed_id in reverse order

# Bad Query method:
> Sort using feed_id columns of the associated table T_feed_info EXPLAIN SELECT * from ' t_feed_idx ' as I left joins ' T_feed_info ' as F on f. ' Feed_ Id ' =i. ' feed_id '
WHERE [email protected] ORDER by f.feed_id LIMIT 0, 10;

Click ( here) to collapse or open

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-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 read 1 times according to USER_ID, T_feed_info read 200 times according to FEED_ID
Handler_read_next + 200:T_FEED_IDX keys sequentially reads 199 feed_id. What's the extra 1 times??
Handler_read_rnd + 10:filesort Each row position is fixed, limit 10 takes 10 rows
Handler_read_rnd_next + 201:filesort Full table traversal reads 200 rows from the temporary table and sorts them; The additional 1 is the EOF flag bit;

Using temporary; Using Filesort reason: cannot use index of T_FEED_IDX table
1> query Table T_FEED_IDX 200 rows that meet @uid and join the table T_feed_info, save the results in the temporary table
Handler_read_key + 201, Handler_read_next + 200
2> then sorts the temporary table;
Handler_read_rnd_next + 201
3> take the first 10.
Handler_read_rnd + 10
> Where conditions are placed in join EXPLAIN SELECT * from ' t_feed_idx ' as I
Left JOIN ' T_feed_info ' as F on f. ' feed_id ' =i. ' feed_id ' and [email protected]
ORDER by I. ' feed_id ' DESC LIMIT 0, 10;

Click ( here) to collapse or open

1. ID select_type table Type possible_keys key Key_len ref rows Extra
2. 1 Simple I index \\n user_id \\n 100396 Using Index; Using Filesort
3. 1 simple F Eq_ref PRIMARY PRIMARY 8 z3.i.feed_id 1

Handler_read_first + 1: Read from USER_ID index starting position in table T_feed_idx
Handler_read_key + 11:T_FEED_IDX read 1 times according to USER_ID, T_feed_info read 10 times according to FEED_ID
Handler_read_rnd_next + 100001:filesort full table traversal reads 100000 rows of the USER_ID index in the table t_feed_idx, sorting; The additional 1 is the EOF flag bit;

There is no Using temporary because the T_FEED_IDX index user_id is sorted first, and then the join
Using filesort Reason:
Cannot use index of T_FEED_IDX table
1> First sort the table T_feed_idx, take 10 feed_id
Handler_read_first + 1, Handler_read_key + 1, Handler_read_rnd_next + 100001
2> then joins the table T_feed_info according to the feed_id
Handler_read_key + 10


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

mysql:handler_read_% parameter Description