mysql全表扫描会涉及到io吗_我说 SELECT COUNT(*) 会造成全表扫描,面试官让我回去等通知...

本文主要是介绍mysql全表扫描会涉及到io吗_我说 SELECT COUNT(*) 会造成全表扫描,面试官让我回去等通知...,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

上篇SQL 进阶技巧(下)中提到使用以下 sql 会导致慢查询SELECT COUNT(*) FROM SomeTableSELECT COUNT(1) FROM SomeTable

原因是会造成全表扫描,有位读者说这种说法是有问题的,实际上针对无 where_clause 的COUNT(*),MySQL 是有优化的,优化器会选择成本最小的辅助索引查询计数,其实反而性能最高,这位读者的说法对不对呢

针对这个疑问,我首先去生产上找了一个千万级别的表使用  EXPLAIN 来查询了一下执行计划EXPLAIN SELECT COUNT(*) FROM SomeTable

结果如下3fdc4631606f8be9b75530b942ac65fc.png

如图所示: 发现确实此条语句在此例中用到的并不是主键索引,而是辅助索引,实际上在此例中我试验了,不管是 COUNT(1),还是 COUNT(*),MySQL 都会用成本最小的辅助索引查询方式来计数,也就是使用 COUNT(*) 由于 MySQL 的优化已经保证了它的查询性能是最好的!随带提一句,COUNT(*)是 SQL92 定义的标准统计行数的语法,并且效率高,所以请直接使用COUNT(*)查询表的行数!

所以这位读者的说法确实是对的。但有个前提,在 MySQL 5.6 之后的版本中才有这种优化。

那么这个成本最小该怎么定义呢,有时候在 WHERE 中指定了多个条件,为啥最终 MySQL 执行的时候却选择了另一个索引,甚至不选索引?

本文将会给你答案,本文将会从以下两方面来分析SQL 选用索引的执行成本如何计算

实例说明

SQL 选用索引的执行成本如何计算

就如前文所述,在有多个索引的情况下, 在查询数据前,MySQL 会选择成本最小原则来选择使用对应的索引,这里的成本主要包含两个方面。IO 成本: 即从磁盘把数据加载到内存的成本,默认情况下,读取数据页的 IO 成本是 1,MySQL 是以页的形式读取数据的,即当用到某个数据时,并不会只读取这个数据,而会把这个数据相邻的数据也一起读到内存中,这就是有名的程序局部性原理,所以 MySQL 每次会读取一整页,一页的成本就是 1。所以 IO 的成本主要和页的大小有关

CPU 成本:将数据读入内存后,还要检测数据是否满足条件和排序等 CPU 操作的成本,显然它与行数有关,默认情况下,检测记录的成本是 0.2。

实例说明

为了根据以上两个成本来算出使用索引的最终成本,我们先准备一个表(以下操作基于 MySQL 5.7.18)CREATE TABLE `person` (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `name` varchar(255) NOT NULL,  `score` int(11) NOT NULL,  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (`id`),  KEY `name_score` (`name`(191),`score`),  KEY `create_time` (`create_time`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这个表除了主键索引之外,还有另外两个索引, name_score 及 create_time。然后我们在此表中插入 10 w 行数据,只要写一个存储过程调用即可,如下:CREATE PROCEDURE insert_person()begin    declare c_id integer default 1;    while c_id<=100000 do    insert into person values(c_id, concat('name',c_id), c_id+100, date_sub(NOW(), interval c_id second));    set c_id=c_id+1;    end while;end

插入之后我们现在使用 EXPLAIN 来计算下统计总行数到底使用的是哪个索引EXPLAIN SELECT COUNT(*) FROM person2a0a78fbea70241318b0c3629720242c.png

从结果上看它选择了 create_time 辅助索引,显然 MySQL 认为使用此索引进行查询成本最小,这也是符合我们的预期,使用辅助索引来查询确实是性能最高的!

我们再来看以下 SQL 会使用哪个索引SELECT * FROM person WHERE NAME >'name84059' AND create_time>'2020-05-23 14:39:18'd1273b1f0d44f6a1b12555005f0c7020.png

用了全表扫描!理论上应该用 name_score 或者 create_time 索引才对,从 WHERE 的查询条件来看确实都能命中索引,那是否是使用SELECT *造成的回表代价太大所致呢,我们改成覆盖索引的形式试一下SELECT create_time FROM person WHERE NAME >'name84059' AND create_time > '2020-05-23 14:39:18'

结果 MySQL 依然选择了全表扫描!这就比较有意思了,理论上采用了覆盖索引的方式进行查找性能肯定是比全表扫描更好的,为啥 MySQL 选择了全表扫描呢,既然它认为全表扫描比使用覆盖索引的形式性能更好,那我们分别用这两者执行来比较下查询时间吧-- 全表扫描执行时间: 4.0 msSELECT create_time FROM person WHERE NAME >'name84059' AND create_time>'2020-05-23 14:39:18' -- 使用覆盖索引执行时间: 2.0 msSELECT create_time FROM person force index(create_time) WHERE NAME >'name84059' AND create_time>'2020-05-23 14:39:18'

从实际执行的效果看使用覆盖索引查询比使用全表扫描执行的时间快了一倍!说明 MySQL 在查询前做的成本估算不准!我们先来看看 MySQL 做全表扫描的成本有多少。

前面我们说了成本主要 IO 成本和 CPU 成本有关,对于全表扫描来说也就是分别和聚簇索引占用的页面数和表中的记录数。执行以下命令SHOW TABLE STATUS LIKE 'person'1cd386482fc89a8c7a86338c3f17dd3c.png

可以发现行数是 100264,我们不是插入了 10 w 行的数据了吗,怎么算出的数据反而多了,其实这里的计算是估算,也有可能这里的行数统计出来比 10 w 少了,估算方式有兴趣大家去网上查找,这里不是本文重点,就不展开了。得知行数,那我们知道 CPU 成本是 100264 * 0.2 = 20052.8。

数据长度是 5783552,InnoDB 每个页面的大小是 16 KB,可以算出页面数量是 353。

也就是说全表扫描的成本是 20052.8 + 353 =  20406。

这个结果对不对呢,我们可以用一个工具验证一下。在 MySQL 5.6 及之后的版本中,我们可以用 optimizer trace 功能来查看优化器生成计划的整个过程 ,它列出了选择每个索引的执行计划成本以及最终的选择结果,我们可以依赖这些信息来进一步优化我们的 SQL。

optimizer_trace 功能使用如下SET optimizer_trace="enabled=on";SELECT create_time FROM person WHERE NAME >'name84059' AND create_time > '2020-05-23 14:39:18';SELECT * FROM information_schema.OPTIMIZER_TRACE;SET optimizer_trace="enabled=off";

执行之后我们主要观察使用 name_score,create_time 索引及全表扫描的成本。

先来看下使用 name_score 索引执行的的预估执行成本:{    "index": "name_score",    "ranges": [      "name84059 <= name"    ],    "index_dives_for_eq_ranges": true,    "rows": 25372,    "cost": 30447}

可以看到执行成本为 30447,高于我们之前算出来的全表扫描成本:20406。所以没选择此索引执行

注意:这里的 30447 是查询二级索引的 IO 成本和 CPU 成本之和,再加上回表查询聚簇索引的 IO 成本和 CPU 成本之和。

再来看下使用 create_time 索引执行的的预估执行成本:{    "index": "create_time",    "ranges": [      "0x5ec8c516 

可以看到成本是 60159,远大于全表扫描成本 20406,自然也没选择此索引。

再来看计算出的全表扫描成本:{    "considered_execution_plans": [      {        "plan_prefix": [        ],        "table": "`person`",        "best_access_path": {          "considered_access_paths": [            {              "rows_to_scan": 100264,              "access_type": "scan",              "resulting_rows": 100264,              "cost": 20406,              "chosen": true            }          ]        },

"condition_filtering_pct": 100,

"rows_for_plan": 100264,

"cost_for_plan": 20406,

"chosen": true

}

]

}

注意看 cost:20406,与我们之前算出来的完全一样!这个值在以上三者算出的执行成本中最小,所以最终 MySQL 选择了用全表扫描的方式来执行此 SQL。

实际上 optimizer trace 详细列出了覆盖索引,回表的成本统计情况,有兴趣的可以去研究一下。

从以上分析可以看出, MySQL 选择的执行计划未必是最佳的,原因有挺多,就比如上文说的行数统计信息不准,再比如 MySQL 认为的最优跟我们认为不一样,我们可以认为执行时间短的是最优的,但 MySQL 认为的成本小未必意味着执行时间短。

总结

本文通过一个例子深入剖析了 MySQL 的执行计划是如何选择的,以及为什么它的选择未必是我们认为的最优的,这也提醒我们,在生产中如果有多个索引的情况,使用 WHERE 进行过滤未必会选中你认为的索引,我们可以提前使用  EXPLAIN, optimizer trace 来优化我们的查询语句。

——End——

这篇关于mysql全表扫描会涉及到io吗_我说 SELECT COUNT(*) 会造成全表扫描,面试官让我回去等通知...的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!


原文地址:https://blog.csdn.net/weixin_36333893/article/details/113946255
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.chinasem.cn/article/413594

相关文章

MySql基本查询之表的增删查改+聚合函数案例详解

《MySql基本查询之表的增删查改+聚合函数案例详解》本文详解SQL的CURD操作INSERT用于数据插入(单行/多行及冲突处理),SELECT实现数据检索(列选择、条件过滤、排序分页),UPDATE... 目录一、Create1.1 单行数据 + 全列插入1.2 多行数据 + 指定列插入1.3 插入否则更

MySQL深分页进行性能优化的常见方法

《MySQL深分页进行性能优化的常见方法》在Web应用中,分页查询是数据库操作中的常见需求,然而,在面对大型数据集时,深分页(deeppagination)却成为了性能优化的一个挑战,在本文中,我们将... 目录引言:深分页,真的只是“翻页慢”那么简单吗?一、背景介绍二、深分页的性能问题三、业务场景分析四、

MySQL 迁移至 Doris 最佳实践方案(最新整理)

《MySQL迁移至Doris最佳实践方案(最新整理)》本文将深入剖析三种经过实践验证的MySQL迁移至Doris的最佳方案,涵盖全量迁移、增量同步、混合迁移以及基于CDC(ChangeData... 目录一、China编程JDBC Catalog 联邦查询方案(适合跨库实时查询)1. 方案概述2. 环境要求3.

SQL server数据库如何下载和安装

《SQLserver数据库如何下载和安装》本文指导如何下载安装SQLServer2022评估版及SSMS工具,涵盖安装配置、连接字符串设置、C#连接数据库方法和安全注意事项,如混合验证、参数化查... 目录第一步:打开官网下载对应文件第二步:程序安装配置第三部:安装工具SQL Server Manageme

C#连接SQL server数据库命令的基本步骤

《C#连接SQLserver数据库命令的基本步骤》文章讲解了连接SQLServer数据库的步骤,包括引入命名空间、构建连接字符串、使用SqlConnection和SqlCommand执行SQL操作,... 目录建议配合使用:如何下载和安装SQL server数据库-CSDN博客1. 引入必要的命名空间2.

全面掌握 SQL 中的 DATEDIFF函数及用法最佳实践

《全面掌握SQL中的DATEDIFF函数及用法最佳实践》本文解析DATEDIFF在不同数据库中的差异,强调其边界计算原理,探讨应用场景及陷阱,推荐根据需求选择TIMESTAMPDIFF或inte... 目录1. 核心概念:DATEDIFF 究竟在计算什么?2. 主流数据库中的 DATEDIFF 实现2.1

MySQL 多列 IN 查询之语法、性能与实战技巧(最新整理)

《MySQL多列IN查询之语法、性能与实战技巧(最新整理)》本文详解MySQL多列IN查询,对比传统OR写法,强调其简洁高效,适合批量匹配复合键,通过联合索引、分批次优化提升性能,兼容多种数据库... 目录一、基础语法:多列 IN 的两种写法1. 直接值列表2. 子查询二、对比传统 OR 的写法三、性能分析

MySQL中的LENGTH()函数用法详解与实例分析

《MySQL中的LENGTH()函数用法详解与实例分析》MySQLLENGTH()函数用于计算字符串的字节长度,区别于CHAR_LENGTH()的字符长度,适用于多字节字符集(如UTF-8)的数据验证... 目录1. LENGTH()函数的基本语法2. LENGTH()函数的返回值2.1 示例1:计算字符串

浅谈mysql的not exists走不走索引

《浅谈mysql的notexists走不走索引》在MySQL中,​NOTEXISTS子句是否使用索引取决于子查询中关联字段是否建立了合适的索引,下面就来介绍一下mysql的notexists走不走索... 在mysql中,​NOT EXISTS子句是否使用索引取决于子查询中关联字段是否建立了合适的索引。以下

Java通过驱动包(jar包)连接MySQL数据库的步骤总结及验证方式

《Java通过驱动包(jar包)连接MySQL数据库的步骤总结及验证方式》本文详细介绍如何使用Java通过JDBC连接MySQL数据库,包括下载驱动、配置Eclipse环境、检测数据库连接等关键步骤,... 目录一、下载驱动包二、放jar包三、检测数据库连接JavaJava 如何使用 JDBC 连接 mys