explain.md

# EXPLAIN

Joe 从交易服务中发现了一些高频查询和慢查询，例如

```sql
with recursive r(id) as (
    select id
    from orders
    where id = $1
    union
    select d.id
    from orders as d
          join r on d.id = r.id + 1)
select orders.id, content
from orders
         join r on orders.id = r.id;
```

，他应该从何入手分析优化？

<hr/>

点击进入[MySQL实战练习环境](https://mydev.csdn.net/product/pod/new?image=cimg-centos7-skilltreemysql&connect=auto&create=auto&utm_source=skill){target="_blank"}。

* `show databases;` 列出所有数据库
* `show tables;` 列出所有表

## 答案

在测试库使用 

```sql
explain with recursive r(id) as (
    select id
    from orders
    where id = $1
    union
    select d.id
    from orders as d
          join r on d.id = r.id + 1)
select orders.id, content
from orders
         join r on orders.id = r.id;
```

进行剖分，对于需要了解详细执行计划的使用

```sql
explain analyze with recursive r(id) as (
    select id
    from orders
    where id = 100
    union
    select d.id
    from orders as d
          join r on d.id = r.id + 1)
select orders.id 
from orders
         join r on orders.id = r.id;
```

进行详细分析。

## 选项

### A

对查询设计的字段加索引

### B

备份数据库，删除重建

### C

使用

```sql
analyze with recursive r(id) as (
    select id
    from orders
    where id = 100
    union
    select d.id
    from orders as d
          join r on d.id = r.id + 1)
select orders.id 
from orders
         join r on orders.id = r.id;
```

进行剖分。