網(wǎng)站被掛馬 301,無錫百度推廣公司哪家好,小型電商平臺(tái),南寧建站官網(wǎng)前言 在學(xué)習(xí)MySQL過程中#xff0c;閱讀到這樣一段話#xff1a;在 MySQL 中#xff0c;B 樹索引按照存儲(chǔ)方式的不同分為聚集索引和非聚集索引。我就在想為什么要分為這兩種#xff0c;下面我就詳細(xì)介紹這兩者的聯(lián)系、優(yōu)缺點(diǎn)。 一、聚集索引和非聚集索引的本質(zhì) 聚集索引…前言在學(xué)習(xí)MySQL過程中閱讀到這樣一段話在 MySQL 中B 樹索引按照存儲(chǔ)方式的不同分為聚集索引和非聚集索引。我就在想為什么要分為這兩種下面我就詳細(xì)介紹這兩者的聯(lián)系、優(yōu)缺點(diǎn)。一、聚集索引和非聚集索引的本質(zhì)聚集索引核心是“數(shù)據(jù)即索引索引即數(shù)據(jù)”nnoDB 中表的存儲(chǔ)結(jié)構(gòu)本身就是聚集索引的結(jié)構(gòu)表的所有行數(shù)據(jù)都會(huì)按照聚集索引的鍵值默認(rèn)是主鍵進(jìn)行排序存儲(chǔ)在聚集索引的葉子節(jié)點(diǎn)中?？梢园阉斫獬梢槐景错摯a主鍵排序的書書的每一頁葉子節(jié)點(diǎn)就是完整的內(nèi)容整行數(shù)據(jù)。非聚集索引是“單獨(dú)的索引結(jié)構(gòu)指向數(shù)據(jù)的指針”非聚集索引有自己獨(dú)立的索引樹結(jié)構(gòu)葉子節(jié)點(diǎn)中只存儲(chǔ)「索引列的值 聚集索引的鍵值主鍵」并不存儲(chǔ)整行數(shù)據(jù)?？梢园阉斫獬蛇@本書的目錄目錄里只記錄章節(jié)名索引列和對(duì)應(yīng)的頁碼主鍵不記錄章節(jié)的具體內(nèi)容。二、聚集索引和非聚集索引的聯(lián)系二者是強(qiáng)綁定的InnoDB 的設(shè)計(jì)邏輯里非聚集索引完全依賴聚集索引非聚集索引的葉子節(jié)點(diǎn)必須存儲(chǔ)聚集索引的鍵值InnoDB 的非聚集索引葉子節(jié)點(diǎn)不會(huì)直接存儲(chǔ)數(shù)據(jù)的物理地址而是存儲(chǔ)聚集索引的主鍵值 —— 這是為了保證數(shù)據(jù)的穩(wěn)定性如果數(shù)據(jù)發(fā)生頁分裂物理地址會(huì)變化但主鍵不會(huì)變非聚集索引的回表操作本質(zhì)是依賴聚集索引當(dāng)非聚集索引無法覆蓋查詢查詢字段不在非聚集索引中時(shí)會(huì)先通過非聚集索引找到主鍵值再用主鍵值去聚集索引中查詢完整數(shù)據(jù)這個(gè)過程就是「回表」二者的索引樹結(jié)構(gòu)邏輯一致不管是聚集還是非聚集索引都采用 B 樹的結(jié)構(gòu)非葉子節(jié)點(diǎn)存儲(chǔ)索引鍵值用于導(dǎo)航葉子節(jié)點(diǎn)存儲(chǔ)實(shí)際的內(nèi)容聚集索引存數(shù)據(jù)非聚集索引存索引列 主鍵三、優(yōu)缺點(diǎn)對(duì)比對(duì)比維度聚集索引非聚集索引葉子節(jié)點(diǎn)存儲(chǔ)存儲(chǔ)整行數(shù)據(jù)存儲(chǔ)索引列的值聚集索引的主鍵值索引體積體積大等于表的實(shí)際數(shù)據(jù)體積體積小僅包含索引列和主鍵遠(yuǎn)小于表體積訪問效率1. 主鍵查詢/范圍查詢效率極高 2.非主鍵查詢效率極低需要全表掃描1. 非主鍵查詢匹配索引列效率極高 2.覆蓋索引場(chǎng)景下效率優(yōu)于聚集索引 3.非覆蓋場(chǎng)景需回表效率略降排序特性天然按主鍵排序無需額外排序可按索引列排序避免ORDER BY觸發(fā)的文件排序創(chuàng)建限制1. 一張表僅能創(chuàng)建1個(gè)聚集索引 2.默認(rèn)以主鍵為聚集索引無主鍵則選唯一非空索引否則生成隱藏rowid1. 一張表可創(chuàng)建多個(gè)非聚集索引 2.支持基于任意字段/聯(lián)合字段創(chuàng)建更新代價(jià)更新主鍵聚集索引鍵會(huì)導(dǎo)致整行數(shù)據(jù)移動(dòng)代價(jià)極高更新索引列僅修改索引樹節(jié)點(diǎn)代價(jià)低若索引列是主鍵會(huì)聯(lián)動(dòng)修改所有非聚集索引四、實(shí)戰(zhàn)案例我們用一個(gè)電商的訂單表order_info來舉例表結(jié)構(gòu)如下CREATETABLEorder_info(order_idBIGINTNOTNULLCOMMENT訂單ID主鍵聚集索引,user_idBIGINTNOTNULLCOMMENT用戶ID,order_timeDATETIMENOTNULLCOMMENT下單時(shí)間,order_amountDECIMAL(10,2)NOTNULLCOMMENT訂單金額,order_statusTINYINTNOTNULLCOMMENT訂單狀態(tài),PRIMARYKEY(order_id),-- 聚集索引KEYidx_user_time(user_id,order_time)-- 非聚集聯(lián)合索引)ENGINEInnoDBDEFAULTCHARSETutf8mb4;場(chǎng)景 1適合用聚集索引的場(chǎng)景如果運(yùn)營需要查詢「訂單 ID 為 100001 的訂單的所有信息」SQL 如下SELECT*FROMorder_infoWHEREorder_id100001;此時(shí)用聚集索引order_id是最優(yōu)選擇直接通過主鍵定位到葉子節(jié)點(diǎn)的整行數(shù)據(jù)不需要任何額外操作效率最高場(chǎng)景 2適合用非聚集索引覆蓋索引的場(chǎng)景如果運(yùn)營需要查詢「用戶 ID 為 1001 的所有訂單的下單時(shí)間和訂單金額」SQL 如下SELECTorder_time,order_amountFROMorder_infoWHEREuser_id1001;此時(shí)我們創(chuàng)建的非聚集聯(lián)合索引idx_user_time (user_id, order_time)加上 InnoDB 默認(rèn)存儲(chǔ)的主鍵order_id但我們的查詢字段是order_time和order_amount—— 這里可以優(yōu)化索引改成idx_user_time_amount (user_id, order_time, order_amount)優(yōu)化后這個(gè)索引就覆蓋了查詢的所有字段MySQL 直接從非聚集索引中返回?cái)?shù)據(jù)不需要回表效率遠(yuǎn)高于用聚集索引查詢場(chǎng)景 3適合用非聚集索引需要回表的場(chǎng)景如果運(yùn)營需要查詢「用戶 ID 為 1001 的所有訂單的完整信息」SQL 如下SELECT*FROMorder_infoWHEREuser_id1001;此時(shí)非聚集索引idx_user_time只能提供user_id、order_time和order_id無法覆蓋所有字段需要回表1.先通過idx_user_time找到所有user_id1001的行拿到對(duì)應(yīng)的order_id2.再用order_id去聚集索引中查詢整行數(shù)據(jù)但這個(gè)效率依然遠(yuǎn)高于用聚集索引全表掃描聚集索引只能按 order_id 排序無法快速找到 user_id1001 的行五、實(shí)戰(zhàn)建議聚集索引的選擇盡量用自增主鍵自增主鍵的插入是順序的不會(huì)導(dǎo)致聚集索引的頁分裂插入效率更高如果用 UUID 作為主鍵插入時(shí)會(huì)隨機(jī)定位位置導(dǎo)致大量頁分裂性能下降非聚集索引的設(shè)計(jì)優(yōu)先考慮覆蓋索引對(duì)于高頻查詢盡量創(chuàng)建包含查詢所需所有字段的聯(lián)合索引避免回表不要濫用非聚集索引非聚集索引會(huì)占用額外的磁盤空間同時(shí)會(huì)降低插入 / 更新 / 刪除的效率因?yàn)樾枰S護(hù)索引樹只給高頻查詢的字段創(chuàng)建索引