-
现在有三个表：商品表 goods、订单表 goods_order、订单详情表 goods_order_detail。三个表中的数据有两种方式导入：
-
1、执行 goods_stru.sql、goods_order_stru.sql、goods_order_detail_stru.sql 建立原始表，然后执行存储过程 create_goods 产生原始数据，再执行存储过程 randon_detail 进行数据更新；
-
2、执行 SQL 脚本 goods_all.sql、 goods_order_all.sql、 goods_order_detail_all.sql，同时导入表结构和数据。
-
三个表目前都没有索引：
-
三个表目前的记录数如下：
-
现在有这么个业务场景：查找包含赠送商品且商品编号是 666 的订单
-
select o.* from goods_order o where o.id in(select order_id from goods_order_detail od where od.goods_id=666 and od.price=0);
-
花了 5 秒多，有没有办法改进呢？先看执行计划如下：
-
从执行计划可以看出，MySQL 对这个查询的处理是：
-
1、建立了物化表；2、将子查询改为了利用物化表进行连接查询；3、只利 用到了主键；4、全表扫描的情况很严重。
-
为了验证这一点，我们查看下 MySQL 对这个查询语句的改写：
-
mysql> show warnings;
-
/* select#1 */ select mysqladv.o.id AS id,mysqladv.o.order_no AS
order_no from mysqladv.goods_order o semi join
(mysqladv.goods_order_detail od) where ((mysqladv.o.id =
<subquery2>.order_id) and (mysqladv.od.price = 0) and
(mysqladv.od.goods_id = 666))
-
可以看到 MySQL 将这个子查询改造为了半连接 semi join。那么我们要对这 个 SQL 语句的执行进行优化。
-
分析我们的 SQL 语句，在 in 子查询中，用到了 goods_order_detail 中的 goods_id 字段和 price 字段作为条件，我们考虑增加一个 goods_id 字段和 price 字段的联合索引。
-
ALTER TABLE mysqladv.goods_order_detail ADD INDEX``idx_price_price(goods_id, price`);
-
再来看看执行效果和执行计划：
-
可以看到，虽然还有物化表，但是对物化表的扫描行数，已经由 9713504 降到了 6，对物化表也不再是全表扫描，速度提升到了 0.01 秒。
-
能不能再做提升呢？去除物化的过程和全表扫描呢？审视我们的子查询： select order_id from goods_order_detail od where od.goods_id=666 and od.price=0；
-
可以利用索引覆盖，在索引扫描的时候直接把 order_id 查找出来，所以我们 修改下索引 idx_price_price，再增加一个字段 order_id。
-
ALTER TABLE mysqladv.goods_order_detail DROP INDEX idx_price_price, ADD INDEX idx_price_price_order(goods_id, price, order_id)
-
再来看看执行效果和执行计划：
-
可以看到，已经完全去除了物化的过程和全表扫描。
-
当然，SQL 语句的执行中，上面的时间没有精确到毫秒，具体执行时长不知 道是多少，没关系，我们用 show profiles; 来观察。
-
set profiling=1;
-
select o.* from goods_order o where o.id in(select order_id from goods_order_detail od where od.goods_id=666 and od.price=0);
-
show profiles;
-
我们将这个 SQL 语句由 5.07 秒优化到 0.01 秒，提升了 507 倍，再优化到 0.00075 秒，再次提升了 13 倍，和最初相比执行速度提升了 6760 倍。
-
总结：
-
1.建立索引很重要
-
2.合适的索引更重要
-
3、覆盖索引是个利器
-
4.学会分析 sql 执行计划，mysql 会对 sql 进行优化，所以分析执行计划很重 要。
-
有这么个故事：全球零售业巨头沃尔玛在对消费者购物行为分析时发现，男 性顾客在购买婴儿尿片时，常常会顺便搭配几瓶啤酒来犒劳自己，于是尝试推出 了将啤酒和尿布摆在一起的促销手段。没想到这个举措居然使尿布和啤酒的销量 都大幅增加了。如今，“啤酒＋尿布”的数据分析成果早已成了大数据技术应用 的经典案例。不过这个故事的真实性是存在着很大的疑问的。
-
但是这个故事本身反映的是销售商品相关性分析，其中的关联规则是反映一 个事物与其他事物之间的相互依存性和关联性，常用于实体商店或在线电商的推 荐系统：通过对顾客的购买记录数据库进行关联规则挖掘，最终目的是发现顾客 群体的购买习惯的内在共性，例如购买产品 A 的同时也连带购买产品 B 的概率， 根据挖掘结果，调整货架的布局陈列、设计促销组合方案，实现销量的提升。当 然这个属于数据挖掘和大数据的领域，比如实际中使用 ClickHouse 来做这些工作。
-
那么我们用 MySQL 能做吗？在数据量不是特别大的情况下也是可以的。依 然使用我们前面所用的三张表商品表 goods、订单表 goods_order、订单详情表 goods_order_detail 来试试。
-
现在的业务场景如下，对我们销售的商品找到关联度最高的商品，也就是在 一张订单表中总是相伴出现的商品，不过考虑到实际情况，购买次数少的商品没 有太大分析的必要。思路如下：
-
第一步，找到订单详情表中出现的商品明细，按商品在详情表中出现的次数 排序：
-
select order_id,goods_id from goods_order_detail where
goods_id in (select odo.goods_id
from goods_order_detail odo
GROUP BY odo.goods_id
ORDER BY count(odo.goods_id));
-
第二步，通过第一步查询后的临时表作为主表，通过单号为关键字段，做连 接（外连接和内连接均可，对结果基本没有影响）：
-
select aa.goods_id as src_goods,bb.goods_id as relative_goods,count(*) from
(select order_id,goods_id from goods_order_detail where
goods_id in (select odo.goods_id from goods_order_detail odo GROUP BY
odo.goods_id ORDER BY count(odo.goods_id))) aa
left join goods_order_detail bb
on aa.order_id=bb.order_id
where aa.goods_id != bb.goods_id
group by aa.goods_id,bb.goods_id
order by count(*) desc;
-
这个 SQL 语句的执行是很慢的，我们不去具体执行，看看他的执行计划和执 行成本。执行成本为 1.88E13，从执行计划来看，扫描的行数和访问类型都让人不满意， 需要改进一下。怎么改？
-
仔细分析我们的的语句和业务，有必要去寻找所有商品的关联商品吗？购 买次数很低的商品没必要进行这种统计与分析，所以我们先把找到订单详情表中 出现的商品明细这个步骤提取出来，限定只寻找售出数量前 10 位的商品。
-
select odo.goods_id,count(odo.goods_id)
from goods_order_detail odo
GROUP BY odo.goods_id
ORDER BY count(odo.goods_id) desc
limit 10;
-
这个速度还是可以接受的，我们把查询出来的商品在应用程序中缓存起来， 然后，一个个商品去处理。比如先处理 id 为 470 的商品：
-
select bb.goods_id as relative_goods,count(*) from
(select order_id from goods_order_detail where
goods_id=470) aa
left join goods_order_detail bb
on aa.order_id=bb.order_id
where bb.goods_id != 470
group by bb.goods_id
order by count(*) desc;
-
成本变为：
-
相比原来的 1.88E13，现在的 1.68E12，已经降低了一个数量级，但依然很 大，最重要的是驱动表的扇出数和被驱动表的查询记录数都非常大，需要继续优 化。虽然驱动表 aa 显示了“Using tempopray,Using filesort”，但是驱动表 aa 的 优化余地很小，发力点还在对被驱动表 bb 的优化上，为什么？请同学自行思考。
-
目前这个 sql 语句利用的是 idx_price_price_order 索引，看起来对我们当前 的业务不是特别适合，从执行计划来看，对被驱动表 bb 的扫描行数达到了 9713504，太大了。我们需要对索引进行调整，分析我们的 SQL 语句：
-
select bb.goods_id as relative_goods,count(*) from (select order_id from goods_order_detail where goods_id=470) aa left join goods_order_detail bb
-
on aa.order_id=bb.order_id where bb.goods_id != 470 group by bb.goods_id order by count(*) desc;
-
所以，我们考虑，在连接列 order_id 上建立一个索引：
-
ALTER TABLE mysqladv.goods_order_detail ADD INDEX ``idx_order(order_id`);
-
可以看见，被驱动表 bb 的扫描行数降到了 9，成本变为了：
-
相比原来的 1.88E13、1.68E12，现在降到了 1.02E7。
-
继续考虑，我们对被驱动表的访问，能不能再降低点成本？比如回表的成 本？因为我们现在的索引 idx_order 是会引发回表的。所以我们再建立了一个联 合索引：
-
ALTER TABLE mysqladv.goods_order_detail
-
DROP INDEX idx_order,
-
ADD INDEX idx_order_goods(order_id, goods_id)
-
为什么我们要删除idx_order，建立idx_order_goods，保留idx_order不好吗？ 请同学们自行思考。
-
可以看见，被驱动表 bb 的扫描利用了覆盖索引，成本变为了 2.74E6，实际 的查询时间：
-
基本上在可以接受的范围内了。如果我们继续增加一个索引：
-
ALTER TABLE mysqladv.goods_order_detail
-
ADD INDEX idx_goods_order(goods_id, order_id);
-
虽然执行计划中的成本没有太大变化，但是查询时间还能降到 4.70S，基本 上时间再次减半。
-
为什么？我们不是已经有了一个 idx_goods_price_order（``goods_id, price, order_id`）吗？请同学们自行思考。
-
最后考虑到业务和网络传输量，960 个结果其实是没有必要的，所以最终 的 SQL 如下：
-
select bb.goods_id as relative_goods,count(*) from
(select order_id from goods_order_detail where
goods_id=470) aa
left join goods_order_detail bb
on aa.order_id=bb.order_id
where bb.goods_id != 470
group by bb.goods_id
order by count(*) desc
limit 10;
-
索引如下：
-
当然上面的 SQL 如果去除排序，比如变为
-
Select …………
group by bb.goods_id
order by null;
-
执行成本还可降低到 2.55E6 同时执行计划里不再出现 using_filesort，不过 对整体的影响并不大，从实际的执行时间来看，也确实是如此。
-
在这种情况下，执行成本相比原来的 1.88E13、1.68E12，现在降到了 1.02E7， 再到 2.74E6，实际的一个商品的查询时间为 4.7 秒，算上我们要查询十个商品， 总时间为 4.7X10+2.93=50 秒，基本上查询成本从 1.88E13 变为 2.74E6X10，降低 了 10 万倍，对于我们这种属于非实时统计的业务来说，这个速度相对还是不错的。当然如果你对上面的业务还有更好的优化措施，并且实验通过了，请分享。
-
https://ke.qq.com/comment/index.html?cid=287404
