我艸，MySQL數(shù)據(jù)量大時，delete操作無法命中索引。
來自：Java面試那些事兒
最近，在脈脈上看到一個樓主提出的問題：MySQL數(shù)據(jù)量大時，delete操作無法命中索引；并且還附上了相關(guān)案例截圖。
最終，樓主通過開啟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 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)注一下：
長按訂閱更多精彩▼
如有收獲，點個在看，誠摯感謝
免責(zé)聲明：本文內(nèi)容由21ic獲得授權(quán)后發(fā)布，版權(quán)歸原作者所有，本平臺僅提供信息存儲服務(wù)。文章僅代表作者個人觀點，不代表本平臺立場，如有問題，請聯(lián)系我們，謝謝！
下載文檔
