今天繼續(xù)給大家分享一個(gè) SQL 優(yōu)化案例。

問題描述

已知表結(jié)構(gòu)如下：

CREATE TABLE `customer` (
`C_CUSTKEY` int NOT NULL,
`C_NAME` varchar(25) NOT NULL,
`C_ADDRESS` varchar(40) NOT NULL,
`C_NATIONKEY` int NOT NULL,
`C_PHONE` char(15) NOT NULL,
`C_ACCTBAL` decimal(15,2) NOT NULL,
`C_MKTSEGMENT` char(10) NOT NULL,
`C_COMMENT` varchar(117) NOT NULL,
PRIMARY KEY (`C_CUSTKEY`)
) ENGINE=InnoDB;

CREATE TABLE `orders` (
`O_ORDERKEY` int NOT NULL,
`O_CUSTKEY` int NOT NULL,
`O_ORDERSTATUS` char(1) NOT NULL,
`O_TOTALPRICE` decimal(15,2) NOT NULL,
`O_ORDERDATE` date NOT NULL,
`O_ORDERPRIORITY` char(15) NOT NULL,
`O_CLERK` char(15) NOT NULL,
`O_SHIPPRIORITY` int NOT NULL,
`O_COMMENT` varchar(79) NOT NULL
PRIMARY KEY (`C_ORDERKEY`)
) ENGINE=InnoDB;

請(qǐng)問下面的查詢語(yǔ)句應(yīng)該如何優(yōu)化？

SELECT o_custkey, c_name, sum(o.o_totalprice)
FROM customer c, orders o 
WHERE o_custkey = c_custkey 
GROUP BY o_custkey, c_name
ORDER BY c_name;

思路分析

首先，這個(gè)查詢的作用是統(tǒng)計(jì)每個(gè)客戶的訂單金額匯總，數(shù)據(jù)來自 orders 表；最后按照客戶名稱排序，名稱來自 customer 表，兩個(gè)表通過 c_custkey 字段關(guān)聯(lián)?？匆幌聢?zhí)行計(jì)劃：

EXPLAIN
select c_custkey, c_name, sum(o.o_totalprice)
from customer c, orders o 
where o_custkey = c_custkey 
group by c_name, c_custkey
order by c_name;

id|select_type|table|partitions|type  |possible_keys|key    |key_len|ref           |rows|filtered|Extra                          |
--+-----------+-----+----------+------+-------------+-------+-------+--------------+----+--------+-------------------------------+
 1|SIMPLE     |o    |          |ALL   |             |       |       |              |   1|   100.0|Using temporary; Using filesort|
 1|SIMPLE     |c    |          |eq_ref|PRIMARY      |PRIMARY|4      |hr.o.O_CUSTKEY|   1|   100.0|                               |

orders 表使用了 ALL 全表掃描，由于 group by，所以 Extra 包含了臨時(shí)表（Using temporary）；由于 order by，所以 Extra 包含了排序操作（Using filesort）。這些都會(huì)導(dǎo)致性能問題。

第一步，考慮到每個(gè)客戶的訂單金額匯總只需要 orders 表中的數(shù)據(jù)，可以單獨(dú)按照 o_custkey 分組，并且基于 o_custkey 和 o_totalprice 創(chuàng)建復(fù)合索引，利用索引覆蓋掃描優(yōu)化；然后再和 customer 表關(guān)聯(lián)。另外，c_custkey 是主鍵，決定了 c_name，延遲關(guān)聯(lián)可以避免 group by 中的 c_name。

-- 創(chuàng)建索引
CREATE INDEX idx_orders_custkey_totalprice ON orders(o_custkey, o_totalprice);

EXPLAIN
select o.o_custkey, c.c_name, o.sumprice
from
(select o_custkey, sum(o_totalprice) sumprice
from orders 
group by o_custkey) o, customer c
where o_custkey = c_custkey 
order by c_name;

id|select_type|table     |partitions|type |possible_keys                |key                          |key_len|ref           |rows|filtered|Extra         |
--+-----------+----------+----------+-----+-----------------------------+-----------------------------+-------+--------------+----+--------+--------------+
 1|PRIMARY    |c         |          |ALL  |PRIMARY                      |                             |       |              |   1|   100.0|Using filesort|
 1|PRIMARY    |<derived2>|          |ref  |<auto_key0>                  |<auto_key0>                  |4      |hr.c.C_CUSTKEY|   2|   100.0|              |
 2|DERIVED    |orders    |          |index|idx_orders_custkey_totalprice|idx_orders_custkey_totalprice|11     |              |   1|   100.0|Using index   |

子查詢 o 已經(jīng)優(yōu)化完成，Extra 中的 Using index 顯示了索引覆蓋掃描。剩下的問題就是 customer 表上的排序操作，對(duì)應(yīng) Extra 中的 Using filesort。

第二步，基于 c_name 字段創(chuàng)建索引，避免排序操作。

-- 創(chuàng)建索引
CREATE INDEX idx_customer_name ON customer(c_name);

EXPLAIN
select c.c_custkey, c.c_name, o.sumprice
from
(select o_custkey, sum(o_totalprice) sumprice
from orders 
group by o_custkey) o, customer c
where o_custkey = c_custkey 
order by c_name;

id|select_type|table     |partitions|type |possible_keys                |key                          |key_len|ref           |rows|filtered|Extra      |
--+-----------+----------+----------+-----+-----------------------------+-----------------------------+-------+--------------+----+--------+-----------+
 1|PRIMARY    |c         |          |index|PRIMARY                      |idx_customer_name            |102    |              |   1|   100.0|Using index|
 1|PRIMARY    |<derived2>|          |ref  |<auto_key0>                  |<auto_key0>                  |4      |hr.c.C_CUSTKEY|   2|   100.0|           |
 2|DERIVED    |orders    |          |index|idx_orders_custkey_totalprice|idx_orders_custkey_totalprice|11     |              |   1|   100.0|Using index|

可以看到，對(duì)于 customer 表的訪問，也使用了索引覆蓋掃描，因?yàn)槎?jí)索引包含了主鍵字段（c_custkey）。

額外補(bǔ)充，還有一種寫法就是調(diào)整 group by 字段的順序，把 c_name 放在第一位，并且把 c_custkey 改成 o_custkey：

EXPLAIN 
select o_custkey, c_name, sum(o.o_totalprice)
from customer c, orders o 
where o_custkey = c_custkey 
group by c_name, o_custkey
order by c_name;

id|select_type|table|partitions|type |possible_keys                |key                          |key_len|ref           |rows|filtered|Extra      |
--+-----------+-----+----------+-----+-----------------------------+-----------------------------+-------+--------------+----+--------+-----------+
 1|SIMPLE     |c    |          |index|PRIMARY,idx_customer_name    |idx_customer_name            |102    |              |   1|   100.0|Using index|
 1|SIMPLE     |o    |          |ref  |idx_orders_custkey_totalprice|idx_orders_custkey_totalprice|4      |hr.c.C_CUSTKEY|   1|   100.0|Using index|

留個(gè)作業(yè)，為什么呢？文章來源地址http://www.zghlxwxcb.cn/news/detail-634249.html

到了這里，關(guān)于SQL面試題：第二個(gè)優(yōu)化案例的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！