mysql or走索引加索引及慢查詢的作用

2022-09-12 18:02:30

前言

小白白跑去鵝廠面試，面試官提出了一個很實際的問題： mysql增加索引，那些情況會失效呢？談一下實際工作中遇到的情況。我們的小白白又丟擲了白氏祕籍：用不用索引，找DBA小姐姐！啊？這是你面試哈，還是DBA小姐姐面試呀。

一概述

日常處理mysql問題中，往往通過增加索引來提高查詢速度，但在有些情況下，執行過程中並沒有按照我們的預期結果執行，也就是說，即使欄位加了索引，但現實也沒有使用到，到底是什麼地方出了差錯，以下我們將一探究竟。

二實驗表結構宣告

我們將對以下表結構進行實際案例分析：

CREATETABLE

三 Mysql不走索引歸類以及詳細解析

根據實驗表做具體case分析，歸納為以下幾點:

1. 查詢條件在索引列上使用函數操作,或者運算的情況

例如以下case是不走索引的：

explain select * from student where abs(age) =18;
explain select * from student where age + 1=18;

2. 查詢條件字串和數位之間的隱式轉換

例如：name與age分別做字串/數位（88）的隱式轉換；

以下case走索引情況：

explain select * from student where name ='88'; 
explain select * from student where age='88'; 
explain select * from student where age =88;

以下case不走索引情況：

explain select * from student where name=88;

3. 特殊修飾符 %%, Or 將不走索引

explain select * from student where name like'%name%' ;
explain select * from student where name ='name' or age = 18;

4. 索引優化器選擇最優的索引

這一點最重要，索引到底用不用，不是列加了索引就一定會用，而是根據索引優化器來決定。

索引優化器的存在，就是找到一個索引掃描行數最少的方案去執行語句。那麼掃描行數怎麼來判斷的？是逐行統計資料表的資料嗎？其實並不是，而是根據統計資訊來估算的值。這個統計資訊就是我們常說的索引的“區分度”。

顯然，一個索引上不同的值越多，這個索引的區分度就越好。我們把一個索引上不同的值的個數，稱之為“索引基數”。也就是說，基數越大，索引的區分度就越好，執行查詢的行數就越少。如何檢視索引基數呢？使用 show index from 表名，cardinality欄位顯示的就是索引的基數。

擴充套件：MySQL 是怎樣得到索引基數的呢？不感興趣的小夥伴可以飄過啦。

索引基數 = 取樣統計*頁數。取樣統計就是避免把整張表取出來一行行統計做精準計算，以免消耗系統效能。在取樣統計時，InnoDB預設會選擇 N 個資料頁，統計這些頁面上的不同值，得到一個平均值，然後乘以這個索引的頁面數，就得到了這個索引的基數。統計資訊不是固定不變的，他會隨著資料表的變化而變化。當變更的資料行數超過 1/M 的時候，會自動觸發重新做一次索引統計。

索引優化器範例一：

經常聽人說，執行<>語句時，不走索引，今天我們將看一看實際執行情況，還是那句話，到底走不走，我們說了不算，還是索引優化器說了算：看截圖，就會發現 <> 其實是走了索引。

為什麼會出現這樣的情景呢？因為 student表中10W多條資料的值全都是'name'，索引基數太小，所以在執行<>'1name'查詢時，實際上要查詢條數為10多W條，如果走了name欄位索引，其實和全表查詢沒什麼區別，況且，執行name欄位索引，最終還是要轉換為主鍵索引（二級索引查詢都會轉換為主鍵查詢），所以索引優化器的優化結果是不走name索引。然而在執行<>'name'查詢時，優化器優化結果是走name索引，因為，<>'name'的查詢行數很小，大部分條數name欄位的值都是'name'。

索引優化器範例二：

同理，字首like匹配是走索引，但是，以下卻展示了不一樣的結果：