V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
j0hnj
V2EX  ›  MySQL

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

  •  
  •   j0hnj · 2018-03-15 13:59:05 +08:00 · 3535 次点击
    这是一个创建于 2206 天前的主题,其中的信息可能已经有所发展或是发生改变。

    表结构是这样的:

    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
        1
    liprais  
       2018-03-15 14:10:03 +08:00
    什么是 CBO 了解一下
    skyleft
        2
    skyleft  
       2018-03-15 14:16:09 +08:00
    user.username = 'user1' 应该放在外面 加 where 里吧
    j0hnj
        3
    j0hnj  
    OP
       2018-03-15 14:25:10 +08:00
    @skyleft on 其实跟 where 没有区别,mysql 处理后都是 where
    jusalun
        4
    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
        5
    sizhitu  
       2018-03-15 20:55:04 +08:00
    是不是 result 表数据量比较小,全表扫描比走索引的效率高?
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2750 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 12:13 · PVG 20:13 · LAX 05:13 · JFK 08:13
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.