MYSQL數(shù)據(jù)庫(kù)中索引的示例分析

這篇文章主要為大家展示了“MYSQL數(shù)據(jù)庫(kù)中索引的示例分析”，內(nèi)容簡(jiǎn)而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“MYSQL數(shù)據(jù)庫(kù)中索引的示例分析”這篇文章吧。

MYSQL INDEX 其中有什么好說(shuō)的，不就是建立索引嗎，一個(gè)建立索引的語(yǔ)句就建立上了，建立的索引就能提高效率，相關(guān)的語(yǔ)句就能使用這個(gè)索引。

真正是這樣的嗎？

這里我們有一個(gè)表employee， 他里面存儲(chǔ)著員工的姓名和出生年月，這里我們有第一個(gè)假設(shè)  

1 只要建立索引，就會(huì)走索引    TRUE OF  FALSE

下圖是表結(jié)構(gòu)展示

我們現(xiàn)在要查詢(xún)出生年月在1960年以前的人的姓名，這里我們提前建立關(guān)于出生日期的索引。

從下圖看雖然執(zhí)行分析器已經(jīng)發(fā)行有了索引，但并未使用

這是為什么，我們總體有30萬(wàn)數(shù)據(jù)，但我們要查詢(xún)大于 1960年的人有多少，進(jìn)過(guò)計(jì)算11萬(wàn)人符合查詢(xún)的條件,也就是說(shuō)，在查詢(xún)中我們的數(shù)據(jù)近乎少一半的數(shù)據(jù)都是符合條件的，自然MYSQL 5.7的基于COST 的MYSQL 數(shù)據(jù)庫(kù)解釋引擎，會(huì)判斷出，走一個(gè)索引，在回調(diào)數(shù)據(jù)，比我直接要全表掃描的來(lái)的爽快。

那我們換一個(gè)條件，我們查找一個(gè)出生在 1960年1月 和 2月之間的員工，果不其然由于數(shù)據(jù)量的縮小，我們清楚的在 KEY 這個(gè) item 中看到我們建立的索引，并且給出一共有1970行符合條件。

從這里我們得出了一個(gè)什么概念，索引的建立也是要基于你搜索的數(shù)據(jù)量與總體的數(shù)據(jù)量的之間的比值，比值如果較大，則不會(huì)走索引，走全表掃描。

所以，建立索引，查詢(xún)就會(huì)走索引，這個(gè)說(shuō)法是錯(cuò)誤的。

我們繼續(xù)假設(shè)，只要建立了索引，并且我們數(shù)據(jù)符合小數(shù)據(jù)量這個(gè)條件就會(huì)走索引。這個(gè)是 TRUE 還是FALSE

下面我們繼續(xù)做一個(gè)實(shí)驗(yàn)，要建立一個(gè)聯(lián)合索引，因?yàn)槲覀冇袃蓚€(gè)查詢(xún)，一個(gè)是查詢(xún)first_name 是 Georgi 出生在1960年后的人數(shù)，按照我們以前的經(jīng)驗(yàn)，我們建立一個(gè)聯(lián)合索引將First_name  和  birth_date 建立一個(gè)聯(lián)合索引，來(lái)滿足這樣的查詢(xún)

create index idx_employee_birth_date_first_nameon employees(birth_date,first_name);

explain select first_name,gender fromemployees where first_name = 'Georgi' and birth_date > '1960-01-01';

結(jié)果是怎樣的，竟然沒(méi)有走索引，是數(shù)據(jù)量符合這個(gè)條件的太多了嗎，我們經(jīng)過(guò)查詢(xún)符合這個(gè)條件的只有106人到底是怎么回事

那我們到底要怎么建立一個(gè)能讓MYSQL在這個(gè)查詢(xún)中，能使用的索引，我們嘗試一下，將查詢(xún)條件換位 

explain select first_name from employees where birth_date > '1960-01-01' and first_name = 'Georgi';

神奇的事情發(fā)生了，同樣的查詢(xún)語(yǔ)句，只需要調(diào)換條件的撰寫(xiě)的順序就可以走索引了事情到此為止了嗎？當(dāng)然沒(méi)有，MYSQL 還有驚喜給我們

查詢(xún)語(yǔ)句的條件的順序可以調(diào)整，那建立索引的順序是否可以調(diào)整，

create index idx_employee_first_name_birth_date on employees(first_name,birth_date);

我們?cè)谶M(jìn)行查詢(xún)看看結(jié)果如何，結(jié)果是我們新建的索引，居然被當(dāng)做最優(yōu)的索引，被查詢(xún)使用，到底為什么

首先，使用過(guò)SQL SERVER 和ORACLE 的程序員，或DBA 要拋棄一個(gè)概念就是我們建立的索引和查詢(xún)條件的順序要基本一致。

這里經(jīng)過(guò)多年的經(jīng)驗(yàn)，總結(jié)出這樣一句話

等于在前，范圍在后，查詢(xún)索引要一致，如有第三者，愛(ài)放那邊放那邊，條件缺一看那個(gè)，缺少等于，就玩完

可能猛的看到這句話，是不大理解里面的意思的，下面我們做演示，大家就能記住

什么是第三者，下面看演示

explain select first_name from employees where first_name = 'Georgi' andlast_name = 'facello' and birth_date > '1960-01-01';

大家可以清晰的看到，在中間查詢(xún)條件中添加了一個(gè)索引中沒(méi)有的條件，但查詢(xún)中還是走了這個(gè)目前最優(yōu)的索引，那如果把這個(gè)條件放到別的地方可以嗎？

explain select first_name from employees where last_name = 'facello' andfirst_name = 'Georgi'  and birth_date> '1960-01-01';

答案也是可以的。最后一句，條件缺一看那個(gè)，缺少等于，就玩完

我們?cè)俅悟?yàn)證一下，

explain select first_name from employees where hire_date > '1986-01-01' andlast_name = 'facello' and first_name = 'Georgi';

果不其然，我們?nèi)サ袅藭r(shí)間字段的范圍查詢(xún)，則查詢(xún)還是可以繼續(xù)走我們建立的索引，而且速度還不慢 那是否我們將前面的查詢(xún)FIRST_NAME 的條件去掉，索引是否還能繼續(xù)使用

explain select first_name from employees where hire_date > '1986-01-01' andlast_name = 'facello'  and  birth_date > '1960-01-01';

我們可以很清晰的看到，索引已經(jīng)失效了，走了全表掃描。

——————————————————————

其實(shí)MYSQL 的索引和查詢(xún)之間的關(guān)系還有更復(fù)雜的地方，今天就到此為止，相對(duì)其他數(shù)據(jù)庫(kù) ORACLE ,SQL SERVER 這方面的要求要復(fù)雜的多，所以使用MYSQL 數(shù)據(jù)庫(kù)有三個(gè)事情是最好不要做的。

1  傳統(tǒng)的表設(shè)計(jì)方式，這里不單單指的是，表的TYPE ，包括邏輯，如何能用更少的表，減少多表之間的關(guān)系等等，讓程序做更多的事情，而不是數(shù)據(jù)庫(kù)。

2  索引和SELECT  , DML 語(yǔ)句，必須要過(guò)一遍，也就是說(shuō)，就算語(yǔ)句是程序生成的，也要導(dǎo)出來(lái)，根據(jù)這些語(yǔ)句的條件順序來(lái)精心的建立索引，所以大部分MYSQL的SQL 語(yǔ)句都是手寫(xiě)而不是程序架構(gòu)生成的，是有這方面因素的考慮，否則很可能就是一個(gè)失敗的開(kāi)始

3 索引建立，應(yīng)謹(jǐn)慎，多個(gè)單個(gè)的索引，去應(yīng)付復(fù)合查詢(xún)的結(jié)果可能和你想象的不一樣， INDEX MERGE optimizer switch在有些查詢(xún)的MERGE 表現(xiàn)并不聰明，MERGE 功能（5.7    有很大的改進(jìn)），很可能意想不到的對(duì)查詢(xún)的二次性能傷害。

所以一個(gè)MYSQL 的 DBA ，含金量要比其他數(shù)據(jù)庫(kù)在這方面掌握的知識(shí)復(fù)雜的多。

以上是“MYSQL數(shù)據(jù)庫(kù)中索引的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！