MySQL:Innodb Handler_read_*引數解釋
本文為學習筆記,有誤請指出。
本文使用原始碼版本: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增加為掃描的行數。
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的用途。
四、總結
-
Handler_read_rnd_next 通常代表著全表掃描。
-
Handler_read_first 通常代表著全表或者全索引掃描。
-
Handler_read_next 通常代表著合理的使用了索引或者全索引掃描。
-
Handler_read_key 不管全表全索引或者正確使用的索引實際上都會增加,只是一次索引定位而已。
-
Innodb中全表掃描也是主鍵的全索引掃描。
-
順序訪問的一條記錄實際上都是呼叫 ha_innobase::general_fetch 函式,另外一個功能innodb_thread_concurrency引數的功能就在裡面實現,下次在說。
作者微信:
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7728585/viewspace-2158728/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL relay log 詳細引數解釋MySql
- MySQL:Innodb:innodb_flush_log_at_trx_commit引數影響的位置MySqlMIT
- MySQL資料庫innodb_fast_shutdown引數MySql資料庫AST
- MySQL 5.6 innodb_io_capacity引數效能測試MySql
- MySQL服務端innodb_buffer_pool_size配置引數MySql服務端
- MySQL 配置InnoDB配置非持久優化器統計資訊引數MySql優化
- mydumper和myloader使用引數解釋
- /etc/sysctl.conf引數解釋
- expdp/impdp 詳細引數解釋
- mysql插入慢之所innodb_flush_log_at_trx_commit引數的意義MySqlMIT
- redis持久化相關引數解釋Redis持久化
- MySQL自增列鎖模式 innodb_autoinc_lock_mode不同引數下效能測試MySql模式
- beego註解路由中各個引數解釋Go路由
- MySQL innodb_buffer_pool_size 變數MySql變數
- mysql innodb_data_file_path引數忘記設定或者重新調整辦法MySql
- [轉帖]HikariCP連線池引數解釋
- DG配置過程中的引數解釋
- MySQL:Innodb purge執行緒略解MySql執行緒
- MySQL5.7統計資訊更新的相關引數解釋和測試MySql
- MySQL8.0新增配置引數詳解MySql
- MySQL:Innodb中數字的儲存方式MySql
- innodb_flush_log_at_trx_commit引數的直白理解MIT
- MySQL Deadlocks in InnoDBMySql
- MySQL InnoDB 索引MySql索引
- Matplotlib基礎:subplot多子圖index引數解釋Index
- 【PARAMETER】Oracle引數:MAX_IDLE_BLOCKER_TIME 解釋OracleBloC
- java 執行緒池的初始化引數解釋和引數設定Java執行緒
- 詳解 MySql InnoDB 中意向鎖的作用MySql
- mysql 引數調優MySql
- Centos7 Docker安裝及常用引數解釋CentOSDocker
- Redis-cluster命令 cluster info 引數資訊解釋Redis
- mysql儲存引擎InnoDB詳解,從底層看清InnoDB資料結構MySql儲存引擎資料結構
- mysql innodb索引高度MySql索引
- MySQL InnoDB update流程MySql
- MySQL效能相關引數MySql
- MySQL 配置後臺InnoDB I/O執行緒數MySql執行緒
- Handler_read_*的總結
- redis info memory命令的各項引數解釋彙總Redis