MySQL的索引優化

来源:https://www.cnblogs.com/changwan/p/18200365
-Advertisement-
Play Games

哪些場景下MySQL會使用索引查詢數據，哪些場景下MySQL不會使用索引查詢數據，以及如何使用索引提示來告知查詢優化器使用索引、忽略索引和強制索引索引。 ...


一、索引的使用場景

1、全值匹配

通過主鍵索引查詢

mysql> explain select * from t_goods where id = 1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

可以看到這裡查詢數據使用了主鍵索引。

現在我們再創建一個索引。

ALTER Table t_goods ADD INDEX index_category_name(t_category_id,t_name);

這裡為t_category_id與t_name創建了聯合索引。

mysql> explain select * from t_goods where t_category_id = 1 and t_name = '手機' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ref
possible_keys: index_category_name
          key: index_category_name
      key_len: 208
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

這裡的查詢條件為t_category_id與t_name，所以查詢時使用了聯合索引index_category_name

2、查詢範圍

對索引的值進行範圍查找

mysql> explain select * from t_goods where id >= 1 and id <=20 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 15
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

type: range說明根據主鍵索引範圍進行查詢。這裡 Extra: Using where，說明MySQL按照主鍵確定範圍後再回表查詢數據。

3、匹配最左首碼

解釋：也就是說，在使用索引時，MySQL優化器會根據查詢條件使用該索引。只有滿足這個匹配原則才會使用索引。例如過程創建的聯合索引index_category_name(t_category_id, t_name)，如果我跳過t_category_id直接使用t_name條件查詢，那麼這個查詢將不會使用索引。

mysql> explain select * from t_goods where t_name='手機' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

可以看到這個查詢並沒有使用索引。

4、查詢索引列

如果在查詢時包含索引的列或者查詢的列都在索引中，那麼查詢的效率會比SELECT * 或者查詢沒有索引的列的效率要高很多。也就是說，如果查詢的列只包含索引列，那麼這個效率會高很多。例如

mysql> explain select t_name,t_category_id from t_goods where t_name='手機' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: index
possible_keys: index_category_name
          key: index_category_name
      key_len: 208
          ref: NULL
         rows: 15
     filtered: 10.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

例如這裡查詢的列都是索引列，所以這個查詢的效率會快很多，並且使用了索引。如果有其他不是索引列需要查詢，那麼這個查詢將不會使用索引。例如

mysql> explain select t_name,t_category_id,t_price from t_goods where t_name='手機' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

5、匹配欄位首碼

如果某個欄位存儲的數據特別長的話，那麼在這個欄位上建立索引會增加MySQL維護索引的負擔。匹配欄位首碼就是用於解決這個問題。在欄位的開頭部分添加索引，按照這個索引進行數據查詢。

例如在欄位的前10個字元上添加索引，查詢時進行匹配。

mysql> create index category_part on t_goods(t_category(10));
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

再次進行模糊匹配查詢

mysql> explain select * from t_goods where t_category like '電子%' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: range
possible_keys: category_part
          key: category_part
      key_len: 43
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

可以看到這裡使用了我們剛纔創建的索引，這個索引應用於欄位的前10個字元。

6、精準與範圍匹配查詢

在查詢數據時，可以同時使用兩個索引，一個為精準匹配索引，一個為範圍匹配索引。例如

mysql> explain select * from t_goods where t_category_id=1 and id>=1 and id<=10 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ref
possible_keys: PRIMARY,index_category_name
          key: index_category_name
      key_len: 5
          ref: const
         rows: 5
     filtered: 66.67
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

這個查詢使用了兩個索引進行查找，使用index_category_name進行精準匹配並且按照主鍵索引進行範圍查詢

7、匹配NULL值

在查詢一個欄位時，如果這個欄位是索引欄位，那麼在判斷這個欄位是否為空時也會使用索引進行查詢。例

mysql> explain select * from t_goods where t_category_id is null \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ref
possible_keys: index_category_name
          key: index_category_name
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

這裡我查詢t_goods表中t_category_id是NULL的欄位，可以看到這裡是使用了索引進行查找的。

8、連接查詢匹配索引

在使用JOIN連接語句查詢多個數據表中的數據時，如果連接的欄位上添加了索引，那麼MySQL會使用索引查詢數據

mysql> explain select goods.t_name,category.t_category from t_goods goods join t_goods_category category on goods.t_category_id = category.id \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: category
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: goods
   partitions: NULL
         type: ref
possible_keys: index_category_name
          key: index_category_name
      key_len: 5
          ref: demo.category.id
         rows: 5
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

在使用JOIN聯合多表查詢時，如果聯合的欄位是索引欄位，那麼這個查詢也會使用索引列。

二、不適合使用索引的場景

1、以通配符開始的LIKE語句

在使用LIKE語句時，如果使用通配符%開頭，那麼MySQL將不會使用索引。例如

mysql> explain select * from t_goods where t_category like '%電' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

這裡的t_category欄位雖然說是索引欄位，但是這裡的條件是以通配符%開頭，所以不會使用索引查詢

2、數據類型轉換

當查詢的欄位數據進行了數據轉換時，也就是說，某個索引欄位的類型為字元，但是在匹配條件時，不是字元類型，那麼這個查詢將不會使用索引查詢。例如

mysql> explain select * from t_goods where t_category = 0 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: category_part
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 10.00
        Extra: Using where
1 row in set, 3 warnings (0.00 sec)

例如這裡的查詢就沒有使用索引，並且type的類型為ALL，說明進行了全表掃描查詢。

3、OR語句

在OR語句中如果條件中有不是索引的欄位，那麼這查詢就不會使用索引查詢。例如

mysql> explain select * from t_goods where t_category_id = 1 or t_stock = 2 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: index_category_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 40.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

這裡因為t_stock不是索引欄位，所以哪怕t_category_id索引欄位匹配成功，這條語句也不會使用索引查詢

4、計算索引列

如果在使用索引條件時，這個索引欄位進行了計算或者使用了函數，那麼此時MySQL是不會使用索引的。

mysql> explain select * from t_goods where left(t_category,2)='電子'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

這裡對索引欄位t_category使用了函數，判斷這個欄位的前兩個字元是否為“電子”。可以看到有15條記錄，但是並沒有使用索引，哪怕t_category是索引列。

5、使用<>或!=操作符匹配查詢條件

這兩個符號都用於表示不等於。當查詢條件使用這個時不會使用索引查詢。

mysql> explain select * from t_goods where t_category<>'電子產品' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: category_part
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

6、匹配NOT NULL值

在MySQL中，使用IS NULL來判斷索引欄位會使用索引查詢，但是使用NOT NULL來判斷時不會使用索引查詢。


mysql> explain select * from t_goods where t_category_id is not null \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: index_category_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

三、索引提示

1、使用索引

提示MySQL查詢優化器使用特定的索引，不需要評估是否使用其他索引。

mysql> explain select * from t_goods use index(index_category_name,category_part) where (t_category_id = 1 and t_name='手機' ) or t_category = '電子產品'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: index_merge
possible_keys: index_category_name,category_part
          key: index_category_name,category_part
      key_len: 208,43
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using sort_union(index_category_name,category_part); Using where
1 row in set, 1 warning (0.00 sec)

這裡可以使用use index()指定查詢時使用特定的索引。但是MySQL仍然可以根據自身的優化器決定是否使用該索引。

2、忽略索引

可以在查詢時，指定不使用某個索引。

mysql> explain select * from t_goods ignore index(category_part) where t_category = '電子產品'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

這裡使用ignore index（），指定在查詢時，忽略指定的索引，使用這條查詢沒有使用索引，而是進行全表掃描

3、強制使用索引

在查詢數據時，強制使用某個索引來檢索數據。

與use index()的區別為，FORCE INDEX會強制使用指定的索引，而不會管MySQL的優化器如何選擇。

mysql> explain select * from t_goods force index(category_part) where t_category = '電子產品'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ref
possible_keys: category_part
          key: category_part
      key_len: 43
          ref: const
         rows: 5
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

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

-Advertisement-
Play Games
更多相關文章
  • MySQL 8.0 Reference Manual（讀書筆記53節--Optimizing SQL Statements）
    基本概念 後臺啟動AsterixDB cd ~/asterixdb/asterixdb/asterix-server/target/asterix-server-0.9.10-SNAPSHOT-binary-assembly/apache-asterixdb-0.9.10-SNAPSHOT/opt/ ...
  • 阿裡 Canal 實時同步 MySQL 增量數據至 ClickHouse 資料庫
    在業務實現的過程中，時常會出現且或關係邏輯的拼接。邏輯運算的組合使用，是實現複雜業務規則和決策支持系統的關鍵技術。 目前袋鼠雲的指標管理平臺、客戶數據洞察平臺、數據資產平臺都有在使用。並且，且或組件已經在 RC 5.0 中添加到組件庫，企業現在可以更加靈活地構建和實施複雜的業務規則。 本文將從前期分 ...
  • .net6中使用EF core多個上下文遷移方式
    正常上下文在複製一個一模一樣的上下文 appsettings.json添加兩個資料庫連接字元串 Program.cs裡邊一樣添加兩個 控制台遷移命令 必須加上-Context 後邊跟的是我們上下文的名稱 Add-Migration MyMigration -Context MYDBContext22 ...
  • 別再發郵件了，必須在開源之夏後臺申請，50%的人竟然都沒有報名成功！
    引言 近期我們註意到很多學生朋友通過郵件嚮導師申請報名，請註意！！！​這是無效的，請必須通過“開源之夏”官方後臺申請報名，請仔細參考這篇【報名攻略】 所以，我們特此舉辦這次宣講會，目的是向所有感興趣的學生詳細介紹Apache DolphinScheduler社區在開源之夏中提供的項目，並且解答學生朋 ...
  • Oracle ORA-06575: 程式包或函數WM_CONCAT處於無效狀態
    Percona Toolkit 神器全攻略 Percona Toolkit 神器全攻略系列共八篇分為 文章名 文章名 Percona Toolkit 神器全攻略 Percona Toolkit 神器全攻略（實用類） Percona Toolkit 神器全攻略（配置類） Percona Toolkit ...
  • MySQL全文索引源碼剖析之Insert語句執行過程
    本文分享自華為雲社區《MySQL全文索引源碼剖析之Insert語句執行過程》 ，作者：GaussDB 資料庫。 1. 背景介紹 全文索引是信息檢索領域的一種常用的技術手段，用於全文搜索問題，即根據單詞，搜索包含該單詞的文檔，比如在瀏覽器中輸入一個關鍵詞，搜索引擎需要找到所有相關的文檔，並且按相關性排 ...
  • MySQL 8.0 Reference Manual（讀書筆記52節--Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions(2)）
    前言 來整理一下緩存雪崩、擊穿和穿透的問題，這個問題在面試中常出現，不是瞎說，我已經遇到幾次了 一、緩存雪崩 1.雪崩 什麼是雪崩，某度給出的解釋 雪崩 當山坡積雪內部的內聚力抗拒不了它所受到的重力拉引時,便向下滑動,引起大量雪體崩塌,人們把這種自然現象稱作雪崩。 說白了就是一部分雪因不可抗力出現問 ...
  • 快速理解MySQL null的10大坑
    創建表時應當設置not null，添加一個預設值0或''去替代null。 sum('field')的坑 若一列的所有值都是null，那麼sum函數的結果不是0，而是null，所以可能會因為值的類型相容問題，出現意料之外的情況。 null值會有NPE問題。 count('field')的坑 有null ...
一周排行
    -Advertisement-
    Play Games
  • 實用接地氣的 .NET 微服務框架
    前言 微服務架構已經成為搭建高效、可擴展系統的關鍵技術之一，然而，現有許多微服務框架往往過於複雜，使得我們普通開發者難以快速上手並體驗到微服務帶了的便利。為瞭解決這一問題，於是作者精心打造了一款最接地氣的 .NET 微服務框架，幫助我們輕鬆構建和管理微服務應用。 本框架不僅支持 Consul 服務註 ...
  • 基於surging的木舟IOT平臺如何添加網路組件
    先看一下效果吧: 如果不會寫動畫或者懶得寫動畫，就直接交給Blend來做吧; 其實Blend操作起來很簡單，有點類似於在操作PS，我們只需要設置關鍵幀，滑鼠點來點去就可以了，Blend會自動幫我們生成我們想要的動畫效果. 第一步：要創建一個空的WPF項目 第二步：右鍵我們的項目，在最下方有一個，在B ...
  • Prism:框架介紹與安裝
    Prism:框架介紹與安裝 什麼是Prism? Prism是一個用於在 WPF、Xamarin Form、Uno 平臺和 WinUI 中構建鬆散耦合、可維護和可測試的 XAML 應用程式框架 Github https://github.com/PrismLibrary/Prism NuGet htt ...
  • 不可不知的WPF畫筆（Brush）
    在WPF中，屏幕上的所有內容，都是通過畫筆（Brush）畫上去的。如按鈕的背景色，邊框，文本框的前景和形狀填充。藉助畫筆，可以繪製頁面上的所有UI對象。不同畫筆具有不同類型的輸出（ 如：某些畫筆使用純色繪製區域，其他畫筆使用漸變、圖案、圖像或繪圖）。 ...
  • .NET 8 高併發微服務電商系統實戰
    前言 嗨，大家好！推薦一個基於 .NET 8 的高併發微服務電商系統，涵蓋了商品、訂單、會員、服務、財務等50多種實用功能。 項目不僅使用了 .NET 8 的最新特性，還集成了AutoFac、DotLiquid、HangFire、Nlog、Jwt、LayUIAdmin、SqlSugar、MySQL、 ...
  • 基於surging 如何利用peerjs進行語音視頻通話
    本文主要介紹攝像頭（相機）如何採集數據，用於類似攝像頭本地顯示軟體，以及流媒體數據傳輸場景如傳屏、視訊會議等。 攝像頭採集有多種方案，如AForge.NET、WPFMediaKit、OpenCvSharp、EmguCv、DirectShow.NET、MediaCaptre（UWP），網上一些文章以及 ...
  • [WPF]數據綁定時為何會出現StringFormat失效
    前言 Seal-Report 是一款.NET 開源報表工具，擁有 1.4K Star。它提供了一個完整的框架，使用 C# 編寫，最新的版本採用的是 .NET 8.0 。 它能夠高效地從各種資料庫或 NoSQL 數據源生成日常報表，並支持執行複雜的報表任務。 其簡單易用的安裝過程和直觀的設計界面，我們 ...
  • NET Core 多身份校驗與策略模式
    背景需求： 系統需要對接到XXX官方的API，但因此官方對接以及管理都十分嚴格。而本人部門的系統中包含諸多子系統，系統間為了穩定，程式間多數固定Token+特殊驗證進行調用，且後期還要提供給其他兄弟部門系統共同調用。 原則上：每套系統都必須單獨接入到官方，但官方的接入複雜，還要官方指定機構認證的證書 ...
  • C#自定義控制項—轉換開關
    本文介紹下電腦設備關機的情況下如何通過網路喚醒設備，之前電源S狀態 電腦Power電源狀態- 唐宋元明清2188 - 博客園 (cnblogs.com) 有介紹過遠程喚醒設備，後面這倆天瞭解多了點所以單獨加個隨筆 設備關機的情況下，使用網路喚醒的前提條件： 1. 被喚醒設備需要支持這WakeOnL ...
  • .NET 8.0 前後分離快速開發框架
    前言 大家好，推薦一個.NET 8.0 為核心，結合前端 Vue 框架，實現了前後端完全分離的設計理念。它不僅提供了強大的基礎功能支持，如許可權管理、代碼生成器等，還通過採用主流技術和最佳實踐，顯著降低了開發難度，加快了項目交付速度。 如果你需要一個高效的開發解決方案，本框架能幫助大家輕鬆應對挑戰，實 ...
所有分類