一句口訣教你辨別索引失效七大場景

来源:https://www.cnblogs.com/huaweiyun/archive/2022/10/19/16806933.html
-Advertisement-
Play Games

摘要：本文將通過一句口訣，教你如何辨別索引失效。 本文分享自華為雲社區《虛竹哥教你一句口訣辨別索引失效七大場景》，作者：小虛竹 。 一、口訣 教你一句功法口訣：模 型 數 或 運 最 快 二、初始化數據 創建存儲引擎為InnoDB的學生表 drop table if exists student; ...


摘要：本文將通過一句口訣，教你如何辨別索引失效。

本文分享自華為雲社區《虛竹哥教你一句口訣辨別索引失效七大場景》，作者：小虛竹 。

一、口訣

教你一句功法口訣：模 型 數 或 運 最 快

二、初始化數據

創建存儲引擎為InnoDB的學生表

drop table if exists student;
CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`order_num` bigint NOT NULL COMMENT '序號',
`student_name` varchar(20) NOT NULL COMMENT '姓名',
`age` int COMMENT '年齡',
`create_time` TIMESTAMP COMMENT '創建時間',
`gender` int COMMENT '性別 0：男； 1：女； 2：其他',
 PRIMARY KEY (`id`)
 )ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

插入100萬條數據,這裡使用了存儲過程，進行批量提交數據，先關閉自動提交，插入一定條數再進行提交。

--創建存儲過程
drop procedure if exists add_student;
CREATE PROCEDURE `add_student`(in n int,in batchNum int) 
BEGIN
 DECLARE i INT DEFAULT 1;
 DECLARE age1 INT DEFAULT 1;
 DECLARE gender1 INT DEFAULT 1;
WHILE (i < n+1 ) DO
set age1=floor(18+( rand() * 5));
set gender1 = floor(rand() * 3);
set autocommit = 0;
INSERT into student (order_num,student_name,age,create_time,gender) VALUES (i,concat('student_name',i),age1,now(),gender1);
set i=i+1;
if i mod batchNum = 0 then
 commit;
 end if;
END WHILE;
 commit;
END

 

-- 調用
CALL add_student(1000000,100000)

三、口訣詳解

模

like 模糊全匹配（like ‘%內容%’），會導致全表掃描；like模糊左匹配（like ‘%內容’），會導致全表掃描。

實戰驗證

查看student表的索引

show index from student;

對student_name 欄位添加索引

CREATE INDEX idx_student_name ON student(student_name(20));

再查看student表的索引

show index from student;

測試：

like 右匹配是可以命中索引的

explain select count(1)
from student 
where student_name like 'student_name1%'

解析出來的type級別是range

當查詢條件使用索引檢索某個範圍的數據，典型的場景為使用=、<>、>、>=、<、<=、IS [NOT] NULL、<=>、BETWEEN AND或者IN操作符時,類型為range

like 左匹配：

explain select count(1)
from student 
where student_name like '%student_name1'

解析出來的type級別是index

查詢條件中的欄位包含索引中的欄位(含有非索引欄位，就會是ALL了)，此時只需要掃描索引樹。也是全表掃描的。

like 完全匹配：

explain select count(1)
from student 
where student_name like '%student_name1%'

解析出來的type級別是index

查詢條件中的欄位包含索引中的欄位(含有非索引欄位，就會是ALL了)，此時只需要掃描索引樹。也是全表掃描的。

測試結論

模 字決索引失效成立。

型

代表數據類型。例如對字元串name欄位加的索引，where條件寫name=1，索引會失效。

實戰驗證

查看student表的索引

show index from student;

對student_name 欄位添加索引

CREATE INDEX idx_student_name ON student(student_name(20));

再查看student表的索引

show index from student;

測試：

explain select count(1)
from student
where student_name=1

解析出來的type級別是index

查詢條件中的欄位包含索引中的欄位(含有非索引欄位，就會是ALL了)，此時只需要掃描索引樹。也是全表掃描的。

測試結論

型 字決索引失效成立。

數

是函數的意思。對索引的欄位使用內部函數，索引也會失效。這種情況下應該建立基於函數的索引。

SELECT * FROM user WHERE DATE(create_time) = ‘2020-09-03’;
這裡使用DATE函數

實戰驗證

查看student表的索引

show index from student;

對create_time 欄位添加索引

CREATE INDEX idx_create_time ON student(create_time);

再查看student表的索引

show index from student;

測試：

explain SELECT count(1) FROM student WHERE DATE(create_time) = '2020-09-03';

解析出來的type級別是index

查詢條件中的欄位包含索引中的欄位(含有非索引欄位，就會是ALL了)，此時只需要掃描索引樹。也是全表掃描的。

直接查create_time 欄位是可以的：

explain SELECT count(1) FROM student WHERE create_time = '2020-09-03';

解析出來的type級別是ref

當查詢語句中的連接條件或者查詢條件使用的索引不是主鍵和非空唯一索引，或者只是一個索引的一部分，則type的取值為ref

測試結論

數 字決索引失效成立。

或

在 where 子句中使用 or 來連接條件，如果一個欄位有索引，一個欄位沒有索引，將導致引擎放棄使用索引而進行全表掃描；

實戰驗證

查看student表的索引

show index from student;

對order_num 欄位添加索引

CREATE INDEX idx_order_num ON student(order_num);

再查看student表的索引

show index from student;

測試：

explain select count(1)
from student
where order_num = 20 or student_name='student_name10'

測試結論

或 字決索引失效成立。

運

對索引的列進行運算，索引失效,例如：WHERE age+1=8;

實戰驗證

查看student表的索引

show index from student;

對age 欄位添加索引

CREATE INDEX idx_age ON student(age);

再查看student表的索引

show index from student;

測試：

explain select count(1)
from student
where age+1 = 20

解析出來的type級別是index

查詢條件中的欄位包含索引中的欄位(含有非索引欄位，就會是ALL了)，此時只需要掃描索引樹。也是全表掃描的。

測試結論

運 字決索引失效成立。

最

組合索引，查詢時的條件列不是聯合索引中的第一個列，索引失效（索引的最左原則）。

實戰驗證

查看student表的索引

show index from student;

對student_name,age,gender 欄位添加組合索引

CREATE INDEX idx_student_name_age_gender ON student(student_name,age,gender);

再查看student表的索引

show index from student;

測試：

查詢條件中包含索引的第一列，索引生效：

explain select count(1)
from student
where student_name ='student_name9527'
and gender =1

解析出來的type級別是ref

當查詢語句中的連接條件或者查詢條件使用的索引不是主鍵和非空唯一索引，或者只是一個索引的一部分，則type的取值為ref

索引生效，查詢條件中包含索引的第一列，其他排列組合，大家可自行體驗下。
查詢條件中不包含索引的第一列，索引不生效：

explain select count(1)
from student
where age=20
and gender =1

測試結論

最 字決索引失效成立。

快

查詢數量是超過表的一部分,mysql30%,oracle 20%（這個數據可能不准確，不是官方說明，僅供參考），導致索引失效；

實戰驗證

show index from student;

對create_time 欄位添加索引

CREATE INDEX idx_create_time ON student(create_time);

再查看student表的索引

show index from student;

測試：

查出來的數據量少，可命中索引：

explain select *
from student
where create_time >='2022-10-03 22:48:12' 
and create_time <='2022-10-03 22:48:13'

解析出來的type級別是range

當查詢條件使用索引檢索某個範圍的數據，典型的場景為使用=、<>、>、>=、<、<=、IS [NOT] NULL、<=>、BETWEEN AND或者IN操作符時,類型為range。

查詢出來的數據量多，會直接走全表：

explain select *
from student
where create_time >='2022-10-03 22:48:12'

測試結論

快 字決索引失效成立。

 

點擊關註，第一時間瞭解華為雲新鮮技術~


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

-Advertisement-
Play Games
更多相關文章
  • 【MFC拓展庫】上海道寧與BCGSOFT合作為您帶來專業的Micrisoft Windows開發業務組件
    BCGControlBar是一個MFC擴展庫，允許您創建具有完全自定義選項和一組豐富的專業設計的Microsoft Office和Microsoft Visual Studio類應用程GUI控制項 ...
  • Winform框架中多語言的處理
    Winform界面中，有時候，需要對界面進行一些多語言的處理，以適應客戶場景的使用，如有時候需要考慮英文、日文、韓文等客戶的使用，那麼在Winform界面如何實現這種多語言的正常處理呢，本篇基於Winform開發框架的基礎上介紹使用多語言的處理過程。 ...
  • 基於SqlSugar的開發框架循序漸進介紹（16）-- 工作流模塊的功能介紹
    工作流是集成系統的模塊應用，使用許可權管理系統的身份認證登錄後即可使用。本篇隨筆介紹工作流模塊的界面功能效果。 ...
  • 如何用Virtualbox搭建一個虛擬機
    Virtualbox是Oracle公司出品的，質量還是有保證的。其次，作為虛擬機，它該有的功能也都有，功能夠用，備份，共用，虛擬化技術支持一個都不少。最重要的是，它還是免費的。 ...
  • 自動化管理軟體與Ansible安裝
    自動化管理軟體與Ansible安裝 常見的自動化管理軟體 常見的自動化管理軟體有哪些？ Linux中，常見的配置管理工具有 puppet saltstack ansible Puppet puppet是什麼 puppet是一種Linux/Unix平臺下的集中配置管理系統，使用自有的puppet描述語 ...
  • Windows開機自動同步時間
    前言 有些Windows客戶端因主板電池沒電或其他原因，每次啟動系統後，讀取到BIOS的時間是初始時間（1970年）或錯誤的時間，這時需要系統啟動後立即向時間伺服器同步一次時間。 該方法是添加一個Windows任務計劃，讓系統啟動後立即向時間伺服器同步時間，所以不需要藉助任何插件。 環境 Windo ...
  • nginx配置文件，rewrite，if
    nginx配置文件，rewrite，if rewrit 語法：rewrite regex replacement flag; 比如 rewrite ^/images/(.*\.jpg)$ /imgs/$1 break; 又如 rewrite ^/bbs/(.*)$ http://www.idfsof ...
  • 子查詢
    定義：select語句中嵌套select語句，被嵌套的select語句是子查詢。 子查詢可以出現在： select ....(select).. from ....(select).. where ....(select).. 1.where後面嵌套子查詢 select * from emp whe ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類