没内鬼,来点干货!SQL优化和诊断-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

没内鬼,来点干货!SQL优化和诊断

简介: 云栖号资讯:【点击查看更多行业资讯】在这里您可以找到不同行业的第一手的上云资讯,还在等什么,快来! SQL优化与诊断 Explain诊断Explain各参数的含义如下: 列名说明 id执行编号,标识select所属的行。

云栖号资讯:【点击查看更多行业资讯
在这里您可以找到不同行业的第一手的上云资讯,还在等什么,快来!


SQL优化与诊断

Explain诊断

Explain各参数的含义如下:

1
2

select_type 常见类型及其含义

SIMPLE:不包含子查询或者 UNION 操作的查询

PRIMARY:查询中如果包含任何子查询,那么最外层的查询则被标记为 PRIMARY

SUBQUERY:子查询中第一个 SELECT

DEPENDENT SUBQUERY:子查询中的第一个 SELECT,取决于外部查询

UNION:UNION 操作的第二个或者之后的查询

DEPENDENT UNION:UNION 操作的第二个或者之后的查询,取决于外部查询

UNION RESULT:UNION 产生的结果集

DERIVED:出现在 FROM 字句中的子查询

type常见类型及其含义

system:这是 const 类型的一个特例,只会出现在待查询的表只有一行数据的情况下

consts:常出现在主键或唯一索引与常量值进行比较的场景下,此时查询性能是最优的

eq_ref:当连接使用的是完整的索引并且是 PRIMARY KEY 或 UNIQUE NOT NULL INDEX 时使用它

ref:当连接使用的是前缀索引或连接条件不是 PRIMARY KEY 或 UNIQUE INDEX 时则使用它

ref_or_null:类似于 ref 类型的查询,但是附加了对 NULL 值列的查询

index_merge:该联接类型表示使用了索引进行合并优化

range:使用索引进行范围扫描,常见于 between、> 、< 这样的查询条件

index:索引连接类型与 ALL 相同,只是扫描的是索引树,通常出现在索引是该查询的覆盖索引的情况

ALL:全表扫描,效率最差的查找方式

阿里编码规范要求:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts 最好

key列

实际在查询中是否使用到索引的标志字段

如何查看Mysql优化器优化之后的SQL

3

结果如下:

/ select#1 / select mytest.student.age AS age,mytest.student.name AS name,mytest.student.year AS year from mytest.student where ((mytest.student.age = 1) and (mytest.student.name = 1))

为什么要做这个事呢?我们知道Mysql有一个最左匹配原则,那么如果我的索引建的是age,name,那我以name,age这样的顺序去查询能否使用到索引呢?实际上是可以的,就是因为Mysql查询优化器可以帮助我们自动对SQL的执行顺序等进行优化,以选取代价最低的方式进行查询(注意是代价最低,不是时间最短)

SQL优化

超大分页场景解决方案

如表中数据需要进行深度分页,如何提高效率?在阿里出品的Java编程规范中写道:

利用延迟关联或者子查询优化超多分页场景

说明:MySQL 并不是跳过 offset 行,而是取 offset+N 行,然后返回放弃前 offset 行,返回 N 行,那当 offset 特别大的时候,效率就非常的低下,要么控制返回的总页数,要么对超过特定阈值的页数进行 SQL 改写

4

获取一条数据时的Limit 1

如果数据表的情况已知,某个业务需要获取符合某个Where条件下的一条数据,注意使用Limit

说明:在很多情况下我们已知数据仅存在一条,此时我们应该告知数据库只用查一条,否则将会转化为全表扫描

反例(耗时2424.612s)

select * from task_result where unique_key = 'ebbf420b65d95573db7669f21fa3be3e_861414030800727_48';

正例(耗时1.036s)

select * from task_result where unique_key = 'ebbf420b65d95573db7669f21fa3be3e_861414030800727_48' LIMIT 1;

说明

task_result表为生产环境的一个表,总数据量为3400万,where条件非索引字段,数据所在行为第19486条记录

批量插入

5

like语句的优化

like语句一般业务要求都是 '%关键字%'这种形式,但是依然要思考能否考虑使用右模糊的方式去替代产品的要求,其中阿里的编码规范提到:

页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决

避免SQL中对where字段进行函数转换或表达式计算

1

对正例的Explain

1    SIMPLE    task_result        range    adapt_id    adapt_id    98        99    100.00    Using index condition

对反例的Explain

1    SIMPLE    task_result        ALL                                        33628554    11.11    Using where

说明

task_result表为生产环境的一个表,总数据量为3400万,taskid是一个普通索引列,可见%%这种匹配方式完全无法使用索引,从而进行全表扫描导致效率极低,而正例通过索引查找数据只需要扫描99条数据即可

避免SQL中对where字段进行函数转换或表达式计算

1

对正例的Explain

1    SIMPLE    task_result        const    PRIMARY    PRIMARY    8    const    1    100.00

对反例的Explain

1    SIMPLE    task_result        ALL                                    33631512  100.00    Using where

说明

其实在知道了有SQL优化器之后,我个人感觉这种普通的表达式转换应该可以提前进行处理再进行查询,这样一来就可以用到索引了,但是问题又来了,如果mysql优化器可以提前计算出结果,那么写sql语句的人也一定可以提前计算出结果,所以矛盾点在这个地方,导致5.7版本以前的此种情况都无法使用索引吧,未来可能会对其进行优化。

使用 ISNULL()来判断是否为 NULL 值

说明:NULL 与任何值的直接比较都为 NULL

2

多表查询

我所在的公司基本禁止了多表查询,那如果必须使用到的话,我们可以一起参考一下阿里的编码规范

Eg:超过三个表禁止 join。需要 join 的字段,数据类型必须绝对一致;多表关联查询时,保证被关联的字段需要有索引

明明有索引为什么还走全表扫描

之前回答一些面试问题的时候,对某一个点的理解出现了偏差,即我认为只要查询的列有索引则一定会使用索引去Push数据

然而实际上不仅仅是这样,真正应该是:针对查询的数据行占总数据量过多时会转化成全表查询

那么这个过多指代的是多少呢?

我的测试结果是50%,但个人认为MySQL优化器不会完全纠结于行数区分是否全表,而是有很多其他因素综合考虑发现全表扫描的效率更高等等,所以充分认识到该问题即可

count(*) 还是 count(id)

阿里的Java编码规范中有以下内容:

【强制】不要使用 count(列名) 或 count(常量) 来替代 count(*)

count(*) 是 SQL92 定义的标准统计行数的语法,跟数据库无关,跟 NULL 和非 NULL 无关。

说明:count(*)会统计值为 NULL 的行,而 count(列名)不会统计此列为 NULL 值的行

字段类型不同导致索引失效

阿里的Java编码规范中有以下内容:

【推荐】防止因字段类型不同造成的隐式转换,导致索引失效

实际上数据库在查询的时候会作一层隐式的转换,比如 varchar 类型字段通过 数字去查询

1

Tips

自建数据表进行测试

2

插入数据

3

【云栖号在线课堂】每天都有产品技术专家分享!
课程地址:https://yqh.aliyun.com/live

立即加入社群,与专家面对面,及时了解课程最新动态!
【云栖号在线课堂 社群】https://c.tb.cn/F3.Z8gvnK

原文发布时间:2020-04-23
本文作者:Kerwin_
本文来自:“掘金”,了解相关信息可以关注“掘金”

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章