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 MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 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 ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類