在书中 15.2.3 中,让查询订单编号为 20005 的订单中的物品信息。
由于查询列来自三个表,所以书中连接了三个表。
select prod_name, vend_name, prod_price, quantity
from orderitems, products, vendors
where products.vend_id = vendors.vend_id and orderitems.prod_id = products.prod_id and order_num = 20005;
我合计改一下,是不是能更高效,改完查到的东西也一样,但改完就没法查 quantity 列了(来自 orderitems )
SELECT
prod_name,
prod_price,
(SELECT vend_name FROM vendors WHERE vendors.vend_id = products.vend_id) AS vend_name
FROM products WHERE prod_id IN
(SELECT prod_id FROM orderitems WHERE order_num = 20005)
表信息:
CREATE TABLE orderitems
(
order_num int NOT NULL ,#订单号
order_item int NOT NULL ,
prod_id char(10) NOT NULL ,#产品 id
quantity int NOT NULL ,#产品数量
item_price decimal(8,2) NOT NULL ,
PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;
CREATE TABLE products
(
prod_id char(10) NOT NULL,#产品 id
vend_id int NOT NULL ,#供应商 id
prod_name char(255) NOT NULL ,#产品姓名
prod_price decimal(8,2) NOT NULL ,#产品价格
prod_desc text NULL ,
PRIMARY KEY(prod_id)
) ENGINE=InnoDB;
CREATE TABLE vendors
(
vend_id int NOT NULL AUTO_INCREMENT,#供应商 id
vend_name char(50) NOT NULL ,#供应商姓名
vend_address char(50) NULL ,
vend_city char(50) NULL ,
vend_state char(5) NULL ,
vend_zip char(10) NULL ,
vend_country char(50) NULL ,
PRIMARY KEY (vend_id)
) ENGINE=InnoDB;
我把有用的信息都标注出来了。
1
qiayue 2020-09-05 21:55:11 +08:00
请问你说的更高效是通过哪些指标判断出来的。
另外,第一个三表连接语句,看起来清晰明确,一眼就知道要查的是订单号为 20005 的订单相关信息。 你改的,实话说,很难理解。 |
2
amiwrong123 OP 其实我是根据书中前面内容瞎改了下,用 explain 看了下,我这么改好像更不好了。本来以为先用子查询查出订单 20005 里的产品 id 会更好呢。
第一个图是第一个查询的。 <img src="https://s1.ax1x.com/2020/09/05/wZSgx0.png" alt="1599316644(1)" border="0"> 第二个图是第二个查询的。 <img src="https://s1.ax1x.com/2020/09/05/wZSHR1.png" alt="1599316719(1)" border="0"> 而且还报了个警告。。 |
3
amiwrong123 OP @qiayue 忘 @了
|
4
xupefei 2020-09-06 00:37:37 +08:00 via iPhone
没啥区别。subquery flatten 是 query optimizer 的基本技能。
|
5
zhangysh1995 2020-09-06 16:34:17 +08:00
@amiwrong123 我寻思着原始这条 query 应该可以更快,orderitems 里面 order_num 没有索引,那么 order_num = 20005 应该直接就给全表扫描了?总之这条查询感觉写法挺奇怪的。。
|
6
Mroldx 2020-09-06 18:19:00 +08:00
这 query optimizer 是啥啊
|
7
CRVV 2020-09-06 22:48:39 +08:00
SQL 是声明式语言而不是命令式语言,也就是你把自己需要什么数据告诉数据库,然后数据库把数据取出来,至于是怎么取的,那是数据库内部的事情,不是用 SQL 写出来的。
写 SQL 的点在于用精确简洁的方式描述清楚你到底要什么数据,比如前一种写法就很好了。 虽然这么说,当然存在换一个写法能让查询变快的方法,但是这种事情并不那么常见。 如果真要做优化,你把两句分别跑一下,如果变快了就是更好了。 |
8
519718366 2020-09-07 11:08:45 +08:00 via iPhone
首先要说明的是: 你这两条 sql 是不等价的。
原始 sql 是通过 join 的形式,join 的形式最大的特点是结果集无法去重,所以当你订单里有两个产品 A 时,返回的结果集里就有两条 A 的纪录 改写后的 sql 是通过子查询的形式,这样做最大的特点是产品信息的去重,即使订单里有两个商品 A,返回的结果里也只有一条 A 的纪录。 where 里的子查询没什么问题,用的时候注意 子查询返回结果过多,可能会导致外层查询不走索引。 select 里的子查询我是坚决反对的,因为我的理解中,select 里的子查询是对每一条返回的纪录再单独执行一条 sql 。假设你订单里有 10 个不同商品,最后你要执行的 sql 数量就是 1 条主的+10 条 select 里的子查询。10 条 select 就是 10 次 io 啊… 所以就单看你这个需求,原始 sql 是一般的做法吧,不会想着花里胡哨改写。 如果非要优化性能的话,我推荐把 sql 拆成多次执行用上覆盖索引。第一次带上查询条件,用上覆盖索引,把关键的 id 返回给服务器,服务器再根据第一次返回的 id 去对应的表查附属信息。 如果理解上有错,还请大家批评指正。 |
9
amiwrong123 OP >改写后的 sql 是通过子查询的形式,这样做最大的特点是产品信息的去重,即使订单里有两个商品 A,返回的结果里也只有一条 A 的纪录。
其实我那个子查询不会去重,不过你提醒了我,子查询应该这么写: (SELECT prod_id, sum(quantity) FROM orderitems WHERE order_num = 20005 GROUP BY prod_id) 或者: (SELECT distinct prod_id FROM orderitems WHERE order_num = 20005) select 里的子查询确实不好,我也觉得。 >我推荐把 sql 拆成多次执行用上覆盖索引。第一次带上查询条件,用上覆盖索引,把关键的 id 返回给服务器,服务器再根据第一次返回的 id 去对应的表查附属信息。 本人比较菜,大概理解下。加覆盖索引就得 ALTER TABLE,以后你意思是 执行多个 sql 语句吗,这怎么搞,第一个 sql 语句的结果存起来吗 |
10
amiwrong123 OP |
11
519718366 2020-09-07 12:56:01 +08:00 via iPhone
@amiwrong123
1. 改写后的 sql 具有了去重功能: 对于我想说的去重你理解歪了。 我想说的是你 where 里的子查询虽然没有去重,可能会查出产品 id 是:1,2,2,3,4 这样的结果。 但是在执行外层的 select from product where prod_id in (1,2,2,3,4)时,id=2 的纪录只会返回一条,所以说是去重了 |
12
encro 2020-09-07 13:07:11 +08:00
select oi.*,
p.prod_name, v.vend_name, from orderitems oi inner join products p on prod_id, inner join vendors v on vend_id, where oi.order_num = 20005; 这个执行性能和第一条一样,但是更加容易读懂。 Mysql 多表查询优化最重要的一条就是先找准基础表,减少基准表返回的记录可以大大减少查询的解析行数, 第一条查询基本最简单了,假设订单 10 条商品,那么需要分析的记录就是 30 条,没有比这更加简单了。合理索引后基本不会有性能问题。 |
13
519718366 2020-09-07 13:18:04 +08:00 via iPhone 1
@amiwrong123
关于覆盖索引优化服务性能 我觉得这算是后端开发做复杂列表时在 mysql 上必备知识点了 原理和实操都很简单。 覆盖索引的意思就是你 select 的列都在索引里,不需要回表。 你原始 sql 肯定没用上覆盖索引。因为你的 select 里的 quantity 应该不在索引里吧。你只是因为正好要关联 product 表,就贪婪的把 quantity 带了出来。 所以改写的话,应该是第一个 sql 只 select prod_id: select prod_id from orderitems where number=xxx(手机回复的,无法对着你的字段回复) 然后服务器根据你返回的这个 prod_id 查产品信息,后端经常说:服务端多次单表查询就是这个意思。 我以前一个列表对应的 sql join 了 8,9 表,然后 select 了 10+个字段,那个 sql 要 2000+ms,后来用了覆盖索引,第一次只查那一页的关键 id,只需要 200+ms,然后服务端多次单表查询,服务最后也只要 600ms 左右。 但是你问我要不要把覆盖索引当个圣经或者必须的准则,我觉得 duck 不必。小表之间的小查询直接带出来需要的字段可能只要 5ms,但是你从服务端走一遭,网络请求都要 10+ms,显然直接 select 出来更划算 |