蜘蛛吧,新站快速收录,高质量网站外链,吸引各种蜘蛛! 免费收录 快速收录 最新收录 网站地图 TAGS
首页/ > 文章资讯/ > 数据库

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

2022-12-31 23:44:24   数据库   0
【导读】:随着问问题的同学越来越多公众号内部私信回答问题已经很困难了所以建立了一个群关于各种数据库的问题都可以目前主要是等期待你的加入加群请添加微信另外最近虽然是阳过了但是身体还是没有缓起来可能写文章有偷懒的情况后面暂时有可能改为一周两篇在以后对于一...本文地址:/news/661.html

随着问问题的同学越来越多,公众号内部私信回答问题已经很困难了,所以建立了一个群,关于各种数据库的问题都可以,目前主要是 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)


 |

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



版权声明:

1、本文系转载,版权归原作者所有,旨在传递信息,不代表看本站的观点和立场。

2、本站仅提供信息发布平台,不承担相关法律责任。

3、若侵犯您的版权或隐私,请联系本站管理员删除。

4、文章来源:来自于网络收集。

网站声明:本站所有资料取之于互联网,任何公司或个人参考使用本资料请自辨真伪、后果自负,本站不承担任何责任。
©2017-2022 蜘蛛吧  https://www.zhizhuba.com/ ICP备案号:冀ICP备19007129号-5