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

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

2020-07-24 13:37:22 +08:00

```
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

2020-07-24 13:47:58 +08:00

@tangtj 谢谢了。parent_id !=0 也是 range 效率。

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

2020-07-24 13:51:49 +08:00

好像写的有点语法错误，大概意思是，先过滤，然后 left join，右表只取一部分来 join 。。

Vimax

2020-07-24 14:06:05 +08:00

@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

2020-07-24 14:08:23 +08:00

说我加外链不让回复。。能跑就好。。