丨mysql調(diào)優(yōu)之索引失效與優(yōu)化

然而，DBA很快就將這個需求駁回了，并給出了重建一個sku、status以及create_time組合索引的建議，查詢順序也改成了sku=10001andstatus=1。當(dāng)時(shí)我是知道為什么要重建組合索引，但卻無法理解為什么要添加create_time這列進(jìn)行組合。從執(zhí)行計(jì)劃中，我們可以發(fā)現(xiàn)使用到了索引，那為什么DBA還要求將eae_ime這一列加入到組合索引中呢？這個問題我們在第32講中提到過，相信你也已經(jīng)知道答案了。通過故事我們可以發(fā)現(xiàn)索引知識在平時(shí)開發(fā)時(shí)的重要性，然而它又很容易被我們忽略，所以今天我們就來詳細(xì)聊一聊索引。MySQL索引結(jié)索引是優(yōu)化數(shù)據(jù)庫查詢最重要的方式之一，它是在MySQL的引擎層中實(shí)現(xiàn)的，所以B+Tree索引和Hash索引是我們比較常用的兩個索引數(shù)據(jù)結(jié)構(gòu)，B+Tree索引是通過B+樹實(shí)現(xiàn)的，是有序排列，所以在排序和范圍查找方面都比較有優(yōu)勢。如果你對B+Tree索引不夠了解，可以通過該了解下它的數(shù)據(jù)結(jié)構(gòu)原理。Hash索引相對簡單些，只有Memory引擎支持Hash索引。Hash索引適合key-valueO(1Hash創(chuàng)建的主鍵索引默認(rèn)使用的是B+Tree索引。不過雖然這兩個引擎都支持B+Tree索引，InnoDB默認(rèn)創(chuàng)建的主鍵索引是聚族索引（ClusteredIndex） CREATETABLE`merchandise``id`int(11)NOT`serial_no`varchar(20)DEFAULT`name`varchar(255)DEFAULT`unit_price`decimal(10,2)DEFAULTPRIMARYKEY(`id`)USING)CHARACTERSET=utf8COLLATE=utf8_general_ciROW_FORMAT=如果我們使用的是MyISAM引擎，由于MyISAM使用的是輔助索引，索引中每一個如果我們使用的是InnoDB引擎，由于InnoDB使用的是聚族索引，聚族索引中的葉idMVVC基于上面的圖示，如果我們需要根據(jù)商品編碼查詢商品，我們就需要將商品編碼serial_no列作為一個索引列。此時(shí)創(chuàng)建的索引是一個輔助索引，與MyISAM引擎的主鍵索引的方式是一致的，但葉子節(jié)點(diǎn)的就不是行指針了，而是主鍵值，并以此來作為指向行如果我們使用主鍵索引查詢商品，則會按照B+樹的索引找到對應(yīng)的葉子節(jié)點(diǎn)，直接獲取到select*frommerchandisewhere如果我們使用商品編碼查詢商品，即使用輔助索引進(jìn)行查詢，則會先檢索輔助索引中的B+樹的serial_no，找到對應(yīng)的葉子節(jié)點(diǎn)，獲取主鍵值，然后再通過聚族索引中的B+樹檢索假設(shè)我們只需要查詢商品的名稱、價(jià)格信息，我們有什么方式來避免回表呢？我們可以建立一個組合索引，即商品編碼、名稱、價(jià)格作為一個組合索引。如果索引中存在這些數(shù)據(jù)，查詢將不會再次檢索主鍵索引，從而避免回表。從輔助索引中查詢得到記錄，而不需要通過聚族索引查詢獲得，MySQL中將其稱為覆蓋索引。使用覆蓋索引的好處很明顯，我們不需要查詢出包含整行記錄的所有信息，因此可以減少大量的I/O操作。通常在InnoDB中，除了查詢部分字段可以使用覆蓋索引來優(yōu)化查詢性能之外，統(tǒng)計(jì)數(shù)量也會用到。例如，在第32講我們講SELECTCOUNT(*)時(shí)，如果不存在輔助索引，此時(shí)會統(tǒng)計(jì)行數(shù)，減少I/O操作。通過EX IN，我們可以看到InnoDB引擎使用了idx_order索引列來統(tǒng)計(jì)行數(shù)，如上面我們講了InnoDB創(chuàng)建主鍵索引默認(rèn)為聚族索引，數(shù)據(jù)被存放在了B+樹的葉子節(jié)點(diǎn)如果我們使用自增主鍵，那么每次插入的新數(shù)據(jù)就會按順序添加到當(dāng)前索引節(jié)點(diǎn)的位置，不需要移動已有的數(shù)據(jù)，當(dāng)頁面寫滿，就會自動開辟一個新頁面。因?yàn)椴恍枰匦乱苿訑?shù)據(jù)，因此這種插入數(shù)據(jù)的方法效率非常高。如果我們使用非自增主鍵，由于每次插入主鍵的索引值都是隨機(jī)的，因此每次插入新的數(shù)據(jù)時(shí)，就可能會插入到現(xiàn)有數(shù)據(jù)頁中間的某個位置，這將不得不移動其它數(shù)據(jù)來滿足新數(shù)據(jù)的插入，甚至需要從一個頁面數(shù)據(jù)到另外一個頁面，我們通常將這種情況稱為頁。頁還有可能會造成大量的內(nèi)存碎片，導(dǎo)致索引結(jié)構(gòu)不緊湊，從而影響查詢效率。因此，在使用InnoDB引擎時(shí)，如果沒有特別的業(yè)務(wù)需求，建議使用自增字段作為主我們知道，索引文件是在磁盤中的，而磁盤中最小分配單元是頁，通常一個頁的默認(rèn)大小為16KB，假設(shè)我們建立的索引的每個索引值大小為2KB，則在一個頁中，我們能記錄8個索引值，假設(shè)我們有8000行記錄，則需要1000個頁來索引。如果我們使用該索引查詢數(shù)據(jù)，可能需要遍歷大量頁，這顯然會降低查詢效率。orderbySQLHash是說Memory引擎實(shí)現(xiàn)的Hash索引只有在“=”的查詢條件下，索引才會生效。order表設(shè)置為Memory引擎，分析查詢條件為id<10的SQL，可以發(fā)現(xiàn)沒有使用如果是以%開頭的LIKE例如我們在order表中建立一個復(fù)合索引idx_user_order_status(order_no,status,user_id)，如果我們使用order_no、order_no+status、order_no+status+user_id以及order_no+user_id組合查詢，則能利用到索引；而如果我們用status、status+user_idor，orMySQLMySQL知識，很多時(shí)候都將被DBA批評到懷疑人生。在大多數(shù)情況下，我們習(xí)慣使用默認(rèn)的InnoDB作為表引擎。在使用InnoDB作為引擎時(shí)，創(chuàng)建的索引默認(rèn)為B+樹數(shù)據(jù)結(jié)構(gòu)，如果是主鍵索引，則屬于聚族索引，非主鍵索 索引，且由于索引是順序，對于范圍或排序查詢來說，可以極大地極少磁盤I/O規(guī)則太多，難以記住，我們就要養(yǎng)成經(jīng)常檢查SQL執(zhí)行計(jì)劃的習(xí)慣。order_detailidorder_idsku段，其中該表有主鍵索引、主訂單id索引。SQL5000~10000，請問該查詢選擇1select*fromorder_detailwhereorder_idbetween5000and期待在留言區(qū)看到你的答案。也歡迎你點(diǎn)擊“請朋友讀”，把今天的內(nèi)容給身邊的朋 不得售賣。頁面已增加防盜追蹤，將依法其上一 33|MySQL調(diào)優(yōu)之事務(wù)：高并發(fā)場景下的數(shù)據(jù)庫事務(wù)調(diào)言言5分之一會全表掃描，可以使用index（索引名）強(qiáng)制使用該索引，這就是有些sql表作者回復(fù):因?yàn)閞riorridor_idi 1 21我的想法是，可以利用子查詢?nèi)p少回表操作，既然有主鍵自增id，便可以利用聚簇索引的優(yōu)勢來強(qiáng)制走索引。代碼方法如下：select*fromorder_detailwhereidinselectidfromorder_detailwhereorder_idbetween5000and10000)由于是select*操作，所以每條記錄都需進(jìn)行回表，當(dāng)server層分析器發(fā)現(xiàn)between的范圍 我覺得SQLidSELECTFROMWHEREidIN(SELECTidFROMorde