實(shí)訓(xùn)案例名稱：索引的基本語(yǔ)法一、任務(wù)介紹索引是用來(lái)提高數(shù)據(jù)庫(kù)查詢效率的一種方式。建立有效、合適的索引可以極大地提高查詢效率，是數(shù)據(jù)庫(kù)高性能優(yōu)化操作必做的一件事。索引的基本語(yǔ)法包括創(chuàng)建索引、查看索引和刪除索引。二、實(shí)現(xiàn)步驟1.創(chuàng)建索引（1）創(chuàng)建表的時(shí)候創(chuàng)建索引。CREATETABLE表名(屬性名數(shù)據(jù)類型[完整性約束條件],屬性名數(shù)據(jù)類型[完整性約束條件],…屬性名數(shù)據(jù)類型[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY[別名](屬性名1[(長(zhǎng)度)][ASC|DESC]));示例如下。mysql>CREATETABLEstudent(idintprimarykey,namevarchar(255),sexvarchar(10),index(name));（2）使用CREATEINDEX創(chuàng)建索引。CREATEINDEXindex_nameONtable_name(column_list)CREATEUNIQUEINDEXindex_nameONtable_name(column_list)示例如下。mysql>CREATEINDEXidx_sexONstudent(sex);（3）使用ALTERTABLE創(chuàng)建索引。#普通索引ALTERTABLEtable_nameADDINDEXindex_name(column_list)#唯一索引ALTERTABLEtable_nameADDUNIQUE(column_list)#主鍵索引ALTERTABLEtable_nameADDPRIMARYKEY(column_list)示例如下。mysql>ALTERTABLEstudentADDINDEXidx_name(name);2.查看索引SHOWINDEXFROMtable_name;或者SHOWKEYSFROMtable_name;執(zhí)行該語(yǔ)句后，會(huì)返回一個(gè)結(jié)果集，包含表的所有索引信息，包括索引名稱、索引類型、索引字段等。每一行代表一個(gè)索引。#查看學(xué)生表student的索引信息mysql>SHOWINDEXFROMstudent;+---------+------+---------------+-------+------------+--------+---------+|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|

Cardinality|+---------+------+---------------+-------+------------+--------+---------+|student|0

|PRIMARY

|1

|id

|A

|0

||student|1

|idx_name

|1

|name

|A

|0

||student|1

|idx_sex

|1

|sex

|A

|0

||student|1

|id_name_index

|1

|id

|A

|0

|+---------+------+---------------+-------+------------+--------+---------+#查看學(xué)生表student的索引信息mysql>SHOWKEYSFROMstudent;+---------+------+---------------+-------+------------+--------+---------+|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|

Cardinality|+---------+------+---------------+-------+------------+--------+---------+|student|0

|PRIMARY

|1

|id

|A

|0

||student|1

|idx_name

|1

|name

|A

|0

||student|1

|idx_sex

|1

|sex

|A

|0

||student|1

|id_name_index

|1

|id

|A

|0

|+---------+------+---------------+-------+------------+--------+---------+3.刪除索引DROPINDEXindex_nameONtalbe_nameALTERTABLEtable_nameDROPINDEXindex_nameALTERTABLEtable_nameDROPPRIMARYKEY示例如下。mysql>DROPINDEXidx_sexONstudent;mysql>ALTERTABLEstudentDROPINDEXname;mysql>ALTERTABLEstudentDROPPRIMARYKEY;#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫(kù)mysql>USEshop;Databasechanged#查詢用戶表usermysql>SELECT*FROMuser;+----+-------+------+------+----------+|id |name|sex|age|password|+----+-------+------+------+----------+|3 |david|女|28|111111||4 |小紅|女|27|123456||5 |小明|男|10|123456||6 |小剛|男|12|123456||7 |小王|男|14|111111||8 |小綠|女|34|222222||9 |曉峰|男|15|333333||10 |小影|女|26|444444||11 |大梅|女|27|555555|+----+-------+------+------+----------+9rowsinset(0.00sec)#在姓名字段上添加索引mysql>ALTERTABLEuserADDINDEXidx_name(name);QueryOK,0rowsaffected(0.43sec)Records:0Duplicates:0Warnings:0#在性別字段上添加索引mysql>ALTERTABLEuserADDINDEXidx_sex(sex);QueryOK,0rowsaffected(0.34sec)Records:0Duplicates:0Warnings:0#查詢用戶表user的索引mysql>SHOWINDEXFROMuser;+-----+----------+---------+------------+-----------+---------+----------+|+-----+----------+---------+------------+-----------+---------+----------+|user| 0|PRIMARY|1|id|A|9||user| 1|idx_name|1|name|A|9||user| 1|idx_sex|1|sex|A|2|+-----+----------+---------+------------+-----------+---------+----------+3rowsinset(0.00sec)#查詢用戶表user的索引mysql>SHOWKEYSFROMuser;+-----+----------+---------+------------+-----------+---------+----------+|+-----+----------+---------+------------+-----------+---------+----------+|user| 0|PRIMARY|1|id|A|9||user| 1|idx_name|1|name|A|9||user| 1|idx_sex|1|sex|A|2|+-----+----------+---------+------------+-----------+---------+----------+3rowsinset(0.00sec)#刪除姓名字段上的索引mysql>DROPINDEXidx_nameONuser;QueryOK,0rowsaffected(0.18sec)Records:0Duplicates:0Warnings:0#刪除性別字段上的索引mysql>DROPINDEXidx_sexONuser;QueryOK,0rowsaffected(0.21sec)Records:0Duplicates:0Warnings:0#查詢用戶表user的索引mysql>SHOWKEYSFROMuser;+------+----------+---------+------------+-----------+---------+----------+|+------+----------+---------+------------+-----------+---------+----------+|user|0|PRIMARY|1|id|A|9|+------+----------+---------+------------+-----------+---------+----------+1rowinset(0.00sec)mysql>實(shí)訓(xùn)案例名稱：創(chuàng)建表及添加索引一、任務(wù)介紹創(chuàng)建一個(gè)學(xué)生表student，它有主鍵id列、姓名name列、性別sex列、年齡age列、年級(jí)grade列、班級(jí)class列、學(xué)號(hào)num列，如表7.1所示，然后給id、name、sex、age每列單獨(dú)添加索引。二、實(shí)現(xiàn)步驟#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure#使用shop數(shù)據(jù)庫(kù)mysql>USEshop;Databasechanged#刪除學(xué)生表studentmysql>DROPTABLEstudent;QueryOK,0rowsaffected(0.15sec)#創(chuàng)建學(xué)生表studentmysql>CREATETABLEstudent(idintnotnullAUTO_INCREMENT,namevarchar(255),sexvarchar(5),agevarchar(5),gradevarchar(10),classvarchar(10),numint,primarykey(id));QueryOK,0rowsaffected(0.42sec)#插入數(shù)據(jù)mysql>insertintostudentvalues(1,'劉明','男','19','高三','6班',3001),(2,'吳倩','女','18','高三','6班',3002),(3,'張欣','男','17','高二','1班',2001),(4,'孫曉','女','17','高二','1班',2002),(5,'趙英俊','男','16','高一','2班',1001),(6,'柳師師','女','17','高一','2班',1002);QueryOK,1rowaffected(0.00sec)#查詢數(shù)據(jù)mysql>SELECT*FROMstudent;+----+--------+------+------+-------+-------+------+|id|name|sex|age|grade|class|num|+----+--------+------+------+-------+-------+------+|1|劉明|男|19|高三|6班|3001||2|吳倩|女|18|高三|6班|3002||3|張欣|男|17|高二|1班|2001||4|孫曉|女|17|高二|1班|2002||5|趙英俊|男|16|高一|2班|1001||6|柳師師|女|17|高一|2班|1002|+----+--------+------+------+-------+-------+------+6rowsinset(0.00sec)#為主鍵id字段添加唯一索引mysql>ALTERTABLEstudentADDUNIQUEuk_id(id);QueryOK,0rowsaffected(0.70sec)Records:0Duplicates:0Warnings:0#為姓名字段添加普通索引mysql>ALTERTABLEstudentADDINDEXidx_name(name);QueryOK,0rowsaffected(0.30sec)Records:0Duplicates:0Warnings:0#為性別字段添加普通索引mysql>ALTERTABLEstudentADDINDEXidx_sex(sex);QueryOK,0rowsaffected(0.63sec)Records:0Duplicates:0Warnings:0#為年齡字段添加普通索引mysql>ALTERTABLEstudentADDINDEXidx_age(age);QueryOK,0rowsaffected(0.42sec)Records:0Duplicates:0Warnings:0#查看索引mysql>SHOWINDEXFROMstudent;+--------+---------+---------+-----------+----------+---------+-----------+| |+--------+---------+---------+-----------+----------+---------+-----------+|student|0 |PRIMARY|1|id|A|0 ||student|0 |uk_id|1|id|A|0 ||student|1 |idx_name|1|name|A| ||student|1 |idx_sex|1|sex|A| ||student|1 |idx_age|1|age|A| |+--------+---------+---------+-----------+----------+---------+-----------+mysql>實(shí)訓(xùn)案例名稱：用explain分析student表索引一、任務(wù)介紹在student表的id、name、sex、age4列上分別加上索引，怎么知道使用哪個(gè)索引呢？我們使用explain來(lái)分析索引。二、實(shí)現(xiàn)步驟#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫(kù)mysql>USEshop;Databasechanged#查詢學(xué)生表studentmysql>SELECT*FROMstudent;+----+----------+------+------+--------+-------+-----+|id|name|sex|age|grade|class|num|+----+----------+------+------+--------+-------+-----+|1|劉明|男|19|高三|6班|3001||2|吳倩|女|18|高三|6班|3002||3|張欣|男|17|高二|1班|2001||4|孫曉|女|17|高二|1班|2002||5|趙英俊|男|16|高一|2班|1001||6|柳師師|女|17|高一|2班|1002|+----+----------+------+------+--------+-------+-----+6rowsinset(0.00sec)#查詢姓名為劉明、性別為男的學(xué)生mysql>SELECT*FROMstudentWHEREname='劉明'andsex='男';+----+-------+------+------+-------+-------+------+|id|name|sex|age|grade|class|num|+----+-------+------+------+-------+-------+------+|1|劉明|男|19|高三|6班|3001|+----+-------+------+------+-------+-------+------+1rowinset(0.09sec)#分析索引使用情況mysql>EXPLAINSELECT*FROMstudentWHEREname='劉明'andsex='男'\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:refpossible_keys:name_index,sex_indexkey:name_indexkey_len:768ref:constrows:1filtered:50.00Extra:Usingwhere1rowinset,1warning(0.00sec)mysql>實(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***************************id:1select_type:SIMPLEtable:userpartitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:9filtered:33.33Extra:Usingwhere1rowinset,1warning(0.00sec)mysql>實(shí)訓(xùn)案例名稱：使用索引一、任務(wù)介紹使用索引的時(shí)候，可以匹配全值索引查詢、匹配最左前綴索引查詢、匹配列全綴索引查詢、匹配值的范圍索引查詢、僅對(duì)索引進(jìn)行查詢。二、實(shí)現(xiàn)步驟（1）把姓名name、性別sex、年齡age、年級(jí)grade建立聯(lián)合索引nsag_index，刪除其他索引id、age_index、sex_index、nsa_index。#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫(kù)mysql>useshop;Databasechanged#查看學(xué)生表student的索引情況mysql>SHOWINDEXFROMstudent;+------+---------+---------------+-----------+---------+---------+--------+|+------+---------+---------------+-----------+---------+---------+--------+||||| || | +------+---------+---------------+-----------+---------+---------+--------+7rowsinset(0.01sec)#刪除idx_age索引mysql>DROPindexidx_ageONstudent;QueryOK,0rowsaffected(0.26sec)Records:0Duplicates:0Warnings:0#刪除idx_sex索引mysql>DROPindexidx_sexONstudent;QueryOK,0rowsaffected(0.37sec)Records:0Duplicates:0Warnings:0#刪除uk_id索引mysql>DROPindexuk_idONstudent;QueryOK,0rowsaffected(0.50sec)Records:0Duplicates:0Warnings:0#刪除聯(lián)合索引idx_name_sex_agemysql>DROPINDEXidx_name_sex_ageONstudent;QueryOK,0rowsaffected(0.31sec)Records:0Duplicates:0Warnings:0#使用姓名（name）、性別（sex）、年齡（age）、年級(jí)（grade）字段創(chuàng)建聯(lián)合索引idx_nsagmysql>ALTERTABLEstudentADDINDEXidx_nsag(name,sex,age,grade);QueryOK,0rowsaffected(0.33sec)Records:0Duplicates:0Warnings:0#查看學(xué)生表student的索引情況mysql>SHOWINDEXFROMstudent;+-------+----------+--------+-------------+-----------+----------+---------+|+-------+----------+--------+-------------+-----------+----------+---------+|||||+-------+----------+--------+-------------+-----------+----------+---------+5rowsinset(0.00sec)（2）SELECT*FROMstudent，key為NULL，說(shuō)明沒有使用索引。mysql>EXPLAINSELECT*FROMstudent\G ***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:6filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)（3）SELECT*FROMstudentWHEREname='小明'，按姓名查找，使用聯(lián)合索引nsag_index，能用到的索引長(zhǎng)度是768，說(shuō)明用到了name列的索引。mysql>EXPLAINSELECT*FROMstudentWHEREname='小明'\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:refpossible_keys:idx_nsagkey:idx_nsagkey_len:1023ref:constrows:1filtered:100.00Extra:NULL（4）SELECT*FROMstudentWHEREname='小明'ANDsex='男'，按姓名和性別查找，使用聯(lián)合索引nsag_index，能用到的索引長(zhǎng)度是786，說(shuō)明用到了name和sex列的索引。mysql>EXPLAINSELECT*FROMstudentWHEREname='小明'ANDsex='男'\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:refpossible_keys:idx_nsagkey:idx_nsagkey_len:1046ref:const,constrows:1filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)（5）SELECT*FROMstudentWHEREsex='男'ANDname='小明'，按性別和姓名查找，性別在前姓名在后，使用聯(lián)合索引nsag_index，能用到的索引長(zhǎng)度是786，說(shuō)明用到了sex和name列的索引，進(jìn)一步說(shuō)明sex和name在AND前后沒有關(guān)系，數(shù)據(jù)庫(kù)會(huì)自動(dòng)查找位置。mysql>EXPLAINSELECT*FROMstudentWHEREsex='男'ANDname='小明'\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:refpossible_keys:idx_nsagkey:idx_nsagkey_len:1046ref:const,constrows:1filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)（6）SELECT*FROMstudentWHEREname='小明'ANDage='18'ANDsex='男'，按姓名、年齡、性別查找，使用聯(lián)合索引nsag_index，能用到的索引長(zhǎng)度是804，說(shuō)明使用到了name、age、sex列的索引。m***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:refpossible_keys:idx_nsagkey:idx_nsagkey_len:1069ref:const,const,constrows:1filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)mysql>（7）SELECT*FROMstudentWHEREname='小明'ANDage='18'，按姓名、年齡查找，使用聯(lián)合索引nsag_index，能用到的索引長(zhǎng)度是768，它和僅使用到name列的索引長(zhǎng)度一致，說(shuō)明只使用到了name列的索引；聯(lián)合索引nsag_index是以name、sex、age、grade的順序建立的索引，按照最左前綴匹配原則，（name）（name、sex）（name、sex、age）（name、sex、age、grade）這4種是有效的，而（name、age）是無(wú)效的，所有智能匹配到（name）。mysql>EXPLAINSELECT*FROMstudentWHEREname='小明'ANDage='18'\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:refpossible_keys:idx_nsagkey:idx_nsagkey_len:1023ref:constrows:1filtered:16.67Extra:Usingindexcondition1rowinset,1warning(0.00sec)（8）SELECT*FROMstudentWHEREname='小明'ANDage='18'ANDsex='男'ANDgrade='高三'，按姓名、年齡、性別、年級(jí)查找，使用聯(lián)合索引nsag_index，能用到的索引長(zhǎng)度是837，說(shuō)明用到了name、age、sex、grade列的索引。m

ANDgrade='高三'\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:refpossible_keys:idx_nsagkey:idx_nsagkey_len:1112ref:const,const,const,constrows:1filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)（9）SELECT*FROMstudentWHEREname='小明'ANDage='18'ANDsexLIKE'男%'ANDgrade='高三'\G，按姓名、年齡、性別、年級(jí)查找，性別采用通配符%匹配列前索引查詢，使用聯(lián)合索引nsag_index，能用到的索引長(zhǎng)度是837，說(shuō)明用到了name、age、sex、grade列的索引。mysql>EXPLAINSELECT*FROMstudentWHEREname='小明'ANDage='18'ANDsexLIKE'男%'ANDgrade='高三'\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:rangepossible_keys:idx_nsagkey:idx_nsagkey_len:1112ref:NULLrows:1filtered:16.67Extra:Usingindexcondition1rowinset,1warning(0.00sec)（10）SELECT*FROMstudentWHEREname='小明'ANDage='18'ANDsexLIKE'%男%'ANDgrade='高三'，按姓名、年齡、性別、年級(jí)查找，性別采用通配符%前后進(jìn)行匹配，使用聯(lián)合索引nsag_index，能用到的索引長(zhǎng)度是768，說(shuō)明只用到了name列的索引。mysql>EXPLAINSELECT*FROMstudentWHEREname='小明'ANDage='18'ANDsexLIKE'%男%'ANDgrade='高三'\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:refpossible_keys:idx_nsagkey:idx_nsagkey_len:1023ref:constrows:1filtered:16.67Extra:Usingindexcondition1rowinset,1warning(0.00sec)（11）SELECT*FROMstudentWHEREage='18'，按年齡查找，使用聯(lián)合索引nsag_index（以name、sex、age、grade的順序），key為NULL，說(shuō)明沒有用到索引。mysql>EXPLAINSELECT*FROMstudentWHEREage='18'\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:ALLpossible_keys:idx_nsagkey:NULLkey_len:NULLref:NULLrows:6filtered:16.67Extra:Usingwhere1rowinset,1warning(0.00sec)（12）SELECT*FROMstudentWHEREname='小明'ORsex='男'，按姓名或者性別查詢，使用OR連接時(shí)是不能使用索引的，key為NULL，說(shuō)明沒有用到索引。mysql>EXPLAINSELECT*FROMstudentWHEREname='小明'ORsex='男'\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:ALLpossible_keys:idx_nsagkey:NULLkey_len:NULLref:NULLrows:6filtered:30.56Extra:Usingwhere1rowinset,1warning(0.00sec)（13）SELECT*FROMstudentWHEREname='小明'ANDsex='男'ORage='18'，按姓名、性別或者年齡查詢，OR連接的是不能使用索引的，它相當(dāng)于（name='小明'ANDsex='男'）ORage='30'，key為NULL，說(shuō)明沒有用到索引。m***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:ALLpossible_keys:idx_nsagkey:NULLkey_len:NULLref:NULLrows:6filtered:18.98Extra:Usingwhere1rowinset,1warning(0.00sec)（14）SELECT*FROMstudentWHEREname='小明'AND（sex='男'ORage='18'），按姓名、性別或者年齡查詢，OR連接的是不能使用索引的，但是AND前面的name是可以使用索引的，使用聯(lián)合索引nsag_index，能用到的索引長(zhǎng)度是768，說(shuō)明只用到了name列的索引。m***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:refpossible_keys:idx_nsagkey:idx_nsagkey_len:1023ref:constrows:1filtered:30.56Extra:Usingindexcondition1rowinset,1warning(0.00sec)（15）SELECT*FROMstudentWHEREname='小明'ANDsex='男'ANDage>'18'ANDgrade='高中'，按姓名、性別、年齡、年級(jí)查詢，年齡采用范圍查詢，使用聯(lián)合索引nsag_index，能用到的索引長(zhǎng)度是804，說(shuō)明使用到了name、sex、age列的索引。mysql>EXPLAINSELECT*FROMstudentWHEREname='小明'ANDsex='男'AND

age>'18'ANDgrade='高中'\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:rangepossible_keys:idx_nsagkey:idx_nsagkey_len:1069ref:NULLrows:1filtered:16.67Extra:Usingindexcondition1rowinset,1warning(0.00sec)（16）SELECT*FROMstudentWHEREname='小明'ANDsex='男'ANDgrade='高中'ORDERBYage，按姓名、性別、年級(jí)查詢，按年齡排序，使用聯(lián)合索引nsag_index，能用到的索引長(zhǎng)度是786，說(shuō)明用到了name和sex列的索引。mysql>EXPLAINSELECT*FROMstudentWHEREname='小明'ANDsex='男'ANDgrade=

'高中'ORDERBYage\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:refpossible_keys:idx_nsagkey:idx_nsagkey_len:1046ref:const,constrows:1filtered:16.67Extra:Usingindexcondition1rowinset,1warning(0.00sec)（17）SELECT*FROMstudentWHEREname='小明'ANDgrade='高中'ORDERBYsex,age，按姓名、年級(jí)查詢，按性別、年齡排序，使用聯(lián)合索引nsag_index，能用到的索引長(zhǎng)度是768，說(shuō)明用到了name列的索引。mysql>EXPLAINSELECT*FROMstudentWHEREname='小明'ANDgrade='高中'ORDERBYsex,age\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:refpossible_keys:idx_nsagkey:idx_nsagkey_len:1023ref:constrows:1filtered:16.67Extra:Usingindexcondition1rowinset,1warning(0.00sec)（18）SELECT*FROMstudentWHEREname='小明'ANDgrade='高中'GROUPBYsex,age，按姓名、年級(jí)查詢，按性別、年齡分組，使用聯(lián)合索引nsag_index，能用到的索引長(zhǎng)度是768，說(shuō)明用到了name列的索引。mysql>EXPLAINSELECT*FROMstudentWHEREname='小明'ANDgrade='高中'GROUPBYsex,age\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:refpossible_keys:idx_nsagkey:idx_nsagkey_len:1023ref:constrows:1filtered:16.67Extra:Usingindexcondition1rowinset,1warning(0.00sec)實(shí)訓(xùn)案例名稱：主鍵索引一、任務(wù)介紹數(shù)據(jù)庫(kù)在建表的時(shí)候，都會(huì)建立表的主鍵，這時(shí)就可以根據(jù)表的主鍵建立索引，稱為主鍵索引（PRIMARYKEY）。主鍵索引是不允許重復(fù)和不允許有空值的，它是唯一索引的一種特例。下面建立一個(gè)課程表course，有主鍵id、課程的名稱，根據(jù)主鍵id建立主鍵索引。二、實(shí)現(xiàn)步驟#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫(kù)mysql>USEshopDatabasechanged#創(chuàng)建課程表course，并創(chuàng)建主鍵索引mysql>CREATETABLEcourse(idintnotnullAUTO_INCREMENT,