MySQL의 count(), Union() 및 group by 문에 대한 자세한 설명

이 기사에서는 count(), Union() 및 명령문별 그룹화를 안내하고 MySQL 지식 포인트(다른 count() 사용법, 공용체 실행 프로세스, 명령문별 그룹화)를 보충합니다.

1. MySQL에서 count()의 다양한 용도

count()는 집계 함수입니다. 반환된 결과 집합은 count 함수의 매개 변수가 NULL이 아닌 경우 누적됩니다. 값이 1로 추가되고, 그렇지 않으면 추가되지 않습니다. 마지막으로 누적 값이 반환됩니다. [관련 권장사항: mysql 동영상 튜토리얼]

1. count(기본 키 id)의 경우 InnoDB 엔진은 전체 테이블을 순회하여 각 행의 id 값을 가져와 서버 계층에 반환합니다. 서버 계층은 ID를 얻은 후 비어 있을 수 없다고 판단하여

2 행별로 누적합니다. count(1)의 경우 InnoDB 엔진은 테이블 전체를 순회하지만 값을 가져오지 않습니다. 서버 계층은 반환된 각 행에 숫자 1을 넣습니다. 비어 있을 수 없다고 판단하여 행별로 누적합니다. 3. count(필드)의 경우 이 필드가 null이 아닌 것으로 정의된 경우 행별로 이 필드를 읽습니다. 해당 레코드는 null이 될 수 없다고 판단하고 행별로 누적하며, 필드 정의에서 null을 허용하면 실행 시 null이 될 수 있다고 판단하고 값을 꺼내어 있는지 판단해야 합니다. Null이 아님

4.

의 경우 Null이 아니어야 합니다.

count(*)来说，并不会把全部字段取出来，而是专门做了优化。不取值，count(*)

2. Union 실행 프로세스

다음 표 t1을 예로 들어 보겠습니다.

create table t1(id int primary key, a int, b int, index(a));

CREATE DEFINER=`root`@`%` PROCEDURE `idata`()
BEGIN
	declare i int;
  set i=1;
  while(i<=1000)do
    insert into t1 values(i, i, i);
    set i=i+1;
  end while;

END

다음 SQL 문을 분석합니다.

(select 1000 as f) union (select id from t1 order by id desc limit 2);

union의 의미는 이 두 하위 쿼리 결과의 합집합을 취하는 것입니다. 합집합은 두 집합이 함께 추가되고 중복 행 중 한 행만 두 번째 행에 유지됨을 의미합니다

key=PRIMARY. 이는 두 번째 절이 세 번째 행의 인덱스 id

Extra 필드를 사용함을 나타냅니다. row, 하위 쿼리의 결과 집합에 대해 Union을 수행할 때 이 문의 실행 흐름은 다음과 같습니다.
1. 이 임시 테이블에는 정수 필드가 하나만 있습니다. , f는 기본 키 필드입니다
2. 첫 번째 하위 쿼리를 실행하고 값 1000

3을 실행합니다.

첫 번째 행 ID=1000을 가져와 임시 테이블에 삽입해 보세요. 하지만 임시 테이블에는 고유성 제약 조건을 위반하는 값 1000이 이미 존재하므로 삽입에 실패하고 실행이 계속됩니다

두 번째 행 id=999를 얻어 임시 테이블에 삽입에 성공합니다

4. 임시 테이블에서 키를 눌러 데이터를 한 행씩 꺼내 결과를 반환하고 임시 테이블을 삭제합니다. 결과에는 1000과 999

여기의 메모리 임시 테이블이 두 행의 데이터가 포함됩니다. 데이터를 임시 저장하는 역할을 하며, 계산 과정에서도 임시 테이블을 사용합니다. 기본 키 ID의 고유성 제약 조건은 Union의 의미를 구현합니다

위 문장의 Union을 Union All로 변경하면 의 의미가 없습니다. 중복 제거. 이런 방식으로 실행되면 하위 쿼리가 순차적으로 실행되고, 얻은 결과는 결과 집합의 일부로 클라이언트에 직접 전송됩니다. 따라서 임시 테이블이 필요하지 않습니다

두 번째 줄의 Extra 필드에는 Using index가 표시됩니다. 이는 포함 인덱스만 사용하고 임시 테이블은 사용하지 않음을 의미합니다

3. 설명문별 그룹 설명

1 , 실행 프로세스별 그룹화

여전히 위의 테이블 t1을 사용하여 다음 SQL 문을 분석합니다.

select id%10 as m, count(*) as c from t1 group by m;

이 문의 논리는 테이블 t1의 데이터를 다음에 따라 그룹화하는 것입니다. id%10, 정렬 후 m 출력 결과에 따라 통계를 수행합니다. explain 결과는 다음과 같습니다:

Extra 필드에서 세 가지 정보를 볼 수 있습니다:

index 사용, 이는 이 명령문이 포함 인덱스를 사용하고 인덱스 a를 선택하며 반환할 필요가 없음을 의미합니다. the table

temporary 테이블을 사용한다는 의미

filesort를 사용하면 정렬이 필요함을 의미

이 문의 실행 흐름은 다음과 같습니다.
1. 메모리에 임시 테이블을 만듭니다. 테이블에 m과 c가 있고 기본 키는 m
2입니다. 테이블 t1 인덱스 a를 스캔하고 리프 노드의 id 값을 차례로 꺼내서 id%10의 결과를 계산하여 다음과 같이 기록합니다. 기본 키 x가 있는 행, 행의 c 값에 1을 더합니다.

内存临时表排序流程图：






如果并不需要对结果进行排序，在SQL语句末尾增加order by null：

select id%10 as m, count(*) as c from t1 group by m order by null;



由于表t1中的id值是从1开始的，因此返回的结果集中第一行是id=1

这个例子里由于临时表只有10行，内存可以放得下，因此全程只使用了内存临时表。但是，内存临时表的大小是有限的，参数tmp_table_size就是控制整个内存大小的，默认是16M

set tmp_table_size=1024;
select id%100 as m, count(*) as c from t1 group by m order by null limit 10;

把内存临时表的大小限制为最大1024字节，并把语句改成id%100，这样返回结果里有100行数据。但是，这时的内存临时表大小不够存下这100行数据，也就是说，执行过程中会发现内存临时表大小达到了上限。那么，这时候会把内存临时表转成磁盘临时表，磁盘临时表默认使用的引擎是InnoDB

2、group by优化方法——索引

group by的语义逻辑，是统计不同的值的个数。但是，由于每一行的id%100的结果是无序的，所以就需要有一个临时表来记录并统计结果。那么，如果扫描过程中可以保证出现的数据是有序的就可以了

假设，现在有一个类似下图的这么一个数据结构


如果可以确保输入的数据是有序的，那么计算group by的时候，就只需要从左到右，顺序扫描，依次累加。也就是下面这个流程：

• 当碰到第一个1的时候，已经知道累积了X个0，结果集里的第一行就是(0,X)
• 当碰到第一个2的时候，已经知道累积了Y个1，结果集里的第一行就是(1,Y)

按照这个逻辑执行的话，扫描到整个输入的数据结束，就可以拿到group by的结果，不需要临时表，也需要再额外排序

在MySQL5.7版本支持了generated column机制，用来实现列数据的关联更新。创建一个列z，在z列上创建一个索引

alter table t1 add column z int generated always as(id % 100), add index(z);

这样，索引z上的数据就是有序的了。group by语句就可以改成：

select z, count(*) as c from t1 group by z;


从这个Extra字段可以看到，这个语句的执行不再需要临时表，也不需要排序了

3、group by优化方法——直接排序

在group by语句中加入SQL_BIG_RESULT这个提示，就可以告诉优化器：这个语句涉及的数据量很大，直接用磁盘临时表。因为磁盘临时表是B+树存储，存储效率不如数组来得高。所以MySQL优化器直接用数组来存

select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

1.初始化sort_buffer，确定放入一个整型字段，记为m

2.扫描表t1的索引a，依次取出里面的id值，将id%100的值存入sort_buffer中

3.扫描完成后，对sort_buffer的字段m做排序（如果sort_buffer内存不够用，就会利用磁盘临时文件辅助排序）

4.排序完成后，就得到了一个有序数组

根据有序数组，得到数组里面的不同值，以及每个值的出现次数


这个语句的执行没有再使用临时表，而是直接用了排序算法

更多编程相关知识，请访问：编程入门！！

위 내용은 MySQL의 count(), Union() 및 group by 문에 대한 자세한 설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!