MySQL數(shù)據(jù)庫(kù)高級(jí)（六）——索引

MySQL數(shù)據(jù)庫(kù)高級(jí)（六）——索引

一、索引簡(jiǎn)介

1、索引簡(jiǎn)介

索引（Index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。
在MySQL中，索引屬于存儲(chǔ)引擎級(jí)別的概念，不同存儲(chǔ)引擎對(duì)索引的實(shí)現(xiàn)方式是不同的。MyISAM和InnoDB存儲(chǔ)引擎只支持BTREE索引，MEMORY/HEAP存儲(chǔ)引擎支持HASH和BTREE索引。　　　　

2、索引的優(yōu)點(diǎn)

A、提高數(shù)據(jù)檢索效率，降低數(shù)據(jù)庫(kù)的IO成本。
B、通過(guò)索引對(duì)數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本，降低了CPU的消耗。
C、大大加快數(shù)據(jù)的查詢速度。

3、索引的缺點(diǎn)

A、創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，并且隨著數(shù)據(jù)量的增加所耗費(fèi)的時(shí)間也會(huì)增加
B、索引也需要占空間，我們知道數(shù)據(jù)表中的數(shù)據(jù)也會(huì)有最大上線設(shè)置的，如果我們有大量的索引，索引文件可能會(huì)比數(shù)據(jù)文件更快達(dá)到上線值
C、當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除、修改時(shí)，索引也需要?jiǎng)討B(tài)的維護(hù)，降低了數(shù)據(jù)的維護(hù)速度。

4、索引的使用原則

A、主鍵自動(dòng)建立唯一索引
B、頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引
C、查詢中與其他表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引
D、頻繁更新的字段不適合建立索引，因?yàn)槊看胃虏粏螁问歉铝擞涗涍€會(huì)更新索引
E、WHERE條件里用不到的字段不創(chuàng)建索引
F、單鍵/組合索引的選擇問(wèn)題，who?(在高并發(fā)下傾向創(chuàng)建組合索引)
G、查詢中排序的字段，排序的字段若通過(guò)索引去訪問(wèn)將大大提高排序速度
H、查詢中統(tǒng)計(jì)或者分組字段
不適合使用索引的場(chǎng)合：
A、對(duì)經(jīng)常更新的表就避免對(duì)其進(jìn)行過(guò)多的索引，對(duì)經(jīng)常用于查詢的字段應(yīng)該創(chuàng)建索引。
B、數(shù)據(jù)量小的表最好不要使用索引，由于數(shù)據(jù)較少，可能查詢?nèi)繑?shù)據(jù)花費(fèi)的時(shí)間比遍歷索引的時(shí)間還要短，索引就可能不會(huì)產(chǎn)生優(yōu)化效果。
C、在不同值少的列上不要建立索引，比如在學(xué)生表的"性別"字段上只有男，女兩個(gè)不同值。在一個(gè)不同值較多的列可以建立索引。

二、索引的分類

1、單列索引

單列索引只包含單個(gè)列，但一個(gè)表中可以有多個(gè)單列索引。
A、普通索引
普通索引允許在定義索引的列中插入重復(fù)值和空值。
B、唯一索引
索引列中的值必須是唯一的，但是允許為空值。
C、主鍵索引
主鍵索引是一種特殊的唯一索引，不允許有空值。

2、復(fù)合索引

在表中的多個(gè)字段組合上創(chuàng)建的索引，只有在查詢條件中使用了組合的多個(gè)字段的左邊字段時(shí)，索引才會(huì)被使用，使用復(fù)合索引時(shí)遵循最左前綴集合。

3、全文索引

全文索引，只有MyISAM存儲(chǔ)引擎支持，只能在CHAR、VARCHAR、TEXT類型字段上使用全文索引。
全文索引主要用來(lái)查找文本中的關(guān)鍵字，而不是直接與索引中的值相比較。在數(shù)據(jù)量較大時(shí)候，先將數(shù)據(jù)放入一個(gè)沒(méi)有全文索引的表中，然后再用CREATE index創(chuàng)建fulltext索引，要比先為一張表建立fulltext然后再將數(shù)據(jù)寫入的速度快很多。

4、空間索引

空間索引是對(duì)空間數(shù)據(jù)類型的字段建立的索引，MySQL中的空間數(shù)據(jù)類型有四種：GEOMETRY、POINT、LINESTRING、POLYGON。
　在創(chuàng)建空間索引時(shí)，使用SPATIAL關(guān)鍵字。
　空間索引必須使用MyISAM存儲(chǔ)引擎， 并且空間索引的字段必須為非空。

三、索引的操作

1、索引的創(chuàng)建

創(chuàng)建表時(shí)創(chuàng)建索引的語(yǔ)法：

CREATE TABLE table_name[col_name data type]
[UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY][index_name](col_name[length])[asc|desc]

在表上創(chuàng)建索引的語(yǔ)法：

ALTER TABLE tablename ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [indexname] (col_name)[ASC|DESC];
CREATE [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] indexname ON tablename(col_name[length])[ASC|DESC];

unique|fulltext為可選參數(shù)，分別表示唯一索引、全文索引
index和key為同義詞，兩者作用相同，用來(lái)指定創(chuàng)建索引
col_name為需要?jiǎng)?chuàng)建索引的字段列，該列必須從數(shù)據(jù)表中該定義的多個(gè)列中選擇
index_name指定索引的名稱，為可選參數(shù)，如果不指定，默認(rèn)col_name為索引值
length為可選參數(shù)，表示索引的長(zhǎng)度，只有字符串類型的字段才能指定索引長(zhǎng)度
asc或desc指定升序或降序的索引值存儲(chǔ)
在創(chuàng)建索引時(shí)如果不指定索引名，默認(rèn)使用字段名作為索引名。

2、普通索引的創(chuàng)建

直接創(chuàng)建索引
CREATE INDEX index_name ON tablename(column(length))
修改表結(jié)構(gòu)
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
創(chuàng)建表時(shí)指定索引

CREATE TABLE tablename
(
col_name1 type,
col_name2 type,
INDEX index_name(col_name)
);

3、唯一索引的創(chuàng)建

索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。
直接創(chuàng)建唯一索引
CREATE UNIQUE INDEX indexName ON tablename(column(length))
修改表結(jié)構(gòu)
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
創(chuàng)建表時(shí)直接指定

CREATE TABLE tablename
(
col_name1 type,
col_name2 type,
UNIQUE INDEX index_name(col_name)
);

4、主鍵索引的創(chuàng)建

修改表結(jié)構(gòu)
ALTER TABLE table_name ADD PRIMARY KEY(col_name)
創(chuàng)建表時(shí)直接指定

CREATE TABLE tablename
(
col_name1 type,
col_name2 type,
PRIMARY KEY(col_name)
);

5、復(fù)合索引的創(chuàng)建

直接創(chuàng)建復(fù)合索引
CREATE INDEX indexName ON tablename(col_name1,col_name2)
修改表結(jié)構(gòu)
ALTER TABLE table_name ADD INDEX indexName(col_name1,col_name2)
創(chuàng)建表時(shí)直接指定

CREATE TABLE tablename
(
col_name1 type,
col_name2 type,
INDEX index_name(col_name1,col_name2)
);

6、全文索引的創(chuàng)建

直接創(chuàng)建全文索引
CREATE FULLTEXT INDEX indexName ON tablename(col_name)
修改表結(jié)構(gòu)
ALTER TABLE table_name ADD FULLTEXT INDEX indexName(col_name)
創(chuàng)建表時(shí)直接指定

CREATE TABLE tablename
(
col_name1 type,
col_name2 type,
FULLTEXT INDEX index_name(col_name)
);

在使用全文索引時(shí)，需要借助MATCH AGAINST操作，而不是一般的WHERE語(yǔ)句加LIKE。全文索引的限制比較多，比如只能使用MyISAM存儲(chǔ)引擎，比如只能在CHAR、VARCHAR、TEXT上設(shè)置全文索引。比如搜索的關(guān)鍵字默認(rèn)至少要4個(gè)字符，比如搜索的關(guān)鍵字太短就會(huì)被忽略掉。
SELECT * FROM tablename WHERE MATCH(col_name) AGAINST('pattern');
col_name為全文索引列，'pattern'為匹配的字符串

7、索引的刪除

DROP INDEX [indexName] ON tablename; 
ALTER TABLE tablename DROP INDEX indexname;

8、索引信息的查看

SHOW INDEX FROM table_name;

9、索引的注意事項(xiàng)

A、索引不會(huì)包含有null值的列
在數(shù)據(jù)庫(kù)設(shè)計(jì)時(shí)不要讓字段的默認(rèn)值為null。
B、使用短索引
C、索引列排序
因此數(shù)據(jù)庫(kù)默認(rèn)排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個(gè)列的排序，如果需要最好給這些列創(chuàng)建復(fù)合索引。
D、like語(yǔ)句操作
一般情況下不推薦使用like操作，如果非使用不可。like “%aaa%”不會(huì)使用索引而like “aaa%”可以使用索引。
E、不要在列上進(jìn)行運(yùn)算
在索引列上進(jìn)行運(yùn)算將導(dǎo)致索引失效而進(jìn)行全表掃描，例如
SELECT * FROM table_name WHERE YEAR(column_name)<2017;
F、不使用not in和<>操作

四、索引查詢速度比較

1、插入記錄

create procedure addStudent(in num int)
begin
declare i int;
set i=1;
delete from TStudent;
while num>=i do
insert TStudent values 
(
       LPAD(convert(i,char(10)),10,'0'),
       CreateName(),
       if(ceil(rand()*10)%2=0,'男','女'),
       RPAD(convert(ceil(rand()*1000000000000000000),char(18)),18,'0'),
       Concat(convert(ceil(rand()*10)+1980,char(4)),'-',LPAD(convert(ceil(rand()*12),char(2)),2,'0'),'-',LPAD(convert(ceil(rand()*28),char(2)),2,'0')),
       Concat(PINYIN(sname),'@hotmail.com'),
       case ceil(rand()*3) when 1 then '網(wǎng)絡(luò)與網(wǎng)站開發(fā)' when 2 then 'JAVA' ELSE 'NET' END,
       NOW()
);
set i=i+1;
end while;
select * from TStudent;
end

修改addStudent存儲(chǔ)過(guò)程，插入500000條記錄
call addStudent(500000);
SQL語(yǔ)句查詢×××號(hào)cardID以12345開頭的學(xué)生。
select * from TStudent where cardID like '12345%'

花費(fèi)時(shí)間為1.27秒

2、給×××列添加索引

alter table TStudent add index cardidIndex(cardID);
SQL語(yǔ)句查詢×××號(hào)cardID以12345開頭的學(xué)生。
select * from TStudent where cardID like '12345%'
花費(fèi)時(shí)間31毫秒。

3、查看索引占用的磁盤空間

schoolDB數(shù)據(jù)庫(kù)索引占用的磁盤空間。

SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') 
AS 'Total Index Size' FROM information_schema.TABLES
WHERE table_schema LIKE 'schoolDB'; 

查看schoolDB數(shù)據(jù)庫(kù)數(shù)據(jù)占用的磁盤空間。

SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024), 2), ' MB') 
AS 'Total Data Size' 
FROM information_schema.TABLES WHERE table_schema LIKE 'schoolDB';

4、查看QL語(yǔ)句執(zhí)行計(jì)劃

EXPLAIN可以查看SQL查詢語(yǔ)句的查詢計(jì)劃，使用索引還是全表掃描，key顯示使用的索引。
explain select * from TStudent where cardid like '12345%' \G;

id:　SELECT識(shí)別符，即SELECT的查詢序列號(hào)，一條語(yǔ)句中，select是第幾次出現(xiàn)。
select_type:所使用的SELECT查詢類型，SIMPLE表示為簡(jiǎn)單的SELECT，不實(shí)用UNION或子查詢。其他取值，PRIMARY：最外面的SELECT在擁有子查詢時(shí)，就會(huì)出現(xiàn)兩個(gè)以上的SELECT。UNION：union(兩張表連接)中的第二個(gè)或后面的select語(yǔ)句? SUBQUERY：在子查詢中，第二SELECT。
table：數(shù)據(jù)表的名字。按被讀取的先后順序排列。
type：指定本數(shù)據(jù)表和其他數(shù)據(jù)表之間的關(guān)聯(lián)關(guān)系，表中所有符合檢索值的記錄都會(huì)被取出來(lái)和從上一個(gè)表中取出來(lái)的記錄作聯(lián)合。ref用于連接程序使用鍵的最左前綴或者鍵不是primary key或unique索引的情況。取值有system、const、eq_ref、index和All。
possible_keys：MySQL在搜索數(shù)據(jù)記錄時(shí)可以選用的各個(gè)索引
key：實(shí)際選用的索引
key_len：顯示MySQL使用索引的長(zhǎng)度(使用的索引個(gè)數(shù))，當(dāng)key字段的值為 null時(shí)，索引的長(zhǎng)度就是null。
ref:給出關(guān)聯(lián)關(guān)系中另一個(gè)數(shù)據(jù)表中數(shù)據(jù)列的名字。
rows：MySQL在執(zhí)行查詢時(shí)預(yù)計(jì)會(huì)從數(shù)據(jù)表里讀出的數(shù)據(jù)行的個(gè)數(shù)。
extra：提供與關(guān)聯(lián)操作有關(guān)的信息。　　　

五、覆蓋索引

1、覆蓋索引

一個(gè)包含查詢所需的字段的索引稱為覆蓋索引（covering index）。MySQL只需要通過(guò)索引就可以返回查詢所需要的數(shù)據(jù)，而不必在查到索引之后進(jìn)行回表操作，減少IO，提供效率。
通過(guò)EXPLAIN查看SQL語(yǔ)句的執(zhí)行計(jì)劃時(shí)，在EXPLAIN的Extra列出現(xiàn)Using Index提示時(shí)，說(shuō)明SQL查詢使用覆蓋索引。

2、使用覆蓋索引的SQL語(yǔ)句

Tstudent表cardID列創(chuàng)建了索引，SQL語(yǔ)句查找的列是cardID，查找條件也是cardID，就會(huì)使用cardID索引進(jìn)行查找，不需要掃描表的頁(yè)。
explain select sname from TStudent where sname like '劉%';
執(zhí)行結(jié)果Extra 出現(xiàn)using index，說(shuō)明是使用覆蓋索引查找。

3、使用覆蓋索引實(shí)現(xiàn)order by排序

在MySQL中的ORDER BY有兩種排序?qū)崿F(xiàn)方式:
A、利用有序索引獲取有序數(shù)據(jù)
B、文件排序
使用EXPLAIN分析SQL查詢時(shí)，利用有序索引獲取有序數(shù)據(jù)顯示Using index。而文件排序顯示Using filesort。
explain select email from TStudent order by email;
email列沒(méi)有索引，SQL語(yǔ)句的查詢計(jì)劃可以看到Extra是using filesort，說(shuō)明是將結(jié)果在內(nèi)存中排序，需要額外時(shí)間開銷。
給Email列添加索引后，

alter table TStudent add index emailIndex(email);
explain select email from TStudent order by email;

再次執(zhí)行，可以看到Extra列是Using index，說(shuō)明使用索引排序，沒(méi)有額外時(shí)間開銷。