這篇文章主要介紹“MySQL中Innodb Handler_read_*參數(shù)分析”，在日常操作中，相信很多人在MySQL中Innodb Handler_read_*參數(shù)分析問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對(duì)大家解答”MySQL中Innodb Handler_read_*參數(shù)分析”的疑惑有所幫助！接下來，請(qǐng)跟著小編一起來學(xué)習(xí)吧！
創(chuàng)新互聯(lián)-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價(jià)比長沙縣網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式長沙縣網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋長沙縣地區(qū)。費(fèi)用合理售后完善，十載實(shí)體公司更值得信賴。
內(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},實(shí)際上這些變量都是MySQL層定義出來的，因?yàn)镸ySQL可以包含多個(gè)存儲(chǔ)引擎。因此這些值如何增加需要在引擎層的接口中自行實(shí)現(xiàn)，也就是說各個(gè)引擎都有自己的實(shí)現(xiàn)，在MySQL層進(jìn)行匯總，因此這些值不是某個(gè)引擎特有的，打個(gè)比方如果有Innodb和MyISAM引擎，那么這些值是兩個(gè)引擎的總和。本文將以Innodb為主要學(xué)習(xí)對(duì)象進(jìn)行解釋。
內(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.
作者解釋：這個(gè)函數(shù)是訪問索引的時(shí)候定位到值所在的位置用到的函數(shù)，因?yàn)楸仨氁雷x取索引的開始位置才能向下訪問。
內(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查詢需要用到和索引全掃描也會(huì)用到index_next，而ref訪問方式會(huì)使用index_next_same
內(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ù)，實(shí)際上也是封裝的ha_innobase::index_read 函數(shù)(如全表掃描/全索引掃描調(diào)用)
內(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ù)，實(shí)際上也是封裝的ha_innobase::general_fetch，在訪問之前會(huì)調(diào)用ha_innobase::index_first
內(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.
作者解釋：這個(gè)狀態(tài)值在我測(cè)試期間只發(fā)現(xiàn)對(duì)臨時(shí)表做排序的時(shí)候會(huì)用到，而且是Memory引擎的，具體只能按照文檔理解了。
最后2個(gè)簡單說一下
Handler_read_prev
Innodb接口為 ha_innobase::index_prev 訪問索引的上一條數(shù)據(jù)，實(shí)際上也是封裝的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ù)作為定位，實(shí)際上也是封裝的ha_innobase::index_read函數(shù)，用于ORDER BY DESC 索引掃描避免排序，內(nèi)部狀態(tài)值ha_read_last_count增加。
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)
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ù)。我們前面說過因?yàn)閔a_innobase::index_first也是封裝的ha_innobase::index_read因此都需要+1。
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ù)訪問接下來的行。我們前面說過因?yàn)閔a_innobase::index_first也是封裝的ha_innobase::index_read因此都需要+1。
我這里因?yàn)槭菧y(cè)試索引全是等于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ù)訪問。
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ù)訪問。
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ū)動(dòng)表z1全表掃描定位的開始，接下來Handler_read_rnd_next掃描全部記錄，每次掃描一次在z10表通過索引a_idx定位一次Handler_read_key增加1次，然后接下來進(jìn)行索引a_idx進(jìn)行數(shù)據(jù)查找Handler_read_next增加為掃描的行數(shù)。
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的用途。
到此，關(guān)于“MySQL中Innodb Handler_read_*參數(shù)分析”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！
