MySQL索引及执行计划

时间:2022-06-06 18:12:26

MySQL索引及执行计划

索引

合理的建立索引可以加快数据查询,例如,学校图书管为每一本书编号,根据编号可以快速锁定一本书所在位置。MySQL索引默认B 树索引。索引虽然能够提高检索效率,但同时也会降低更新的速度,因为insert 、update、delete也会操作索引文件,会调整因为更新等操作带来的键值变化后的索引信息。

索引类型

  • 主键索引:唯一索引,并且并指定为primary key,每个表中只能有一个主键
  • 唯一索引:索引列的所有值都只能出现一次,即值必须唯一,值可以为空
  • 普通索引:基本的索引类型,值可以为空,没有唯一性限制
  • 复合索引:一个索引中包含多个列

何时建立索引

  • 主键自动创建唯一索引
  • 频繁作为查询条件的字段适合创建索引
  • 查询中与其他表关联的字段适合创建索引
  • 频繁更新的字段不适合创建索引,因为在更新记录的同时会更新索引
  • where条件中使用不到的字段不需要创建索引
  • 在高并发下,在单值索引和复合索引中,复合索引更合适
  • order by字段添加索引可以提高排序速度

不需要索引

  • 表记录较少
  • 经常增删改的表
  • 数据重复且分布平均的表,如性别

执行计划

可以使用explain 关键字模拟优化器执行sql查询语句,从而分析查询语句或者表结构的性能瓶颈。

通过查看执行计划,可以了解到表的读取顺序、数据读取操作、哪些索引可以使用、哪些索引被实际使用、表之间的引用、每张表中有多少行被优化器查询

explain select * from tz_order a left join tz_order_item b on b.order_number = a.order_number where b.order_number = 1146347329394184192
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE b ALL order_number 7 14.29 Using where
1 SIMPLE a eq_ref order_number_unique_ind order_number_unique_ind 152 yami_shops.b.order_number 1 100

id

表示实际操作中加载表的顺序,可以相同也可以不相同,如果id相同则从上往下依次加载执行,不相同则id越大对应的表越先加载。

select_type

  • SIMPLE:简单的select查询,查询中不包含子查询或者union
  • PRIMARY:查询中若包含任何复杂的子查询,最外层的最后加载的标记为此
  • SUBQUERY:在select或者where中包含子查询
  • DERIVED:在from列表中包含的子查询被标记为Derived(衍生),mysql会递归执行这些子查询,把结果放到临时表中
  • UNION:若第二个select出现在union之后,则被标记为union;若union包含在from子句的子查询中,外层的select为标记为derived
  • UNION RESULT:从union表中获取结果的select

type

  • System:表中只有一条记录
  • Const:表示通过索引一次就可以找到,用于主键索引或者唯一索引。因为只需要匹配一行,查询速度往往很快,mysql可以将该查询转换成一个常量
  • Eq_ref:唯一索引扫描,对于每个索引键,表中只有一条记录与之匹配,用于主键索引或者唯一索引
  • Ref:非唯一索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,返回所有匹配某个单独值的行,可能找到多行。
  • Rang:只检索给定范围的行,使用一个索引来选择行,一般where中使用了<、>、in、between这种范围扫描。
  • Index:index只遍历所引树,因为索引文件比数据文件小,所以读取索引文件比读全表快。
  • All:全表扫描,从硬盘读取

possible_key

可能应用到这张表的索引,查询涉及到的字段上若存在索引,则列出,但是不一定会使用。

key

实际使用到的索引,如果是null,则没有使用索引,如果查询中使用了覆盖索引,则该索引仅会出现在key列表中

key_len

表示索引中使用的字节数,计算查询中索引的长度,在不损失精度的情况下,越小越好,这个值显示的是索引字段的最大可能长度,不是实际长度

ref

显示索引的哪一列被使用

rows

根据表统计信息以及索引选用情况,大致估算找到所需记录需要扫描的行数

extra

  • Using filesort:mysql会对数据使用一个外部索引排序,而不是按照表内的索引进行读取,mysql中无法利用索引完成的排序操作叫做文件排序
  • Using temporary:使用了临时表保存中结果,mysql对查询结果排序使用了临时表。一般来说,是因为order by排序或者分组查询group by时候没有按照索引走。使用了临时表,更加严重影响效率。
  • Using index:表示相应的select操作中使用到了覆盖索引,避免访问表的数据行。如果同时出现Using where,表示索引被用来执行索引值的查找,没有同时出现 Using where表示索引用来读取数据而非执行查找。

表驱动

  • select * from A where id in (select id from B) --> B的数据小于A
  • select * from B where exists (select 1 from A where A.id = B.id) ---> B的数据小于A