MySQL:Innodb Handler_read_*變量解釋

本文為學(xué)習(xí)筆記灵寺，有誤請指出。
本文使用源碼版本：Percona 5.7.14

一、Handler_read_*值的實質(zhì)

內(nèi)部表示如下：

  {"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可以包含多個存儲引擎。因此這些值如何增加需要在引擎層的接口中自行實現(xiàn)烫映，也就是說各個引擎都有自己的實現(xiàn)沼本，在MySQL層進行匯總，因此這些值不是某個引擎特有的锭沟，打個比方如果有Innodb和MyISAM引擎抽兆，那么這些值是兩個引擎的總和。本文將以Innodb為主要學(xué)習(xí)對象進行解釋族淮。

二郊丛、各個值的解釋

1、Handler_read_key

內(nèi)部表示：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.
源碼函數(shù)解釋：Positions an index cursor to the index specified in the handle. Fetches the row if any.
作者解釋：這個函數(shù)是訪問索引的時候定位到值所在的位置用到的函數(shù)瞧筛，因為必須要知道讀取索引的開始位置才能向下訪問。

2导盅、Handler_read_next

內(nèi)部表示：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.
源碼函數(shù)解釋：
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.
作者解釋：訪問索引的下一條數(shù)據(jù)封裝的ha_innobase::general_fetch函數(shù)较幌，index_next_same和index_next不同在于訪問的方式不一樣，比如范圍range查詢需要用到和索引全掃描也會用到index_next白翻，而ref訪問方式會使用index_next_same

3乍炉、Handler_read_first

內(nèi)部表示：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
源碼函數(shù)解釋：Positions a cursor on the first record in an index and reads the corresponding row to buf.
作者解釋：定位索引的第一條數(shù)據(jù)绢片，實際上也是封裝的ha_innobase::index_read 函數(shù)(如全表掃描/全索引掃描調(diào)用)

4、Handler_read_rnd_next

內(nèi)部表示：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.
源碼函數(shù)解釋：Reads the next row in a table scan (also used to read the FIRST row in a table scan).
作者解釋：全表掃描訪問下一條數(shù)據(jù)岛琼，實際上也是封裝的ha_innobase::general_fetch底循，在訪問之前會調(diào)用ha_innobase::index_first

5、Handler_read_rnd

內(nèi)部表示：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.
作者解釋：這個狀態(tài)值在我測試期間只發(fā)現(xiàn)對臨時表做排序的時候會用到槐瑞，而且是Memory引擎的熙涤，具體只能按照文檔理解了。

6困檩、其他

最后2個簡單說一下

Handler_read_prev
Innodb接口為 ha_innobase::index_prev 訪問索引的上一條數(shù)據(jù)祠挫，實際上也是封裝的ha_innobase::general_fetch函數(shù)，用于ORDER BY DESC 索引掃描避免排序悼沿，內(nèi)部狀態(tài)值ha_read_prev_count增加等舔。
Handler_read_last
Innodb接口為ha_innobase::index_last 訪問索引的最后一條數(shù)據(jù)作為定位，實際上也是封裝的ha_innobase::index_read函數(shù)糟趾，用于ORDER BY DESC 索引掃描避免排序慌植，內(nèi)部狀態(tài)值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增加掃描行數(shù)。我們前面說過因為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增加掃描行數(shù)用于連續(xù)訪問接下來的行纵顾。我們前面說過因為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增加掃描行數(shù)次數(shù)用于接下來的數(shù)據(jù)訪問。

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增加掃描行數(shù)次數(shù)用于接下來的數(shù)據(jù)訪問。

6榨汤、被驅(qū)動表帶索引訪問

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 增加一次作為驅(qū)動表z1全表掃描定位的開始蠕搜，接下來Handler_read_rnd_next掃描全部記錄，每次掃描一次在z10表通過索引a_idx定位一次Handler_read_key增加1次收壕，然后接下來進行索引a_idx進行數(shù)據(jù)查找Handler_read_next增加為掃描的行數(shù)妓灌。

6轨蛤、索引避免排序正向和反向

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的用途虫埂。

四祥山、總結(jié)

Handler_read_rnd_next 通常代表著全表掃描。
Handler_read_first 通常代表著全表或者全索引掃描掉伏。
Handler_read_next 通常代表著合理的使用了索引或者全索引掃描缝呕。
Handler_read_key 不管全表全索引或者正確使用的索引實際上都會增加，只是一次索引定位而已岖免。
Innodb中全表掃描也是主鍵的全索引掃描岳颇。
順序訪問的一條記錄實際上都是調(diào)用ha_innobase::general_fetch函數(shù)，另外一個功能innodb_thread_concurrency參數(shù)的功能就在里面實現(xiàn)颅湘，下次在說话侧。

五、參考棧幀

全表掃描

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

最后編輯于：2020.09.21 16:17:53

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末闯参，一起剝皮案震驚了整個濱河市瞻鹏，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌鹿寨，老刑警劉巖新博，帶你破解...
沈念sama閱讀 211,042評論 6贊 490
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異脚草，居然都是意外死亡赫悄，警方通過查閱死者的電腦和手機，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 89,996評論 2贊 384
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進店門馏慨，熙熙樓的掌柜王于貴愁眉苦臉地迎上來埂淮，“玉大人，你說我怎么就攤上這事写隶【笞玻” “怎么了？”我有些...
開封第一講書人閱讀 156,674評論 0贊 345
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵慕趴，是天一觀的道長痪蝇。經(jīng)常有香客問我，道長冕房，這世上最難降的妖魔是什么躏啰？我笑而不...
開封第一講書人閱讀 56,340評論 1贊 283
?港島之戀（遺憾婚禮）
正文為了忘掉前任，我火速辦了婚禮耙册，結(jié)果婚禮上给僵，老公的妹妹穿的比我還像新娘。我一直安慰自己觅玻，他們只是感情好想际，可當我...
茶點故事閱讀 65,404評論 5贊 384
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布。她就那樣靜靜地躺著溪厘，像睡著了一般胡本。火紅的嫁衣襯著肌膚如雪。梳的紋絲不亂的頭發(fā)上畸悬，一...
開封第一講書人閱讀 49,749評論 1贊 289
城市分裂傳說
那天侧甫，我揣著相機與錄音，去河邊找鬼蹋宦。笑死披粟，一個胖子當著我的面吹牛，可吹牛的內(nèi)容都是我干的冷冗。我是一名探鬼主播守屉，決...
沈念sama閱讀 38,902評論 3贊 405
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼，長吁一口氣：“原來是場噩夢啊……” “哼蒿辙！你這毒婦竟也來了拇泛？” 一聲冷哼從身側(cè)響起，我...
開封第一講書人閱讀 37,662評論 0贊 266
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤思灌，失蹤者是張志新（化名）和其女友劉穎俺叭，沒想到半個月后，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體泰偿，經(jīng)...
沈念sama閱讀 44,110評論 1贊 303
?護林員之死
正文獨居荒郊野嶺守林人離奇死亡熄守，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點故事閱讀 36,451評論 2贊 325
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時候發(fā)現(xiàn)自己被綠了耗跛。大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片裕照。...
茶點故事閱讀 38,577評論 1贊 340
活死人
序言：一個原本活蹦亂跳的男人離奇死亡，死狀恐怖课兄，靈堂內(nèi)的尸體忽然破棺而出牍氛，到底是詐尸還是另有隱情，我是刑警寧澤烟阐，帶...
沈念sama閱讀 34,258評論 4贊 328
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布搬俊，位于F島的核電站，受9級特大地震影響蜒茄，放射性物質(zhì)發(fā)生泄漏唉擂。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點故事閱讀 39,848評論 3贊 312
男人毒藥：我在死后第九天來索命
文/蒙蒙一檀葛、第九天我趴在偏房一處隱蔽的房頂上張望玩祟。院中可真熱鬧，春花似錦屿聋、人聲如沸空扎。這莊子的主人今日做“春日...
開封第一講書人閱讀 30,726評論 0贊 21
一樁弒父案，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽转锈。三九已至盘寡，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間撮慨，已是汗流浹背竿痰。一陣腳步聲響...
開封第一講書人閱讀 31,952評論 1贊 264
情欲美人皮
我被黑心中介騙來泰國打工，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留砌溺，地道東北人影涉。一個月前我還...
沈念sama閱讀 46,271評論 2贊 360
代替公主和親
正文我出身青樓，卻偏偏與公主長得像规伐，于是被迫代替她去往敵國和親蟹倾。傳聞我的和親對象是個殘疾皇子，可洞房花燭夜當晚...
茶點故事閱讀 43,452評論 2贊 348