前言

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

一 概述

日常處理mysql問題中，往往通過增加索引來提高查詢速度，但在有些情況下，執(zhí)行過程中并沒有按照我們的預(yù)期結(jié)果執(zhí)行，也就是說，即使字段加了索引，但現(xiàn)實(shí)也沒有使用到，到底是什么地方出了差錯，以下我們將一探究竟。

二 實(shí)驗(yàn)表結(jié)構(gòu)聲明

我們將對以下表結(jié)構(gòu)進(jìn)行實(shí)際案例分析：

CREATETABLE 

三 Mysql不走索引歸類以及詳細(xì)解析

根據(jù)實(shí)驗(yàn)表做具體case分析，歸納為以下幾點(diǎn):

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

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

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

2. 查詢條件字符串和數(shù)字之間的隱式轉(zhuǎn)換

例如：name與age分別做字符串/數(shù)字（88）的隱式轉(zhuǎn)換；

以下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. 索引優(yōu)化器選擇最優(yōu)的索引

這一點(diǎn)最重要，索引到底用不用，不是列加了索引就一定會用，而是根據(jù)索引優(yōu)化器來決定。

索引優(yōu)化器的存在，就是找到一個索引掃描行數(shù)最少的方案去執(zhí)行語句。那么掃描行數(shù)怎么來判斷的？是逐行統(tǒng)計(jì)數(shù)據(jù)表的數(shù)據(jù)嗎？其實(shí)并不是，而是根據(jù)統(tǒng)計(jì)信息來估算的值。這個統(tǒng)計(jì)信息就是我們常說的索引的“區(qū)分度”。

顯然，一個索引上不同的值越多，這個索引的區(qū)分度就越好。我們把一個索引上不同的值的個數(shù)，稱之為“索引基數(shù)”。也就是說，基數(shù)越大，索引的區(qū)分度就越好，執(zhí)行查詢的行數(shù)就越少。如何查看索引基數(shù)呢？使用 show index from 表名，cardinality字段顯示的就是索引的基數(shù)。

擴(kuò)展：MySQL 是怎樣得到索引基數(shù)的呢？不感興趣的小伙伴可以飄過啦。

索引基數(shù) = 采樣統(tǒng)計(jì)*頁數(shù)。采樣統(tǒng)計(jì)就是避免把整張表取出來一行行統(tǒng)計(jì)做精準(zhǔn)計(jì)算，以免消耗系統(tǒng)性能。在采樣統(tǒng)計(jì)時，InnoDB默認(rèn)會選擇 N 個數(shù)據(jù)頁，統(tǒng)計(jì)這些頁面上的不同值，得到一個平均值，然后乘以這個索引的頁面數(shù)，就得到了這個索引的基數(shù)。統(tǒng)計(jì)信息不是固定不變的，他會隨著數(shù)據(jù)表的變化而變化。當(dāng)變更的數(shù)據(jù)行數(shù)超過 1/M 的時候，會自動觸發(fā)重新做一次索引統(tǒng)計(jì)。

索引優(yōu)化器實(shí)例一：

經(jīng)常聽人說，執(zhí)行<>語句時，不走索引，今天我們將看一看實(shí)際執(zhí)行情況，還是那句話，到底走不走，我們說了不算，還是索引優(yōu)化器說了算：看截圖 ，就會發(fā)現(xiàn) <> 其實(shí)是走了索引。

為什么會出現(xiàn)這樣的情景呢？因?yàn)?student表中10W多條數(shù)據(jù)的值全都是'name'，索引基數(shù)太小，所以在執(zhí)行<>'1name'查詢時，實(shí)際上要查詢條數(shù)為10多W條，如果走了name字段索引，其實(shí)和全表查詢沒什么區(qū)別，況且，執(zhí)行name字段索引，最終還是要轉(zhuǎn)換為主鍵索引（二級索引查詢都會轉(zhuǎn)換為主鍵查詢），所以索引優(yōu)化器的優(yōu)化結(jié)果是不走name索引。然而在執(zhí)行<>'name'查詢時，優(yōu)化器優(yōu)化結(jié)果是走name索引，因?yàn)椋?lt;>'name'的查詢行數(shù)很小，大部分條數(shù)name字段的值都是'name'。

索引優(yōu)化器實(shí)例二：

同理，前綴like匹配是走索引，但是，以下卻展示了不一樣的結(jié)果：

為什么會這樣呢？請小伙伴們參考實(shí)例一仔細(xì)思考一下。

如何指定優(yōu)化器執(zhí)行固定的索引？

索引優(yōu)化器的存在，我們就沒辦法指定強(qiáng)制走我們指定的索引？答案就是通過 force index強(qiáng)制來實(shí)現(xiàn)，

執(zhí)行語句和分析結(jié)果如下圖所示：

四 總結(jié)以及實(shí)際應(yīng)用

實(shí)際應(yīng)用中，應(yīng)該牢記上述索引優(yōu)化的原則，比如在實(shí)際工作中，由于索引優(yōu)化器選錯索引，導(dǎo)致數(shù)據(jù)查詢緩慢，阻塞線上業(yè)務(wù)，而當(dāng)時的解決辦法，就是上述文章的分析過程，以及采用force 強(qiáng)制索引才解決的，前車之鑒，希望廣大讀者避免踩坑。

到此這篇關(guān)于mysql or走索引加索引及慢查詢的作用的文章就介紹到這了,更多相關(guān)mysql or索引 內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論