SELECT COUNT(*) 会造成全表扫描？回去等通知吧
阅读本文大概需要 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......等技术栈！
⬇戳阅读原文领取！ 朕已阅
