1. 什麼是索引?
索引是一種特殊的文件(InnoDB數據表上的索引是表空間的一個組成部分),它們包含著對數據表裡所有記錄的引用指針。
索引是一種數據結構。數據庫索引,是數據庫管理系統中一個排序的數據結構,以協助快速查詢、更新數據庫表中數據。索引的實現通常使用B樹及其變種B+樹。
更通俗的說,索引就相當於目錄。為了方便查找書中的內容,通過對內容建立索引形成目錄。索引是一個文件,它是要佔據物理空間的。
2. 索引有哪些優缺點?
索引的優點
· 可以大大加快數據的檢索速度,這也是創建索引的最主要的原因。
· 通過使用索引,可以在查詢的過程中,使用優化隱藏器,提高系統的性能。
索引的缺點
· 時間方面:創建索引和維護索引要耗費時間,具體地,當對錶中的數據進行增加、刪除和修改的時候,索引也要動態的維護,會降低增/改/刪的執行效率;
· 空間方面:索引需要佔物理空間。
3. 索引使用場景(重點)
where
上圖中,根據id查詢記錄,因為id字段僅建立了主鍵索引,因此此SQL執行可選的索引只有主鍵索引,如果有多個,最終會選一個較優的作為檢索的依據。
-- 增加一個沒有建立索引的字段
alter table innodb1 add sex char(1);
-- 按sex檢索時可選的索引為null
EXPLAIN SELECT * from innodb1 where sex='男';
可以嘗試在一個字段未建立索引時,根據該字段查詢的效率,然後對該字段建立索引(alter table 表名 add index(字段名)),同樣的SQL執行的效率,你會發現查詢效率會有明顯的提升(數據量越大越明顯)。
order by
當我們使用order by將查詢結果按照某個字段排序時,如果該字段沒有建立索引,那麼執行計劃會將查詢出的所有數據使用外部排序(將數據從硬盤分批讀取到內存使用內部排序,最後合併排序結果),這個操作是很影響性能的,因為需要將查詢涉及到的所有數據從磁盤中讀到內存(如果單條數據過大或者數據量過多都會降低效率),更無論讀到內存之後的排序了。
但是如果我們對該字段建立索引alter table 表名 add index(字段名),那麼由於索引本身是有序的,因此直接按照索引的順序和映射關係逐條取出數據即可。而且如果分頁的,那麼只用取出索引表某個範圍內的索引對應的數據,而不用像上述那取出所有數據進行排序再返回某個範圍內的數據。(從磁盤取數據是最影響性能的)
join
對join語句匹配關係(on)涉及的字段建立索引能夠提高效率
索引覆蓋
如果要查詢的字段都建立過索引,那麼引擎會直接在索引表中查詢而不會訪問原始數據(否則只要有一個字段沒有建立索引就會做全表掃描),這叫索引覆蓋。因此我們需要儘可能的在select後只寫必要的查詢字段,以增加索引覆蓋的幾率。
這裡值得注意的是不要想著為每個字段建立索引,因為優先使用索引的優勢就在於其體積小。
4. 索引有哪幾種類型?
主鍵索引: 數據列不允許重複,不允許為NULL,一個表只能有一個主鍵。
唯一索引: 數據列不允許重複,允許為NULL值,一個表允許多個列創建唯一索引。
· 可以通過 ALTER TABLE table_name ADD UNIQUE (column); 創建唯一索引
· 可以通過 ALTER TABLE table_name ADD UNIQUE (column1,column2); 創建唯一組合索引
普通索引: 基本的索引類型,沒有唯一性的限制,允許為NULL值。
· 可以通過ALTER TABLE table_name ADD INDEX index_name (column);創建普通索引
· 可以通過ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);創建組合索引
全文索引:是目前搜索引擎使用的一種關鍵技術。
· 可以通過ALTER TABLE table_name ADD FULLTEXT (column);創建全文索引
5. 索引的數據結構(b樹,hash)
索引的數據結構和具體存儲引擎的實現有關,在MySQL中使用較多的索引有Hash索引,B+樹索引等,而我們經常使用的InnoDB存儲引擎的默認索引實現為:B+樹索引。對於哈希索引來說,底層的數據結構就是哈希表,因此在絕大多數需求為單條記錄查詢的時候,可以選擇哈希索引,查詢性能最快;其餘大部分場景,建議選擇BTree索引。
1)B樹索引
mysql通過存儲引擎取數據,基本上90%的人用的就是InnoDB了,按照實現方式分,InnoDB的索引類型目前只有兩種:BTREE(B樹)索引和HASH索引。B樹索引是Mysql數據庫中使用最頻繁的索引類型,基本所有存儲引擎都支持BTree索引。通常我們說的索引不出意外指的就是(B樹)索引(實際是用B+樹實現的,因為在查看錶索引時,mysql一律打印BTREE,所以簡稱為B樹索引)
查詢方式:
主鍵索引區:PI(關聯保存的時數據的地址)按主鍵查詢,
普通索引區:si(關聯的id的地址,然後再到達上面的地址)。所以按主鍵查詢,速度最快
B+tree性質:
1.)n棵子tree的節點包含n個關鍵字,不用來保存數據而是保存數據的索引。
2.)所有的葉子結點中包含了全部關鍵字的信息,及指向含這些關鍵字記錄的指針,且葉子結點本身依關鍵字的大小自小而大順序鏈接。
3.)所有的非終端結點可以看成是索引部分,結點中僅含其子樹中的最大(或最小)關鍵字。
4.)B+ 樹中,數據對象的插入和刪除僅在葉節點上進行。
5.)B+樹有2個頭指針,一個是樹的根節點,一個是最小關鍵碼的葉節點。
2)哈希索引
簡要說下,類似於數據結構中簡單實現的HASH表(散列表)一樣,當我們在mysql中用哈希索引時,主要就是通過Hash算法(常見的Hash算法有直接定址法、平方取中法、摺疊法、除數取餘法、隨機數法),將數據庫字段數據轉換成定長的Hash值,與這條數據的行指針一併存入Hash表的對應位置;如果發生Hash碰撞(兩個不同關鍵字的Hash值相同),則在對應Hash鍵下以鏈表形式存儲。當然這只是簡略模擬圖。
6. 索引的基本原理
索引用來快速地尋找那些具有特定值的記錄。如果沒有索引,一般來說執行查詢時遍歷整張表。
索引的原理很簡單,就是把無序的數據變成有序的查詢
1. 把創建了索引的列的內容進行排序
2. 對排序結果生成倒排表
3. 在倒排表內容上拼上數據地址鏈
4. 在查詢的時候,先拿到倒排表內容,再取出數據地址鏈,從而拿到具體數據
7. 索引算法有哪些?
索引算法有 BTree算法和Hash算法
BTree算法
BTree是最常用的mysql數據庫索引算法,也是mysql默認的算法。因為它不僅可以被用在=,>,>=,
-- 只要它的查詢條件是一個不以通配符開頭的常量
select * from user where name like 'jack%';
-- 如果一通配符開頭,或者沒有使用常量,則不會使用索引,例如:
select * from user where name like '%jack';
Hash算法
Hash Hash索引只能用於對等比較,例如=,<=>(相當於=)操作符。由於是一次定位數據,不像BTree索引需要從根節點到枝節點,最後才能訪問到頁節點這樣多次IO訪問,所以檢索效率遠高於BTree索引。
8. 索引設計的原則?
1. 適合索引的列是出現在where子句中的列,或者連接子句中指定的列
2. 基數較小的類,索引效果較差,沒有必要在此列建立索引
3. 使用短索引,如果對長字符串列進行索引,應該指定一個前綴長度,這樣能夠節省大量索引空間
4. 不要過度索引。索引需要額外的磁盤空間,並降低寫操作的性能。在修改表內容的時候,索引會進行更新甚至重構,索引列越多,這個時間就會越長。所以只保持需要的索引有利於查詢即可。
9. 創建索引的原則(重中之重)
索引雖好,但也不是無限制的使用,最好符合一下幾個原則
1) 最左前綴匹配原則,組合索引非常重要的原則,mysql會一直向右匹配直到遇到範圍查詢(>、 3 and d = 4 如果建立(a,b,c,d)順序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引則都可以用到,a,b,d的順序可以任意調整。
2)較頻繁作為查詢條件的字段才去創建索引
3)更新頻繁字段不適合創建索引
4)若是不能有效區分數據的列不適合做索引列(如性別,男女未知,最多也就三種,區分度實在太低)
5)儘量的擴展索引,不要新建索引。比如表中已經有a的索引,現在要加(a,b)的索引,那麼只需要修改原來的索引即可。
6)定義有外鍵的數據列一定要建立索引。
7)對於那些查詢中很少涉及的列,重複值比較多的列不要建立索引。
8)對於定義為text、image和bit的數據類型的列不要建立索引。
10. 創建索引的三種方式,刪除索引
第一種方式:在執行CREATE TABLE時創建索引
CREATE TABLE user_index2 (
id INT auto_increment PRIMARY KEY,
first_name VARCHAR (16),
last_name VARCHAR (16),
id_card VARCHAR (18),
information text,
KEY name (first_name, last_name),
FULLTEXT KEY (information),
UNIQUE KEY (id_card)
);
第二種方式:使用ALTER TABLE命令去增加索引
ALTER TABLE table_name ADD INDEX index_name (column_list);
· 1
ALTER TABLE用來創建普通索引、UNIQUE索引或PRIMARY KEY索引。
其中table_name是要增加索引的表名,column_list指出對哪些列進行索引,多列時各列之間用逗號分隔。
索引名index_name可自己命名,缺省時,MySQL將根據第一個索引列賦一個名稱。另外,ALTER TABLE允許在單個語句中更改多個表,因此可以在同時創建多個索引。
第三種方式:使用CREATE INDEX命令創建
CREATE INDEX index_name ON table_name (column_list);
CREATE INDEX可對錶增加普通索引或UNIQUE索引。(但是,不能創建PRIMARY KEY索引)
刪除索引
根據索引名刪除普通索引、唯一索引、全文索引:alter table 表名 drop KEY 索引名
alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;
刪除主鍵索引:alter table 表名 drop primary key(因為主鍵只有一個)。這裡值得注意的是,如果主鍵自增長,那麼不能直接執行此操作(自增長依賴於主鍵索引):
需要取消自增長再行刪除:
alter table user_index
-- 重新定義字段
MODIFY id int,
drop PRIMARY KEY
但通常不會刪除主鍵,因為設計主鍵一定與業務邏輯無關。
小編分類整理了許多java進階學習材料和BAT面試題,需要資料的請加QQ群:731611386 就能領取2019年java進階學習資料和BAT面試題以及《Effective Java》(第3版)電子版書籍。
閱讀更多 newer大俠 的文章