【new friends】点击标题下面蓝色字“恒生DBA公社”关注。
关注福利：
关注恒生DBA公社，回复18c，即可得到Oracle 18c outline官方手册
前 言
这几年，MySQL的版本在不断变更，使用的范围也越来越广，尤其的MySQL 5.5及之后的版本，可以说是数据库性能有了很大改善，今天主要跟大家一起探讨关于MySQL的优化器，5.7版本对比5.5版本的几点大的改进。当然，我会穿插一些小的实验来给大家更清晰直观的比较。
MySQL针对子查询select的优化
MySQL 的子查询一直以性能差著称，之前的解决方案手工改写SQL使用关联join查询代替子查询。但是mySQL5.7版本中，子查询有了强劲的优化，也就是说，可以在不改变原有的SQL情况下，mySQL5.7 通过内部的优化器把子查询改成了关联查询。
比如通常情况下，我们希望在一个子查询中，优先完成内表里的查询结果，然后去驱动外查询的表，完成最终查询。但是在mySQL5.5及之前的版本里，子查询总是会先扫描外表中的所有数据，每条数据传到内表与之关联，如果外表很大，那么可想而知，性能将会很差。
下面我们看一个例子
select orderNumber,
productCode,
quantityOrdered,
priceEach,
orderLineNumber
from orderdetails o
where o.ordernumber in
(select orderNumber from orders d where d.ordernumber = 10423);
其中：orderdetails记录2996 条，orders 记录326条
我们希望SQL首先执行内表 oreders 把预定号码为10423的找出来，然后外表orderdetail只需匹配内表结果即可。但是mySQL 5.5版本中，数据库SQL是先把外表orderdetails全部查询一遍，然后再与内表order 进行一次select orderNumber from orders d whered.ordernumber=10423，所以性能低下。执行计划如下图：
而在MySQL 5.7的版本中，优化器使用semijoin=on的方式开启半连接子查询，通过优化器重写了SQL，实现了SQL由内向外的执行方式，而无需手工处理，也解决了之前子查询性能低的问题。
MySQL 5.7 此SQL的执行计划如下图：
对于MySQL 5.7，优化器默认半连接查询开启，可以通过showvariables like ‘optimizer_switch’\G来查看：如下图
MySQL针对IN条件表达式的优化
MySQL 的子查询一直以性能差著称，之前的解决方案手工改写SQL使用关联join查询代替子查询。但是mySQL5.7版本中，子查询有了强劲的优化，也就是说，可以在不改变原有的SQL情况下，mySQL5.7 通过内部的优化器把子查询改成了关联查询。
MySQL针对子查询select的优化
在mySQL 5.5数据库中，关于SQL中含有in的条件表达式，发现表达式中即使有索引，mySQL5.5优化器内部也会走全表扫描，无法用到索引。比如执行下面SQL语句时：
select productcode, productName, buyprice
from products p
where (productcode, buyprice) in
(('S12_4473', '55.70'), ('S72_3212', '33.30'));
其中products的表结构如下，productCode 是有主键索引的
mySQL>show create table products\G;
***************************1. row ***************************
Table: products
CreateTable: CREATE TABLE `products` (
`productCode` varchar(15) NOT NULL DEFAULT ''COMMENT '产品代码',
`productName` varchar(70) NOT NULL COMMENT '产品名称',
`productLine` varchar(50) NOT NULL COMMENT '产品线',
`productScale` varchar(10) NOT NULL,
`productVendor` varchar(50) NOT NULL,
`productDescription` text NOT NULL,
`quantityInStock` smallint(6) NOT NULLCOMMENT '库存',
`buyPrice` decimal(10,2) NOT NULL COMMENT '价格',
`MSRP` decimal(10,2) NOT NULL COMMENT '建议零售价',
PRIMARY KEY (`productCode`),
KEY `productLine` (`productLine`),
CONSTRAINT `products_ibfk_1` FOREIGN KEY(`productLine`) REFERENCES `productlines` (`productLine`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8
1row in set (0.00 sec)
但是通过mySQL 5.5的执行计划看到，SQL执行以后仍然是全表扫描，并未用到主键索引。下图
而MySQL5.7版本中，该SQL已经被内部优化器优化，不在是全表扫描，执行计划改写为primary 的主键，速度当然也会更快。如图：
MySQL针对Union all的优化
在MySQL5.5 版本数据库中，涉及union all的SQL查询，数据库内部优化器需要创建一张临时表，来实现量表的关联，但是这种方式会增大I/O的开销。看下面SQL执行计划：
Orderdetails表的查询结果和orders表的查询结果合并在了一张临时表里，然后返回输出给客户端。但是在MySQL5.7版本中，优化器不需要再创建一张临时表，而是直接依次将量表结果返回客户端，这样执行大大减少数据库关于I/O开销，提升了查询速度。
但是需要注意的是，union all对union和最外层使用orderby 不起作用。如图
小结
这次我们主要讨论了MySQL 5.5和5.7两个版本，关于优化器的3点改进，分别是子查询select优化、IN条件表达式优化、unionall优化，当然MySQL 5.7 数据库性能方面的优化不止这些，比如优化器关于索引的改进、MySQL Innodb存储引擎的优化提升等等。大家一起学习，在以后的文章中我们会接着探讨。
