注册    登录
关注
Py学习  »  DATABASE

MySQL:Innodb Handler_read_*变量解释

老叶茶馆 • 4 年前 • 315 次点击  

导读：

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

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


一、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
    querying an index column with a range constraint or if you are doing an index scan.

  • 源码函数解释：
    index_next - Reads the next row from a cursor, which must have previously been positioned using index_read.
    index_next_same - Reads the next row matching to the key value given as the parameter.

  • 作者解释：访问索引的下一条数据封装的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
    server is doing a lot of full index scans; for example, SELECT col1 FROM foo, assuming that col1
    is indexed

  • 源码函数解释：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
    are not written to take advantage of the indexes you have.

  • 源码函数解释：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
    Innodb接口为 ha_innobase::index_prev 访问索引的上一条数据，实际上也是封装的ha_innobase::general_fetch函数，用于ORDER BY DESC 索引扫描避免排序，内部状态值ha_read_prev_count增加。

  • Handler_read_last
    Innodb接口为ha_innobase::index_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、全表扫描
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)

mysql> pager cat >>/dev/null
PAGER set to 'cat >>/dev/null'
mysql> flush status;
Query OK, 0 rows affected (0.10 sec)
mysql> select * from z1;
56415 rows in set (4.05 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     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 56416 |
+-----------------------+-------+
7 rows in set (0.01 sec)

Handler_read_first增加1次用于初次定位，Handler_read_key增加1次，Handler_read_rnd_next增加扫描行数。我们前面说过因为ha_innobase::index_first也是封装的ha_innobase::index_read因此都需要+1。

3、全索引扫描
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)

mysql> flush status;
Query OK, 0 rows affected (0.12 sec)

mysql> pager cat >>/dev/null
PAGER set to 'cat >>/dev/null'
mysql> select a from z1;
56415 rows in set (4.57 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)

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参数的功能就在里面实现，下次在说。


五、参考栈帧

  1. 全表扫描

  3. mysql> desc  select *


    
 from z1 ;
  4. +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
  5. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
  6. +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
  7. |  1 | SIMPLE      | z1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |


    
 56650 |   100.00 | NULL  |
  8. +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
  9. 1 row in set, 1 warning (0.00 sec)

  11. 第一次:
  12. #0  row_search_mvcc (buf=0x7fff2ccc9380 "\377", mode=PAGE_CUR_G, prebuilt=0x7fff2cd4bb40, match_mode=0, direction=0)
  13.     at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/row/row0sel.


    
cc:4479
  14. #1  0x00000000019b3051 in ha_innobase::index_read (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\377", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY)
  15.     at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9104
  16. #2  0x00000000019b4374 in ha_innobase::index_first (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\377")
  17.     at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/


    
handler/ha_innodb.cc:9551
  18. #3  0x00000000019b462c in ha_innobase::rnd_next (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\377")
  19.     at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9656
  20. #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
  21. #5  0x00000000014c61b6 in rr_sequential (info=0x7fff2c0026a0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/records.cc:520
  22. #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
  23. #7  0x000000000155c381 in sub_select (join=0x7fff2c001f70, qep_tab=0x7fff2c002650, end_of_records=false)
  24.     at 


    
/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1271
  25. #8  0x000000000155bd06 in do_select (join=0x7fff2c001f70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:944
  26. #9  0x0000000001559bdc in JOIN::exec (this=0x7fff2c001f70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:199


  29. 随后访问:
  30. #0  row_search_mvcc (buf=0x7fff2ccc9380 "\376\n", mode=PAGE_CUR_UNSUPP, prebuilt=0x7fff2cd4bb40, match_mode=0, direction=1)
  31.     at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/row/


    
row0sel.cc:4479
  32. #1  0x00000000019b3f50 in ha_innobase::general_fetch (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n", direction=1, match_mode=0)
  33.     at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9425
  34. #2  0x00000000019b4666 in ha_innobase::rnd_next (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n")
  35.     at /root/mysql5.7.14/percona-server-5.7.14-7/storage/


    
innobase/handler/ha_innodb.cc:9664
  36. #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
  37. #4  0x00000000014c61b6 in rr_sequential (info=0x7fff2c0026a0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/records.cc:520
  38. #5  0x000000000155c397 in sub_select (join=0x7fff2c001f70, qep_tab=0x7fff2c002650, end_of_records=false)
  39.     at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1274
  40. #6  0x000000000155bd06 in do_select (join=0x7fff2c001f70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:944
  41. #7  0x0000000001559bdc in JOIN::exec (this=0x7fff2c001f70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:199



  45. 全索引扫描



  49. 


    
mysql> desc select a from z1 ;
  50. +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-------------+
  51. | id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
  52. +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-------------+
  53. |  1 | SIMPLE      | z1    | NULL       | index | NULL          


    
| a    | 5       | NULL | 56650 |   100.00 | Using index |
  54. +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-------------+
  55. 1 row in set, 1 warning (0.00 sec)


  58. 第一次:
  59. #0  row_search_mvcc (buf=0x7fff2ccc9380 "\377", mode=PAGE_CUR_G, prebuilt=0x7fff2cd4bb40, match_mode=0, direction=0)
  60.     at /root/mysql5.7.14/percona-server-5.7.14


    
-7/storage/innobase/row/row0sel.cc:4479
  61. #1  0x00000000019b3051 in ha_innobase::index_read (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\377", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY)
  62.     at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9104
  63. #2  0x00000000019b4374 in ha_innobase::index_first (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\377")
  64.     at /root/mysql5.7.14/percona-server


    
-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9551
  65. #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
  66. #4  0x000000000155f997 in join_read_first (tab=0x7fff2c002578) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:2646
  67. #5  0x000000000155c381 in sub_select (join=0x7fff2c001e98, qep_tab=0x7fff2c002578, end_of_records=false)
  68.     at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1271
  69. #6  0x000000000155bd06 in do_select (join=0x7fff2c001e98) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:944
  70. 


    
#7  0x0000000001559bdc in JOIN::exec (this=0x7fff2c001e98) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:199

  72. 随后访问:
  73. #0  row_search_mvcc (buf=0x7fff2ccc9380 "\376\n", mode=PAGE_CUR_UNSUPP, prebuilt=0x7fff2cd4bb40, match_mode=0, direction=1)
  74.     at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/row/row0sel.cc:4479
  75. #1  0x00000000019b3f50 in ha_innobase::general_fetch (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n", direction=1, match_mode=0)
  76.     at /root/mysql5.7.14/percona-server-5.7.14-7/


    
storage/innobase/handler/ha_innodb.cc:9425
  77. #2  0x00000000019b4261 in ha_innobase::index_next (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n")
  78.     at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9503
  79. #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
  80. #4  0x000000000155fa02 in join_read_next (info=0x7fff2c0025c8) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:2660
  81. #5  0x000000000155c397 in sub_select (join=0x7fff2c001e98, qep_tab=0x7fff2c002578, end_of_records=false)
  82.     at /root


    
/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1274
  83. #6  0x000000000155bd06 in do_select (join=0x7fff2c001e98) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:944
  84. #7  0x0000000001559bdc in JOIN::exec (this=0x7fff2c001e98) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:199



  88. 索引ref const

  90. mysql> desc  select * from z1 where a=10;
  91. +----+-------------+-------+------------+------+---------------+------+---------+-------+-------+----------+-------+
  92. | id | select_type |


    
 table | partitions | type | possible_keys | key  | key_len | ref   | rows  | filtered | Extra |
  93. +----+-------------+-------+------------+------+---------------+------+---------+-------+-------+----------+-------+
  94. |  1 | SIMPLE      | z1    | NULL       | ref  | a             | a    | 5       | const | 28325 |   100.00 | NULL  |
  95. 


    
+----+-------------+-------+------------+------+---------------+------+---------+-------+-------+----------+-------+
  96. 1 row in set, 1 warning (0.00 sec)


  99. 初次访问：
  100. #0  row_search_mvcc (buf=0x7fff2ccc9380 "\376\n", mode=PAGE_CUR_GE, prebuilt=0x7fff2cd4bb40, match_mode=1, direction=0)
  101.     at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/row/row0sel.cc:4479
  102. #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)
  103. 


    
    at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9104
  104. #2  0x0000000000f75dc4 in handler::index_read_map (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n", key=0x7fff2cddf0d0 "", keypart_map=1, find_flag=HA_READ_KEY_EXACT)
  105.     at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.h:2843
  106. #3  0x0000000000f675fa in handler::ha_index_read_map (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n", key=0x7fff2cddf0d0 "", keypart_map=1, 
  107. 


    
    find_flag=HA_READ_KEY_EXACT) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:3183
  108. #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
  109. #5  0x000000000155c381 in sub_select (join=0x7fff2c002528, qep_tab=0x7fff2cddf1d0, end_of_records=false)
  110.     at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1271
  111. 


    
#6  0x000000000155bd06 in do_select (join=0x7fff2c002528) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:944
  112. #7  0x0000000001559bdc in JOIN::exec (this=0x7fff2c002528) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:199


  115. 随后访问
  116. #0  row_search_mvcc (buf=0x7fff2ccc9380 "\376\n", mode=PAGE_CUR_UNSUPP, prebuilt=0x7fff2cd4bb40, match_mode=1, direction=1)
  117.     at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/row/row0sel.cc:4479
  118. #1  0x00000000019b3f50 in ha_innobase::general_fetch (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n", direction=1, match_mode=1)
  119.     at /root/mysql5.7.14/percona-server


    
-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9425
  120. #2  0x00000000019b42b5 in ha_innobase::index_next_same (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n", key=0x7fff2cddf0d0 "", keylen=5)
  121.     at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9519
  122. #3  0x0000000000f68e37 in handler::ha_index_next_same (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n", key=0x7fff2cddf0d0 "", keylen=5)
  123.     at /root/mysql5.7.14


    
/percona-server-5.7.14-7/sql/handler.cc:3407
  124. #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
  125. #5  0x000000000155c397 in sub_select (join=0x7fff2c002528, qep_tab=0x7fff2cddf1d0, end_of_records=false)
  126.     at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1274
  127. #6  0x000000000155bd06 in do_select (join=0x7fff2c002528) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:944
  128. #7  0x0000000001559bdc in JOIN::exec (this=0x7fff2c002528) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:199




  133. 


    
索引 range

  135. mysql> desc select * from z1 force index(a) where a>=10 and a<12;
  136. +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
  137. | id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                 |
  138. 


    
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
  139. |  1 | SIMPLE      | z1    | NULL       | range | a             | a    | 5       | NULL | 28325 |   100.00 | Using index condition |
  140. +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
  141. 1 row in set, 1 warning (0.03 sec)

  143. 初次访问：
  144. 0  row_search_mvcc 


    
(buf=0x7fff2ccc9380 "\377", mode=PAGE_CUR_GE, prebuilt=0x7fff2cd4bb40, match_mode=0, direction=0)
  145.     at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/row/row0sel.cc:4479
  146. #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)
  147.     at /root/mysql5.


    
7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9104
  148. #2  0x0000000000f75dc4 in handler::index_read_map (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\377", key=0x7fff2ce21bc0 "", keypart_map=1, find_flag=HA_READ_KEY_OR_NEXT)
  149.     at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.h:2843
  150. #3  0x0000000000f675fa in handler::ha_index_read_map (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\377", key=0x7fff2ce21bc0 "", keypart_map=1, 
  151.     find_flag=HA_READ_KEY_OR_NEXT) at /


    
root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:3183
  152. #4  0x0000000000f7186e in handler::read_range_first (this=0x7fff2cd32480, start_key=0x7fff2cd32568, end_key=0x7fff2cd32588, eq_range_arg=false, sorted=false)
  153.     at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:7565
  154. #5  0x0000000000f6f7cd in handler::multi_range_read_next (this=0x7fff2cd32480, range_info=0x7ffff03598c0)
  155.     at /root/mysql5


    
.7.14/percona-server-5.7.14-7/sql/handler.cc:6635
  156. #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
  157. #7  0x00000000019c6210 in ha_innobase::multi_range_read_next (this=0x7fff2cd32480, range_info=0x7ffff03598c0)
  158.     at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:21372
  159. #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
  160. 


    
#9  0x00000000014c5f56 in rr_quick (info=0x7fff2cddf050) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/records.cc:398
  161. #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
  162. #11 0x000000000155c381 in sub_select (join=0x7fff2cdde4e0, qep_tab=0x7fff2cddf000, end_of_records=false)
  163.     at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1271
  164. #12 0x000000000155bd06 in do_select (join=0x7fff2cdde4e0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:944
  165. #13 0x0000000001559bdc in JOIN::exec (this=0x7fff2cdde4e0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:199

  167. 随后访问:

  169. #0  row_search_mvcc (buf=0x7fff2ccc9380 "\376\n", mode=PAGE_CUR_UNSUPP, prebuilt=0x7fff2cd4bb40, match_mode=0, direction=1)
  170.     at /root/mysql5.


    
7.14/percona-server-5.7.14-7/storage/innobase/row/row0sel.cc:4479
  171. #1  0x00000000019b3f50 in ha_innobase::general_fetch (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n", direction=1, match_mode=0)
  172.     at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9425
  173. #2  0x00000000019b4261 in ha_innobase::index_next (this=0x7fff2cd32480, buf=0x7fff2ccc9380 "\376\n")
  174.     at /


    
root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:9503
  175. #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
  176. #4  0x0000000000f719af in handler::read_range_next (this=0x7fff2cd32480) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:7612
  177. #5  0x0000000000f6f710 in handler::multi_range_read_next (this=0x7fff2cd32480, range_info=0x7ffff0359910)
  178.     at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:6614


    

  179. #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
  180. #7  0x00000000019c6210 in ha_innobase::multi_range_read_next (this=0x7fff2cd32480, range_info=0x7ffff0359910)
  181.     at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:21372
  182. #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
  183. #9  0x00000000014c5f56 in rr_quick (info=0x7fff2cddf050) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/records.cc:398
  184. #10 0x000000000155c397 in sub_select (join=0x7fff2cdde4e0, qep_tab=0x7fff2cddf000, end_of_records=false)
  185.     at /root/mysql5.7.14/


    
percona-server-5.7.14-7/sql/sql_executor.cc:1274
  186. #11 0x000000000155bd06 in do_select (join=0x7fff2cdde4e0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:944
  187. #12 0x0000000001559bdc in JOIN::exec (this=0x7fff2cdde4e0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:199


  190. 断点：
  191. row_search_mvcc


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

END


点击下图小程序订阅

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

可了解更多八怪技术文章





扫码加入MySQL技术Q群

（群号：650149401）

   


Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/48041
Reply
 
315 次点击  
登录后回复
关于   移动版   ·   三行代码   ·   今天看啥   ·   Code   ·   link之家   ·   卧龙搜索   ·   藏经阁   ·   小百科
Py学习 - 专注于Python技术发展的社区(原Django社区)
沪ICP备11025650号