MySQL查询性能优化武器之链路追踪

2024-10-03 03:10:22

前言

MySQL优化器可以生成Explain执行计划，我们可以通过执行计划查看是否使用了索引，使用了哪种索引？

但是到底为什么会使用这个索引，我们却无从得知。

好在MySQL提供了一个好用的分析工具 — optimizer trace（优化器追踪），可以帮助我们查看优化器生成执行计划的整个过程，以及做出的各种决策，包括访问表的方法、各种开销计算、各种转换等。

1. 查看optimizer trace配置

show variables like '%optimizer_trace%';

输出参数详解：

optimizer_trace 主配置，enabled的on表示开启，off表示关闭，one_line表示是否展示成一行

optimizer_trace_features 表示优化器的可选特性，包括贪心搜索、范围优化等

optimizer_trace_limit 表示优化器追踪最大显示数目，默认是1条

optimizer_trace_max_mem_size 表示优化器追踪占用的最大容量

optimizer_trace_offset 表示显示的第一个优化器追踪的偏移量

2. 开启optimizer trace

optimizer trace默认是关闭，我们可以使用命令手动开启：

SET optimizer_trace="enabled=on";

3. 线上问题复现

先造点数据备用，创建一张用户表：

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(100) NOT NULL COMMENT '姓名',
  `gender` tinyint NOT NULL COMMENT '性别',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_gender_name` (`gender`,`name`)
) ENGINE=InnoDB COMMENT='用户表';

创建了两个索引，分别是(name)和(gender,name)。

执行一条SQL，看到底用到了哪个索引：

select * from user where gender=0 and name='一灯';

跟期望的一致，优先使用了(gender,name)的联合索引，因为where条件中刚好有gender和name两个字段。

我们把这条SQL传参换一下试试：

select * from user where gender=0 and name='张三';

这次竟然用了(name)上面的索引，同一条SQL因为传参不同，而使用了不同的索引。

到这里，使用现有工具，我们已经无法排查分析，MySQL优化器为什么使用了(name)上的索引，而没有使用(gender,name)上的联合索引。

只能请今天的主角 —optimizer trace（优化器追踪）出场了。

3. 使用optimizer trace

使用optimizer trace查看优化器的选择过程：

SELECT * FROM information_schema.OPTIMIZER_TRACE;

输出结果共有4列：

QUERY 表示我们执行的查询语句

TRACE 优化器生成执行计划的过程（重点关注）

MISSING_BYTES_BEYOND_MAX_MEM_SIZE 优化过程其余的信息会被显示在这一列

INSUFFICIENT_PRIVILEGES 表示是否有权限查看优化过程，0是，1否

接下来我们看一下TRACE列的内容，里面的数据很多，我们重点分析一下range_scan_alternatives结果列，这个结果列展示了索引选择的过程。

输出结果字段含义：

index 索引名称
ranges 查询范围
index_dives_for_eq_ranges 是否用到索引潜水的优化逻辑
rowid_ordered 是否按主键排序
using_mrr 是否使用mrr
index_only 是否使用了覆盖索引
in_memory 使用内存大小
rows 预估扫描行数
cost 预估成本大小，值越小越好
chosen 是否被选择
cause 没有被选择的原因，cost表示成本过高

从输出结果中，可以看到优化器最终选择了使用(name)索引，而(gender,name)索引因为成本过高没有被使用。

再也不用担心找不到MySQL用错索引的原因，赶紧用起来吧！

到此这篇关于MySQL查询性能优化武器之链路追踪的文章就介绍到这了,更多相关MySQL链路追踪内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

大幅优化MySQL查询性能的奇技淫巧

回顾 MySQL / InnoDB 的改善历史.你能很容易发现.在MySQL 5.6稳定版本中从来没有在read-only 这么快的提速,它很容易搞懂,以及在read-only(RO)有着良好的扩张性.也很期待它在read+write(RW)上达到一个较高水平.(特别是在读取数据是数据库主要工作的时候) 然而.我们对于RO在 MySQL 5.6的表现也十分的高兴,在5.7这个版本中,主要工作集中在 read+write (RW)上, 因为在大数据的处理上还没能达到我们的期望.但是RW依赖RO下.
MySQL查询性能优化七种方式索引潜水

目录前言: 有读者可能会一脸懵? 啥是索引潜水? 你给起的名字的吗?有没有索引蛙泳? 这个名字还真不是我起的,今天要讲的知识点就叫索引潜水(Index dive) . 先要从一件怪事说起: 我先造点数据复现一下问题,创建一张用户表: CREATE TABLE `user` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID', `name` varchar(100) NOT NULL DEFAULT '' COM
Mysql数据库性能优化之子查询

记得在做项目的时候, 听到过一句话, 尽量不要使用子查询, 那么这一篇就来看一下, 这句话是否是正确的. 那在这之前, 需要介绍一些概念性东西和mysql对语句的大致处理. 当Mysql Server的连接线程接收到Client发送过来的SQL请求后, 会经过一系列的分解Parse, 进行相应的分析, 然后Mysql会通过查询优化器模块, 根据该Sql所涉及到的数据表的相关统计信息进行计算分析. 然后在得出一个Mysql自认为最合理最优化的数据访问方式, 也就是我们常说的"执行计划",
mysql千万级数据分页查询性能优化

mysql数据量大时使用limit分页,随着页码的增大,查询效率越低下. 实验 1.直接使用用limit start, count分页语句: select * from order limit start, count 当起始页较小时,查询没有性能问题,我们分别看下从10, 100, 1000, 10000开始分页的执行时间(每页取20条), 如下: select * from order limit 10, 20 0.016秒 select * from order limit 100, 20
MySQL数据库查询性能优化的4个技巧干货

目录前言 SQL的执行频率慢查询日志 show profiles详情分析 explain执行计划 1.ID参数 2.select_type参数 3.type参数前言 MySQL性能优化是一个老生常谈的问题,无论是在实际工作中还是面试中,都不可避免遇到相应的场景,下面博主就总结一些能够帮助大家解决这个问题的小技巧. SQL优化之前需要确认哪些SQL需要优化,这时就需要引起SQL性能分析工具,主要优化的是查询语句. SQL的执行频率 SQL性能优化一般是针对查询语句,所以在定位是否需要优化之前
通过MySQL慢查询优化MySQL性能的方法讲解

随着访问量的上升,MySQL数据库的压力就越大,几乎大部分使用MySQL架构的web应用在数据库上都会出现性能问题,通过mysql慢查询日志跟踪有问题的查询非常有用,可以分析出当前程序里有很耗费资源的sql语句. 慢查询日志我们可以通过my.cnf文件设置开启,下面先来看一下相关参数的意义 log-slow-queries <slow_query_log_file> 存放slow query日志的文件.你必须保证mysql server进程mysqld_safe进程用户对该文件有w权限. lo
MySQL数据库查询性能优化策略

优化查询使用Explain语句分析查询语句 Explain 用来分析 SELECT 查询语句,开发人员可以通过分析 Explain 结果来优化查询语句. 通过对查询语句的分析,可以了解查询语句的执行情况,找出查询语句执行的瓶颈,从而优化查询语句. 使用索引查询 MySql中提高性能的一个最有效的方式就是对数据表设计合理的索引. 索引提供了高效访问数据的方法,并且加快查询速度. 如果查询时没有使用索引,那么查询语句将扫描表中所有的记录.在数据量大的时候,这样查询速度会很慢. 使用索引进行查询,查
mysql查询时offset过大影响性能的原因和优化详解

前言 mysql查询使用select命令,配合limit,offset参数可以读取指定范围的记录.本文将介绍mysql查询时,offset过大影响性能的原因及优化方法. 准备测试数据表及数据 1.创建表 CREATE TABLE `member` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(10) NOT NULL COMMENT '姓名', `gender` tinyint(3) unsigned NOT NU
MySQL查询性能优化武器之链路追踪

目录前言 1. 查看optimizer trace配置 2. 开启optimizer trace 3. 线上问题复现 3. 使用optimizer trace 前言 MySQL优化器可以生成Explain执行计划,我们可以通过执行计划查看是否使用了索引,使用了哪种索引? 但是到底为什么会使用这个索引,我们却无从得知. 好在MySQL提供了一个好用的分析工具 — optimizer trace(优化器追踪),可以帮助我们查看优化器生成执行计划的整个过程,以及做出的各种决策,包括访问表的方法.各种
MySQL查询性能优化索引下推

目录前言 1. 索引下推的作用 2. 案例实践 3. 索引下推配置 4. 索引下推原理剖析 5. 索引下推应用范围前言前面已经讲了MySQL的其他查询性能优化方式,没看过可以去了解一下: MySQL查询性能优化七种方式索引潜水 MySQL查询性能优化武器之链路追踪今天要讲的是MySQL的另一种查询性能优化方式 — 索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本增加的特性. 1. 索引下推的作用主要作用有两个: 减少回表查询的次数减少存
解析MySQL数据库性能优化的六大技巧

数据库表表面上存在索引和防错机制,然而一个简单的查询就会耗费很长时间.Web应用程序或许在开发环境中运行良好,但在产品环境中表现同样糟糕.如果你是个数据库管理员,你很有可能已经在某个阶段遇到上述情况.因此,本文将介绍对MySQL进行性能优化的技巧和窍门. 1.存储引擎的选择如果数据表需要事务处理,应该考虑使用InnoDB,因为它完全符合ACID特性.如果不需要事务处理,使用默认存储引擎MyISAM是比较明智的.并且不要尝试同时使用这两个存储引擎.思考一下:在一个事务处理中,一些数据表使用Inno
Mysql数据库性能优化一

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显.关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情.当我们去设计数据库表结构,对操作数据库时(尤其是查表时的SQL语句),我们都需要注意数据操作的性能.这里,我们不会讲过多的SQL语句的优化,而只是针对MySQL这一Web应用最多的数据库. mysql的性能优化无法一蹴而就,必须一步一步慢慢来,从各个方面进行优化,最终性能就会有大的提升. Mysql数据库的优化技术对mysql优化是
Mysql数据库性能优化三（分表、增量备份、还原）

接上篇Mysql数据库性能优化二对表进行水平划分如果一个表的记录数太多了,比如上千万条,而且需要经常检索,那么我们就有必要化整为零了.如果我拆成100个表,那么每个表只有10万条记录.当然这需要数据在逻辑上可以划分.一个好的划分依据,有利于程序的简单实现,也可以充分利用水平分表的优势.比如系统界面上只提供按月查询的功能,那么把表按月拆分成12个,每个查询只查询一个表就够了.如果非要按照地域来分,即使把表拆的再小,查询还是要联合所有表来查,还不如不拆了.所以一个好的拆分依据是最重要的
MySQL数据库性能优化介绍

目录为什么做优化?? 从哪些方面入手?? 解决方案是什么??? 要怎样选择??? SQL优化总结为什么做优化?? 因为数据量太多了,项目部署上线再到用户使用,每天数据增长几十万条,给服务器带来非常大的负担,互联网一直追求高性能,可是随着业务规模变大,用户数量变多,服务器的性能越来越差,因此我们不得不对数据库有更高要求. 从哪些方面入手?? 第一,是查询的速度,我们期望数据量到达TB级别仍然能够实现百万级别查询速度. 第二.是并发量,我们对它的要求能够同时处理几千甚至上万的并发访问,还要配

MySQL查询性能优化武器之链路追踪

目录

前言

1. 查看optimizer trace配置

2. 开启optimizer trace

3. 线上问题复现

3. 使用optimizer trace

相关推荐

随机推荐