电脑知识|欧美黑人一区二区三区|软件|欧美黑人一级爽快片淫片高清|系统|欧美黑人狂野猛交老妇|数据库|服务器|编程开发|网络运营|知识问答|技术教程文章 - 好吧啦网

您的位置:首頁(yè)技術(shù)文章
文章詳情頁(yè)

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

瀏覽:127日期:2023-10-05 11:17:58
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)有倆選擇:

給id_card字段創(chuàng)建唯一索引 創(chuàng)建一個(gè)普通索引

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

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

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

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

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

3 查詢性能

select id from T where k=4

通過(guò)B+樹(shù)從樹(shù)根開(kāi)始層序遍歷到葉節(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è)單位讀寫(xiě)。即讀一條記錄時(shí),并非將該一個(gè)記錄從磁盤(pán)讀出,而以頁(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開(kāi)銷(xiāo)忽略不計(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ù)從磁盤(pán)讀入內(nèi)存涉及隨機(jī)IO訪問(wèn),是數(shù)據(jù)庫(kù)里面成本最高操作之一。而change buffer減少隨機(jī)磁盤(pán)訪問(wèn),所以更新性能提升明顯。

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

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

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

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

6 change buffer 和 redo log

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

6.1 插入流程

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

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

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

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

該更新做了如下操作:

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

之后事務(wù)完成。執(zhí)行該更新語(yǔ)句成本很低,只寫(xiě)兩處內(nèi)存,然后寫(xiě)一處磁盤(pán)(前兩次操作合在一起寫(xiě)了一次磁盤(pán)),還是順序?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)畫(huà)這倆。

帶change buffer的讀過(guò)程

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

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

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

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

redo log 主要節(jié)省隨機(jī)寫(xiě)磁盤(pán)的IO消耗(轉(zhuǎn)成順序?qū)懀? change buffer主要節(jié)省隨機(jī)讀磁盤(pán)的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ì)寫(xiě)入重復(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 如果某次寫(xiě)入使用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ù)直接寫(xiě)回磁盤(pán)?merge執(zhí)行流程 從磁盤(pán)讀入數(shù)據(jù)頁(yè)到內(nèi)存(老版本數(shù)據(jù)頁(yè)) 從change buffer找出該數(shù)據(jù)頁(yè)的change buffer 記錄(可能有多個(gè)),依次應(yīng)用,得到新版數(shù)據(jù)頁(yè) 寫(xiě)redo log

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

至此merge過(guò)程結(jié)束。 這時(shí),數(shù)據(jù)頁(yè)和內(nèi)存中change buffer對(duì)應(yīng)磁盤(pán)位置都尚未修改,是臟頁(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開(kāi)啟了事務(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。

以上就是MySQL 普通索引和唯一索引的區(qū)別詳解的詳細(xì)內(nèi)容,更多關(guān)于MySQL 普通索引和唯一索引的資料請(qǐng)關(guān)注好吧啦網(wǎng)其它相關(guān)文章!

標(biāo)簽: MySQL 數(shù)據(jù)庫(kù)
相關(guān)文章:
主站蜘蛛池模板: 假肢-假肢价格-假肢厂家-河南假肢-郑州市力康假肢矫形器有限公司 | 热缩管切管机-超声波切带机-织带切带机-无纺布切布机-深圳市宸兴业科技有限公司 | 坏男孩影院-提供最新电影_动漫_综艺_电视剧_迅雷免费电影最新观看 | 桂林腻子粉_内墙外墙抗裂砂浆腻子粉推荐广西鑫达涂料厂家供应 | 大巴租车平台承接包车,通勤班车,巴士租赁业务 - 鸿鸣巴士 | 冷库安装厂家_杭州冷库_保鲜库建设-浙江克冷制冷设备有限公司 | 钢衬四氟管道_钢衬四氟直管_聚四氟乙烯衬里管件_聚四氟乙烯衬里管道-沧州汇霖管道科技有限公司 | 冷柜风机-冰柜电机-罩极电机-外转子风机-EC直流电机厂家-杭州金久电器有限公司 | 蔡司三坐标-影像测量机-3D扫描仪-蔡司显微镜-扫描电镜-工业CT-ZEISS授权代理商三本工业测量 | 奥因-光触媒除甲醛公司-除甲醛加盟公司十大品牌 | 水篦子|雨篦子|镀锌格栅雨水篦子|不锈钢排水篦子|地下车库水箅子—安平县云航丝网制品厂 | 步进电机_agv电机_伺服马达-伺服轮毂电机-和利时电机 | 多功能干燥机,过滤洗涤干燥三合一设备-无锡市张华医药设备有限公司 | 玄米影院| 万师讲师网-优质讲师培训师供应商,讲师认证,找讲师来万师 | 杭州月嫂技术培训服务公司-催乳师培训中心报名费用-产后康复师培训机构-杭州优贝姆健康管理有限公司 | 阿尔法-MDR2000无转子硫化仪-STM566 SATRA拉力试验机-青岛阿尔法仪器有限公司 | 玻纤土工格栅_钢塑格栅_PP焊接_单双向塑料土工格栅_复合防裂布厂家_山东大庚工程材料科技有限公司 | 塑料薄膜_PP薄膜_聚乙烯薄膜-常州市鑫美新材料包装厂 | 涿州网站建设_网站设计_网站制作_做网站_固安良言多米网络公司 | pbt头梳丝_牙刷丝_尼龙毛刷丝_PP塑料纤维合成毛丝定制厂_广州明旺 | 玄米影院| 成都LED显示屏丨室内户外全彩led屏厂家方案报价_四川诺显科技 | 防堵吹扫装置-防堵风压测量装置-电动操作显示器-兴洲仪器 | 润东方环保空调,冷风机,厂房车间降温设备-20年深圳环保空调生产厂家 | MVE振动电机_MVE震动电机_MVE卧式振打电机-河南新乡德诚生产厂家 | 颚式破碎机,圆锥破碎机,制砂机-新乡市德诚机电制造有限公司 | 石油/泥浆/不锈钢防腐/砂泵/抽砂泵/砂砾泵/吸砂泵/压滤机泵 - 专业石油环保专用泵厂家 | 道康宁消泡剂-瓦克-大川进口消泡剂供应商 | 广州番禺搬家公司_天河黄埔搬家公司_企业工厂搬迁_日式搬家_广州搬家公司_厚道搬迁搬家公司 | 掺铥光纤放大器-C/L波段光纤放大器-小信号光纤放大器-合肥脉锐光电技术有限公司 | 水篦子|雨篦子|镀锌格栅雨水篦子|不锈钢排水篦子|地下车库水箅子—安平县云航丝网制品厂 | 定制异形重型钢格栅板/钢格板_定做踏步板/排水沟盖板_钢格栅板批发厂家-河北圣墨金属制品有限公司 | 优考试_免费在线考试系统_培训考试系统_题库系统_组卷答题系统_匡优考试 | 清水-铝合金-建筑模板厂家-木模板价格-铝模板生产「五棵松」品牌 | 上海阳光泵业制造有限公司 -【官方网站】 | 深圳法律咨询【24小时在线】深圳律师咨询免费 | 建大仁科-温湿度变送器|温湿度传感器|温湿度记录仪_厂家_价格-山东仁科 | 危废处理系统,水泥厂DCS集散控制系统,石灰窑设备自动化控制系统-淄博正展工控设备 | 厦门ISO认证|厦门ISO9001认证|厦门ISO14001认证|厦门ISO45001认证-艾索咨询专注ISO认证行业 | 明渠式紫外线杀菌器-紫外线消毒器厂家-定州市优威环保 |