MySql 表索引設計原則

来源:https://www.cnblogs.com/tqlin/archive/2019/12/02/11973079.html

索引的優點 1.加快數據的檢索速度,這是創建索引的最主要的原因; 2.通過創建唯一性索引,可以保證資料庫表中每一行數據的唯一性; 3.加速表和表之間的連接; 4.在使用分組和排序子句進行數據檢索時,可以顯著減少查詢中分組和排序的時間。 索引的缺點 1.創建索引和維護索引要耗費時間,這種時間隨著數據量 ...


索引的優點

  • 1.加快數據的檢索速度,這是創建索引的最主要的原因;

  • 2.通過創建唯一性索引,可以保證資料庫表中每一行數據的唯一性;

  • 3.加速表和表之間的連接;

  • 4.在使用分組和排序子句進行數據檢索時,可以顯著減少查詢中分組和排序的時間。

索引的缺點

  • 1.創建索引和維護索引要耗費時間,這種時間隨著數據量的增加而增加。

  • 2.索引需要占物理空間,除了數據表占數據空間之外,每一個索引還要占一定的物理空間,如果要建立聚簇索引,那麼需要的空間就會更大。

  • 3.當對錶中的數據進行增加、刪除和修改的時候,索引也要同步動態的維護,這樣就降低了數據的增刪改速度。

所以單表數據太少,索引反而會影響速度;更新非常頻繁的數據不適宜建索引

索引設計原則

根據資料庫的功能,可以在資料庫設計器中創建三種索引

  • 唯一索引:唯一索引是不允許其中任何兩行具有相同索引值的索引
  • 主鍵索引:表定義主鍵將自動創建主鍵索引,主鍵索引是唯一索引的特定類型。該索引要求主鍵中的每個值都唯一。當在查詢中使用主鍵索引時, 它允許對數據的快速訪問
  • 聚集索引:表中行的物理順序與鍵值的邏輯(索引)順序相同。一個表 只能包含一個聚集索引

選擇索引的最終目的是為了使查詢的速度變快。下麵給出的原則是最基本的準則,但不能拘泥於這些準則,應該根據應用的實際情況進行分析和判斷,選擇最合適的索引方式。

1.索引最左匹配原則

  • 索引可以簡單如一個列(a),也可以複雜如多個列(a, b, c, d),即聯合索引。
  • 如果是聯合索引,那麼key也由多個列組成,同時,索引只能用於查找key是否存在(相等),遇到範圍查詢(>、<、between、like左匹配)等就不能進一步匹配了,後續退化為線性查找。因此,列的排列順序決定了可命中索引的列數。

例子:

如有索引(a, b, c, d),查詢條件a = 1 and b = 2 and c > 3 and d = 4,則會在每個節點依次命中a、b、c,無法命中d。(很簡單:索引命中只能是相等的情況,不能是範圍匹配)

明白最左匹配原則,對我們設計索引和編寫高效SQL語句非常有幫助

2.為經常需要排序、分組操作的欄位建立索引

經常需要ORDER BY、GROUP BY、DISTINCT等操作的欄位,排序操作會浪費很多時間。如果為其建立索引,可以有效地避免排序操作。

分組欄位或者排序欄位應該創建索引

3.為常作為查詢條件的欄位建立索引

如果某個欄位經常用來做查詢條件,那麼該欄位的查詢速度會影響整個表的查詢速度。因此,為這樣的欄位建立索引,可以提高整個表的查詢速度。

Where 子句中經常使用的欄位應該創建索引

4.限制索引的數目

索引的數目不是越多越好。每個索引都需要占用磁碟空間,索引越多,需要的磁碟空間就越大。修改表時,對索引的重構和更新很麻煩。越多的索引,會使更新表變得很浪費時間。

5.儘量選擇區分度高的列作為索引

儘量選擇區分度高的列作為索引,區分度的公式是count(distinct col)/count(*),表示欄位不重覆的比例,比例越大我們掃描的記錄數越少,唯一鍵的區分度是1,而一些狀態、性別欄位可能在大數據面前區分度就是0,那可能有人會問,這個比例有什麼經驗值嗎?使用場景不同,這個值也很難確定,一般需要join的欄位我們都要求是0.1以上,即平均1條掃描10條記錄

6.索引列不能參與計算

索引列不能參與計算,保持列“乾凈”,比如from_unixtime(create_time) = ’2019-12-02’就不能使用到索引,原因很簡單,b+樹中存的都是數據表中的欄位值,但進行檢索時,需要把所有元素都應用函數才能比較,顯然成本太大。所以語句應該寫成create_time = unix_timestamp(’2014-05-29’);

即索引列不能帶函數,否則會導致索引失效

7.擴展索引

儘量的擴展索引,不要新建索引。比如表中已經有a的索引,現在要加(a,b)的索引,那麼只需要修改原來的索引即可

8.條件帶like 註意事項

like 模糊查詢中,右模糊查詢(abc%)會使用索引,而(%abc)和(%abc%)會放棄索引而使用全表掃描

9.儘量使用數據量少的索引

如果索引的值很長,那麼查詢的速度會受到影響。例如,對一個CHAR(100)類型的欄位進行全文檢索需要的時間要比對CHAR(10)類型的欄位需要的時間要多。

10.儘量使用首碼來索引

如果索引欄位的值很長,最好使用值的首碼來索引。例如,TEXT和BLOG類型的欄位,進行全文檢索會很浪費時間。如果只檢索欄位的前面的若幹個字元,這樣可以提高檢索速度。

11.刪除不再使用或者很少使用的索引

表中的數據被大量更新,或者數據的使用方式被改變後,原有的一些索引可能不再需要。資料庫管理員應當定期找出這些索引,將它們刪除,從而減少索引對更新操作的影響。

12.=和in可以亂序。

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序,mysql的查詢優化器會幫你優化成索引可以識別的形式

13.聯合查詢

聯合查詢,子查詢等多表操作時關連欄位要加索引

資料


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

更多相關文章
  • 今天介紹一個開源的C# WPF開源控制項庫,非常漂亮,重點是開源哦 WPF做桌面開發是很有優勢的,除了微軟自帶的控制項外,還有很多第三方的控制項庫,比如收費的Dev Express For WPF、Telerik For WPF等,及Github上開源免費的控制項庫如MaterialDesignInXAML ...
  • Mark Zhou寫了很不錯的一系列介紹C# 7的文章,雖然是2年多年前發佈的,不過對於不熟悉C# 7特性的同學來說,仍然有很高的閱讀價值。 原文:https://blogs.msdn.microsoft.com/mazhou/2017/05/26/c-7-series-part-1-value-t ...
  • 配置 在 Startup.ConfigureServices 方法中,創建具有 AddAuthentication 和 AddCookie 方法的身份驗證中間件服務: AuthenticationScheme 傳遞到 AddAuthentication 設置應用程式的預設身份驗證方案。 如果有多個 ...
  • abp攔截器基本定義 攔截器介面定義: 預設抽象類定義: abp的攔截器實現是基於Autofac.Extras.DynamicProxy,這個包依賴兩個組件:Autofac、Castle.Core(實質上是調用內部組件DynamicProxy實現動態代理)。關於此組件的資料參考 ".NET 通過 A ...
  • 面向對象開發有三大特性(特點 / 特征) : 封裝, 繼承, 多態。我們今天主要討論封裝和繼承,多態會在下篇中討論。 一、封裝: 所謂封裝,也就是把客觀事物封裝成抽象的類,並且類可以把自己的數據和方法只讓可信的類或者對象操作,對不可信的進行信息隱藏。封裝是面向對象的特征之一,是對象和類概念的主要特性 ...
  • 最近, 做的一個windows 桌面WPF程式, 需要資料庫支持。嘗試了 sql server 的開發版,使用EF , 效率太低。後來採用sqlite資料庫,中間踩坑無數。但最終完美的解決了這些問題。為了幫助廣大同好更好的使用sqlite ,今分享所有相關代碼。 sqlite , 是一款免費的資料庫 ...
  • 下圖為鏡像重命名後的鏡像名為uoj,現在要把這個鏡像中的mysql導出 運行如下命令: docker exec -it uoj mysqldump -uroot -proot app_uoj233 >/data/database_bak/app_uoj233.sql #註釋:uoj 為鏡像名 -u ...
  • 問題描述:我們配置DG的目的就是為了在主庫出現故障時,備庫能夠提供服務,保證業務的正常運行,switchover是用戶有計劃的進行停機切換,能夠保證不丟失數據,我記錄一下我進行switchover中的操作。 1.主庫(orcl) 備庫(orclstd) ,查看主庫上的狀態,角色orcl是主庫 SQL ...
一周排行
  • HttpReports 簡單介紹 HttpReports 是 .Net Core下的一個Web組件,適用於 WebAPI 項目和 API 網關項目,通過中間件的形式集成到您的項目中, 通過HttpReports,可以讓開發人員快速的搭建出一個 API 性能分析的基礎報表網站。 主要包含 HttpRe ...
  • 大家好,這幾天試著從Github上拉取AspNetCore的源碼,嘗試著通過Visual Studio 打開,但是並不盡人意。我們需要去構建我們拉去的源代碼,這樣才可以通過VisualStudio可還原的項目。畢竟AspNetCore是一個巨型的項目集。 先決條件 在Windows中構建AspNet ...
  • 知識需要不斷積累、總結和沉澱,思考和寫作是成長的催化劑 梯子 一、鎖1、lock2、Interlocked3、Monitor4、SpinLock5、Mutex6、Semaphore7、Events1、AutoResetEvent2、ManualResetEvent3、ManualResetEvent ...
  • 安裝Docker CentOS 7 安裝 Docker 編寫Dockerfile 右鍵項目-》添加-》Docker 支持 選擇Linux 修改為如下: FROM mcr.microsoft.com/dotnet/core/aspnet:2.2-stretch-slim AS base WORKDIR ...
  • 原文:https://blogs.msdn.microsoft.com/mazhou/2017/06/06/c-7-series-part-3-default-literals/ C#的default關鍵字有兩種用法:一種是標記switch…case結構的預設分支(會匹配任意不被所有case條件匹配 ...
  • 相關模塊 1. AbpAspNetCoreModule 2. AbpAspNetCoreMvcModule 3. AbpAspNetCoreMvcContractsModule abp通過這三個模塊載入並配置了 asp.net core。,最主要的就是AbpAspNetCoreMvcModule模塊 ...
  • 安裝 參考文檔:https://docs.docker.com/install/linux/docker-ce/centos/#install-using-the-repository 前提條件 Docker 要求 CentOS 系統的內核版本高於 3.10,在終端輸入以下命令: uname -r ...
  • 目前遇到的問題: 1.路徑區分大小寫及路徑用“/”,而不是常用的"\\"。 windows下路徑為:"xxxx\\yyyy",Linux路徑下為:"xxxx/yyyy" 使用 Path.Combine("xxxx","yyyy") 進行合併即可。 2.有時候就需要在 docker 容器里訪問宿主機提 ...
  • 《.Net 最佳實踐》 [作者] (美) Stephen Ritchie[譯者] (中) 黃燈橋 黃浩宇 李永[出版] 機械工業出版社[版次] 2014年01月 第1版[印次] 2018年01月 第1次 印刷[定價] 69.00元 (P001) 開發人員應該對任何稱之為“最佳實踐”的實踐保持一種懷疑 ...
  • 本文介紹了C#中的屬性,以及C#6和C#7中與屬性相關的新特性。 ...
x