小工具      在线工具  汉语词典  css  js  c++  java

Mysql的Expalin执行计划

Java 额外说明

收录于:97天前

当数据量太大时,即使有索引,也不会使用索引,而是使用全表索引。

原因:数据量太大。建立索引所花费的时间比搜索整个表所花费的时间要长。因此,MySQL会优化执行计划,进行全表扫描。

1. Expalin中关键字的解释

选择类型

查询类型主要用于区分普通查询、联合查询、子查询和其他复杂查询。

1、简单的:简单的select查询,查询中不包含子查询或者union
2、基本的:查询中包含任何复杂的子部分,最外层查询则被标记为primary
3、子查询:在select 或 where列表中包含了子查询
4、衍生的:在from列表中包含的子查询被标记为derived(衍生),mysql或递归执行这些子查询,把结果放在零时表里
5、联盟:若第二个select出现在union之后,则被标记为union;若union包含在from子句的子查询中,外层select将被标记为derived
6、联盟结果:从union表获取结果的select

类型

访问类型是SQL查询优化中非常重要的指标。结果值从最好到最差是:

系统 > 常量 > 等式引用 > 参考 > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > 范围 > 指数 > 全部

一般来说,一个好的SQL查询至少应该达到range级别,最好达到ref级别。

1、系统:表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,可以忽略不计

2、常量:表示通过索引一次就找到了,const用于比较primary key 或者 unique索引。因为只需匹配一行数据,所有很快。如果将主键置于where列表中,mysql就能将该查询转换为一个const

3、等式引用:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。

注意:ALL全表扫描会扫描记录最少的表,例如t1表。

4、参考:非唯一性索引扫描,返回匹配某个单独值的所有行。本质是也是一种索引访问,它返回所有匹配某个单独值的行,然而他可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体

5、范围:只检索给定范围的行,使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点,结束于另一个点,不用扫描全部索引

6、指数:Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常为ALL块,应为索引文件通常比数据文件小。(Index与ALL虽然都是读全表,但index是从索引中读取,而ALL是从硬盘读取)

7、全部:Full Table Scan,遍历全表以找到匹配的行

可能的键

如果查询涉及的字段存在索引,则该索引将被列出,但可能不会被查询实际使用。

key

实际使用的索引,如果为NULL,则没有使用索引。
如果查询中使用了覆盖索引,则该索引只出现在键列表中

密钥长度

表示索引中使用的字节数和查询中使用的索引长度(可能的最大长度),而不是实际使用的长度。理论上来说,长度越短越好。 key_len是根据表定义计算的,而不是从表中检索

ref

如果可能,将显示索引的列用作常量 const。

rows

根据表统计信息和索引选择,粗略估计需要读取的行数才能找到所需的记录。

Extra

不适合在其他领域展示但非常重要的附加信息

1、使用文件排序
mysql对数据使用一个外部的索引排序,而不是按照表内的索引进行排序读取。也就是说mysql无法利用索引完成的排序操作成为“文件排序”

由于索引是先按邮箱排序,再按地址排序,如果查询时直接按地址排序,索引无法满足要求。 MySQL内部必须再次实现“文件排序”。

2、使用临时
使用临时表保存中间结果,也就是说mysql在对查询结果排序时使用了临时表,常见于order by 和 group by

3、使用索引
表示相应的select操作中使用了覆盖指数(Covering Index),避免了访问表的数据行,效率高
如果同时出现Using where,表明索引被用来执行索引键值的查找(参考上图)
如果没用同时出现Using where,表明索引用来读取数据而非执行查找动作

覆盖指数(Covering Index):也叫索引覆盖。就是select列表中的字段,只用从索引中就能获取,不必根据索引再次读取数据文件,换句话说查询列必须被创建的索引覆盖
注意:
a、如需使用覆盖索引,select列表中的字段只取出需要的列,不要使用select *
b、如果将所有字段都建索引会导致索引文件过大,反而降低crud性能

4、Using where :
使用了where过滤

5、Using join buffer :
使用了链接缓存

6、Impossible WHERE:
where子句的值总是false,不能用来获取任何元祖

7、select tables optimized away:
在没有group by子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段在进行计算,查询执行计划生成的阶段即可完成优化

8、distinct:
优化distinct操作,在找到第一个匹配的元祖后即停止找同样值得动作
 

问题找到了,总结如下:“MySQL优化器认为在limit 1的情况下,主键索引可以更快的找到那条数据,如果使用联合索引,则需要将索引扫描排序了,主键索引自然是有序的,所以优化器综合考虑,把主键索引去掉了,实际上MySQL遍历了8000万条数据,依然找不到选中的那条(合格数据),所以浪费了很多时间。”

2. MySQL优化器(强制索引,强制索引(PRIMARY))

首先要知道的是,选择索引是MySQL优化器的工作。

优化器选择索引的目的是找到最优的执行计划,以最小的成本执行语句。在数据库中,扫描行数是影响执行成本的因素之一。扫描的行数更少,这意味着磁盘数据访问更少,消耗的 CPU 资源也更少。

“当然,扫描的行数并不是唯一标准,优化器还会根据是否使用临时表、是否排序等因素进行综合判断。”

我们再回顾一下刚才的两张解释截图:

使用“主键索引”查询语句时,预估行数为1833,而强制“联合索引”行数为45640,并且Extra信息显示需要Using filesort进行额外排序。因此,在没有强制索引的情况下,“优化器选择主键索引是因为感觉主键索引扫描的行数较少,并且不需要额外的排序操作。主键索引自然是有序的”。

Explain比较重要的字段有:

  • select_type:查询类型,包括简单查询、联合查询、子查询等。
  • key :要使用的索引
  • rows:预期扫描的行数

possible_keys 我们期望使用的索引

实际使用的关键索引

eq_ref:唯一索引扫描,对于每个索引键,表中只有一条记录与其匹配。常见于主键或唯一索引扫描

显示查询使用的类型,从最好到最差:system>const>eq_ref>ref>range>index>ALL

explain2 datanode=trade_dn1 sql=select amount.amountId, amount.code, amount amount LEFT JOIN order order力量指数(初级) ON amount.orderId = order.orderId

其中 1=1 且 order.companyCode = '51757' 且 order.inputDate > '2020-08-25 00:00:00' 且 order.inputDate <= '2020-11-24 23:59:59'

和 amount.pay = '平台' ORDER BY amount.amountId desc limit 0,10

参考:https://blog.csdn.net/drdongshiye/article/details/84546264

https://mp.weixin.qq.com/s/4QXfzDKd6W5Sy-ktFl1Ivg

. . .

相关推荐

额外说明

使用Redis Hash存储Java对象

Hash的特点 1.将具有同一类规则的数据放到redis中的一个数据容器里,便于查找数据。 2.使用hash 省内存。在hash类型中,一个key可以对应多个field,一个field对应一个value。将一个对象存储为hash类型的好处之一:较于每个字

额外说明

SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column ‘url‘ at row 1

问题:   SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'url' at row 1     原因是:插入字段长度超过设定的长度 解决方法:  

额外说明

高级DBA教你Mysql中IF的使用以及与子查询结合的实际应用,加上实用技巧。

高级DBA教你Mysql中IF的使用并与子查询联合运用实战 一、IF表达式的基础用法 IF #表达式 IF(expr1 , expr2 , expr3) expr1的值为TRUE,则返回值为expr2 expr1的值为FALSE,则返回值为exp

额外说明

MyBatis介绍,缓存使用及分页、XML映射文件解析

1、什么是MyBatis? MyBatis是一个可以自定义SQL、存储过程和高级映射的持久层框架。 2、讲下MyBatis的缓存 MyBatis的缓存分为一级缓存和二级缓存,一级缓存放在session里面,默认就有,二级缓存放在它的命名空间里,默认是不打

额外说明

解决java.nio.file.AccessDeniedException: Permission denied

解决java.nio.file.AccessDeniedException: Permission denied 摘要 引言 正文 1. 理解异常的根本原因 2. 检查文件权限 3. 处理文件被锁定 4. 提升权限或更改文件所有者 5. 异常处理 总结

额外说明

Python爬虫 随机请求头

user_agent = [ "Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10_6_8; en-us) AppleWebKit/534.50 (KHTML, like Gecko) Version/5.1

额外说明

[C#]使用Costura.Fody将源DLL合并到目标EXE

原文链接 一、本文主要是使用Costura.Fody工具将源DLL合并到目标EXE,因此,需要从以下任一链接下载: ①从Github地址下载: https://github.com/Fody/Costura/releases ②从百度网盘下载: htt

额外说明

【Java 进阶篇】CSS盒子模型详解

CSS盒子模型是网页布局的基础之一,它定义了HTML元素在页面上的占用空间和相互关系。理解CSS盒子模型对于构建各种类型的网页布局至关重要。在本文中,我们将深入探讨CSS盒子模型的各个方面,包括盒子模型的概念、属性和如何使用它们来控制元素的大小和间距。

额外说明

PHP或者JavaScript获取当前页面完整URL的方法

使用PHP: #不带参数 $url='http://'.$_SERVER['HTTP_HOST'].$_SERVER['REQUEST_URI']; #带参数 $url2='http://'.$_SERVER['HTTP_HOST'].$_SERVER

额外说明

如何在一页上显示所有WordPress帖子

您想在一页上显示所有 WordPress 帖子吗?最近,我们的一位读者想要创建一个档案页面并在单个页面上显示所有 WordPress 帖子。在本文中,我们将向您展示如何在一个页面上显示所有 WordPress 帖子而不分页。 您想在一页上显示所有 Wor

ads via 小工具