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
Vimax
V2EX  ›  MySQL

Mysql 如何优化 SQL 让其 SQL 性能至少要达到 range 级别

  •  1
     
  •   Vimax · 2020-07-24 13:09:39 +08:00 · 2138 次点击
    这是一个创建于 1344 天前的主题,其中的信息可能已经有所发展或是发生改变。

    前端页面需要显示在表单中的格式。

    父级名称需要多次显示,表中只存在一个。

    上级名称 | 子名称

    ---- | ------

    设计 | 功能设计

    设计 | 接口设计

    开发 | 功能开发

    开发 | 接口开发

    有如下初始 sql:

    CREATE TABLE task_type
    (
        id        int AUTO_INCREMENT
            PRIMARY KEY,
        name      varchar(255) NOT NULL COMMENT '任务类型名称',
        parent_id int          NULL COMMENT '父级 id'
    );
    
    INSERT INTO task_type (id, name, parent_id)
    VALUES (2, '设计', 0);
    INSERT INTO task_type (id, name, parent_id)
    VALUES (3, '开发', 0);
    INSERT INTO task_type (id, name, parent_id)
    VALUES (10, '功能设计', 2);
    INSERT INTO task_type (id, name, parent_id)
    VALUES (11, '接口设计', 2);
    INSERT INTO task_type (id, name, parent_id)
    VALUES (12, '功能开发', 3);
    INSERT INTO task_type (id, name, parent_id)
    VALUES (13, '接口开发', 3);
    

    其中 id 是主键,name 和 parent_id 允许重复。

    写了一个查询 sql:

    SELECT t2.name as "parentName", t1.name As "childName"
    FROM task_type t1
             JOIN task_type t2 ON t1.parent_id = t2.id
        AND t1.parent_id != 0
    

    使用EXPLAIN分析后,发现 t1 表的执行效率为All,全表扫描。 t2 表的执行效率为eq_ref

    发现给 sql 加上联合索引或者普通索引,explain 表的结果,t1 表的执行效率最多为index,type=index,索引物理文件全扫描,速度非常慢,这个 index 级别比较 range 还低,与全表扫描是小巫见大巫。

    如何优化 SQL,让其的执行效率可以有所提高呢?

    6 条回复    2020-07-24 14:08:23 +08:00
    tangtj
        1
    tangtj  
       2020-07-24 13:37:22 +08:00   ❤️ 1
    ```
    SELECT p.NAME AS "parentName",c.NAME AS "childName" FROM task_type c
    LEFT JOIN task_type p on c.parent_id = p.id
    WHERE c.parent_id > 0
    ```
    Vimax
        2
    Vimax  
    OP
       2020-07-24 13:47:58 +08:00
    @tangtj 谢谢了。parent_id !=0 也是 range 效率。
    zhangysh1995
        3
    zhangysh1995  
       2020-07-24 13:48:12 +08:00
    ````
    (SELECT t1.parent_id, t1.name as "parentName"
    FROM task_type t1
    WHERE t1.parent_id !=0
    ) AS t
    LEFT JOIN
    (SELECT id, name as "childName"
    FROM task_type) AS t2
    ON t.parent_id = t2.id;
    ````
    zhangysh1995
        4
    zhangysh1995  
       2020-07-24 13:51:49 +08:00
    好像写的有点语法错误,大概意思是,先过滤,然后 left join,右表只取一部分来 join 。。
    Vimax
        5
    Vimax  
    OP
       2020-07-24 14:06:05 +08:00   ❤️ 1
    @zhangysh1995 恩,修改了下。性能也是 range.谢谢。
    ```sql
    SELECT t.parentName,t2.childName from (
    SELECT t1.parent_id, t1.name as "parentName"
    FROM task_type t1
    WHERE t1.parent_id !=0) t
    LEFT JOIN
    (SELECT id, name as "childName"
    FROM task_type) AS t2
    ON t.parent_id = t2.id
    ```
    zhangysh1995
        6
    zhangysh1995  
       2020-07-24 14:08:23 +08:00
    说我加外链不让回复。。能跑就好。。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   4091 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 05:20 · PVG 13:20 · LAX 22:20 · JFK 01:20
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.