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

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

MySQL 覆蓋索引的優點

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

一個通常的建議是為WHERE條件創建索引,但這其實是片面的。索引應當為全部查詢設計,而不僅僅是WHERE條件。索引確實能有效地查找數據行,但MySQL也能夠使用索引獲取列數據,這樣根本不需要去讀取一行數據。畢竟,索引的葉子節點包含了索引對應的值。當年能夠讀取索引就能夠拿到想要的數據時為什么還去讀數據行呢?當索引包含了所有查詢的數據時,這個索引就稱之為覆蓋索引。

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

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

在所有的場景中,最典型的就是相比查找數據行,只包含索引列的查詢的代價相當低。需要注意的是,聚集索引并不是任意類型的索引。聚集索引必須存儲索引數據列對應的值。哈希,空間和全文索引并沒有存儲這些值,因此MySQL只能使用二叉樹去覆蓋查詢。而且,不同的存儲引擎實現覆蓋索引的方式不同,并且并不是全部的存儲引擎都支持覆蓋索引(例如Memory存儲引擎當前就不支持)。

當你驗證查詢中索引使用了覆蓋索引時,使用Explain語句時,會在Extra列中看到“Using index”。例如,在store_goods表有一個(shop_id, goods_category_id1)的多列索引。MySQL可以在查詢返回數據只有這兩列時使用索引:

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

MySQL 覆蓋索引的優點

覆蓋索引查詢在某些情況會讓這樣的優化失效。MySQL查詢優化器在執行查詢時會判斷索引是否覆蓋到。假設索引覆蓋了WHERE條件,但沒有覆蓋整個查詢。如果評估結果決定不走覆蓋索引,那么MySQL 5.5及以前的版本會直接獲取數據行,即便是不需要這些數據,然后才會過濾掉。

讓我們看一下為什么這種情況會發生,然后如何重寫查詢以便解決這個問題。首先查詢是這樣的:

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

這個時候的結果是不會走覆蓋索引,而是普通的索引,這是因為:

沒有索引覆蓋了查詢數據列,因為我們從數據表讀取了全部列并且沒有索引列覆蓋了全部列。理論上,MySQL還有一個快捷方式可以使用,那就是WHERE條件中使用了索引覆蓋的列,因此MySQL可以先使用這個索引找到對應的actor,然后在檢查他們的title是否匹配,然后在讀取滿足條件的全部的數據行。 對于早期的低版本的存儲引擎API(MySQL 5.5以前的版本)來說,MySQL無法在索引中使用LIKE操作,而只支持簡單的比較操作(=,IN,>=)。MySQL可以在索引中使用前綴匹配的LIKE查詢,這是因為它可以將它們轉換為比較操作。但是前導通配符(也就是LIKE中前置的%)導致存儲引擎無法評估匹配條件。因此,MySQL會獲取行數據再比較,而不是索引的值。

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

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 覆蓋索引的優點

我們稱之為“遞延JOIN(deferred join)”,因為它延遲了列的訪問。在第一階段的查詢中,當它在子查詢中找到了匹配的行的過程中,MySQL使用了覆蓋索引。雖然在整個查詢中沒有覆蓋到,但總比沒有的好。

這種優化的效果好壞取決于WHERE條件查找到了多少行數據。假設products表包含了上百萬行的數據。可以對比一下這兩種查詢的性能對比,總的數據為100萬行。

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

對比結果如下表。

數據集 原始查詢 優化后查詢 第一種情況 5qps 5qps 第二種情況 7qps 35qps 第三種情況 2400qps 2000qps

結果的解釋如下:

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

在大多數存儲引擎中,一個索引只能夠覆蓋訪問列是索引的一部分。然而,InnoDB實際上會做進一步的優化。想想InnoDB的的輔助索引在葉子節點中存儲了主鍵的值。這意味著InnoDB的輔助索引實際上有了額外的列幫助InnoDB使用覆蓋索引。 例如,sakila.actor表使用了InnoDB,然后在last_name有一個索引,因此這個索引能夠覆蓋或者主鍵actor_id的查詢——即便這個列并不是索引的一部分。

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

MySQL 覆蓋索引的優點

以上就是MySQL 覆蓋索引的優點的詳細內容,更多關于MySQL 覆蓋索引的資料請關注好吧啦網其它相關文章!

標簽: MySQL 數據庫
相關文章:
主站蜘蛛池模板: 温控器生产厂家-提供温度开关/热保护器定制与批发-惠州市华恺威电子科技有限公司 | 中控室大屏幕-上海亿基自动化控制系统工程有限公司 | 净化车间_洁净厂房_净化公司_净化厂房_无尘室工程_洁净工程装修|改造|施工-深圳净化公司 | 微信聊天记录恢复_手机短信删除怎么恢复_通讯录恢复软件下载-快易数据恢复 | 合肥活动房_安徽活动板房_集成打包箱房厂家-安徽玉强钢结构集成房屋有限公司 | 数显水浴恒温振荡器-分液漏斗萃取振荡器-常州市凯航仪器有限公司 | 滑板场地施工_极限运动场地设计_滑板公园建造_盐城天人极限运动场地建设有限公司 | 冷热冲击试验箱_温度冲击试验箱价格_冷热冲击箱排名_林频厂家 | PCB厂|线路板厂|深圳线路板厂|软硬结合板厂|电路板生产厂家|线路板|深圳电路板厂家|铝基板厂家|深联电路-专业生产PCB研发制造 | 防爆大气采样器-防爆粉尘采样器-金属粉尘及其化合物采样器-首页|盐城银河科技有限公司 | 防潮防水通风密闭门源头实力厂家 - 北京酷思帝克门窗 | 全自动端子机|刺破式端子压接机|全自动双头沾锡机|全自动插胶壳端子机-东莞市傅氏兄弟机械设备有限公司 | 巨野电机维修-水泵维修-巨野县飞宇机电维修有限公司 | 深圳市源和塑胶电子有限公司-首页 | 超声波分散机-均质机-萃取仪-超声波涂料分散设备-杭州精浩 | 谷歌关键词优化-外贸网站优化-Google SEO小语种推广-思亿欧外贸快车 | 新疆系统集成_新疆系统集成公司_系统集成项目-新疆利成科技 | 蔬菜配送公司|蔬菜配送中心|食材配送|饭堂配送|食堂配送-首宏公司 | 制氮设备_PSA制氮机_激光切割制氮机_氮气机生产厂家-苏州西斯气体设备有限公司 | 生物制药洁净车间-GMP车间净化工程-食品净化厂房-杭州波涛净化设备工程有限公司 | 汽液过滤网厂家_安平县银锐丝网有限公司 | 广西教师资格网-广西教师资格证考试网 | 广东燎了网络科技有限公司官网-网站建设-珠海网络推广-高端营销型外贸网站建设-珠海专业h5建站公司「了了网」 | 蔡司三坐标-影像测量机-3D扫描仪-蔡司显微镜-扫描电镜-工业CT-ZEISS授权代理商三本工业测量 | 不锈钢电动球阀_气动高压闸阀_旋塞疏水调节阀_全立阀门-来自温州工业阀门巨头企业 | 定坤静电科技静电消除器厂家-除静电设备 | 步进驱动器「一体化」步进电机品牌厂家-一体式步进驱动 | 混合反应量热仪-高温高压量热仪-微机差热分析仪DTA|凯璞百科 | 工业雾炮机_超细雾炮_远程抑尘射雾器-世纪润德环保设备 | 高温高压釜(氢化反应釜)百科 | 3D全息投影_地面互动投影_360度立体投影_水幕灯光秀 | 广东银虎 蜂窝块状沸石分子筛-吸附脱硫分子筛-萍乡市捷龙环保科技有限公司 | 英国公司注册-新加坡公司注册-香港公司开户-离岸公司账户-杭州商标注册-杭州优创企业 | 丝印油墨_水性油墨_环保油墨油漆厂家_37国际化工 | 洗瓶机厂家-酒瓶玻璃瓶冲瓶机-瓶子烘干机-封口旋盖压盖打塞机_青州惠联灌装机械 | 土壤肥料养分速测仪_测土配方施肥仪_土壤养分检测仪-杭州鸣辉科技有限公司 | 东莞动力锂电池保护板_BMS智能软件保护板_锂电池主动均衡保护板-东莞市倡芯电子科技有限公司 | 蒜肠网-动漫,二次元,COSPLAY,漫展以及收藏型模型,手办,玩具的新媒体.(原变形金刚变迷TF圈) | 十字轴_十字轴万向节_十字轴总成-南京万传机械有限公司 | 天津暖气片厂家_钢制散热器_天津铜铝复合暖气片_维尼罗散热器 | 品牌设计_VI设计_电影海报设计_包装设计_LOGO设计-Bacross新越品牌顾问 | 高低温老化试验机-步入式/低温恒温恒湿试验机-百科 |