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

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

MySQL 覆蓋索引的優點

瀏覽:34日期: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 數據庫
相關文章:
主站蜘蛛池模板: 睿婕轻钢别墅_钢结构别墅_厂家设计施工报价 | 合肥注册公司|合肥代办营业执照、2024注册公司流程 | HV全空气系统_杭州暖通公司—杭州斯培尔冷暖设备有限公司 | 酒店厨房设计_中央厨房设计_北京商用厨房设计公司-奇能商厨 | 青岛代理记账_青岛李沧代理记账公司_青岛崂山代理记账一个月多少钱_青岛德辉财税事务所官网 | 钛合金标准件-钛合金螺丝-钛管件-钛合金棒-钛合金板-钛合金锻件-宝鸡远航钛业有限公司 | ★店家乐|服装销售管理软件|服装店收银系统|内衣店鞋店进销存软件|连锁店管理软件|收银软件手机版|会员管理系统-手机版,云版,App | TTCMS自助建站_网站建设_自助建站_免费网站_免费建站_天天向上旗下品牌 | 档案密集柜_手动密集柜_智能密集柜_内蒙古档案密集柜-盛隆柜业内蒙古密集柜直销中心 | 锂电池砂磨机|石墨烯砂磨机|碳纳米管砂磨机-常州市奥能达机械设备有限公司 | 岩棉切条机厂家_玻璃棉裁条机_水泥基保温板设备-廊坊鹏恒机械 | 酒水灌装机-白酒灌装机-酒精果酒酱油醋灌装设备_青州惠联灌装机械 | 即用型透析袋,透析袋夹子,药敏纸片,L型涂布棒-上海桥星贸易有限公司 | 正压送风机-多叶送风口-板式排烟口-德州志诺通风设备 | C形臂_动态平板DR_动态平板胃肠机生产厂家制造商-普爱医疗 | 旋转气浴恒温振荡器-往复式水浴恒温振荡器-金怡百科 | 无缝方管|无缝矩形管|无缝方矩管|无锡方管厂家 | 工业铝型材-铝合金电机壳-铝排-气动执行器-山东永恒能源集团有限公司 | 中央空调温控器_风机盘管温控器_智能_液晶_三速开关面板-中央空调温控器厂家 | 螺杆真空泵_耐腐蚀螺杆真空泵_水环真空泵_真空机组_烟台真空泵-烟台斯凯威真空 | 建筑资质代办-建筑资质转让找上海国信启航 | 钣金加工厂家-钣金加工-佛山钣金厂-月汇好 | 胃口福饺子加盟官网_新鲜现包饺子云吞加盟 - 【胃口福唯一官网】 | 壹车网 | 第一时间提供新车_资讯_报价_图片_排行! | 贵州成人高考网_贵州成考网| 磁力抛光机_磁力研磨机_磁力去毛刺机-冠古设备厂家|维修|租赁【官网】 | 黑龙江「京科脑康」医院-哈尔滨失眠医院_哈尔滨治疗抑郁症医院_哈尔滨精神心理医院 | LNG鹤管_内浮盘价格,上装鹤管,装车撬厂家-连云港赛威特机械 | 过滤器_自清洗过滤器_气体过滤器_苏州华凯过滤技术有限公司 | 球形钽粉_球形钨粉_纳米粉末_难熔金属粉末-广东银纳官网 | 小青瓦丨古建筑瓦丨青瓦厂家-宜兴市徽派古典建筑材料有限公司 | 温湿度记录纸_圆盘_横河记录纸|霍尼韦尔记录仪-广州汤米斯机电设备有限公司 | 房屋质量检测-厂房抗震鉴定-玻璃幕墙检测-房屋安全鉴定机构 | 上海平衡机-单面卧式动平衡机-万向节动平衡机-圈带动平衡机厂家-上海申岢动平衡机制造有限公司 | 广州各区危化证办理_危险化学品经营许可证代办| 钢骨架轻型板_膨石轻型板_钢骨架轻型板价格_恒道新材料 | 探鸣起名网-品牌起名-英文商标起名-公司命名-企业取名包满意 | 低温柔性试验仪-土工布淤堵-沥青车辙试验仪-莱博特(天津)试验机有限公司 | 首页-恒温恒湿试验箱_恒温恒湿箱_高低温试验箱_高低温交变湿热试验箱_苏州正合 | 衢州装饰公司|装潢公司|办公楼装修|排屋装修|别墅装修-衢州佳盛装饰 | 消泡剂_水处理消泡剂_切削液消泡剂_涂料消泡剂_有机硅消泡剂_广州中万新材料生产厂家 |