索引与explain的使用 | 青训营笔记

Rikkaaaa
94 阅读6分钟

这是我参与「 第五届青训营 」伴学笔记创作活动的第 16 天 官方介绍索引是帮助MySQL高效获取数据的数据结构。简单来讲，数据库索引就像是书前面的目录，能加快数据库的查询速度

简介

  • 索引(Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质:索引是数据结构。
  • 如果没有特殊指明，那么索引默认为B树。而聚集索引、复合索引、前缀索引、唯一索引默认为B+树。
  • 在数组中索引的表现形式为数组下标。
  • 通过建立索引可以提高mysql在查询排序等操作时的性能。
  • 索引本质也是一张表，存储于磁盘空间，所以索引会影响表的更新相关操作

分类

  • 单值索引：索引占单独一个列，一个表可以有多个单值索引
  • 唯一索引：索引列值必须唯一，但允许有空值
  • 复合索引：索引占多个列

操作

  • 创建

create [unique] index index_name on table_name(col_name(length)) alter table_name add [unique] index [index_name] on col_name(length)

  • 删除

drop index index_name on table_name

  • 查看

show index from table_name

结构

  • B-Tree
  • Hash
  • full-text
  • R-Tree

建立索引的选择

  1. 主键自动建立唯一索引频繁
  2. 作为查询条件的字段应该创建索引
  3. 查询中与其它表关联的字段，外键关系建立索引
  4. 频繁更新的字段不适合创建索引。因为每次更新不单单是更新了记录还会更新索引
  5. Where条件里用不到的字段不创建索引
  6. 单键/组合索引的选择问题，who? (在高并发下倾向创建组合索引)
  7. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
  8. 查询中统计或者分组字段

性能分析

  • mysql query optimizer：mysql内部优化模块

  • 常见瓶颈：

    • cpu
    • 磁盘IO
    • 硬件：top、free、iostat、vmstat查看性能状态

explain

作用介绍

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

功能详解

image-20220606155235782

  • id：select查询的序列号，包含一组数字，表示查询的select语句或操作表的顺序。

    • id相同时，执行顺序由上至下
    • id全不同时，如果是子查询，id值越大优先级越高
    • id不全相同时，先执行id值大的，后顺序执行

  • select_type：查询的类型

    • simple：普通查询
    • primary：子查询最外层的查询
    • subquery：在select或where中包含子查询
    • derived：在from中包含子查询，结果保存为临时表
    • union：若第二个select出现在union之后，则被标记为union;若union包含在from子句的子查询中,外层select将被标记为: derived
    • union result：union的结果

  • table：本次查询所使用的表

  • type：查询的类型 ALL、index、range、ref、eq_ ref、const, system、all。其中system> const>eq ref> ref> range> index> all

    • system：只有一行记录const类型的特例
    • const：表示通过索引一次就找到了,const用于比较primary key或者unique索引。
    • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
    • ref：非唯一性索引扫描，返回匹配某个单独值的所有行，用到索引查出多条记录。
    • range：只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引。一般就是在你的where语句中出现了between、<、>、in等的查询，不用扫描全部索引。
    • index：与ALL区别为index类型只遍历索引树。这通常比all快，因为索引文件通常比数据文件小。也就是说虽然all和Index都是读全表，但index是 从索引中读取的，而all是 从硬盘中读的)
    • all：从磁盘空间读并遍历全表

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

  • key：实际使用的索引，如果为null则表示没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中

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

  • ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

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

  • extra：额外属性

    • using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为"文件排序”
    • using temporary：使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by未能根据已有索引进行查找
    • using index：表示相应的select操作中使用了覆盖索引(Covering Index)， 避免访问了表的数据行，效率不错! 如果同时出现usingwhere,表明索引被用来执行索引键值的查找; 如果没有同时出现usingwhere,表明索引用来读取数据而非执行查找动作。
    • using where：使用了where过滤
    • using join buffer：使用了链接缓存
    • impossible where：查询失败
    • select tables optimized：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
    • distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

索引优化

  1. 全值匹配,对索引中所有列都指定具体值
  2. 最佳左前缀法则，如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
  3. 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描
  4. 存储引擎不能使用索引中范围条件右边的列
  5. 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select*
  6. mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
  7. is null ,is not null也无法使用索引
  8. like以通配符开头('%abc...') mysq|索引失效会变成全表扫描的操作，双%可使用覆盖索引查询
  9. 字符串不加单引号索引失效
  10. 少用or，用它来连接时会索引失效
avatar
文章
阅读
粉丝