MYSQL 8.0 后对语句的优化与特性

随着问问题的同学越来越多，公众号内部私信回答问题已经很困难了，所以建立了一个群，关于各种数据库的问题都可以，目前主要是 POSTGRESQL, MYSQL ,MONGODB ,POLARDB ,REDIS 等，期待你的加入，加群请添加微信liuaustin3.

另外最近虽然是阳过了，但是身体还是没有缓起来，可能写文章有偷懒的情况，后面暂时有可能改为一周两篇。

在MYSQL 8 以后对于一些语句的处理是进行了优化的，主要有以下几个方面

1  针对语句在 in  和 exists 子查询中使用半连接的方式进行优化

2  针对子查询的物化

3  优化了子查询在使用EXISTS 中的一些策略

4  优化了驱动表视图以及CTE 在查询中的一些方式如使用了merge 和物化的方式

5  条件下推方式的优化 

等等这几类，今天来简单的说说，他们都在那些方面对这样的数据处理方式进行了优化，后面会针对部分在更细致的研究。

1  针对语句中的 in exists any 等在使用了这些查询方式后，优化的选择倾向于 semijoin , mateialization, exists , exists strategy  等策略 对于在查询中使用了 not in  ， not exists 等查询方式使用了   materialization 和 exits strategy ，对于派生表，采用了两种方式   1  将派生表合并到外部查询模式  2 将派生表进行物化并当做内部的临时表使用。

需要注意的地方是如果你的语句是update 或 delete的方式进行如上的语句操作，尤其是子查询的方式，进行数据的修改，那么以上的工作将无法进行，所以对于在MYSQL 8 中的数据修改还是建议分两步走，查询出需要修改数据的行的主键，然后在对数据行进行修改，而不是在一个语句中就将数据一起进行修改。同时也要考虑数据量的大小，数据量大的情况下，也强烈不建议使用子查询的模式，来直接更新表。

在语句的处理中，我们还应该去考虑业务的业务逻辑性对结果的需求

1  需要具体的结果 

2  需要是否存在，而不是实际得结果

从业务的角度去考虑这个问题，举例  一家餐饮集团想确认一个顾客去过他旗下的那些门店，和 他想确认这个顾客是否去过他旗下的任何一家餐厅，那么我们如何进行这样的查询

从业务的角度来看，1 如果是第一个需求，则建议使用join 的处理方式  比如LEFT JOIN ， 2  如果是第二个需求，则需要使用 exists  或者 in 的方式来进行处理更为妥当。在上一期中也提到过，使用 join  exists in 在数据处理上的不同，有需要的可以参考上一期的文字.

我们来通过语句来展示一下上面的信息的不同，在给出语句前我们给出四张表，四张表分别是 电影录影带表， 库存表  ， 租赁表， 和 客户表

| film  | CREATE TABLE `film` (

  `film_id` smallint unsigned NOT NULL AUTO_INCREMENT,

  `title` varchar(128) NOT NULL,

  `description` text,

  `release_year` year DEFAULT NULL,

  `language_id` tinyint unsigned NOT NULL,

  `original_language_id` tinyint unsigned DEFAULT NULL,

  `rental_duration` tinyint unsigned NOT NULL DEFAULT '3',

  `rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',

  `length` smallint unsigned DEFAULT NULL,

  `replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',

  `rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G',

  `special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,

  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`film_id`),

  KEY `idx_title` (`title`),

  KEY `idx_fk_language_id` (`language_id`),

  KEY `idx_fk_original_language_id` (`original_language_id`),

  CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON DELETE RESTRICT ON UPDATE CASCADE,

  CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON DELETE RESTRICT ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

| inventory | CREATE TABLE `inventory` (

  `inventory_id` mediumint unsigned NOT NULL AUTO_INCREMENT,

  `film_id` smallint unsigned NOT NULL,

  `store_id` tinyint unsigned NOT NULL,

  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`inventory_id`),

  KEY `idx_fk_film_id` (`film_id`),

  KEY `idx_store_id_film_id` (`store_id`,`film_id`),

  CONSTRAINT `fk_inventory_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON DELETE RESTRICT ON UPDATE CASCADE,

  CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

| rental | CREATE TABLE `rental` (

  `rental_id` int NOT NULL AUTO_INCREMENT,

  `rental_date` datetime NOT NULL,

  `inventory_id` mediumint unsigned NOT NULL,

  `customer_id` smallint unsigned NOT NULL,

  `return_date` datetime DEFAULT NULL,

  `staff_id` tinyint unsigned NOT NULL,

  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`rental_id`),

  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),

  KEY `idx_fk_inventory_id` (`inventory_id`),

  KEY `idx_fk_customer_id` (`customer_id`),

  KEY `idx_fk_staff_id` (`staff_id`),

  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON DELETE RESTRICT ON UPDATE CASCADE,

  CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON DELETE RESTRICT ON UPDATE CASCADE,

  CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

| customer | CREATE TABLE `customer` (

  `customer_id` smallint unsigned NOT NULL AUTO_INCREMENT,

  `store_id` tinyint unsigned NOT NULL,

  `first_name` varchar(45) NOT NULL,

  `last_name` varchar(45) NOT NULL,

  `email` varchar(50) DEFAULT NULL,

  `address_id` smallint unsigned NOT NULL,

  `active` tinyint(1) NOT NULL DEFAULT '1',

  `create_date` datetime NOT NULL,

  `last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`customer_id`),

  KEY `idx_fk_store_id` (`store_id`),

  KEY `idx_fk_address_id` (`address_id`),

  KEY `idx_last_name` (`last_name`),

  CONSTRAINT `fk_customer_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON DELETE RESTRICT ON UPDATE CASCADE,

  CONSTRAINT `fk_customer_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=600 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

1  总部想知晓一个录影带是否被租赁过

2  总部想知道一个录影带被租赁过多少次

这两个需求实际上我们就需要通过不同的查询方式来进行处理

1  被租赁过，这里明显只是在确认某件事情是否存在，这里我们使用exists 来对查询语句进行处理。两个语句的处理的结果是一致的，但是执行的效率是不同的。

两个语句比较，相对来说 第二个语句的撰写方式 要优于第一个语句的的撰写方式。

select fi.film_id,fi.title

from film as fi 

inner join (select distinct film_id

from inventory as inv 

where exists (select 1 from rental as re where re.inventory_id = inv.inventory_id)) as invd on fi.film_id = invd.film_id; 

-> Nested loop inner join  (cost=9562.39 rows=16008) (actual time=8.231..9.249 rows=958 loops=1)

    -> Table scan on invd  (cost=0.01..202.60 rows=16008) (actual time=0.000..0.037 rows=958 loops=1)

        -> Materialize  (cost=6613.70..6816.29 rows=16008) (actual time=8.223..8.301 rows=958 loops=1)

            -> Table scan on <temporary>  (cost=0.01..202.60 rows=16008) (actual time=0.001..0.031 rows=958 loops=1)

                -> Temporary table with deduplication  (cost=4810.25..5012.84 rows=16008) (actual time=8.086..8.158 rows=958 loops=1)

                    -> Nested loop semijoin  (cost=3209.39 rows=16008) (actual time=0.040..7.324 rows=4580 loops=1)

                        -> Covering index scan on inv using idx_fk_film_id  (cost=460.85 rows=4581) (actual time=0.026..0.851 rows=4581 loops=1)

                        -> Covering index lookup on re using idx_fk_inventory_id (inventory_id=inv.inventory_id)  (cost=0.88 rows=3) (actual time=0.001..0.001 rows=1 loops=4581)

    -> Single-row index lookup on fi using PRIMARY (film_id=invd.film_id)  (cost=0.25 rows=1) (actual time=0.001..0.001 rows=1 loops=958)

select distinct fi.film_id,fi.title

from film as fi 

inner join (select  film_id

from inventory as inv 

where exists (select 1 from rental as re where re.inventory_id = inv.inventory_id)) as invd on fi.film_id = invd.film_id; 

| -> Table scan on <temporary>  (cost=0.01..211.38 rows=16710) (actual time=0.002..0.057 rows=958 loops=1)

    -> Temporary table with deduplication  (cost=5371.84..5583.21 rows=16710) (actual time=12.736..12.833 rows=958 loops=1)

        -> Nested loop semijoin  (cost=3700.80 rows=16710) (actual time=0.069..10.737 rows=4580 loops=1)

            -> Nested loop inner join  (cost=831.76 rows=4782) (actual time=0.060..3.570 rows=4581 loops=1)

                -> Covering index scan on fi using idx_title  (cost=103.00 rows=1000) (actual time=0.046..0.336 rows=1000 loops=1)

                -> Covering index lookup on inv using idx_fk_film_id (film_id=fi.film_id)  (cost=0.25 rows=5) (actual time=0.002..0.003 rows=5 loops=1000)

            -> Covering index lookup on re using idx_fk_inventory_id (inventory_id=inv.inventory_id)  (cost=0.88 rows=3) (actual time=0.001..0.001 rows=1 loops=4581)

 |

另外如果将语句全部改写成inner join的方式，相关的成本比对上面的要更低一些。

explain analyze select distinct fi.film_id,fi.title

from film as fi 

inner join inventory as inv on inv.film_id = fi.film_id 

inner join  rental as re on  re.inventory_id = inv.inventory_id; 

 Table scan on <temporary>  (cost=0.01..62.26 rows=4782) (actual time=0.002..0.054 rows=958 loops=1)

    -> Temporary table with deduplication  (cost=2986.15..3048.40 rows=4782) (actual time=18.336..18.430 rows=958 loops=1)

        -> Nested loop inner join  (cost=2507.95 rows=4782) (actual time=0.148..15.045 rows=4580 loops=1)

            -> Nested loop inner join  (cost=831.76 rows=4782) (actual time=0.122..4.937 rows=4581 loops=1)

                -> Covering index scan on fi using idx_title  (cost=103.00 rows=1000) (actual time=0.086..0.478 rows=1000 loops=1)

                -> Covering index lookup on inv using idx_fk_film_id (film_id=fi.film_id)  (cost=0.25 rows=5) (actual time=0.003..0.004 rows=5 loops=1000)

            -> Limit: 1 row(s)  (cost=0.25 rows=1) (actual time=0.002..0.002 rows=1 loops=4581)

                -> Covering index lookup on re using idx_fk_inventory_id (inventory_id=inv.inventory_id)  (cost=0.25 rows=3) (actual time=0.002..0.002 rows=1 loops=4581)

所以通过上面的语句分析，同一个结果，将语句撰写成 join 的模式，对于数据处理上更有利，而不是使用子查询和 exists 的方式，如果使用exists的方式，去重的问题的解决方案中，需要考虑数据量的问题，如果数据量大，可以在语句的子查询中对数据进行去重，降低后续的数据处理量，如果数据量小则在最外部进行数据的去重，在这个例子中是更好的选择。

在MYSQL 8.016 后 EXISTS 和 IN 是等价的，在优化器处理中走的一个优化的方式，在 MYSQL 8.017 后 NOT IN , NOT EXISTS 等已经走了antijoin 的方式。

antijoin 的方式就是在查找匹配中，凡是找到匹配的行就直接被丢弃，而不是保留他。但是上期的测试中也可以看到，antijoin的方式的cost 比较高，在可以不适用 not 的方式请的情况下，还是进来不要使用。

在使用半连接的情况下，相关功能已经包含了Distinct 和 GROUP BY 以及ORDER BY

在使用半连接的情况下，半连接会带来一些独有的查询处理的方式，

1  在产生的临时表中去除重复数据

2  firstMatch 在有多个条件的情况下，如果第一组条件就可以决定结果集，则不会再对后面的条件进行匹配

3  lososeScan  通过索引来对子表进行扫描

4  物化子查询的结果到一个带有索引的临时表并通过临时表来进行JOIN 的操作，索引也具有去重的作用，通过lookups 的方式来解决join 临时表的操作。

通过以上的方式来对数据库的查询来进行优化，以上的功能需要查看

select @@optimizer_switch;
engine_condition_pushdown=on,

index_condition_pushdown=on,

materialization=on,
semijoin=on,
loosescan=on,
firstmatch=on,
duplicateweedout=on,
subquery_materialization_cost_based=on,
derived_condition_pushdown=on

为了证明，in exists 的子查询都会改写成同样与 join的方式我们将三种语句来撰写，并查看他的执行计划。
mysql> explain analyze select  fi.film_id
    -> from film as fi
    -> where film_id in (select film_id from inventory as inv where inv.store_id = 1);
 1;
 EXPLAIN                                                                                                                                                                                                                                                                                                                                      
| -> Nested loop semijoin  (cost=654.55 rows=3012) (actual time=0.023..1.984 rows=759 loops=1)
    -> Covering index scan on fi using idx_fk_language_id  (cost=103.00 rows=1000) (actual time=0.016..0.244 rows=1000 loops=1)
    -> Covering index lookup on inv using idx_store_id_film_id (store_id=1, film_id=fi.film_id)  (cost=0.75 rows=3) (actual time=0.002..0.002 rows=1 loops=1000)

1 row in set (0.00 sec)

mysql>
mysql> explain analyze select  fi.film_id
    -> from film as fi
    -> left join inventory as inv on inv.film_id = fi.film_id and inv.store_id = 1;
| EXPLAIN                                                                                                   
| -> Nested loop left join  (cost=654.55 rows=3012) (actual time=0.028..3.171 rows=2511 loops=1)
    -> Covering index scan on fi using idx_fk_language_id  (cost=103.00 rows=1000) (actual time=0.019..0.254 rows=1000 loops=1)
    -> Covering index lookup on inv using idx_store_id_film_id (store_id=1, film_id=fi.film_id)  (cost=0.25 rows=3) (actual time=0.002..0.003 rows=2 loops=1000)
 |

上图可以完全证明，三种写法的方式最终的执行计划是一致的。