>  기사  >  데이터 베이스  >  count(*)가 왜 그렇게 느린가요? 원인 분석

count(*)가 왜 그렇게 느린가요? 원인 분석

青灯夜游
青灯夜游앞으로
2023-01-05 21:21:084080검색

count(*)가 왜 그렇게 느린가요? 다음 글에서는 그 이유를 분석하고 count(*)의 실행 과정에 대해 이야기해보겠습니다. 모두에게 도움이 되길 바랍니다!

count(*)가 왜 그렇게 느린가요? 원인 분석

이 기사를 쓰고 싶지 않았습니다. 대부분의 숙련된 개발자가 이 문제를 접했고 관련 이유를 이해했을 것입니다. 그러나 최근에는 우려되는 몇몇 기술 공개 No.가 관련 기사를 추진하는 것을 보았습니다. 정말 놀랐어요!

먼저 공개 계정 기사의 결론:

  • count(*): 아무런 처리 없이 모든 행의 데이터를 가져오고, 행 수가 1씩 증가합니다.
  • count(1): 각 행에 대해 고정 값 1을 사용하여 모든 행의 데이터를 가져옵니다. 이 값은 행 수에 1을 더한 값이기도 합니다.
  • count(id): id는 기본 키를 나타내며, 데이터의 모든 행에서 id 필드를 구문 분석해야 하며, 행 수는 1씩 증가합니다.
  • count(일반 인덱스 열): 모든 행의 데이터에서 일반 인덱스 열을 구문 분석한 후 NULL인지 여부를 확인해야 하며, NULL이 아닌 경우 행 수 + 1입니다.
  • count(인덱싱되지 않은 열): 테이블 전체를 스캔하여 모든 데이터를 얻은 후 분석에 인덱싱된 열을 추가하지 않고 NULL인지 여부를 판단하여 NULL이 아닌 경우 행 수 + 1입니다.

결론: count(*) ≒ count(1) > count(id) > count (일반 인덱스 열) > count (인덱싱되지 않은 열)

위의 결론 는 순전히 Fart를 기반으로 합니다. 그냥 사람이 만들어낸 것일 뿐이고, 실행 계획을 살펴봐도 그렇게 황당한 결론은 내릴 수 없습니다.

이 글이 여러 기술 공개 계정에 다시 게시되었다는 것이 믿겨지지 않습니다!

다음 내용은 모두 mysql 5.7 + InnoDB 엔진 분석을 바탕으로 작성되었습니다. mysql 5.7 + InnoDB引擎, 进行的分析。

拓展:

MyISAM 如果没有查询条件,只是简单的统计表中数据总数,将会返回的超快,因为service层中获取到表信息中的总行数是准确的,而InnoDB只是一个估值。

实例

废话不多说,先看一个例子。

以下是一张表数据量有100w,表中字段相对较短,整体数据量不算大。

CREATE TABLE `hospital_statistics_data` (
  `pk_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `id` varchar(36) COLLATE utf8mb4_general_ci NOT NULL COMMENT '外键',
  `hospital_code` varchar(36) COLLATE utf8mb4_general_ci NOT NULL COMMENT '医院编码',
  `biz_type` tinyint NOT NULL COMMENT '1服务流程  2管理效果',
  `item_code` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '考核项目编码',
  `item_name` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '考核项目名称',
  `item_value` varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '考核结果',
  `is_deleted` tinyint DEFAULT NULL COMMENT '是否删除 0否 1是',
  `gmt_created` datetime DEFAULT NULL COMMENT '创建时间',
  `gmt_modified` datetime DEFAULT NULL COMMENT 'gmt_modified',
  `gmt_deleted` datetime(3) DEFAULT '9999-12-31 23:59:59.000' COMMENT '删除时间',
  PRIMARY KEY (`pk_id`)
) DEFAULT CHARSET=utf8mb4  COMMENT='医院统计数据';

此表初始状态只有一个聚簇索引

以下分不同索引情况,看一下COUNT(*)的执行计划。

1)在只有一个聚簇索引的情况下看一下执行计划。

EXPLAIN select COUNT(*) from hospital_statistics_data;

结果:

关于执行计划的各个参数的含义,不在本文的讨论范围内,可自行了解。

这里只关注以下几个属性。

  • type: 这里显示index,说明使用了索引。

  • key:PRIMARY使用了主键索引。

  • key_len: 索引长度8字节。

这里有很关键的一点:count(*)也会走索引,在当前情况下使用了聚簇索引。

好,再往下看。

2)存在一个非聚簇索引(二级索引)

给表添加一个hospital_code索引。

alter table hospital_statistics_data add index idx_hospital_code(hospital_code)

此时表中存在2个索引,主键 hospital_code

同样的,再执行一下:

EXPLAIN select COUNT(*) from hospital_statistics_data;

结果:

同样的,看一下 type、key和key_len三个字段。

是不是觉得有点“神奇”。

为何索引变成刚添加的idx_hospital_code了。

先别急着想结论,再看下面一种情况。

3)存在两个非聚簇索引(二级索引)

在上面的基础上,再添加一个二级索引。

alter table hospital_statistics_data add index idx_biz_type(biz_type)

此时表中存在3个索引,主键 、hospital_code 和 biz_type。

同样的,执行一下:

EXPLAIN select COUNT(*) from hospital_statistics_data;

结果:

是不是更困惑了,索引又..又又...变了.

变成新添加的idx_biz_type。

先不说为何会产生以上的变化,继续往下分析。

在以上3个索引的基础上,分别看一下,count(1)count(id)count(index)count(无索引)

확장:

MyISAM 쿼리 조건이 없고 단순히 테이블의 전체 데이터 수를 센 경우 서비스 계층에서 얻은 테이블 정보의 전체 행 수가 정확하기 때문에 반환이 매우 빠릅니다. , InnoDB는 단지 추정일 뿐입니다.

  • Example

    더 이상 고민하지 말고 먼저 예를 살펴보겠습니다.
다음은 데이터 볼륨이 100만 개인 테이블입니다. 테이블의 필드는 상대적으로 짧고 전체 데이터 볼륨은 크지 않습니다.

select * from hospital_statistics_data where hospital_code is not null;
이 테이블의 초기 상태에는 클러스터형 인덱스가 하나만 있습니다.

    다음은 다양한 인덱스 상황에서 COUNT(*)의 실행 계획을 살펴봅니다.
  • 1) 클러스터형 인덱스가 하나만 있을 때의 실행 계획을 살펴보세요. 🎜🎜rrreee🎜결과: 🎜🎜🎜🎜실행 계획의 각 매개변수의 의미는 이 글의 범위에 포함되지 않습니다. 스스로 이해할 수 있습니다. 🎜🎜여기서는 다음 속성에만 집중하세요. 🎜🎜🎜🎜type: 여기에 인덱스가 표시되어 인덱스가 사용되었음을 나타냅니다. 🎜🎜🎜🎜key: PRIMARY는 기본 키 인덱스를 사용합니다. 🎜🎜🎜🎜key_len: 인덱스 길이 8바이트. 🎜🎜🎜🎜여기에는 매우 중요한 점이 있습니다. count(*)도 인덱스를 사용하며 현재의 경우 클러스터형 인덱스가 사용됩니다. 🎜🎜좋아, 아래를 내려다봐. 🎜🎜🎜2) 비클러스터형 인덱스(보조 인덱스)가 있습니다. 🎜🎜🎜테이블에 Hospital_code 인덱스를 추가합니다. 🎜rrreee🎜이때 테이블에는 기본 키hospital_code라는 두 개의 인덱스가 있습니다. 🎜🎜동일, 다시 실행: 🎜rrreee🎜결과: 🎜🎜🎜🎜마찬가지로 type, key 및 key_len의 세 가지 필드를 살펴보세요. 🎜🎜조금 "🎜마법🎜"한 느낌이 드시나요? 🎜🎜인덱스가 새로 추가된 idx_hospital_code가 된 이유는 무엇인가요? 🎜🎜먼저 성급히 결론짓지 마시고, 다음 상황을 살펴보세요. 🎜🎜🎜3) 논클러스터형 인덱스(보조 인덱스)가 2개 있습니다. 🎜🎜🎜위 내용을 바탕으로 보조 인덱스를 추가합니다. 🎜rrreee🎜이때 테이블에는 기본키, Hospital_code, biz_type 3개의 인덱스가 있습니다. 🎜🎜마찬가지로 실행: 🎜rrreee🎜결과: 🎜🎜🎜🎜더 헷갈리시나요? 인덱스가... 또 바뀌었습니다. 🎜🎜새로 바뀌었습니다. idx_biz_type이 추가되었습니다. 🎜🎜위의 변경 사항이 발생한 이유에 대해서는 이야기하지 말고 아래에서 계속 분석해 보겠습니다. 🎜🎜위의 세 가지 인덱스를 바탕으로 각각 count(1), count(id), count(index)를 살펴보겠습니다. code> , count (no index)🎜🎜이 네 가지 상황과 count(*)의 실행 계획의 차이점은 무엇인가요? 🎜🎜🎜🎜count(1)🎜🎜🎜🎜🎜🎜🎜🎜🎜count(id) 샘플 테이블의 경우 기본 키는 pk_id🎜입니다.

count(*)가 왜 그렇게 느린가요? 원인 분석

  • count(index)

这里选取biz_type索引字段。

  • count(无索引)

小结:

  • count(index) 会使用当前index指定的索引。

  • count(无索引) 是全表扫描,未走索引。

  • count(1) , count(*), count(id) 一样都会选择idx_biz_type索引

看到这,你还觉得那些千篇一律的公众号文章的结论正确吗?

必要知识点

  • mysql 分为service层引擎层

  • 所有的sql在执行前会经过service层的优化,优化分为很多类型,简单的来说可分为成本规则

  • 执行计划所反映的是service层经过sql优化后,可能的执行过程。并非绝对(免得有些人说我只看执行计划过于片面)。绝大多数情况执行计划是可信的

  • 索引类型分为聚簇索引非聚簇索引(二级索引)。其中数据都是挂在聚簇索引上的,非聚簇索引上只是记录的主键id。

  • 抛开数据内存,只谈数据量,都是扯淡。什么500w就是极限,什么2个表以上的join都需要优化了,什么is null不会走索引等,纯纯的放屁。

  • 相信一点,编写mysql代码的人比,看此文章的大部分人都要优秀。他们会尽可能在执行前,对我这样菜逼写的乱七八糟的sql进行优化。

原因分析

其实原因非常非常简单,上面也说了,service层会基于成本进行优化

并且,正常情况下,非聚簇索引所占有的内存要远远小于聚簇索引。所以问题来了,如果你是mysql的开发人员,你在执行count(*)查询的时候会使用那个索引?

我相信正常人都会使用非聚簇索引

那如果存在2个甚至多个非聚簇索引又该如何选择呢?

那肯定选择最短的,占用内存最小的一个呀,在回头看看上面的实例,还迷惑吗。

同样都是非聚簇索引。idx_hospital_codelen146字节;而idx_biz_typelen只有1。那还要选吗?

那为何count(*)走了索引,却还是很慢呢?

这里要明确一点,索引只是提升效率的一种方式,但不能完全的解决效率问题。count(*)有一个明显的缺陷,就是它要计算总数,那就意味着要遍历所有符合条件的数据,相当于一个计数器,在数据量足够大的情况下,即使使用非聚簇索引也无法优化太多。

官方文档:

InnoDBhandlesSELECT COUNT(*)andSELECT COUNT(1)operations in the same way. There is no performance difference.

简单的来说就是,InnoDB下 count(*) 等价于 count(1)

既然会自动走索引,那么上面那个所谓的速度排序还觉得对吗? count(*)的性能跟数据量有很大的关系,此外最好有一个字段长度较短的二级索引。

拓展:

另外,多说一下,关于网上说的那些索引失效的情况,大多都是片面的,我这里只说一点。量变才能引起质变,索引的失效取决于你圈定数据的范围,若你圈定的数据量占整体数据量的比例过高,则会放弃使用索引,反之则会优先使用索引。但是此规则并不是完美的,有时候可能与你预期的不同,也可以通过一些技巧强制使用索引,但这种方式少用。

举个栗子:

通过上面这个表hospital_statistics_data,我进行了如下查询:

select * from hospital_statistics_data where hospital_code is not null;

此时这个sql会使用到hospital_code的索引吗?

这里也不卖关子了,若hospital_code只有很少一部分数据是null值,那么将不会走索引,反之则走索引。

原因就2个字:回表

그것은 설탕 오렌지를 사는 것과 같습니다. 몇 킬로그램만 사면 바구니에 있는 가장 좋은 것을 고르면 됩니다. 하지만 바구니를 사고 싶다면 사장님이 하나씩 고르게하지 않고 한 번에 전체 바구니를 주실 것이라고 믿습니다. 물론 모두가 바보가 아니며 몇 개가 있어야한다는 것을 모두 알고 있습니다. 바구니에 나쁜 과일. 그러나 이것이 가장 효율적이며 상사에게 손실을 덜 초래합니다.

실행 과정

"루트에서 MySQL 이해"에서 발췌. MySQL을 체계적으로 배우지 못한 분들도 이 책을 꼭 읽어보시길 권합니다.

1. 먼저 서버 계층에서 count 변수를 유지합니다.

2. 서버 계층은 InnoDB 엔진에 첫 번째 레코드를 요청합니다.

3. InnoDB는 첫 번째 보조 인덱스 레코드를 찾아 서버 계층에 반환합니다. 이것으로 레코드 수만 계산하므로 테이블로 돌아갈 필요가 없습니다)

4. COUNT 함수의 매개 변수가 *이므로 MySQL은 *를 상수 0으로 처리합니다. 0은 NULL이 아니므로 서버 계층에서는 count 변수에 1을 추가합니다.

5. 서버 계층은 InnoDB에 다음 레코드를 요청합니다.

6.InnoDB는 보조 인덱스 레코드의 next_record 속성을 통해 다음 보조 인덱스 레코드를 찾아 서버 계층으로 반환합니다.

7. 서버 계층은 계속해서 count 변수에 1을 추가합니다.

8. InnoDB가 서버 계층에 기록 가능한 메시지를 반환하지 않을 때까지 위 프로세스를 반복합니다.

9. 서버 계층은 count 변수의 최종 값을 클라이언트에 보냅니다.

요약

글을 다 쓴 후에도 여전히 우울한 기분이 들었습니다. 공개 계정에서 얻을 수 있는 좋은 글이 점점 줄어들고 있습니다.

처음 일을 시작하던 시절이 정말 그리워요. 그땐 매일 아침 공식 계정 기사를 읽으며 시간을 보냈는데 지금은 다 광고에요. 왜!

하지만 정상입니다. 누구도 항상 사랑을 위해 전기를 생산할 수는 없습니다.

공부할 때 책을 더 많이 읽는 것이 좋습니다. 일반적으로 책으로 쓸 수 있는 책도 나쁘지 않습니다. 요즘 밤에 검색할 수 있는 건 똑같은 기사들뿐이고 그게 맞는지 그른지 모르겠어요. 온라인

【관련 추천: mysql 비디오 튜토리얼

위 내용은 count(*)가 왜 그렇게 느린가요? 원인 분석의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!

성명:
이 기사는 juejin.cn에서 복제됩니다. 침해가 있는 경우 admin@php.cn으로 문의하시기 바랍니다. 삭제