MySQL數據庫高級（六）——索引

MySQL數據庫高級（六）——索引

一、索引簡介

1、索引簡介

索引（Index）是幫助MySQL高效獲取數據的數據結構。
在MySQL中，索引屬于存儲引擎級別的概念，不同存儲引擎對索引的實現方式是不同的。MyISAM和InnoDB存儲引擎只支持BTREE索引，MEMORY/HEAP存儲引擎支持HASH和BTREE索引。　　　　

2、索引的優點

A、提高數據檢索效率，降低數據庫的IO成本。
B、通過索引對數據進行排序，降低數據排序的成本，降低了CPU的消耗。
C、大大加快數據的查詢速度。

3、索引的缺點

A、創建索引和維護索引要耗費時間，并且隨著數據量的增加所耗費的時間也會增加
B、索引也需要占空間，我們知道數據表中的數據也會有最大上線設置的，如果我們有大量的索引，索引文件可能會比數據文件更快達到上線值
C、當對表中的數據進行增加、刪除、修改時，索引也需要動態的維護，降低了數據的維護速度。

4、索引的使用原則

A、主鍵自動建立唯一索引
B、頻繁作為查詢條件的字段應該創建索引
C、查詢中與其他表關聯的字段，外鍵關系建立索引
D、頻繁更新的字段不適合建立索引，因為每次更新不單單是更新了記錄還會更新索引
E、WHERE條件里用不到的字段不創建索引
F、單鍵/組合索引的選擇問題，who?(在高并發下傾向創建組合索引)
G、查詢中排序的字段，排序的字段若通過索引去訪問將大大提高排序速度
H、查詢中統計或者分組字段
不適合使用索引的場合：
A、對經常更新的表就避免對其進行過多的索引，對經常用于查詢的字段應該創建索引。
B、數據量小的表最好不要使用索引，由于數據較少，可能查詢全部數據花費的時間比遍歷索引的時間還要短，索引就可能不會產生優化效果。
C、在不同值少的列上不要建立索引，比如在學生表的"性別"字段上只有男，女兩個不同值。在一個不同值較多的列可以建立索引。

二、索引的分類

1、單列索引

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

2、復合索引

在表中的多個字段組合上創建的索引，只有在查詢條件中使用了組合的多個字段的左邊字段時，索引才會被使用，使用復合索引時遵循最左前綴集合。

3、全文索引

全文索引，只有MyISAM存儲引擎支持，只能在CHAR、VARCHAR、TEXT類型字段上使用全文索引。
全文索引主要用來查找文本中的關鍵字，而不是直接與索引中的值相比較。在數據量較大時候，先將數據放入一個沒有全文索引的表中，然后再用CREATE index創建fulltext索引，要比先為一張表建立fulltext然后再將數據寫入的速度快很多。

4、空間索引

空間索引是對空間數據類型的字段建立的索引，MySQL中的空間數據類型有四種：GEOMETRY、POINT、LINESTRING、POLYGON。
　在創建空間索引時，使用SPATIAL關鍵字。
　空間索引必須使用MyISAM存儲引擎， 并且空間索引的字段必須為非空。

三、索引的操作

1、索引的創建

創建表時創建索引的語法：

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

在表上創建索引的語法：

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為可選參數，分別表示唯一索引、全文索引
index和key為同義詞，兩者作用相同，用來指定創建索引
col_name為需要創建索引的字段列，該列必須從數據表中該定義的多個列中選擇
index_name指定索引的名稱，為可選參數，如果不指定，默認col_name為索引值
length為可選參數，表示索引的長度，只有字符串類型的字段才能指定索引長度
asc或desc指定升序或降序的索引值存儲
在創建索引時如果不指定索引名，默認使用字段名作為索引名。

2、普通索引的創建

直接創建索引
CREATE INDEX index_name ON tablename(column(length))
修改表結構
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
創建表時指定索引

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

3、唯一索引的創建

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

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

4、主鍵索引的創建

修改表結構
ALTER TABLE table_name ADD PRIMARY KEY(col_name)
創建表時直接指定

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

5、復合索引的創建

直接創建復合索引
CREATE INDEX indexName ON tablename(col_name1,col_name2)
修改表結構
ALTER TABLE table_name ADD INDEX indexName(col_name1,col_name2)
創建表時直接指定

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

6、全文索引的創建

直接創建全文索引
CREATE FULLTEXT INDEX indexName ON tablename(col_name)
修改表結構
ALTER TABLE table_name ADD FULLTEXT INDEX indexName(col_name)
創建表時直接指定

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

在使用全文索引時，需要借助MATCH AGAINST操作，而不是一般的WHERE語句加LIKE。全文索引的限制比較多，比如只能使用MyISAM存儲引擎，比如只能在CHAR、VARCHAR、TEXT上設置全文索引。比如搜索的關鍵字默認至少要4個字符，比如搜索的關鍵字太短就會被忽略掉。
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、索引的注意事項

A、索引不會包含有null值的列
在數據庫設計時不要讓字段的默認值為null。
B、使用短索引
C、索引列排序
因此數據庫默認排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列創建復合索引。
D、like語句操作
一般情況下不推薦使用like操作，如果非使用不可。like “%aaa%”不會使用索引而like “aaa%”可以使用索引。
E、不要在列上進行運算
在索引列上進行運算將導致索引失效而進行全表掃描，例如
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 '網絡與網站開發' when 2 then 'JAVA' ELSE 'NET' END,
       NOW()
);
set i=i+1;
end while;
select * from TStudent;
end

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

花費時間為1.27秒

2、給×××列添加索引

alter table TStudent add index cardidIndex(cardID);
SQL語句查詢×××號cardID以12345開頭的學生。
select * from TStudent where cardID like '12345%'
花費時間31毫秒。

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

schoolDB數據庫索引占用的磁盤空間。

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

查看schoolDB數據庫數據占用的磁盤空間。

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語句執行計劃

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

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

五、覆蓋索引

1、覆蓋索引

一個包含查詢所需的字段的索引稱為覆蓋索引（covering index）。MySQL只需要通過索引就可以返回查詢所需要的數據，而不必在查到索引之后進行回表操作，減少IO，提供效率。
通過EXPLAIN查看SQL語句的執行計劃時，在EXPLAIN的Extra列出現Using Index提示時，說明SQL查詢使用覆蓋索引。

2、使用覆蓋索引的SQL語句

Tstudent表cardID列創建了索引，SQL語句查找的列是cardID，查找條件也是cardID，就會使用cardID索引進行查找，不需要掃描表的頁。
explain select sname from TStudent where sname like '劉%';
執行結果Extra 出現using index，說明是使用覆蓋索引查找。

3、使用覆蓋索引實現order by排序

在MySQL中的ORDER BY有兩種排序實現方式:
A、利用有序索引獲取有序數據
B、文件排序
使用EXPLAIN分析SQL查詢時，利用有序索引獲取有序數據顯示Using index。而文件排序顯示Using filesort。
explain select email from TStudent order by email;
email列沒有索引，SQL語句的查詢計劃可以看到Extra是using filesort，說明是將結果在內存中排序，需要額外時間開銷。
給Email列添加索引后，

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

再次執行，可以看到Extra列是Using index，說明使用索引排序，沒有額外時間開銷。