實(shí)訓(xùn)案例名稱：降序索引一、任務(wù)介紹MySQL8.0新增了降序索引，用于對(duì)列按降序進(jìn)行排序和查詢。它與普通的升序索引排列順序相反，可以優(yōu)化降序排列和降序查詢的性能。二、實(shí)現(xiàn)步驟#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshopDatabasechanged#創(chuàng)建學(xué)校表school，創(chuàng)建聯(lián)合索引idx_no_total，按學(xué)校編號(hào)字段升序索引、學(xué)生總?cè)藬?shù)字段降序索引mysql>CREATETABLEschool(noint,totalint,INDEXidx_no_total(no,totaldesc));QueryOK,0rowsaffected(0.06sec)#向?qū)W校表school中插入數(shù)據(jù)mysql>INSERTINTOschool(no,total)values(1,100),(2,200),(3,300),(4,400),

(5,500);QueryOK,5rowsaffected(0.01sec)Records:5Duplicates:0Warnings:0#查看創(chuàng)建表的語句mysql>SHOWCREATETABLEschool\G***************************1.row***************************Table:schoolCreateTable:CREATETABLE`school`(`no`intDEFAULTNULL,`total`intDEFAULTNULL,KEY`idx_no_total`(`no`,`total`DESC))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci1rowinset(0.00sec)#Extra字段中沒有Usingfilesort，降序索引生效mysql>EXPLAINSELECT*FROMschoolORDERBYno,totaldesc\G***************************1.row***************************id:1select_type:SIMPLEtable:schoolpartitions:NULLtype:indexpossible_keys:NULLkey:idx_no_totalkey_len:10ref:NULLrows:5filtered:100.00Extra:Usingindex1rowinset,1warning(0.00sec)#Extra字段中有Backwardindexscan，反向掃描索引mysql>EXPLAINSELECT*FROMschoolORDERBYnodesc,total\G***************************1.row***************************id:1select_type:SIMPLEtable:schoolpartitions:NULLtype:indexpossible_keys:NULLkey:idx_no_totalkey_len:10ref:NULLrows:5filtered:100.00Extra:Backwardindexscan;Usingindex1rowinset,1warning(0.00sec)#Extra字段中有Usingfilesort，排序必須按照每個(gè)字段定義的順序或相反順序才能利用索引mysql>EXPLAINSELECT*FROMschoolORDERBYnodesc,totaldesc\G***************************1.row***************************id:1select_type:SIMPLEtable:schoolpartitions:NULLtype:indexpossible_keys:NULLkey:idx_no_totalkey_len:10ref:NULLrows:5filtered:100.00Extra:Usingindex;Usingfilesort1rowinset,1warning(0.00sec)#Extra字段中有Usingfilesort，排序必須按照每個(gè)字段定義的順序或相反順序才能利用索引mysql>EXPLAINSELECT*FROMschoolORDERBYno,total\G***************************1.row***************************id:1select_type:SIMPLEtable:schoolpartitions:NULLtype:indexpossible_keys:NULLkey:idx_no_totalkey_len:1