【背景】
周末比较闲,我这个人又没有什么爱好,当然了读书除外;前一些天我一个同事说:“你一个dba想去写一本“django”书,合适吗?”
我想也是,一个人不能忘了本,所以MySQL还是要好好的搞一搞的;自那时起决定每周至少要写一篇MySQL博客,技术这东西不进则退
刚刚看官方文档的时候吓到了,本以为官方写的SQL应该是比较好的,是同一类问题的最佳实践,没想到并不是这样的;
The Row Holding the Maximum of a Certain Column 这一章节我就觉得有问题
【问题介绍】
假设我们建了一个用于保存商品信息的表,那我们如何查出最贵的商品是哪件呢?
drop table if exists shop;
create table if not exists shop (
article int(4) UNSIGNED ZEROFILL default '0000' not null,
dealer char(20) default '' not null,
price double(16,2) default '0.00' not null,
primary key(article, dealer),
INDEX shop__price(price));
insert into shop values
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
(3,'C',1.69),(3,'D',1.25),(4,'D',19.95);
select * from shop;
-- +---------+--------+-------+
-- | article | dealer | price |
-- +---------+--------+-------+
-- | 0003 | D | 1.25 |
-- | 0003 | B | 1.45 |
-- | 0003 | C | 1.69 |
-- | 0001 | A | 3.45 |
-- | 0001 | B | 3.99 |
-- | 0002 | A | 10.99 |
-- | 0004 | D | 19.95 |
-- +---------+--------+-------+
-- 7 rows in set (0.00 sec)
1): 官方给出的查询语句如下
mysql> select a.*
from shop as a
where price = (select max(b.price) from shop as b);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0004 | D | 19.95 |
+---------+--------+-------+
可以看到官方给出的解决方案也是“直抒胸臆”,但是MySQL的物理查询并不是这么做的!
【官方的解决方案有什么问题】
性能问题”! 就上面的问题逻辑上要分两步走 1): 在shop表中找出最高的价格是多少 2): 在shop表中找出“价格”等于“最高价格”的行。
循环表中的每一行,针对每一行都去比较一个当前
行的price是否等于 shop表的最高价格(每次循环都会去计算shop表的最高价格)”这样就引入了大量的没有必要的工作量,这样的SQL自然就慢
啦。
执行计划如下,可以说是铁证如山了
explain select a.*
-> from shop as a
-> where price = (select max(b.price) from shop as b);
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+------------------------------+
| 1 | PRIMARY | a | NULL | ref | shop__price | shop__price | 8 | const | 1 | 100.00 | Using where; Using index |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)
【面对这样的问题我们怎么办】
MySQL对“子查询”的支持不太好,那我们人为的把子查询分离出来就行了吧!是的就是这么干(just do IT)
select max(price) into @maxprice from shop;
select article,dealer,price from shop where price=@maxprice;
-- +---------+--------+-------+
-- | article | dealer | price |
-- +---------+--------+-------+
-- | 0004 | D | 19.95 |
-- +---------+--------+-------+
-- 1 row in set (0.00 sec)
两次SQL对应的执行计划如下
explain select max(price) into @maxprice from shop;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)
explain select article,dealer,price from shop where price=@maxprice;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | shop | NULL | ref | shop__price | shop__price | 8 | const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
【一个dba要做点什么】
我上面的例子是在mysql-8.0.12中复现的,从侧面看出整个MySQL对“子查询”的处理都不太好,那还能说什么呢! 当然是不能在MySQL中使用
在MySQL中子查询不准上生产环境”