虚拟生成列又叫 Generated Column，是 MySQL 5.7 引入的新特性，就是数据 库中这一列由其他列计算而得。在 MySQL 5.7 中，支持两种 Generated Column，即 Virtual Generated Column（虚拟生成的列）和 Stored Generated Column（存储 生成的列），二者含义如下：
1、Virtual Generated Column（虚拟生成的列）：不存储该列值，即 MySQL 只是将这一列的元信息保存在数据字典中，并不会将这一列数据持久化到磁盘上， 而是当读取该行时，触发触发器对该列进行计算显示。
2、Stored Generated Column（存储生成的列）： 存储该列值，即该列值在 插入或更新行时进行计算和存储。所以相对于 Virtual Column 列需要更多的磁盘 空间，与 Virtual Column 相比并没有优势。因此，MySQL 5.7 中，不指定 Generated Column 的类型，默认是 Virtual Column
在表中允许 Virtual Column 和 Stored Column 的混 合使用
提高效率：由于 mysql 在普通索引上加函数会造成索引失效，造成查询性能 下降，Generated Column（函数索引）刚好可以解决这个问题，可以在 Generated Column 加上索引来提高效率。但是不能建立虚拟列和真实列的联合索引，同时 虚拟列是不允许创建主键索引和全文索引。
创建虚拟生成列的语法：
CREATE TABLE triangle (
a double DEFAULT NULL,
b double DEFAULT NULL,
sidec double GENERATED ALWAYS AS (SQRT(a * a + b * b))
) ;
alter table triangle add column sided tinyint(1) generated always as (a*b) virtual;
a)合并多条 insert 为一条，即： insert into t values(a,b,c), (d,e,f) , 原因分析：主要原因是多条 insert 合并后日志量（MySQL 的 binlog 和 innodb 的事务日志） 减少了，降低日志刷盘的数据量和频率，从而提高效率。通过合 并 SQL 语句，同时也能减少 SQL 语句解析的次数，减少网络传输的 IO。
b)修改参数 bulk_insert_buffer_size， 调大批量插入的缓存；
c)设置 innodb_flush_log_at_trx_commit = 0，相对于 innodb_flush_log_at_trx_commit = 1 可以十分明显的提升导入速度； 备注：innodb_flush_log_at_trx_commit 参数对 InnoDB Log 的写入性能有 非常关键的影响。该参数可以设置为 0，1，2，解释如下：
d）手动使用事务 因为 mysql 默认是 autocommit 的，这样每插入一条数据，都会进行一次 commit；所以，为了减少创建事务的消耗，我们可用手工使用事务，即 START TRANSACTION;insert 。。,insert。。 commit；即执行多个 insert 后再一起提交； 一般 1000 条 insert 提交一次。
InnoDB 是基于索引来完成行锁，在锁的算法实现上有三种：
· Record lock：单个行记录上的锁
· Gap lock：间隙锁，锁定一个范围，不包括记录本身
· Next-key lock：record+gap 锁定一个范围，包含记录本身
Gap 锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导 致幻读问题的产生，innodb 对于行的查询使用 next-key lock，Next-locking keying 是 Record lock 和 Gap lock 的组合。当查询的索引含有唯一属性时，将 next-key lock 降级为 record key。
有两种方式显式关闭 gap 锁 ，第一种. 将事务隔离级别设置为 RC ；第二种. 将参数 innodb_locks_unsafe_for_binlog 设置为 1。
现在有三个表：商品表 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 =
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