您好,登錄后才能下訂單哦!
這篇文章主要講解了“mysql索引失效的原因是什么”,文中的講解內(nèi)容簡單清晰,易于學(xué)習(xí)與理解,下面請(qǐng)大家跟著小編的思路慢慢深入,一起來研究和學(xué)習(xí)“mysql索引失效的原因是什么”吧!
mysql的innodb引擎查詢記錄時(shí)在無法使用索引覆蓋的場景下,需要做回表操作獲取記錄的所需字段。
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à)過大而導(dǎo)致索引使用錯(cuò)誤的情況。
示例如下,在5.6版本的mysql、1CPU2G內(nèi)存的Linux環(huán)境下,新建一個(gè)測試表,并創(chuàng)建將近200萬的記錄用于測試。
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();
測試數(shù)據(jù)創(chuàng)建完成后,執(zhí)行以下sql語句進(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í)際上通過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版本開始支持)功能來分析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)化過程、索引選擇過程的執(zhí)行計(jì)劃。
重點(diǎn)關(guān)注執(zhí)行計(jì)劃的json中range_analysis下的內(nèi)容,這里展示了where范圍查詢過程中索引選擇。table_scan表示全表掃描,預(yù)估需要掃描1973546條記錄,但是由于全表掃描走聚集索引是順序IO讀,因此每條記錄的查詢成本很小,最終計(jì)算出來的查詢成本為399741。range_scan_alternatives表示使用索引的范圍查詢,year_school_key索引預(yù)估需要掃描812174條記錄,但是由于需要回表操作導(dǎo)致隨機(jī)IO讀,最終計(jì)算出來的查詢成本為974610。所以對(duì)于where查詢過程最終選擇全表掃描不走索引。
"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值完全可以手算出來,cost=I/O成本(每一次讀取記錄頁一次成本,每次成本為1.0)+CPU成本(每一條記錄一次成本,每次成本為0.2)。
table_scan全表掃描時(shí)預(yù)估需要掃描1973546條記錄,通過show table status like "salary_static"命令可得全表記錄為82411520字節(jié)(Data_length),innodb每個(gè)記錄頁為16KB即全表掃描需要讀取82411520/1024/16 = 5030個(gè)記錄頁。
I/O成本
5030 * 1.0 = 5030
CPU成本
1973546 * 0.2 = 394709.2
合計(jì)查詢成本
5030 + 394709.2 = 399739.2
year_school_key索引時(shí)預(yù)估需要掃描812174條記錄,且使用該索引需要先通過索引查詢到rowId,然后通過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,詳見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,詳見Bug#93845。
通過分析sql執(zhí)行過程,可以發(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過濾語句最先執(zhí)行,所以year字段在聯(lián)合索引第一位;group by語句本質(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語句后效果如下,僅花費(fèi)了0.2秒完成查詢,比起school_id_key索引的時(shí)間減少了35倍。
上述問題為sql一次性查詢數(shù)量太多,導(dǎo)致回表代價(jià)太大。事實(shí)上,上述現(xiàn)象的臨界值完全可以計(jì)算出來:
假設(shè)一行記錄的大小為a字節(jié),表的記錄數(shù)量為b,臨界記錄數(shù)量為c,則該表的記錄頁數(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查詢超過表中超過大概17%的記錄且不能使用覆蓋索引時(shí),會(huì)出現(xiàn)索引的回表代價(jià)太大而選擇全表掃描的現(xiàn)象。且這個(gè)比例隨著單行記錄的字節(jié)大小的增加而略微增大。
感謝各位的閱讀,以上就是“mysql索引失效的原因是什么”的內(nèi)容了,經(jīng)過本文的學(xué)習(xí)后,相信大家對(duì)mysql索引失效的原因是什么這一問題有了更深刻的體會(huì),具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云,小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章,歡迎關(guān)注!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。