mysql回表致索引失效案例講解
簡(jiǎn)介
mysql的innodb引擎查詢記錄時(shí)在無(wú)法使用索引覆蓋的場(chǎng)景下，需要做回表操作獲取記錄的所需字段。
mysql執(zhí)行sql前會(huì)執(zhí)行sql優(yōu)化、索引選擇等操作，mysql會(huì)預(yù)估各個(gè)索引所需要的查詢代價(jià)以及不走索引所需要的查詢代價(jià)，從中選擇一個(gè)mysql認(rèn)為代價(jià)最小的方式進(jìn)行sql查詢操作。而在回表數(shù)據(jù)量比較大時(shí)，經(jīng)常會(huì)出現(xiàn)mysql對(duì)回表操作查詢代價(jià)預(yù)估代價(jià)過(guò)大而導(dǎo)致索引使用錯(cuò)誤的情況。
案例
示例如下，在5.6版本的mysql、1CPU2G內(nèi)存的Linux環(huán)境下，新建一個(gè)測(cè)試表，并創(chuàng)建將近200萬(wàn)的記錄用于測(cè)試。
CREATE TABLE `salary_static` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵', `school_id` int(11) NOT NULL COMMENT '學(xué)校id', `student_id` int(11) NOT NULL COMMENT '畢業(yè)生id', `salary` int(11) NOT NULL DEFAULT '0' COMMENT '畢業(yè)薪水', `year` int(11) NOT NULL COMMENT '畢業(yè)年份', PRIMARY KEY (`id`), KEY `school_id_key` (`school_id`) USING BTREE, KEY `year_school_key` (`year`,`school_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='畢業(yè)生薪水?dāng)?shù)據(jù)統(tǒng)計(jì)';
delimiter // CREATE PROCEDURE init_salary_static() BEGIN DECLARE year INT; DECLARE schid INT; DECLARE stuid INT; SET year = 2000; WHILE year < 2020 DO START TRANSACTION; SET schid = 1; WHILE schid < 100 DO SET stuid = 1; WHILE stuid < 1000 DO insert into salary_static(school_id,student_id,salary,year) values (schid,stuid,floor(rand()*10000),year); SET stuid = stuid + 1; END WHILE; SET schid = schid + 1; END WHILE; SET year = year + 1; COMMIT; END WHILE; END // delimiter ; call init_salary_static();
測(cè)試數(shù)據(jù)創(chuàng)建完成后，執(zhí)行以下sql語(yǔ)句進(jìn)行統(tǒng)計(jì)查詢。
select school_id,avg(salary) from salary_static where year between 2016 and 2019 group by school_id;
預(yù)計(jì)該sql應(yīng)該使用year_school_key索引進(jìn)行查詢，但實(shí)際上通過(guò)explain命令可以發(fā)現(xiàn)，該sql使用的是school_id_key索引，并且由于使用了錯(cuò)誤的索引，該sql進(jìn)行了全表掃描導(dǎo)致查詢時(shí)間花費(fèi)了7秒。
強(qiáng)制使用year_school_key索引進(jìn)行查詢后發(fā)現(xiàn)，該sql的查詢時(shí)間花費(fèi)銳減到了0.6秒，比起school_id_key索引的時(shí)間減少了10倍。
select school_id,avg(salary) from salary_static force index(year_school_key) where year between 2015 and 2019 group by school_id;
分析
使用mysql的optimizer tracing（mysql5.6版本開(kāi)始支持）功能來(lái)分析sql的執(zhí)行計(jì)劃：
SET optimizer_trace="enabled=on"; select school_id,avg(salary) from salary_static where year between 2016 and 2019 group by school_id; SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
輸出的結(jié)果為一個(gè)json，展示了該sql在mysql內(nèi)部的sql優(yōu)化過(guò)程、索引選擇過(guò)程的執(zhí)行計(jì)劃。
重點(diǎn)關(guān)注執(zhí)行計(jì)劃的json中range_analysis下的內(nèi)容，這里展示了where范圍查詢過(guò)程中索引選擇。table_scan表示全表掃描，預(yù)估需要掃描1973546條記錄，但是由于全表掃描走聚集索引是順序IO讀，因此每條記錄的查詢成本很小，最終計(jì)算出來(lái)的查詢成本為399741。range_scan_alternatives表示使用索引的范圍查詢，year_school_key索引預(yù)估需要掃描812174條記錄，但是由于需要回表操作導(dǎo)致隨機(jī)IO讀，最終計(jì)算出來(lái)的查詢成本為974610。所以對(duì)于where查詢過(guò)程最終選擇全表掃描不走索引。
"range_analysis": {
"table_scan": {
"rows": 1973546,
"cost": 399741
},
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "school_id_key",
"usable": true,
"key_parts": [
"school_id",
"id"
]
},
{
"index": "year_school_key",
"usable": true,
"key_parts": [
"year",
"school_id",
"id"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_applicable_aggregate_function"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "year_school_key",
"ranges": [
"2016 <= year <= 2019"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 812174,
"cost": 974610,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
}
}
這里的查詢成本cost值完全可以手算出來(lái)，cost=I/O成本（每一次讀取記錄頁(yè)一次成本，每次成本為1.0）+CPU成本（每一條記錄一次成本，每次成本為0.2）。
全表掃描查詢成本
table_scan全表掃描時(shí)預(yù)估需要掃描1973546條記錄，通過(guò)show table status like "salary_static"命令可得全表記錄為82411520字節(jié)（Data_length），innodb每個(gè)記錄頁(yè)為16KB即全表掃描需要讀取82411520/1024/16 = 5030個(gè)記錄頁(yè)。
- I/O成本
5030 * 1.0 = 5030
- CPU成本
1973546 * 0.2 = 394709.2
- 合計(jì)查詢成本
5030 + 394709.2 = 399739.2
索引查詢成本
year_school_key索引時(shí)預(yù)估需要掃描812174條記錄，且使用該索引需要先通過(guò)索引查詢到rowId，然后通過(guò)rowId回表。mysql認(rèn)為每次回表均需要一次單獨(dú)的I/O成本
- CPU成本
812174 * 0.2 = 162434.8
- I/O成本
812174 * 1.0 = 812174
- 合計(jì)查詢成本
162434.8 + 812174 = 974608.8
接著再關(guān)注reconsidering_access_paths_for_index_ordering，表示最終對(duì)排序再進(jìn)行一次索引選擇優(yōu)化。這里選擇了school_id_key索引并且一票否決了上面where條件選擇的全表掃描："plan_changed": true，詳見(jiàn)group-by-optimization。
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "GROUP BY",
"index_order_summary": {
"table": "`salary_static`",
"index_provides_order": true,
"order_direction": "asc",
"index": "school_id_key",
"plan_changed": true,
"access_type": "index_scan"
}
}
}
事實(shí)上排序索引優(yōu)化也存在bug，詳見(jiàn)Bug＃93845。
優(yōu)化
通過(guò)分析sql執(zhí)行過(guò)程，可以發(fā)現(xiàn)選擇索引錯(cuò)誤的是因?yàn)閥ear_school_key索引回表記錄太多導(dǎo)致預(yù)估查詢成本大于全表掃描最終選擇了錯(cuò)誤的索引。
因此減少該sql的執(zhí)行時(shí)間，下一步的優(yōu)化方案是減少該sql的回表操作，即讓該sql進(jìn)行索引覆蓋。該sql涉及到的字段只有school_id、salary和year這3個(gè)字段，因此創(chuàng)建這3個(gè)索引的聯(lián)合索引，并注意這3個(gè)字段在聯(lián)合索引中的順序：where過(guò)濾語(yǔ)句最先執(zhí)行，所以year字段在聯(lián)合索引第一位；group by語(yǔ)句本質(zhì)上和order by一樣，因此排在where后面即聯(lián)合索引第二位；salary僅僅為了減少回表因此放在聯(lián)合索引末位。
CREATE INDEX year_school_salary_key ON salary_static (year, school_id, salary);
在創(chuàng)建了聯(lián)合索引后，再執(zhí)行sql語(yǔ)句后效果如下，僅花費(fèi)了0.2秒完成查詢，比起school_id_key索引的時(shí)間減少了35倍。
回表率計(jì)算
上述問(wèn)題為sql一次性查詢數(shù)量太多，導(dǎo)致回表代價(jià)太大。事實(shí)上，上述現(xiàn)象的臨界值完全可以計(jì)算出來(lái)：
假設(shè)一行記錄的大小為a字節(jié)，表的記錄數(shù)量為b，臨界記錄數(shù)量為c，則該表的記錄頁(yè)數(shù)量為b*a/1024/16
全表掃描的查詢成本 = I/O成本 + CPU成本 = b*a/1024/16 * 1.0 + b * 0.2 索引掃描的查詢成本 = I/O成本 + CPU成本 = c * 1.0 + c * 0.2 = c * 1.2 b*a/1024/16 * 1.0 + b * 0.2 = c * 1.2 臨界比例 = c/b = (a/1024/16 + 0.2)/1.2 = a * 5E-5 + 0.1667
即當(dāng)一條sql查詢超過(guò)表中超過(guò)大概17%的記錄且不能使用覆蓋索引時(shí)，會(huì)出現(xiàn)索引的回表代價(jià)太大而選擇全表掃描的現(xiàn)象。且這個(gè)比例隨著單行記錄的字節(jié)大小的增加而略微增大。
到此這篇關(guān)于mysql回表致索引失效案例講解的文章就介紹到這了,更多相關(guān)mysql回表致索引失效內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了優(yōu)雅地解決mysql深分頁(yè)問(wèn)題,本文將會(huì)討論當(dāng)mysql表大數(shù)據(jù)量的情況，如何優(yōu)化深分頁(yè)問(wèn)題，并附上最近的優(yōu)化慢sql問(wèn)題的案例偽代碼,需要的朋友可以參考下2022-12-12
mysql查詢過(guò)去24小時(shí)內(nèi)每小時(shí)數(shù)據(jù)量的方法(精確到分鐘)
我們經(jīng)常遇到類似這樣的需求,查詢最近N秒、N分鐘、N小時(shí)的數(shù)據(jù)及N天的數(shù)據(jù),下面這篇文章主要給大家介紹了關(guān)于mysql查詢過(guò)去24小時(shí)內(nèi)每小時(shí)數(shù)據(jù)量(精確到分鐘)的相關(guān)資料,需要的朋友可以參考下2023-03-03
完整的把MYSQL服務(wù)刪除的步驟。2009-06-06
MySQL 多表關(guān)聯(lián)一對(duì)多查詢實(shí)現(xiàn)取最新一條數(shù)據(jù)的方法示例
這篇文章主要介紹了MySQL 多表關(guān)聯(lián)一對(duì)多查詢實(shí)現(xiàn)取最新一條數(shù)據(jù)的方法,結(jié)合實(shí)例形式詳細(xì)分析了MySQL 多表關(guān)聯(lián)一對(duì)多查詢實(shí)現(xiàn)取最新一條數(shù)據(jù)相關(guān)原理、實(shí)現(xiàn)方法與操作注意事項(xiàng),需要的朋友可以參考下2020-05-05
mysql數(shù)據(jù)庫(kù)入門第一步之創(chuàng)建表
關(guān)于mysql介紹網(wǎng)上一搜一大堆,這里就不再介紹了,我之后的mysql文章只講最簡(jiǎn)單基礎(chǔ)的用法,主要是為java程序服務(wù)的.文中有非常詳細(xì)的圖文示例,需要的朋友可以參考下2021-05-05
mysql數(shù)據(jù)庫(kù)limit的四種用法小結(jié)
mysql數(shù)據(jù)庫(kù)中l(wèi)imit子句可以被用于強(qiáng)制select語(yǔ)句返回指定的記錄數(shù),本文主要介紹了mysql數(shù)據(jù)庫(kù)limit的四種用法小結(jié),感興趣的可以了解一下2023-10-10
這篇文章主要介紹了Mysql索引性能優(yōu)化問(wèn)題解決方案,文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-09-09
大家對(duì)join應(yīng)該都不會(huì)陌生,join可以將兩個(gè)表連接起來(lái),下面這篇文章主要給大家介紹了關(guān)于MySQL中JOIN連接用法的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-06-06
MySQL如何更改數(shù)據(jù)庫(kù)數(shù)據(jù)存儲(chǔ)目錄詳解
這篇文章主要給大家介紹了關(guān)于MySQL如何更改數(shù)據(jù)庫(kù)數(shù)據(jù)存儲(chǔ)目錄的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2018-11-11
在MySQL中,空格是一個(gè)特殊的字符,本文主要介紹了MySQL中的空格處理方法,具有一定的參考價(jià)值,感興趣的可以了解一下2023-11-11