前言

對于非數(shù)據(jù)庫開發(fā)人員而言，難以對MySQL源碼進行分析或調(diào)試，接近一個黑盒，但MySQL提供了一些命令及系統(tǒng)狀態(tài)變量，可對索引及其他內(nèi)容進行分析。掌握這些方法后，可以盡量深入地了解MySQL的一些實現(xiàn)細(xì)節(jié)。

MySQL索引分析除了EXPLAIN還有什么方法

通過以下方法，可以獲得MySQL索引相關(guān)的數(shù)據(jù)，便于分析與理解索引相關(guān)的問題。

2.1. 通過慢查詢?nèi)罩居涗汼QL語句執(zhí)行時間

參考 https://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html 。

慢查詢?nèi)罩局邪腟QL語句，其執(zhí)行時間超過long_query_time（系統(tǒng)參數(shù)）秒，并且至少檢查min_examined_row_limit（系統(tǒng)參數(shù)）行。

獲取初始鎖的時間不包含在執(zhí)行時間中。mysqld（MySQL服務(wù)器）在SQL語句執(zhí)行且釋放所有鎖之后，將一條語句寫入慢查詢?nèi)罩局?#xff0c;因此日志順序可能與執(zhí)行順序不同。

2.1.1. 慢查詢?nèi)罩镜南到y(tǒng)參數(shù)

long_query_time系統(tǒng)參數(shù)的最小值為0，默認(rèn)值為10（秒）。將慢查詢?nèi)罩居涗浀轿募r，寫入的時間包括微秒部分。將慢查詢?nèi)罩居涗浀奖?#xff0c;僅寫入整數(shù)時間，微秒部分將被忽略。

默認(rèn)情況下，慢查詢?nèi)罩臼墙玫摹low_query_log系統(tǒng)參數(shù)可以設(shè)置慢查詢?nèi)罩臼欠駟⒂?#xff0c;參數(shù)值為0或OFF表示禁用，1或ON表示啟用。

slow_query_log_file系統(tǒng)參數(shù)用于設(shè)置日志文件名稱，默認(rèn)值為“[主機名]-slow.log”。MySQL服務(wù)器會在data目錄中創(chuàng)建文件，除非通過絕對路徑名指定了其他目錄。

log_output系統(tǒng)參數(shù)用于設(shè)置通用查詢?nèi)罩九c慢查詢?nèi)罩镜妮敵鑫恢谩Ｆ渲禐橐粋€或多個逗號分隔的單詞組成的列表，可選值為TABLE，FILE及NONE。

• 當(dāng)指定TABLE時，表示將日志記錄到名為mysql的系統(tǒng)數(shù)據(jù)庫的general_log與slow_log表中；
• 當(dāng)指定FILE時，表示將日志記錄到文件中；
• NONE表示禁用日志。

不使用索引對行進行查找的查詢，需要在啟用log_queries_not_using_indexes系統(tǒng)變量后，才會在寫入慢查詢?nèi)罩緯r被包含進去。該系統(tǒng)默認(rèn)為關(guān)閉。

查詢緩存處理的查詢不會被MySQL服務(wù)器記錄。

2.1.2. 慢查詢?nèi)罩緝?nèi)容格式

為了通過慢查詢?nèi)罩居涗洸樵冋Z句的準(zhǔn)確耗時，需要將慢查詢?nèi)罩颈４嬷廖募?#xff0c;log_output系統(tǒng)參數(shù)需要設(shè)置為FILE。慢查詢?nèi)罩疚募?#xff0c;每條語句生成的記錄都有一行以#開頭的數(shù)據(jù)，并包含以下字段（所有字段在一行中顯示）：

• Query_time

語句執(zhí)行時間，單位為秒；

• Lock_time

獲取鎖的時間，單位為秒；

• Rows_sent

發(fā)送給客戶端的行數(shù)；

• Rows_examined

MySQL服務(wù)器層檢查的行數(shù)（不包含存儲引擎內(nèi)部的處理）。

慢查詢?nèi)罩疚募械拿總€語句之前都有一個SET語句，該SET語句包括一個時間戳，代表該慢查詢語句的記錄時間。

2.1.3. 開啟慢查詢?nèi)罩镜膮?shù)配置

為了開啟慢查詢?nèi)罩?#xff0c;需要修改MySQL服務(wù)器使用的配置文件的“[mysqld]”節(jié)點對應(yīng)的系統(tǒng)參數(shù)，修改配置文件并重啟MySQL服務(wù)器程序后生效。（通過“set GLOBAL 系統(tǒng)變量=xxx”命令修改慢查詢?nèi)罩鞠嚓P(guān)參數(shù)時，未生效。）系統(tǒng)參數(shù)設(shè)置如下所示：

slow_query_log = 1 long_query_time = 0 min_examined_row_limit = 1

設(shè)置“slow_query_log = 1”，以開啟慢查詢?nèi)罩尽?/p>

設(shè)置“l(fā)ong_query_time = 0”，將每次的查詢語句都記錄至慢查詢?nèi)罩疚募小?/p>

設(shè)置“min_examined_row_limit = 1”，可以使explain語句不出現(xiàn)在慢查詢?nèi)罩局?#xff0c;因為explain語句的Rows_examined為0。

slow_query_log = 1 long_query_time = 0

慢查詢?nèi)罩疚募J(rèn)生成在安裝目錄的data目錄中。

使用可視化數(shù)據(jù)庫管理工具連接MySQL時，可能會定時發(fā)送請求，也會生成在慢查詢?nèi)罩疚募小?/p>

2.1.4. 慢查詢?nèi)罩緝?nèi)容示例

慢查詢?nèi)罩緝?nèi)容示例如下：

# Time: 200311 19:25:39 # User@Host: test[test] @ localhost [127.0.0.1] # Thread_id: 3 Schema: testdb QC_hit: No Query_time: 0.015360 Lock_time: 0.001452 Rows_sent: 5222 Rows_examined: 5222 use testdb; SET timestamp=1583925939; select id,cust_info from test_table_log where cust_info='1881';

經(jīng)測試，以上Query_time時間僅包含MySQL服務(wù)器執(zhí)行查詢操作的耗時，不包含客戶端展示數(shù)據(jù)的耗時。

2.2. 獲取SQL語句讀取索引次數(shù)

通過以下所述的方法，可以獲取SQL語句執(zhí)行時，讀取的索引次數(shù)及方法，但不會顯示讀取了哪個索引。

2.2.1. SHOW STATUS語句用法

參考 https://dev.mysql.com/doc/refman/5.6/en/show-status.html 。

SHOW STATUS語句提供服務(wù)器狀態(tài)信息。該語句不需要任何特權(quán)，只要能連接到服務(wù)器即可。

SHOW STATUS語句用法如下所示：

SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern' | WHERE expr]

SHOW STATUS語句接受可選的作用域修飾符，可為GLOBAL或SESSION：

• 當(dāng)指定作用域為GLOBAL時，SHOW STATUS顯示全局的狀態(tài)值。全局狀態(tài)變量可以表示服務(wù)器本身某些方面的狀態(tài)，或者表示MySQL所有連接的匯總狀態(tài)。如果某個變量沒有全局值，則顯示會話值；
• 當(dāng)指定作用域為SESSION時，SHOW STATUS顯示當(dāng)前連接的狀態(tài)變量值。如果某個變量沒有會話值，則顯示全局值。LOCAL是SESSION的代名詞；
• 如果沒有指定作用域，默認(rèn)使用SESSION。

每次調(diào)用SHOW STATUS語句都會使用一個內(nèi)部臨時表，并增加全局Created_tmp_tables值。

SHOW STATUS語句支持LIKE子句，僅顯示變量名稱與指定模式匹配的行。

2.2.2. 與讀取索引次數(shù)相關(guān)的服務(wù)器狀態(tài)變量

參考 https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html 。

與讀取索引次數(shù)相關(guān)的服務(wù)器狀態(tài)變量以“HANDLER_READ”開頭，如下所示：

• Handler_read_first

索引中第一個條目的讀取索引次數(shù)。如果該數(shù)值很高，則表明服務(wù)器正在執(zhí)行很多全索引掃描（例如，SELECT col1 FROM foo，假設(shè)col1列存在索引）；

• Handler_read_key

基于索引鍵讀取行的請求數(shù)（即通過索引讀取行的數(shù)量）。如果該數(shù)值很高，則表明表的索引很合適查詢；

• Handler_read_last

讀取索引中最后一個鍵的請求數(shù)。使用ORDER BY時，服務(wù)器將發(fā)出一個first-key請求（對應(yīng)Handler_read_first），然后發(fā)出多個next-key（對應(yīng)Handler_read_next）請求；使用ORDER BY DESC時，服務(wù)器將發(fā)出一個last-key（對應(yīng)Handler_read_last）請求，然后發(fā)出多個previous-key（對應(yīng)Handler_read_prev）請求；

• Handler_read_next

按索引鍵順序讀取下一行的請求數(shù)。當(dāng)對索引列使用范圍約束進行查詢，或進行索引掃描時，該數(shù)值會增加；

• Handler_read_prev

按索引鍵順序讀取上一行的請求數(shù)。這種讀取方法主要用于優(yōu)化ORDER BY … DESC；

• Handler_read_rnd

基于固定位置讀取行的請求數(shù)。當(dāng)執(zhí)行很多需要對結(jié)果進行排序的查詢時，該數(shù)值會很高?？赡苡泻芏嗖樵冃枰狹ySQL掃描整個表，或者聯(lián)接未正確使用索引鍵；

• Handler_read_rnd_next

讀取數(shù)據(jù)文件下一行的請求數(shù)（即通過全表掃描讀取行的數(shù)量）。當(dāng)進行大量全表掃描時，該數(shù)值會變高。通常這表明表未正確建立索引，或者編寫的查詢未利用索引。

2.2.3. 使用SHOW STATUS獲取SQL語句讀取索引次數(shù)

當(dāng)需要獲取當(dāng)前會話的SQL語句讀取索引次數(shù)，可以執(zhí)行SHOW STATUS查看以“HANDLER_READ”開頭的服務(wù)器狀態(tài)變量（以下執(zhí)行命令中的HANDLER_READ大小寫不限）：

SHOW STATUS LIKE 'HANDLER_READ%';

若需要使以上數(shù)值重置，可以重新連接MySQL服務(wù)器（創(chuàng)建新的會話），或重啟MySQL服務(wù)器程序。

2.2.4. SQL語句讀取索引次數(shù)示例

在以下示例中，test_table_log表的create_time字段存在單列索引，id列為主鍵。執(zhí)行以下語句時，需要讀取索引中第一個記錄，Handler_read_first值加1。

select * from test_table_log order by create_time limit 1;

執(zhí)行以下語句時，需要讀取索引中的最后一個記錄，Handler_read_last值加1。

select * from test_table_log order by create_time desc limit 1;

執(zhí)行以下語句時，需要基于索引讀取行，Handler_read_key值加1。

select * from test_table_log where create_time = '2020/3/10 12:23:38' limit 1;

執(zhí)行以下語句時，需要基于索引讀取行，Handler_read_key值加1；并需要根據(jù)索引讀取后續(xù)9行，Handler_read_next值增加9。

select * from test_table_log where create_time = '2020/3/10 12:23:38' limit 10;

執(zhí)行以下語句時，共查詢到4條記錄需要基于索引讀取行，Handler_read_key值加1；并需要根據(jù)索引讀取后續(xù)行，Handler_read_next值增加4。

select * from test_table_log where id >= 'testtime1583814037num1' and id <= 'testtime1583814037num1000'

執(zhí)行以下語句時，需要讀取索引中第一個記錄，Handler_read_first值加1；并需要根據(jù)索引讀取后續(xù)9行，Handler_read_next值增加9。

select * from test_table_log order by create_time limit 10;

執(zhí)行以下語句時，需要讀取索引中的最后一個記錄，Handler_read_last值加1；并需要根據(jù)索引讀取之前的9行，Handler_read_prev值增加9。

select * from test_table_log order by create_time desc limit 10;

執(zhí)行以下語句時，需要通過全表掃描讀取數(shù)據(jù)文件中的行，數(shù)量為110，Handler_read_rnd_next值增加110。

select * from test_table_log limit 100,10;

執(zhí)行以下語句時，需要通過全表掃描讀取數(shù)據(jù)文件中的行，Handler_read_rnd_next值增加超過110；并需要對查詢到的10條記錄進行排序，Handler_read_rnd值增加10。

select a.* from ( select * from test_table_log limit 100,10 ) as a order by a.create_time;

2.2.5. 查詢數(shù)據(jù)庫表獲取SQL語句讀取索引次數(shù)（不使用）

參考 https://dev.mysql.com/doc/refman/5.6/en/status-table.html 。

INFORMATION_SCHEMA.GLOBAL_STATUS與INFORMATION_SCHEMA.SESSION_STATUS表與“SHOW GLOBAL STATUS”及“SHOW SESSION STATUS”命令顯示對應(yīng)的信息。查詢示例如下：

select * from INFORMATION_SCHEMA.SESSION_STATUS where VARIABLE_NAME like 'HANDLER_READ%' and VARIABLE_VALUE <> '0' order by cast(VARIABLE_VALUE as UNSIGNED INTEGER) desc;

當(dāng)從上述表查詢數(shù)據(jù)獲取SQL語句讀取索引次數(shù)時，會導(dǎo)致Handler_read_rnd_next變量值增大；當(dāng)使用排序時，還會導(dǎo)致Handler_read_rnd變量值增大。因此需要獲取SQL語句讀取索引次數(shù)時，不通過上述數(shù)據(jù)庫表查詢。

2.3. 獲取InnoDB緩沖池中索引頁與記錄數(shù)

InnoDB緩沖池中索引頁與記錄數(shù)，可以反映MySQL在執(zhí)行SQL語句時，將指定表的索引讀取到緩沖池中的索引頁及記錄數(shù)量。

若需要重置InnoDB緩沖池相關(guān)數(shù)據(jù)，需要重啟MySQL服務(wù)器程序（未找到其他方法清空InnoDB緩沖池）。

2.3.1. INNODB_BUFFER_PAGE表

參考 https://dev.mysql.com/doc/refman/5.6/en/innodb-information-schema-buffer-pool-tables.html 。

InnoDB INFORMATION_SCHEMA緩沖池表提供了緩沖池狀態(tài)信息，以及有關(guān)InnoDB緩沖池頁面的元數(shù)據(jù)。

InnoDB INFORMATION_SCHEMA緩沖池表中包含INNODB_BUFFER_PAGE，該表保存InnoDB緩沖池中每個頁的信息。查詢INNODB_BUFFER_PAGE或INNODB_BUFFER_PAGE_LRU表可能會影響性能，應(yīng)避免在生產(chǎn)環(huán)境查詢。

2.3.2. INNODB_BUFFER_PAGE表字段

參考 https://dev.mysql.com/doc/refman/5.6/en/innodb-buffer-page-table.html 。INNODB_BUFFER_PAGE表中包含以下關(guān)注的字段：

• POOL_ID

緩沖池ID。是用于區(qū)分多個緩沖池實例的標(biāo)識符。

• SPACE

表空間ID。

• PAGE_NUMBER

頁編號（page number）。

• PAGE_TYPE

頁的類型。例如“INDEX”代表B+樹節(jié)點，“BLOB”代表未壓縮的BLOB頁等。

• TABLE_NAME

頁所屬的表名。該列僅適用于PAGE_TYPE值為INDEX的頁。

• INDEX_NAME

頁所屬的索引名?？梢允蔷鄞厮饕蚨壦饕拿Q。該列僅適用于PAGE_TYPE值為INDEX的頁。

• NUMBER_RECORDS

頁的記錄的數(shù)量。

2.3.3. 從INNODB_BUFFER_PAGE表獲取InnoDB緩沖池中索引頁與記錄數(shù)

從INNODB_BUFFER_PAGE表中可獲取InnoDB緩沖池中索引頁與記錄數(shù)，table_name字段的形式為“`[數(shù)據(jù)庫名]`.`[表名]`”。

查詢的示例語句如下，page_num為指定表的指定索引在InnoDB緩沖池中的頁的總數(shù)，record_num為指定表的指定索引在InnoDB緩沖池中記錄的總數(shù)：

select TABLE_NAME,INDEX_NAME,count(*) as page_num,sum(NUMBER_RECORDS) as record_num from INFORMATION_SCHEMA.INNODB_BUFFER_PAGE where table_name = '`testdb`.`test_table_log`' group by TABLE_NAME,INDEX_NAME order by page_num desc;

查詢結(jié)果如下所示：

2.4. 獲取InnoDB索引頁操作次數(shù)

InnoDB對索引頁的操作包括讀取、創(chuàng)建及寫入等。

若需要重置上述數(shù)據(jù)，需要重啟MySQL服務(wù)器程序。

2.4.1. 使用SHOW STATUS獲取InnoDB索引頁操作次數(shù)

參考 https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html 。

MySQL服務(wù)器狀態(tài)中，包含以下與InnoDB索引頁操作次數(shù)相關(guān)的狀態(tài)：

• Innodb_buffer_pool_pages_data

InnoDB緩沖池中包含數(shù)據(jù)的頁數(shù)。該數(shù)量包含臟頁與非臟頁。

• Innodb_pages_created

對InnoDB表操作創(chuàng)建的頁數(shù)。

• Innodb_pages_read

對InnoDB表操作，從InnoDB緩沖池讀取的頁數(shù)。

• Innodb_pages_written

對InnoDB表操作導(dǎo)致被寫的頁數(shù)。

使用SHOW STATUS獲取InnoDB索引頁操作次數(shù)時，可執(zhí)行以下語句：

SHOW STATUS LIKE 'Innodb_pages_%'; SHOW STATUS LIKE 'Innodb_buffer_pool_pages_data';

2.4.2. 查詢數(shù)據(jù)庫表獲取InnoDB索引頁操作次數(shù)

參考 https://dev.mysql.com/doc/refman/5.6/en/innodb-buffer-pool-stats-table.html 。

INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS表提供了許多與“SHOW ENGINE INNODB STATUS”輸出相同的緩沖池信息。

上述表包含以下關(guān)注的列：

• DATABASE_PAGES

InnoDB緩沖池中包含數(shù)據(jù)的頁數(shù)。該數(shù)量包含臟頁與非臟頁。

與“SHOW STATUS”輸出中的Innodb_buffer_pool_pages_data相同。

• NUMBER_PAGES_READ

讀取的頁數(shù)。

與“SHOW STATUS”輸出中的Innodb_pages_read相同。

• NUMBER_PAGES_CREATED

創(chuàng)建的頁數(shù)。

與“SHOW STATUS”輸出中的Innodb_pages_created相同。

• NUMBER_PAGES_WRITTEN

寫入的頁數(shù)。

與“SHOW STATUS”輸出中的Innodb_pages_written相同。

從上述表獲取InnoDB索引頁操作次數(shù)示例語句如下（查詢操作不會導(dǎo)致以上參數(shù)變化）：

select DATABASE_PAGES, NUMBER_PAGES_READ, NUMBER_PAGES_CREATED, NUMBER_PAGES_WRITTEN from INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS;

2.4.3. 使用SHOW ENGINE STATUS獲取InnoDB索引頁操作次數(shù)（不使用）

參考 https://dev.mysql.com/doc/refman/5.6/en/show-engine.html 。

“SHOW ENGINE”顯示指定存儲引擎的操作信息。執(zhí)行時需要“PROCESS”權(quán)限。

“SHOW ENGINE INNODB STATUS”顯示來自標(biāo)準(zhǔn)InnoDB監(jiān)控器的有關(guān)InnoDB存儲引擎狀態(tài)的大量信息。

以上輸出信息全部包含在Status字段中，無法進行過濾，不方便單獨查看Pages read, created, written等參數(shù)。

2.5. 獲取索引頁分裂次數(shù)

參考 https://dev.mysql.com/doc/refman/5.6/en/index-page-merge-threshold.html ，頻繁發(fā)生頁分裂可能對性能產(chǎn)生影響。

2.5.1. INFORMATION_SCHEMA.INNODB_METRICS表

參考 https://dev.mysql.com/doc/refman/5.6/en/innodb-metrics-table.html 。

INFORMATION_SCHEMA.INNODB_METRICS表提供了InnoDB的各種性能信息。

每個監(jiān)控器代表InnoDB源代碼中用于收集計數(shù)器（counter）信息的位置。每個計數(shù)器可被啟動、停止及重置。

默認(rèn)情況下，收集的數(shù)據(jù)比較少。為了啟動、停止及重置計算器，需要對系統(tǒng)變量innodb_monitor_enable、innodb_monitor_disable、innodb_monitor_reset或innodb_monitor_reset_all進行設(shè)置。

INFORMATION_SCHEMA.INNODB_METRICS表包含以下關(guān)注的列：

• NAME

計數(shù)器的唯一名稱；

• COUNT

計數(shù)器啟用后的計數(shù)值；

• STATUS

enabled代表計數(shù)器在執(zhí)行，disabled代表已停止；

• COMMENT

計數(shù)器描述。

在查詢INFORMATION_SCHEMA.INNODB_METRICS表時，需要有PROCESS權(quán)限。

在INFORMATION_SCHEMA.INNODB_METRICS表中，存在NAME為“index_page_splits”，COMMENT為“Number of index page splits”的記錄，即索引頁分裂次數(shù)對應(yīng)的計數(shù)器，COUNT列為索引頁分裂次數(shù)。

select count from INFORMATION_SCHEMA.INNODB_METRICS where name='index_page_splits';

2.5.2. 啟用索引頁分裂次數(shù)計數(shù)器

參考 https://dev.mysql.com/doc/refman/5.6/en/innodb-information-schema-metrics-table.html 。

全局變量innodb_monitor_enable、innodb_monitor_disable可分別用于啟用、禁用記數(shù)器。命令如下所示：

SET GLOBAL innodb_monitor_enable = index_page_splits;SET GLOBAL innodb_monitor_disable = index_page_splits;

innodb_monitor_enable變量還可以MySQL配置文件的[mysqld]節(jié)點進行配置，可使用半角逗號對多項進行分隔。innodb_monitor_disable不支持在MySQL配置文件配置。

經(jīng)驗證，對以上系統(tǒng)變量進行設(shè)置時，需要有SUPER權(quán)限（root用戶）。

2.6. 獲取執(zhí)行計劃

參考 https://dev.mysql.com/doc/refman/5.6/en/explain-output.html 。

EXPLAIN語句提供有關(guān)MySQL如何執(zhí)行語句的信息。EXPLAIN適用于SELECT，DELETE，INSERT，REPLACE和UPDATE語句。

EXPLAIN為SELECT語句中使用的每個表返回一行信息。輸出中的表的順序，與MySQL在處理語句時讀取表的順序一致。

2.6.1. EXPLAIN輸出列

EXPLAIN輸出的列及含義如下所示：

2.6.2. id

id列為SELECT的標(biāo)識符，其值為查詢中SELECT的順序號。

即id列反映了SELECT在執(zhí)行時的順序（從小到大）。

2.6.3. select_type

select_type列為SELECT的類型，select_type值及含義如下所示：

DEPENDENT通常表示使用了相關(guān)子查詢（correlated subquery）。

2.6.4. type

type列為連接類型，描述了表的連接方式。不同的連接類型如下所示：

• system

表只有一行（系統(tǒng)表）。system是const連接類型的特例。

• const

查詢的開頭讀取的表，最多只有一行匹配。因為只有一行，優(yōu)化器的其余部分可以將此行中對應(yīng)列的值視為常量。

const連接類型對應(yīng)的表查詢非常快，因為它們只讀取一次。

將主鍵或唯一索引的所有部分與常量值進行比較時會使用const連接類型。

在以下示例中，tbl_name表可以作為const連接類型使用：

SELECT * FROM tbl_name WHERE primary_key=1;SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;

• eq_ref

對于先前的表的每種行組合，從eq_ref連接類型對應(yīng)的表中讀取一行。除了system和const連接類型外，eq_ref是最好的聯(lián)接類型。當(dāng)連接使用索引的所有部分并且索引是主鍵或非空唯一索引時，將使用eq_ref連接類型。

eq_ref可用于使用=運算符進行比較的索引列。比較值可以是常量，也可以是使用在此表之前讀取的表中列的表達(dá)式。

在以下示例中，MySQL可以使用eq_ref連接類型處理ref_table表：

SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

• ref

對于先前的表的每種行組合，從ref連接類型對應(yīng)的表讀取所有具有匹配索引值的行。

如果連接僅使用索引鍵的最左前綴或者索引鍵不是主鍵或唯一索引（即連接不能基于索引鍵值選擇單行），則使用ref連接類型。

如果使用的鍵只匹配少量幾行，則ref是一個好的連接類型。

ref連接類型可用于使用=或<=>運算符進行比較的索引列。

在以下示例中，MySQL可以使用ref連接類型處理ref_table表：

SELECT * FROM ref_table WHERE key_column=expr;SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

• fulltext

使用全文索引執(zhí)行連接。

• ref_or_null

ref_or_null與ref類似，區(qū)別在于使用ref_or_null時，MySQL對包含NULL值的行進行了額外搜索。

ref_or_null連接類型最常用于解決子查詢。

在以下示例中，MySQL可以使用ref_or_null連接類型處理ref_table表：

SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;

• index_merge

index_merge連接類型表示使用了索引合并優(yōu)化。在這種情況下，輸出行中的key列包含使用的索引列表，key_len列包含所用索引的最長鍵部分列表。

• unique_subquery

unique_subquery連接類型在某些使用IN的子查詢的情況下替代eq_ref，如下所示：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery只是一個索引查找函數(shù)，可以完全替代子查詢以提高效率。

• index_subquery

index_subquery連接類型與unique_subquery類似，它替代了IN子查詢，支持子查詢中的非唯一索引，如下所示：

value IN (SELECT key_column FROM single_table WHERE some_expr)

• range

range連接類型表示僅檢索給定范圍內(nèi)的行，使用索引選擇行。

EXPLAIN輸出行中的key列表示使用哪個索引。key_len列包含使用的最長的索引鍵部分。對于此連接類型，ref列為NULL。

使用=，<>，>，> =，<，<=，IS NULL，<=>，BETWEEN，LIKE或IN()運算符中的任何一個將索引鍵列與常量進行比較時，可以使用range類型。

• index

index連接類型與ALL類型類似，區(qū)別在于index連接類型有對索引樹進行掃描。有兩種方式：a. 如果查詢的索引是覆蓋索引，并且可滿足所需表的所有數(shù)據(jù)，則僅掃描索引樹。在這種情況下，Extra列顯示“Using index”。僅掃描索引通常比ALL連接類型快，因為索引的大小通常小于表數(shù)據(jù)。b. 對指定索引讀取數(shù)據(jù)，進行全表掃描，以按索引順序查找數(shù)據(jù)行。Extra列不會顯示“Using index”。

當(dāng)查詢僅使用屬于單個索引一部分的列時，MySQL可以使用此連接類型。

• ALL

對之前的表中的每個行組合進行全表掃描。

如果當(dāng)前表是第一個沒有標(biāo)記為const的表，這通常是不好的，并且通常在所有其他情況下非常糟糕。

通常，可以通過添加索引來避免ALL連接類型，這些索引基于常量值或先前的表的列值，啟用從該表的行檢索。

2.6.5. possible_keys

possible_keys列表示MySQL可以選擇用于查找此表的行的索引。請注意，此列于EXPLAIN輸出中顯示的表的順序完全獨立。

假如possible_keys列為NULL，說明沒有相關(guān)的索引。

2.6.6. key

key列表示MySQL實際決定使用的索引鍵。如果MySQL決定使用其中一個possible_keys索引來查找行，那么該索引將被顯示在key列中。

如果key列為NULL，說明MySQL未找到更有效執(zhí)行查詢的索引。

要強制MySQL使用或忽略possible_keys列中列出的索引，請在查詢中使用FORCE INDEX，USE INDEX或IGNORE INDEX。

2.6.7. key_len

key_len列表示MySQL決定使用的索引鍵的長度。根據(jù)key_len的值可以確定MySQL實際使用了聯(lián)合索引的多少部分。

由于索引鍵的存儲格式，可以為NULL的列比不允許為NULL的列的索引鍵長度大1。

2.6.8. ref

ref列顯示了在key列中列出的，用于從表中查詢行的索引，與哪些列或常量進行了比較。

如果ref列的值為func，說明使用的值是某些函數(shù)的結(jié)果。

2.6.9. rows

rows列表示MySQL認(rèn)為執(zhí)行查詢時必須檢查的行數(shù)。

對于InnoDB表，該數(shù)字是估計值，可能并不總是準(zhǔn)確的。

2.6.10. Extra

Extra列包含MySQL如何解決查詢的額外信息。以下為Extra列的部分可能的值及說明：

• Using filesort

出現(xiàn)該提示說明MySQL在進行排序時需要使用filesort，未使用索引進行排序。

• Using index

從表中檢索列信息時，僅使用索引樹的信息，不需要進行額外的查找以讀取實際行。

當(dāng)僅查詢屬于單個索引的部分列時，可以使用此策略。

對于具有用戶定義的聚簇索引的InnoDB表，即使Extra列中沒有出現(xiàn)“Using index”，對應(yīng)的索引也可能被使用。當(dāng)type列為index且key列為主鍵時，對應(yīng)以上情況。

• Using index condition

出現(xiàn)該提示說明MySQL在查詢時使用了索引條件下推。

• Using sort_union(…), Using union(…), Using intersect(…)

以上顯示了對應(yīng)index_merge連接類型，即使用索引合并時，索引掃描合并時使用的特定算法。

• Using temporary

為了解決該查詢，MySQL需要創(chuàng)建一個臨時表來保存結(jié)果。如果查詢包含GROUP BY和ORDER BY子句，且列出了不同的列時，通常會發(fā)生這種情況。

• Using where

WHERE子句用于限制與下一個表匹配的行，或發(fā)送到客戶端的行。假如Extra列的值不是using where，且表的連接類型為ALL或index時，則說明查詢語句存在一些問題，除非是特意為了獲取或檢查表中所有的行。

3. InnoDB讀取索引頁時機

InnoDB讀取索引頁的時機，包括但不限于MySQL啟動、執(zhí)行EXPLAIN語句、執(zhí)行查詢/插入/更新操作等情況。

為了獲取InnoDB讀取索引頁的次數(shù)，可參考前文，查詢以“Innodb_pages_”開頭的系統(tǒng)狀態(tài)，或從INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS表查詢。

3.1. MySQL啟動時讀取索引頁

當(dāng)MySQL啟動時，會讀取對部分表的索引。此時還未執(zhí)行任何語句，查看InnoDB讀取索引頁的次數(shù)大于0，查看InnoDB緩沖池中索引頁與記錄數(shù)大于0。

3.2. 執(zhí)行EXPLAIN語句時讀取索引頁

在執(zhí)行EXPLAIN語句時，可能會讀取相關(guān)表對應(yīng)的索引頁，若所需的索引頁數(shù)據(jù)已被讀取至緩沖池中，可能不會讀取。對于上述情況，可查看InnoDB讀取索引頁的次數(shù)，執(zhí)行EXPLAIN語句后次數(shù)會增加。

3.3. 執(zhí)行插入/查詢/更新/刪除語句時讀取索引頁

在執(zhí)行插入/查詢/更新/刪除語句時，可能會讀取相關(guān)表對應(yīng)的索引頁，若所需的索引頁數(shù)據(jù)已被讀取至緩沖池中，可能不會讀取。

4. 參考資料

以上參考的資料如下：https://dev.mysql.com/doc/refman/5.6/en/

---

原作者：adrninistrat0r
原文鏈接：MySQL 索引分析除了 EXPLAIN 還有什么方法？
原出處：csdn公眾號
侵刪

總結(jié)

以上是生活随笔為你收集整理的dev gridcontrol 根据数据获取索引_MySQL 索引分析除了 EXPLAIN 还有什么方法？的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。