Mysql性能優化:如何給字元串加索引?

来源:https://www.cnblogs.com/Chenjiabing/archive/2020/04/02/12620427.html
-Advertisement-
Play Games

導讀 現代大部分的登錄系統都支持郵箱、手機號碼登錄兩種方式,那麼如何在郵箱或者手機號碼這個字元串上建立索引才能保證性能最佳呢? 今天這篇文章就來探討一下在Mysql中如何給一個字元串加索引才能達到性能最佳。 本文首發於作者的微信公眾號【碼猿技術專欄】,原創不易,喜歡的朋友支持一下,謝謝!!! 陳某將 ...


導讀

  • 現代大部分的登錄系統都支持郵箱、手機號碼登錄兩種方式,那麼如何在郵箱或者手機號碼這個字元串上建立索引才能保證性能最佳呢?

  • 今天這篇文章就來探討一下在Mysql中如何給一個字元串加索引才能達到性能最佳。

  • 本文首發於作者的微信公眾號【碼猿技術專欄】,原創不易,喜歡的朋友支持一下,謝謝!!!
  • 陳某將會從什麼是首碼索引首碼索引和普通索引的比較如何建麗最佳性能的首碼索引首碼索引對覆蓋索引的影響這幾段來講。

 

首碼索引

  • 顧名思義,對於列值較長,比如BLOBTEXTVARCHAR,就 "必須" 使用首碼索引,即將值的前一部分作為索引。因為索引的存儲也是需要空間的,同樣索引太長維護起來也比較困難。

  • 比如我們給User表中的郵箱添加首碼索引,如下:

   alter table user add index index1(email(7));
  • 上述語句將email的前7個字元作為索引。

 

首碼索引和普通索引比較

  • 我們分別將email的全部作為索引和前7個字元作為索引來看看在性能上有什麼差異。建立索引的語句如下:

  alter table user add index index1(email);

  alter table user add index index2(email(7));
  • 假設有user表中有這樣幾條數據(id,name,email):(1,"陳某","chenmou1993@xxx")(2,"張某","chenmou1994@xxx")(3,"李某","chenmou1995@xxx")(4,"王某","chenmou1996@xxx")

  • 對應於index1和index2的索引樹如下兩張圖:

 

  • 如果執行下麵的查詢語句,Mysql如何利用索引來查詢呢?

  select * from user where email="chenmou1995@xxx";

 

【1】普通索引的執行過程

  1. 從index1索引樹找到滿足索引值是chenmou1995@xxx的這條記錄,取得id=2的值;

  2. 到主鍵上查到主鍵值是id=2的行,判斷email的值是正確的,將這行記錄加入結果集;

  3. index1索引樹上剛剛查到的位置的下一條記錄,發現已經不滿足email=chenmou1995@xxx的條件了,迴圈結束。

這個過程中,只需要回主鍵索引取一次數據,所以系統認為只掃描了一行

 

【2】首碼索引的執行過程

  1. 從index2索引樹找到滿足索引值是chenmou的記錄,找到的第一個是id=1;

  2. 到主鍵上查到主鍵值是id=1的行,判斷出email的值不是chenmou1995@xxx,這行記錄丟棄;

  3. 取index2上剛剛查到的位置的下一條記錄,發現仍然是chenmou,取出id=2,再到ID索引上取整行然後判斷,這次值對了,將這行記錄加入結果集;

  4. 重覆上一步,直到在idxe2上取到的值不是chenmou時,迴圈結束。

  在這個過程中,要回主鍵索引取4次數據,也就是掃描了4行。

 

  • 通過以上查詢的對比,很容易就可以發現,使用首碼索引後,可能會導致查詢語句讀數據的次數變多。

  • 但是對於這個查詢語句來說,如果建立的首碼索引的長度為13呢?那麼滿足chenmou1995的記錄只有一個,這樣就可以直接定位到id=2,此時不但空間縮小了,掃描的行數也減少了。

  • 於是結論就來了:使用首碼索引,只要定義好長度,就可以做到既節省空間,又不用額外增加太多的查詢成本。

  • 那麼如何建立正確的首碼索引才能達到最佳的性能呢?接著往下看................

 

如何建立最佳性能的首碼索引

  • 通過上述的比較,可以得出一個結論,建立首碼索引的區分度越高越好,意味著重覆的鍵值越少

  • 那麼如何統計區分度,其實很簡單,只需要判斷資料庫中重覆的次數即可。sql如下:

  select 
   count(distinct left(email,4))as L4,
   count(distinct left(email,5))as L5,
   count(distinct left(email,6))as L6,
   count(distinct left(email,7))as L7,
  from user;
  • 但是如果對於使用首碼區分度不太好的情況,比如,我們國家的身份證號,一共18位,其中前6位是地址碼,所以同一個縣的人的身份證號前6位一般會是相同的。 這時候如果對身份證號做長度為6的首碼索引的話,這個索引的區分度就非常低了。

  • 按照我們前面說的方法,可能你需要創建長度為12以上的首碼索引,才能夠滿足區分度要求。

  • 但是,索引選取的越長,占用的磁碟空間就越大,相同的數據頁能放下的索引值就越少,搜索的效率也就會越低。

  • 那麼,如果我們能夠確定業務需求裡面只有按照身份證進行等值查詢的需求,還有沒有別的處理方法呢?這種方法,既可以占用更小的空間,也能達到相同的查詢效率。現在簡單的介紹一種解決此種問題的方式,當然方法肯定不止一種,如下:

 

  倒序存儲

  如果你存儲身份證號的時候把它倒過來存,每次查詢的時候,你可以這麼寫:

   select field_list from t where id_card = reverse('輸入的身份證號');

  由於身份證號的最後6位沒有地址碼這樣的重覆邏輯,所以最後這6位很可能就提供了足夠的區分度。當然了,實踐中你不要忘記使用count(distinct)方法去做個驗證。

 

首碼索引對覆蓋索引的影響

  • 首碼索引會導致覆蓋索引失效,查詢語句如下:

  select id,name from user where email="chenmou1995@xxx";
  • 由於使用了首碼索引,因此必須會回表驗證查詢到的時候正確,此處使用了覆蓋索引也是無效的。

  • 也就是說,使用首碼索引就用不上覆蓋索引對查詢性能的優化了,這也是你在選擇是否使用首碼索引時需要考慮的一個因素。

 

總結

  • 如何給字元串加索引是一個需要考量的問題,陳某在這裡給出如下的建議:

  1. 如果字元串長度很短,建議直接用全部作為索引。

  2. 使用首碼索引註意分析區分度,區分度越高越好。

  3. 使用首碼索引需要考慮覆蓋索引失效的問題。

 


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

-Advertisement-
Play Games
更多相關文章
  • 簡介 Windows 10 上內置了 Hyper V。Hyper V 提供硬體虛擬化,每個虛擬機都在虛擬硬體上運行。 系統要求 Windows 10 企業版、專業版或教育版。家庭版、移動版、移動企業版無法使用。 具有二級地址轉換 (SLAT) 的 64 位處理器。 CPU 支持 VM 監視器模式擴展 ...
  • 前提:先在centos7中安裝好了VMware Tools 安裝gcc: yum -y install gcc 安裝kernel-devel: yum -y install kernel-devel 設置好共用目錄: 點擊添加之後選擇共用目錄 在centos7終端命令中輸入 vmware-hgfsc ...
  • 最近的項目要求配置共用存儲的四節點集群,使集群能夠形成負載均衡。 但是大家知道,PostgreSQL不支持使用同一數據目錄生成多個實例,在執行pg_ctl start的時候,如果指定的數據目錄有實例在運行,則該實例會發生錯誤導致資料庫down掉。故而,我們選擇了基於pgpool-II + repmg ...
  • SQL Server中鎖機制保證併發情況下的數據訪問,開發過程中利用好索引減少數據,能減少數據掃描數據加鎖的過程,合理規範使用事務,能減少死鎖發生 ...
  • 1、鎖監控 查看鎖住的表: select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT' ...
  • 參考資料:C語言中文網 存儲引擎: 資料庫存儲引擎是資料庫底層軟體組件,資料庫管理系統使用數據引擎進行創建、查詢、更新和刪除數據操作。 不同的存儲引擎提供不同的存儲機制、索引技巧、鎖定水平等功能,使用不同的存儲引擎還可以獲得特定的功能。 註:InnoDB 事務型資料庫的首選引擎,支持事務安全表(AC ...
  • 大體來說,MySQL 可以分為 Server 層和存儲引擎層兩部分。 select * from T where ID=10; 這條查詢語句的執行過程: 外部層: 用戶與server層交互的媒介 一.客戶端【用於連接資料庫,輸入命令/語句】 界面化連接資料庫 輸入 select * from T w ...
  • 什麼是事務 事務的概念 從業務層面上來說,事務就是一個最小的不可分割的單元,通常一個事務對應的是一個完整的業務(比如銀行的轉賬操作)。 為什麼要有事務 仍以銀行轉賬為例加以說明,比如我要從賬號A轉賬100元到賬號B,現在資料庫有一張表account,那麼就意味著需要同時執行兩條SQL語句的更新: 以 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...