• 索引优化分析 2


    1、性能下降SQL慢 执行时间长 等待时间长

      1.查询语句写的烂

      2.索引失效

        单值索引

        复合索引

      3.关联查询太多 join (设计缺陷或不得已的需求)

      4.服务器调优即各个参数的设置(缓冲、线程等)

    2、常见通用的join查询

      1.SQL执行顺序

        手写

        机读

        总结

      2.join 7种方法

    1/inner join 
    select <select_list>
    from tablea a 
    inner join tableb b 
    on a.key = b.key;
    
    2/left join 
    select <select_list>
    from tablea a 
    left join tableb b 
    on a.key = b.key; 
    
    3/right join 
    select <select_list>
    from tablea a 
    right join tableb b 
    on a.key = b.key 
    
    4/左外join
    select <select_list>
    from tablea a 
    left join tableb b 
    on a.key = b.key 
    where b.key is null; 
    
    5/右外join
    select <select_list>
    from tablea a 
    right join tableb b 
    on a.key = b.key 
    where a.key is null; 
    
    6/full outer join 
    select <select_list>
    from tablea a 
    full outer join tableb b 
    on a.key = b.key 
    
    7/满外join
    select <select_list>
    from tablea a 
    full outer join tableb b 
    where a.key is null 
    or b.key is null

    3.索引 简介

      1.是什么

        mysql官方对索引的定义为:索引(index)是帮助mysql高效获取数据的数据结构。 可以得到索引的本质:索引是数据结构。

        1、可以理解为 “排好序的快速查找数据结构

          1. 详解(重要)  

          2. 结论

             数据本身之外,数据库还维护着一个满足特定查找的数据结构,这些数据结构一某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法, 这种数据结构就是索引。

       2、一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上, 

        

      2.优势

        类似图书馆,提高数据检索效率,降低数据库的IO成本,通过索引列对数据进行排序,降低数据排序的成本 和 CPU的消耗

      3.劣势

                                                                                

      4.mysql 索引分类

        1、单值索引

          即一个索引只包含单个列,一个表可以有多个单列索引                            

        2、唯一索引

          索引列的值必须唯一,但允许有空值 。

        3、复合索引

          即一个索引包含多个列

        4、基本语法

    #创建
    create [unique]index indexName on mytable(columnname(length));    #unique 表示唯一字段
    alter mytable add [unique]index[indexName] on (columnname(length))
    #删除
    drop index [indexName] on mytable;
    #查看
    show index from table_nameG
    #使用alter 命令

        

      5.mysql索引结构  

        1、BTree索引

          检索原理 

                         

        2、Hash索引 

        3、full_text 全文索引

        4、R_Tree索引

      6那些情况需要创建索引

        

      7,那些情况不需要创建索引

        1、表太少

        2、经常增删改的表:[ 提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT, UPDATE, DELETE。 因为更新表时,mysql不仅要保存数据,还要保存一下索引文件。]

        3、数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据建立索引。【注:如果某个列包含许多重复的内容,为它建立索引就没有太大的实际效果。】 

    1.mysql query optimizer

    2.Mysql 常见瓶颈

    1. cpu:cpu在饱和的时候(一般发生在数据装入内存,或从内存中读取数据时)
    2. IO:磁盘IO瓶颈发生(装入数据大于内存容量时)
    3. 服务器硬件的性能瓶颈:top,tree,iotat,vmstat 查看系统性能状态

    3.性能分析

    Explain:

    1. 是什么(查看执行计划):
      1. 使用Explain关键字模拟优化器执行sql查询语句(分析查询语句,或是表结构的性能瓶颈),mysql是如何执行sql语句的顺序
      2. 官网分析图: http://dev.mysql.com/doc/refman/5.5/en/explain-output.html
    1. 能干嘛(下面解答,重复阅读本段内容,会有不同体会):
      1. 表的读取顺序    ?
      2. 数据读取操作的读取类型    ?
      3. 某些索引可以使用    ?
      4. 某些索引被实际使用    ?
      5. 表之间的引用    ?
      6. 每张表有多少行被优化器所查询    ?
    1. 怎么玩
      1. Explain + sql语句
      2. 执行计划包含的信息 -》 
    1. 字段名解释
    • id:重点
      • 描述:select 查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序。
      • 三种情况:
        • id相同,执行顺序由上至下
        • id不同,如果是子查询,id的序号会递增,id值越大,优先级越高,越先被执行
        • id相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行。 衍生 =derived
    • select_type
      • 描述:查询的类型,主要用于区别 普通查询、联合查询、子查询等  
      • 值(作用):
        • simple:简单的select 查询,查询中不包含子查询或 UNION
        • PRIMARY:查询中若包含任何复杂的子查询,最外层查询
        • SUBQUERY:在select 或 where 包含子查询
        • DERIVED:
          • 在 Form 列表中包含的子查询被标记为DERIVED(衍生)
          • mysql会递归执行这些子查询,结果放置到临时表中。
        • UNION :
          • 若第二个Select 出现在UNION 之后,则被标记为:UNION
          • 若 UNION 包含在FROM 子句的子查询中,则被标记:DERIVED
        • UNION RESULT:从UNION表中获取结果的select
    • table :显示这一行是某一张表的。
    • type:重点
      • 描述:访问类型
      • 常用显示排序(最好->最坏):system->const->ref->range->index->All
      • 值:
        • system:    
          • 表只用一行记录(等于系统表),这是const类型的特例,平时不会出现(可以忽略)
        • const:  
          • 表示通过索引一次找到,const用于比较primary key 或者unique索引。如果将主键至于where列表中,mysql就能将该查询转换成为一个常量。
        • eq_ref:  
          • 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常用于主键,或唯一索引的扫描。
        • ref:
          • 唯一性索引扫描,返回匹配某个单独只的所有行。
          • 本质上也是一种索引访问,它返回所有匹配某个单独值的行,它可能会找到多个条件的行,所以他应该属于查找和扫描的混合体。
        • range:
          • 只检索给定范围的行,使用一个索引选择行。
          • key列显示使用的索引列,一般就是where中出现between、< 、>、in等查询条件。
          • 这种范围索引扫描比全表扫描好,因为他只需要开始与索引的某一个点,而到结束的另一个点,不需要全表扫描。
        • index:
          • Full Index Scan,index与All的区别为index只遍历索引树,通常比All快,因为索引文件通常比数据文件小。(虽然index和all都是读全表,index是从索引中获取,但是All是从磁盘中获取)。
        • All:
          • Full Table Scan,将全表以找到匹配的行(全表磁盘扫描,多一些IO的操作)。
      • 全显示排序(最好->最坏):system->const->eq_ref->ref-f>fulltext->ref_or_null->index_merge->unique_subquery->range->index->All
      • 优化:一般来说,得保证查询达到range级别,最好达到ref
    • key_len:
      • 描述: 
        • 表示索引中使用的字节数,可通过该列计算查询中的索引长度,长度越短越好。
        • key_len 显示的值为索引字段最大的可能长度,并非实际使用长度,即key_len是根据表定义计算面得,不通过表内检索出的。
      • 案例:

                       

      • 结论:条件越细,key_len越长。
    • ref
      • 描述:显示索引的某一列被使用。如果可能是一个常数,某些列或常量被用于查找索引列上的值。
      • 案例:
    • rows 重点
      • 描述:根据表统计信息及索引选用情况,大致估算出找到的记录所需要读取的行数。
      • 案例:image.png
    • Extra 重点
      • 描述:包含不适合在其他列中显示,但十分重要的其他信息。
      • 值:
        • Using fileshort :  重点
          • mysql 会对数据库使用一个外部的索引排序,而不是按照表内的索引进行读取。
          • mysql 中无法利用索引完成排序的操作成为"文件排序"。
          • 案例:image.png
            • 案例1:未正确使用索引排序(文件排序)
            • 案例2:正确使用索引排序
        • Using temporay  重点
          • 描述:
            • 用临时表保存临时结果,mysql 查询结果使用临时表。
            • 常见使用 排序 order by 和 group by 分组查询
          • 案例:image.png
            • 案例1:未正确使用索引排序,又使用 group by 或 order by 产生的结果,会严重拖慢数据库的效率。
            • 案例2:正确使用索引排序
        • Using index  重点
          • 描述:
            • 表示相应的操作中使用了覆盖索引(Covering index),避免访问,表的数据行,效率不错!
            • 如果同时出现Using where 表明 索引备用类执行索引键值的查找
            • 如果没有同事出现Using w here 表明 索引用来执行读取数据,而非查找动作。
            • 覆盖索引(Covering index)又被称为:索引覆盖
              • 描述:
                • 理解方式 一:select 的数据链只用从索引中获取,不必读取数据行,mysql 利用索引返回 select 中的字段,而不必根据索引再次读取数据文件,换句话说 查询列 要被所创建的索引覆盖。
                • 理解方式 二:索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一列的数据,因此它不必读取整行。毕竟索引的叶子节点,存储它们索引的数据,当能通过索引获取数据,就可以获取到所需要的数据。一个索引包含(或覆盖)满足查询结果的数据叫做覆盖索引。
              • 注意:
                • 使用覆盖索引,注意select 列表中只能取出所需要的列,不可使用select *
                • 因为如果将所有的字段一起做索引,会导致索引文件过大,查询性能下降。
          • 案例:image.png
            • 案例 1:
            • 案例 2:
        • Using where : 表示使用 where 过滤
        • Using join buffer : 表示 使用连接缓存  #如果join比较多,可以把join buffer 调大一点。
        • impossible where :
          • 描述:where 子句的值总是 false,不能用来获取任何元素。
          • 案例:image.png
            • 过滤字段重复
        • select table optimized away: 了解
          • 在没有Group By 子句的情况下,基于索引优化MIN/MAX的操作或者对于 MyISAM 存储引擎优化 count(*) 操作,不必等到执行间断进行计算,查询计算生成间断完成优化。
        •  distinct:优化distinct操作,在找到第一匹配的元组即停止找同样值的操作。 了解
    1. 热身case
    • 案例:image.png

                   

     

    4.索引优化

    • 1.  索引分析
      • 单表:
        • 建表SQL:
        • 案例(分析好后,才能有优化):
      • 俩表: index 时 
        • 建表:

                                  

               。。。。。

        • 案例:

            索引优化(左连接索引添加到右表):

            索引优化(右连接索引添加到左表)

                                     

                                     

      • 三表:
        • 建表:

                                

        • 案例(和俩表连接的结论类似):
       
    结论:
    join语句的优化:
    尽可能减少 join 语句中的NestedLoop循环总数;"永远记住小结果集驱动大结果集"。
    尽可能优化 NestedLoop 的内层循环;
    保证Join语句中被驱动表驱动表上的 join 字段被索引
    当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下,不要太吝啬joinBuffer的设置。
    • 2.  索引失效(避免)
      • 建表 SQL:

                      

      • 案例(索引失效)
        • 全值匹配我最爱
          • 案例:
        • 最佳左前缀法则
          • 案例:
          • 描述:索引多列,遵守最佳左前缀法则(查询从索引最左前列开始,并且不跳过中间的索引列)。
        • 不能在索引上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描。
          • 案例:
          •   
            • 结论:使用计算会导致索引失效
        • 存储索引不能使用索引中范围条件右边的列
          • 案例:
        • 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select * 操作
          • 案例:
          • 结论:尽量使用覆盖索引
        • mysql 使用(!=或< >)的时候无法使用索引会导致全表扫描。
          • 案例:
        • is null,is not null 无法使用索引。 
          • 案例:image.png
        • like 以通配符开头('%abc……') mysql索引失效会变成全表扫描的操作。
          • 案例:image.png
          • 案例 2 生产环境中(like 俩边+%) 

                               

     

                  

               

               

          • 结论:
            • 1. 使用like 'aaa%' ,%加到右边
            • 2.如果 like '%ff%',%加俩边,使用覆盖索引(分析覆盖索引的字段 )
        • 少用 or ,用or 连接时索引会失效
        • 口诀:
          • 1.全值匹配我最爱,最左前缀要遵守。
          • 2.带头大哥不能死,中间兄弟不能段。
          • 3.索引列上少计算,范围之后全失效。
          • 4.like 百分写最右,覆盖索引不写*。
          • 5.不等空值还有or,索引失效要少用。
          • 6.字符串引号不可丢,SQL高级也不难。

            小总结

            

    • 面试题讲解:
      • 题目SQL

          

          

          

               

           

           

      • 定值、范围还是排序,一般 oder by 是给个范围
      • group by 基本上都会进行排序,会有临时表的产生。[注:group by 表面是分组,但分组之前必排序]
    • 3. 一般性建议
      • 对于单值索引,尽量选择针对当前 query 过滤性更好的索引。
      • 在选择组合索引的时候,当前query中过滤最好的字段在索引字段顺序中,位置越靠前 [左]  越好。
      • 在选择组合索引的时候,尽量选择可以能够包含当前query中的where子句中更多字段的索引。
      • 尽可能通过分析统计信息和调整query的写法来达到合适的索引。
  • 相关阅读:
    大数减法
    MySQL配置的一些坑
    最大流_Edmonds-Karp算法
    最小生成树两连
    最短路三连
    对拍
    Broadcast
    Intent
    Custom Views
    Fragment
  • 原文地址:https://www.cnblogs.com/yh-z/p/13024689.html
Copyright © 2020-2023  润新知