MYSQL數據庫中索引的示例分析

這篇文章主要為大家展示了“MYSQL數據庫中索引的示例分析”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“MYSQL數據庫中索引的示例分析”這篇文章吧。

MYSQL INDEX 其中有什么好說的，不就是建立索引嗎，一個建立索引的語句就建立上了，建立的索引就能提高效率，相關的語句就能使用這個索引。

真正是這樣的嗎？

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

1 只要建立索引，就會走索引    TRUE OF  FALSE

下圖是表結構展示

我們現在要查詢出生年月在1960年以前的人的姓名，這里我們提前建立關于出生日期的索引。

從下圖看雖然執行分析器已經發行有了索引，但并未使用

這是為什么，我們總體有30萬數據，但我們要查詢大于 1960年的人有多少，進過計算11萬人符合查詢的條件,也就是說，在查詢中我們的數據近乎少一半的數據都是符合條件的，自然MYSQL 5.7的基于COST 的MYSQL 數據庫解釋引擎，會判斷出，走一個索引，在回調數據，比我直接要全表掃描的來的爽快。

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

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

所以，建立索引，查詢就會走索引，這個說法是錯誤的。

我們繼續假設，只要建立了索引，并且我們數據符合小數據量這個條件就會走索引。這個是 TRUE 還是FALSE

下面我們繼續做一個實驗，要建立一個聯合索引，因為我們有兩個查詢，一個是查詢first_name 是 Georgi 出生在1960年后的人數，按照我們以前的經驗，我們建立一個聯合索引將First_name  和  birth_date 建立一個聯合索引，來滿足這樣的查詢

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';

結果是怎樣的，竟然沒有走索引，是數據量符合這個條件的太多了嗎，我們經過查詢符合這個條件的只有106人到底是怎么回事

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

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

神奇的事情發生了，同樣的查詢語句，只需要調換條件的撰寫的順序就可以走索引了事情到此為止了嗎？當然沒有，MYSQL 還有驚喜給我們

查詢語句的條件的順序可以調整，那建立索引的順序是否可以調整，

create index idx_employee_first_name_birth_date on employees(first_name,birth_date);

我們在進行查詢看看結果如何，結果是我們新建的索引，居然被當做最優的索引，被查詢使用，到底為什么

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

這里經過多年的經驗，總結出這樣一句話

等于在前，范圍在后，查詢索引要一致，如有第三者，愛放那邊放那邊，條件缺一看那個，缺少等于，就玩完

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

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

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

大家可以清晰的看到，在中間查詢條件中添加了一個索引中沒有的條件，但查詢中還是走了這個目前最優的索引，那如果把這個條件放到別的地方可以嗎？

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

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

我們再次驗證一下，

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

果不其然，我們去掉了時間字段的范圍查詢，則查詢還是可以繼續走我們建立的索引，而且速度還不慢 那是否我們將前面的查詢FIRST_NAME 的條件去掉，索引是否還能繼續使用

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

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

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

其實MYSQL 的索引和查詢之間的關系還有更復雜的地方，今天就到此為止，相對其他數據庫 ORACLE ,SQL SERVER 這方面的要求要復雜的多，所以使用MYSQL 數據庫有三個事情是最好不要做的。

1  傳統的表設計方式，這里不單單指的是，表的TYPE ，包括邏輯，如何能用更少的表，減少多表之間的關系等等，讓程序做更多的事情，而不是數據庫。

2  索引和SELECT  , DML 語句，必須要過一遍，也就是說，就算語句是程序生成的，也要導出來，根據這些語句的條件順序來精心的建立索引，所以大部分MYSQL的SQL 語句都是手寫而不是程序架構生成的，是有這方面因素的考慮，否則很可能就是一個失敗的開始

3 索引建立，應謹慎，多個單個的索引，去應付復合查詢的結果可能和你想象的不一樣， INDEX MERGE optimizer switch在有些查詢的MERGE 表現并不聰明，MERGE 功能（5.7    有很大的改進），很可能意想不到的對查詢的二次性能傷害。

所以一個MYSQL 的 DBA ，含金量要比其他數據庫在這方面掌握的知識復雜的多。

以上是“MYSQL數據庫中索引的示例分析”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！