當(dāng)前位置:首頁 > 公眾號精選 > 架構(gòu)師社區(qū)
[導(dǎo)讀]來自:Java面試那些事兒 最近,在脈脈上看到一個樓主提出的問題:MySQL數(shù)據(jù)量大時,delete操作無法命中索引;并且還附上了相關(guān)案例截圖。 最終,樓主通過開啟MySQL分析優(yōu)化器追蹤,定位到是優(yōu)化器搞的鬼,它覺得花費(fèi)時間太長。因為我這個是測試數(shù)據(jù),究其原因


我艸,MySQL數(shù)據(jù)量大時,delete操作無法命中索引。

來自:Java面試那些事兒


最近,在脈脈上看到一個樓主提出的問題:MySQL數(shù)據(jù)量大時,delete操作無法命中索引;并且還附上了相關(guān)案例截圖。

我艸,MySQL數(shù)據(jù)量大時,delete操作無法命中索引。

我艸,MySQL數(shù)據(jù)量大時,delete操作無法命中索引。

我艸,MySQL數(shù)據(jù)量大時,delete操作無法命中索引。

我艸,MySQL數(shù)據(jù)量大時,delete操作無法命中索引。

最終,樓主通過開啟MySQL分析優(yōu)化器追蹤,定位到是優(yōu)化器搞的鬼,它覺得花費(fèi)時間太長。因為我這個是測試數(shù)據(jù),究其原因是因為數(shù)據(jù)傾斜,導(dǎo)致計算出的數(shù)據(jù)占比較大、花費(fèi)時間長。


大家要記住一點,一條SQL語句走哪條索引是通過其中的優(yōu)化器代價分析兩個部分來決定的。所以,隨著數(shù)據(jù)的不斷變化,最優(yōu)解也要跟著變化。因此,就需要DBA來不斷的優(yōu)化SQL。


對于查詢情況,其實MySQL提供給我們一個功能來引導(dǎo)優(yōu)化器更好的優(yōu)化,那便是MySQL的查詢優(yōu)化提示(Query Optimizer Hints)。比如,想讓SQL強(qiáng)制走索引的話,可以使用 FORCE INDEX 或者USE INDEX;它們基本相同,不同點:在于就算索引的實際用處不大,F(xiàn)ORCE INDEX也得要使用索引。

EXPLAIN SELECT * FROM yp_user FORCE INDEX(idx_gender) where gender=1 ;

同樣,你也可以通過IGNORE INDEX來忽略索引。

EXPLAIN SELECT * FROM yp_user IGNORE INDEX(idx_gender) where gender=1 ;

在我看來,雖然有MySQL Hints這種好用的工具,但我建議還是不要再生產(chǎn)環(huán)境使用,因為當(dāng)數(shù)據(jù)量增長時,你壓根兒都不知道這種索引的方式是否還適應(yīng)于當(dāng)前的環(huán)境,還是得配合DBA從索引的結(jié)構(gòu)上去優(yōu)化。


接下來,我來教大家如何用MySQL的trace分析優(yōu)化器是如何選擇執(zhí)行計劃的?很重要的手段,建議多實戰(zhàn)一下。


1、什么是Trace?


關(guān)于這個問題,我覺得去最好的描述是官方文檔。

我艸,MySQL數(shù)據(jù)量大時,delete操作無法命中索引。

在MySQL 5.6中,MySQL優(yōu)化器增加了一個新的跟蹤功能。該接口由一組optimizer_trace_xxx系統(tǒng)變量和INFORMATION_SCHEMA.OPTIMIZER_TRACE表提供,但可能會發(fā)生變化。

通俗點,就是通過trace文件能夠進(jìn)一步了解為什么優(yōu)化器選擇A執(zhí)行計劃而不選擇B執(zhí)行計劃,幫助我們更好的理解優(yōu)化器的行為。


2、如何使用?


還是得看官方文檔。

# 查看優(yōu)化器跟蹤是否狀態(tài)SHOW VARIABLES LIKE '%optimizer_trace%';# 開啟tracing (默認(rèn)是關(guān)閉的):SET optimizer_trace="enabled=on";# 你的查詢語句SELECT ...; # 查詢trace json文件SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;# 當(dāng)完成后,關(guān)閉traceSET optimizer_trace="enabled=off";


3、分析trace文件


根據(jù)我本地的一個例子為例,具體文件內(nèi)容如下。

SELECT * FROM yp_user where gender=1 | { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `yp_user`.`open_id` AS `open_id`,`yp_user`.`avatar_url` AS `avatar_url`,`yp_user`.`city` AS `city`,`yp_user`.`country` AS `country`,`yp_user`.`create_time` AS `create_time`,`yp_user`.`gender` AS `gender`,`yp_user`.`language` AS `language`,`yp_user`.`nick_name` AS `nick_name`,`yp_user`.`province` AS `province`,`yp_user`.`skey` AS `skey`,`yp_user`.`update_time` AS `update_time`,`yp_user`.`privilege` AS `privilege` from `yp_user` where (`yp_user`.`gender` = 1)" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`yp_user`.`gender` = 1)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal(1, `yp_user`.`gender`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal(1, `yp_user`.`gender`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(1, `yp_user`.`gender`)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`yp_user`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`yp_user`", "field": "gender", "equals": "1", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": "`yp_user`", "range_analysis": { "table_scan": { "rows": 3100, "cost": 719.1 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "idx_skey", "usable": false, "cause": "not_applicable" }, { "index": "idx_gender", "usable": true, "key_parts": [ "gender", "open_id" ] } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "idx_gender", "ranges": [ "1 <= gender <= 1" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 2731, "cost": 3278.2, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`yp_user`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "idx_gender", "rows": 2731, "cost": 837.2, "chosen": true }, { "rows_to_scan": 3100, "access_type": "scan", "resulting_rows": 3100, "cost": 717, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 3100, "cost_for_plan": 717, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`yp_user`.`gender` = 1)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`yp_user`", "attached": "(`yp_user`.`gender` = 1)" } ] } }, { "refine_plan": [ { "table": "`yp_user`" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ ] } } ]}

通過這個例子,我們可以得到全表掃描的代價如下。

"table_scan": { "rows": 3100, "cost": 719.1}

分析結(jié)果:全表掃描訪問的rows記錄為3100,代價cost計算為719.1。


索引掃描的代價如下。

"range_scan_alternatives": [ { "index": "idx_gender", "ranges": [ "1 <= gender <= 1" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 2731, "cost": 3278.2, "chosen": false, "cause": "cost" }]

分析結(jié)果:這里看到了通過idx_gender索引過濾時,優(yōu)化器預(yù)估需要返回2731記錄,訪問代價cost為3278.2,大于全表掃描代價719.1;因此,優(yōu)化器傾向于選擇全表掃描。


今晚上就熬夜寫到這里吧。


-----------------------------

特別推薦一個分享架構(gòu)+算法的優(yōu)質(zhì)內(nèi)容,還沒關(guān)注的小伙伴,可以長按關(guān)注一下:

我艸,MySQL數(shù)據(jù)量大時,delete操作無法命中索引。

長按訂閱更多精彩▼

我艸,MySQL數(shù)據(jù)量大時,delete操作無法命中索引。

如有收獲,點個在看,誠摯感謝

免責(zé)聲明:本文內(nèi)容由21ic獲得授權(quán)后發(fā)布,版權(quán)歸原作者所有,本平臺僅提供信息存儲服務(wù)。文章僅代表作者個人觀點,不代表本平臺立場,如有問題,請聯(lián)系我們,謝謝!

本站聲明: 本文章由作者或相關(guān)機(jī)構(gòu)授權(quán)發(fā)布,目的在于傳遞更多信息,并不代表本站贊同其觀點,本站亦不保證或承諾內(nèi)容真實性等。需要轉(zhuǎn)載請聯(lián)系該專欄作者,如若文章內(nèi)容侵犯您的權(quán)益,請及時聯(lián)系本站刪除。
換一批
延伸閱讀

9月2日消息,不造車的華為或?qū)⒋呱龈蟮莫毥谦F公司,隨著阿維塔和賽力斯的入局,華為引望愈發(fā)顯得引人矚目。

關(guān)鍵字: 阿維塔 塞力斯 華為

加利福尼亞州圣克拉拉縣2024年8月30日 /美通社/ -- 數(shù)字化轉(zhuǎn)型技術(shù)解決方案公司Trianz今天宣布,該公司與Amazon Web Services (AWS)簽訂了...

關(guān)鍵字: AWS AN BSP 數(shù)字化

倫敦2024年8月29日 /美通社/ -- 英國汽車技術(shù)公司SODA.Auto推出其旗艦產(chǎn)品SODA V,這是全球首款涵蓋汽車工程師從創(chuàng)意到認(rèn)證的所有需求的工具,可用于創(chuàng)建軟件定義汽車。 SODA V工具的開發(fā)耗時1.5...

關(guān)鍵字: 汽車 人工智能 智能驅(qū)動 BSP

北京2024年8月28日 /美通社/ -- 越來越多用戶希望企業(yè)業(yè)務(wù)能7×24不間斷運(yùn)行,同時企業(yè)卻面臨越來越多業(yè)務(wù)中斷的風(fēng)險,如企業(yè)系統(tǒng)復(fù)雜性的增加,頻繁的功能更新和發(fā)布等。如何確保業(yè)務(wù)連續(xù)性,提升韌性,成...

關(guān)鍵字: 亞馬遜 解密 控制平面 BSP

8月30日消息,據(jù)媒體報道,騰訊和網(wǎng)易近期正在縮減他們對日本游戲市場的投資。

關(guān)鍵字: 騰訊 編碼器 CPU

8月28日消息,今天上午,2024中國國際大數(shù)據(jù)產(chǎn)業(yè)博覽會開幕式在貴陽舉行,華為董事、質(zhì)量流程IT總裁陶景文發(fā)表了演講。

關(guān)鍵字: 華為 12nm EDA 半導(dǎo)體

8月28日消息,在2024中國國際大數(shù)據(jù)產(chǎn)業(yè)博覽會上,華為常務(wù)董事、華為云CEO張平安發(fā)表演講稱,數(shù)字世界的話語權(quán)最終是由生態(tài)的繁榮決定的。

關(guān)鍵字: 華為 12nm 手機(jī) 衛(wèi)星通信

要點: 有效應(yīng)對環(huán)境變化,經(jīng)營業(yè)績穩(wěn)中有升 落實提質(zhì)增效舉措,毛利潤率延續(xù)升勢 戰(zhàn)略布局成效顯著,戰(zhàn)新業(yè)務(wù)引領(lǐng)增長 以科技創(chuàng)新為引領(lǐng),提升企業(yè)核心競爭力 堅持高質(zhì)量發(fā)展策略,塑強(qiáng)核心競爭優(yōu)勢...

關(guān)鍵字: 通信 BSP 電信運(yùn)營商 數(shù)字經(jīng)濟(jì)

北京2024年8月27日 /美通社/ -- 8月21日,由中央廣播電視總臺與中國電影電視技術(shù)學(xué)會聯(lián)合牽頭組建的NVI技術(shù)創(chuàng)新聯(lián)盟在BIRTV2024超高清全產(chǎn)業(yè)鏈發(fā)展研討會上宣布正式成立。 活動現(xiàn)場 NVI技術(shù)創(chuàng)新聯(lián)...

關(guān)鍵字: VI 傳輸協(xié)議 音頻 BSP

北京2024年8月27日 /美通社/ -- 在8月23日舉辦的2024年長三角生態(tài)綠色一體化發(fā)展示范區(qū)聯(lián)合招商會上,軟通動力信息技術(shù)(集團(tuán))股份有限公司(以下簡稱"軟通動力")與長三角投資(上海)有限...

關(guān)鍵字: BSP 信息技術(shù)
關(guān)閉
關(guān)閉