SQL索引
遙遠的將不再遙遠，平凡的已不再平凡，
索引 index
索引是一種排好序的快速查找的資料結構，它幫助資料庫高效的進行資料的檢索，在資料之外，資料庫系統還維護著滿足特定查找演算法的資料結構（額外的存盤空間），這些資料結構以某種方式指向資料，這樣就可以在這些資料結構上實作高效的查找演算法，這種資料結構就叫做索引，
一般來說索引本身也很大，不可能全部存盤在記憶體中，因此往往以索引檔案的形式存放在磁盤中，目前大多數索引都采用BTree樹方式構建，
分類
單值索引：一個索引只包括一個列，一個表可以有多個列
唯一索引：索引列的值必須唯一，但允許有空值；主鍵會自動創建唯一索引
復合索引：一個索引同時包括多列
創建索引
- 查看索引，主鍵會自動創建索引
show index from user;
- 創建索引
create index 索引名字 on 表名(欄位名); #創建索引
create index score_index on scores(score); #創建索引
- 修改表結構,添加普通索引
alter table user add index sex_index(sex)
- 創建唯一索引
alter table students add unique(id) #創建唯一索引,索引列的值必須唯一
- 創建復合索引
alter table user add index cp_index(name,age);
- 創建復合唯一索引
alter table user add unique cp_index(name,age);
- 洗掉索引
alter table uesr drop index cp_index;
索引掃描型別
type：
- ALL 全表掃描，沒有優化，最慢的方式
- index 索引全掃描，其次慢的方式
- range 索引范圍掃描，常用語<，<=，>=，between等操作
- ref 使用非唯一索引掃描或唯一索引前綴掃描，回傳單條記錄，常出現在關聯查詢中
- eq_ref 類似ref，區別在于使用的是唯一索引，使用主鍵的關聯查詢
- const/system 單條記錄，系統會把匹配行中的其他列作為常數處理，如主鍵或唯一索引查詢，system是const的特殊情況
- null MySQL不訪問任何表或索引，直接回傳結果
使用索引 explain
explain
select * from user where id = 1;#使用了id的索引
最左特性
當我們創建一個聯合索引（復合索引）的時候，如(k1,k2,k3)，相當于創建了（k1）、(k1,k2)和(k1,k2,k3)三個索引，這就是最左匹配原則，也稱為最左特性，
注意：查詢k2欄位或者k3欄位或者k2和k3欄位，索引就會失效
ALTER TABLE user ADD INDEX user_index(id,name,age); #這里添加復合索引
explain
select * from user where id = 1 and name = "阿柒"; #索引生效
select * from user where name = "阿柒"; #索引失效
select * from user where id = 1 and age = 21; #索引失效
索引查詢
明顯查詢索引表比直接查詢資料表要快的多，首先，索引表是排序了，可以類似二分查找，非常有效的提高了查詢的速度，
其程序如下圖，先到事先排序好的索引表中檢索查詢，找到其主鍵后，就直接定位到記錄所在位置，然后直接回傳這條資料，
- 排序，tree結構，類似二分查找
- 索引表小
優點:
- 索引是資料庫優化
- 表的主鍵會默認自動創建索引
- 每個欄位都可以被索引
- 大量降低資料庫的IO磁盤讀寫成本，極大提高了檢索速度
- 索引事先對資料進行了排序，大大提高了查詢效率
缺點:
- 索引本身也是一張表，該表保存了主鍵與索引欄位，并指向物體表的記錄，所以索引列也要占用空間
- 索引表中的內容，在業務表中都有，資料是重復的，空間是“浪費的”
- 雖然索引大大提高了查詢的速度，但對資料的增、刪、改的操作需要更新索引表資訊，如果資料量非常巨大，更新效率就很慢,因為更新表時,MySQL不僅要保存資料,也要保存一下索引檔案
- 隨著業務的不斷變化，之前建立的索引可能不能滿足查詢需求，需要消耗我們的時間去更新索引
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/296422.html
標籤：MySQL
上一篇：博客專案總結
下一篇：TiDB 學習筆記一（運維管理）