视图在数据库中是非常普及的功能。但是长期以来,大多数互联网公司的《MySQL开发规范》中都有一条规范:在MySQL中禁止(或建议不要)使用视图。究其原因,主要是由于在MySQL中视图的查询性能不好,同时带来了管理维护上的高成本。 不过随着MySQL 8.0中派生条件下推特性的引入,尤其是最近GA的MySQL 8.0.29版本中对于包含union子句的派生条件下推优化,MySQL中视图查询的性能得到了质的提升。 《MySQL开发规范》已经过时了,DBA该考虑考虑将禁止使用视图的规定重新修订一下了。
最近遇到了不少MySQL性能优化的案例,都和子查询有关,今天就这个话题做一定的分析。
随着MySQL版本的发展,优化器是越来越智能,优化器开关也越来越多,本文给大家分享一下MySQL对derived table的优化处理。
前面说了子查询里有no/any/all不能用limit,group by,order by等,他会被查询优化器优化掉,子查询可能会物化转成内连接semi-join查询,物化就是会吧子查询看做一个表,如果数据太大,超过系统变量tmp_table_size,则会在磁盘里创建b+树的临时表,如果比较小,则会创建内存里hash树的临时表,之后会物化表转连接,但如果直接转where 和on,则可能会出现子查询多条的情况,我们的真实需求并不需要多条,所以有了semi-join。
当我们遇到一个慢查询语句时,首先要做的是检查所编写的 SQL 语句是否合理,优化 SQL 语句从而提升查询效率。所以对 SQL 有一个整体的认识是有必要的。
这是一个统计类的 SQL,直接执行跑了好几个小时都没有结束,所以暂时不知道实际耗时,因为实在是太久了~
很明显,这个语句在8.0.25版本运行出的结果与我们给定where条件不符,我们要查询关于“张三”的记录,结果返回的结果是”李四“的,很明显的一个bug,但是到8.0.26版本这个问题得到了修正。
最近和同事排查了一个MySQL的SQL性能问题。问题的背景是有一个业务的数据库从MySQL 5.5迁移到了MySQL 5.7,原来在5.5中有一个SQL秒级就能完成,但是在5.7版本中执行时间长了好多,业务也产生了延迟。
上篇文章说了,mysql的访问效率有几大类别,const,ref,Ref_null,rang,index,all,以及连接查询走索引,驱动表和被驱动表的查询效率。
MySQL执行计划是sql语句经过查询优化器后,查询优化器会根据用户的sql语句所包含的字段和内容数量等统计信息,选择出一个执行效率最优(MySQL系统认为最优)的执行计划,然后根据执行计划,调用存储引擎提供的接口,获取数据。
爱可生 DBA 团队成员,擅长故障分析、性能优化,个人博客:https://www.jianshu.com/u/a95ec11f67a8,欢迎讨论。
可以利用order by 子句完成随机抽取某些行的功能,他的原理就是order by rand()能够数据随机排序。
在工作中,我们用于捕捉性能问题最常用的就是打开慢查询,定位执行效率差的SQL,那么当我们定位到一个SQL以后还不算完事,我们还需要知道该SQL的执行计划,比如是全表扫描,还是索引扫描,这些都需要通过EXPLAIN去完成。EXPLAIN命令是查看优化器如何决定执行查询的主要方法。可以帮助我们深入了解MySQL的基于开销的优化器,还可以获得很多可能被优化器考虑到的访问策略的细节,以及当运行SQL语句时哪种策略预计会被优化器采用。
前面说了explain的table是表名,显示在前面的代表驱动表,正常select会出现不同的id,但如果子查询本来是两个select,但被优化成连接查询,就会导致是相同的id,union查询会出现临时表,id为null,这个临时表作用于去重,union all不需要去重,所以也就不需要建立临时表。
5.7以前,该项是explain partitions显示的选项; 5.7以后成为了默认选项.
select查询的序列号,包含一组数字,表示查询中执行select子句或者操作表的顺序 id号分为三种情况: 1、如果id相同,那么执行顺序从上到下 2、如果id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行 3、id相同和不同的,同时存在:相同的可以认为是一组,从上往下顺序执行,在所有组中,id值越大,优先级越高,越先执行
MySQL的最新版本8.0.22于2020年10月19日正式发行。这一版本里面有哪些变化,让我们快速浏览一下。
2NF 在满足1NF的基础上,在考虑此点。对记录的唯一性约束,同一张表不可能出现完全相同的记录。
使用 EXPLAIN 查看执行计划, 5.6后可以加参数 EXPLAIN FORMAT=JSON xxx输出json格式的信息。
前面文章,我们学习了 MySQL 慢日志相关内容,当我们筛选得到具体的慢 SQL 后,就要想办法去优化啦。优化 SQL 的第一步应该是读懂 SQL 的执行计划。本篇文章,我们一起来学习下 MySQL explain 执行计划相关知识。
根据表、列、索引和WHERE子句中的条件的详细信息,MySQL优化器考虑了许多技术来有效地执行SQL查询中涉及的查找。对一个巨大表的查询可以在不读取所有行的情况下执行;涉及多个表的联接可以在不比较每个行组合的情况下执行。「优化器选择执行最有效查询的操作集称为“查询执行计划(query execution plan)”,也称为EXPLAIN计划。」
MySql Explain是对SQL进行性能优化不可或缺的工具,通过他我们可以对SQL进行一定的分析和性能优化,降低线上业务因慢查询造成的性能损失。
先执行exlpain语句,EXPLAIN SELECT * from db,执行结果如下:
先执行exlpain语句,EXPLAIN SELECT * from film,执行结果如下:
上篇文章说了,mysql优化器会从cpu和io成本来考虑查询的消耗,possible key来计算全表和索引的成本,选择成本最小的,子查询有物化和semi-join半连接的方式优化,物化会优先哈希索引memory存储引擎,如果数据量太大会选择b+树。
前面的文章写过 MySQL 的事务和锁,这篇文章我们来聊聊 MySQL 的 Explain,估计大家在工作或者面试中多多少少都会接触过这个。可能工作中实际使用的不多,但是不论的自己学习还是面试,都需要掌握的。
MySQL EXPLAIN命令是查询性能优化不可缺少的一部分,该文主要讲解explain命令的使用及相关参数说明。
上面的参数是对所有存储引擎的表进行累计,下面参数是针对InnoDB存储引擎的,累加算法略有不同
MySQL EXPLAIN详解:http://www.jianshu.com/p/ea3fc71fdc45
通过上述参数可以了解当前DB应用是插入更新为主还是查询为主,以及各类的SQL执行比例。
EXPLAIN 工具能用于获取查询执行计划,即分析 MySQL 如何执行一个 SQL 语句。我们可以通过使用EXPLAIN 去模拟优化器执行 SQL 语句,从而分析 SQL 语句有没有使用索引、是否采用全表扫描方式、判断能否更进一步优化等。我们可以根据EXPLAIN 输出的数据来分析如何优化查询语句,提升查询语句的性能瓶颈。
SQL优化 通过show status命令了解各种sql的执行效率 查看本session的sql执行效率 show status like 'Com_%'; 查看全局的统计结果 SHOW GLOBAL STATUS LIKE 'Com_%' 查看服务器的状态 show global status; 结果 Com_select:执行select操作的次数,依次查询之累加1 Com_insert:执行insert操作的次数,对于批量插入的insert操作,只累加依次 Com_update:执行update操作
开启了MySQL慢查询日志之后,MySQL会自动将执行时间超过指定秒数的SQL统统记录下来,这对于搜罗线上慢SQL有很大的帮助。
前言 Mysql 8 正式发布了,新增了很多优秀特性,之后我会挑些重点来分享。 下面和大家一起熟悉下 CTE(Common Table Expressions)通用表表达式。 CTE 是什么 派生表大家都比较熟悉了,CTE 就是针对派生表来的,可以说是增强的派生表,或者说时派生表的替换。 派生表是 FROM 中的子查询,例如: SELECT ... FROM (subquery) AS derived, t1 ... CTE 就像派生表,但它的声明是在查询块儿之前,而不是在 FROM 中,例如: WITH
MySQL Hints是一组特殊的注释或指令,可以直接嵌入到SQL查询中,以改变MySQL优化器的默认行为。这些Hints通常被用于解决性能问题,或者当开发者比优化器更了解数据分布和查询特性时,来指导优化器选择更好的查询计划。
slow_launch_time:表示如果建立线程花费了比这个值更长的时间,slow_launch_threads 计数器将增加
最近面试过程中问了MySQL的Explain的使用,问了:Explain你最关注哪些字段?
MySQL 官方文档地址: 8.8 Understanding the Query Execution Plan
前面说了semi-join,这个是在where或者on语句后面,in里面,并且外层的条件必须用and与子查询连接,semi-join的作用就是,不管子查询有多少条数据返回,都不管,外层都只查询出来外层表数据,如果不符合条件,可以用物化表或者in变exists方法优化。还有派生表查询,可以内外合并,不行的话就物化查询。
optimizer_switch 是一个由多个标志组成的字符串,每个标志控制一个特定的优化器行为。这些标志可以被设置为 on 或 off,以启用或禁用相应的优化策略。通过调整这些标志,数据库管理员可以精细地控制查询优化器的行为,以达到最佳的性能表现。
在 select 语句之前增加 explain 关键字,MySQL 会在查询上设置一个标记,执行查询时,会返回执行计划的信息,而不是执行这条SQL(如果 from 中包含子查询,仍会执行该子查询,将结果放入临时表中)
Common Table Expression Common table expression简称CTE,由SQL:1999标准引入,可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。CTE 与派生表类似,具体表现在不存储为对象,并且只在查询期间有效。与派生表的不同之处在于,CTE 可自引用,还可在同一查询中引用多次。 目前支持CTE的数据库有Teradata, DB2, Firebird, Microsoft SQL
一条SQL被一个懵懂的少年,一阵蹂躏,扔向了MySQL服务器的尽头,少年苦苦等待,却迟迟等不来那满载而归的硕果。于是少年气愤,费尽苦心想从度娘那边寻求帮助,面对执行计划EXPLAIN,却等来的是无尽的折磨与抓狂。
本文将和大家分享 MySQL 更新语句的一些小众语法,及笔者在使用多表关联更新遇到的一些问题。
注意:本文基于mysql5.7进行操作,各个版本的mysql使用Explan会有微小的差异
首先我们要了解mysql查询优化器的执行效率,大约有10个,重点几个主要就是const,ref,range ,index,all。Const效率是最块的,成本可以忽略不计,主要通过主键或者唯一值查询的sql。还有比const更快的system,这种时候必须是mysql优化器内部精确计算查询成本,所以system不适用于innoDB,只适用于myISAM。Ref代表用的是索引b+tree查询的时候,比如用连接查询的时候,连接查询的条件是索引唯一值,这时候还分为eq-ref,er-ef是当被驱动表查询的是主键或者唯一二级索引的时候,这时候就是显示eq-ref。当连接表的条件是普通索引查询的时候,这时候显示就是ref,range顾名思义就是索引区间查询的时候,index代表查询覆盖索引的时候,all就是放弃索引全盘扫描了。
指出MySQL能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用(该查询可以利用的索引,如果没有任何索引显示 null)
领取专属 10元无门槛券
手把手带您无忧上云