看MySQL官方文档的示例SQL有感
【背景】
周末比较闲，我这个人又没有什么爱好，当然了读书除外；前一些天我一个同事说：“你一个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表中找出“价格”等于“最高价格”的行。
就这么简单的逻辑MySQL不会搞错吧！事实上MySQL还真会搞错这个逻辑，它是这样干的“循环表中的每一行，针对每一行都去比较一个当前
行的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中使用
“子查询”啦！ 要用别的方式重写SQL，可以用“join” 也可以用刚才的“变量” 等方式来处理。一句话“在MySQL中子查询不准上生产环境”
【学习交流】
-----------------------------http://www.sqlpy.com-------------------------------------------------
-----------------------------http://www.sqlpy.com-------------------------------------------------
