精心總結十三條建議,幫你創建更合適的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
  • 記錄一下Winform程式打包過程 參考文章:VS2017 WinFrom打包設置與教程 下載 Visual Studio Installer 拓展插件 從VS2017開始VS已預設不再集成Installer拓展,所以需要手動下載安裝,在 工具 - 插件和更新 裡面的插件商店裡面搜索"install ...
  • 先下載一個gif圖片,這裡提供一個gif圖片示例 線上GIF圖片幀拆分工具 - UU線上工具 (uutool.cn) 使用這個網站將gif切成單張圖片 創建一個Avalonia MVVM的項目,將圖片copy進去 在項目文件中添加一下代碼: <ItemGroup> <None Update="gif ...
  • 1 簡介 Dapr是一個可移植的、事件驅動的運行時,它使任何開發人員能夠輕鬆構建出彈性的、無狀態和有狀態的應用程式,並可運行在雲平臺或邊緣計算中,它同時也支持多種編程語言和開發框架。Dapr支持的語言很多,包括C++/Go/Java/JavaScript/Python/Rust/.NET/PHP等。 ...
  • 作者:大眼睛圖圖 鏈接:https://juejin.cn/post/7156483709338648613 前言 還有誰?剛上三天班就被公司公司的工作不適合我,叫我先提升一下。 後面我也向公司那邊討要了一個說法,我只能說他們那邊的說辭讓我有些不服氣。 現在之所以把這件事在掘金上記錄一下,一是記錄一 ...
  • 雲原生下的流水線是通過啟動容器來運行具體的功能步驟,每次運行流水線可能會被調度到不同的計算節點上。這會導致一個問題:容器運行完是不會保存數據的,每當流水線重新運行時,又會重新拉取代碼、編譯代碼、下載依賴包等等。在雲原生場景下,不存在本地宿主機編譯代碼、構建鏡像時緩存的作用,大大延長了流水線運行時間,... ...
  • 本文介紹基於Python語言中TensorFlow的tf.estimator介面,實現深度學習神經網路回歸的具體方法。 () 1 寫在前面 1. 本文介紹的是基於TensorFlow tf.estimator介面的深度學習網路,而非TensorFlow 2.0中常用的Keras介面;關於Keras接 ...
  • 基礎 我是在ruoyi-vue已經安裝了mybatis-plus的基礎上進行的修改 關於SQLite SQLite 是一個軟體庫,實現了自給自足的、無伺服器的、零配置的、事務性的 SQL 資料庫引擎。 SQLite主頁:https://www.sqlite.org/index.html 1. pom ...
  • 1. 服務監控三要素^1 服務監控需要滿足的三要素分別如下: 日誌監控 指標監控 請求鏈路追蹤 服務監控只要能滿足這三個要素,基本就能實現我們想要的監控效果。 1.1.主流APM系統^1 APM 系統(Application Performance Management,即應用性能管理)是對企業的應 ...
  • 雙重檢查鎖定與延遲初始化 在 java 程式中,有時候可能需要推遲一些高開銷的對象初始化操作,並且只有在使用這些對象時才進行初始化。此時程式員可能會採用延遲初始化。但要正確實現線程安全的延遲初始化需要一些技巧,否則很容易出現問題。比如,下麵是非線程安全的延遲初始化對象的示例代碼: COPYpubli ...
  • 顧名思義單調棧就是具有單調性的棧 ==常見模型:找出每個數左邊離它最近的比它大/小的數== 【演算法】 int stk[N],tt = 0; // 棧中存數據 for (int i = 1; i <= n; i ++){ int x; cin >> x; while (tt && stk[tt] >= ...