不用索引
數(shù)據(jù)量比較小時(shí)，由于數(shù)據(jù)較小，查詢花費(fèi)的時(shí)間可能比遍歷索引的時(shí)間還要短，索引可能不會(huì)產(chǎn)生優(yōu)化效果
用索引
數(shù)據(jù)量比較大的情況
怎么用索引
- 唯一值的字段，建立唯一索引（其實(shí)并不推薦在數(shù)據(jù)庫級(jí)別做唯一性限制，可以將唯一性限制放在業(yè)務(wù)層去處理）
- 常用排序字段，建立索引，可以有效地避免排序操作
- 常作為查詢條件的字段，建立索引，可以提高整個(gè)表的查詢速度
- 選值類型較小的字段做索引。如果索引的值很長，那么查詢的速度會(huì)受到影響。例如，對(duì)一個(gè) CHAR(100) 類型的字段進(jìn)行全文檢索需要的時(shí)間肯定要比對(duì) CHAR(10) 類型的字段需要的時(shí)間要多。
索引增減
索引的數(shù)目不是越多越好。每個(gè)索引都需要占用磁盤空間，索引越多，需要的磁盤空間就越大。在修改表的內(nèi)容時(shí)，索引必須進(jìn)行更新，有時(shí)還可能需要重構(gòu)。因此，索引越多，更新表的時(shí)間就越長。
數(shù)據(jù)的使用方式被改變后，原有的一些索引可能不再需要。應(yīng)該定期找出這些索引，將它們刪除，從而減少索引對(duì)更新操作的影響。
一個(gè)索引很少利用或從不使用，那么會(huì)不必要地減緩表的修改速度。此外，MySQL 在生成一個(gè)執(zhí)行計(jì)劃時(shí)，要考慮各個(gè)索引，這也要花費(fèi)時(shí)間。創(chuàng)建多余的索引給查詢優(yōu)化帶來了更多的工作。索引太多，也可能會(huì)使 MySQL 選擇不到所要使用的最佳索引。
唯一索引比普通索引快？
查詢
對(duì)于普通索引來說，查找到滿足條件的第一個(gè)記錄后，需要查找下一個(gè)記錄，直到碰到第一個(gè)不滿足條件的記錄。
對(duì)于唯一索引來說，由于索引定義了唯一性，查找到第一個(gè)滿足條件的記錄后，就會(huì)停止繼續(xù)檢索
由于少了一個(gè)步驟，所以感覺用唯一性索引會(huì)快一些。但是這個(gè)不同帶來的性能差距微乎其微
為什么查詢性能微乎其微？
InnoDB 的數(shù)據(jù)是按數(shù)據(jù)頁為單位來讀寫的。也就是說，當(dāng)需要讀一條記錄的時(shí)候，并不是將這個(gè)記錄本身從磁盤讀出來，而是以頁為單位，將其整體讀入內(nèi)存。在 InnoDB 中，每個(gè)數(shù)據(jù)頁的大小默認(rèn)是 16KB。
因?yàn)橐媸前错撟x寫的，所以說，當(dāng)找到符合條件的記錄的時(shí)候，它所在的數(shù)據(jù)頁就都在內(nèi)存里了。那么，對(duì)于普通索引來說，要多做的那一次“查找和判斷下一條記錄”的操作，就只需要一次指針尋找和一次計(jì)算。
當(dāng)然也會(huì)有特殊情況，就是符合條件的記錄正好處于數(shù)據(jù)頁的最后一個(gè)，那往下查找的操作就會(huì)拿下一個(gè)數(shù)據(jù)頁放進(jìn)內(nèi)存，這個(gè)時(shí)候就會(huì)慢了，但是一個(gè)整型字段，一個(gè)數(shù)據(jù)頁可以放進(jìn)千的key，所以這個(gè)概率很低
更新
幾個(gè)概念
buffer pool： Innodb維護(hù)了一個(gè)緩存區(qū)域叫做Buffer Pool，用來緩存數(shù)據(jù)和索引在內(nèi)存中。Buffer Pool可以用來加速數(shù)據(jù)的讀寫，如果Buffer Pool越大，那么Mysql就越像一個(gè)內(nèi)存數(shù)據(jù)庫，所以了解Buffer Pool的配置可以提高Buffer Pool的性能
change buffer：的是 buffer pool 里的內(nèi)存，因此不能無限增大。change buffer 的大小，可以通過參數(shù) innodb_change_buffer_max_size 來動(dòng)態(tài)設(shè)置。這個(gè)參數(shù)設(shè)置為 50 的時(shí)候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%
merge：將 change buffer 中的操作應(yīng)用到原數(shù)據(jù)頁，得到最新結(jié)果的過程稱為 merge。除了訪問這個(gè)數(shù)據(jù)頁會(huì)觸發(fā) merge 外，系統(tǒng)有后臺(tái)線程會(huì)定期 merge。在數(shù)據(jù)庫正常關(guān)閉（shutdown）的過程中，也會(huì)執(zhí)行 merge 操作。
InnoDB更新數(shù)據(jù)的情況
- 數(shù)據(jù)頁在內(nèi)存中，直接更新
- 數(shù)據(jù)頁不在內(nèi)存中，InnoDB將更新操作緩存在change buffer中，在下次訪問那條記錄的時(shí)候，將那條記錄所在的數(shù)據(jù)頁放入內(nèi)存中，然后執(zhí)行change buffer中記錄下來的操作。即，并沒有立馬更新，而是在后續(xù)查詢這個(gè)數(shù)據(jù)頁的時(shí)候再做的更新操作。
所以，普通索引會(huì)用到 change buffer。
具體流程
如果要在這張表中插入一個(gè) id=5的新紀(jì)錄，InnoDB 的處理流程是怎樣的。
第一種情況：目標(biāo)數(shù)據(jù)頁在內(nèi)存中
唯一索引：找到 4 和 6 之間的位置，判斷到?jīng)]有沖突，插入這個(gè)值，語句執(zhí)行結(jié)束；
普通索引：找到 4 和 6 之間的位置，插入這個(gè)值，語句執(zhí)行結(jié)束。
這樣看來，普通索引和唯一索引對(duì)更新語句性能影響的差別，只是一個(gè)判斷，只會(huì)耗費(fèi)微小的 CPU 時(shí)間。
第二種情況是：目標(biāo)數(shù)據(jù)頁不在內(nèi)存中
唯一索引：需要將數(shù)據(jù)頁讀入內(nèi)存，判斷到?jīng)]有沖突，插入這個(gè)值，語句執(zhí)行結(jié)束；
普通索引：則是將更新記錄在 change buffer，語句執(zhí)行就結(jié)束了。
主要區(qū)別就是唯一索引需要把磁盤中的數(shù)據(jù)頁放入內(nèi)存。就是這步影響了性能。
將數(shù)據(jù)從磁盤讀入內(nèi)存涉及隨機(jī) IO 的訪問，是數(shù)據(jù)庫里面成本最高的操作之一。change buffer 因?yàn)闇p少了隨機(jī)磁盤訪問，所以對(duì)更新性能的提升是會(huì)很明顯的。
應(yīng)用場景
因?yàn)?merge 的時(shí)候是真正進(jìn)行數(shù)據(jù)更新的時(shí)刻，而 change buffer 的主要目的就是將記錄的變更動(dòng)作緩存下來，所以在一個(gè)數(shù)據(jù)頁做merge 之前，change buffer 記錄的變更越多（也就是這個(gè)頁面上要更新的次數(shù)越多），收益就越大。
由此看來就是對(duì)于寫多讀少的業(yè)務(wù)來說，頁面在寫完以后馬上被訪問到的概率比較小，此時(shí) change buffer 的使用效果最好。這種業(yè)務(wù)模型常見的就是賬單類、日志類的系統(tǒng)。
所以反過來，假設(shè)一個(gè)業(yè)務(wù)的更新模式是寫入之后馬上會(huì)做查詢，那么即使?jié)M足了條件，將更新先記錄在 change buffer，但之后由于馬上要訪問這個(gè)數(shù)據(jù)頁，會(huì)立即觸發(fā) merge 過程。這樣隨機(jī)訪問 IO 的次數(shù)不會(huì)減少，反而增加了 change buffer 的維護(hù)代價(jià)。所以，對(duì)于這種業(yè)務(wù)模式來說，change buffer 反而起到了副作用。
總結(jié)
普通索引和唯一索引，在查詢能力上基本是無差別的，而對(duì)于更新操作，由于change buffer的作用，所以還是盡量選擇普通索引。
在實(shí)際使用中，普通索引和 change buffer 的配合使用，對(duì)于數(shù)據(jù)量大的表的更新優(yōu)化還是很明顯的。特別地，在使用機(jī)械硬盤時(shí)，change buffer 這個(gè)機(jī)制的收效是非常顯著的。所以，當(dāng)你有一個(gè)類似“歷史數(shù)據(jù)”的庫，并且出于成本考慮用的是機(jī)械硬盤時(shí)，那你應(yīng)該特別關(guān)注這些表里的索引，盡量使用普通索引，然后把 change buffer 盡量開大，以確保這個(gè)“歷史數(shù)據(jù)”表的數(shù)據(jù)寫入速度。這時(shí)候，歸檔數(shù)據(jù)已經(jīng)是確保沒有唯一鍵沖突了。要提高歸檔效率，可以考慮把表里面的唯一索引改成普通索引。
唯一索引使用的問題，主要是糾結(jié)在“業(yè)務(wù)可能無法確保”的情況。
首先，業(yè)務(wù)正確性優(yōu)先。如果業(yè)務(wù)不能保證數(shù)據(jù)的唯一性，或者業(yè)務(wù)就是要求數(shù)據(jù)庫來做約束，那么沒得選，必須創(chuàng)建唯一索引，否則，唯一索引并不推薦。
