2025年程序員sql面試題庫及答案請描述SELECT語句中各子句的執(zhí)行順序，并說明WHERE與HAVING的區(qū)別。SELECT語句的實際執(zhí)行順序與書寫順序不同，核心順序為：FROM→ON（處理JOIN條件）→JOIN（合并表）→WHERE（過濾行）→GROUPBY（分組）→WITHCUBE/ROLLUP（分組擴展）→HAVING（過濾分組）→SELECT（選擇列）→DISTINCT（去重）→ORDERBY（排序）→LIMIT（限制行數(shù)）。WHERE與HAVING的本質區(qū)別在于作用階段：WHERE在分組前過濾原始數(shù)據(jù)行，不能引用聚合函數(shù)；HAVING在分組后過濾分組結果，可以引用聚合函數(shù)。例如統(tǒng)計各部門平均薪資時，WHERE用于篩選特定部門（如部門ID=10），HAVING用于篩選平均薪資高于10000的部門（如AVG(salary)>10000）。索引失效的常見場景有哪些？如何避免？索引失效的典型場景包括：①對索引列使用函數(shù)或表達式（如WHEREDATE(create_time)=’2024-01-01’）；②索引列存在隱式類型轉換（如VARCHAR字段與數(shù)字直接比較）；③LIKE查詢以通配符開頭（如WHEREnameLIKE‘%張%’）；④OR條件中部分列未建立索引（如WHEREid=1ORname=’張三’且僅id有索引）；⑤聯(lián)合索引未遵循最左匹配原則（如索引為(col1,col2)，但查詢僅用col2）；⑥數(shù)據(jù)分布極端（如性別字段僅有兩個值，索引效率低）。避免方法：①將函數(shù)/表達式移到等號另一側（如WHEREcreate_time>=’2024-01-01’ANDcreate_time<’2024-01-02’）；②確保查詢條件與索引列類型一致；③左模糊查詢改用全文索引（如MySQL的FULLTEXT）；④OR條件轉換為UNION（如WHEREid=1UNIONWHEREname=’張三’）；⑤聯(lián)合索引按高頻查詢順序設計（如常用col1+col2查詢則索引為(col1,col2)）；⑥對低區(qū)分度字段避免單獨索引，可組合其他列建立復合索引。簡述事務的ACID特性，并說明MySQL中InnoDB如何實現(xiàn)原子性與持久性。ACID指原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、持久性（Durability）。原子性保證事務要么全執(zhí)行要么全回滾；一致性確保事務前后數(shù)據(jù)符合業(yè)務規(guī)則；隔離性控制多事務間的可見性；持久性保證提交的事務不丟失。InnoDB通過undolog實現(xiàn)原子性：事務執(zhí)行時記錄每行修改前的舊值，若事務回滾則利用undolog恢復數(shù)據(jù)。持久性通過redolog實現(xiàn)：事務提交時，先將redolog寫入磁盤（WAL預寫日志機制），再更新內存中的數(shù)據(jù)頁。即使數(shù)據(jù)庫崩潰，重啟后可通過redolog重新執(zhí)行已提交但未刷盤的事務，保證數(shù)據(jù)不丟失。使用窗口函數(shù)計算每個部門中薪資排名前3的員工，要求顯示部門ID、員工姓名、薪資、部門內排名。假設表結構為employees（emp_idINT,dept_idINT,nameVARCHAR(20),salaryDECIMAL(10,2)），SQL示例如下：```sqlSELECTdept_id,name,salary,DENSE_RANK()OVER(PARTITIONBYdept_idORDERBYsalaryDESC)ASrankFROMemployeesQUALIFYrank<=3;-部分數(shù)據(jù)庫（如PostgreSQL）支持QUALIFY，MySQL需嵌套查詢-MySQL替代方案：SELECTdept_id,name,salary,rankFROM(SELECTdept_id,name,salary,DENSE_RANK()OVER(PARTITIONBYdept_idORDERBYsalaryDESC)ASrankFROMemployees)AStWHERErank<=3;```注：RANK()會跳過重復排名（如兩人并列第1則下一人為第3），DENSE_RANK()則連續(xù)（并列第1后為第2），需根據(jù)需求選擇。INNERJOIN、LEFTJOIN、RIGHTJOIN、FULLOUTERJOIN的區(qū)別是什么？當關聯(lián)表存在重復鍵時，結果集數(shù)量會如何變化？INNERJOIN返回兩表中關聯(lián)鍵匹配的行；LEFTJOIN返回左表所有行，右表無匹配時補NULL；RIGHTJOIN與LEFTJOIN相反；FULLOUTERJOIN返回兩表所有行，無匹配時補NULL（MySQL不直接支持，需用LEFTJOINUNIONRIGHTJOIN實現(xiàn)）。若兩表關聯(lián)鍵存在重復值（如左表有2行dept_id=10，右表有3行dept_id=10），則INNERJOIN會產生2×3=6行（笛卡爾積），LEFT/RIGHT/FULLJOIN同理。因此設計表結構時應確保關聯(lián)鍵（如外鍵）的唯一性，避免因重復鍵導致結果集膨脹。普通子查詢與CTE（公共表表達式）的區(qū)別是什么？在什么場景下優(yōu)先使用CTE？普通子查詢是嵌套在主查詢中的SELECT語句，需重復編寫或通過臨時表存儲；CTE通過WITH子句定義，可在后續(xù)查詢中多次引用。主要區(qū)別：①可讀性：CTE命名后更易理解復雜查詢；②遞歸支持：CTE可定義遞歸查詢（如查詢層級部門關系）；③作用域：CTE僅在當前查詢有效，臨時表需顯式創(chuàng)建/刪除；④性能：部分數(shù)據(jù)庫（如PostgreSQL）會優(yōu)化CTE為子查詢，MySQL8.0+支持物化CTE（可重用結果）。優(yōu)先使用CTE的場景：①多層嵌套查詢（如三級子查詢）；②遞歸查詢（如組織架構樹）；③需要多次引用同一數(shù)據(jù)集（如同時計算總和與平均值）；④提升代碼可維護性（通過命名CTE明確業(yè)務含義）。執(zhí)行EXPLAIN后，哪些關鍵字段需要重點關注？如何根據(jù)這些字段優(yōu)化查詢？EXPLAIN輸出的關鍵字段及優(yōu)化策略：id：查詢的執(zhí)行順序，id相同則從上到下執(zhí)行，id越大優(yōu)先級越高。若存在多個不同id，可能需調整查詢結構減少嵌套。type：訪問類型，從優(yōu)到劣為system>const>eq_ref>ref>range>index>ALL。若type為ALL（全表掃描），需為WHERE條件列添加索引；若為index（索引全掃描），檢查是否使用覆蓋索引。key：實際使用的索引，NULL表示未使用索引。若key與預期不符，可能是索引未命中（如類型不匹配）。rows：估計掃描的行數(shù)，數(shù)值越大性能越差。通過優(yōu)化索引或縮小查詢范圍（如添加時間條件）減少rows。Extra：額外信息，常見問題及處理：Usingfilesort：需為ORDERBY列添加索引（按排序順序）。Usingtemporary：GROUPBY或ORDERBY涉及多列，需調整索引為(分組列,排序列)。Usingwhere：未使用索引過濾，需檢查WHERE條件是否可優(yōu)化。Usingindex：使用覆蓋索引，無需回表，為理想狀態(tài)。設計一個訂單系統(tǒng)，包含用戶表、商品表、訂單表、訂單詳情表，要求說明各表字段及關聯(lián)關系。表結構設計如下（以MySQL為例）：用戶表（users）：user_id（主鍵，INT自增）、username（VARCHAR(50)，唯一）、mobile（VARCHAR(11)，唯一）、create_time（TIMESTAMP，默認當前時間）。商品表（products）：product_id（主鍵，INT自增）、product_name（VARCHAR(100)）、price（DECIMAL(10,2)）、stock（INT，庫存）、category（VARCHAR(20)）。訂單表（orders）：order_id（主鍵，BIGINT自增）、user_id（外鍵，INT，關聯(lián)users.user_id）、total_amount（DECIMAL(12,2)，總金額）、status（TINYINT，狀態(tài)：1未支付/2已支付/3已發(fā)貨）、create_time（TIMESTAMP）、pay_time（TIMESTAMP，可NULL）。訂單詳情表（order_items）：item_id（主鍵，BIGINT自增）、order_id（外鍵，BIGINT，關聯(lián)orders.order_id）、product_id（外鍵，INT，關聯(lián)duct_id）、quantity（INT，購買數(shù)量）、unit_price（DECIMAL(10,2)，下單時單價）。關聯(lián)關系：訂單表通過user_id關聯(lián)用戶表（一對多，一個用戶多個訂單）；訂單詳情表通過order_id關聯(lián)訂單表（一對多，一個訂單多個商品），通過product_id關聯(lián)商品表（多對一，多個訂單詳情對應一個商品）。如何用SQL實現(xiàn)行列轉換？以學提供績表（學生ID、科目、成績）為例，轉換為（學生ID、語文、數(shù)學、英語）的格式。假設成績表結構為scores（student_idINT,subjectVARCHAR(10),scoreDECIMAL(5,2)），目標是將每個學生的各科成績轉為列。通用解法（支持MySQL、PostgreSQL）使用CASEWHEN+聚合函數(shù)：```sqlSELECTstudent_id,MAX(CASEWHENsubject='語文'THENscoreEND)AS語文,MAX(CASEWHENsubject='數(shù)學'THENscoreEND)AS數(shù)學,MAX(CASEWHENsubject='英語'THENscoreEND)AS英語FROMscoresGROUPBYstudent_id;```若數(shù)據(jù)庫支持PIVOT（如SQLServer、Oracle），可簡化為：```sqlSELECTstudent_id,語文,數(shù)學,英語FROMscoresPIVOT(MAX(score)FORsubjectIN(語文,數(shù)學,英語))ASpvt;```注：需確?？颇苛校╯ubject）的值包含所有目標列（如“語文”“數(shù)學”“英語”），否則缺失科目會顯示NULL。分布式數(shù)據(jù)庫中，分庫分表后如何保證SQL查詢的效率？需要注意哪些問題？分庫分表后優(yōu)化效率的關鍵措施：分片鍵選擇：優(yōu)先使用高頻查詢的過濾條件（如訂單表按user_id分片），確保查詢可定位到單分片（如查詢某用戶的訂單）。避免跨庫Join：將關聯(lián)表按相同分片鍵分片（如訂單表與訂單詳情表均按order_id分片），或通過應用層組裝結果（先查訂單再查詳情）。全局二級索引：對需跨分片查詢的字段（如商品表的category），建立全局索引（單獨存儲索引數(shù)據(jù)，指向分片位置），但需權衡寫入性能。分頁查詢限制：跨分片的LIMITN查詢需匯總所有分片的前N條，可能導致性能下降，可通過記錄上次查詢的最大ID實現(xiàn)“下一頁”（如WHEREid>last_idLIMITN）。數(shù)據(jù)傾斜處理：避免分片鍵分布不均（如按時間分片導致近期數(shù)據(jù)集中），可通過哈希取模（如user_id%10）或添加隨機后綴（如order_id_隨機數(shù)）分散數(shù)據(jù)。需注意的問題：跨分片事務（需使用分布式事務如Seata）、跨分片統(tǒng)計（如SUM/COUNT需聚合所有分片結果）、全局唯一ID（需雪花算法或UUID保證）、擴容復雜度（需數(shù)據(jù)遷移與索引重建）。如何處理慢查詢？請描述完整的排查與優(yōu)化流程。慢查詢排查優(yōu)化流程：1.開啟慢查詢日志：設置long_query_time（如1秒），記錄執(zhí)行時間超過閾值的SQL。2.分析慢查詢SQL：通過EXPLAIN查看執(zhí)行計劃，重點關注type（是否全表掃描）、key（是否用索引）、Extra（是否有Usingfilesort或Usingtemporary）。3.定位性能瓶頸：全表掃描（type=ALL）：檢查WHERE條件是否無索引，添加索引或優(yōu)化查詢條件。索引未命中：檢查是否因函數(shù)/表達式、類型轉換、左模糊導致索引失效，調整查詢寫法。文件排序（Usingfilesort）：為ORDERBY列添加索引（順序需與排序一致）。臨時表（Usingtemporary）：優(yōu)化GROUPBY或ORDERBY，使用覆蓋索引避免臨時表。4.優(yōu)化