• 7、索引优化分析


    7、索引优化分析

    7.1、索引优化器(MySql Query Optimizer)

    • MySQL中有专门负责优化SELECT语句的优化器模块,主要功能:通过计算分析系统中收集到的统计信息,为客户端请求的Query提供他认为最优的执行计划(他认为最优的数据检索方式,但不见得是DBA认为是最优的,这部分最耗费时间)

    • 当客户端向MySQL 请求一条Query,命令解析器模块完成请求分类,区别出是 SELECT 并转发给MySQL Query Optimizer时,MySQL Query Optimizer 首先会对整条Query进行优化,处理掉一些常量表达式的预算,直接换算成常量值。并对 Query 中的查询条件进行简化和转换,如去掉一些无用或显而易见的条件、结构调整等。然后分析 Query 中的 Hint 信息(如果有),看显示Hint信息是否可以完全确定该Query 的执行计划。如果没有 Hint 或Hint 信息还不足以完全确定执行计划,则会读取所涉及对象的统计信息,根据 Query 进行些相应的计算分析,然后再得出最后的执行计划。

    7.2、Explain

    7.2.1、介绍

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

    • 使用 EXPLAIN 关键字可以帮我们分析:

      • 表的读取顺序
      • 数据读取操作的操作类型
      • 哪些索引可以使用
      • 哪些索引被实际使用
      • 表之间的引用
      • 每张表有多少行被优化器查询

    7.2.2、使用方法

    Explain + SQL语句

    在这里插入图片描述

    返回数据介绍
    ColumnJSON NameMeaning
    idselect_id表示查询中执行 select 子句或操作表的顺序:
    id 相同由上到下执行,id 不同由大到小执行
    select_typeNone查询的类型,主要是用于区别 普通查询、联合查询、子查询等的复杂查询
    tabletable_name显示这一行的数据是关于哪张表的
    partitionspartitions代表分区表中的命中情况,非分区表,该项为null
    typeaccess_type访问类型排列:用于区分 SQL 的执行优劣,是较为重要的一个指标
    possible_keyspossible_keys显示可能应用在这张表中的索引,一个或多个。
    keykey实际使用的索引。如果为NULL,则没有使用索引
    key_lenkey_lengthkey_len 表示索引使用的字节数,值越大越好
    refref显示将哪些列或常量与键列中命名的索引进行比较,以从表中选择行。
    rowsrowsrows 列显示 MySQL 认为它执行查询时必须检查的行数。值越小越好
    filteredfiltered这个字段表示存储引擎返回的数据在 server 层过滤后,
    剩下多少满足查询的记录数量的比例,注意是百分比
    ExtraNone包含不适合在其他列中显示,但十分重要的额外信息

    7.2.3、EXPLAIN 返回各字段解释

    建表语句
     CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
     CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
     CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
     CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
     
     INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000))); 
     INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));  
     INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));    
     INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    id 查询的序列号

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

    有三种情况:

    • id 相同,执行顺序由上至下
    • id 不同,如果是子查询,id 的序号会递增,id 值越大优先级越高,越先被执行;
    • id 相同,不同,同时存在;
      • id 相同的,可以认为是一组,从上往下顺序执行;
      • 在所有组中,id 值越大,优先级越高,越先执行;
      • 关注点:id 号每个号码,表示一趟独立的查询;
      • 一个 sql 的查询趟数越少越好;
    select * from t1,t2,t3;
    
    • 1

    在这里插入图片描述

    explain select t1.id from t1 where t1.id = (select t2.id from t2 where t2.id = (select t3.id from t3 where t3.content='t3_897'));
    
    • 1

    在这里插入图片描述

    select_type 查询的类型
    valuemeaning
    SIMPLE简单的 select 查询,查询中不包含子查询或者 union
    PRIMARY查询中若包含任何复杂的子部分,最外层查询则被标记为 primary
    SUBQUERY在 select 或 where 列表中包含了子查询
    DEPENDENT SUBQUERY在 select 或 where 列表中包含了子查询,子查询基于外层
    UNCACHEABLE SUBQUREY表示这个 subquery 的查询要受到外部系统变量的影响
    UNION若第二个 select 出现在 union 之后,则被标记为 union; 若 union 包含在 from 子句的子查询中,外层 select 将被标记为:DERIVED
    UNION RESULT从 union 表获取结果的 select
    1. SIMPLE:简单的 select 查询,查询中不包含子查询或者 union

    在这里插入图片描述

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

    在这里插入图片描述

    1. SUBQUERY:在 select 或 where 列表中包含了子查询

    在这里插入图片描述

    1. DEPENDENT SUBQUERY:在 select 或 where 列表中包含了子查询,子查询基于外层

    在这里插入图片描述

    1. UNCACHEABLE SUBQUREY:表示这个 subquery 的查询要受到外部系统变量的影响

    在这里插入图片描述

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

    在这里插入图片描述

    1. UNION RESULT:从 union 表获取结果的 select

    在这里插入图片描述

    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

    一般来说,得保证查询至少达到 range 级别,最好能达到 ref。

    1. system:表仅有一行记录,必须是系统表,这是 const 类型的特例,查询起来非常迅速。

    在这里插入图片描述

    1. const:表示通过索引一次就找到了,const 用于 primary key 或者 unique 索引。

      因为只匹配一行数据,所以很快 如将主键置于 where 列表中,MySQL 就能将该查询转换为一个常量

    在这里插入图片描述

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

    在这里插入图片描述

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

    在这里插入图片描述

    1. range:只检索给定范围的行,使用一个索引来选择行。

      key 列显示使用了哪个索引 一般就是在你的where语句中出现了between、<、>、in等的查询 这种范围索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束语另一点,不用扫描全部索引。

    在这里插入图片描述

    1. index:出现 index 是 sql 使用了索引但是没用通过索引进行过滤,一般是使用了覆盖索引或者是利用索引进行了排序分组

      Full Index Scan,index 与 all 区别为 index 类型只遍历索引树。

    在这里插入图片描述

    1. all:Full Table Scan,将遍历全表以找到匹配的行

    在这里插入图片描述

    key_len 表示索引使用的字节数

    okey_len 表示索引使用的字节数,根据这个值可以判断索引的使用情况,特别是在组合索引的时候,判断该索引有多少部分被使用到非常重要。值越大越好。

    在这里插入图片描述

    key_len 的计算方式:

    • varchr(10)变长字段且允许NULL = 10 * ( character set:utf8=3, gbk=2, latin1=1) + 1(NULL) + 2(变长字段)
      varchr(10)变长字段且不允许NULL = 10 * ( character set:utf8=3, gbk=2, latin1=1) + 2(变长字段)
      char(10) 固定字段且允许NULL = 10 * ( character set:utf8=3, gbk=2, latin1=1) + 1(NULL)
      char(10) 固定字段且不允许NULL = 10 * ( character set:utf8=3, gbk=2, latin1=1)

    在这里插入图片描述

    ref

    显示将哪些列或常量与键列中命名的索引进行比较,以从表中选择行。

    Extra 其他重要信息
    1. Using filesort:order by 没有用上索引。

    在这里插入图片描述

    优化后(给 deptno 和 ename 字段建立复合索引),去掉 filesort:

    在这里插入图片描述

    • 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
    • 说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。 MySQL中无法利用索引完成的排序操作称为“文件排序”
    1. Using temporary:分组没有用上索引。产生临时表。注意:分组操作是需要先排序后分组的。所以,也会出现 Using filesort。

    优化前存在 using temporary 和 using filesort

    在这里插入图片描述

    优化后(给 deptno 和 ename 建立复合索引)去掉 using temporary 和 using filesort,性能发生明显变化:

    在这里插入图片描述

    使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by。

    1. Using index:表示使用了覆盖索引[content 是一个索引]
      在这里插入图片描述

    如果同时出现using where,表明索引被用来执行索引键值的查找;
    在这里插入图片描述

    如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。

    1. Using where:表示使用了 where 过滤

    2. using join buffer:如果有它则表明关联字段没有使用索引,使用了连接缓存

    在这里插入图片描述

    1. impossible where:where 后面筛选条件有错误!

    在这里插入图片描述

  • 相关阅读:
    【Dubbo】Dubbo负载均衡实现解析
    关注我,我们一起彻底学会java
    Python PyInstaller安装和使用教程(详解版)
    力扣学习笔记——49. 字母异位词分组
    C语言面试题 - 字符空间操作类
    这款 7k Star 的国产监控系统,真不错!
    Java设计模式——策略模式
    VSCode 占用内存过高
    被妖魔化的奥数,到底该怎么学?
    OneFlow的大模型分片保存和加载策略
  • 原文地址:https://blog.csdn.net/qq_44704799/article/details/126833586