您好,登錄后才能下訂單哦!
只是從個人角度分析了下這次比賽的題目,涉及到一些個人經驗,學習交流而已~
貼上官方的git鏈接:
https://github.com/DBbrain/Diagnosis
order 表的數據量 2000
CREATE TABLE `order` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar ( 32) COLLATE utf8_bin NOT NULL,
`creator` varchar(24) COLLATE utf8_bin NOT NULL,
`price` varchar(64) COLLATE utf8_bin NOT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`status` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
);
order_item 表的數據量 499760
CREATE TABLE `order_item` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(32) COLLATE utf8_bin NOT NULL,
`parent` bigint(20) NOT NULL,
`status` int(11) NOT NULL,
`type` varchar(12) COLLATE utf8_bin NOT NULL DEFAULT '0',
`quantity` int(11) NOT NULL DEFAULT '1',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);
SELECT * FROM `order` o INNER JOIN order_item i ON i.parent = o.id
ORDER BY o.status ASC, i.update_time DESC LIMIT 0, 20;
mysql> explain SELECT * FROM `order` o INNER JOIN order_item i
-> ON i.parent = o.id ORDER BY o.status ASC, i.update_time DESC LIMIT 0, 20;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------------------+--------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------------------+--------+----------+---------------------------------+
| 1 | SIMPLE | i | NULL | ALL | NULL | NULL | NULL | NULL | 497839 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | o | NULL | eq_ref | PRIMARY | PRIMARY | 8 | sql_optimization_match.i.parent | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------------------+--------+----------+---------------------------------+
兩張表 inner join,根據執行計劃,驅動表選擇 order_item ,同時由于排序字段來自兩張表,且方向不一致,造成了寫臨時表,由于排序字段不能使用到索引,因此造成了外排序。
order_item.parent = order.id
mysql> select count(distinct(parent)) from order_item; // 區分度較低,忽略
+-------------------------+
| count(distinct(parent)) |
+-------------------------+
| 300 |
+-------------------------+
mysql> select count(distinct(id)) from `order`; // 區分度較高,但 id 上已有主鍵索引
+---------------------+
| count(distinct(id)) |
+---------------------+
| 2000 |
+---------------------+
1 row in set (0.00 sec)
order_item.parent 沒有索引;
order.id 有索引。
ORDER BY o.status ASC, i.update_time DESC
mysql> select count(distinct(status)) from `order`; //區分度很低
+-------------------------+
| count(distinct(status)) |
+-------------------------+
| 2 |
+-------------------------+
mysql> select count(distinct(update_time)) from `order_item`; // 區分度一般
+------------------------------+
| count(distinct(update_time)) |
+------------------------------+
| 32768 |
+------------------------------+
兩張表的 order by 字段排序方式不一樣,可能需要用到外排序,同時原表在排序字段上沒有索引。
根據之前的查詢計劃可以看到,是對 order_item 進行了一次全表掃,之后再進行外排序。由于 sql 語義中需要對兩列進行排序,因此,可以通過其他的方式,減少外排序的數據量,從而降低時耗。
order 表中的排序字段 status 僅有兩個不同值,嘗試去掉 status 排序字段之后,速度明顯提高,此時,order by 中的 update_time 字段可以嘗試增加索引,區分度也滿足要求;
status 僅有兩列,可以使用 union all 來代替,避免 order by 中不同的表不同的排序順序導致無法使用索引。
這里是官方給出的建議(應該不是ML自動改寫的),不過這種改寫sql的方式有一定局限性,適用場景受限,如果 status 類型不是tinyint(1),且以后如果會增加新的類型,sql需要不斷改寫。
可以嘗試推動業務改造,在重新優化索引。
另外,sql改寫之后,給出的索引建議是增加聯合索引(
update_time
,parent
),上面分析可以看到,parent
的區分度較低,這里增加聯合索引或者只給update_time
增加索引,性能相差不多。
# sql 改寫
SELECT o.*,i.* FROM (
( SELECT o.id, i.id item_id FROM `order_1` o
INNER JOIN order_item i ON i.parent =o.id
WHERE o.status = 0
ORDER BY i.update_time DESC LIMIT 0, 20)
UNION ALL
(SELECT o.id, i.id item_id FROM `order_1` o
INNER JOIN order_item i ON i.parent =o.id
WHERE o.status = 1
ORDER BY i.update_time DESC LIMIT 0, 20)
) tmp
INNER JOIN `order_1` o ON tmp.id = o.id
INNER JOIN order_item i ON tmp.item_id = i.id
ORDER BY o.status ASC,
i.update_time DESC
LIMIT 0, 20
# 增加索引
alter table order_item add index `item_idx_1` (`update_time`,`parent`);
# 執行計劃
+----+-------------+------------+------------+--------+---------------+------------+---------+---------------------------------+------+----------+---------------------------------+order_item i ON tmp.ite
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+------------+---------+---------------------------------+------+----------+---------------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 40 | 100.00 | Using temporary; Using filesort |
| 1 | PRIMARY | o | NULL | eq_ref | PRIMARY | PRIMARY | 8 | tmp.id | 1 | 100.00 | NULL |
| 1 | PRIMARY | i | NULL | eq_ref | PRIMARY | PRIMARY | 8 | tmp.item_id | 1 | 100.00 | NULL |
| 2 | DERIVED | i | NULL | index | NULL | item_idx_1 | 12 | NULL | 20 | 100.00 | Using index |
| 2 | DERIVED | o | NULL | eq_ref | PRIMARY | PRIMARY | 8 | sql_optimization_match.i.parent | 1 | 10.00 | Using where |
| 3 | UNION | i | NULL | index | NULL | item_idx_1 | 12 | NULL | 20 | 100.00 | Using index |
| 3 | UNION | o | NULL | eq_ref | PRIMARY | PRIMARY | 8 | sql_optimization_match.i.parent | 1 | 10.00 | Using where |
+----+-------------+------------+------------+--------+---------------+------------+---------+---------------------------------+------+----------+---------------------------------+
1. 對區分度極低的字段如果有排序、范圍比較等操作,可以轉換為 union all;
2. 對排序字段,嘗試使用索引避免 filesort,如果不可避免,在 filesort 之前嘗試減少排序的數據量;
update `order` set create_time = now()
where id in (select parent from order_item where type = 2 );
# 執行計劃
mysql> explain update `order_1` set create_time = now() where id in (select parent from order_item where type = 2 );
+----+--------------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| 1 | UPDATE | order_1 | NULL | index | NULL | PRIMARY | 8 | NULL | 2000 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | order_item | NULL | ALL | NULL | NULL | NULL | NULL | 496836 | 1.00 | Using where |
+----+--------------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
update 的條件是 in 子查詢的方式,explain 中注意到 select type 為 DEPENDENT SUBQUERY ,表示先做外查詢,外查詢匹配到的行數為N,那么接下來會進行N次子查詢,效率極低。
對子查詢通常的做法是轉換為連表查詢 join。
update `order` o inner join (select parent from `order_item` where type = 2) tmp on o.id = tmp.parent set create_time = now();
mysql> explain update `order` o inner join (select parent from `order_item` where type = 2) tmp on o.id = tmp.parent set create_time = now() \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: order_item
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 497839
filtered: 10.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: UPDATE
table: o
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: sql_optimization_match.order_item.parent
rows: 1
filtered: 100.00
Extra: NULL
2 rows in set (0.00 sec)
轉換后的速度相比之前已經快了很多,沒有了dependence subquery,不過還是秒級,驅動表選擇了 order_item 表, 但是基本是一次全表掃,意味著要使用 49w 行的數據和 order 表進行 join,開銷還是很大。
看最原始的慢update,修改的只有 order 表,條件的話只需要 id 在對 order_item 的子查詢范圍內即可,重復的 parent 對于 update 毫無意義,因此,可以對parent字段進行一次聚合(group by),由于子查詢中有 order_item.type = 2 的條件,因此,可以對 type 字段同時進行聚合。
由于 order_item 僅有主鍵索引,對 order_item 表的等值判斷條件和聚合操作使用索引最佳,因此,可以建立聯合索引,索引順序優先等值操作。
額外注意一點,我們要創建索引,索引字段類型和 sql 中的等值類型是否一致。
增加索引:
alter table `order_item` add index idx_1(type,parent);
sql 優化:
update `order` o inner join (
select parent from `order_item`
where type = '2' group by type, parent) i
on o.id = i.parent set create_time = now();
mysql> explain update `order` o inner join ( select parent from `order_item` where type = '2' group by type, parent ) i on o.id = i.parent set create_time = now()\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 571
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: UPDATE
table: o
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: i.parent
rows: 1
filtered: 100.00
Extra: NULL
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: order_item
partitions: NULL
type: range
possible_keys: idx_1
key: idx_1
key_len: 46
ref: NULL
rows: 571
filtered: 100.00
Extra: Using where; Using index for group-by
優化之后,使用了 order_item 表做了驅動表,同時這里也使用到了上面建立的索引 idx_1,之后生成的臨時表和 order 表進行 join。由于 group by 的原因,order_item 生成的結果集數量更少,因此被選為了驅動表。
另外需要注意的是在 group by 這里使用了兩列,這個是為了使用 idx_1 索引(盡管 groiup by parent 和 group by type,parent 的返回結果行數都是一樣的,但是執行計劃還是有很大差距)
優化之后的執行時間在毫秒級。
1. 驅動表的選擇,始終是小表驅動大表,驅動表會走全表掃,所以通常索引都是在被驅動表上增加;
2. 如果執行計劃中出現了 DEPENDENT SUBQUERY,一定會對 sql 的執行效率有影響(同時 DEPENDENT SUBQUERY 還會潛在地造成一定程度的鎖放大), in + 子查詢 方式很容易引起,可以將子查詢優化為 join 操作;
3. 對于 join 連表查詢,進行連表的數據越少,執行效率就越高,因此,在不改變sql語義的前提下,盡量使參加 join 的數據量減少;
4. 關于索引順序: 等值條件 > group by > order by
5. 注意索引字段類型和 sql 中的的判斷條件中的數據類型是否一致。
區分度的計算過程省略,這里直接給出區分度好壞
由于某些表的行數較多,區分度的計算使用的是統計前5000行中 distinct 的值(生成環境中也可以這樣做,可以降低計算區分度帶來的額外開銷),極端情況下部分小表可能會造成誤判,但行數極少的表加索引的意義也不是很大。
# customer 數據量 1,200,000
CREATE TABLE `customer` (
`custkey` int(11) NOT NULL, // 區分度 OK
`name` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, // 區分度 OK
`address` varchar(40) NOT NULL,
`nationkey` int(11) NOT NULL, // 區分度較低
`phone` char(15) NOT NULL, // 區分度 OK
`acctbal` decimal(15,2) NOT NULL,
`mktsegment` char(10) NOT NULL, // 區分度較低
`comment` varchar(117) NOT NULL,
PRIMARY KEY (`custkey`),
KEY `idx_nationkey` (`nationkey`)
);
# nation 數據量 25
CREATE TABLE `nation` (
`nationkey` int(11) NOT NULL, // 區分度 OK
`name` char(25) NOT NULL,
`regionkey` int(11) NOT NULL,
`comment` varchar(152) DEFAULT NULL,
PRIMARY KEY (`nationkey`),
KEY `idx_4_0` (`name`)
);
# orders 數據量12,000,000
CREATE TABLE `orders` (
`orderkey` int(11) NOT NULL,
`custkey` int(11) NOT NULL, // 區分度 OK
`orderstatus` varchar(1) NOT NULL,
`totalprice` decimal(15,2) NOT NULL, // 區分度 OK
`orderdate` date NOT NULL,
`orderpriority` char(15) NOT NULL,
`clerk` char(15) NOT NULL, // 區分度 OK
`shippriority` int(11) NOT NULL,
`comment` varchar(79) NOT NULL,
PRIMARY KEY (`orderkey`)
);
# region 數據量 5
CREATE TABLE `region` (
`regionkey` int(11) NOT NULL,
`name` varchar(25) NOT NULL,
`comment` varchar(152) DEFAULT NULL,
PRIMARY KEY (`regionkey`)
);
select c.custkey, c.phone, sum(o.totalprice) totalprice
from nation n
inner join customer c on c.nationkey = n.nationkey
inner join orders o on o.clerk = c.name
where n.name = "CHINA" and c.mktsegment = "HOUSEHOLD" and c.phone like "28-520%"
group by c.custkey, c.phone;
# 執行計劃
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
| 1 | SIMPLE | n | NULL | ALL | PRIMARY | NULL | NULL | NULL | 25 | 10.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | c | NULL | ALL | NULL | NULL | NULL | NULL | 1189853 | 0.11 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | o | NULL | ALL | NULL | NULL | NULL | NULL | 10963843 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
三張表 【customer c】;【nation n】;【orders o】
where條件:
c.mktsegment = "HOUSEHOLD":區分度較低,放棄
? c.phone like "28-520%":區分度較好,考慮添加索引
聚合條件:
group by c.custkey: 區分度較好,但是已經是主鍵,放棄
? c.phone:同where,考慮添加
join 條件:
c.nationkey = n.nationkey:區分度較低,放棄
? o.clerk = c.name:區分度較高,考慮添加索引
advice:
add index `dx_1_0`(name);
add index `idx_1_1` (phone);
數據量 25,不考慮添加索引
nation 表可以考慮增加索引 add index `idx_1_0`(name); 但意義不大
join 條件:
? o.clerk = c.name:區分度較高,考慮添加索引
advice:
add index `idx_1_0` (clerk)
按上述分析增加三條索引后,執行計劃如下
+----+-------------+-------+------------+--------+----------------+---------+---------+-------------------+----------+----------+---------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+----------------+---------+---------+-------------------+----------+----------+---------------------------------------------------------------------+
| 1 | SIMPLE | c | NULL | range | dx_1_0,idx_1_1 | idx_1_1 | 45 | NULL | 46 | 10.00 | Using index condition; Using where; Using temporary; Using filesort |
| 1 | SIMPLE | n | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dbaas.c.nationkey | 1 | 10.00 | Using where |
| 1 | SIMPLE | o | NULL | ALL | idx_1_0 | NULL | NULL | NULL | 10963843 | 10.00 | Range checked for each record (index map: 0x2) |
+----+-------------+-------+------------+--------+----------------+---------+---------+-------------------+----------+----------+---------------------------------------------------------------------+
1. 在 inner join 的情況下,我們無法判斷出驅動表,因此,我們會選擇在合適的字段上都添加索引;
2. 在 sql 中的條件類型較多時,選擇把等值條件和聚合條件添加為組合索引,join 條件單獨增加索引;
3. 如果數據量過少,增加索引意義不大,可以不考慮;
4. dbrain 給出的是組合索引,兩者相比,性能基本一致;
select * from (
select custkey, orderdate, sum(totalprice) as totalprice
from orders group by custkey, orderdate
) o
where orderdate = "2019-08-01"
# 執行計劃
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+----------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+----------+----------+---------------------------------+
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 3 | const | 10 | 100.00 | NULL |
| 2 | DERIVED | orders | NULL | ALL | NULL | NULL | NULL | NULL | 10963843 | 100.00 | Using temporary; Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+----------+----------+---------------------------------+
僅涉及到一張表,group by 用到了 filesort,sql 看上去并不復雜,但是卻產生了驅動表。
查看sql,發現select * from (子查詢),多余的嵌套,可以考慮去掉,sql 可以改寫為
select custkey, orderdate, sum(totalprice) as totalprice
from orders where orderdate = "2019-08-01" group by custkey, orderdate;
索引分析
where 條件:
? orderdate = "2019-08-01":區分度較高,考慮增加索引
聚合條件:
? group by custkey, orderdate:兩個字段區分區都較高,考慮增加索引
advice:
等值條件 優先于 聚合條件
add index `idx_2_0` (orderdate, custkey)
使用優化后的 sql,增加聯合索引,執行計劃為
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | orders | NULL | ref | idx_2_0 | idx_2_0 | 3 | const | 1 | 100.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
如果是增加兩個單獨的索引,
add index `idx_2_1` (custkey);
add index `idx_2_2` (orderdate);
執行計劃為
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+--------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+--------------------------------------------------------+
| 1 | SIMPLE | orders | NULL | ref | idx_2_2 | idx_2_2 | 3 | const | 1 | 100.00 | Using index condition; Using temporary; Using filesort |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+--------------------------------------------------------+
使用到了 filesort,只有custkey可以使用索引,因此建議聯合索引。
1. 單張表的sql如果執行計劃出現 filesort 等需要關注,頻繁嵌套的子查詢,會對性能有一定影響,可以考慮 sql 重寫;
2. 關于加索引,等值條件要優先于聚合、join等條件;
select c.custkey, sum(o.totalprice) totalprice from customer c
left join orders o on o.custkey = c.custkey
where c.phone like "33-64%" and c.name like concat("Customer#00003", "%")
group by c.custkey
在已經有前兩條 sql 增加的索引前提下,執行計劃為
+----+-------------+-------+------------+-------+------------------------+---------+---------+------+----------+----------+---------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+------------------------+---------+---------+------+----------+----------+---------------------------------------------------------------------+
| 1 | SIMPLE | c | NULL | range | PRIMARY,dx_1_0,idx_1_1 | idx_1_1 | 45 | NULL | 552 | 1.63 | Using index condition; Using where; Using temporary; Using filesort |
| 1 | SIMPLE | o | NULL | ALL | NULL | NULL | NULL | NULL | 10963843 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+------------------------+---------+---------+------+----------+----------+---------------------------------------------------------------------+
customer 表已經使用到了索引,是否需要增加其它索引稍后分析;
order 表是走了全表掃,掃描 12,000,000 行數據,可能缺少索引;
兩張表 【customer c】;【order o】
customer
where條件:
c.phone like "33-64%":第一條 select 已經添加過索引
c.name like concat("Customer#00003", "%"):第一條 select 已經添加過索引
聚合條件:
group by c.custkey: 區分度較好,但是已經是主鍵,放棄
join 條件:
o.custkey = c.custkey:區分度較好,但是已經是主鍵,放棄
advice:
無建議
order
join 條件:
o.custkey = c.custkey:區分度較高,考慮增加索引
advice:
add index `idx_3_0` (custkey)
增加索引之后的執行計劃為:
+----+-------------+-------+------------+-------+------------------------+---------+---------+-----------------+------+----------+---------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+------------------------+---------+---------+-----------------+------+----------+---------------------------------------------------------------------+
| 1 | SIMPLE | c | NULL | range | PRIMARY,dx_1_0,idx_1_1 | idx_1_1 | 45 | NULL | 552 | 1.63 | Using index condition; Using where; Using temporary; Using filesort |
| 1 | SIMPLE | o | NULL | ref | idx_3_0 | idx_3_0 | 4 | dbaas.c.custkey | 13 | 100.00 | NULL |
+----+-------------+-------+------------+-------+------------------------+---------+---------+-----------------+------+----------+---------------------------------------------------------------------+
增加索引之后。掃描 order 表的行數已經大大減少,執行效率也提升很高
1. 關于 【using where】, 【using index】, 【using index condition】; 【Using where &&Using index】的區別(為什么總結這個呢,我這邊的話是建立了兩個庫,數據和基本的表結構是一致的,但是其中一個庫中表的索引是按照我自己分析的情況加上去的,另一個庫是官方給出的建議,發現在執行效率都很高的情況下,兩者執行計劃的 extra 內容有所區別,本想 google 解決,但是看了排名前三的博客,兩篇的內容是一樣的,和第三篇的解釋完全不同,自己嘗試了下,這里給出結論,最后附上測試流程):
select c.custkey, c.phone from nation n
inner join customer c on c.nationkey = n.nationkey
where n.name = "CHINA" and exists (
select 1 from orders o where o.custkey = c.custkey and o.orderdate = "1998-08-11");
在上面已有的索引前提下,執行計劃為
+----+--------------------+-------+------------+------+-----------------+---------+---------+-----------------------+---------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+------+-----------------+---------+---------+-----------------------+---------+----------+----------------------------------------------------+
| 1 | PRIMARY | n | NULL | ref | PRIMARY,idx_1_0 | idx_1_0 | 75 | const | 1 | 100.00 | Using index |
| 1 | PRIMARY | c | NULL | ALL | NULL | NULL | NULL | NULL | 1189853 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
| 2 | DEPENDENT SUBQUERY | o | NULL | ref | idx_2_0,idx_3_0 | idx_2_0 | 7 | const,dbaas.c.custkey | 1 | 100.00 | Using index |
+----+--------------------+-------+------------+------+-----------------+---------+---------+-----------------------+---------+----------+----------------------------------------------------+
看到了 DEPENDENT SUBQUERY,在 in/exists +子查詢的條件下。經常會出現,有什么危害上面有解釋,出現了這個東西,就要想辦法改寫 sql。既然是 exists + 子查詢,那么優化策略就是改寫為 join。
最通俗的改寫方式:先全部 inner join,最后加 where 條件
select c.custkey, c.phone from nation n
inner join customer c on c.nationkey = n.nationkey
inner join orders o on o.custkey = c.custkey
where n.name = "CHINA" and o.orderdate = "1998-08-11";
官方給出的 sql 比較復雜,但做的事情差不多,多考慮了一點試圖使用 group by 來減少 join 的數據量,給出官方答案,這里不多解釋它【不過這里去掉 group by 會更好】
SELECT `t1`.`custkey`, `t1`.`phone` FROM
( SELECT * FROM `dbaas`.`nation` AS `t` WHERE `t`.`name` = 'CHINA' ) AS `t0`
INNER JOIN `dbaas`.`customer` AS `t1`
ON `t0`.`nationkey` = `t1`.`nationkey`
INNER JOIN (
SELECT `t2`.`custkey` FROM `dbaas`.`orders` AS `t2`
WHERE `t2`.`orderdate` = '1998-08-11' GROUP BY `t2`.`custkey` ) AS `t5`
ON `t1`.`custkey` = `t5`.`custkey`
索引建議的話這里就沒有太多了,條件字段已經都有了相應的索引。
優化后的執行計劃如下:
+----+-------------+-------+------------+--------+-----------------+---------+---------+-----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+-----------------+---------+---------+-----------------+------+----------+-------------+
| 1 | SIMPLE | n | NULL | ref | PRIMARY,idx_1_0 | idx_1_0 | 75 | const | 1 | 100.00 | Using index |
| 1 | SIMPLE | o | NULL | ref | idx_2_0,idx_3_0 | idx_2_0 | 3 | const | 20 | 100.00 | Using index |
| 1 | SIMPLE | c | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dbaas.o.custkey | 1 | 10.00 | Using where |
+----+-------------+-------+------------+--------+-----------------+---------+---------+-----------------+------+----------+-------------+
可以看到兩次join的驅動表分別選擇了n和o,ref 也是 const,性能要比 DEPENDENT SUBQUERY 這種要好太多了
1. 并不是所有的復雜 join 都要使用 group by,和數據分布有關,如果 group by 并不能顯著降低 join 行數的話, 沒有必要;
mysql 的查詢優化器相對來說是一個比較復雜的邏輯,期待它可以更好工作的前提是sql的寫法要合理,同時也要有恰當的索引。
我們對 sql 的優化,通常是先去考慮優化sql,再根據優化后的 sql 增加所需索引。(在實際數據庫開發過程中,尤其是 2B 的服務提供端,我們會優先在不需要業務改動的情況下增加所需索引嘗試解決慢查詢的問題,如果增加索引不能解決問題,那么就需要業務進行相應改造)
首先,關于sql改寫,這個要考慮的比較多,因為mysql的優化器、執行器做了太多的事情,靠AI可以自動改寫優化的的話,不敢想象。。。(DBA又要有一波人下崗了)人工的話根據經驗吧,根據執行計劃中的異常點去考慮改進,比如子查詢改寫為 join 等,像預賽題目中關于order by status 改寫為 union all 的做法,確實有一定的效果,但是并不是一個通用的方法,這里就太靈活了;
其次,相比之下,在已知sql和表結構的情況下,依靠AI給出索引建議還是更加讓人有真實感,索引的話有一些通用的規則,網上介紹的很多了,自己瞎寫了點,看看就好
1. 找出所有條件字段,計算字段區分度,區分度很低的字段沒有必要加索引,數據量很少的字段一樣,加上意義也不大;
2. 條件的話優先級 等值 > 聚合(group/order by) > join ,同一優先級根據區分度建立聯合索引;
3. 在聚合條件下,如果聚合后的行數太多,回表量太大的情況下,mysql 可能不會使用這些索引;
4. 驅動表不需要考慮什么索引,驅動表的數據必然都在join的結果集中,對于 inner join 這種無法確定驅動表的,可以考慮在兩張表的合適字段上都添加索引
繼續扯。。。
現在大家都在往云上遷業務,云上的db智能診斷難以避免是以后的剛需。AI 可以做到哪一步,不清楚,以后的事情誰知道呢。
免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。