MySQL:Innodb Handler_read_*变量解释

青旅半醒 2023-05-31 07:41 14阅读 0赞

**导读**：

> 作者：高鹏（网名八怪），《深入理解MySQL主从原理32讲》系列文的作者
> 
> 本文为源码版本 **Percona 5.7.14**，水平有限如果有误，请谅解

**想阅读八怪源码文章欢迎订阅**

![640?wx\_fmt=png][640_wx_fmt_png]

**本文建议横屏观看，效果更佳**

--------------------

###     ###

### 一、Handler\_read\_\*值的实质    ###

内部表示如下：

{"Handler_read_first",       (char*) offsetof(STATUS_VAR, ha_read_first_count),     SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
    {"Handler_read_key",         (char*) offsetof(STATUS_VAR, ha_read_key_count),       SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
    {"Handler_read_last",        (char*) offsetof(STATUS_VAR, ha_read_last_count),      SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
    {"Handler_read_next",        (char*) offsetof(STATUS_VAR, ha_read_next_count),      SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
    {"Handler_read_prev",        (char*) offsetof(STATUS_VAR, ha_read_prev_count),      SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
    {"Handler_read_rnd",         (char*) offsetof(STATUS_VAR, ha_read_rnd_count),       SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
    {"Handler_read_rnd_next",    (char*) offsetof(STATUS_VAR, ha_read_rnd_next_count),  SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},

实际上这些变量都是MySQL层定义出来的，因为MySQL可以包含多个存储引擎。因此这些值如何增加需要在引擎层的接口中自行实现，也就是说各个引擎都有自己的实现，在MySQL层进行汇总，因此这些值不是某个引擎特有的，打个比方如果有Innodb和MyISAM引擎，那么这些值是两个引擎的总和。本文将以Innodb为主要学习对象进行解释。

### 二、各个值的解释 ###

##### 1、Handler\_read\_key #####

*  内部表示：ha\_read\_key\_count
 *  Innodb更改接口：ha\_innobase::index\_read
 *  文档解释：The number of requests to read a row based on a key. If this value is high, it is a good indication that your tables are properly indexed for your queries.
 *  源码函数解释：Positions an index cursor to the index specified in the handle. Fetches the row if any.
 *  作者解释：这个函数是访问索引的时候定位到值所在的位置用到的函数，因为必须要知道读取索引的开始位置才能向下访问。

#####     #####

##### 2、Handler\_read\_next #####

*  内部表示：ha\_read\_next\_count
 *  Innodb更改接口：ha\_innobase::index\_next\_same ha\_innobase::index\_next
 *  文档解释：The number of requests to read the next row in key order. This value is incremented if you are
 *  源码函数解释：
 *  作者解释：访问索引的下一条数据封装的ha\_innobase::general\_fetch函数，index\_next\_same和index\_next不同在于访问的方式不一样，比如范围range查询需要用到和索引全扫描也会用到index\_next，而ref访问方式会使用index\_next\_same

##### 3、Handler\_read\_first #####

*  内部表示：ha\_read\_first\_count
 *  Innodb更改接口：ha\_innobase::index\_first
 *  文档解释：The number of times the first entry in an index was read. If this value is high, it suggests that the
 *  源码函数解释：Positions a cursor on the first record in an index and reads the corresponding row to buf.
 *  作者解释：定位索引的第一条数据，实际上也是封装的ha\_innobase::index\_read 函数(如全表扫描/全索引扫描调用)

#####     #####

##### 4、Handler\_read\_rnd\_next #####

*  内部表示：ha\_read\_rnd\_next\_count
 *  Innodb更改接口：ha\_innobase::rnd\_next
 *  文档解释：The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries
 *  源码函数解释：Reads the next row in a table scan (also used to read the FIRST row in a table scan).
 *  作者解释：全表扫描访问下一条数据，实际上也是封装的ha\_innobase::general\_fetch，在访问之前会调用ha\_innobase::index\_first

#####     #####

##### 5、Handler\_read\_rnd #####

*  内部表示：ha\_read\_rnd\_count
 *  Innodb更改接口：ha\_innobase::rnd\_pos
 *  Memory更改接口：ha\_heap::rnd\_pos
 *  文档解释：The number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that do not use keys properly.
 *  作者解释：这个状态值在我测试期间只发现对临时表做排序的时候会用到，而且是Memory引擎的，具体只能按照文档理解了。

#####     #####

##### 6、其他 #####

最后2个简单说一下

*  **Handler\_read\_prev**ha\_innobase::general\_fetch函数，用于ORDER BY DESC 索引扫描避免排序，内部状态值ha\_read\_prev\_count增加。
 *  **Handler\_read\_last**ha\_innobase::index\_read函数，用于ORDER BY DESC 索引扫描避免排序，内部状态值ha\_read\_last\_count增加。

###     ###

### 三、常用查询测试 ###

##### 1、测试用例 #####

mysql> show create table z1;
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                              |
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------+
    | z1    | CREATE TABLE `z1` (
      `a` int(11) DEFAULT NULL,
      `name` varchar(20) DEFAULT NULL,
      KEY `a` (`a`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> show create table z10;
    +-------+------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                                   |
    +-------+------------------------------------------------------------------------------------------------------------------------------------------------+
    | z10   | CREATE TABLE `z10` (
      `a` int(11) DEFAULT NULL,
      `name` varchar(20) DEFAULT NULL,
      KEY `a_idx` (`a`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
    +-------+------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    mysql> select count(*) from z1;
    +----------+
    | count(*) |
    +----------+
    |    56415 |
    +----------+
    1 row in set (5.27 sec)
    
    mysql> select count(*) from z10;
    +----------+
    | count(*) |
    +----------+
    |       10 |
    +----------+
    1 row in set (0.00 sec)

##### 2、全表扫描 #####

Handler\_read\_first增加1次用于初次定位，Handler\_read\_key增加1次，Handler\_read\_rnd\_next增加扫描行数。我们前面说过因为ha\_innobase::index\_first也是封装的ha\_innobase::index\_read因此都需要+1。

##### 3、全索引扫描 #####

Handler\_read\_first增加1次用于初次定位，Handler\_read\_key增加1次，Handler\_read\_next增加扫描行数用于连续访问接下来的行。我们前面说过因为ha\_innobase::index\_first也是封装的ha\_innobase::index\_read因此都需要+1。

##### 4、索引ref访问 #####

我这里因为是测试索引全是等于10的加上了force index

mysql>  desc select  * from z1 force index(a) where a=10;
    +----+-------------+-------+------------+------+---------------+------+---------+-------+-------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows  | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+------+---------+-------+-------+----------+-------+
    |  1 | SIMPLE      | z1    | NULL       | ref  | a             | a    | 5       | const | 28325 |   100.00 | NULL  |
    +----+-------------+-------+------------+------+---------------+------+---------+-------+-------+----------+-------+
    1 row in set, 1 warning (0.01 sec)
    
    mysql> flush status;
    Query OK, 0 rows affected (0.13 sec)
    
    mysql> pager cat >>/dev/null
    PAGER set to 'cat >>/dev/null'
    mysql> select  * from z1 force index(a) where a=10;
    56414 rows in set (32.39 sec)
    mysql> pager
    Default pager wasn't set, using stdout.
    mysql> show status like 'Handler_read%';
    +-----------------------+-------+
    | Variable_name         | Value |
    +-----------------------+-------+
    | Handler_read_first    | 0     |
    | Handler_read_key      | 1     |
    | Handler_read_last     | 0     |
    | Handler_read_next     | 56414 |
    | Handler_read_prev     | 0     |
    | Handler_read_rnd      | 0     |
    | Handler_read_rnd_next | 0     |
    +-----------------------+-------+
    7 rows in set (0.06 sec)

Handler\_read\_key增加1次这是用于初次定位，Handler\_read\_next增加扫描行数次数用于接下来的数据访问。

##### 5、索引range访问 #####

mysql> desc select  * from z1 force index(a) where a>9 and a<12;
    +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
    | id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                 |
    +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
    |  1 | SIMPLE      | z1    | NULL       | range | a             | a    | 5       | NULL | 28325 |   100.00 | Using index condition |
    +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql>  pager cat >>/dev/null
    PAGER set to 'cat >>/dev/null'
    mysql> select  * from z1 force index(a) where a>9 and a<12;
    56414 rows in set (47.54 sec)
    mysql> show status like 'Handler_read%';
    7 rows in set (0.03 sec)
    mysql>  pager
    Default pager wasn't set, using stdout.
    mysql> show status like 'Handler_read%';
    +-----------------------+-------+
    | Variable_name         | Value |
    +-----------------------+-------+
    | Handler_read_first    | 0     |
    | Handler_read_key      | 1     |
    | Handler_read_last     | 0     |
    | Handler_read_next     | 56414 |
    | Handler_read_prev     | 0     |
    | Handler_read_rnd      | 0     |
    | Handler_read_rnd_next | 0     |
    +-----------------------+-------+
    7 rows in set (0.02 sec)

Handler\_read\_key增加1次这是用于初次定位，Handler\_read\_next增加扫描行数次数用于接下来的数据访问。

##### 6、被驱动表带索引访问 #####

mysql> desc select * from z1 STRAIGHT_JOIN z10 force index(a_idx) on z1.a=z10.a;
    +----+-------------+-------+------------+------+---------------+-------+---------+-----------+-------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key   | key_len | ref       | rows  | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+-------+---------+-----------+-------+----------+-------------+
    |  1 | SIMPLE      | z1    | NULL       | ALL  | a             | NULL  | NULL    | NULL      | 56650 |   100.00 | Using where |
    |  1 | SIMPLE      | z10   | NULL       | ref  | a_idx         | a_idx | 5       | test.z1.a |    10 |   100.00 | NULL        |
    +----+-------------+-------+------------+------+---------------+-------+---------+-----------+-------+----------+-------------+
    2 rows in set, 1 warning (0.01 sec)
    
    mysql> flush status;
    Query OK, 0 rows affected (0.47 sec)
    
    mysql> pager cat >> /dev/null
    PAGER set to 'cat >> /dev/null'
    mysql>  select * from z1 STRAIGHT_JOIN z10 force index(a_idx) on z1.a=z10.a;
    112828 rows in set (1 min 21.21 sec)
    
    mysql> pager
    Default pager wasn't set, using stdout.
    mysql>  show status like 'Handler_read%';
    +-----------------------+--------+
    | Variable_name         | Value  |
    +-----------------------+--------+
    | Handler_read_first    | 1      |
    | Handler_read_key      | 56416  |
    | Handler_read_last     | 0      |
    | Handler_read_next     | 112828 |
    | Handler_read_prev     | 0      |
    | Handler_read_rnd      | 0      |
    | Handler_read_rnd_next | 56416  |
    +-----------------------+--------+
    7 rows in set (0.00 sec)

Handler\_read\_first 增加一次作为驱动表z1全表扫描定位的开始，接下来Handler\_read\_rnd\_next扫描全部记录，每次扫描一次在z10表通过索引a\_idx定位一次Handler\_read\_key增加1次，然后接下来进行索引a\_idx进行数据查找Handler\_read\_next增加为扫描的行数。

##### 7、索引避免排序正向和反向 #####

mysql>  flush status;
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> pager cat >> /dev/null
    PAGER set to 'cat >> /dev/null'
    mysql> select * from z1 force index(a) order by a;
    56415 rows in set (27.39 sec)
    
    mysql> pager
    Default pager wasn't set, using stdout.
    mysql> show status like 'Handler_read%';
    +-----------------------+-------+
    | Variable_name         | Value |
    +-----------------------+-------+
    | Handler_read_first    | 1     |
    | Handler_read_key      | 1     |
    | Handler_read_last     | 0     |
    | Handler_read_next     | 56415 |
    | Handler_read_prev     | 0     |
    | Handler_read_rnd      | 0     |
    | Handler_read_rnd_next | 0     |
    +-----------------------+-------+
    7 rows in set (0.01 sec)
    
    mysql> flush status;
    Query OK, 0 rows affected (0.10 sec)
    
    mysql> desc  select * from z1 force index(a) order by a desc;
    +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-------+
    | id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
    +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-------+
    |  1 | SIMPLE      | z1    | NULL       | index | NULL          | a    | 5       | NULL | 56650 |   100.00 | NULL  |
    +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> pager cat >> /dev/null
    PAGER set to 'cat >> /dev/null'
    mysql>  select * from z1 force index(a) order by a desc;
    56415 rows in set (24.94 sec)
    
    mysql> pager
    Default pager wasn't set, using stdout.
    mysql>  show status like 'Handler_read%';
    +-----------------------+-------+
    | Variable_name         | Value |
    +-----------------------+-------+
    | Handler_read_first    | 0     |
    | Handler_read_key      | 1     |
    | Handler_read_last     | 1     |
    | Handler_read_next     | 0     |
    | Handler_read_prev     | 56415 |
    | Handler_read_rnd      | 0     |
    | Handler_read_rnd_next | 0     |
    +-----------------------+-------+
    7 rows in set (0.01 sec)

不用过多解释，可以看到Handler\_read\_last 和Handler\_read\_prev的用途。

### 四、总结 ###

*  Handler\_read\_rnd\_next 通常代表着全表扫描。
 *  Handler\_read\_first 通常代表着全表或者全索引扫描。
 *  Handler\_read\_next 通常代表着合理的使用了索引或者全索引扫描。
 *  Handler\_read\_key 不管全表全索引或者正确使用的索引实际上都会增加，只是一次索引定位而已。
 *  Innodb中全表扫描也是主键的全索引扫描。
 *  顺序访问的一条记录实际上都是调用ha\_innobase::general\_fetch函数，另外一个功能innodb\_thread\_concurrency参数的功能就在里面实现，下次在说。

###     ###

### 五、参考栈帧 ###

全表扫描	
    mysql> desc  select * from z1 ;	
    +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+	
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |	
    +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+	
    |  1 | SIMPLE      | z1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 56650 |   100.00 | NULL  |	
    +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+	
    1 row in set, 1 warning (0.00 sec)	
    第一次:	
    #0  row_search_mvcc (buf=0x7fff2ccc9380 "\377", mode=PAGE_CUR_G, prebuilt=0x7fff2cd4bb40, match_mode=0, direction=0)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/row/row0sel.cc:4479	
    #1  0x00000000019b3051 in ha_innobase::index_read (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\377", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9104	
    #2  0x00000000019b4374 in ha_innobase::index_first (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\377")	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9551	
    #3  0x00000000019b462c in ha_innobase::rnd_next (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\377")	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9656	
    #4  0x0000000000f66fa2 in handler::ha_rnd_next (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\377") at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:3099	
    #5  0x00000000014c61b6 in rr_sequential (info=0x7fff2c0026a0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/records.cc:520	
    #6  0x000000000155f2a4 in join_init_read_record (tab=0x7fff2c002650) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:2481	
    #7  0x000000000155c381 in sub_select (join=0x7fff2c001f70, qep_tab=0x7fff2c002650, end_of_records=false)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1271	
    #8  0x000000000155bd06 in do_select (join=0x7fff2c001f70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:944	
    #9  0x0000000001559bdc in JOIN::exec (this=0x7fff2c001f70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:199	
    随后访问:	
    #0  row_search_mvcc (buf=0x7fff2ccc9380 "\376\n", mode=PAGE_CUR_UNSUPP, prebuilt=0x7fff2cd4bb40, match_mode=0, direction=1)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/row/row0sel.cc:4479	
    #1  0x00000000019b3f50 in ha_innobase::general_fetch (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n", direction=1, match_mode=0)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9425	
    #2  0x00000000019b4666 in ha_innobase::rnd_next (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n")	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9664	
    #3  0x0000000000f67026 in handler::ha_rnd_next (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n") at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:3099	
    #4  0x00000000014c61b6 in rr_sequential (info=0x7fff2c0026a0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/records.cc:520	
    #5  0x000000000155c397 in sub_select (join=0x7fff2c001f70, qep_tab=0x7fff2c002650, end_of_records=false)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1274	
    #6  0x000000000155bd06 in do_select (join=0x7fff2c001f70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:944	
    #7  0x0000000001559bdc in JOIN::exec (this=0x7fff2c001f70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:199	
    全索引扫描	
    mysql> desc select a from z1 ;	
    +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-------------+	
    | id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |	
    +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-------------+	
    |  1 | SIMPLE      | z1    | NULL       | index | NULL          | a    | 5       | NULL | 56650 |   100.00 | Using index |	
    +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-------------+	
    1 row in set, 1 warning (0.00 sec)	
    第一次:	
    #0  row_search_mvcc (buf=0x7fff2ccc9380 "\377", mode=PAGE_CUR_G, prebuilt=0x7fff2cd4bb40, match_mode=0, direction=0)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/row/row0sel.cc:4479	
    #1  0x00000000019b3051 in ha_innobase::index_read (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\377", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9104	
    #2  0x00000000019b4374 in ha_innobase::index_first (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\377")	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9551	
    #3  0x0000000000f686f4 in handler::ha_index_first (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\377") at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:3337	
    #4  0x000000000155f997 in join_read_first (tab=0x7fff2c002578) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:2646	
    #5  0x000000000155c381 in sub_select (join=0x7fff2c001e98, qep_tab=0x7fff2c002578, end_of_records=false)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1271	
    #6  0x000000000155bd06 in do_select (join=0x7fff2c001e98) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:944	
    #7  0x0000000001559bdc in JOIN::exec (this=0x7fff2c001e98) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:199	
    随后访问:	
    #0  row_search_mvcc (buf=0x7fff2ccc9380 "\376\n", mode=PAGE_CUR_UNSUPP, prebuilt=0x7fff2cd4bb40, match_mode=0, direction=1)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/row/row0sel.cc:4479	
    #1  0x00000000019b3f50 in ha_innobase::general_fetch (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n", direction=1, match_mode=0)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9425	
    #2  0x00000000019b4261 in ha_innobase::index_next (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n")	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9503	
    #3  0x0000000000f680d8 in handler::ha_index_next (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n") at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:3269	
    #4  0x000000000155fa02 in join_read_next (info=0x7fff2c0025c8) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:2660	
    #5  0x000000000155c397 in sub_select (join=0x7fff2c001e98, qep_tab=0x7fff2c002578, end_of_records=false)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1274	
    #6  0x000000000155bd06 in do_select (join=0x7fff2c001e98) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:944	
    #7  0x0000000001559bdc in JOIN::exec (this=0x7fff2c001e98) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:199	
    索引ref const	
    mysql> desc  select * from z1 where a=10;	
    +----+-------------+-------+------------+------+---------------+------+---------+-------+-------+----------+-------+	
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows  | filtered | Extra |	
    +----+-------------+-------+------------+------+---------------+------+---------+-------+-------+----------+-------+	
    |  1 | SIMPLE      | z1    | NULL       | ref  | a             | a    | 5       | const | 28325 |   100.00 | NULL  |	
    +----+-------------+-------+------------+------+---------------+------+---------+-------+-------+----------+-------+	
    1 row in set, 1 warning (0.00 sec)	
    初次访问：	
    #0  row_search_mvcc (buf=0x7fff2ccc9380 "\376\n", mode=PAGE_CUR_GE, prebuilt=0x7fff2cd4bb40, match_mode=1, direction=0)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/row/row0sel.cc:4479	
    #1  0x00000000019b3051 in ha_innobase::index_read (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n", key_ptr=0x7fff2cddf0d0 "", key_len=5, find_flag=HA_READ_KEY_EXACT)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9104	
    #2  0x0000000000f75dc4 in handler::index_read_map (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n", key=0x7fff2cddf0d0 "", keypart_map=1, find_flag=HA_READ_KEY_EXACT)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.h:2843	
    #3  0x0000000000f675fa in handler::ha_index_read_map (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n", key=0x7fff2cddf0d0 "", keypart_map=1, 	
        find_flag=HA_READ_KEY_EXACT) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:3183	
    #4  0x000000000155e9ab in join_read_always_key (tab=0x7fff2cddf1d0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:2260	
    #5  0x000000000155c381 in sub_select (join=0x7fff2c002528, qep_tab=0x7fff2cddf1d0, end_of_records=false)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1271	
    #6  0x000000000155bd06 in do_select (join=0x7fff2c002528) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:944	
    #7  0x0000000001559bdc in JOIN::exec (this=0x7fff2c002528) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:199	
    随后访问	
    #0  row_search_mvcc (buf=0x7fff2ccc9380 "\376\n", mode=PAGE_CUR_UNSUPP, prebuilt=0x7fff2cd4bb40, match_mode=1, direction=1)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/row/row0sel.cc:4479	
    #1  0x00000000019b3f50 in ha_innobase::general_fetch (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n", direction=1, match_mode=1)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9425	
    #2  0x00000000019b42b5 in ha_innobase::index_next_same (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n", key=0x7fff2cddf0d0 "", keylen=5)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9519	
    #3  0x0000000000f68e37 in handler::ha_index_next_same (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n", key=0x7fff2cddf0d0 "", keylen=5)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:3407	
    #4  0x000000000155ebd4 in join_read_next_same (info=0x7fff2cddf220) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:2319	
    #5  0x000000000155c397 in sub_select (join=0x7fff2c002528, qep_tab=0x7fff2cddf1d0, end_of_records=false)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1274	
    #6  0x000000000155bd06 in do_select (join=0x7fff2c002528) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:944	
    #7  0x0000000001559bdc in JOIN::exec (this=0x7fff2c002528) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:199	
    索引 range	
    mysql> desc select * from z1 force index(a) where a>=10 and a<12;	
    +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+	
    | id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                 |	
    +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+	
    |  1 | SIMPLE      | z1    | NULL       | range | a             | a    | 5       | NULL | 28325 |   100.00 | Using index condition |	
    +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+	
    1 row in set, 1 warning (0.03 sec)	
    初次访问：	
    0  row_search_mvcc (buf=0x7fff2ccc9380 "\377", mode=PAGE_CUR_GE, prebuilt=0x7fff2cd4bb40, match_mode=0, direction=0)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/row/row0sel.cc:4479	
    #1  0x00000000019b3051 in ha_innobase::index_read (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\377", key_ptr=0x7fff2ce21bc0 "", key_len=5, find_flag=HA_READ_KEY_OR_NEXT)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9104	
    #2  0x0000000000f75dc4 in handler::index_read_map (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\377", key=0x7fff2ce21bc0 "", keypart_map=1, find_flag=HA_READ_KEY_OR_NEXT)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.h:2843	
    #3  0x0000000000f675fa in handler::ha_index_read_map (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\377", key=0x7fff2ce21bc0 "", keypart_map=1, 	
        find_flag=HA_READ_KEY_OR_NEXT) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:3183	
    #4  0x0000000000f7186e in handler::read_range_first (this=0x7fff2cd32480, start_key=0x7fff2cd32568, end_key=0x7fff2cd32588, eq_range_arg=false, sorted=false)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:7565	
    #5  0x0000000000f6f7cd in handler::multi_range_read_next (this=0x7fff2cd32480, range_info=0x7ffff03598c0)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:6635	
    #6  0x0000000000f706b7 in DsMrr_impl::dsmrr_next (this=0x7fff2cd328f0, range_info=0x7ffff03598c0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:7019	
    #7  0x00000000019c6210 in ha_innobase::multi_range_read_next (this=0x7fff2cd32480, range_info=0x7ffff03598c0)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:21372	
    #8  0x00000000017904b2 in QUICK_RANGE_SELECT::get_next (this=0x7fff2ccc9760) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/opt_range.cc:11171	
    #9  0x00000000014c5f56 in rr_quick (info=0x7fff2cddf050) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/records.cc:398	
    #10 0x000000000155f2a4 in join_init_read_record (tab=0x7fff2cddf000) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:2481	
    #11 0x000000000155c381 in sub_select (join=0x7fff2cdde4e0, qep_tab=0x7fff2cddf000, end_of_records=false)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1271	
    #12 0x000000000155bd06 in do_select (join=0x7fff2cdde4e0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:944	
    #13 0x0000000001559bdc in JOIN::exec (this=0x7fff2cdde4e0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:199	
    随后访问:	
    #0  row_search_mvcc (buf=0x7fff2ccc9380 "\376\n", mode=PAGE_CUR_UNSUPP, prebuilt=0x7fff2cd4bb40, match_mode=0, direction=1)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/row/row0sel.cc:4479	
    #1  0x00000000019b3f50 in ha_innobase::general_fetch (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n", direction=1, match_mode=0)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9425	
    #2  0x00000000019b4261 in ha_innobase::index_next (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n")	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9503	
    #3  0x0000000000f680d8 in handler::ha_index_next (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n") at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:3269	
    #4  0x0000000000f719af in handler::read_range_next (this=0x7fff2cd32480) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:7612	
    #5  0x0000000000f6f710 in handler::multi_range_read_next (this=0x7fff2cd32480, range_info=0x7ffff0359910)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:6614	
    #6  0x0000000000f706b7 in DsMrr_impl::dsmrr_next (this=0x7fff2cd328f0, range_info=0x7ffff0359910) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:7019	
    #7  0x00000000019c6210 in ha_innobase::multi_range_read_next (this=0x7fff2cd32480, range_info=0x7ffff0359910)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:21372	
    #8  0x00000000017904b2 in QUICK_RANGE_SELECT::get_next (this=0x7fff2ccc9760) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/opt_range.cc:11171	
    #9  0x00000000014c5f56 in rr_quick (info=0x7fff2cddf050) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/records.cc:398	
    #10 0x000000000155c397 in sub_select (join=0x7fff2cdde4e0, qep_tab=0x7fff2cddf000, end_of_records=false)	
        at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1274	
    #11 0x000000000155bd06 in do_select (join=0x7fff2cdde4e0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:944	
    #12 0x0000000001559bdc in JOIN::exec (this=0x7fff2cdde4e0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:199	
    断点：	
    row_search_mvcc

**识别下方二维码添加作者为好友**

![640?wx\_fmt=png][640_wx_fmt_png 1]

END

--------------------

**点击下图小程序订阅**

**《深入理解MySQL主从原理32讲》专栏**

**可了解更多八怪技术文章**

**![640?wx\_fmt=png][640_wx_fmt_png]**

***![640?wx\_fmt=png][640_wx_fmt_png 2]***

![640?wx\_fmt=gif][640_wx_fmt_gif]

扫码加入MySQL技术Q群

**（群号：****650149401）**

![640?wx\_fmt=jpeg][640_wx_fmt_jpeg]

[640_wx_fmt_png]: /images/20230531/36231f53d51b4eedb1cb1e47d2f66582.png
[640_wx_fmt_png 1]: /images/20230531/7eb64d3310534f84bd5b9833e0925c6b.png
[640_wx_fmt_png 2]: /images/20230531/d2cbb8a2eec546f59e6126e9348f2e59.png
[640_wx_fmt_gif]: /images/20230531/46d6fb431f3f46daa26689651c4241df.png
[640_wx_fmt_jpeg]: /images/20230531/11e5ae8d640743108e54121990efdb82.png