编程知识 cdmana.com

精通MySQL之索引篇,這篇注重練習!

  • 老劉是即將找工作的研究生,自學大資料開發,一路走來,感慨頗深,網上大資料的資料良莠不齊,於是想寫一份詳細的大資料開發指南。這份指南把大資料的【基礎知識】【框架分析】【原始碼理解】都用自己的話描述出來,讓夥伴自學從此不求人。
  • 大資料開發指南地址如下:
    • github:https://github.com/BigDataLaoLiu/BigDataGuide
    • 碼雲:https://gitee.com/BigDataLiu/BigDataGuide
  • 您的點贊是我持續更新的動力,禁止白嫖,看了就要有收穫,有需要聯絡公眾號:努力的老劉。

今天給大家分享的是大資料開發基礎部分MySQL的索引篇,這篇內容大家一定要跟著練習,不然等於白看!

索引是什麼?

在日常開發中常常會遇到查詢比較慢的情況,我們的第一反應就是給它加索引,那索引是什麼呢?官方介紹索引是幫助MySQL高效獲取資料的資料結構,資料庫索引好比是一本書的目錄,能加快資料庫的資料查詢速度。

那索引的好處有哪些呢?

  1. 它可以提高資料檢索的效率,降低資料庫的成本。
  2. 通過索引對資料進行排序,降低資料排序的成本,降低CPU消耗。

任何事情都會有正反面,索引也不例外,那索引的壞處有哪些呢?

  1. 索引會佔據磁碟空間。
  2. 索引雖然會提高查詢效率,但會降低更新表的效率。
  3. MySQL不僅要儲存資料,還有儲存或者更新對應的索引檔案。

那是不是有壞處就不用索引呢?

當然不是,索引必須拿來。一般來說索引本身也很大,不可能全部儲存在記憶體中,因此索引往往是儲存在磁碟檔案上的檔案中。

索引的分類

  1. 單列索引:
    • 普通索引:add unique
    • 唯一索引:索引列中的值必須是唯一的,但允許有空值,add unique index
    • 主鍵索引:是一種特殊的唯一索引,不允許有空值
  2. 組合索引:
    • 在表中的多個欄位組合上建立的索引
    • 組合索引的使用,需要遵循最左字首原則
    • 一般情況下,建議使用組合索引代替單列索引(主鍵索引除外)
  3. 全文索引:只有在MyIsam、InnoDB上才能使用,而且只能在char、varchar、text型別欄位上使用全文索引。
  4. 空間索引:一般用不到

索引的使用

建立索引

CREATE INDEX index_name ON table(column(length)) ;

刪除索引

DROP INDEX index_name ON table;

檢視索引

SHOW INDEX FROM table_name \G;

索引原理(重點)

索引的儲存結構

說索引原理之前,先說說索引儲存結構。索引是在儲存引擎中實現的,也就是不同的儲存引擎,會使用不同索引。其中MyIsam和InnoDB只支援B+數索引,老劉先不講B樹和B+樹的概念,大家自行搜尋。

接下來就是索引的重點,搞清楚了非聚集索引和聚集索引,索引原理就差不多了!

非聚集索引(MyIsam)

它說的是B+樹葉子節點只會儲存資料行(資料檔案)的指標,即資料和索引不在一起。它包含主鍵索引和輔助索引,都會儲存指標的值。

主鍵索引

MyIsam中B+樹葉子節點儲存的資料是資料的指標值,通過索引樹找到對應的索引,然後通過索引中儲存的記錄指標,找到資料檔案中對應的記錄。

輔助索引(次要索引)

在MyIsam中,主索引和輔助索引在結構上沒有任何區別,只是主索引要求key是唯一的,而輔助索引的key是可以重複的。

聚集索引(InnoDB)

  • 主鍵索引(聚集索引)的葉子節點會儲存資料行,也就是說資料和索引在一起。
  • 輔助索引只會儲存主鍵值。
  • 如果沒有主鍵,則使用唯一索引建立聚集索引;如果沒有唯一索引,MySQL會按照一定規則建立聚集索引。

主鍵索引

在InnoDB中要求表必須有主鍵(MyIsam可以沒有),如果沒有顯示指定,則MySQL系統會自動選擇一個可以唯一標識資料記錄的列作為主鍵,如果不存在這種列,則MySQL自動為InnoDB表生成一個隱含欄位作為主鍵型別為長整形。

上圖是 InnoDB 主索引(同時也是資料檔案)的示意圖,可以看到葉節點包含了完整的資料記錄,這種索引叫做聚集索引。因為 InnoDB 的資料檔案本身要按主鍵聚集。

輔助索引

InnoDB 的輔助索引 data 域儲存相應記錄主鍵的值而不是地址。換句話說,InnoDB 的所有輔助索引都引用主鍵作為 data 域。

聚集索引這種實現方式使得按主鍵的搜尋十分高效,但是輔助索引搜尋需要檢索兩遍索引:首先檢索輔助索引獲得主鍵,然後用主鍵到主索引中檢索獲得記錄,即回表查詢。

select * from user where name='Alice'

根據這段SQL語句,會進行回表查詢,檢索兩次,才會獲得記錄。回表效能比較低,儘量做到不回表。

索引使用場景

介紹完索引的相關概念後,老劉必須給講講哪些場景下可以使用場景,大家記幾個就行。

哪些情況下需要使用索引

  1. 主鍵自動建立唯一索引
  2. 頻繁作為查詢條件的欄位應該建立索引
  3. 多表關聯查詢中,關聯欄位應該建立索引
  4. 查詢中排序的欄位應該建立索引
  5. 頻繁查詢欄位
  6. 查詢中統計或者分組欄位應該建立索引

哪些情況下不需要建立索引

  1. 表記錄太少,沒必要建立索引
  2. 經常進行增刪改的表
  3. 頻繁更新的欄位
  4. where條件裡使用頻率不高的欄位

為什麼推薦多使用組合索引?

為了節省mysql索引儲存空間以及提升搜尋效能,能使用組合索引就不使用單列索引。

使用組合索引需要遵循最左字首原則,什麼是最左字首原則?

  1. 字首索引:where like a%

    萬用字元%在右邊不在左邊,什麼是字首索引呢?當索引是很長的字元序列時,這個索引會很慢,佔用記憶體。如果以name為索引,當name對應的字串很長時,就可以用字首索引where like a%。

  2. 從左到右都有索引,不能斷,直到遇到範圍查詢<,>,between。

索引失效

我們進行資料查詢很慢時,可能就會存在索引失效的情況。遇到這種情況不要怕,我們可以使用explain命令對select語句的執行計劃進行分析。explain出來的資訊有10列,分別是

id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

下面老劉就使用一個案例進行這些引數進行說明,大家可以跟著老劉一起練習。這10個引數老劉只講重要的,其他的大家自行學習。

--使用者表
create table tuser(
id int primary key,
loginname varchar(100),
name varchar(100),
age int,
sex char(1),
dep int,
address varchar(100)
);
--部門表
create table tdep(
id int primary key,
name varchar(100)
);
--地址表
create table taddr(
id int primary key,
addr varchar(100)
);
--建立普通索引
mysql> alter table tuser add index idx_dep(dep);
--建立唯一索引
mysql> alter table tuser add unique index idx_loginname(loginname);
--建立組合索引
mysql> alter table tuser add index idx_name_age_sex(name,age,sex);
--建立全文索引
mysql> alter table taddr add fulltext ft_addr(addr);

id

每個SELECT語句都會自動分配的一個唯一識別符號,表示查詢中操作表的順序,有四種情況:

  • id相同:執行順序由上到下
  • id不同:如果是子查詢,id號會自增,id越大,優先順序越高。
  • id相同的不同的同時存在
  • id列為null的就表示這是一個結果集,不需要使用它來進行查詢。

select_type(重要)

表示查詢型別,主要用於區別普通查詢、聯合查詢(union、union all)、子查詢等複雜查詢。

simple,表示不需要union操作或者不包含子查詢的簡單select查詢。

primary,一個需要union操作或者含有子查詢的select,位於最外層的單位查詢的select_type即為primary,並且只有有一個 。

先執行括號裡面的sql語句,再執行外面的sql語句,內層的查詢就是subquery。

subquery,除了from字句中包含的子查詢外,其他地方出現的子查詢都可能是subquery。

dependent subquery,表示這個subquery的查詢要受到外部表查詢的影響。

union,它連線的兩個select查詢,第一個查詢是PRIMARY,除了第一個表外,第二個以後的表select_type都是union。

dependent union,它與union一樣,出現在union 或union all語句中,但是這個查詢要受到外部查詢的影響。

union result,它包含union的結果集,在union和union all語句中,因為它不需要參與查詢,所以id欄位為null。

derived,from字句中出現的子查詢,也叫做派生表,其他資料庫中可能叫做內聯檢視或巢狀select。

可以理解為就是from字句後面出現子查詢,取個別名,叫派生表。

table

顯示查詢的表名,如果查詢使用了別名,那麼這裡顯示的是別名。

type(重要)

它會顯示很多引數型別,效能依次從好到壞顯示為這樣:

system,const,eq_ref,ref,fulltext,ref_or_null,unique_subquery,index_subquery,range,index_merge,index,ALL

除了all之外,其他的type都可以使用到索引,除了index_merge之外,其他的type只可以用到一個索引,優化器會選用最優索引一個,最少要索引使用到range級別。 老劉只講這個重要的,有些內容也沒搞清楚。

system

可遇不可求,表中只有一行資料或是空表。

const(重要)

使用唯一索引或主鍵,返回記錄一定是1行記錄的等值where條件。

eq_ref(重要)

一般是連線欄位主鍵或者唯一性索引。

此型別通常出現在多表的 join 查詢,表示對於前表的每一個結果,都只能匹配到後表的一行結果。並且查詢的比較操作通常是 '=',查詢效率較高。

ref(重要)

針對非唯一性索引,使用等值(=)查詢非主鍵。或者是使用了最左字首規則索引的查詢。

range(重要)

索引範圍掃描,常見於使用>,<,is null,between ,in ,like等運算子的查詢中。

index(重要)

關鍵字:條件是出現在索引樹中的節點的,可能沒有完全匹配索引。

索引全表掃描,把索引從頭到尾掃一遍,常見於使用索引列就可以處理不需要讀取資料檔案的查詢、可以使用索引排序或者分組的查詢。

all(重要)

這個就是全表掃描資料檔案,然後再在server層進行過濾返回符合要求的記錄。

possible_keys、key、key_len、ref、rows就不講了,直接講最後一個extra。

extra

這個列包含不適合在其他列中顯示單十分重要的額外的資訊,這個列可以顯示的資訊非常多,有幾十種,這裡寫常見的幾種。

no tables used

表示不帶from字句的查詢,使用not in()形式子查詢或not exists運算子的連線查詢,這種叫做反連線。一般連線查詢是先查詢內表,再查詢外表,反連線就是先查詢外表,再查詢內表。

using filesort(重要)

排序時無法使用到索引時,就會出現這個,常見於order by和group by語句中。

using index(重要)

查詢時不需要回表查詢,直接通過索引就可以獲取查詢的資料。

using where(重要)

通常type型別為all,記錄並不是所有的都滿足查詢條件,通常有where條件,並且一般沒索引或者索引失效。

講完分析索引的引數後,現在老劉講一些索引失效的情況,大家一定要用心記住,老劉也記了好幾遍!

索引失效分析

  1. 一般SQL語句查詢採用全值匹配,資料上叫全值匹配我最愛。

  2. 最左字首法則,對於組合索引而言,查詢從索引的最左前列開始,並且不能跳過索引中的列不然就會失效。

    現在舉一個帶頭的索引斷(帶頭索引生效,其他索引失效)的例子:

  3. 不要在索引上做計算,例如計算、函式、自動/手動型別轉換,不然會導致索引失效而轉向全表掃描。

  4. 範圍條件右邊的列失效,就是不能繼續使用索引中範圍條件(bettween、<、>、in等)右邊的列。

  5. 儘量使用覆蓋索引(只查詢索引的列),也就是索引列和查詢列一致,減少select *。

  6. 索引欄位上不要使用不等,索引欄位上使用(!= 或者 < >)判斷時,會導致索引失效而轉向全表掃描。

  7. 主鍵索引欄位上不可以判斷null,索引欄位上使用 is null 判斷時,可使用索引。

  8. 索引欄位使用like以萬用字元開頭(‘%字串’)時,會導致索引失效而轉向全表掃描。like要以萬用字元結束相當於範圍查詢,索引不會失效。

  9. 索引欄位是字串時,要加單引號,否則會導致索引失效而轉向全表掃描。

  10. 索引欄位不要使用or,否則會導致索引失效而轉向全表掃描。

總結

這篇內容大家一定要跟著老劉練習,光看不練等於白學!儘管當前水平可能不及各位大佬,但老劉會努力變得更加優秀,讓各位小夥伴自學從此不求人!

如果有相關問題,聯絡公眾號:努力的老劉。文章都看到這了,點贊關注支援一波!

版权声明
本文为[itread01]所创,转载请带上原文链接,感谢
https://www.itread01.com/content/1612080125.html

Scroll to Top