Mysql索引、explain执行计划

2020-04-12

1、索引的使用场景

  哪些情况使用索引:
  1、主键自动建立唯一索引
  2、频繁作为查询条件的字段应该创建索引 where
  3、多表关联查询中,关联字段应该创建索引on两边都要创建索引
    select * from user left join order on user.id = order.userid
  4、查询中排序的字段,应该创建索引B + tree 有顺序
  5、覆盖索引 好处是?不需要回表组合索引
    user表 组合索引(name,age)
    select * from user ------- 全表扫描,没有使用索引
    select name,age from user ---- 索引覆盖,不需要回表。
  6、统计或者分组字段,应该创建索引
  哪些情况不适用索引: 
  1、表记录太少 索引是要有存储的开销
  2、频繁更新 索引要维护
  3、查询字段使用频率不高
  为什么使用组合索引
    由多个字段组成的索引 使用顺序就是创建的顺序
    
     ALTER TABLE 'table_name' ADD INDEX index_name(col1,col2,col3)
    在一颗索引树上由多个字段
    优势: 效率高、省空间、容易形成覆盖索引     
    使用:
      遵循最左前缀原则
    1、前缀索引
      like 常量% 使用索引 like %常量 不使用索引
    2、最左前缀
      从左向右匹配直到遇到范围查询 > < between 索引失效

2、索引失效

    查看索引计划:EXPLAIN 命令    

      可以通过explain命令深入了解MySQL的基于开销的优化器,还可以获得很多可
      能被优化器考虑到的访问策略的细节,以及当运行SQL语句时哪种策略预计会被优化器采用。
    eg: explain select * from user where id = 1
    
     *************************** 1. row ***************************
      id: 1
      select_type: SIMPLE
      table: user_info
      partitions: NULL
      type: const
      possible_keys: PRIMARY
  各列的含义如下:
   id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
   select_type: SELECT 查询的类型.
   table: 查询的是哪个表
      partitions: 匹配的分区
           type: join 类型
   possible_keys: 此次查询中可能选用的索引
   key: 此次查询中确切使用到的索引.
   ref: 哪个字段或常数与 key 一起被使用
   rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
   fifiltered: 表示此查询条件所过滤的数据的百分比
   extra: 额外的信息
id
  每个单位查询的SELECT语句都会自动分配的一个唯一标识符,表示查询中操作表的顺序,
  有四种情况:
    id相同:执行顺序由上到下
    id不同:如果是子查询,id号会自增,id越大,优先级越高。
    id相同的不同的同时存在
    id列为null的就表示这是一个结果集,不需要使用它来进行查询。
select_type(重要)
  单位查询的查询类型,比如:普通查询、联合查询(union、union all)、子查询等复杂查询。
    包括

    simple

     表示不需要union操作或者不包含子查询的简单select查询。有连接查询时,
       外层的查询为simple,且只有一个
  
  primary
    一个需要union操作或者含有子查询的select,位于最外层的单位
    查询的select_type即为primary。且只有一个
  union
    union连接的两个select查询,第一个查询是dervied派生表,除
    了第一个表外,第二个以后的表select_type都是union
  dependent union
    与union一样,出现在union 或union all语句中,但是这个查询要
    受到外部查询的影响 
  union result
    包含union的结果集,在union和union all语句中,因为它不需要参
    与查询,所以id字段为null
  subquery
    除了from字句中包含的子查询外,其他地方出现的子查询都可能
    是subquery 
  dependent subquery
    与dependent union类似,表示这个subquery的查询要受到外部表
    查询的影响
  derived
    from字句中出现的子查询,也叫做派生表,其他数据库中可能叫
    做内联视图或嵌套select
 
table
  显示的单位查询的表名,有如下几种情况:
  •   如果查询使用了别名,那么这里显示的是别名
  •   如果不涉及对数据表的操作,那么这显示为null
  •   如果显示为尖括号括起来的就表示这个是临时表,后边的N就是执行计划中的id,表示结果来自于这个查询产生。
  •   如果是尖括号括起来的<union M,N>,与类似,也是一个临时表,表示这个结果来自于union查询的id为M,N的结果集。 
type(重要)
  显示的是单位查询的连接类型或者理解为访问类型,访问性能依次从好到差:
    system、const、eq_ref、ref、fulltext、ref_or_null、unique_subquery、index_subquery、
    range、index_merge、index、ALL
   注意事项:    
    - 除了all之外,其他的type都可以使用到索引
    - 除了index_merge之外,其他的type只可以用到一个索引
    - 最少要使用到range级别
possible_keys
  此次查询中可能选用的索引,一个或多个
  key查询真正使用到的索引,select_type为index_merge时,这里可能出现两个以上的索引,其他的
  select_type这里只会出现一个。
key_len
  用于处理查询的索引长度,如果是单列索引,那就整个索引长度算进去,如果是多列索引,那么查
  询不一定都能使用到所有的列,具体使用到了多少个列的索引,这里就会计算进去,没有使用到的
  列,这里不会计算进去。
  留意下这个列的值,算一下你的多列索引总长度就知道有没有使用到所有的列了。
  另外,key_len只计算where条件用到的索引长度,而排序和分组就算用到了索引,也不会计算到
  key_len中。
ref
  如果是使用的常数等值查询,这里会显示const
  如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段
  如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func
rows
  这里是执行计划中估算的扫描行数,不是精确值(InnoDB不是精确的值,MyISAM是精确的值,主要原
  因是InnoDB里面使用了MVCC并发机制)
extra(重要)
  这个列包含不适合在其他列中显示单十分重要的额外的信息,这个列可以显示的信息非常多,有几十
  种,常用的有:using fifilesort(重要)、using index(重要)、using index condition(重要)、
          using temporary、distinct、using where(重要)
  
    using fifilesort(重要):
  •            排序时无法使用到索引时,就会出现这个。常见于order by和group by语句中
  •         说明MySQL会使用一个外部的索引排序,而不是按照索引顺序进行读取。
  •         MySQL中无法利用索引完成的排序操作称为“文件排序”
索引失效分析