MySQL性能优化(九)range和ref

俊达2周前技术文章31

有的时候,我们会遇到这样的情况:明明有索引,明明有更好的执行计划,但是优化器并没有选择这个最优的执行计划。

优化器可能会选择并非最优的索引,可能选择并非最优的数据访问方式。

下面是一个真实的例子:

一个例子

SELECT * 
FROM dtops_mysql_stat  
WHERE tenant_id = 1 
  and instance_name='dtstack-dev1:3306' 
  and check_time >='2018-04-03' and check_time <= '2018-05-03' 
ORDER BY check_time desc limit 10;


索引:

   KEY `ind_tenantid` (`tenant_id`,`instance_name`,`check_time`)


根据这个SQL的where条件和索引,这个SQL的效率应该非常好,索引ind_tenantid和where条件完全符合,order by也可以利用索引的有序性。

但是,这个SQL执行却需要1秒。


我们来看一下SQL的执行计划:

9-1.jpg


查询用到了ind_tenantid索引,但是只用到了索引中的tenant_id和instance_name字段,这可以从type和key len看出。索引中的check_time,本来可以用来过滤check_time的数据,但是优化器并没有使用。


我们给这个SQL加上一个force index:

SELECT * 
FROM mysql_stat  force index(ind_tenantid)
WHERE tenant_id = 1 
  and instance_name='dtstack:3306' 
  and check_time >='2018-04-03' and check_time <= '2018-05-03' 
ORDER BY check_time desc limit 10;


加上force index之后,SQL的执行时间只需要几毫秒,对比一下执行计划:

9-2.jpg


type变成了range,key len变成了204,说明加上force index后,优化器用到了check_time来过滤数据。


但是优化器为什么会这么选择呢?我们可以使用optimizer trace功能来跟踪优化器是如何来评估各种可能的执行计划。


关于optimizer trace的具体使用方法,可以参考官方文档: https://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_OPT_TRACE.html

这里不做具体介绍。


我们来对比下上述2个SQL的trace文件:


不加force index的trace

9-3.png

(图1)


 9-4.png

(图2)


9-5.png

(图3)


我们来看一下trace中的关键信息:

1、table scan的cost为78431,这是优化器根据表的记录数评估得到的全表扫描的成本。

2、使用ind_tenantid进行range访问的成本为84899,比全表扫描的成本还要高,所以没有选择这个执行计划。

3、ref的成本为30635。

最终选择了ind_tenantid的ref执行计划。


所以不加force index的情况下,由于where条件的过滤性不高,优化器评估后认为所用索引range的成本比全表扫描还要高,所以放弃了对本SQL效率最高的执行计划。

加了force index的trace

加上force index之后的trace:

(图1)


(图2)


(图3)


加上force index后,

1、table scan的成本为infinity。这可能就是mysql优化器实现force index使用的手段。

2、range scan的成本还是84899,由于加了force idnex,这个成本比table scan的成本低,所以没有排除这个执行计划。

3、最终选择了range scan的执行计划。为什么这里没有使用ref的执行计划?可能是优化器在选择同一个索引的不同执行计划时,优先使用range。

总结

优化器是关系型数据库的一个核心组件,实现也比较复杂。

当遇到优化器选择的执行计划和我们想要的最优的执行计划不一致时,我们可以尝试使用SQL Hint或调整优化器参数来影响优化器。

我们可以使用optimizer trace来观察优化器如何选择执行计划。

如果要对优化器成本评估有更深入的理解,可以尝试去分析优化器的代码。











相关文章

使用clickhouse-backup迁移数据

使用clickhouse-backup迁移数据

说明上一篇文章中,我们介绍了clickhouse-backup工具。除了备份恢复,我们也可以使用该工具来迁移数据。这篇文章中,我们提供一个使用clickhouse-backup做集群迁移的方案。前置条...

 MySQL性能优化(十)in参数列表过长导致的性能问题

MySQL性能优化(十)in参数列表过长导致的性能问题

有时候可能有人会问:where条件中使用in和or有什么区别,哪种写法性能更好?in参数个数有没有限制?下面就是一个由于in参数列表过长导致的性能问题。一个例子当时使用的是mysql 5.6版本SEL...

寻找CPU使用率高进程方法

寻找CPU使用率高进程方法

背景节点报CPU使用率高,需要定位是什么进程占用CPU使用率高。CPU使用率持续较高在对应节点使用 “top”命令,然后键盘输入“P”,即按照CPU使用率排序进程。执行ps -ef | grep &l...

SparkStreaming对接kafka消费模式区别

SparkStreaming对接kafka消费模式区别

Sparkstreaming对接kafka使用的消费方式与常规的kafka消费方式完全不同,其中区别主要为消费者的管理方式不同。Ø  常规消费模式Kafka常规的消费模式以消费者组为消费单元...

CDH实操--hive高可用

CDH实操--hive高可用

前言在CDH中,hive metastore、hiveserver2若角色单实例部署,或者部署多个实例但是连接配置任选其一的话,均存在单点问题,一旦实例故障就会影响业务稳定;这时我们就好考虑高可用部署...

hadoop集群集成Iceberg操作指导

hadoop集群集成Iceberg操作指导

hadoop集群集成Iceberg操作指导书一、    准备工作1.       大数据集群运行正常,完成hi...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。