为什么这个 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

liprais

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 表数据量比较小，全表扫描比走索引的效率高？