精华内容
下载资源
问答
  • 2018-07-20 11:46:22

    执行计划是什么?

    执行计划,简单的来说,是SQL在数据库中执行时的表现情况,通常用于SQL性能分析,优化等场景。在MySQL使用 explain 关键字来查看SQL的执行计划。如下所示:

     

    1

    2

    3

    4

    5

    //1. 查询t_base_user

    select * from t_base_user where name="andyqian";

     

    //2. 查看上述语句的执行计划

    explain select * from t_base_user where name="andyqian";

    执行查看上述2语句后,我们可以得出以下执行计划结果

    idselect_typetabletypepossible_keskeykey_lenrefrowsExtra
    1SIMPLEt_base_userALL    1Using where

    上面执行计划是什么意思呢?有什么参考价值呢?

    上面这个执行计划给到的信息是: 这个结果通过一个简单的语句全表扫描,共扫描1行,使用where条件在t_base_user表中筛选出的。发现该语句并没有走索引,为什么是这样的呢?别急,我们紧接着看下一节。

    读懂执行计划

    通过上面,我们知道了什么是执行计划,也看到了执行计划到底是什么东西,现在我们来具体了解一下,MySQL执行计划中,每个属性代表的是什么意思?

    我们一一来介绍,并说明每个属性有哪些可选值,以及每个可选值的意思。

    • id
      表示查询中select操作表的顺序,按顺序从大到依次执行
    • select_type :
      该表示选择的类型,可选值有: SIMPLE(简单的),
    • type :
      该属性表示访问类型,有很多种访问类型。
      最常见的其中包括以下几种: ALL(全表扫描), index(索引扫描),range(范围扫描),ref (非唯一索引扫描),eq_ref(唯一索引扫描,),(const)常数引用, 访问速度依次由慢到快。其中 : range(范围)常见与 between and …, 大于 and 小于这种情况。
      提示 : 慢SQL是否走索引,走了什么索引,也就可以通过该属性查看了。
    • table :
      表示该语句查询的表
    • possible_keys :
      顾名思义,该属性给出了,该查询语句,可能走的索引,(如某些字段上索引的名字)这里提供的只是参考,而不是实际走的索引,也就导致会有possible_Keys不为null,key为空的现象。
    • key :
      显示MySQL实际使用的索引,其中就包括主键索引(PRIMARY),或者自建索引的名字。
    • key_len :
      表示索引所使用的字节数,
    • ref :
      连接匹配条件,如果走主键索引的话,该值为: const, 全表扫描的话,为null值
    • rows :
      扫描行数,也就是说,需要扫描多少行,采能获取目标行数,一般情况下会大于返回行数。通常情况下,rows越小,效率越高, 也就有大部分SQL优化,都是在减少这个值的大小。注意:  理想情况下扫描的行数与实际返回行数理论上是一致的,但这种情况及其少,如关联查询,扫描的行数就会比返回行数大大增加)
    • Extra
      这个属性非常重要,该属性中包括执行SQL时的真实情况信息,如上面所属,使用到的是”using where”,表示使用where筛选得到的值,常用的有:
      “Using temporary”: 使用临时表 “using filesort”: 使用文件排序

    看到这里,我们应该已经发现,在第一步中,我们的这条SQL

     

    1

    select * from t_base_user where name="andyqian";

    是没有走索引的,而且还是全表扫描,在数据量少的情况下,问题还不会特别突出,如果数据量比较大,这可是个会造成生产事故的慢查询哦,现在我们改造一下,将name字段添加上索引,

     

    1

    2

    # 添加索引

    alter table t_base_user add index idx_name(name);

    看看它的执行计划是怎样的。

    idselect_typetabletypepossible_keskeykey_lenrefrowsExtra
    1SIMPLEt_base_userrefidx_nameidx_name93cons1Using where

    你看,现在已经走idx_name索引了,其type从All(全表扫描)到ref(非唯一索引了),别看就只有这一点点小区别,在大数据量的时候,可是会起大作用的哦。

    数据结构

    本文中演示的数据结构如下:

     

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    # 创建表  

    create table t_base_user(

    oid bigint(20) not null primary key auto_increment,

    name varchar(30) null comment "name",

    email varchar(30) null comment "email",

    age int null comment "age",

    telephone varchar(30) null comment "telephone",

    status tinyint(4) null comment "0  无效 1 有效",

    created_at datetime null comment "",

    updated_at datetime null comment ""

    )

     

    ## 新增记录:

    insert into t_base_user(name,email,age,telephone,created_at,updated_at)values("andyqian","andytohome@gmail.com",20,"15608411",now(),now());

    )

     

    最后

    一个好的数据库表设计,从一开始就应该考虑添加索引,而不是到最后发现慢SQL了,影响业务了,才来补救。其实我在工作经历当中,由于新建表,或新加字段后,忘记添加索引也造成了多次生产事故,记忆犹新!!!

    更多相关内容
  • MYSQL执行计划详解.pdf

    2021-04-21 09:00:34
    执行计划的ID1. id相同,执行顺序由上至下 id不同,如果是子查询,id的序号会递增,id的值越大优先级越高,越先被执行
  • mysql执行计划详解

    千次阅读 2022-04-15 11:01:25
    mysql执行计划详解

     mysql执行计划详解

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

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

    Mycat执行计划格式

    1.先用explain查看路由信息

    格式: explain sql语句
     
    例: explain select * from t_user

     2.查看执行计划的语句格式(MyCat)

    # 格式:
    explain2 datanode=数据节点 sql=SQL语句
    
    # 实例
    EXPLAIN2 explain2 datanode=ios_dn1 sql=select * from t_user;

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

    执行计划中包含的信息

    ColumnMeaning
    idThe SELECT identifier
    select_typeThe SELECT type
    tableThe table for the output row
    partitionsThe matching partitions
    typeThe join type
    possible_keysThe possible indexes to choose
    keyThe index actually chosen
    key_lenThe length of the chosen key
    refThe columns compared to the index
    rowsEstimate of rows to be examined
    filteredPercentage of rows filtered by table condition
    extraAdditional 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 ValueMeaning
    SIMPLESimple SELECT (not using UNION or subqueries)
    PRIMARYOutermost SELECT
    UNIONSecond or later SELECT statement in a UNION
    DEPENDENT UNIONSecond or later SELECT statement in a UNION, dependent on outer query
    UNION RESULTResult of a UNION.
    SUBQUERYFirst SELECT in subquery
    DEPENDENT SUBQUERYFirst SELECT in subquery, dependent on outer query
    DERIVEDDerived table
    UNCACHEABLE SUBQUERYA subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
    UNCACHEABLE UNIONThe 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;

    文章部分内容来自:https://www.bilibili.com/video/BV16i4y1g7TF?from=search&seid=17650357849987331116&spm_id_from=333.337.0.0

    展开全文
  • Mysql执行计划详解

    千次阅读 2021-08-17 16:39:51
    执行计划“反编译”成SELECT语句,运行SHOW WARNINGS 可得到被MySQL优化器优化后的查询语句 2. EXPLAIN PARTITIONS SELECT …… 用于分区表的EXPLAIN 执行计划包含的信息 id 包含一组数字,表示查询中执行...

    Explain语法

    EXPLAIN  SELECT ……
    变体:
    1. EXPLAIN EXTENDED SELECT ……
    将执行计划“反编译”成SELECT语句,运行SHOW WARNINGS 可得到被MySQL优化器优化后的查询语句 
    2. EXPLAIN PARTITIONS SELECT ……
    用于分区表的EXPLAIN

    执行计划包含的信息

    id

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

    id相同,执行顺序由上至下

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

    id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行

    select_type 

    表示查询中每个select子句的类型(简单 OR复杂)

    a.SIMPLE:查询中不包含子查询或者UNION

    b.查询中若包含任何复杂的子部分,最外层查询则被标记为:PRIMARY

    c.在SELECT或WHERE列表中包含了子查询,该子查询被标记为:SUBQUERY

    d.在FROM列表中包含的子查询被标记为:DERIVED(衍生)

    e.若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在  FROM子句的子查询中,外层SELECT将被标记为:DERIVED

    f.从UNION表获取结果的SELECT被标记为:UNION RESULT

    type

    表示MySQL在表中找到所需行的方式,又称“访问类型”,常见类型如下:

    由左至右,由最差到最好

    a.ALL:Full Table Scan, MySQL将遍历全表以找到匹配的行

    b.index:Full Index Scan,index与ALL区别为index类型只遍历索引树

    c.range:索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行,常见于between、<、>等的查询

    range访问类型的不同形式的索引访问性能差异

    d.ref:非唯一性索引扫描,返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找

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

    f.const、system:当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量

    system是const类型的特例,当查询的表只有一行的情况下, 使用system

    g.NULL:MySQL在优化过程中分解语句,执行时甚至不用访问表或索引

    possible_keys

    指出MySQL能使用哪个索引在表中找到行,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用

    key

    显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL

    TIPS:查询中若使用了覆盖索引,则该索引仅出现在key列表中

    key_len

    表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度

    key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的

    ref

    表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

    本例中,由key_len可知t1表的idx_col1_col2被充分使用,col1匹配t2表的col1,col2匹配了一个常量,即 ’ac’

    rows

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

    Extra

    包含不适合在其他列中显示但十分重要的额外信息

    a.Using index

    该值表示相应的select操作中使用了覆盖索引(Covering Index)

    TIPS:覆盖索引(Covering Index)

    MySQL可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件

    包含所有满足查询需要的数据的索引称为 覆盖索引(Covering Index)

    注意:

    如果要使用覆盖索引,一定要注意select列表中只取出需要的列,不可select *,因为如果将所有字段一起做索引会导致索引文件过大,查询性能下降

    b.Using where

    表示MySQL服务器在存储引擎受到记录后进行“后过滤”(Post-filter),

    如果查询未能使用索引,Using where的作用只是提醒我们MySQL将用where子句来过滤结果集

    c.Using temporary

    表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询

    d.Using filesort

    MySQL中无法利用索引完成的排序操作称为“文件排序”

    MySQL执行计划的局限

    •EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

    •EXPLAIN不考虑各种Cache

    •EXPLAIN不能显示MySQL在执行查询时所作的优化工作

    •部分统计信息是估算的,并非精确值

    •EXPALIN只能解释SELECT操作,其他操作要重写为SELECT后查看执行计划

    展开全文
  • mysql 执行计划详解

    2019-04-17 01:23:13
    NULL 博文链接:https://xq0804200134.iteye.com/blog/1826023
  • MySQL中,我们可以通过EXPLAIN命令获取MySQL如何执行SELECT语句的信息,包括在SELECT语句执行过程中表如何连接和连接的顺序。 下面分别对EXPLAIN命令结果的每一列进行说明: .select_type:表示SELECT的类型,常见...
  • 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执行计划详解

    万次阅读 多人点赞 2017-05-09 22:55:10
    explain执行计划包含的信息其中最重要的字段为:id、type、key、rows、Extra各字段详解idselect查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序 三种情况: 1、id相同:执行顺序由上至下 2...
  • MySQL架构执行与SQL性能优化-MySQL高并发详解课程,课程的目标简单明确,核心就是MySQL的性能优化与高并发。课程内容进行了精华的浓缩,有四大内容主旨,MySQL架构与执行流程,MySQL索引原理详解MySQL事务原理与...
  • 一、执行计划是什么?执行计划,简单的来说,是SQL在数据库中执行时的表现情况,通常用于SQL性能分析,优化等...三、解读MySQL执行计划EXPLAIN SELECT * FROM tb_blog tb LEFT JOIN tb_user tu ON tb.userId = tu....
  • 本文主要给大家介绍了关于mysql执行计划id为空(UNION关键字)的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的 介绍吧 UNION 操作符用于合并两个或多个 SELECT 语句的结果集。例如,我有两个...
  • 在select之前添加explain,mysql会在查询上设置一个标记,当执行查询计划时,这个标记会使其返回关于执行计划中每一步的信息,而不是执行它。 它会返回一行或多行信息,显示出执行计划中的每一部分和执行次序。 这...
  • MySQL EXPLAIN执行计划详解

    千次阅读 2021-08-28 16:11:07
    详细介绍了MySQL EXPLAIN执行计划的各个字段的含义以及使用方式。
  • MySQL通过执行计划分析步骤耗时
  • mysql explain执行计划详解

    千次阅读 2021-01-28 02:18:25
    1)、id列数字越大越先执行,如果说数字一样大,那么就从上往下依次执行,id列为null的就表是这是一个结果集,不需要使用它来进行查询。2)、select_type列常见的有:A:simple:表示不需要union操作或者不包含子查询...
  • mysql的sql执行计划详解

    千次阅读 多人点赞 2018-06-19 09:02:47
    explain执行计划包含的信息其中最重要的字段为:id、type、key、rows、Extra各字段详解idselect查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序 三种情况: 1、id相同:执行顺序由上至下 2...
  • Mysql explain 执行计划之type详解

    千次阅读 2021-09-12 22:29:24
    EXPLAIN执行计划中type字段分为以下几种: ALL INDEX RANGE REF EQ_REF CONST,SYSTEM NULL 自上而下,性能从最差到最好 type = ALL,全表扫描,MYSQL扫描全表来找到匹配的行 (因为film表中rating...
  •  通过上面,我们知道了什么是执行计划,也看到了执行计划到底是什么东西,现在我们来具体了解一下,MySQL执行计划中,每个属性代表的是什么意思?  我们一一来介绍,并说明每个属性有哪些可选值,以及每个可选值的...
  • MySQL explain执行计划详解

    千次阅读 2019-04-25 15:11:33
    一、Explain基本概念 ...· EXPLAIN :模拟Mysql优化器是如何执行SQL查询语句的,从而知道Mysql是如何处理你的SQL语句的,分析你的查询语句或是表结构的性能瓶颈。 · explain显示了mysql如何使用索引来处理selec...
  • mysql 索引与执行计划

    2018-10-21 15:57:40
    目录 1. 索引与执行计划 1 1.1. 索引入门 1 1.1.1. 索引是什么 1 1.1.2. 索引得分类 3 1.1.3. 基础语法 3 1.2. 执行计划 3 1.2.1. 什么是执行计划 3 1.2.2. 执行计划的作用 3 ...1.2.4. 执行计划详解 4
  • 主要介绍了MySQL数据库Event定时执行任务
  • 以前有过一篇关于MySQL查询语句的执行过程,这里总结一下update语句的执行过程。由于update涉及到数据的修改,所以,很容易推断,update语句比select语句会更复杂一些。 1,准备 创建一张test表 CREATE TABLE `...
  • 执行效果4.1. 命令行4.2. 可视化工具–`HeidiSQL Portable 9.4`五. Explain 字段详解5.1. 一览全局字段5.2. Id字段5.2.1. id相同5.2.2. id不相同5.2.3. id相同,又不相同5.3. `select_type`字段5.3.1.类型如下5.3.2....
  • mysql 执行计划explain详解

    千次阅读 2017-03-28 01:34:59
    explain主要是用来获取一个query的执行计划,描述mysql如何执行查询操作、执行顺序,使用到的索引,以及mysql成功返回结果集需要执行的行数。可以帮助我们分析 select 语句,让我们知道查询效率低下的原因,从而改进...
  • mysql执行计划PPT

    2016-04-03 10:41:52
    mysql执行计划详解,PPT格式

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 91,820
精华内容 36,728
关键字:

mysql执行计划详解

mysql 订阅