如何理解MySQL handler相關狀態參數

143次閱讀

共計 2631 個字符，預計需要花費 7 分鐘才能閱讀完成。

如何理解 MySQL handler 相關狀態參數，相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現的原因和解決方法，通過這篇文章希望你能解決這個問題。

概述

MySQL“自古以來”都有一個神秘的 HANDLER 命令，而此命令非 SQL 標準語法，可以降低優化器對于 SQL 語句的解析與優化開銷，從而提升查詢性能。

一、Handler 參數列表

mysql  show global status like  Handle%

參數介紹如下：

二、實際優化中比較看重的幾個參數

1. Handler_read_first 和 Handler_read_rnd_next

前者表示全索引掃描的次數，當前者值較大，說明可能是一個全索引掃描，此外走全表也可能導致這個值比較大; 后者表示在進行數據文件掃描時，從數據文件里取數據的次數。當后者值較大，說明掃描的行非常多，可能沒有合理的使用索引

2. Handler_read_key

這個表示走索引的次數，如果這個值比較大，說明索引使用良好

三、實驗演示

1. 準備數據

CREATE TABLE test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, DATA VARCHAR ( 32 ), ts TIMESTAMP, INDEX ( DATA ) ); INSERT INTO test VALUES ( NULL,  abc , NOW( ) ), ( NULL,  abc , NOW( ) ), ( NULL,  abd , NOW( ) ), ( NULL,  acd , NOW( ) ), ( NULL,  def , NOW( ) ), ( NULL,  pqr , NOW( ) ), ( NULL,  stu , NOW( ) ), ( NULL,  vwx , NOW( ) ), ( NULL,  yza , NOW( ) ), ( NULL,  def , NOW( ) )

2. limit 2 觀察 Handler_read_first、Handler_read_rnd_next、Handler_read_key

FLUSH STATUS; select * from test limit 2; SHOW SESSION STATUS LIKE  handler_read%  explain select * from test limit 2;

可以看到全表掃描其實也是走了 key(Handler_read_key=1), 可能是因為索引組織表的原因。因為 limit 2 所以 rnd_next 為 2. 這個 Stop Key 在執行計劃中是看不出來的。

3. 索引消除排序 (升序)，只走索引

FLUSH STATUS; select data from test order by data limit 4; SHOW SESSION STATUS LIKE  handler_read%  explain select data from test order by data limit 4;

使用索引消除排序，因為是升序，所以 read first 為 1, 由于 limit 4, 所以 read_next 為 3，因為只從索引拿，不從數據文件里取數據所以 rnd_next 為 0，索引通過這個可以看出 Stop Key.

4. 索引消除排序 (倒序)

FLUSH STATUS; select data from test order by data desc limit 3; SHOW SESSION STATUS LIKE  handler_read%  explain select data from test order by data desc limit 3;

使用索引消除排序，因為是倒序，所以 read_last 為 1,read_prev 為 2. 因為往回讀了兩個 key.

5. 沒有使用索引

ALTER TABLE test ADD COLUMN file_sort text; UPDATE test SET file_sort =  abcdefghijklmnopqrstuvwxyz  WHERE id = 1; UPDATE test SET file_sort =  bcdefghijklmnopqrstuvwxyza  WHERE id = 2; UPDATE test SET file_sort =  cdefghijklmnopqrstuvwxyzab  WHERE id = 3; UPDATE test SET file_sort =  defghijklmnopqrstuvwxyzabc  WHERE id = 4; UPDATE test SET file_sort =  efghijklmnopqrstuvwxyzabcd  WHERE id = 5; UPDATE test SET file_sort =  fghijklmnopqrstuvwxyzabcde  WHERE id = 6; UPDATE test SET file_sort =  ghijklmnopqrstuvwxyzabcdef  WHERE id = 7; UPDATE test SET file_sort =  hijklmnopqrstuvwxyzabcdefg  WHERE id = 8; UPDATE test SET file_sort =  ijklmnopqrstuvwxyzabcdefgh  WHERE id = 9; UPDATE test SET file_sort =  jklmnopqrstuvwxyzabcdefghi  WHERE id = 10; FLUSH STATUS; select * from test order by file_sort limit 4; SHOW SESSION STATUS LIKE  handler_read%  explain select * from test order by file_sort limit 4;

Handler_read_rnd 為 4 說明沒有使用索引，rnd_next 為 11 說明掃描了所有的數據,read key 總是 read_rnd+1。

看完上述內容，你們掌握如何理解 MySQL handler 相關狀態參數的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注丸趣 TV 行業資訊頻道，感謝各位的閱讀！

正文完