精心總結十三條建議,幫你創建更合適的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
  • PasteSpider是什麼? 一款使用.net編寫的開源的Linux容器部署助手,支持一鍵發佈,平滑升級,自動伸縮, Key-Value配置,項目網關,環境隔離,運行報表,差量升級,私有倉庫,集群部署,版本管理等! 30分鐘上手,讓開發也可以很容易的學會在linux上部署你得項目! [從需求角度介 ...
  • SQLSugar是什麼 **1. 輕量級ORM框架,專為.NET CORE開發人員設計,它提供了簡單、高效的方式來處理資料庫操作,使開發人員能夠更輕鬆地與資料庫進行交互 2. 簡化資料庫操作和數據訪問,允許開發人員在C#代碼中直接操作資料庫,而不需要編寫複雜的SQL語句 3. 支持多種資料庫,包括但 ...
  • 在C#中,經常會有一些耗時較長的CPU密集型運算,因為如果直接在UI線程執行這樣的運算就會出現UI不響應的問題。解決這類問題的主要途徑是使用多線程,啟動一個後臺線程,把運算操作放在這個後臺線程中完成。但是原生介面的線程操作有一些難度,如果要更進一步的去完成線程間的通訊就會難上加難。 因此,.NET類 ...
  • 一:背景 1. 講故事 前些天有位朋友在微信上丟了一個崩潰的dump給我,讓我幫忙看下為什麼出現了崩潰,在 Windows 的事件查看器上顯示的是經典的 訪問違例 ,即 c0000005 錯誤碼,不管怎麼說有dump就可以上windbg開幹了。 二:WinDbg 分析 1. 程式為誰崩潰了 在 Wi ...
  • CSharpe中的IO+NPOI+序列化 文件文件夾操作 學習一下常見的文件、文件夾的操作。 什麼是IO流? I:就是input O:就是output,故稱:輸入輸出流 將數據讀入記憶體或者記憶體輸出的過程。 常見的IO流操作,一般說的是[記憶體]與[磁碟]之間的輸入輸出。 作用 持久化數據,保證數據不再 ...
  • C#.NET與JAVA互通之MD5哈希V2024 配套視頻: 要點: 1.計算MD5時,SDK自帶的計算哈希(ComputeHash)方法,輸入輸出參數都是byte數組。就涉及到字元串轉byte數組轉換時,編碼選擇的問題。 2.輸入參數,字元串轉byte數組時,編碼雙方要統一,一般為:UTF-8。 ...
  • CodeWF.EventBus,一款靈活的事件匯流排庫,實現模塊間解耦通信。支持多種.NET項目類型,如WPF、WinForms、ASP.NET Core等。採用簡潔設計,輕鬆實現事件的發佈與訂閱。通過有序的消息處理,確保事件得到妥善處理。簡化您的代碼,提升系統可維護性。 ...
  • 一、基本的.NET框架概念 .NET框架是一個由微軟開發的軟體開發平臺,它提供了一個運行時環境(CLR - Common Language Runtime)和一套豐富的類庫(FCL - Framework Class Library)。CLR負責管理代碼的執行,而FCL則提供了大量預先編寫好的代碼, ...
  • 本章將和大家分享在ASP.NET Core中如何使用高級客戶端NEST來操作我們的Elasticsearch。 NEST是一個高級別的Elasticsearch .NET客戶端,它仍然非常接近原始Elasticsearch API的映射。所有的請求和響應都是通過類型來暴露的,這使得它非常適合快速上手 ...
  • 參考delphi的代碼更改為C# Delphi 檢測密碼強度 規則(仿 google) 仿 google 評分規則 一、密碼長度: 5 分: 小於等於 4 個字元 10 分: 5 到 7 字元 25 分: 大於等於 8 個字元 二、字母: 0 分: 沒有字母 10 分: 全都是小(大)寫字母 20 ...