單擊此處編輯母版標(biāo)題樣式,單擊此處編輯母版文本樣式,第二級,第三級,第四級,第五級,#,單擊此處編輯母版標(biāo)題樣式,單擊此處編輯母版文本樣式,第二級,第三級,第四級,第五級,第9章索引電子課件 MySQL數(shù)據(jù)庫管理與應(yīng)用,第,9,章 索引,主要內(nèi)容,9.1,索引概述,9.2,創(chuàng)建索引,9.3,查看索引,9.4,刪除索引,9.5,使用,EXPLAIN,進(jìn)行索引分析,9.6,本章小結(jié),9.1,索引概述,數(shù)據(jù)庫應(yīng)用系統(tǒng)中，數(shù)據(jù)查詢及處理速度是衡量系統(tǒng)性能的重要標(biāo)準(zhǔn)，如何提高數(shù)據(jù)庫的性能是數(shù)據(jù)庫設(shè)計(jì)時(shí)需要重點(diǎn)考慮的問題，利用索引來提高數(shù)據(jù)查詢速度是最常用的一種性能優(yōu)化方法。,。,9.1,索引概述,9.1.1,索引的,概念,索引是對數(shù)據(jù)表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu)。索引就像圖書的目錄一樣用于快速查找需要的數(shù)據(jù)，提升數(shù)據(jù)庫的查詢性能,。,在,一個(gè)數(shù)據(jù)表中查找特定的記錄也可以采取兩種方法：一種是全表掃描，從第一行開始一一查看表中的每一行數(shù)據(jù)，與查詢條件進(jìn)行對比，返回滿足條件的記錄；另一種方法是通過對表中的數(shù)據(jù)創(chuàng)建索引，先在索引中找到符合查詢條件的索引值，然后通過索引值對應(yīng)的位置快速找到表中的記錄。,當(dāng)表中的數(shù)據(jù)很多的時(shí)候，全表掃描的效率很低，而如果合理地創(chuàng)建了索引，就可以利用索引避免全表掃描從而有效提高查詢效率。,9.1,索引概述,9.1.1,索引的,概念,數(shù)據(jù)庫中索引的作用主要體現(xiàn)在以下幾個(gè)方面：,索引,可以提高查詢的速度，這是創(chuàng)建索引的主要原因；,通過,創(chuàng)建唯一索引，可以保證表中每一行數(shù)據(jù)的唯一性；,對,有參照關(guān)系的父表和子表進(jìn)行連接查詢時(shí)，索引可以加速表與表之間的連接；,使用,GROUP BY,和,ORDER BY,子句進(jìn)行查詢時(shí)，索引可以顯著減少分組和排序的時(shí)間。,9.1,索引概述,9.1.2 MySQL,索引的,分類,1,普通索引和唯一,索引,普通索引是最基本的索引類型，創(chuàng)建普通索引時(shí)對于索引列的數(shù)據(jù)類型和值是否唯一沒有限制,。,唯一索引要求索引列的值必須唯一，但允許有空值（除非列的定義中有,NOT NULL,）。主鍵是一種特殊的唯一索引，不允許有空值,。,2,單列索引和組合,索引,可以在表的單個(gè)列上創(chuàng)建索引，稱為單列索引,。,也,可以在表的多個(gè)列的組合上創(chuàng)建索引，稱為組合索引、復(fù)合索引或多列索引。如果創(chuàng)建的是組合索引，只有查詢條件中用到了索引中的第一個(gè)列才會使用該索引。,9.1,索引概述,9.1.2 MySQL,索引的,分類,3,前綴,索引,MySQL,中，對于字符串列（數(shù)據(jù)類型為,CHAR,、,VARCHAR,、,BINARY,、,VARBINARY,、,BLOB,、,TEXT,）可以創(chuàng)建只使用列值的前導(dǎo)部分的索引，使用,col_name,（,length,）語法指定索引前綴長度，前綴限制以字節(jié)為單位。,使用列前綴索引可以使索引文件小得多，從而節(jié)省大量磁盤空間，還可能加快插入操作。,4,函數(shù),索引,MySQL 8.0.13,及更高版本提供了函數(shù)索引，可以對表達(dá)式的值進(jìn)行索引，又稱為表達(dá)式索引。,9.1,索引概述,9.1.2 MySQL,索引的,分類,5,全文,索引,MySQL,中使用參數(shù),FULLTEXT,設(shè)置索引為全文索引,。,全文索引基于,文本的列（數(shù)據(jù)類型為,CHAR,、,VARCHAR,或,TEXT,）上,創(chuàng)建，,以加快對這些列中數(shù)據(jù)的查詢和,DML,操作,。,6,空間,索引,MySQL,中使用參數(shù),SPATIAL,設(shè)置索引為空間索引,。,空間,索引只能建立在空間數(shù)據(jù)類型的列上，提高系統(tǒng)查詢空間數(shù)據(jù)的效率。空間索引中的列必須聲明為,NOT NULL,。,9.1,索引概述,9.1.2 MySQL,索引的,分類,7,聚集索引和,輔助索引,聚集索引是指索引項(xiàng)的排序方式和表中數(shù)據(jù)記錄排序方式一致的索引，每張表只能有一個(gè)聚集索引，聚集索引的葉子節(jié)點(diǎn)存儲了所有的行數(shù)據(jù),。,聚集索引對表中數(shù)據(jù)重新組織以按照一個(gè)或多個(gè)列的值排序。由于聚集索引的葉子節(jié)點(diǎn)存儲了表中的所有數(shù)據(jù)，索引搜索直接指向包含行數(shù)據(jù)的頁面，所以使用聚集索引查詢數(shù)據(jù)通常要比使用非聚集索引快。,9.1,索引概述,9.1.2 MySQL,索引的,分類,7,聚集索引和,輔助索引,每個(gè),InnoDB,表都必須有一個(gè)聚集索引：,如果,表上定義了主鍵，那么主鍵就作為聚集索引；,如果,表上沒有定義了主鍵，那么該表的第一個(gè)唯一非空索引被作為聚集索引；,如果,表上沒有主鍵也沒有合適的唯一索引，,InnoDB,會在包含行,ID,值的合成列上生成一個(gè)名為,GEN_CLUST_index,的隱藏聚集索引。行,ID,是一個(gè),6,字節(jié)的字段，隨著新行的插入而單調(diào)增加。因此，按行,ID,排序?qū)嶋H上是按插入順序排列。,9.1,索引概述,9.1.2 MySQL,索引的,分類,7,聚集索引和,輔助索引,聚集索引以外的索引稱為輔助索引（二級索引、次索引）,。,在,InnoDB,中，輔助索引中的每條數(shù)據(jù)都包含該行的聚集索引值（通常為主鍵值），以及該輔助索引中的列值,。,InnoDB,使用此聚集索引值搜索聚集索引中的行。如果主鍵較長，則輔助索引會占用更多空間，因此主鍵較短是有利的。,9.1,索引概述,9.1.3,索引的設(shè)計(jì),原則,為,查詢條件中經(jīng)常用到的并且重復(fù)值較少的列上創(chuàng)建索引以便提高查詢效率，重復(fù)值較多的列無須創(chuàng)建索引,。,考慮,為經(jīng)常作為排序依據(jù)、分組依據(jù)的列創(chuàng)建索引以便提高排序和分組的效率。,對于,取值有唯一性要求的列創(chuàng)建唯一索引，既保證數(shù)據(jù)的唯一性又能提高查詢速度。,創(chuàng)建,組合索引的時(shí)候要注意索引中列的順序,。,9.1,索引概述,9.1.3,索引的設(shè)計(jì),原則,索引,并不是越多越好，索引太多不僅占用過多的磁盤空間，還會降低,INSERT,、,UPDATE,和,DELETE,的執(zhí)行速度。,數(shù)據(jù),較少的表最好不要創(chuàng)建索引，因?yàn)閿?shù)據(jù)量少，使用索引進(jìn)行查詢的時(shí)間相對全表掃描的時(shí)間優(yōu)化效果很小，而索引維護(hù)和更新還會帶來更多的開銷。,避免,對經(jīng)常更新的表創(chuàng)建多的索引。,9.2,創(chuàng)建,索引,9.2.1,使用,CREATE TABLE,語句創(chuàng)建,索引,語法,格式：,CREATE TEMPORARY TABLE IF NOT EXISTS tbl_name,(column_definition,.,FULLTEXT|SPATIAL|UNIQUE INDEX|KEY index_name(col_name(length)|(expr)ASC|DESC,.),),9.2,創(chuàng)建,索引,9.2.1,使用,CREATE TABLE,語句創(chuàng)建,索引,說明：,FULLTEXT,|SPATIAL|UNIQUE,：可選項(xiàng)，分別表示全文索引、空間索引和唯一索引；,INDEX,|KEY,：二選一，作用相同；,index_name,：要創(chuàng)建的索引的名稱，如果省略，,MySQL,默認(rèn)用列名,col_name,作為索引名稱；,col_name,(length),：索引包含的列的名稱和長度，,length,為可選參數(shù)且只有字符串類型的列才可以指定長度；,(,expr),：函數(shù)索引對應(yīng)的表達(dá)式；,ASC,|DESC,：索引值的排序方式，,ASC,表示升序，默認(rèn)值，,DESC,表示降序。,9.2,創(chuàng)建,索引,9.2.1,使用,CREATE TABLE,語句創(chuàng)建,索引,【,例,9.1】,在,jwgl,數(shù)據(jù)庫中，創(chuàng)建,dept,表時(shí)在,dname,列創(chuàng)建唯一索引。,在,MySQL,命令行客戶端輸入命令：,USE jwgl,CREATE TABLE dept,(,dno CHAR(2)PRIMARY KEY,dname VARCHAR(20),dloc VARCHAR(20),dphone CHAR(8),UNIQUE INDEX ind_dname(dname),);,9.2,創(chuàng)建,索引,9.2.1,使用,CREATE TABLE,語句創(chuàng)建,索引,定義主鍵約束或唯一性約束后，,MySQL,會自動創(chuàng)建唯一索引，因此本例也可以通過創(chuàng)建唯一約束的方法創(chuàng)建唯一索引，語句為：,CREATE TABLE dept,(,dno CHAR(2)PRIMARY KEY,dname VARCHAR(20)UNIQUE,dloc VARCHAR(20),dphone CHAR(8),);,9.2,創(chuàng)建,索引,9.2.2,使用,CREATE INDEX,語句創(chuàng)建,索引,語法,格式：,CREATE UNIQUE|FULLTEXT|SPATIAL INDEX index_name,ON tbl_name(col_name(length)|(expr)ASC|DESC,.),【,例,9.2】,在,jwgl,數(shù)據(jù)庫中的,student,表的,sname,列上創(chuàng)建普通索引，降序排列。,在,MySQL,命令行客戶端輸入命令：,CREATE INDEX ind_sname ON student(sname DESC);,9.2,創(chuàng)建,索引,9.2.2,使用,CREATE INDEX,語句創(chuàng)建,索引,【,例,9.3】,在,jwgl,數(shù)據(jù)庫中的,major,表的,mname,列上創(chuàng)建唯一索引。,在,MySQL,命令行客戶端輸入命令：,CREATE UNIQUE INDEX ind_mname ON major(mname);,9.2,創(chuàng)建,索引,9.2.2,使用,CREATE INDEX,語句創(chuàng)建,索引,【,例,9.4】,在,jwgl,數(shù)據(jù)庫中的,student,表的,sname,和,ssex,列創(chuàng)建組合索引。,在,MySQL,命令行客戶端輸入命令：,CREATE INDEX ind_sname_ssex ON student(sname,ssex);,9.2,創(chuàng)建,索引,9.2.3,使用,ALTER TABLE,語句創(chuàng)建,索引,語法,格式：,ALTER TABLE tbl_name,ADD FULLTEXT|SPATIAL|UNIQUE INDEX|KEY index_name(col_name(length)|(expr)ASC|DESC,.),【,例,9.5】,在,jwgl,數(shù)據(jù)庫中的,course,表的,cname,列上創(chuàng)建普通索引。,在,MySQL,命令行客戶端輸入命令：,ALTER TABLE,course ADD,INDEX ind_cname(cname);,9.3,查看索引,可以使用,SHOW INDEX,語句查看表的索引信息，語法格式如下：,SHOW INDEX|INDEXES|KEYS FROM|IN tbl_name FROM|IN db_name,或者：,SHOW INDEX|INDEXES|KEYS FROM|IN db_name.tbl_name,SHOW INDEX,語句以二維表的形式返回指定表上的索引信息，包括表名、索引名、是否唯一索引、索引中的列名、列序號、排序方式、索引前綴等。因?yàn)轱@示信息較多，可以使用,G,。,9.3,查看索引,【,例,9.6】,查看,jwgl,數(shù)據(jù)庫中,student,表上的索引。,在,MySQL,命令行客戶端輸入命令：,SHOW INDEX FROM jwgl.course G,9.4,刪除,索引,9.4.1,使用,DROP INDEX,語句刪除,索引,語法格式如下：,DROP INDEX index_name ON tbl_name,index_name,為要刪除的索引的名稱，,tbl_name,為索引所在的表的名稱。,【,例,9.7】,刪除,course,表的索引,ind_cname,。,在,MySQL,命令行客戶端輸入命令：,DROP INDEX ind_cname ON course;,9.4,刪除,索引,9.4.2,使用,ALTER TABLE,語句刪除,索引,語法格式如下：,ALTER TABLE tbl_name DROP INDEX index_name,index_name,為要刪除的索引的名稱，,tbl_name,為索引所在的表的名稱。,【,例,9.8】,刪除,student,表的索引,ind_