前言

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

一 概述

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

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

我們將對(duì)以下表結(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)最重要，索引到底用不用，不是列加了索引就一定會(huì)用，而是根據(jù)索引優(yōu)化器來(lái)決定。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

最新評(píng)論