背景

  周末比较闲,我这个人又没有什么爱好,当然了读书除外;前一些天我一个同事说:“你一个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中子查询不准上生产环境”