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

在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) |

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