色综合图-色综合图片-色综合图片二区150p-色综合图区-玖玖国产精品视频-玖玖香蕉视频

您的位置:首頁技術文章
文章詳情頁

MySQL 普通索引和唯一索引的區(qū)別詳解

瀏覽:101日期:2023-10-05 11:17:58
1 概念區(qū)分 普通索引和唯一索引

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

主鍵和唯一索引

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

2 案例引入

某居民系統(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的索引組織結構:

MySQL 普通索引和唯一索引的區(qū)別詳解

接下來分析性能。

3 查詢性能

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。

4 更新性能

現(xiàn)在來看往表中插入一個新記錄(4,400),InnoDB會做什么?

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

4.1 在內(nèi)存 唯一索引

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

普通索引

找到3和5之間位置,插入值,語句執(zhí)行結束。

普通索引和唯一索引對更新語句性能影響的差別,只是一個判斷,耗費微小CPU時間。

4.2 不在內(nèi)存 唯一索引

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

普通索引

將更新記錄在change buffer,語句執(zhí)行結束。

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

5 實踐中的索引選擇

普通索引和唯一索引究竟如何抉擇?這兩類索引在查詢性能上沒差別,主要考慮對更新性能影響。所以,推薦盡量選擇普通索引。

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

在使用機械硬盤時,change buffer機制的收效非常顯著。 所以,當你有一個類似“歷史數(shù)據(jù)”的庫,并且出于成本考慮用機械硬盤時,應該關注這些表里的索引,盡量使用普通索引,把change buffer 開大,確保“歷史數(shù)據(jù)”表的數(shù)據(jù)寫速度。

6 change buffer 和 redo log

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

6.1 插入流程

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的更新流程圖,圖中兩個箭頭都是后臺操作,不影響更新響應。

MySQL 普通索引和唯一索引的區(qū)別詳解

該更新做了如下操作:

Page1在內(nèi)存,直接更新內(nèi)存 Page2不在內(nèi)存,就在change buffer區(qū),緩存下“往Page2插一行記錄”的信息 將前兩個動作記入redo log

之后事務完成。執(zhí)行該更新語句成本很低,只寫兩處內(nèi)存,然后寫一處磁盤(前兩次操作合在一起寫了一次磁盤),還是順序?qū)憽?/p>6.2 怎么處理之后的讀請求?

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

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

帶change buffer的讀過程

MySQL 普通索引和唯一索引的區(qū)別詳解

讀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消耗(轉成順序?qū)懀? change buffer主要節(jié)省隨機讀磁盤的IO消耗 7 總結

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

7.1 關于到底是否使用唯一索引

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

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

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

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

7.3 merge的過程是否會把數(shù)據(jù)直接寫回磁盤?merge執(zhí)行流程 從磁盤讀入數(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)容,更多關于MySQL 普通索引和唯一索引的資料請關注好吧啦網(wǎng)其它相關文章!

相關文章:
主站蜘蛛池模板: 一级毛片免费观看不卡的 | 一区二区三区亚洲视频 | 欧美午夜视频一区二区三区 | 妖精www成人动漫在线观看 | 精品久久久久久无码中文字幕 | 亚洲综合射 | 日本一级特黄啪啪片 | 国产一区二区亚洲精品天堂 | 国产小网站 | 亚洲一在线 | 亚洲三级精品 | 男人的天堂在线免费视频 | 99精品免费久久久久久久久日本 | 中文字幕第9页 | 国产精品久久免费视频 | 免费看成人毛片 | 国产精品自拍一区 | 免费一级a毛片在线 | 午夜日韩视频 | 成年视频国产免费观看 | 日韩精品网 | 久久er热这里只有精品23 | 99热久久精品国产 | 好湿好紧好痛a级是免费视频 | 免费高清一级欧美片在线观看 | 成人性视频在线三级 | 国产成人精品区在线观看 | 中文字幕成人在线观看 | 日本a级三级三级三级久久 日本a级特黄三级三级三级 | 亚洲第一影院 | 国内精品久久国产大陆 | 日本一区二区高清不卡 | 九草视频 | 欧美一区二区三区免费不卡 | 99综合在线 | 97视频在线免费播放 | 欧美成人一级视频 | 欧美激情精品久久久久久久九九九 | 思思99re | 最新69成人精品毛片 | 亚洲日本高清成人aⅴ片 |