此时explain分析后你会发现，虽然使用的是相同的索引，但是Extra这一栏的信息已经变了，Extra信息如下，Using index condition; Using aggregate; Using index Using aggregate 这条sql会使用mysql内置的聚合函数进行分组聚合的操作。我们来具体分析下，因为group by此次是按dir_id文件夹id进行分组的，而dir_id刚好可以用...
1. row *** EXPLAIN: -> Aggregate: count(0) (actual time=26501.232..26501.233 rows1 loops=1) -> Nested loop leftjoin (cost=810799.35 rows=4550475 (actual time=0.036..22572.530 rows=77902616 loops=1) -> Index scan on t1 usingidx3 (cost=108280.56 rows=986408) (actual time=0.026....
1 EXPLAIN FORMAT=TREE 2 SELECT first_name, last_name, SUM(amount) AS total 3 FROM staff INNER JOIN payment 4 ON staff.staff_id = payment.staff_id 5 AND 6 payment_date LIKE '2005-08%' 7 GROUP BY first_name, last_name; 8 9 -> Table scan on <temporary>10 -> Aggregate using tem...
MySQL explains howit would process the statement, including information about how tables are joinedand in which order. EXPLAIN EXTENDED can be used to provide additionalinformation.
-> Aggregate using temporary table (actual time=58.104..58.104 rows=2 loops=1) -> Nested loop inner join (cost=1757.30 rows=1787) (actual time=0.816..46.135 rows=5687 loops=1) -> Table scan on staff (cost=3.20 rows=2) (actual time=0.047..0.051 rows=2 loops=1) ...
EXPLAIN的输出 EXPLAIN主要包含以下信息: id (JSON name: select_id) SQL查询中的序列号。 select_type (JSON name: none) 查询的类型,可以是下表的任何一种类型: 查询类型例子: 1、SIMPLE 简单查询(不适用union和子查询的) mysql> explain select * from staff; ...
与explain analyze类似,同时包含了以预估的每一步的代价信息,仅仅是预估信息,并不包含实际执行信息 1->Tablescanon<temporary>2->Aggregate usingtemporarytable3->Nested loopinnerjoin(cost=1757.30rows=1787)4->Tablescanonstaff (cost=3.20rows=2)5->Filter: (payment.payment_datelike'2005-08%') (cost=117.43...
using <auto_key0> (dept_id=e.dept_id) (actual time=0.011..0.015 rows=1 loops=25) -> Materialize (actual time=0.048..0.057 rows=1 loops=25) -> Group aggregate: avg(employee.salary) (actual time=0.228..0.510 rows=5 loops=1) -> Index scan on employee using idx_emp_dept (cost=...
EXPLAIN FORMAT=TREE SELECT first_name, last_name, SUM(amount) AS total FROM staff INNER JOIN payment ON staff.staff_id = payment.staff_id AND payment_date LIKE '2005-08%' GROUP BY first_name, last_name; -> Table scan on <temporary> -> Aggregate using temporary table -> Nested loop ...
*** EXPLAIN: -> Aggregate: count(1) (cost366.05 rows=331) (actual time=23.188..23.189 rows=1 loops=1) -> Nested loop inner join (cost=332.95 rows=331) (actual time=1.421..22.925rows=302 loops=1) -> Filter: ((b.b > 100) and (b.c is not null)) (cost=101.25 rows=662) (...