为什么这个 SQL 语句做了全表扫描？

CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, username CHAR(10) UNIQUE ); CREATE TABLE business ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, FOREIGN KEY (user_id) REFERENCES user (id) ); CREATE TABLE asset ( id INT PRIMARY KEY AUTO_INCREMENT, business_id INT NOT NULL, FOREIGN KEY (business_id) REFERENCES business (id) ); CREATE TABLE task ( id INT PRIMARY KEY AUTO_INCREMENT, asset_id INT NOT NULL, FOREIGN KEY (asset_id) REFERENCES asset (id) ); CREATE TABLE problem ( id INT PRIMARY KEY AUTO_INCREMENT ); CREATE TABLE result ( id INT PRIMARY KEY AUTO_INCREMENT, task_id INT NOT NULL, problem_id INT NOT NULL, FOREIGN KEY (task_id) REFERENCES task (id), FOREIGN KEY (problem_id) REFERENCES problem (id) );

这是查询语句：

EXPLAIN SELECT problem.*, business.* FROM user INNER JOIN business INNER JOIN asset INNER JOIN task INNER JOIN result INNER JOIN problem ON (user.username = 'user1' AND business.user_id = user.id AND asset.business_id = business.id AND task.asset_id = asset.id AND result.task_id = task.id AND problem.id = result.problem_id ) GROUP BY problem.id, business.id;

这是输出结果：

+----+-------------+----------+------------+--------+---------------------+----------+---------+-----------------------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+--------+---------------------+----------+---------+-----------------------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | user | <null> | const | PRIMARY,username | username | 11 | const | 1 | 100.0 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | problem | <null> | index | PRIMARY | PRIMARY | 4 | <null> | 5 | 100.0 | Using index | | 1 | SIMPLE | result | <null> | ALL | task_id,problem_id | <null> | <null> | <null> | 6 | 25.0 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | task | <null> | eq_ref | PRIMARY,asset_id | PRIMARY | 4 | tmp.result.task_id | 1 | 100.0 | <null> | | 1 | SIMPLE | asset | <null> | eq_ref | PRIMARY,business_id | PRIMARY | 4 | tmp.task.asset_id | 1 | 100.0 | <null> | | 1 | SIMPLE | business | <null> | eq_ref | PRIMARY,user_id | PRIMARY | 4 | tmp.asset.business_id | 1 | 50.0 | Using where | +----+-------------+----------+------------+--------+---------------------+----------+---------+-----------------------+------+----------+----------------------------------------------------+

从结果的第三行可以看出，MySQL 对 result 表做了全表扫描，这是为什么了？

5 条回复 2018-03-15 20:55:04 +08:00

F281M6Dh8DXpD1g2

2018-03-15 14:10:03 +08:00

什么是 CBO 了解一下

skyleft

2018-03-15 14:16:09 +08:00

user.username = 'user1' 应该放在外面加 where 里吧

j0hnj

2018-03-15 14:25:10 +08:00

@skyleft on 其实跟 where 没有区别，mysql 处理后都是 where

jusalun

2018-03-15 14:31:48 +08:00

你好歹把测试数据也给出来啊，你只给出结构定义和 sql，我自己造的数据打执行计划全走索引了啊

1 SIMPLE user const PRIMARY,username username 41 const 1 100 Using index; Using temporary; Using filesort
1 SIMPLE business ref PRIMARY,user_id user_id 4 const 1 100 Using index
1 SIMPLE asset ref PRIMARY,business_id business_id 4 test_db.business.id 1 100 Using index
1 SIMPLE task ref PRIMARY,asset_id asset_id 4 test_db.asset.id 1 100 Using index
1 SIMPLE result ref task_id,problem_id task_id 4 test_db.task.id 1 100
1 SIMPLE problem eq_ref PRIMARY PRIMARY 4 test_db.result.problem_id 1 100 Using index

sizhitu

2018-03-15 20:55:04 +08:00

是不是 result 表数据量比较小，全表扫描比走索引的效率高？