精心總結十三條建議,幫你創建更合適的MySQL索引

来源:https://www.cnblogs.com/yidengjiagou/archive/2022/07/31/16538652.html
-Advertisement-
Play Games

上篇文章講到使用MySQL的Explain命令可以分析SQL性能瓶頸,優化SQL查詢,以及查看是否用到了索引。 我們都知道創建索引可以提高查詢效率,但是具體該怎麼創建索引? 哪些欄位適合創建索引? 哪些欄位又不適合創建索引? 本文跟大家一塊學習一下如何創建合適資料庫索引。 ...


上篇文章講到使用MySQL的Explain命令可以分析SQL性能瓶頸,優化SQL查詢,以及查看是否用到了索引。

我們都知道創建索引可以提高查詢效率,但是具體該怎麼創建索引?

哪些欄位適合創建索引?

哪些欄位又不適合創建索引?

本文跟大家一塊學習一下如何創建合適資料庫索引。

1. MySQL索引的分類

在創建索引之前瞭解一下MySQL有哪些索引,然後我們才能選擇合適的索引。

常見的索引有,普通索引、唯一索引、主鍵索引、聯合索引、全文索引等。

普通索引

普通索引就是最基本的索引,沒有任何限制。

可以使用命令創建普通索引:

ALTER TABLE `table_name` ADD INDEX index_name (`column`);

唯一索引

與普通索引不同,唯一索引的列值必須唯一,允許為null。

創建方式是這樣的:

ALTER TABLE `table_name` ADD UNIQUE index_name (`column`);

主鍵索引

主鍵索引是一種特殊的唯一索引,並且一張表只有一個主鍵,不允許為null。

創建方式是這樣的:

ALTER TABLE `table_name` ADD PRIMARY KEY (`column`);

聯合索引

聯合索引是同時在多個欄位上創建索引,查詢效率更高。

創建方式是這樣的:

ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`);

全文索引

全文索引主要用來匹配字元串文本中關鍵字。

當需要字元串中是否包含關鍵字的時候,我們一般用like,如果是以%開頭的時候,則無法用到索引,這時候就可以使用全文索引了。

創建方式是這樣的:

ALTER TABLE `table_name` ADD FULLTEXT (`column`);

2. 哪些欄位適合創建索引?

我總結了有以下幾條:

2.1 頻繁查詢的欄位適合創建索引

一張表的欄位總會有冷熱之分,很明顯那些頻繁使用的欄位更適合為它創建索引。

2.2 在where和on條件出現的欄位優先創建索引

為什麼不是在select後面出現的欄位優先創建索引?

因為查詢SQL會先匹配on和where條件的欄位,具體的匹配順序是這樣的:

from > on > join > where > group by > having > select > distinct > order by > limit

2.3 區分度高的欄位適合創建索引

比如對於一張用戶表來說,生日比性別的區分度更高,更適合創建索引。

可以使用下麵的方式手動統計一下,每個欄位的區分度,值越大,區分度越高:

select 
    count(distinct birthday)/count(*), 
    count(distinct gender)/count(*) 
from user;

image

對於已經創建好的索引,我們還可以使用MySQL命令查看每個索引的區分度排名:

image

圖中Cardinality列表示索引的區分度排名,也被稱為基數。

2.4 有序的欄位適合創建索引

有序的欄位在插入資料庫的過程中,仍能保持B+樹的索引結構,不需要頻繁更新索引文件,性能更好。

3. 哪些欄位不合適創建索引?

說完哪些欄位適合創建索引,就有不適合創建索引的的欄位。

3.1 區分度低的欄位不適合創建索引。

剛纔說了用戶表中性別的區分度較低,不如生日欄位適合創建索引。

3.2 頻繁更新的欄位不適合創建索引

更新欄位的過程中,需要維護B+樹結構,會頻繁更新索引文件,降低SQL性能。

3.3 過長的欄位不適合創建索引

過長的欄位會占用更多的空間,不適合創建索引。

3.4 無序的欄位不適合創建索引

無序的欄位在插入資料庫的過程中,為了維護B+樹索引結構,需要頻繁更新索引文件,性能較差。

4. 創建索引的其他註意事項

4.1 優先使用聯合索引

查詢的時候,聯合索引比普通索引能更精準的匹配到所需數據。

image

圖中就是在(age,name)兩個欄位上建立的聯合索引,在B+樹中的存儲結構。

可以看出,是先age排序,age相等的數據,再按name排序。

對於這條查詢SQL:

select age,name from user where age=18 and name='李四';

聯合索引只需一次就可以查到所需數據,如果我們只在age欄位上建立索引,會先匹配到age=18的三條數據,然後再逐個遍歷,效率更差,所以平時應該優先使用聯合索引。

4.2 使用聯合索引時,區分度的欄位放前面

這樣可以減少查詢次數,更快地匹配到所需數據。

4.3 過長字元串可以使用首碼索引

比如在匹配用戶地址的時候,如果鄉鎮已經能區分大部分用戶了,就沒必要精確到街道小區了。

創建普通索引的時候,指定索引長度,就可以創建首碼索引了。

ALTER TABLE `user` ADD INDEX idx_address (address(3));

4.4 值唯一的欄位,使用唯一索引

使用唯一索引,可以避免程式bug導致產生重覆數據。

4.5 排序和分組欄位也儘量創建索引

在order by和group by中的欄位也儘量創建索引,避免使用文件排序,可以使用索引排序提供性能。

4.6 避免創建過多索引

索引好用,適度即可。創建過多的索引,會占用更多存儲空間,也會嚴重影響SQL性能,每次更新SQL,都需要更新大量索引文件,得不償失。

知識點總結:

image

文章持續更新,可以微信搜一搜「 一燈架構 」第一時間閱讀更多技術乾貨。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 據我理解,標簽是為了使得jsp內容更加簡潔,編寫起來更加方便,不用再去在jsp里寫大段的java代碼,標簽就顯得與html格外契合;當然,並不是一定要使用標簽,只用jsp的語法來寫java代碼也可以實現功能,只是稍顯繁瑣。 言歸正傳,我們在使用標簽之前首先需要導入兩個依賴 <dependency> ...
  • 作者:Steven Giesel 翻譯:Alan Wang 校對:李衛涵 – 微軟 MVP 排版:Rani Sun 有什麼比參考包含分步說明和代碼示例的動手教程更好的學習新技術的方式呢?當你完成或 fork 本教程後,你將得到這樣一個應用程式: Steven Giesel 最近發佈了一個由5部分內容 ...
  • 自從2021年2月第20輪公佈的測試以後,一年半後 的2022年7月19日 發佈了 TechEmpower 21輪測試報告:Round 21 results - TechEmpower Framework Benchmarks。Techempower benchmark是包含範圍最廣泛的web框架性 ...
  • 我個人對GRPC是比較感興趣的,最近在玩通過前端調用GRPC。通過前端調用GRPC業界有兩種方式:GRPC Web和GRPC JSON轉碼。 GRPC Web 通過JS或者Blazor WASM調用GRPC,微軟在這方面做的還是很好的,從.NET Core3.0之後就提供了兩種實現GRPC Web的 ...
  • Doxillion是一款多格式文檔轉換器軟體,支持在Microsoft office文件(如doc,docx,xls和xlsx)之間進行轉換,且一次可以轉換數千個文件。Doxillion還支持從WordPerfect,txt,Open office,odt等轉換為pdf,docx等,可以大大的提升你 ...
  • Music Tag Editor 中文版是一款macOS平臺的音頻標簽管理工具,支持UNICODE,軟體帶有PLS/M3U列表生成器,還可以生成XML/TXT/CSV格式的播放列表,通過它用戶可以快速修改音樂文件的tag信息、創建播放列表,另外,軟體自帶播放器,可以播放常見的音頻文件。 詳情:Mus ...
  • PDFelement Pro Mac是一款可以幫助用戶編輯PDF的工具,其設計的功能針對中小型用戶開發,支持常規的編輯、修改、操作,並且可以實現PDF文件轉換功能,您可以將word、Excel等office文件轉換為PDF文件保存,讓您可以擁有一款簡單、高效的PDF辦公軟體。 詳情:Wondersh ...
  • SoundSource 5 for Mac是一款優質的音頻控制軟體,可讓您直接從菜單欄調整輸入,輸出和音效設備以及音量設置。啟用輸入設備的軟播放到您想要的輸出,這樣您就可以聽到通過任何麥克風或其他來源發出的聲音。 詳情:SoundSource 5 for Mac(音頻控制工具) 功能介紹 1、快速訪 ...
一周排行
    -Advertisement-
    Play Games
  • 一、引言:什麼是 JSON JSON (Java Script Object Notation) 是一種很常用的數據格式,它常常用在 web 應用程式中。它可以表示結構化的數據。 下麵是常見的 JSON 文件結構 { "name": "Kamishiro Rize", "age": "22", "o ...
  • 前言 大家好,我是蝸牛,在上一篇中,我們介紹了不同版本的HTTP區別和發展背景,這篇文章我們來聊聊HTTP的缺點,HTTP缺點大致總結有以下三點: 通信使用明文(不加密),內容可能會被竊聽。 不驗證通信方的身份,因此有可能遭遇偽裝(客戶端和服務端都有可能) 無法證明報文的完整性,有可能會被篡改。 其 ...
  • resultMap處理欄位和屬性的映射關係 如果欄位名與實體類中的屬性名不一致,該如何處理映射關係? 第一種方法:為查詢的欄位設置別名,和屬性名保持一致 下麵是實體類中的屬性名: private Integer empId; private String empName; private Integ ...
  • 大家在看到這篇文章前,為了有一個舒適的c++IDE,一定感受到了Dev-c++的廉價感,Clion功能的多餘,VS的臃腫。他們也有自己的優點,但糟點太多,令人十分難受。而VS Code,可以取長補短。下麵的配置內容,可以讓你在刷題時,享受絲滑的動畫,體會集成終端的方便,讓你覺得Coding不再枯燥。 ...
  • 給定一個不含重覆數字的數組 nums ,返回其 所有可能的全排列 。你可以 按任意順序 返回答案。 示例 1: 輸入:nums = [1,2,3] 輸出:[[1,2,3],[1,3,2],[2,1,3],[2,3,1],[3,1,2],[3,2,1]] 示例 2: 輸入:nums = [0,1] 輸 ...
  • 設計模式的目的 編寫軟體過程中,程式員面臨著來自 耦合性,內聚性以及可維護性,可擴展性,重用性,靈活性 等多方面的 挑戰,設計模式是為了讓程式(軟體),具有更好 代碼重用性 (即:相同功能的代碼,不用多次編寫) 可讀性 (即:編程規範性, 便於其他程式員的閱讀和理解) 可擴展性 (即:當需要增加新的 ...
  • 本文講解了決策樹的創鍵的過程,包括熵,信息增益的計算,還有決策樹的創建,以及使用matplotlib讓決策樹可視化的詳細過程 ...
  • ♠ use C++11 倍數 若 $a,b,k \in \mathbb N$,且 $a \times k=b$,那麼 $b$ 是 $a$ 的倍數,稱 $a$ 整除 $b$,記作 $a \mid b$。 $[1,n]\in \mathbb N$ 中 $x \in \mathbb N$ 的倍數有 $\l ...
  • LinkList可以定義指向List的指針 1.當函數參數為LinkList L時,意味著只改變或操作List的內容,而不需要改變L這個指針 如 Status GetElem(LinkList L,int i,ElemType) 2.當參數為LinkList &L時,意味著需要改變或操作L這個指針本 ...
  • Spring 5框架 一、Spring概念 1、Spring是輕量級的JavaEE框架 2、Spring可以解決企業應用開發的複雜性 3、Spring有兩個核心部分:IOC和AOP ​ 1)IOC:控制反轉,把創建對象過程交給Spring進行管理 ​ 2)AOP:面向切麵,不修改源代碼進行功能增強 ...