前言
小弟最近在開發(fā)一個項目時遇到了有點困擾我的問題，很有意思，而且也值得記錄一下，希望對大家有用
場景：
我們有兩個表，一個訂單表表示t1，一個是訂單的明細(xì)表t2，t2表中包含用戶購買的各個產(chǎn)品，他們是根據(jù)訂單編號關(guān)聯(lián)的，當(dāng)我用t1作為驅(qū)動表left join 連接t2表時沒用到索引，但是用t2表連接t1表時，就用到了全文檢索，很奇怪！因為按照我們通常的想法都是小表驅(qū)動大表
業(yè)務(wù)要求：
1.根據(jù)產(chǎn)品名稱或產(chǎn)品型號或訂單編號模糊查詢這個訂單的所有信息
2：不能用like，所以用的是全文檢索
說明：
這篇博客最大的作用不是教大家怎么解決，而是分析為什么會這樣！
文章目錄
準(zhǔn)備
MySQL：8.0
Java：1.8
創(chuàng)建t1表和t2表
創(chuàng)建索引：
t1和t2表的關(guān)系：一對多
t1
字段是一個唯一索引
字段是普通索引
t2
除了主鍵id，其它字段全是全文檢索
1、在已有的表中添加全文索引
create fulltext index 索引名稱 on 表名(字段名) WITH PARSER ngram;
2、搜索
SELECT * FROM tab_name WHERE MATCH ('列名1,列名2...列名n') AGAINST('詞1 詞2 詞3 ... 詞m' IN BOOLEAN MODE);
問題
用t1作為驅(qū)動表left join 連接t2表時沒用到索引，但是用t2表連接t1表時，就用到了全文檢索，用沒用到索引有多大的差距，不做多訴說，相信大家都知道
t1 left join t2
sql：
EXPLAIN
SELECT t1.* FROM t1
LEFT JOIN t2 on t1.order_number = t2.order_number where t1.`status` = "true" and MATCH(t2.`name`) AGAINST("華為" IN BOOLEAN MODE)
結(jié)果：
說明：
所用實際為100毫秒左右
t1和t2表的type都是ALL，證明全都是全表掃描，
指預(yù)計會用到的索引
key為實際用到的索引
rows為11和15是預(yù)計掃描了11行（可能跟實際行數(shù)有差距，這里只是預(yù)計，所以只做參考數(shù)據(jù)）
結(jié)果值從好到壞依次是： > const > > ref > > > > > > range > index > ALL。一般來說，得保證查詢至少達到range級別，最好能達到ref，否則就可能會出現(xiàn)性能問題。所以這個sql要優(yōu)化了。
t2 left join t1
sql：
EXPLAIN
SELECT t1.* FROM t2
LEFT JOIN t1 on t1.order_number = t2.order_number where t1.`status` = "true" and MATCH(t2.`name`) AGAINST("華為" IN BOOLEAN MODE)
結(jié)果：
說明：
所用實際為50毫秒左右
比沒用到索引快一倍，這個倍數(shù)會隨著數(shù)據(jù)的增多二增加，現(xiàn)在只有十幾條數(shù)據(jù)就差一倍，后續(xù)可想而知（大家在測試時如果速度一樣，則增多數(shù)據(jù)，就會有很明顯的差距）
t1和t2表的type一個為，一個為，細(xì)表是用到了全文檢索，主表的索引等級也是很高
指預(yù)計會用到的索引
key為實際用到的索引
rows都為1（可能跟實際行數(shù)有差距，這里只是預(yù)計，所以只做參考數(shù)據(jù)）
小結(jié)：
不言而喻，我們肯定會選擇第二種，但是又會產(chǎn)生一些問題：
我們都在強調(diào)用小表驅(qū)動大表，我們現(xiàn)在選用第二種好像違背了這一點為什么換了一下位置就用到了索引索引的走向到底是怎么走的 說明 問題1：MySQL為什么推薦小表驅(qū)動大表
MySQL 表關(guān)聯(lián)的算法是 Nest Loop Join，是通過驅(qū)動表的結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù)，然后一條一條地通過該結(jié)果集中的數(shù)據(jù)作為過濾條件到下一個表中查詢數(shù)據(jù)，然后合并結(jié)果。
例如：t1有10條數(shù)據(jù)，t2表有1000條數(shù)據(jù)，t1表每條數(shù)據(jù)關(guān)聯(lián)t2表100條數(shù)據(jù)
sql：
SELECT * FROM t2 LEFT JOIN t1 on t1.order_number = t2.order_number
這樣則需要用t2表循環(huán)1000次才能查詢出來，而如果用t1表驅(qū)動user表則只需要循環(huán)20次就能查詢出來。
小表驅(qū)動大表主要是減少連接次數(shù)，增加速度
因此這個語句的執(zhí)行流程是：
從表t2中讀入一行數(shù)據(jù)從數(shù)據(jù)行中，取出字段到表t1里去查找取出表t1中滿足條件的行，跟t2的數(shù)據(jù)組成一行，作為結(jié)果集的一部分重復(fù)上面操作，直到t2的數(shù)據(jù)取完為止 NLJ
這個過程是先遍歷表t2，然后根據(jù)表t1中取出的每行數(shù)據(jù)中的值，去表t1中查詢滿足條件的記錄。這就是"Index -Loop Join"，簡稱NLJ。
因此建議內(nèi)表走索引，也叫INLJ，但是如果內(nèi)表是二級索引，效率也低，因為要回表查主鍵。
索引MySQL會判斷使用索引和不使用的差別，如果檢測全表掃描比使用索引還快，它就會選擇ALL
所以一般情況下我們是建議都是小表驅(qū)動大表
BNL
這里我們把t2表的字段的索引刪除
sql:
SELECT * FROM t2 LEFT JOIN t1 on t1.order_number = t2.order_number
結(jié)果就是兩個表都要全表掃描，這里我們看到，Extra顯示的是（Using where; Using join (Block Loop)）
這個其實是MySQL對join不走索引全表掃描做了一個優(yōu)化，簡稱BNL。
因此這個語句的執(zhí)行流程是：
把表t1的數(shù)據(jù)讀入線程內(nèi)存中，這里我們是把整個表t1放入內(nèi)存中。掃描表t2，把表t2中的每一行取出來，跟中的數(shù)據(jù)做對比，滿足join條件的，作為結(jié)果集的一部分返回。
這里，我們兩個表都是做的全表掃描，所以不管是哪個表做驅(qū)動表都是執(zhí)行消耗都是一樣的。
如果一個表的數(shù)據(jù)太大了，根本裝不下所有數(shù)據(jù)的話，就采用分段放。也可以修改。
MRR
.6版本開始支持的Multi-Range Read（MRR）優(yōu)化。MRR目的是為了減少磁盤的隨機訪問，并且將隨機訪問轉(zhuǎn)換為較為順序的數(shù)據(jù)訪問，MRR可適用于range，ref，類型的查詢
MRR優(yōu)化有以下幾個好處：
MRR使數(shù)據(jù)訪問變的較為順序。在查詢輔助索引時，首先根據(jù)得到的查詢結(jié)果，按照主鍵進行排序，并按照主鍵排序的順序進行書簽查找減少緩沖池中頁被替換的次數(shù)批量處理對鍵值的查詢操作
MRR的設(shè)計思路是因為大多數(shù)的數(shù)據(jù)都是按照主鍵遞增順序插入得到的，所以我們可以認(rèn)為，如果按照主鍵的遞增順序查詢的話，對磁盤的讀比較接近順序讀，能夠提升讀性能。
MRR 能夠提升性能的核心在于，這條查詢語句在索引 a 上做的是一個范圍查詢（也就是說，這是一個多值查詢），可以得到足夠多的主鍵 id。這樣通過排序以后，再去主鍵索引查數(shù)據(jù)，才能體現(xiàn)出“順序性”的優(yōu)勢。
BKA
針對于有索引的被驅(qū)動表，.6版本開始增加了 Key （BKA）的新特性
對于多表join語句，當(dāng)MySQL使用索引訪問第一個join表的時候，使用來收集第一個操作對象生成的相關(guān)列的值。BKA構(gòu)建好key之后，通過MRR接口提交給引擎做查詢。
BKA步驟：
將驅(qū)動表相關(guān)的列放入中。批量的將Key（索引鍵值）發(fā)送到MRR接口。MRR通過收到的key，根據(jù)其對應(yīng)的ROWID進行排序，然后再進行數(shù)據(jù)的讀取操作。
這里來看，BKA和BNL其實是差不多的，主要區(qū)別就是BKA是針對被驅(qū)動表是走索引的情況下，索引是非主鍵索引的時候，按照索引字段進行排序，因此減少了隨機IO，提高性能。
問題2：為什么換了一下位置就用到了索引？
我開始在網(wǎng)上搜索的時候得到一種答案，說的是兩個表的字符集不一樣
我檢查了我的兩個表，字符集都是一致的
雖然不適用這篇文章，但是確實是一種情況，當(dāng)字符集不一致，是會出現(xiàn)的，所以只要改成一樣就能解決
那么：
為什么我們的一致還是會出現(xiàn)這種情況呢？
最好的解釋就是MySQL認(rèn)為不走索引比走索引快where條件的干擾 t1 left join t2 沒有where條件
sql：
EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 on t1.order_number = t2.order_number
加上where條件
sql：
EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 on t1.order_number = t2.order_number where t1.`status` = "true" and MATCH(t2.`name`) AGAINST("華為" IN BOOLEAN MODE)
分析
我們可以看到結(jié)果差不多的，那可以分析一下各種情況：
沒有where條件時我的訂單詳情查詢，主表就是全表掃描，為啥？
因為MySQL在此認(rèn)為走ALL更快，所以沒選，或者說，就應(yīng)該全表掃描，（還有一些特殊情況會用到索引， 例如主表關(guān)聯(lián)字段是多個，也就是多對一的情況下會用到索引，大家可以試試，然后可以想想為什么）
沒有where條件時：細(xì)表預(yù)計用到索引，但最后也沒用到，為啥？
因為主表是全表掃描，一樣的道理，t1表需要全部查出來,然后拿查出來的字段去t2表匹配，然后組成一行數(shù)據(jù)返回，可想而知；
t2全部掃描用索引不是更快嗎？為啥不用？
因為t2表的是全文檢索，全文檢索是啥大家可以去自行了解一下，它跟es一樣，是把字段內(nèi)容分詞處理，每個分詞都是索引，索引聯(lián)表時如果是全文檢索，那比ALL更慢
一旦把字段換成別的索引，比如普通索引或者唯一索引，結(jié)果是有驚喜的，它S是會用到索引的
有where條件時：主表預(yù)計用到索引，但實際還是沒用到，為啥？
首先要知道為什么會預(yù)計用到 索引；是因為where后面有t1. = "true" 這個條件，但最后沒用到的原因是：
是二級索引，也就是非聚集索引，所以它會造成一個回表操作還有就是t1表中這個字段的值都是ture，一但把一半的數(shù)據(jù)改成fasel，那么就會用到索引當(dāng)這個字段的結(jié)果都是一樣時，MySQL認(rèn)為這個回表操作比ALL更慢，所以選擇了ALL
有where條件時：細(xì)表預(yù)計使用,但最后也沒用到，為啥？
我們先把改成普通索引，這種情況下，沒有where條件時是會用到索引的,但是當(dāng)我們加上where條件后，結(jié)果還是跟上面一摸一樣,為什么呢？而且我們where的條件還是name字段的索引，就連預(yù)計都不會使用name這個索引
我們假設(shè)用到了這個索引，當(dāng)一條數(shù)據(jù)從t1表過來，MySQL需要先根據(jù)索引找到主鍵索引，然后根據(jù)這個主鍵再去主鍵索引也就是id去查找name字段，然后才能使用這個索引，看到這里，大家應(yīng)該懂了為什么MySQL連預(yù)計都沒有
至于最后為什么還是沒用到索引，主要是where后面使用了name這個字段，需要像上面一樣繁瑣，一旦我們后面的條件改成 where t1.= "true" and t2. = ""，那么結(jié)果將大大不一樣
為什么會這樣呢？就連主表也用到了索引，大家可以自行想想，其實很有意思，哈哈
在有索引的情況下，MySQL會嘗試去使用Index -Loop Join算法，在有些情況下，可能Join的列就是沒有索引，那么這時MySQL的選擇Block -。
t2 left join t1 沒有where條件
sql：
EXPLAIN SELECT t1.* FROM t2 LEFT JOIN t1 on t1.order_number = t2.order_number
加上where條件
sql：
EXPLAIN SELECT t1.* FROM t2 LEFT JOIN t1 on t1.order_number = t2.order_number where t1.`status` = "true" and MATCH(t2.`name`) AGAINST("華為" IN BOOLEAN MODE)
分析
可以看到結(jié)果的區(qū)別主要就是在t2表用到了索引，我們不去分析沒有加where條件的情況，上面已經(jīng)說得很清楚了，沒看懂的同學(xué)可以仔細(xì)琢磨琢磨，下面我們將分析一下有條件的情況：
其實當(dāng)你理清楚上面用t1 left join t2的情況時，就很容易理解為什么會這樣了
主表也就是驅(qū)動表為啥使用了索引?
其實主要是有 MATCH(t2.name) ("華為" IN MODE)這個條件，我們可以想象一下，當(dāng)一條數(shù)據(jù)進來時，MySQL可以根據(jù)這個索引快速定位到具體哪一行數(shù)據(jù)，所以連它的rows預(yù)計掃描行數(shù)都只有1條我的訂單詳情查詢，然后根據(jù)這行數(shù)據(jù)的字段去掃描t1表，而t1表正好也有這個索引，所以結(jié)果不言而喻
其實到了這一步，已經(jīng)不需要分析太多，估計大家懂了，懂得都懂，不懂得可以再看看
結(jié)束語
相信看了這篇博客可以很好的幫助你設(shè)計索引，還有最后一句話：
任何執(zhí)拗都會成為過往，只有時間會告訴你對錯。
免責(zé)聲明：部分文章信息來源于網(wǎng)絡(luò)以及網(wǎng)友投稿，本站只負(fù)責(zé)對文章進行整理、排版、編輯，出于傳遞更多信息之目的，并不意味著贊同其觀點或證實其內(nèi)容的真實性，如本站文章和轉(zhuǎn)稿涉及版權(quán)等問題，請作者在及時聯(lián)系本站，我們會盡快為您處理。
