SELECT COUNT(*) 会造成全表扫描?回去等通知吧
共 10129字,需浏览 21分钟
·
2023-10-16 10:38
阅读本文大概需要 6 分钟。
来自:程序员大彬
-
前言 -
SQL 选用索引的执行成本如何计算 -
实例说明 -
总结
前言
SELECT COUNT(*)
会不会导致全表扫描引起慢查询呢?
SELECT COUNT(*) FROM SomeTable
where_clause
的 COUNT(*)
,MySQL 是有优化的,优化器会选择成本最小的辅助索引查询计数,其实反而性能最高,这种说法对不对呢
EXPLAIN
来查询了一下执行计划
EXPLAIN SELECT COUNT(*) FROM SomeTable
COUNT(1)
,还是 COUNT(*)
,MySQL 都会用成本最小 的辅助索引查询方式来计数,也就是使用 COUNT(*)
由于 MySQL 的优化已经保证了它的查询性能是最好的!随带提一句,COUNT(*)
是 SQL92 定义的标准统计行数的语法,并且效率高,所以请直接使用COUNT(*)
查询表的行数!
-
SQL 选用索引的执行成本如何计算 -
实例说明
SQL 选用索引的执行成本如何计算
-
IO 成本: 即从磁盘把数据加载到内存的成本,默认情况下,读取数据页的 IO 成本是 1,MySQL 是以页的形式读取数据的,即当用到某个数据时,并不会只读取这个数据,而会把这个数据相邻的数据也一起读到内存中,这就是有名的程序局部性原理,所以 MySQL 每次会读取一整页,一页的成本就是 1。所以 IO 的成本主要和页的大小有关 -
CPU 成本:将数据读入内存后,还要检测数据是否满足条件和排序等 CPU 操作的成本,显然它与行数有关,默认情况下,检测记录的成本是 0.2。
实例说明
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 SELECT COUNT(*) FROM person
create_time
辅助索引,显然 MySQL 认为使用此索引进行查询成本最小,这也是符合我们的预期,使用辅助索引来查询确实是性能最高的!
SELECT * FROM person WHERE NAME >'name84059' AND create_time>'2020-05-23 14:39:18'
name_score
或者 create_time
索引才对,从 WHERE 的查询条件来看确实都能命中索引,那是否是使用 SELECT *
造成的回表代价太大所致呢,我们改成覆盖索引的形式试一下
SELECT create_time FROM person WHERE NAME >'name84059' AND create_time > '2020-05-23 14:39:18'
-- 全表扫描执行时间: 4.0 ms
SELECT create_time FROM person WHERE NAME >'name84059' AND create_time>'2020-05-23 14:39:18'
-- 使用覆盖索引执行时间: 2.0 ms
SELECT create_time FROM person force index(create_time) WHERE NAME >'name84059' AND create_time>'2020-05-23 14:39:18'
SHOW TABLE STATUS LIKE 'person'
-
行数是 100264,我们不是插入了 10 w 行的数据了吗,怎么算出的数据反而多了,其实这里的计算是估算 ,也有可能这里的行数统计出来比 10 w 少了,估算方式有兴趣大家去网上查找,这里不是本文重点,就不展开了。得知行数,那我们知道 CPU 成本是 100264 * 0.2 = 20052.8
。 -
数据长度是 5783552,InnoDB 每个页面的大小是 16 KB,可以算出页面数量是 353。
20052.8 + 353 = 20406
。
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
}
create_time
索引执行的的预估执行成本:
{
"index": "create_time",
"ranges": [
"0x5ec8c516 < create_time"
],
"index_dives_for_eq_ranges": true,
"rows": 50132,
"cost": 60159,
"cause": "cost"
}
{
"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
}
]
}
optimizer trace
详细列出了覆盖索引,回表的成本统计情况,有兴趣的可以去研究一下。
总结
EXPLAIN
, optimizer trace
来优化我们的查询语句。
推荐阅读:
Java21正式发布,史诗级增强!虚拟线程、分代 ZGC 正式来袭!!
互联网初中高级大厂面试题(9个G) 内容包含Java基础、JavaWeb、MySQL性能优化、JVM、锁、百万并发、消息队列、高性能缓存、反射、Spring全家桶原理、微服务、Zookeeper......等技术栈!
⬇戳阅读原文领取! 朕已阅