请教下MySQL一次性从驱动表取多少行数据?
EXPLAIN ANALYZE
SELECT A.ID,A.name AS FATHER, C.NAME AS MOTHER,B.NAME AS CHILDER,B.SEX
FROM father A
LEFT JOINchilder B ON A.ID=B.father_id
LEFT JOINmother CON A.ID=husband_id;
-> Nested loop left join(cost=50.65 rows=252) (actual time=0.360..1.926 rows=9 loops=1)
-> Nested loop left join(cost=9.70 rows=63) (actual time=0.248..1.163 rows=9 loops=1)
-> Table scan on A(cost=1.15 rows=9) (actual time=0.162..0.205 rows=9 loops=1)
-> Filter: (b.father_id = a.id)(cost=0.33 rows=7) (actual time=0.079..0.105 rows=1 loops=9)
-> Table scan on B(cost=0.33 rows=7) (actual time=0.063..0.100 rows=7 loops=9)
-> Filter: (c.husband_id = a.id)(cost=0.26 rows=4) (actual time=0.069..0.083 rows=0 loops=9)
-> Table scan on C(cost=0.26 rows=4) (actual time=0.049..0.080 rows=4 loops=9)
从A表一次性取9行数据 到服务层,然后循环跟B表做关联?还是一次从引擎INNODB 取一行数据?
假如是一次性取9行, 那么放在服务层哪个内存里? 服务层哪个内存参数可调整大小?
同时观察optimize_trace，再看有没有下推 yejr 发表于 2023-9-1 09:20
同时观察optimize_trace，再看有没有下推
我问这步 是一次性读取全部9行技术 放到服务层?
-> Table scan on A(cost=1.15 rows=9) (actual time=0.162..0.205 rows=9 loops=1) innerDBA 发表于 2023-9-1 10:41
我问这步 是一次性读取全部9行技术 放到服务层?
-> Table scan on A(cost=1.15 rows=9) (actual time= ...
EXPLAIN
SELECT A.ID,A.name AS FATHER, C.NAME AS MOTHER,B.NAME AS CHILDER,B.SEX
FROM father A
LEFT JOINchilder B ON A.ID=B.father_id
LEFT JOINmother CON A.ID=husband_id; 从这个执行计划贴出来看看 可能更好解释
页:
[1]