mysql-覆蓋索引

来源:http://www.cnblogs.com/happyflyingpig/archive/2017/10/13/7662881.html
-Advertisement-
Play Games

什麼叫做覆蓋索引？ 解釋一： 就是select的數據列只用從索引中就能夠取得，不必從數據表中讀取，換句話說查詢列要被所使用的索引覆蓋。 解釋二： 索引是高效找到行的一個方法，當能通過檢索索引就可以讀取想要的數據，那就不需要再到數據表中讀取行了。如果一個索引包含了（或覆蓋了）滿足查詢語句中欄位與條件的 ...


什麼叫做覆蓋索引？

  • 解釋一： 就是select的數據列只用從索引中就能夠取得，不必從數據表中讀取，換句話說查詢列要被所使用的索引覆蓋。
  • 解釋二： 索引是高效找到行的一個方法，當能通過檢索索引就可以讀取想要的數據，那就不需要再到數據表中讀取行了。如果一個索引包含了（或覆蓋了）滿足查詢語句中欄位與條件的數據就叫做覆蓋索引。
  • 解釋三：是非聚集組合索引的一種形式，它包括在查詢里的Select、Join和Where子句用到的所有列（即建立索引的欄位正好是覆蓋查詢語句[select子句]與查詢條件[Where子句]中所涉及的欄位，也即，索引包含了查詢正在查找的所有數據）。

　　不是所有類型的索引都可以成為覆蓋索引。覆蓋索引必須要存儲索引的列，而哈希索引、空間索引和全文索引等都不存儲索引列的值，所以MySQL只能使用B-Tree索引做覆蓋索引

　　當發起一個被索引覆蓋的查詢(也叫作索引覆蓋查詢)時，在EXPLAIN的Extra列可以看到“Using index”的信息

　　

幾種優化場景：

　　1.無WHERE條件的查詢優化：

　　執行計劃中，type 為ALL，表示進行了全表掃描

　　如何改進？優化措施很簡單，就是對這個查詢列建立索引。如下，

ALERT TABLE t1 ADD KEY(staff_id);

 

  •  再看一下執行計劃
explain select sql_no_cache count(staff_id) from t1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: staff_id
      key_len: 1
          ref: NULL
         rows: 1023849
　　　　　 Extra: Using index


1 row in set (0.00 sec)

 

　　possible_key: NULL，說明沒有WHERE條件時查詢優化器無法通過索引檢索數據，這裡使用了索引的另外一個優點，即從索引中獲取數據，減少了讀取的數據塊的數量。 無where條件的查詢，可以通過索引來實現索引覆蓋查詢，但前提條件是，查詢返回的欄位數足夠少，更不用說select *之類的了。畢竟，建立key length過長的索引，始終不是一件好事情。

  • 查詢消耗

　　

　　從時間上看，小了0.13 sec

2、二次檢索優化

　　如下這個查詢：

select sql_no_cache rental_date from t1 where inventory_id<80000;
…
…
| 2005-08-23 15:08:00 |
| 2005-08-23 15:09:17 |
| 2005-08-23 15:10:42 |
| 2005-08-23 15:15:02 |
| 2005-08-23 15:15:19 |
| 2005-08-23 15:16:32 |
+---------------------+
79999 rows in set (0.13 sec)

 

　　執行計劃：

explain select sql_no_cache rental_date from t1 where inventory_id<80000\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: inventory_id
          key: inventory_id
      key_len: 3
          ref: NULL
         rows: 153734
        Extra: Using index condition
1 row in set (0.00 sec)

 

　　Extra：Using index condition 表示使用的索引方式為二級檢索，即79999個書簽值被用來進行回表查詢。可想而知，還是會有一定的性能消耗的

　　嘗試針對這個SQL建立聯合索引，如下：

alter table t1 add key(inventory_id,rental_date);

 

　　執行計劃：

explain select sql_no_cache rental_date from t1 where inventory_id<80000\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: inventory_id,inventory_id_2
          key: inventory_id_2
      key_len: 3
          ref: NULL
         rows: 162884
        Extra: Using index
1 row in set (0.00 sec)

 

　　Extra：Using index 表示沒有會標查詢的過程，實現了索引覆蓋

3、分頁查詢優化

　　如下這個查詢場景

select tid,return_date from t1 order by inventory_id limit 50000,10;
+-------+---------------------+
| tid   | return_date         |
+-------+---------------------+
| 50001 | 2005-06-17 23:04:36 |
| 50002 | 2005-06-23 03:16:12 |
| 50003 | 2005-06-20 22:41:03 |
| 50004 | 2005-06-23 04:39:28 |
| 50005 | 2005-06-24 04:41:20 |
| 50006 | 2005-06-22 22:54:10 |
| 50007 | 2005-06-18 07:21:51 |
| 50008 | 2005-06-25 21:51:16 |
| 50009 | 2005-06-21 03:44:32 |
| 50010 | 2005-06-19 00:00:34 |
+-------+---------------------+
10 rows in set (0.75 sec)

 

　　在未優化之前，我們看到它的執行計劃是如此的糟糕

　　

explain select tid,return_date from t1 order by inventory_id limit 50000,10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1023675
        
1 row in set (0.00 sec)

　　看出是全表掃描。加上而外的排序，性能消耗是不低的

　　如何通過覆蓋索引優化呢？

　　我們創建一個索引，包含排序列以及返回列，由於tid是主鍵欄位，因此，下麵的複合索引就包含了tid的欄位值

alter table t1 add index liu(inventory_id,return_date);

 

　　那麼，效果如何呢？

select tid,return_date from t1 order by inventory_id limit 50000,10;
+-------+---------------------+
| tid   | return_date         |
+-------+---------------------+
| 50001 | 2005-06-17 23:04:36 |
| 50002 | 2005-06-23 03:16:12 |
| 50003 | 2005-06-20 22:41:03 |
| 50004 | 2005-06-23 04:39:28 |
| 50005 | 2005-06-24 04:41:20 |
| 50006 | 2005-06-22 22:54:10 |
| 50007 | 2005-06-18 07:21:51 |
| 50008 | 2005-06-25 21:51:16 |
| 50009 | 2005-06-21 03:44:32 |
| 50010 | 2005-06-19 00:00:34 |
+-------+---------------------+
10 rows in set (0.03 sec)

 

　　可以發現，添加複合索引後，速度提升0.7s！ 　　我們看一下改進後的執行計劃 
explain select tid,return_date from t1 order by inventory_id limit 50000,10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: liu
      key_len: 9
          ref: NULL
         rows: 50010
　　　　Extra: Using index
1 row in set (0.00 sec)

 　　執行計劃也可以看到，使用到了複合索引，並且不需要回表

　　對比一下如下的改寫SQL，思想是通過索引消除排序

select a.tid,a.return_date from  t1 a 
inner join 
(select tid from t1 order by inventory_id limit 800000,10) b on a.tid=b.tid;

 

　　併在此基礎上，我們為inventory_id列創建索引，並刪除之前的覆蓋索引

alter table t1 add index idx_inid(inventory_id)；
drop index liu;

 

　　然後收集統計信息。

select a.tid,a.return_date from  t1 a inner join  (select tid from t1 order by inventory_id limit 800000,10) b on a.tid=b.tid;
+--------+---------------------+
| tid    | return_date         |
+--------+---------------------+
| 800001 | 2005-08-24 13:09:34 |
| 800002 | 2005-08-27 11:41:03 |
| 800003 | 2005-08-22 18:10:22 |
| 800004 | 2005-08-22 16:47:23 |
| 800005 | 2005-08-26 20:32:02 |
| 800006 | 2005-08-21 14:55:42 |
| 800007 | 2005-08-28 14:45:55 |
| 800008 | 2005-08-29 12:37:32 |
| 800009 | 2005-08-24 10:38:06 |
| 800010 | 2005-08-23 12:10:57 |
+--------+---------------------+

 

　　這種優化手段較前者時間多消耗了大約140ms。這種優化手段雖然使用索引消除了排序，但是還是要通過主鍵值回表查詢。因此，在select返回列較少或列寬較小的時候，我們可以通過建立複合索引的方式優化分頁查詢，效果更佳，因為它不需要回表！

參考文獻:

[1] 袋鼠雲技術團隊博客，https://yq.aliyun.com/articles/62419

[2] Baron Schwartz等 著，寧海元等 譯 ；《高性能MySQL》（第3版）； 電子工業出版社 ，2013


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

-Advertisement-
Play Games
更多相關文章
  • Android 為TV端助力
    記錄兩次事情： 第一個給view添加動畫效果，需要保證view是可以獲取焦點的 第二個給listview，GridView設置選擇器 listselector時，要保證他的子item無背景，否則選擇器的背景會被子item的背景擋住 ...
  • Android ListView getViewTypeCount 的返回值問題解決
    最近在學慕課網上的一個實戰課程，期間有一個智能聊天機器人模塊。 聊天界面通過 ListView 顯示，用 Adapter 載入。一般來說，單對單的聊天，兩者發出的話分別列在聊天頁面的左右兩邊。所以，在 Adapter 里需要設置一個 Type 來區分發送方和接收方，從而正確顯示左右。 在課程視頻里， ...
  • iOS：UITableView相關（17-10-10更）
    UITableView用得較多，遇到的情況也較多，單獨記錄一篇。 一、零散的技巧 二、取cell 三、cell高度 四、導航欄、TableView常見問題相關 一、零散的技巧 1、 cell的選中效果是cell的屬性，可以有的有，無的無。 2、cell的下劃線是Table的屬性，全部有，或全部無。 ...
  • android動畫源碼合集、動態主題框架、社交app源碼等
    Android精選源碼 仿MIUI果凍視圖-BouncingJellyView 一個快速易用的動態主題框架 android動畫效果集合源碼 android使用Kotlin開發的Dribbble客戶端 android一款開源社交app源碼 android實現RecyclerView頂部懸浮效果源碼 開 ...
  • Phoenix簡介及安裝部署使用
    Phoenix簡介及安裝部署使用 ...
  • MS SQLServer 批量附加資料庫
    /************************************************************ * 標題：MS SQLServer 批量附加資料庫 * 說明：請根據下麵的註釋使用此腳本 * 時間: 2015/7/13 11:16:41 ******************... ...
  • MSSQL查詢數據分頁
    這幾天剛好碰到數據的分頁查詢，覺得不錯，Mark一下，方法有兩種,都是使用select top，效率如何就不在這討論 方法1:利用select top配合not in(或者not exists)，查詢第n頁的時候，過濾掉n-1頁的數據即可，示例假設每頁查詢數量為5，查詢第3頁的數據； Select ...
  • Oracle05、函數+多表查詢+子查詢+集合查詢+分頁+創建表約束
    一、通用函數和條件判斷語句 單引號出現的地方如下：1）字元串，例如：'hello'2）日期型，例如：'17-12月-80'3）to_char/to_date(日期,'YYYY-MM-DD HH24:MI:SS')雙引號出現的地方如下：1）列別名，例如：select ename "姓 名" from ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類