mysql 等于 eq mysql eq_ref

转载

半夜未央好 2023-11-14 07:37:26

文章标签 mysql 等于 eq 数据 MySQL 搜索 文章分类 MySQL 数据库

一、explain必备知识

1.type取值

性能从好到坏排序如下

system：该表只有一行（相当于系统表），system是const类型的特例

const：针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可

eq_ref：当使用了索引的全部组成部分，并且索引是PRIMARY KEY或UNIQUE NOT NULL 才会使用该类型，性能仅次于system及const

ref：当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引条件只会匹配到少量的行，性能也很好

fulltext：全文索引

ref_or_null：该类型类似于ref，但是MySQL会额外搜索哪些行包含了NULL。这种类型常见于解析子查询

index_merge：此类型表示使用了索引合并优化，表示一个查询里面用到了多个索引

unique_subquery：该类型和eq_ref类似，但是使用了IN查询，且子查询是主键或者唯一索引

index_subquery：和unique_subquery类似，只是子查询使用的是非唯一索引

range：范围扫描，表示检索了指定范围的行，主要用于有限制的索引扫描。比较常见的范围扫描是带有BETWEEN子句或WHERE子句里有>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN()等操作符

index：全索引扫描，和ALL类似，只不过index是全盘扫描了索引的数据。当查询仅使用索引中的一部分列时，可使用此类型。有两种场景会触发：

如果索引是查询的覆盖索引，并且索引查询的数据就可以满足查询中所需的所有数据，则只扫描索引树。此时，explain的Extra 列的结果是Using index。index通常比ALL快，因为索引的大小通常小于表数据

按索引的顺序来查找数据行，执行了全表扫描。此时，explain的Extra列的结果不会出现Uses index

ALL：全表扫描，性能最差

2.Extra常用信息

Using filesort【可优化】

排序未能使用索引
当Query 中包含 ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。数据较少时从内存排序，否则从磁盘排序。Explain不会显示的告诉客户端用哪种排序。官方解释：“MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行”

Using temporary【可优化】

为了解决该查询，MySQL需要创建一个临时表来保存结果。如果查询包含不同列的GROUP BY和 ORDER BY子句，通常会发生这种情况。

Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)【了解】
Join时使用Block Nested Loop或Batched Key Access算法提高join的性能
Using index for group-by【了解】
数据访问和 Using index 一样，所需数据只须要读取索引，一般在使用GROUP BY或DISTINCT 子句时出现，比如优化器优化使用了松散索引扫描

3.show warnings

在终端执行explain后，再执行show warnings，会自动显示SQL的警告信息，方便快速找出SQL问题

二、优化跟踪（OPTIMIZER_TRACE）

1.使用方式

SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;

SET optimizer_trace_offset=-30, optimizer_trace_limit=30;

SELECT * from information_schema.OPTIMIZER_TRACE ot WHERE ot.QUERY LIKE '%t_order%';

2.关键节点

总体分为三块

join_preparation（准备阶段）
join_optimization（优化阶段）
join_execution（执行阶段）

单表看 rows_estimation
多表看 considered_execution_plans
覆盖索引看 best_covering_index_scan中choose为true

三、查询性能估算

估算方法

通过计算磁盘的搜索次数来估算查询性能。对于比较小的表，通常可以在一次磁盘搜索中找到行（因为索引可能已经被缓存了），而对于更大的表，你可以使用B-tree索引进行估算：你需要进行多少次查找才能找到行：log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1