id select_typetablepartitions type possible_keyskeykey_len ref rows filtered Extra--- --- --- --- --- --- --- --- --- --- --- ---1SIMPLE a (NULL)ALL(NULL) (NULL) (NULL) (NULL)1100.00(NULL)1SIMPLE b (NULL)ALL(NULL) (NULL) (NULL) (NULL)1100.00Usingwhere; Usingjoinbu...
EXPLAIN select * from t_user t1 WHERE t1.username IN (SELECT t2.username FROM t_user2 t2 WHERE t1.create_time = t2.create_time) or username = 'c7c70eca-903b-44b7-9a76-b3286bfa1d3a'; 当使用in到子查询时,而且in的对象是一个二级索引时,就会出现index_subquery 4.9 table = range EXPLAI...
3.1 table 3.2 id 3.3 select_type 3.4 partition(略):匹配的分区信息 3.5 type 3.6 possible_keys和key 3.7 key_len 3.8 ref 3.9 rows 3.10 filtered 3.11 Extra 1.关于EXPLAIN 官网介绍 https://dev.mysql.com/doc/refman/5.7/en/explain-output.html https://dev.mysql.com/doc/refman/8.0/en/explain-...
Each row in the explain table represents a result, up to the point of evaluation. The operators that can appear in a DFE query plan are as follows: DFEApply – Executes the function specified by functor in the arguments section, on the value stored in the specified variable DFEAlgoWrite...
unique_subquery和eq_ref不一样的地方是使用了in的子查询: valueIN(SELECTprimary_keyFROMsingle_tableWHEREsome_expr) unique_subquery is just an index lookup function that replaces the subquery completely for better efficiency. unique_subquery是一个索引查找函数,代替子查询提高效率。
1.SIMPLE：简单的SELECT查询，不包含子查询和联合查询。2.PRIMARY：当查询中包含子查询或联合查询时，最外层的SELECT语句的类型为PRIMARY。3.SUBQUERY：子查询中第一个SELECT的类型，例如SELECT * FROM table1 WHERE column1 IN (SUBQUERY)中括号内子查询的select_type就是SUBQUERY。4.DERIVED：表示派生表的SELECT，...
SELECT...|tableFunction(...)[COLUMNS(...)] [ORDERBY...] [PARTITIONBY...] [PRIMARY KEY] [SAMPLEBY...] [TTL ...] ] [FORMAT ...] 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 例如,执行 EXPLAIN 查看如下 SQL 的执行计划: ...
下面我们使用 explain 做一个查询，如下：mysql> explain select * from payment;+---+---+---+---+---+---+---+---+---+---+---+---+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+---+--...
row *** Id: 2 Select_type: SIMPLE Table: pq_test Partitions: NULL Type: index Possible_keys: NULL Key: PRIMARY Key_len: 8 Ref: NULL Rows: 10032494 Filtered: 100.00 Extra: Parallel scan (2 workers); Using index 2 rows in set, 1 warning (0.00 sec) PolarDB MySQL版8.0.2版本中,通过...
SHOW CREATE TABLE <table>命令是最简单的查看表和索引列细节的方式。和key 列相关的列还包括possible_keys、rows 以及key_len。 2.2 ROWS rows 列提供了试图分析所有存在于累计结果集中的行数目的MySQL 优化器估计值。QEP 很容易描述这个很困难的统计量。