精华内容
下载资源
问答
  • MySQL执行计划

    万次阅读 2020-06-24 00:48:46
    mysql执行计划 ​ 在企业的应用场景中,为了知道优化SQL语句的执行,需要查看SQL语句的具体执行过程,以加快SQL语句的执行效率。 ​ 可以使用explain+SQL语句来模拟优化器执行SQL查询语句,从而知道mysql是如何处理...

    mysql执行计划

    ​ 在企业的应用场景中,为了知道优化SQL语句的执行,需要查看SQL语句的具体执行过程,以加快SQL语句的执行效率。

    ​ 可以使用explain+SQL语句来模拟优化器执行SQL查询语句,从而知道mysql是如何处理sql语句的。

    ​ 官网地址: https://dev.mysql.com/doc/refman/5.5/en/explain-output.html

    1、执行计划中包含的信息

    Column Meaning
    id The SELECT identifier
    select_type The SELECT type
    table The table for the output row
    partitions The matching partitions
    type The join type
    possible_keys The possible indexes to choose
    key The index actually chosen
    key_len The length of the chosen key
    ref The columns compared to the index
    rows Estimate of rows to be examined
    filtered Percentage of rows filtered by table condition
    extra Additional information

    id

    select查询的序列号,包含一组数字,表示查询中执行select子句或者操作表的顺序

    id号分为三种情况:

    ​ 1、如果id相同,那么执行顺序从上到下

    explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal;
    

    ​ 2、如果id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行

    explain select * from emp e where e.deptno in (select d.deptno from dept d where d.dname = 'SALES');
    

    ​ 3、id相同和不同的,同时存在:相同的可以认为是一组,从上往下顺序执行,在所有组中,id值越大,优先级越高,越先执行

    explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal where e.deptno in (select d.deptno from dept d where d.dname = 'SALES');
    

    select_type

    主要用来分辨查询的类型,是普通查询还是联合查询还是子查询

    select_type Value Meaning
    SIMPLE Simple SELECT (not using UNION or subqueries)
    PRIMARY Outermost SELECT
    UNION Second or later SELECT statement in a UNION
    DEPENDENT UNION Second or later SELECT statement in a UNION, dependent on outer query
    UNION RESULT Result of a UNION.
    SUBQUERY First SELECT in subquery
    DEPENDENT SUBQUERY First SELECT in subquery, dependent on outer query
    DERIVED Derived table
    UNCACHEABLE SUBQUERY A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
    UNCACHEABLE UNION The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)
    --sample:简单的查询,不包含子查询和union
    explain select * from emp;
    
    --primary:查询中若包含任何复杂的子查询,最外层查询则被标记为Primary
    explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;
    
    --union:若第二个select出现在union之后,则被标记为union
    explain select * from emp where deptno = 10 union select * from emp where sal >2000;
    
    --dependent union:跟union类似,此处的depentent表示union或union all联合而成的结果会受外部表影响
    explain select * from emp e where e.empno  in ( select empno from emp where deptno = 10 union select empno from emp where sal >2000)
    
    --union result:从union表获取结果的select
    explain select * from emp where deptno = 10 union select * from emp where sal >2000;
    
    --subquery:在select或者where列表中包含子查询
    explain select * from emp where sal > (select avg(sal) from emp) ;
    
    --dependent subquery:subquery的子查询要受到外部表查询的影响
    explain select * from emp e where e.deptno in (select distinct deptno from dept);
    
    --DERIVED: from子句中出现的子查询,也叫做派生类,
    explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;
    
    --UNCACHEABLE SUBQUERY:表示使用子查询的结果不能被缓存
     explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);
     
    --uncacheable union:表示union的查询结果不能被缓存:sql语句未验证
    

    table

    对应行正在访问哪一个表,表名或者别名,可能是临时表或者union合并结果集
    1、如果是具体的表名,则表明从实际的物理表中获取数据,当然也可以是表的别名

    ​ 2、表名是derivedN的形式,表示使用了id为N的查询产生的衍生表

    ​ 3、当有union result的时候,表名是union n1,n2等的形式,n1,n2表示参与union的id

    type

    type显示的是访问类型,访问类型表示我是以何种方式去访问我们的数据,最容易想的是全表扫描,直接暴力的遍历一张表去寻找需要的数据,效率非常低下,访问的类型有很多,效率从最好到最坏依次是:

    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

    一般情况下,得保证查询至少达到range级别,最好能达到ref

    --all:全表扫描,一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。
    explain select * from emp;
    
    --index:全索引扫描这个比all的效率要好,主要有两种情况,一种是当前的查询时覆盖索引,即我们需要的数据在索引中就可以索取,或者是使用了索引进行排序,这样就避免数据的重排序
    explain  select empno from emp;
    
    --range:表示利用索引查询的时候限制了范围,在指定范围内进行查询,这样避免了index的全索引扫描,适用的操作符: =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN() 
    explain select * from emp where empno between 7000 and 7500;
    
    --index_subquery:利用索引来关联子查询,不再扫描全表
    explain select * from emp where emp.job in (select job from t_job);
    
    --unique_subquery:该连接类型类似与index_subquery,使用的是唯一索引
     explain select * from emp e where e.deptno in (select distinct deptno from dept);
     
    --index_merge:在查询过程中需要多个索引组合使用,没有模拟出来
    
    --ref_or_null:对于某个字段即需要关联条件,也需要null值的情况下,查询优化器会选择这种访问方式
    explain select * from emp e where  e.mgr is null or e.mgr=7369;
    
    --ref:使用了非唯一性索引进行数据的查找
     create index idx_3 on emp(deptno);
     explain select * from emp e,dept d where e.deptno =d.deptno;
    
    --eq_ref :使用唯一性索引进行数据查找
    explain select * from emp,emp2 where emp.empno = emp2.empno;
    
    --const:这个表至多有一个匹配行,
    explain select * from emp where empno = 7369;
     
    --system:表只有一行记录(等于系统表),这是const类型的特例,平时不会出现
    

    possible_keys

    ​ 显示可能应用在这张表中的索引,一个或多个,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用

    explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;
    

    key

    ​ 实际使用的索引,如果为null,则没有使用索引,查询中若使用了覆盖索引,则该索引和查询的select字段重叠。

    explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;
    

    key_len

    表示索引中使用的字节数,可以通过key_len计算查询中使用的索引长度,在不损失精度的情况下长度越短越好。

    explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;
    

    ref

    显示索引的哪一列被使用了,如果可能的话,是一个常数

    explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;
    

    rows

    根据表的统计信息及索引使用情况,大致估算出找出所需记录需要读取的行数,此参数很重要,直接反应的sql找了多少数据,在完成目的的情况下越少越好

    explain select * from emp;
    

    extra

    包含额外的信息。

    --using filesort:说明mysql无法利用索引进行排序,只能利用排序算法进行排序,会消耗额外的位置
    explain select * from emp order by sal;
    
    --using temporary:建立临时表来保存中间结果,查询完成之后把临时表删除
    explain select ename,count(*) from emp where deptno = 10 group by ename;
    
    --using index:这个表示当前的查询时覆盖索引的,直接从索引中读取数据,而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找,如果没有,表面索引被用来读取数据,而不是真的查找
    explain select deptno,count(*) from emp group by deptno limit 10;
    
    --using where:使用where进行条件过滤
    explain select * from t_user where id = 1;
    
    --using join buffer:使用连接缓存,情况没有模拟出来
    
    --impossible where:where语句的结果总是false
    explain select * from emp where empno = 7469;
    
    展开全文
  • Mysql执行计划和Mysql优化

    万次阅读 2020-12-05 10:19:56
    执行计划是什么:使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。 作用:分析你的查询语句或是表结构的性能瓶颈。 语法:Explain + SQL语句 执行计划输出内容介绍:表的...

    1. 执行计划

    1.1. 执行计划概念

    执行计划是什么:使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。
    作用:分析你的查询语句或是表结构的性能瓶颈。
    语法:Explain + SQL语句

    执行计划输出内容介绍:表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用、每张表有多少行被优化器查询。

    执行计划包含的信息:
    在这里插入图片描述

    1.2. 执行计划各字段详解

    1.2.1. 执行计划-ID

    select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序

    规则:
    id相同,执行顺序由上至下
    id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
    id相同不同,同时存在

    第一种情况 Id相同
    在这里插入图片描述
    第二种情况 Id不同
    在这里插入图片描述
    如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行。

    第三种情况 Id相同又不同
    在这里插入图片描述
    id如果相同,可以认为是一组,从上往下顺序执行;
    在所有组中,id值越大,优先级越高,越先执行

    1.2.2. 执行计划-select_type

    查询的类型,主要是用于区别普通查询、联合查询、子查询等复杂查询。

    在这里插入图片描述
    具体描述
    在这里插入图片描述
    例子:

    Simple:简单的 select 查询,查询中不包含子查询或者UNION
    在这里插入图片描述
    PRIMARY:查询中若包含任何复杂的子部分,最外层查询则被标记为
    SUBQUERY:在SELECT或WHERE列表中包含了子查询
    EXPLAIN
    select t1.,(select t2.id from t2 where t2.id = 1 ) from t1
    在这里插入图片描述
    在FROM列表中包含的子查询被标记为DERIVED(衍生)
    MySQL会递归执行这些子查询, 把结果放在临时表里。
    select t1.
    from t1 ,(select t2.* from t2 where t2.id = 1 ) s2 where t1.id = s2.id
    在这里插入图片描述

    1.2.3. 执行计划-table

    显示这一行的数据是关于哪张表的
    在这里插入图片描述

    1.2.4. 执行计划-type

    type显示的是访问类型,是较为重要的一个指标,结果值从最好到最坏依次是:
    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

    需要记忆的 system > const > eq_ref > ref > range > index > ALL

    **System:**表只有一行记录(等于系统表),这是const类型的特列,平时不会出现,这个也可以忽略不计。

    Const:表示通过索引一次就找到了。
    Const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快
    如将主键置于where列表中,MySQL就能将该查询转换为一个常量
    在这里插入图片描述
    读取本表中和关联表表中的每行组合成的一行。除 了 system 和 const 类型之外, 这是最好的联接类型。当连接使用索引的所有部分时, 索引是主键或唯一非 NULL 索引时, 将使用该值。
    eq_ref 可用于使用 = 运算符比较的索引列。比较值可以是常量或使用此表之前读取的表中的列的表达式。
    在这里插入图片描述
    Ref:非唯一性索引扫描(二级索引),返回匹配某个单独值的所有行。
    本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体。
    在这里插入图片描述
    Range:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引
    一般就是在你的where语句中出现了between、<、>、in等的查询。
    这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束语另一点,不用扫描全部索引。

    在这里插入图片描述
    Index:当查询的结果全为索引列的时候,虽然也是全部扫描,但是只查询的索引库,而没有去查询数据。
    在这里插入图片描述
    All:Full Table Scan,将遍历全表以找到匹配的行。
    在这里插入图片描述

    1.2.5. 执行计划-possible_keys 与 key

    possible_keys:可能使用的key索引。
    Key:实际使用的索引。如果为NULL,则表示没有使用索引。

    查询中若使用了覆盖索引,则该索引和查询的select字段重叠。

    在这里插入图片描述
    在这里插入图片描述
    表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好。
    key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。

    根据key_len这个值,就可以判断索引使用情况,特别是在组合索引的时候,判断所有的索引字段是否都被查询用到。
    char和varchar跟字符编码也有密切的联系,
    latin1占用1个字节,gbk占用2个字节,utf8占用3个字节。(不同字符编码占用的存储空间不同)

    1.2.6. 执行计划-key_len

    在这里插入图片描述
    字符类型
    在这里插入图片描述
    字符类型-索引字段为char类型+不可为Null时
    在这里插入图片描述
    字符类型-索引字段为char类型+允许为Null时
    在这里插入图片描述
    索引字段为varchar类型+不可为Null时
    在这里插入图片描述
    索引字段为varchar类型+允许为Null时
    在这里插入图片描述
    varchar(n)变长字段+允许Null=n*(utf8=3,gbk=2,latin1=1)+1(NULL)+2

    在这里插入图片描述
    日期和时间
    在这里插入图片描述
    datetime类型在5.6中字段长度是5个字节,
    datetime类型在5.5中字段长度是8个字节

    整数/浮点数/时间类型的索引长度
    NOT NULL=字段本身的字段长度
    NULL=字段本身的字段长度+1(因为需要有是否为空的标记,这个标记需要占用1个字节)

    datetime类型在5.6及其以后版本中字段长度是5个字节,datetime类型在5.5中字段长度是8个字节

    key_len 总结:
    变长字段需要额外的2个字节(VARCHAR值保存时只保存需要的字符数,另加一个字节来记录长度(如果列声明的长度超过255,则使用两个字节),所以VARCAHR索引长度计算时候要加2),固定长度字段不需要额外的字节。

    而NULL都需要1个字节的额外空间,所以索引字段最好不要为NULL,因为NULL让统计更加复杂并且需要额外的存储空间。

    复合索引有最左前缀的特性,如果复合索引能全部使用上,则是复合索引字段的索引长度之和,这也可以用来判定复合索引是否部分使用,还是全部使用。

    1.2.7. 执行计划-ref

    Ref:显示索引的哪一列被使用了,如果可能的话,是一个常量。哪些列或常量被用于查找索引列上的值。
    在这里插入图片描述
    由key_len可知t1表的idx_col1_col2被充分使用,col1匹配t2表的col1,col2匹配了一个常量,即 ‘ac’。

    1.2.8. 执行计划-rows

    根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数。
    在这里插入图片描述

    1.2.9. 执行计划-Extra

    Extra:包含不适合在其他列中显示但十分重要的额外信息
    在这里插入图片描述
    Using filesort
    说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。
    MySQL中无法利用索引完成的排序操作称为“文件排序”
    在这里插入图片描述
    在这里插入图片描述
    Using temporary
    使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

    在这里插入图片描述
    在这里插入图片描述
    USING index
    表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错!
    如果同时出现using where,表明索引被用来执行索引键值的查找;

    在这里插入图片描述
    如果没有同时出现using where,表明索引用来读取数据而非执行查找动作
    在这里插入图片描述
    USING index
    Using where
    表明使用了where过滤

    2. 执行计划

    2.1. sql优化实战

    2.1.1. 尽量全值匹配

    EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
    EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
    EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';
    
    

    在这里插入图片描述

    2.1.2. 最佳左前缀法则-最左匹配

    如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
    在这里插入图片描述

    2.1.3. 不在索引列上做任何操作

    不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
    在这里插入图片描述

    EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = 'July';
    

    2.1.4. 范围条件放最后

    存储引擎不能使用索引中范围条件右边的列
    在这里插入图片描述

    2.1.5. 覆盖索引尽量用

    尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *
    在这里插入图片描述

    2.1.6. 不等于要甚用

    mysql 在使用不等于(!= 或者<>)的时候有可能无法使用索引会导致全表扫描。
    在这里插入图片描述

    2.1.7. Like查询要当心

    like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作。
    在这里插入图片描述

    2.1.8. 字符类型加引号

    字符串不加单引号索引失效。在这里插入图片描述

    2.1.9. OR改UNION效率高

    在这里插入图片描述

    2.2. Mysql架构层面优化

    2.2.1 硬件方面的优化

    mysql服务器端增大并发的连接数。如果服务器端并发连接数不够,可以增加并发连接数的配置。

    show variables like '%max_connections%'
    

    在这里插入图片描述

    2.2.2 集群

    1. 可以做基于主从复制
      在这里插入图片描述
      主从复制+读写分离原理:
      在这里插入图片描述
      在这里插入图片描述

    2. 基于mycat或者shading做都写分离
      基于mycat

    cat schema.xml 
    <?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
    
            <schema name="BLADEX" checkSQLschema="true" sqlMaxLimit="100" dataNode="bladex"/>
            <schema name="BLADEX_FLOW" checkSQLschema="true" sqlMaxLimit="100" dataNode="bladex_flow"/>
            <schema name="XXL_JOB" checkSQLschema="true" sqlMaxLimit="100" dataNode="xxl_job"/>
            <dataNode name="bladex" dataHost="bladex" database="bladex" />
            <dataNode name="bladex_flow" dataHost="bladex_flow" database="bladex_flow" />
            <dataNode name="xxl_job" dataHost="xxl_job" database="xxl_job" />
    
            <dataHost name="bladex" maxCon="1000" minCon="10" balance="1"
                              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
                    <heartbeat>select user()</heartbeat>
                    <!-- can have multi write hosts -->
                    <writeHost host="bladexM1" url="jdbc:mysql://10.1.33.21:3306?useSSL=false" user="root" password="111.com">
                            <readHost host="bladexS1" url="jdbc:mysql://10.1.33.26:3306?useSSL=false" user="root" password="111.com"/>
                    </writeHost>
                    <writeHost host="bladexM2" url="jdbc:mysql://10.1.33.22:3306?useSSL=false" user="root" password="111.com">
                            <readHost host="bladexS2" url="jdbc:mysql://10.1.33.27:3306" user="root" password="123.com"/>
                    </writeHost>
            </dataHost>
            <dataHost name="bladex_flow" maxCon="1000" minCon="10" balance="1"
                              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
                    <heartbeat>select user()</heartbeat>
                    <!-- can have multi write hosts -->
                    <writeHost host="bladex_flowM1" url="jdbc:mysql://10.1.33.21:3306?useSSL=false" user="root" password="111.com">
                            <readHost host="bladex_flowS1" url="jdbc:mysql://10.1.33.26:3306?useSSL=false" user="root" password="123.com"/>
                    </writeHost>
                    <writeHost host="bladex_flowM2" url="jdbc:mysql://10.1.33.22:3306?useSSL=false" user="root" password="111.com">
                            <readHost host="bladex_flowS2" url="jdbc:mysql://10.1.33.27:3306?useSSL=false" user="root" password="123.com"/>
                    </writeHost>
            </dataHost>
            <dataHost name="xxl_job" maxCon="1000" minCon="10" balance="1"
                              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
                    <heartbeat>select user()</heartbeat>
                    <!-- can have multi write hosts -->
                    <writeHost host="xxl_jobM1" url="jdbc:mysql://10.1.33.21:3306?useSSL=false" user="root" password="1111">
                            <readHost host="xxl_jobS1" url="jdbc:mysql://10.1.33.26:3306?useSSL=false" user="root" password="1111"/>
                    </writeHost>
                    <writeHost host="xxl_jobM2" url="jdbc:mysql://10.1.33.22:3306?useSSL=false" user="root" password="1111.com">
                            <readHost host="xxl_jobS2" url="jdbc:mysql://10.1.33.27:3306?useSSL=false" user="root" password="1111.com"/>
                    </writeHost>
            </dataHost>
    </mycat:schema>
    
    

    或者基于shading也是可以的。

    1. 分库分表
      每个微服务一个库
      单个表达可以分表

    单个数据库或者表数据量过大,那么可以选择分库分表的方式去优化。
    垂直分库
    在这里插入图片描述
    水平方向的的分库分表在这里插入图片描述

    展开全文
  • 如何查看MySQL执行计划(Explain)

    万次阅读 2020-06-28 23:25:27
    我们经常会使用Explain去查看执行计划,这个众所周知。但我在面试时问面试者,你用Explain主要是看什么?对方的回答大多是“查看是否有使用到索引”,很显然我对这个回答不太满意。 今天我们就来说一说Explain的详细...

    我们经常会使用Explain去查看执行计划,这个众所周知。但我在面试时问面试者,你用Explain主要是看什么?对方的回答大多是“查看是否有使用到索引”,很显然我对这个回答不太满意。
    今天我们就来说一说Explain的详细用法。

    1 查看执行计划命令

    explain + SQL
    

    example:

    explain  SELECT * FROM billing_item_dis WHERE item_name='粪便常规'
    

    在这里插入图片描述

    2 执行计划中各个字段的含义

    2.1 id

    表示执行顺序,id的数字越大越先执行,如果数字一样,那么从上往下依次执行,如果为null表示这是一个结果集,不需要用它来进行查询。

    2.2 select_type

    查询的类型,主要是用于区分普通查询、联合查询、子查询等复杂的查询

    取值 含义
    simple 简单的select查询,查询中不包含子查询或者union
    primary 查询中包含任何复杂的子部分,最外层查询则被标记为primary
    union 若第二个select出现在union之后,则被标记为union;若union包含在from子句的子查询中,外层select将被标记为derived
    dependent union 与union一样,出现在union 或union all语句中,但是这个查询要受到外部查询的影响
    union result 包含union的结果集,在union和union all语句中,因为它不需要参与查询,所以id字段为null
    subquery 在select 或 where列表中包含了子查询
    dependent subquery 与dependent union类似,表示这个subquery的查询要受到外部表查询的影响
    derived 在from列表中包含的子查询被标记为derived(衍生),mysql或递归执行这些子查询,把结果放在临时表里

    2.3 table

    显示的查询表名,如果查询使用了别名,那么这里显示的是别名,如果不涉及对数据表的操作,那么这显示为null,如果显示为尖括号括起来的就表示这个是临时表,后边的N就是执行计划中的id,表示结果来自于这个查询产生。如果是尖括号括起来的<union M,N>,与类似,也是一个临时表,表示这个结果来自于union查询的id为M,N的结果集。

    2.4 type

    MySQL的官网解释为:连接类型(the join type)。它描述了找到所需数据使用的扫描方式。
    访问类型,sql查询优化中一个很重要的指标,结果值从好到坏依次是:
    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,除了all之外,其他的type都可以使用到索引,除了index_merge之外,其他的type只可以用到一个索引

    2.4.1 扫描方式汇总

    取值 含义
    system 系统表,少量数据,往往不需要进行磁盘IO
    const 常量连接
    eq_ref 主键索引(primary key)或者非空唯一索引(unique not null)等值扫描
    ref 非主键非唯一索引等值扫描
    ref_or_null 与ref方法类似,只是增加了null值的比较。实际用的不多
    range 范围扫描
    index 索引树扫描
    index_merge 表示查询使用了两个以上的索引,最后取交集或者并集
    fulltext 全文索引检索
    unique_subquery 用于where中的in形式子查询,子查询返回不重复值唯一值
    index_subquery 用于in形式子查询,子查询可能返回重复值,可以使用索引将子查询去重
    ALL 全表扫描(full table scan)

    2.4.2 扫描方式详解

    2.4.2.1 system

    扫码类型为system,说明数据已经加载到内存里,不需要进行磁盘IO。
    这类扫描是速度最快的。但是我没有遇到过,遇到了我再来补充!

    2.4.2.2 const

    explain select id from billing_item_dis where id =1;
    

    const扫描的条件为:

    1. 命中主键(primary key)或者唯一(unique)索引;
    2. 被连接的部分是一个常量(const)值;

    2.4.2.3 eq_ref

    eq_ref扫描的条件为:对于前表的每一行(row),后表只有一行被扫描。
    出现在要连接过个表的查询计划中,驱动表只返回一行数据,且这行数据是第二个表的主键或者唯一索引,且必须为not null,唯一索引和主键是多列时,只有所有的列都用作比较时才会出现eq_ref

    2.4.2.4 ref

    explain select * from billing_item_dis t1,billing_item_reslut t2 where t1.id = t2.binli_id;
    

    对于前表的每一行(row),后表可能有多于一行的数据被扫描。
    不像eq_ref那样要求连接顺序,也没有主键和唯一索引的要求,只要使用相等条件检索时就可能出现,常见与辅助索引的等值查找。或者多列主键、唯一索引中,使用第一个列之外的列作为等值查找也会出现,总之,返回数据不唯一的等值查找就可能出现

    2.4.2.5 range

    explain select * from billing_item_dis where id > 4;
    

    索引范围扫描,常见于使用>,<,is null,between ,in ,like等运算符的查询中。

    2.4.2.6 index

    explain select id from billing_item_dis;
    

    索引全表扫描,把索引从头到尾扫一遍,常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。

    2.4.2.7 all

    explain select * from billing_item_dis;
    

    这个就是全表扫描数据文件,然后再在server层进行过滤返回符合要求的记录。

    2.5 possible_keys

    查询涉及到的字段上存在索引,则该索引将被列出,但不一定被查询实际使用

    2.6 key

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

    2.7 key_len

    用于处理查询的索引长度,如果是单列索引,那就整个索引长度算进去,如果是多列索引,那么查询不一定都能使用到所有的列,具体使用到了多少个列的索引,这里就会计算进去,没有使用到的列,这里不会计算进去。留意下这个列的值,算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意,mysql的ICP特性使用到的索引不会计入其中。另外,key_len只计算where条件用到的索引长度,而排序和分组就算用到了索引,也不会计算到key_len中。

    2.8 ref

    如果是使用的常数等值查询,这里会显示const,如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段,如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func

    2.9 rows

    根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数

    2.10 Extra

    取值 含义 举例
    Using where Extra为Using where说明,
    SQL使用了where条件过滤数据。
    explain select * from billing_item_dis where id > 4;
    Using index Extra为Using index说明,
    SQL所需要返回的所有列数据均在一棵索引树上,
    而无需访问实际的行记录。
    explain select id from billing_item_dis;
    Using index condition Extra为Using index condition说明,
    确实命中了索引,但不是所有的
    列数据都在索引树上,还需要访问实际的行记录。
    explain select * from billing_item_dis t1, billing_item_result t2 where t1.user_id = t2.id;
    Using filesort Extra为Using filesort说明,得到所需结果集,
    需要对所有记录进行文件排序。典型的,在一个没有建立索引的列上进行了order by,就会触发,常见的优化方案是,在order by的列上添加索引,避免每次查询都全量排序。
    explain select id from billing_item_dis order by item_name;
    Using temporary Extra为Using temporary说明,
    需要建立临时表(temporary table)来暂存中间结果。
    这类SQL语句性能较低,往往也需要进行优化。
    典型的,group by和order by同时存在,且作用于不同的字段时,就会建立临时表,以便计算出最终的结果集。
    explain select item_name, COUNT(*) from billing_item_dis GROUP BY item_name order by item_name;
    展开全文
  • 解决DBeaver无法查看MySQL执行计划问题 DBeaver这个数据库连接工具的确很好用,但是最近在使用时发现它不能查看MySQL的执行计划 explain execute select * from table; explain select * from table; /**点 ...

    解决DBeaver无法查看MySQL执行计划问题

    DBeaver这个数据库连接工具的确很好用,但是最近在使用时发现它不能查看MySQL的执行计划

    	explain execute select * from table;
    	explain select * from table;
    	/**点 解析执行计划按钮*/
    	explain extended select * from table
    

    提示内容为:“sql is not a supported statement”
    经过查看DBeaver的源码找到了如下代码

    public class MySQLDataSource extends JDBCDataSource implements DBSObjectSelector, DBCQueryPlanner {
    	@Override
    	protected Connection openConnection(@NotNull DBRProgressMonitor monitor, JDBCRemoteInstance remoteInstance, @NotNull String purpose)
    			throws DBCException {
    		Connection mysqlConnection = super.openConnection(monitor, remoteInstance, purpose);
    
    		if (!getContainer().getPreferenceStore().getBoolean(ModelPreferences.META_CLIENT_NAME_DISABLE)) {
    			// Provide client info
    			try {
    				// 注意这一行设置了 ApplicationName 信息
    				mysqlConnection.setClientInfo("ApplicationName", DBUtils.getClientApplicationName(getContainer(), purpose));
    			} catch (Throwable e) {
    				// just ignore
    				log.debug(e);
    			}
    		}
    		return mysqlConnection;
    	}
    }
    

    注意这行代码

    mysqlConnection.setClientInfo("ApplicationName", DBUtils.getClientApplicationName(getContainer(), purpose));
    

    该行代码设置了 ApplicationName 属性,设置该属性后,发送给服务端的SQL前面都会带上一个类似于 “/* ApplicationName=DBeaver 5.1.4 - Main */ ”的信息,这个信息会导致数据库抛出“sql is not a supported statement”

    解决思路:

    不设置 ApplicationName,即 getContainer().getPreferenceStore().getBoolean(ModelPreferences.META_CLIENT_NAME_DISABLE) 结果为 true,让 if 的判断结果为 false ,这样就跳过了设置语句。 经查找相关配置项,发现 META_CLIENT_NAME_DISABLE 为“禁用客户端身份识别”。

    解决方案:
        在【首选项->数据库->连接】中勾选“禁用客户端身份识别”
    

    禁用以后再SQL编辑器中执行 explain execute select ... 就可以查看执行计划了

    注:点击【解析执行计划】按钮后,DBeaver生成的执行计划语句为:explain extended select ...,extended关键字在某些MySQL版本(例如:MySQL 5.6.29)中已经不被支持

    转载于:https://my.oschina.net/dush/blog/1976008

    展开全文
  • 2018/3/19 初次分析explainmysql执行计划分析主要从一下几个关键字入手:1.idid标识sql语句中表的执行顺序,id越大越先执行;如果id相同,按照从上到下的顺序执行;如果id为null,表示当列是一个结果集。2.select_...
  • [数据库] ------ mysql 执行计划

    千次阅读 2018-12-21 20:59:06
    mysql 执行计划 简单来说,mysql整体架构分为三块:应用层,逻辑层,物理层 应用层:负责与客户端交互,建立连接,返回数据,响应请求。 逻辑层:负责查询处理,事务管理等 物理层:实际物理磁盘上存储的文件,主要...
  • MySql执行计划的查看

    千次阅读 2019-06-20 11:47:20
    什么是数据库执行计划: 利用一个SQL语句, 你可能要Server取出所有News表中的信息. 当Server收到的这条SQL的时候, 第一件事情并不是解析它. 如果这条SQL没有语法错误, Server才会继续工作. Server会决定最好的计算...
  • 查看Mysql执行计划

    千次阅读 2019-10-09 14:07:27
    :Query Optimizer 所选定的执行计划中查询的序列号; table :显示这一行的数据是关于哪张表的 2、type 显示连接使用了何种类型,对表所使用的访问方式。从最好到最差的连接类型为const、eq_reg、ref、range...
  • MySQL执行计划详解(优化sql语句)

    千次阅读 2018-07-20 11:46:22
    执行计划是什么?...在MySQL使用 explain 关键字来查看SQL的执行计划。如下所示:   1 2 3 4 5 //1. 查询t_base_user select * from t_base_user where name=...
  • MySQL执行计划理解与实践

    千次阅读 2016-05-17 13:43:35
    SQL执行过程和优化器首先看一下MySQL中,一条sql的执行过程,这里主要是引用了《高性能MySQL》中的内容:1、客户端发送一条...4、MYSQL根据优化器生成的执行计划调用存储引擎的API来执行查询 5、将结果返回给客户端
  • 6.读懂mysql执行计划

    千次阅读 2019-06-28 23:51:49
    执行计划概念和语法1.执行计划的概念2.执行计划的语法1.常规执行计划语法2.扩展执行计划的语法3.分区表的执行计划语法2.执行计划包含的信息1.id​:查询的顺序2.select_type:查询类型3.table:查询涉及到的表4.type...
  • MySQL执行计划 type类型

    千次阅读 2019-01-18 14:01:00
    MySql提供了EXPLAIN语法用来进行查询分析,在SQL语句前加一个"EXPLAIN"即可。 explain select * from t_settlement_settle_order_detail where id = 2; 下面来介绍各个字段: type:连接类型  system:...
  • 查看mysql执行计划以及各个参数

    千次阅读 2017-03-18 23:30:23
    MySQL的EXPLAIN命令用于SQL语句的查询执行计划(QEP)。输出结果可以让我们了解MySQL 优化器是如何执行 SQL 语句的。这条命令不会提供任何建议方案,能够提供重要信息进行调优 如EXPLAIN SELECT * FROM s1 以s1表为...
  • explain select * from student s where s. classid = (select id from classes where classno='2017001');

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,038,803
精华内容 415,521
关键字:

mysql执行计划

mysql 订阅