實(shí)訓(xùn)案例名稱：索引使用策略一、任務(wù)介紹索引的使用，可以分為匹配全值索引查詢、匹配最左前綴索引查詢、匹配列前綴索引查詢、匹配值的范圍索引查詢、僅對(duì)索引進(jìn)行查詢（索引覆蓋）。二、實(shí)現(xiàn)步驟（1）匹配全值索引查詢：針對(duì)建索引的列可以匹配列的全值進(jìn)行查詢。mysql>EXPLAINSELECT*FROMstudentWHEREid=3\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:constpossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:constrows:1filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)mysql>（2）匹配最左前綴索引查詢：索引不僅可以進(jìn)行單列索引，也可以建立聯(lián)合索引，也就是把多列建成一個(gè)聯(lián)合索引。#使用姓名（name）、性別（sex）、年齡（age）字段創(chuàng)建聯(lián)合索引mysql>ALTERTABLEstudentADDINDEXidx_name_sex_age(name,sex,age);QueryOK,0rowsaffected(0.35sec)Records:0Duplicates:0Warnings:0#刪除姓名字段的索引mysql>DROPINDEXidx_nameONstudent;QueryOK,0rowsaffected(0.20sec)Records:0Duplicates:0Warnings:0#按姓名字段的索引查找，匹配最左前綴列mysql>EXPLAINSELECT*FROMstudentWHEREname='小明'\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:refpossible_keys:idx_name_sex_agekey:idx_name_sex_agekey_len:1023ref:constrows:1filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)#當(dāng)可選的索引有多個(gè)時(shí)，如idx_sex、idx_name_sex_age，存儲(chǔ)引擎會(huì)選擇最優(yōu)的索引mysql>EXPLAINSELECT*FROMstudentWHEREname='小明'ANDsex='男'\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:refpossible_keys:idx_sex,idx_name_sex_agekey:idx_name_sex_agekey_len:1046ref:const,constrows:1filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)#當(dāng)可選的索引有多個(gè)時(shí)，如idx_age、idx_name_sex_age，存儲(chǔ)引擎會(huì)選擇最優(yōu)的索引mysql>EXPLAINSELECT*FROMstudentWHEREname='小明'ANDage='30'\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:refpossible_keys:idx_age,idx_name_sex_agekey:idx_agekey_len:23ref:constrows:1filtered:16.67Extra:Usingwhere1rowinset,1warning(0.00sec)#當(dāng)可選的索引有多個(gè)時(shí)，存儲(chǔ)引擎會(huì)選擇最優(yōu)的索引mysql>EXPLAINSELECT*FROMstudentWHEREname='小明'ANDsex='男'ANDage='30'\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:refpossible_keys:idx_sex,idx_age,idx_name_sex_agekey:idx_agekey_len:23ref:constrows:1filtered:16.67Extra:Usingwhere1rowinset,1warning(0.00sec)#使用性別字段的索引mysql>EXPLAINSELECTsexFROMstudentWHEREsex='男'\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:refpossible_keys:idx_sex,idx_name_sex_agekey:idx_sexkey_len:23ref:constrows:3filtered:100.00Extra:Usingindex1rowinset,1warning(0.00sec)#按照最左前綴匹配原則，聯(lián)合索引不能匹配性別、年齡這兩個(gè)字段mysql>EXPLAINSELECT*FROMstudentWHEREsex='男'ANDage='30'\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:refpossible_keys:idx_sex,idx_agekey:idx_agekey_len:23ref:constrows:1filtered:50.00Extra:Usingwhere（3）匹配列前綴索引查詢：匹配列的前一部分進(jìn)行查詢，如查詢姓名，可以按姓氏“劉”開頭進(jìn)行查詢，使用通配符%，但是通配符只能放置在右側(cè)，不能放置在左側(cè)。#通配符放置在右側(cè)mysql>EXPLAINSELECT*FROMstudentWHEREnamelike'劉%'\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:rangepossible_keys:idx_name_sex_agekey:idx_name_sex_agekey_len:1023ref:NULLrows:1filtered:100.00Extra:Usingindexcondition1rowinset,1warning(0.01sec)#通配符放置在左側(cè)時(shí)，不能使用索引mysql>EXPLAINSELECT*FROMstudentWHEREnamelike'%劉%'\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:6filtered:16.67Extra:Usingwhere1rowinset,1warning(0.00sec)mysql>（4）匹配值的范圍索引查詢：對(duì)索引可以進(jìn)行范圍查詢。mysql>EXPLAINSELECT*FROMstudentWHEREid>2ANDid<5\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:rangepossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:NULLrows:2filtered:100.00Extra:Usingwhere1rowinset,1warning(0.00sec)（5）僅對(duì)索引進(jìn)行查詢：只針對(duì)索引進(jìn)行查詢，也叫索引覆蓋，不查詢數(shù)據(jù)表。#使用年齡字段創(chuàng)建索引idx_age，查詢年齡的時(shí)候從索引上直接獲取，不需要到數(shù)據(jù)表中獲取mysql>EXPLAINSELECTageFROMuserWHEREage>20\G***************************1.row***************************i