【黑魔法】Covering Indexes、STRAIGHT_JOIN

今天给大家介绍两个黑魔法,这都是压箱底的法宝。大家在使用时,一定要弄清他们的适用场景及用法,用好了,就是一把开天斧,用不好那就是画蛇添足。自从看过耗子哥(左耳朵耗子)的博客,都会给对相应专题有兴趣的小伙伴列出几篇拓展文章,我觉得这种方式还是非常不错,所以这篇文章我也会列出几篇扩展的文章,供想更深入思考的小伙伴查阅。

可能有人会认为这两个用法会比较冷门,但是在跨系统调用api的过程中,表的数据量比较大时,sql查询性能太差,会导致接口响应超时,就会对相应的业务产生非常大的影响。系统优化,大家千万不要以为只是后端的代码优化而已,sql的优化同样也是重点

1.Covering Indexes

可能有小伙伴会问,Covering Indexes到底是什么神器呢?它又是如何来提升性能的呢?接下来我会用最通俗易懂的语言来进行介绍,毕竟不是每个程序猿都要像DBA那样深刻理解数据库,知道如何用以及如何用好神器才是最关键的。

Covering Indexes就是一个索引覆盖所有要查询的字段(ps:这句话我挖个坑,文末我来解释)。

An index that contains all required information to resolve the query is known as a “Covering Index” – it completely covers the query.
Covering Index includes all the columns, the query refers to in the SELECT, JOIN, and WHERE clauses.

接下来我们通过一个非常简单的sql来进行分析:

SELECT column1, column2 FROM tablename WHERE column3=xxx;

你能想象将sql的执行时间从1.8秒,降到1.2秒,继续压榨到0.5,0.2.....,酣畅淋漓,怎一个爽字了得。就跟排兵布阵一样,打胜仗固然重要,但得想出成本最低效果最好的阵法,定会收获满满的成就感。

这条sql要如何来进行优化呢?第一反应可能就是说给“column3”加索引(普通索引或唯一索引)啊,没错,这样确实能在很大程度上提升这条sql的性能。

我们来分析下上面sql的执行计划:因为给“column3”建了索引,就会快速根据这个索引查询到符合条件的结果;然后再去这些符合条件的结果里查找所需的column1、column2字段;请注意,整个过程出现了两次查询,一次是查询索引,另一次查询结果的所需字段。

那能不能将上面说的执行计划再优化一下呢?大杀器Covering Indexes就是用来干这事的。给column3、column1、column2建个复合索引,如下:

alter table table_name add index index_column3 (column3,column1,column2) ;

这样就可以直接通过索引就能查询出符合条件的数据,而不必像上面那样先去查索引,然后再去查数据的两个过程

光说不练那是假把式!小伙伴们可以用explain去试试上面的两种情况,如果执行复合索引后的情况,你会发现Extra里出现Using index。

刚开始我说挖了个坑,现在我把坑填上。既然神器Covering Indexes这么好用,以后select语句的我都不管三七二十一的都亮出神器。难不成你select *也要亮神器?一个表那么多字段,全建成索引?那索引文件会不堪重负的,这就会适得其反,带来一系列恶果的。索引文件过大会造成insert、update非常慢,你select倒是爽快了,不能不顾其他兄弟吧,不仗义的事咱不能干,切记!

如果看完这个分析还不过瘾,下面我给几篇扩展文章:

https://www.c-sharpcorner.com/UploadFile/b075e6/improving-sql-performance-using-covering-indexes/

https://www.red-gate.com/simple-talk/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/

https://stackoverflow.com/questions/609343/what-are-covering-indexes-and-covered-queries-in-sql-server

https://stackoverflow.com/questions/62137/what-is-a-covered-index

2.STRAIGHT_JOIN

接下来给大家下另一个性能提升神器-STRAIGHT_JOIN,在数据量大的联表查询中灵活运用的话,能大大缩短查询时间。

首先来解释下STRAIGHT_JOIN到底是用做什么的:

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. 
This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

意思就是说STRAIGHT_JOIN功能同join类似,但能让左边的表来驱动右边的表,能改表优化器对于联表查询的执行顺序。

接下来我们举个例子进行大致的分析:

select t1.*
from Table1 t1
inner join Table2 t2
on t1.CommonID = t2.CommonID
where t1.FilterID = 1

以上sql大数据量下执行需要30s,是不是很奇怪?明明Table1表的FilterID字段建了索引啊,Table1和Table2的CommonID也建了索引啊。通过explain来分析,你会发现执行计划中表的执行顺序是Table2->Table1。这个时候要略微介绍下驱动表的概念,mysql中指定了连接条件时,满足查询条件的记录行数少的表为驱动表;如未指定查询条件,则扫描行数少的为驱动表。mysql优化器就是这么粗暴以小表驱动大表的方式来决定执行顺序的

但如下sql的执行时间都少于1s:

select t1.*
from Table1 t1
where t1.FilterID = 1

select t1.*
from Table1 t1
inner join Table2 t2
on t1.CommonID = t2.CommonID

这个时候STRAIGHT_JOIN就派上用场,我们对sql进行改造如下:

select t1.*
from Table1 t1
STRAIGHT_JOIN  Table2 t2
on t1.CommonID = t2.CommonID
where t1.FilterID = 1

用explain进行分析,发现执行顺序为Table1->Table2,这时就由Table1来作为驱动表了,Table1中相应的索引也就用上了,执行时间竟然低于1s了。

分析到这里,必须要重点说下:

  • STRAIGHT_JOIN只适用于inner join,并不使用与left join,right join。(因为left join,right join已经代表指定了表的执行顺序)
  • 尽可能让优化器去判断,因为大部分情况下mysql优化器是比人要聪明的。使用STRAIGHT_JOIN一定要慎重,因为啊部分情况下认为指定的执行顺序并不一定会比优化引擎要靠谱。

扩展阅读:

https://stackoverflow.com/questions/512294/when-to-use-straight-join-with-mysql

https://stackoverflow.com/questions/5818837/why-does-straight-join-so-drastically-improve-this-query-and-what-does-it-mean

https://dev.mysql.com/doc/refman/8.0/en/join.html

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Django Scrapy

关系型数据库和非关系型数据库

一 三范式 1,所有列必须为原子化列 2,设计时需要主键列 3,所有非主键列不能依靠传递与主键列发生关系(所有列与主键列发生的都是直接关系) 生活中的...

38350
来自专栏数据和云

【云和恩墨大讲堂】Oracle线上嘉年华第二讲

编辑手记:Oracle线上嘉年华,正在持续分享中。本次的主题是系统割接中的SQL解析问题和结合业务的SQL优化改写技巧。 1 嘉宾介绍 小鱼(邓秋爽) 云和恩...

29960
来自专栏andychai

MySQL模糊查询性能优化

根据模糊查找的业务场景,比对一下上面列出的6种条件,如果你的场景是全都要支持,并且是 大用户量, 接口qps高,海量的数据检索量,那就不要在数据库上做任何挣扎了...

7.5K70
来自专栏张戈的专栏

WordPress酷炫CSS3读者墙,排名按年度、本月、本周划分的小方法

WordPress 酷炫 CSS3 读者墙这个玩意一般不用我多说,大部分用 WordPress 的博主都了解过了,出自折子戏博客。 不过他这个读者墙的排行是按年...

40330
来自专栏枕边书

见招拆招-PostgreSQL中文全文索引效率优化

前言 上文 使用PostgreSQL进行中文全文检索 中我使用 PostgreSQL 搭建完成了一套中文全文检索系统,对数据库配置和分词都进行了优化,基本的查询...

59780
来自专栏CSDN技术头条

MySQL查询优化之道

查询优化器的任务是发现执行 SQL 查询的最佳方案。大多数查询优化器,要么基于规则、要么基于成本。

16540
来自专栏杨建荣的学习笔记

Oracle表中含有255列以上时需要注意的(r12笔记第77天)

今天看JL(Jonathan Lewis)的一篇文章,真是费了不少的脑细胞,玩Oracle几十年的老司机,看问题的角度和深度果然不一样,当时看他的大作《O...

420100
来自专栏CDA数据分析师

【干货】找不到适合自己的编程书?我自己动手写了一个热门编程书搜索网站(附PDF书单)

原作者 Vlad Wetzel 编译 CDA 编译团队 本文为 CDA 数据分析师原创作品,转载需授权 选择适合自己的编程书绝非易事,美国的程序员小哥根据国外著...

28860
来自专栏更流畅、简洁的软件开发方式

为or、in平反——or、in到底能不能利用索引?

  先说一个笑话,作为开场白。俺也换换风格试一试,呵呵。   在以前,有三个书生赶考,在路上遇到了一个算命先生,于是就问算命先生:我们三个人赶考,结果如何呀?...

238100
来自专栏带你撸出一手好代码

深入浅出数据库索引

前段时间,公司一个新上线的网站出现页面响应速度缓慢的问题, 一位负责这个项目的但并不是搞技术的妹子找到我,让我想办法提升网站的访问速度 ,因为已经有很多用户来投...

38640

扫码关注云+社区

领取腾讯云代金券