普通索引與索引在MySQL中有什么區(qū)別-創(chuàng)新互聯(lián)

這篇文章給大家介紹普通索引與索引在MySQL 中有什么區(qū)別，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對(duì)大家能有所幫助。

創(chuàng)新互聯(lián)于2013年創(chuàng)立，先為龍圩等服務(wù)建站，龍圩等地企業(yè)，進(jìn)行企業(yè)商務(wù)咨詢服務(wù)。為龍圩企業(yè)網(wǎng)站制作PC+手機(jī)+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問(wèn)題。

1 概念區(qū)分

• 普通索引和索引

  
  

普通索引可重復(fù)，索引和主鍵一樣不能重復(fù)。 索引可作為數(shù)據(jù)的一個(gè)合法驗(yàn)證手段，例如學(xué)生表的身份證號(hào)碼字段，我們?nèi)藶橐?guī)定該字段不得重復(fù)，那么就使用索引。（一般設(shè)置學(xué)號(hào)字段為主鍵）

• 主鍵和索引

  
  

主鍵保證數(shù)據(jù)庫(kù)里面的每一行都是的，比如身份證，學(xué)號(hào)等，在表中要求，不重復(fù)。索引的作用跟主鍵的作用一樣。 不同的是，在一張表里面只能有一個(gè)主鍵，主鍵不能為空，索引可以有多個(gè)，索引可以有一條記錄為空，即保證跟別人不一樣就行。 比如學(xué)生表，在學(xué)校里面一般用學(xué)號(hào)做主鍵，身份證則弄成索引；而到了教育局，他們就把身份證號(hào)弄成主鍵，學(xué)號(hào)換成了索引。 選誰(shuí)做表的主鍵，要看實(shí)際應(yīng)用，主鍵不能為空。

2 案例引入

某居民系統(tǒng)，每人有身份證號(hào)。如果系統(tǒng)需要按身份證號(hào)查姓名，就會(huì)執(zhí)行類似如下SQL：

select name from CUser where id_card = 'ooxx';

然后你肯定會(huì)在id_card字段建索引。但id_card字段較大，不推薦將其做主鍵。于是現(xiàn)有倆選擇：

1. 給id_card字段創(chuàng)建索引

2. 創(chuàng)建一個(gè)普通索引

   
   

假定業(yè)務(wù)代碼已保證不會(huì)寫入重復(fù)的身份證號(hào)，這兩個(gè)選擇邏輯上都正確。但從性能角度考慮，索引還是普通索引呢？

再看如下案例：假設(shè)字段 k 上的值都不重復(fù)。

• InnoDB的索引組織結(jié)構(gòu)：

接下來(lái)分析性能。

3 查詢性能

select id from T where k=4

通過(guò)B+樹從樹根開始層序遍歷到葉節(jié)點(diǎn)，可認(rèn)為數(shù)據(jù)頁(yè)內(nèi)部是通過(guò)二分法搜索。

• 普通索引，查找到滿足條件的第一個(gè)記錄(4,400)后，需查找下個(gè)記錄，直到碰到第一個(gè)不滿足k=4的記錄

• 索引，由于索引具備性，查找到第一個(gè)滿足條件的記錄后，就會(huì)停止檢索

  
  

看起來(lái)性能差距很微小。

InnoDB數(shù)據(jù)按數(shù)據(jù)頁(yè)單位讀寫。即讀一條記錄時(shí)，并非將該一個(gè)記錄從磁盤讀出，而以頁(yè)為單位，將其整體讀入內(nèi)存。

因此普通索引，要多做一次“查找和判斷下一條記錄”的操作，也就一次指針尋找和一次計(jì)算。 如果k=4記錄恰為該數(shù)據(jù)頁(yè)最后一個(gè)記錄，那么要取下個(gè)記錄，還得讀取下個(gè)數(shù)據(jù)頁(yè)，操作稍微復(fù)雜。 對(duì)整型字段，一個(gè)數(shù)據(jù)頁(yè)可存近千key，因此這種情況概率其實(shí)也很低。因此計(jì)算平均性能差異時(shí)，可認(rèn)為該操作成本對(duì)現(xiàn)在CPU開銷忽略不計(jì)。

我們知道 MySQL 有 change buffer。

4 更新性能

現(xiàn)在來(lái)看往表中插入一個(gè)新記錄(4,400)，InnoDB會(huì)做什么？

需要區(qū)分該記錄要更新的目標(biāo)頁(yè)是否在內(nèi)存：

4.1 在內(nèi)存

• 索引

  
  

找到3和5之間位置，判斷到?jīng)]有沖突，插入值，語(yǔ)句執(zhí)行結(jié)束。

• 普通索引

  
  

找到3和5之間位置，插入值，語(yǔ)句執(zhí)行結(jié)束。

普通索引和索引對(duì)更新語(yǔ)句性能影響的差別，只是一個(gè)判斷，耗費(fèi)微小CPU時(shí)間。

4.2 不在內(nèi)存

• 索引

  
  

需將數(shù)據(jù)頁(yè)讀入內(nèi)存，判斷到?jīng)]有沖突，插入值，語(yǔ)句執(zhí)行結(jié)束。

• 普通索引

  
  

將更新記錄在change buffer，語(yǔ)句執(zhí)行結(jié)束。

將數(shù)據(jù)從磁盤讀入內(nèi)存涉及隨機(jī)IO訪問(wèn)，是數(shù)據(jù)庫(kù)里面成本高操作之一。而change buffer減少隨機(jī)磁盤訪問(wèn)，所以更新性能提升明顯。

5 實(shí)踐中的索引選擇

普通索引和索引究竟如何抉擇？這兩類索引在查詢性能上沒(méi)差別，主要考慮對(duì)更新性能影響。所以，推薦盡量選擇普通索引。

如果所有更新后面，都緊跟對(duì)該記錄的查詢，那么該關(guān)閉change buffer。 而在其他情況下，change buffer都能提升更新性能。 普通索引和change buffer的配合使用，對(duì)于數(shù)據(jù)量大的表的更新優(yōu)化還是很明顯的。

在使用機(jī)械硬盤時(shí)，change buffer機(jī)制的收效非常顯著。 所以，當(dāng)你有一個(gè)類似“歷史數(shù)據(jù)”的庫(kù)，并且出于成本考慮用機(jī)械硬盤時(shí)，應(yīng)該關(guān)注這些表里的索引，盡量使用普通索引，把change buffer 開大，確?！皻v史數(shù)據(jù)”表的數(shù)據(jù)寫速度。

6 change buffer 和 redo log

WAL 提升性能的核心機(jī)制，也是盡量減少隨機(jī)讀寫，這兩個(gè)概念易混淆。 所以，這里我把它們放到了同一個(gè)流程里來(lái)說(shuō)明區(qū)分。

6.1 插入流程

insert into t(id,k) values(id1,k1),(id2,k2);

假設(shè)當(dāng)前k索引樹的狀態(tài)，查找到位置后，k1所在數(shù)據(jù)頁(yè)在內(nèi)存(InnoDB buffer pool)，k2數(shù)據(jù)頁(yè)不在內(nèi)存。

• 帶change buffer的更新流程圖，圖中兩個(gè)箭頭都是后臺(tái)操作，不影響更新響應(yīng)。

該更新做了如下操作：

1. Page1在內(nèi)存，直接更新內(nèi)存

2. Page2不在內(nèi)存，就在change buffer區(qū)，緩存下“往Page2插一行記錄”的信息

3. 將前兩個(gè)動(dòng)作記入redo log

   
   

之后事務(wù)完成。執(zhí)行該更新語(yǔ)句成本很低，只寫兩處內(nèi)存，然后寫一處磁盤（前兩次操作合在一起寫了一次磁盤），還是順序?qū)憽?/p>

6.2 怎么處理之后的讀請(qǐng)求？

select * from t where k in (k1, k2);

讀語(yǔ)句緊隨更新語(yǔ)句，內(nèi)存中的數(shù)據(jù)都還在，此時(shí)這倆讀操作就與系統(tǒng)表空間和 redo log 無(wú)關(guān)。所以在圖中就沒(méi)畫這倆。

• 帶change buffer的讀過(guò)程

讀Page1時(shí)，直接從內(nèi)存返回。 WAL之后如果讀數(shù)據(jù)，是不是一定要讀盤，是不是一定要從redo log里面把數(shù)據(jù)更新以后才可以返回？其實(shí)不用。 看上圖狀態(tài)，雖然磁盤上還是之前數(shù)據(jù)，但這里直接從內(nèi)存返回結(jié)果，結(jié)果正確。

要讀Page2時(shí)，需把Page2從磁盤讀入內(nèi)存，然后應(yīng)用change buffer里面的操作日志，生成一個(gè)正確版本并返回結(jié)果。 可見直到需讀Page2時(shí)，該數(shù)據(jù)頁(yè)才被讀入內(nèi)存。

所以，要簡(jiǎn)單對(duì)比這倆機(jī)制對(duì)更新性能影響

• redo log 主要節(jié)省隨機(jī)寫磁盤的IO消耗（轉(zhuǎn)成順序?qū)懀?/p>

• change buffer主要節(jié)省隨機(jī)讀磁盤的IO消耗

  
  

7 總結(jié)

由于索引用不了change buffer的優(yōu)化機(jī)制，因此如果業(yè)務(wù)可以接受，從性能角度，推薦優(yōu)先考慮非索引。

7.1 關(guān)于到底是否使用索引

主要糾結(jié)在“業(yè)務(wù)可能無(wú)法確?！?。本文前提是“業(yè)務(wù)代碼已經(jīng)保證不會(huì)寫入重復(fù)數(shù)據(jù)”下，討論性能問(wèn)題。

如果業(yè)務(wù)不能保證，或者業(yè)務(wù)就是要求數(shù)據(jù)庫(kù)來(lái)做約束，那么沒(méi)得選，必須創(chuàng)建索引。這種情況下，本文意義在于，如果碰上大量插入數(shù)據(jù)慢、內(nèi)存命中率低時(shí)，多提供一個(gè)排查思路。
然后，在一些“歸檔庫(kù)”的場(chǎng)景，可考慮使用索引的。比如，線上數(shù)據(jù)只需保留半年，然后歷史數(shù)據(jù)保存在歸檔庫(kù)。此時(shí)，歸檔數(shù)據(jù)已是確保沒(méi)有鍵沖突。要提高歸檔效率，可考慮把表的索引改普通索引。

7.2 如果某次寫入使用change buffer，之后主機(jī)異常重啟，是否會(huì)丟失change buffer的數(shù)據(jù)？

不會(huì)丟失。 雖然是只更新內(nèi)存，但在事務(wù)提交時(shí)，我們把change buffer的操作也記錄到redo log，所以崩潰恢復(fù)時(shí)，change buffer也能找回。

7.3 merge的過(guò)程是否會(huì)把數(shù)據(jù)直接寫回磁盤？

merge執(zhí)行流程

1. 從磁盤讀入數(shù)據(jù)頁(yè)到內(nèi)存（老版本數(shù)據(jù)頁(yè)）

2. 從change buffer找出該數(shù)據(jù)頁(yè)的change buffer 記錄(可能有多個(gè)），依次應(yīng)用，得到新版數(shù)據(jù)頁(yè)

3. 寫redo log

   
   

該redo log包含數(shù)據(jù)的變更和change buffer的變更

至此merge過(guò)程結(jié)束。 這時(shí)，數(shù)據(jù)頁(yè)和內(nèi)存中change buffer對(duì)應(yīng)磁盤位置都尚未修改，是臟頁(yè)，之后各自刷回自己物理數(shù)據(jù)，就是另外一過(guò)程。

問(wèn)題思考

在構(gòu)造第一個(gè)例子的過(guò)程，通過(guò)session A的配合，讓session B刪除數(shù)據(jù)后又重新插入一遍數(shù)據(jù)，然后就發(fā)現(xiàn)explain結(jié)果中，rows字段從10001變成37000多。 而如果沒(méi)有session A的配合，只是單獨(dú)執(zhí)行delete from t 、call idata()、explain這三句話，會(huì)看到rows字段其實(shí)還是10000左右。這是什么原因呢？

如果沒(méi)有復(fù)現(xiàn)，檢查

• 隔離級(jí)別是不是RR（Repeatable Read，可重復(fù)讀）

• 創(chuàng)建的表t是不是InnoDB引擎

  
  

為什么經(jīng)過(guò)這個(gè)操作序列，explain的結(jié)果就不對(duì)了？ delete 語(yǔ)句刪掉了所有的數(shù)據(jù)，然后再通過(guò)call idata()插入了10萬(wàn)行數(shù)據(jù)，看上去是覆蓋了原來(lái)10萬(wàn)行。 但是，session A開啟了事務(wù)并沒(méi)有提交，所以之前插入的10萬(wàn)行數(shù)據(jù)是不能刪除的。這樣，之前的數(shù)據(jù)每行數(shù)據(jù)都有兩個(gè)版本，舊版本是delete之前數(shù)據(jù)，新版本是標(biāo)記deleted的數(shù)據(jù)。 這樣，索引a上的數(shù)據(jù)其實(shí)有兩份。

然后你會(huì)說(shuō)，不對(duì)啊，主鍵上的數(shù)據(jù)也不能刪，那沒(méi)有使用force index的語(yǔ)句，使用explain命令看到的掃描行數(shù)為什么還是100000左右？（潛臺(tái)詞，如果這個(gè)也翻倍，也許優(yōu)化器還會(huì)認(rèn)為選字段a作為索引更合適） 是的，不過(guò)這個(gè)是主鍵，主鍵是直接按照表的行數(shù)來(lái)估計(jì)的。而表的行數(shù)，優(yōu)化器直接用的是show table status的值。 大家的機(jī)器如果IO能力比較差的話，做這個(gè)驗(yàn)證的時(shí)候，可以把innodb_flush_log_at_trx_commit和sync_binlog都設(shè)置成0。

關(guān)于普通索引與索引在MySQL 中有什么區(qū)別就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到。

分享標(biāo)題：普通索引與索引在MySQL中有什么區(qū)別-創(chuàng)新互聯(lián)
路徑分享：http://muchs.cn/article32/higpc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)頁(yè)設(shè)計(jì)公司、小程序開發(fā)、服務(wù)器托管、App設(shè)計(jì)、全網(wǎng)營(yíng)銷推廣、移動(dòng)網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)