今天就從這個(gè)問題來聊聊MySQL選擇索引時(shí)都做一些什么事情。

[[433710]]

一、如何選擇索引

影響優(yōu)化器的幾大因素

一條查詢SQL執(zhí)行需要經(jīng)過連接器、分析器、優(yōu)化器、執(zhí)行器，而選擇索引的重任就交給了優(yōu)化器。

優(yōu)化器在多個(gè)索引中選擇目的是為了找出執(zhí)行代價(jià)最低的方案。

影響優(yōu)化器選擇無非就這幾個(gè)因素，掃描行數(shù)、是否使用了臨時(shí)表、是否使用文件排序。

臨時(shí)表、文件排序這個(gè)兩個(gè)點(diǎn)會在后期文章給大家慢慢引出，今天只聊掃描行數(shù)。

掃描行數(shù)越少則訪問磁盤數(shù)據(jù)的次數(shù)就越少，消耗的CPU資源越少。

那么這個(gè)掃描行數(shù)是從哪里取的呢?

掃描行數(shù)從何而來?

創(chuàng)建索引一直提倡大家給區(qū)分度高的列建立索引，在一個(gè)索引上不同值的個(gè)數(shù)稱之為基數(shù)(cardinality)。

使用show index from table_name可以查看每個(gè)索引的基數(shù)是多少。

索引基數(shù)

索引基數(shù)怎么計(jì)算

MySQL使用采樣統(tǒng)計(jì)的方法，會選出N個(gè)數(shù)據(jù)頁，每個(gè)數(shù)據(jù)頁大小16kb，接著統(tǒng)計(jì)選出來的數(shù)據(jù)頁上的不同值就會得到一個(gè)平均值，用平均值在乘以索引的頁面數(shù)得到的結(jié)果就是這個(gè)索引的基數(shù)。

表數(shù)據(jù)是持續(xù)增加或刪減的，統(tǒng)計(jì)的這個(gè)數(shù)據(jù)也不是時(shí)時(shí)變化的，當(dāng)變更的數(shù)據(jù)超過1/M時(shí)會自動(dòng)觸發(fā)重新計(jì)算。

這個(gè)M是根據(jù)參數(shù)innodb_stats_persistent的值選則的，設(shè)置為on值為10，設(shè)置為off值為16。

索引基數(shù)通過這種方式計(jì)算不是精準(zhǔn)的但也差不了多少

為什么優(yōu)化器選擇了掃描行數(shù)多的索引?

第一種情況

表增刪十分頻繁，導(dǎo)致掃描行數(shù)不準(zhǔn)確

第二種情況

假設(shè)你主鍵索引掃描行數(shù)是10W行，而普通索引需要掃描5W行，這種情況就會遇到優(yōu)化器選擇了掃描行數(shù)多的。

在索引那一期文章中知道主鍵索引是不需要回表的，找到值直接就返回對應(yīng)的數(shù)據(jù)了。

而普通索引是需要先拿到主鍵值，再根據(jù)主鍵值獲取對應(yīng)的數(shù)據(jù)，這個(gè)過程優(yōu)化器選擇索引時(shí)需要計(jì)算的一個(gè)成本。

如何解決這種情況

掃描行數(shù)不準(zhǔn)確時(shí)可以執(zhí)行analyze table table_name命令，重新統(tǒng)計(jì)索引信息，達(dá)到預(yù)期優(yōu)化器選擇的索引。

二、索引選擇異常如何處理

方案一

在MySQL中提供了force index來強(qiáng)制優(yōu)化器使用這個(gè)索引。

使用方法：select * from table_name force index (idx_a) where a = 100;

但別誤解force index的使用方法，之前在代碼中看到這樣一個(gè)案例，給查詢列使用了函數(shù)操作導(dǎo)致使用不上索引，然后這哥們就直接使用force index，肯定不行的哈!

當(dāng)優(yōu)化器沒有正確選擇索引時(shí)是可以使用這種方案來解決。

缺點(diǎn)

使用force index的缺點(diǎn)相信大家也知道就是太死板，一旦索引名字改動(dòng)就會失效。

方案二

刪掉誤選的索引，簡單粗暴，很多索引建立其實(shí)也是給優(yōu)化器的一個(gè)誤導(dǎo)，直接刪掉即可。

方案三

修改SQL語句，主動(dòng)引導(dǎo)MySQL使用期望的索引，一般情況這種做法使用的很少除非你對系統(tǒng)十分熟悉，否則盡量少操作。

三、總結(jié)

優(yōu)化器選擇索引首先會根據(jù)掃描行數(shù)再由執(zhí)行成本決定。

當(dāng)索引統(tǒng)計(jì)信息不準(zhǔn)確時(shí)，使用analyze table 解決。

優(yōu)化器選擇了錯(cuò)誤的索引，只用force index來快速矯正，再通過優(yōu)化SQL語句來引導(dǎo)優(yōu)化器選擇正確的索引，最暴力的手法是直接刪除誤選的索引。