這篇文章給大家介紹普通索引與索引在MySQL 中有什么區(qū)別，內(nèi)容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。
普通索引和索引
普通索引可重復，索引和主鍵一樣不能重復。 索引可作為數(shù)據(jù)的一個合法驗證手段，例如學生表的身份證號碼字段，我們?nèi)藶橐?guī)定該字段不得重復，那么就使用索引。（一般設置學號字段為主鍵）
主鍵和索引
主鍵保證數(shù)據(jù)庫里面的每一行都是的，比如身份證，學號等，在表中要求，不重復。索引的作用跟主鍵的作用一樣。 不同的是，在一張表里面只能有一個主鍵，主鍵不能為空，索引可以有多個，索引可以有一條記錄為空，即保證跟別人不一樣就行。 比如學生表，在學校里面一般用學號做主鍵，身份證則弄成索引；而到了教育局，他們就把身份證號弄成主鍵，學號換成了索引。 選誰做表的主鍵，要看實際應用，主鍵不能為空。
某居民系統(tǒng)，每人有身份證號。如果系統(tǒng)需要按身份證號查姓名，就會執(zhí)行類似如下SQL：
select name from CUser where id_card = 'ooxx';
然后你肯定會在id_card字段建索引。但id_card字段較大，不推薦將其做主鍵。于是現(xiàn)有倆選擇：
給id_card字段創(chuàng)建索引
創(chuàng)建一個普通索引
假定業(yè)務代碼已保證不會寫入重復的身份證號，這兩個選擇邏輯上都正確。但從性能角度考慮，索引還是普通索引呢？
再看如下案例：假設字段 k 上的值都不重復。
InnoDB的索引組織結構：
接下來分析性能。
select id from T where k=4
通過B+樹從樹根開始層序遍歷到葉節(jié)點，可認為數(shù)據(jù)頁內(nèi)部是通過二分法搜索。
普通索引，查找到滿足條件的第一個記錄(4,400)后，需查找下個記錄，直到碰到第一個不滿足k=4的記錄
索引，由于索引具備性，查找到第一個滿足條件的記錄后，就會停止檢索
看起來性能差距很微小。
InnoDB數(shù)據(jù)按數(shù)據(jù)頁單位讀寫。即讀一條記錄時，并非將該一個記錄從磁盤讀出，而以頁為單位，將其整體讀入內(nèi)存。
因此普通索引，要多做一次“查找和判斷下一條記錄”的操作，也就一次指針尋找和一次計算。 如果k=4記錄恰為該數(shù)據(jù)頁最后一個記錄，那么要取下個記錄，還得讀取下個數(shù)據(jù)頁，操作稍微復雜。 對整型字段，一個數(shù)據(jù)頁可存近千key，因此這種情況概率其實也很低。因此計算平均性能差異時，可認為該操作成本對現(xiàn)在CPU開銷忽略不計。
我們知道 MySQL 有 change buffer。
現(xiàn)在來看往表中插入一個新記錄(4,400)，InnoDB會做什么？
需要區(qū)分該記錄要更新的目標頁是否在內(nèi)存：
索引
找到3和5之間位置，判斷到?jīng)]有沖突，插入值，語句執(zhí)行結束。
普通索引
找到3和5之間位置，插入值，語句執(zhí)行結束。
普通索引和索引對更新語句性能影響的差別，只是一個判斷，耗費微小CPU時間。
索引
需將數(shù)據(jù)頁讀入內(nèi)存，判斷到?jīng)]有沖突，插入值，語句執(zhí)行結束。
普通索引
將更新記錄在change buffer，語句執(zhí)行結束。
將數(shù)據(jù)從磁盤讀入內(nèi)存涉及隨機IO訪問，是數(shù)據(jù)庫里面成本高操作之一。而change buffer減少隨機磁盤訪問，所以更新性能提升明顯。
普通索引和索引究竟如何抉擇？這兩類索引在查詢性能上沒差別，主要考慮對更新性能影響。所以，推薦盡量選擇普通索引。
如果所有更新后面，都緊跟對該記錄的查詢，那么該關閉change buffer。 而在其他情況下，change buffer都能提升更新性能。 普通索引和change buffer的配合使用，對于數(shù)據(jù)量大的表的更新優(yōu)化還是很明顯的。
在使用機械硬盤時，change buffer機制的收效非常顯著。 所以，當你有一個類似“歷史數(shù)據(jù)”的庫，并且出于成本考慮用機械硬盤時，應該關注這些表里的索引，盡量使用普通索引，把change buffer 開大，確?！皻v史數(shù)據(jù)”表的數(shù)據(jù)寫速度。
WAL 提升性能的核心機制，也是盡量減少隨機讀寫，這兩個概念易混淆。 所以，這里我把它們放到了同一個流程里來說明區(qū)分。
insert into t(id,k) values(id1,k1),(id2,k2);
假設當前k索引樹的狀態(tài)，查找到位置后，k1所在數(shù)據(jù)頁在內(nèi)存(InnoDB buffer pool)，k2數(shù)據(jù)頁不在內(nèi)存。
帶change buffer的更新流程圖，圖中兩個箭頭都是后臺操作，不影響更新響應。
該更新做了如下操作：
Page1在內(nèi)存，直接更新內(nèi)存
Page2不在內(nèi)存，就在change buffer區(qū)，緩存下“往Page2插一行記錄”的信息
將前兩個動作記入redo log
之后事務完成。執(zhí)行該更新語句成本很低，只寫兩處內(nèi)存，然后寫一處磁盤（前兩次操作合在一起寫了一次磁盤），還是順序?qū)憽?/p>
select * from t where k in (k1, k2);
讀語句緊隨更新語句，內(nèi)存中的數(shù)據(jù)都還在，此時這倆讀操作就與系統(tǒng)表空間和 redo log 無關。所以在圖中就沒畫這倆。
帶change buffer的讀過程
讀Page1時，直接從內(nèi)存返回。 WAL之后如果讀數(shù)據(jù)，是不是一定要讀盤，是不是一定要從redo log里面把數(shù)據(jù)更新以后才可以返回？其實不用。 看上圖狀態(tài)，雖然磁盤上還是之前數(shù)據(jù)，但這里直接從內(nèi)存返回結果，結果正確。
要讀Page2時，需把Page2從磁盤讀入內(nèi)存，然后應用change buffer里面的操作日志，生成一個正確版本并返回結果。 可見直到需讀Page2時，該數(shù)據(jù)頁才被讀入內(nèi)存。
所以，要簡單對比這倆機制對更新性能影響
redo log 主要節(jié)省隨機寫磁盤的IO消耗（轉(zhuǎn)成順序?qū)懀?/p>
change buffer主要節(jié)省隨機讀磁盤的IO消耗
由于索引用不了change buffer的優(yōu)化機制，因此如果業(yè)務可以接受，從性能角度，推薦優(yōu)先考慮非索引。
主要糾結在“業(yè)務可能無法確?！薄１疚那疤崾恰皹I(yè)務代碼已經(jīng)保證不會寫入重復數(shù)據(jù)”下，討論性能問題。
如果業(yè)務不能保證，或者業(yè)務就是要求數(shù)據(jù)庫來做約束，那么沒得選，必須創(chuàng)建索引。這種情況下，本文意義在于，如果碰上大量插入數(shù)據(jù)慢、內(nèi)存命中率低時，多提供一個排查思路。
然后，在一些“歸檔庫”的場景，可考慮使用索引的。比如，線上數(shù)據(jù)只需保留半年，然后歷史數(shù)據(jù)保存在歸檔庫。此時，歸檔數(shù)據(jù)已是確保沒有鍵沖突。要提高歸檔效率，可考慮把表的索引改普通索引。
不會丟失。 雖然是只更新內(nèi)存，但在事務提交時，我們把change buffer的操作也記錄到redo log，所以崩潰恢復時，change buffer也能找回。
從磁盤讀入數(shù)據(jù)頁到內(nèi)存（老版本數(shù)據(jù)頁）
從change buffer找出該數(shù)據(jù)頁的change buffer 記錄(可能有多個），依次應用，得到新版數(shù)據(jù)頁
寫redo log
該redo log包含數(shù)據(jù)的變更和change buffer的變更
至此merge過程結束。 這時，數(shù)據(jù)頁和內(nèi)存中change buffer對應磁盤位置都尚未修改，是臟頁，之后各自刷回自己物理數(shù)據(jù)，就是另外一過程。
在構造第一個例子的過程，通過session A的配合，讓session B刪除數(shù)據(jù)后又重新插入一遍數(shù)據(jù)，然后就發(fā)現(xiàn)explain結果中，rows字段從10001變成37000多。 而如果沒有session A的配合，只是單獨執(zhí)行delete from t 、call idata()、explain這三句話，會看到rows字段其實還是10000左右。這是什么原因呢？
如果沒有復現(xiàn)，檢查
隔離級別是不是RR（Repeatable Read，可重復讀）
創(chuàng)建的表t是不是InnoDB引擎
為什么經(jīng)過這個操作序列，explain的結果就不對了？ delete 語句刪掉了所有的數(shù)據(jù)，然后再通過call idata()插入了10萬行數(shù)據(jù)，看上去是覆蓋了原來10萬行。 但是，session A開啟了事務并沒有提交，所以之前插入的10萬行數(shù)據(jù)是不能刪除的。這樣，之前的數(shù)據(jù)每行數(shù)據(jù)都有兩個版本，舊版本是delete之前數(shù)據(jù)，新版本是標記deleted的數(shù)據(jù)。 這樣，索引a上的數(shù)據(jù)其實有兩份。
然后你會說，不對啊，主鍵上的數(shù)據(jù)也不能刪，那沒有使用force index的語句，使用explain命令看到的掃描行數(shù)為什么還是100000左右？（潛臺詞，如果這個也翻倍，也許優(yōu)化器還會認為選字段a作為索引更合適） 是的，不過這個是主鍵，主鍵是直接按照表的行數(shù)來估計的。而表的行數(shù)，優(yōu)化器直接用的是show table status的值。 大家的機器如果IO能力比較差的話，做這個驗證的時候，可以把innodb_flush_log_at_trx_commit和sync_binlog都設置成0。
關于普通索引與索引在MySQL 中有什么區(qū)別就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。
重慶分公司
重慶分公司
