據(jù)庫(kù)優(yōu)化實(shí)戰(zhàn)技巧)
1. 題目背景與考察要點(diǎn)解析最近在準(zhǔn)備華為OD技術(shù)面試的同學(xué)大概率會(huì)遇到數(shù)據(jù)庫(kù)相關(guān)的實(shí)戰(zhàn)題目。這類題目往往不是簡(jiǎn)單的語(yǔ)法考察而是聚焦實(shí)際業(yè)務(wù)場(chǎng)景中的典型問題處理能力。以數(shù)據(jù)庫(kù)Mysql - 1這個(gè)真題為例我們重點(diǎn)需要關(guān)注以下幾個(gè)核心能力復(fù)雜查詢構(gòu)建多表關(guān)聯(lián)時(shí)的性能優(yōu)化策略事務(wù)處理機(jī)制隔離級(jí)別與鎖機(jī)制的實(shí)戰(zhàn)應(yīng)用索引優(yōu)化技巧如何避免索引失效的常見陷阱分庫(kù)分表設(shè)計(jì)大數(shù)據(jù)量場(chǎng)景下的解決方案2. 典型真題場(chǎng)景還原2.1 訂單系統(tǒng)的查詢優(yōu)化假設(shè)題目給出一個(gè)電商系統(tǒng)的數(shù)據(jù)庫(kù)結(jié)構(gòu)用戶表(user)含1000萬條記錄訂單表(order)含1億條記錄商品表(product)含50萬條記錄要求實(shí)現(xiàn)查詢最近3個(gè)月消費(fèi)金額TOP100的用戶信息及其訂單明細(xì)。-- 典型錯(cuò)誤寫法面試常見扣分點(diǎn) SELECT * FROM user u JOIN order o ON u.id o.user_id JOIN product p ON o.product_id p.id WHERE o.create_time DATE_SUB(NOW(), INTERVAL 3 MONTH) ORDER BY o.amount DESC LIMIT 100;2.2 高效解決方案-- 優(yōu)化方案面試加分寫法 WITH temp_orders AS ( SELECT user_id, SUM(amount) as total_amount FROM order WHERE create_time DATE_SUB(NOW(), INTERVAL 3 MONTH) GROUP BY user_id ORDER BY total_amount DESC LIMIT 100 ) SELECT u.*, o.order_no, o.amount, p.product_name FROM temp_orders t JOIN user u ON t.user_id u.id JOIN order o ON u.id o.user_id JOIN product p ON o.product_id p.id WHERE o.create_time DATE_SUB(NOW(), INTERVAL 3 MONTH) ORDER BY t.total_amount DESC, o.create_time DESC;3. 技術(shù)要點(diǎn)深度剖析3.1 執(zhí)行計(jì)劃分析關(guān)鍵點(diǎn)使用EXPLAIN分析時(shí)要特別注意type列至少達(dá)到range級(jí)別最好能到refkey列必須命中復(fù)合索引如(create_time,user_id)rows列掃描行數(shù)應(yīng)控制在百萬級(jí)以下Extra列避免出現(xiàn)Using filesort和Using temporary3.2 索引設(shè)計(jì)黃金法則針對(duì)這個(gè)案例的最佳索引方案-- 訂單表核心索引 ALTER TABLE order ADD INDEX idx_user_time (user_id, create_time); ALTER TABLE order ADD INDEX idx_time_amount (create_time, amount); -- 用戶表主鍵索引 ALTER TABLE user MODIFY id BIGINT UNSIGNED PRIMARY KEY; -- 商品表覆蓋索引 ALTER TABLE product ADD INDEX idx_id_name (id, product_name);4. 高頻考點(diǎn)實(shí)戰(zhàn)錦囊4.1 事務(wù)隔離陷阱題題目可能要求設(shè)計(jì)一個(gè)庫(kù)存扣減方案保證高并發(fā)下不會(huì)超賣-- 正確實(shí)現(xiàn)方案 START TRANSACTION; -- 先鎖定記錄 SELECT stock FROM inventory WHERE product_id123 FOR UPDATE; -- 業(yè)務(wù)邏輯判斷 IF stock order_quantity THEN UPDATE inventory SET stockstock-order_quantity WHERE product_id123; COMMIT; ELSE ROLLBACK; RETURN 庫(kù)存不足; END IF;4.2 分頁(yè)查詢優(yōu)化當(dāng)面試官要求優(yōu)化深度分頁(yè)時(shí)-- 低效寫法偏移量大時(shí)性能急劇下降 SELECT * FROM order ORDER BY id LIMIT 1000000, 20; -- 優(yōu)化方案利用索引覆蓋主鍵定位 SELECT * FROM order WHERE id (SELECT id FROM order ORDER BY id LIMIT 1000000, 1) ORDER BY id LIMIT 20;5. 性能優(yōu)化實(shí)戰(zhàn)技巧5.1 慢查詢?nèi)罩痉治雠渲胢y.cnf開啟慢查詢監(jiān)控slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1使用mysqldumpslow工具分析mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log5.2 連接池配置要點(diǎn)建議的Druid連接池配置# 初始連接數(shù) initialSize5 # 最大連接數(shù) maxActive50 # 最小空閑連接 minIdle5 # 獲取連接超時(shí)時(shí)間(ms) maxWait60000 # 檢測(cè)空閑連接有效性 testWhileIdletrue # 檢測(cè)連接有效性SQL validationQuerySELECT 16. 面試實(shí)戰(zhàn)注意事項(xiàng)白板編碼規(guī)范先寫整體思路注釋關(guān)鍵字段要定義清晰數(shù)據(jù)類型JOIN條件必須顯式聲明問題回答策略遇到不熟悉的問題先拆解已知部分明確區(qū)分確定知道和合理推測(cè)可以適當(dāng)詢問業(yè)務(wù)場(chǎng)景細(xì)節(jié)性能優(yōu)化話術(shù)我會(huì)先通過EXPLAIN分析...考慮到數(shù)據(jù)量級(jí)建議...在真實(shí)環(huán)境中還需要考慮...7. 真實(shí)案例問題排查7.1 死鎖場(chǎng)景重現(xiàn)典型死鎖日志分析LATEST DETECTED DEADLOCK ------------------------ 2023-08-20 14:23:56 *** (1) TRANSACTION: TRANSACTION 1823, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 37, OS thread handle 139887582312192, query id 1234 localhost root updating UPDATE account SET balancebalance-100 WHERE user_id10 *** (2) TRANSACTION: TRANSACTION 1824, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 38, OS thread handle 139887581429504, query id 1235 localhost root updating UPDATE account SET balancebalance100 WHERE user_id20解決方案統(tǒng)一鎖獲取順序如按user_id升序減小事務(wù)粒度添加合適的索引減少鎖定范圍7.2 線上事故處理流程當(dāng)面試官問如何應(yīng)對(duì)數(shù)據(jù)庫(kù)CPU飆升時(shí)標(biāo)準(zhǔn)回答框架緊急處理通過show processlist定位問題會(huì)話對(duì)問題SQL執(zhí)行kill命令必要時(shí)重啟從庫(kù)原因分析檢查慢查詢?nèi)罩痉治霰O(jiān)控圖表QPS、連接數(shù)變化確認(rèn)是否有批量操作預(yù)防措施增加SQL審核流程完善監(jiān)控報(bào)警機(jī)制準(zhǔn)備限流降級(jí)方案8. 最新技術(shù)趨勢(shì)準(zhǔn)備華為OD面試可能會(huì)涉及云數(shù)據(jù)庫(kù)特性讀寫分離自動(dòng)路由分布式事務(wù)處理彈性擴(kuò)展能力新版本特性MySQL 8.0的窗口函數(shù)CTE遞歸查詢不可見索引華為云數(shù)據(jù)庫(kù)服務(wù)GaussDB架構(gòu)特點(diǎn)分布式SQL優(yōu)化與開源MySQL的兼容性建議準(zhǔn)備2-3個(gè)實(shí)際使用過的新特性案例避免只談概念。例如 我們?cè)陧?xiàng)目中使用了MySQL 8.0的JSON_TABLE函數(shù)來處理動(dòng)態(tài)表單數(shù)據(jù)相比原來的應(yīng)用層解析方案性能提升了40%...9. 模擬面試自測(cè)題檢驗(yàn)自己是否準(zhǔn)備好的方法能否在5分鐘內(nèi)手寫出三表關(guān)聯(lián)的優(yōu)化查詢能否說清楚B樹索引的底層原理能否解釋清楚MVCC的實(shí)現(xiàn)機(jī)制能否設(shè)計(jì)一個(gè)千萬級(jí)用戶系統(tǒng)的分庫(kù)方案能否說清楚redo log和binlog的區(qū)別建議用手機(jī)錄下自己的回答過程檢查技術(shù)表述是否準(zhǔn)確邏輯是否清晰連貫是否存在長(zhǎng)時(shí)間卡頓10. 推薦學(xué)習(xí)路徑基礎(chǔ)鞏固《高性能MySQL》第4、5、6章MySQL官方手冊(cè)InnoDB部分實(shí)戰(zhàn)提升leetcode數(shù)據(jù)庫(kù)題庫(kù)精練自己搭建百萬級(jí)測(cè)試數(shù)據(jù)擴(kuò)展視野阿里云數(shù)據(jù)庫(kù)最佳實(shí)踐美團(tuán)技術(shù)博客分布式DB文章華為云數(shù)據(jù)庫(kù)白皮書最后提醒面試前務(wù)必準(zhǔn)備好3-5個(gè)能體現(xiàn)技術(shù)深度的項(xiàng)目案例建議采用STAR法則Situation-Task-Action-Result來組織回答內(nèi)容。例如在我們的電商系統(tǒng)中遇到秒殺超賣問題Situation需要保證庫(kù)存準(zhǔn)確性Task我通過Redis分布式鎖MySQL樂觀鎖方案Action最終在5000QPS壓力下實(shí)現(xiàn)零超賣Result 華為OD面試必備：MySQL數(shù)據(jù)庫(kù)優(yōu)化實(shí)戰(zhàn)技巧
1. 題目背景與考察要點(diǎn)解析最近在準(zhǔn)備華為OD技術(shù)面試的同學(xué)大概率會(huì)遇到數(shù)據(jù)庫(kù)相關(guān)的實(shí)戰(zhàn)題目。這類題目往往不是簡(jiǎn)單的語(yǔ)法考察而是聚焦實(shí)際業(yè)務(wù)場(chǎng)景中的典型問題處理能力。以數(shù)據(jù)庫(kù)Mysql - 1這個(gè)真題為例我們重點(diǎn)需要關(guān)注以下幾個(gè)核心能力復(fù)雜查詢構(gòu)建多表關(guān)聯(lián)時(shí)的性能優(yōu)化策略事務(wù)處理機(jī)制隔離級(jí)別與鎖機(jī)制的實(shí)戰(zhàn)應(yīng)用索引優(yōu)化技巧如何避免索引失效的常見陷阱分庫(kù)分表設(shè)計(jì)大數(shù)據(jù)量場(chǎng)景下的解決方案2. 典型真題場(chǎng)景還原2.1 訂單系統(tǒng)的查詢優(yōu)化假設(shè)題目給出一個(gè)電商系統(tǒng)的數(shù)據(jù)庫(kù)結(jié)構(gòu)用戶表(user)含1000萬條記錄訂單表(order)含1億條記錄商品表(product)含50萬條記錄要求實(shí)現(xiàn)查詢最近3個(gè)月消費(fèi)金額TOP100的用戶信息及其訂單明細(xì)。-- 典型錯(cuò)誤寫法面試常見扣分點(diǎn) SELECT * FROM user u JOIN order o ON u.id o.user_id JOIN product p ON o.product_id p.id WHERE o.create_time DATE_SUB(NOW(), INTERVAL 3 MONTH) ORDER BY o.amount DESC LIMIT 100;2.2 高效解決方案-- 優(yōu)化方案面試加分寫法 WITH temp_orders AS ( SELECT user_id, SUM(amount) as total_amount FROM order WHERE create_time DATE_SUB(NOW(), INTERVAL 3 MONTH) GROUP BY user_id ORDER BY total_amount DESC LIMIT 100 ) SELECT u.*, o.order_no, o.amount, p.product_name FROM temp_orders t JOIN user u ON t.user_id u.id JOIN order o ON u.id o.user_id JOIN product p ON o.product_id p.id WHERE o.create_time DATE_SUB(NOW(), INTERVAL 3 MONTH) ORDER BY t.total_amount DESC, o.create_time DESC;3. 技術(shù)要點(diǎn)深度剖析3.1 執(zhí)行計(jì)劃分析關(guān)鍵點(diǎn)使用EXPLAIN分析時(shí)要特別注意type列至少達(dá)到range級(jí)別最好能到refkey列必須命中復(fù)合索引如(create_time,user_id)rows列掃描行數(shù)應(yīng)控制在百萬級(jí)以下Extra列避免出現(xiàn)Using filesort和Using temporary3.2 索引設(shè)計(jì)黃金法則針對(duì)這個(gè)案例的最佳索引方案-- 訂單表核心索引 ALTER TABLE order ADD INDEX idx_user_time (user_id, create_time); ALTER TABLE order ADD INDEX idx_time_amount (create_time, amount); -- 用戶表主鍵索引 ALTER TABLE user MODIFY id BIGINT UNSIGNED PRIMARY KEY; -- 商品表覆蓋索引 ALTER TABLE product ADD INDEX idx_id_name (id, product_name);4. 高頻考點(diǎn)實(shí)戰(zhàn)錦囊4.1 事務(wù)隔離陷阱題題目可能要求設(shè)計(jì)一個(gè)庫(kù)存扣減方案保證高并發(fā)下不會(huì)超賣-- 正確實(shí)現(xiàn)方案 START TRANSACTION; -- 先鎖定記錄 SELECT stock FROM inventory WHERE product_id123 FOR UPDATE; -- 業(yè)務(wù)邏輯判斷 IF stock order_quantity THEN UPDATE inventory SET stockstock-order_quantity WHERE product_id123; COMMIT; ELSE ROLLBACK; RETURN 庫(kù)存不足; END IF;4.2 分頁(yè)查詢優(yōu)化當(dāng)面試官要求優(yōu)化深度分頁(yè)時(shí)-- 低效寫法偏移量大時(shí)性能急劇下降 SELECT * FROM order ORDER BY id LIMIT 1000000, 20; -- 優(yōu)化方案利用索引覆蓋主鍵定位 SELECT * FROM order WHERE id (SELECT id FROM order ORDER BY id LIMIT 1000000, 1) ORDER BY id LIMIT 20;5. 性能優(yōu)化實(shí)戰(zhàn)技巧5.1 慢查詢?nèi)罩痉治雠渲胢y.cnf開啟慢查詢監(jiān)控slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1使用mysqldumpslow工具分析mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log5.2 連接池配置要點(diǎn)建議的Druid連接池配置# 初始連接數(shù) initialSize5 # 最大連接數(shù) maxActive50 # 最小空閑連接 minIdle5 # 獲取連接超時(shí)時(shí)間(ms) maxWait60000 # 檢測(cè)空閑連接有效性 testWhileIdletrue # 檢測(cè)連接有效性SQL validationQuerySELECT 16. 面試實(shí)戰(zhàn)注意事項(xiàng)白板編碼規(guī)范先寫整體思路注釋關(guān)鍵字段要定義清晰數(shù)據(jù)類型JOIN條件必須顯式聲明問題回答策略遇到不熟悉的問題先拆解已知部分明確區(qū)分確定知道和合理推測(cè)可以適當(dāng)詢問業(yè)務(wù)場(chǎng)景細(xì)節(jié)性能優(yōu)化話術(shù)我會(huì)先通過EXPLAIN分析...考慮到數(shù)據(jù)量級(jí)建議...在真實(shí)環(huán)境中還需要考慮...7. 真實(shí)案例問題排查7.1 死鎖場(chǎng)景重現(xiàn)典型死鎖日志分析LATEST DETECTED DEADLOCK ------------------------ 2023-08-20 14:23:56 *** (1) TRANSACTION: TRANSACTION 1823, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 37, OS thread handle 139887582312192, query id 1234 localhost root updating UPDATE account SET balancebalance-100 WHERE user_id10 *** (2) TRANSACTION: TRANSACTION 1824, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 38, OS thread handle 139887581429504, query id 1235 localhost root updating UPDATE account SET balancebalance100 WHERE user_id20解決方案統(tǒng)一鎖獲取順序如按user_id升序減小事務(wù)粒度添加合適的索引減少鎖定范圍7.2 線上事故處理流程當(dāng)面試官問如何應(yīng)對(duì)數(shù)據(jù)庫(kù)CPU飆升時(shí)標(biāo)準(zhǔn)回答框架緊急處理通過show processlist定位問題會(huì)話對(duì)問題SQL執(zhí)行kill命令必要時(shí)重啟從庫(kù)原因分析檢查慢查詢?nèi)罩痉治霰O(jiān)控圖表QPS、連接數(shù)變化確認(rèn)是否有批量操作預(yù)防措施增加SQL審核流程完善監(jiān)控報(bào)警機(jī)制準(zhǔn)備限流降級(jí)方案8. 最新技術(shù)趨勢(shì)準(zhǔn)備華為OD面試可能會(huì)涉及云數(shù)據(jù)庫(kù)特性讀寫分離自動(dòng)路由分布式事務(wù)處理彈性擴(kuò)展能力新版本特性MySQL 8.0的窗口函數(shù)CTE遞歸查詢不可見索引華為云數(shù)據(jù)庫(kù)服務(wù)GaussDB架構(gòu)特點(diǎn)分布式SQL優(yōu)化與開源MySQL的兼容性建議準(zhǔn)備2-3個(gè)實(shí)際使用過的新特性案例避免只談概念。例如 我們?cè)陧?xiàng)目中使用了MySQL 8.0的JSON_TABLE函數(shù)來處理動(dòng)態(tài)表單數(shù)據(jù)相比原來的應(yīng)用層解析方案性能提升了40%...9. 模擬面試自測(cè)題檢驗(yàn)自己是否準(zhǔn)備好的方法能否在5分鐘內(nèi)手寫出三表關(guān)聯(lián)的優(yōu)化查詢能否說清楚B樹索引的底層原理能否解釋清楚MVCC的實(shí)現(xiàn)機(jī)制能否設(shè)計(jì)一個(gè)千萬級(jí)用戶系統(tǒng)的分庫(kù)方案能否說清楚redo log和binlog的區(qū)別建議用手機(jī)錄下自己的回答過程檢查技術(shù)表述是否準(zhǔn)確邏輯是否清晰連貫是否存在長(zhǎng)時(shí)間卡頓10. 推薦學(xué)習(xí)路徑基礎(chǔ)鞏固《高性能MySQL》第4、5、6章MySQL官方手冊(cè)InnoDB部分實(shí)戰(zhàn)提升leetcode數(shù)據(jù)庫(kù)題庫(kù)精練自己搭建百萬級(jí)測(cè)試數(shù)據(jù)擴(kuò)展視野阿里云數(shù)據(jù)庫(kù)最佳實(shí)踐美團(tuán)技術(shù)博客分布式DB文章華為云數(shù)據(jù)庫(kù)白皮書最后提醒面試前務(wù)必準(zhǔn)備好3-5個(gè)能體現(xiàn)技術(shù)深度的項(xiàng)目案例建議采用STAR法則Situation-Task-Action-Result來組織回答內(nèi)容。例如在我們的電商系統(tǒng)中遇到秒殺超賣問題Situation需要保證庫(kù)存準(zhǔn)確性Task我通過Redis分布式鎖MySQL樂觀鎖方案Action最終在5000QPS壓力下實(shí)現(xiàn)零超賣Result