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

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

MySQL 覆蓋索引的優(yōu)點(diǎn)

瀏覽:36日期:2023-10-02 17:37:35

一個(gè)通常的建議是為WHERE條件創(chuàng)建索引,但這其實(shí)是片面的。索引應(yīng)當(dāng)為全部查詢?cè)O(shè)計(jì),而不僅僅是WHERE條件。索引確實(shí)能有效地查找數(shù)據(jù)行,但MySQL也能夠使用索引獲取列數(shù)據(jù),這樣根本不需要去讀取一行數(shù)據(jù)。畢竟,索引的葉子節(jié)點(diǎn)包含了索引對(duì)應(yīng)的值。當(dāng)年能夠讀取索引就能夠拿到想要的數(shù)據(jù)時(shí)為什么還去讀數(shù)據(jù)行呢?當(dāng)索引包含了所有查詢的數(shù)據(jù)時(shí),這個(gè)索引就稱之為覆蓋索引。

覆蓋索引能夠成為一個(gè)非常有力的工具并且能夠顯著改善性能。考慮一下不讀數(shù)據(jù)只需要讀取索引的情況:

索引值通常會(huì)比整個(gè)行存儲(chǔ)空間小很多,因此MySQL只讀取索引值時(shí)可以只讀取很少的數(shù)據(jù)。這對(duì)于緩存負(fù)荷來(lái)說(shuō)十分重要——響應(yīng)時(shí)間大部分消耗在復(fù)制數(shù)據(jù)。對(duì)于磁盤I/O而言也是一樣,因?yàn)樗饕龜?shù)據(jù)相比行數(shù)據(jù)存儲(chǔ)空間小很多,因此更節(jié)省I/O負(fù)載和內(nèi)存占用(對(duì)于MyISAM引擎更顯著,因?yàn)镸yISAM可以將索引打包使得存儲(chǔ)空間更小)。 索引是按索引值的順序存儲(chǔ)的,因此I/O訪問(wèn)跨度相比隨機(jī)磁盤位置獲取行數(shù)據(jù)而言消耗的I/O頻次更少。對(duì)于某些存儲(chǔ)引擎,例如MyISAM和Percona XtraDB,你甚至能夠使用OPTIMIZE優(yōu)化表獲得完全有序的索引,這會(huì)使得簡(jiǎn)單范圍的查詢完全使用順序訪問(wèn)。 有些存儲(chǔ)引擎,例如MyISAM,MySQL內(nèi)存中只緩存索引。由于操作系統(tǒng)為MyISAM緩存了數(shù)據(jù),訪問(wèn)時(shí)通常需要一個(gè)系統(tǒng)調(diào)用。這可能導(dǎo)致巨大的性能影響,尤其是對(duì)于緩存負(fù)荷場(chǎng)景來(lái)說(shuō),系統(tǒng)調(diào)用對(duì)于數(shù)據(jù)訪問(wèn)來(lái)說(shuō)是最昂貴的代價(jià)。 由于InnoDB的聚集索引,覆蓋索引對(duì)于InnoDB來(lái)說(shuō)十分有幫助。InnoDB的輔助索引在其葉子節(jié)點(diǎn)中保存了行的主鍵值。因此,輔助索引覆蓋查詢后可以避免進(jìn)行主鍵查詢。

在所有的場(chǎng)景中,最典型的就是相比查找數(shù)據(jù)行,只包含索引列的查詢的代價(jià)相當(dāng)?shù)汀P枰⒁獾氖牵奂饕⒉皇侨我忸愋偷乃饕>奂饕仨毚鎯?chǔ)索引數(shù)據(jù)列對(duì)應(yīng)的值。哈希,空間和全文索引并沒(méi)有存儲(chǔ)這些值,因此MySQL只能使用二叉樹(shù)去覆蓋查詢。而且,不同的存儲(chǔ)引擎實(shí)現(xiàn)覆蓋索引的方式不同,并且并不是全部的存儲(chǔ)引擎都支持覆蓋索引(例如Memory存儲(chǔ)引擎當(dāng)前就不支持)。

當(dāng)你驗(yàn)證查詢中索引使用了覆蓋索引時(shí),使用Explain語(yǔ)句時(shí),會(huì)在Extra列中看到“Using index”。例如,在store_goods表有一個(gè)(shop_id, goods_category_id1)的多列索引。MySQL可以在查詢返回?cái)?shù)據(jù)只有這兩列時(shí)使用索引:

EXPLAIN SELECT `goods_category_id1`,`shop_id` FROM `store_goods` WHERE 1

MySQL 覆蓋索引的優(yōu)點(diǎn)

覆蓋索引查詢?cè)谀承┣闆r會(huì)讓這樣的優(yōu)化失效。MySQL查詢優(yōu)化器在執(zhí)行查詢時(shí)會(huì)判斷索引是否覆蓋到。假設(shè)索引覆蓋了WHERE條件,但沒(méi)有覆蓋整個(gè)查詢。如果評(píng)估結(jié)果決定不走覆蓋索引,那么MySQL 5.5及以前的版本會(huì)直接獲取數(shù)據(jù)行,即便是不需要這些數(shù)據(jù),然后才會(huì)過(guò)濾掉。

讓我們看一下為什么這種情況會(huì)發(fā)生,然后如何重寫(xiě)查詢以便解決這個(gè)問(wèn)題。首先查詢是這樣的:

EXPLAIN SELECT * FROM products WHERE actor=’SEAN CARREY’ AND title like ’%APOLLO%’

這個(gè)時(shí)候的結(jié)果是不會(huì)走覆蓋索引,而是普通的索引,這是因?yàn)椋?/p> 沒(méi)有索引覆蓋了查詢數(shù)據(jù)列,因?yàn)槲覀儚臄?shù)據(jù)表讀取了全部列并且沒(méi)有索引列覆蓋了全部列。理論上,MySQL還有一個(gè)快捷方式可以使用,那就是WHERE條件中使用了索引覆蓋的列,因此MySQL可以先使用這個(gè)索引找到對(duì)應(yīng)的actor,然后在檢查他們的title是否匹配,然后在讀取滿足條件的全部的數(shù)據(jù)行。 對(duì)于早期的低版本的存儲(chǔ)引擎API(MySQL 5.5以前的版本)來(lái)說(shuō),MySQL無(wú)法在索引中使用LIKE操作,而只支持簡(jiǎn)單的比較操作(=,IN,>=)。MySQL可以在索引中使用前綴匹配的LIKE查詢,這是因?yàn)樗梢詫⑺鼈冝D(zhuǎn)換為比較操作。但是前導(dǎo)通配符(也就是LIKE中前置的%)導(dǎo)致存儲(chǔ)引擎無(wú)法評(píng)估匹配條件。因此,MySQL會(huì)獲取行數(shù)據(jù)再比較,而不是索引的值。

有一種方式可以使用巧妙的組合索引和重寫(xiě)查詢條件。我們可以將索引擴(kuò)展到(artist, title, prod_id),然后像下面那樣重寫(xiě)查詢語(yǔ)句:

EXPLAIN SELECT * FROM productsJOIN ( SELECT prod_id FROM products WHERE actor=’SEAN CARREY’ AND TITLE LIKE ’%APOLLO%’ ) AS t1 ON (t1.prod_id=products.prod_id)

MySQL 覆蓋索引的優(yōu)點(diǎn)

我們稱之為“遞延JOIN(deferred join)”,因?yàn)樗舆t了列的訪問(wèn)。在第一階段的查詢中,當(dāng)它在子查詢中找到了匹配的行的過(guò)程中,MySQL使用了覆蓋索引。雖然在整個(gè)查詢中沒(méi)有覆蓋到,但總比沒(méi)有的好。

這種優(yōu)化的效果好壞取決于WHERE條件查找到了多少行數(shù)據(jù)。假設(shè)products表包含了上百萬(wàn)行的數(shù)據(jù)。可以對(duì)比一下這兩種查詢的性能對(duì)比,總的數(shù)據(jù)為100萬(wàn)行。

第一種情況:有30000個(gè)products的actor是“SEAN CARREY”,其中20000個(gè)的title包含“APOLLO”; 第二種情況:有30000個(gè)products的actor是“SEAN CARREY”,其中40個(gè)的title包含“APOLLO”; 第三種情況:有50個(gè)products的actor是“SEAN CARREY”,其中10個(gè)的title包含“APOLLO”。

對(duì)比結(jié)果如下表。

數(shù)據(jù)集 原始查詢 優(yōu)化后查詢 第一種情況 5qps 5qps 第二種情況 7qps 35qps 第三種情況 2400qps 2000qps

結(jié)果的解釋如下:

在第一種情況中,查詢返回了很大的結(jié)果集,因此看不到優(yōu)化效果。大部分時(shí)間花在了讀取和發(fā)送數(shù)據(jù)。 在第二種情況中,使用覆蓋索引后子查詢過(guò)濾得到了一個(gè)小的結(jié)果集,這樣優(yōu)化的效果是性能提升了5倍。產(chǎn)生這種效果的原因是相比查出30000行的數(shù)據(jù)集,這里只需要讀取40行。 第三種情況顯示了子查詢失效了。覆蓋索引過(guò)濾返回的結(jié)果集太少了,導(dǎo)致子查詢的代價(jià)比直接從數(shù)據(jù)表讀取數(shù)據(jù)還要高。

在大多數(shù)存儲(chǔ)引擎中,一個(gè)索引只能夠覆蓋訪問(wèn)列是索引的一部分。然而,InnoDB實(shí)際上會(huì)做進(jìn)一步的優(yōu)化。想想InnoDB的的輔助索引在葉子節(jié)點(diǎn)中存儲(chǔ)了主鍵的值。這意味著InnoDB的輔助索引實(shí)際上有了額外的列幫助InnoDB使用覆蓋索引。 例如,sakila.actor表使用了InnoDB,然后在last_name有一個(gè)索引,因此這個(gè)索引能夠覆蓋或者主鍵actor_id的查詢——即便這個(gè)列并不是索引的一部分。

EXPLAIN SELECT actor_id, last_nameFROM sakila.actor WHERE last_name = ’HOPPER’

MySQL 覆蓋索引的優(yōu)點(diǎn)

以上就是MySQL 覆蓋索引的優(yōu)點(diǎn)的詳細(xì)內(nèi)容,更多關(guān)于MySQL 覆蓋索引的資料請(qǐng)關(guān)注好吧啦網(wǎng)其它相關(guān)文章!

標(biāo)簽: MySQL 數(shù)據(jù)庫(kù)
相關(guān)文章:
主站蜘蛛池模板: 悬浮拼装地板_篮球场木地板翻新_运动木地板价格-上海越禾运动地板厂家 | 咖啡加盟,咖啡店加盟连锁品牌-卡小逗 | 披萨石_披萨盘_电器家电隔热绵加工定制_佛山市南海区西樵南方综合保温材料厂 | 玻璃钢格栅盖板|玻璃钢盖板|玻璃钢格栅板|树篦子-长沙川皖玻璃钢制品有限公司 | 超声波电磁流量计-液位计-孔板流量计-料位计-江苏信仪自动化仪表有限公司 | 纳米涂料品牌 防雾抗污纳米陶瓷涂料厂家_虹瓷科技 | 牛皮纸|牛卡纸|进口牛皮纸|食品级牛皮纸|牛皮纸厂家-伽立实业 | 上海网站建设-上海网站制作-上海网站设计-上海做网站公司-咏熠软件 | 气体检测仪-氢气检测仪-可燃气体传感器-恶臭电子鼻-深国安电子 | 电缆隧道在线监测-智慧配电站房-升压站在线监测-江苏久创电气科技有限公司 | 烟气换热器_GGH烟气换热器_空气预热器_高温气气换热器-青岛康景辉 | 卫生人才网-中国专业的医疗卫生医学人才网招聘网站! | 钢格板_钢格栅_格栅板_钢格栅板 - 安平县鑫拓钢格栅板厂家 | 气体检测仪-氢气检测仪-可燃气体传感器-恶臭电子鼻-深国安电子 | 青岛美佳乐清洁工程有限公司|青岛油烟管道清洗|酒店|企事业单位|学校工厂厨房|青岛油烟管道清洗 插针变压器-家用电器变压器-工业空调变压器-CD型电抗器-余姚市中驰电器有限公司 | 座椅式升降机_无障碍升降平台_残疾人升降平台-南京明顺机械设备有限公司 | 电主轴-高速精密电主轴-高速电机厂家-瑞德沃斯品牌有限公司 | 全温恒温摇床-水浴气浴恒温摇床-光照恒温培养摇床-常州金坛精达仪器制造有限公司 | 回转炉,外热式回转窑,回转窑炉-淄博圣元窑炉工程有限公司 | 中央空调维修、中央空调保养、螺杆压缩机维修-苏州东菱空调 | 超声波分散机-均质机-萃取仪-超声波涂料分散设备-杭州精浩 | 聚氨酯催化剂K15,延迟催化剂SA-1,叔胺延迟催化剂,DBU,二甲基哌嗪,催化剂TMR-2,-聚氨酯催化剂生产厂家 | 气动隔膜泵厂家-温州永嘉定远泵阀有限公司 | 地磅-电子地磅维修-电子吊秤-汽车衡-无人值守系统-公路治超-鹰牌衡器 | 南京种植牙医院【官方挂号】_南京治疗种植牙医院那个好_南京看种植牙哪里好_南京茀莱堡口腔医院 尼龙PA610树脂,尼龙PA612树脂,尼龙PA1010树脂,透明尼龙-谷骐科技【官网】 | 有福网(yofus.com)洗照片冲印,毕业聚会纪念册相册制作个性DIY平台 | 药品冷藏箱厂家_低温冰箱_洁净工作台-济南欧莱博电子商务有限公司官网 | 铝镁锰板_铝镁锰合金板_铝镁锰板厂家_铝镁锰金属屋面板_安徽建科 | 过跨车_过跨电瓶车_过跨转运车_横移电动平车_厂区转运车_无轨转运车 | 螺旋丝杆升降机-SWL蜗轮-滚珠丝杆升降机厂家-山东明泰传动机械有限公司 | 「阿尔法设计官网」工业设计_产品设计_产品外观设计 深圳工业设计公司 | 权威废金属|废塑料|废纸|废铜|废钢价格|再生资源回收行情报价中心-中废网 | 【星耀裂变】_企微SCRM_任务宝_视频号分销裂变_企业微信裂变增长_私域流量_裂变营销 | 电表箱-浙江迈峰电力设备有限公司-电表箱专业制造商 | 液压升降货梯_导轨式升降货梯厂家_升降货梯厂家-河南东圣升降设备有限公司 | 保镖公司-私人保镖-深圳保镖公司【环宇兄弟保镖】 | 上海冠顶工业设备有限公司-隧道炉,烘箱,UV固化机,涂装设备,高温炉,工业机器人生产厂家 | 不锈钢反应釜,不锈钢反应釜厂家-价格-威海鑫泰化工机械有限公司 不干胶标签-不干胶贴纸-不干胶标签定制-不干胶标签印刷厂-弗雷曼纸业(苏州)有限公司 | 杭州月嫂技术培训服务公司-催乳师培训中心报名费用-产后康复师培训机构-杭州优贝姆健康管理有限公司 | 活性炭厂家-蜂窝活性炭-粉状/柱状/果壳/椰壳活性炭-大千净化-活性炭 | 吹塑加工_大型吹塑加工_滚塑代加工-莱力奇吹塑加工有限公司 |