MySQL性能優化（三）– 索引
- 2019 年 12 月 5 日
- 筆記
一、什麼是索引及索引的特點
索引是一種數據結構
索引的特點：查找速度快，排好序，數據結構
索引的數據結構類型有：BTREE索引和HASH索引，下面展示的是BTREE索引。
BTREE：balance tree （平衡樹）
BTREE的特點實例：
假設有一張表，表中的數據為下圖中的左側，則索引中數據為下圖中的右側：
如果查詢id為9，name為ii的，在表中需要查詢9次，但是在二叉樹中需要查詢3次。
二、索引的「類型」
1.聚集索引：節點就是數據本身，即索引表中存的就是數據本身
2.非聚集索引：節點仍然是索引節點，只不過有指向對應數據塊的指針，上面所說的BTREE索引就是非聚集索引
聚集索引的速度比非聚集索引快。
三、索引的類型
1.單列索引（一個索引只包含一個列,一個表可以有多個單列索引）
1-1.主鍵索引：primary key
創建主鍵索引，有兩種方式，其中t1和t2位表名，id為列：
1）建表的時候創建：create table t1 (id int primary key);
2）通過alter語句：alter table t2 add primary key(id);
1-2.普通索引：index，最基本的索引
創建普通索引：
（1）create index 索引名 on 表(列)，
例如：create index idx_id on t3(id);
（2）通過alter語句：alter table t4 add index(id);
1-3.唯一索引：unique
創建唯一索引：
（1）建表的時候創建：create table t5 (id int unique);
（2）create unique index 索引名 on 表(列)，
例如：create unique index idx_id on t5(id);
注意：unique的欄位可以為null，也可以重複，「」不可以重複出現。
1-4.全文索引：fulltext
資料庫自帶的全文索引，對中文支援不友好，可以藉助第三方的框架，如：sphinx（斯芬克斯）、coreseek
2.多列索引（組合索引，一個組合索引包含兩個或兩個以上的列）
創建：create index idx列名1列名2 on 表名（列名1，列名2）
實例：
建表：create table student(id int,name varchar(20),address varchar(20),remark varchar(20));
建組合索引：create index idxnameaddress_remark on student(name,address,remark);
查詢sql是否使用到了索引，可以使用explain進行分析，後續會給出介紹。
在使用查詢的時候遵循mysql組合索引的「最左前綴」，where時的條件要按建立索引的時候欄位的排序方式，下面都是基於多列索引講述的：
1、不按索引最左列開始查詢
（1） where address='深圳' 不會走索引
（2）where address = '深圳' and remark='程式設計師' 不會走索引
2、查詢中某個列有範圍查詢，則其右邊的所有列都無法使用查詢（多列查詢）
where name='xbq' and address like '%深%' and remark='程式設計師' ，該查詢只會使用索引中的前兩列，因為like是範圍查詢
3、查詢中第一個索引欄位出現like '%xxx%'或者'%xxx'，不會走索引
4.查詢中多條件用or連接，此類型和1相似
那麼對於索引怎麼刪除呢？刪除索引：alter table 表名 drop index 索引名稱
查詢索引有兩種方式：
- show index from t1;
- show keys from t1;
四、索引的優點和缺點
優點：
1.可以通過建立唯一索引或者主鍵索引,保證資料庫表中每一行數據的唯一性.
2.提高檢索速度，降低磁碟讀取IO
索引是排序好的，不需要進行全表掃描，降低了數據排序的運算成本，也就是降低了CPU的消耗
缺點：
1.索引也需要存儲，所以也需要空間，實際上索引也是一張表，保存了索引欄位的值和指向實體表的指針
2.降低更新（增刪改）表的速度，更新不僅僅只是數據本身，如果數據正好是索引欄位，同時需要更新索引資訊
當索引欄位對應的數據改變了，則索引表也會改變，例如，當圖書館中 的書櫃和書，書的類型為科普類，書柜上的目錄也是科普類，
當此書櫃中的書改變了位置的時候，則對應的書櫃的目錄表也要改變。
