MySQL 성능 최적화 하위 쿼리에 대한 자세한 소개

프로젝트를 할 때 서브 쿼리를 사용하지 말라는 말을 들었던 기억이 나니 이 문장이 맞는지 이번 글을 한 번 살펴보도록 하겠습니다.

그 전에,

Mysql Server의 연결 스레드는 클라이언트가 보낸 SQL 요청을 받으면 일련의 분해 분석을 거쳐 해당 작업을 수행합니다. 그런 다음 MySQL은 쿼리 최적화 모듈을 사용하여 SQL에 포함된 데이터 테이블의 관련 통계 정보를 기반으로 계산 및 분석을 수행한 다음 Mysql이 고려하는 데이터 액세스 방법을 도출합니다. 가장 합리적이고 최적의 방법, 즉 우리는 종종 "실행 계획"이라고 말하고, 얻은 실행 계획에 따라 스토리지 엔진 인터페이스를 호출하여 해당 데이터를 얻은 다음 반환된 데이터에 대해 관련 처리를 수행합니다. 해당 형식은 결과 집합으로 사용되어 클라이언트에 반환됩니다.

참고: 여기에 언급된 통계 데이터는 Mysql에 통보한 후 얻은 일부 데이터 통계입니다. Analyze table 명령을 통해 테이블의 관련 데이터를 분석합니다. 이러한 데이터는 MySQL 최적화 프로그램에서 생성되는 실행 계획의 품질이 주로 이러한 통계 데이터에 의해 결정됩니다.

1 . 테이블 생성

create table User(
　　Id int not null PRIMARY key auto_increment ,
　　NickName varchar(50) comment '用户昵称',
　　Sex int comment '性别',
　　Sign varchar(50) comment '用户签名',
　　Birthday datetime comment '用户生日',
　　CreateTime datetime comment '创建时间') default charset=utf8 comment '用户表';create table UserGroup(
　　Id int not null PRIMARY key auto_increment ,
　　UserId int not null comment 'user Id',
　　GroupId int not null comment '用户组Id',
　　CreateTime datetime comment '创建时间',
　　-- key index_groupid(GroupId) using btree,　　key index_userid(groupid, UserId) using btree
) default charset=utf8 comment '用户组表';

2. 데이터 준비

var conStr = ConfigurationManager.ConnectionStrings["ConStr"].ToString();
using (IDbConnection conn = new MySqlConnection(conStr))
{
    Stopwatch watch = new Stopwatch();
    var sql = string.Empty;
    var names = new string[] { "非", "想", "红", "帝", "德", "看", "梅", "插", "兔" };
    Random ran = new Random();  
    var insertSql = @" insert into User(NickName,Sex,Sign, Birthday, CreateTime) values(@NickName,@Sex,@Sign, @Birthday, @CreateTime); 
    INSERT INTO usergroup  (UserId,  GroupId,  CreateTime )  VALUES (LAST_INSERT_ID() ,   @GroupId,  @CreateTime);";
    watch.Start();
    if (conn.State == ConnectionState.Closed)
    {
        conn.Open();
    }

    var tran = conn.BeginTransaction();
    for (int i = 0; i < 100000; i++)
    {
        var param = new { NickName = names[ran.Next(9)] + names[ran.Next(9)] + i, Sign = names[ran.Next(9)] + names[ran.Next(9)], CreateTime = DateTime.Now, Birthday = DateTime.Now.AddYears(ran.Next(10, 30)), Sex = i % 2, GroupId = ran.Next(1, 100) };
        conn.Execute(insertSql, param, tran);
    }
    tran.Commit();

    conn.Dispose();
    watch.Stop();
    Console.WriteLine(watch.ElapsedMilliseconds);
}

여기에 5000개의 데이터를 삽입했고, 그룹을 무작위로 99개 그룹으로 나눴습니다.

3.

explain
select user.id, user.nickname from usergroup 
left join user  on usergroup.UserId = user.Id
where  usergroup.groupid = 1 
order by usergroup.UserId desc
limit 100, 20;

 explain
select user.id, user.nickname
from (select id, userid from usergroup where groupid = 1 order by userid limit 100, 20) t
left join  user on t.UserId = user.id ;

 explain
select user.id, user.nickname
from (select id, userid from usergroup where groupid = 1 order by userid ) t
left join  user on t.UserId = user.id 
limit 100, 20;

두 번째 첫 번째 문장과 세 번째 문장 모두 하위 쿼리를 사용합니다. 차이점은 두 번째 문장이 먼저 20개의 데이터를 얻은 다음 이를 사용자 테이블과 연결한다는 것입니다.

4.

데이터 100,000개의 경우:

첫 번째 문장을 보세요

두 번째 문장을 보세요 문장

세 번째 문장

위 세 장의 사진을 보면 뭔가 그럴 수 있을 것 같은 느낌이 든다

먼저 보세요 그들의 행은 두 번째 문장이 가장 많아서 더하면 1,000이 넘고 나머지 두 문장을 더하면 996이 됩니다. 하지만 제가 말씀드리고 싶은 것은 여기서는 보지 말라는 것입니다. 행의 합계에서 가장 큰 ID부터 시작하는 것이 올바른 방법입니다. 명령문부터 시작하여 동일한 ID를 가진 명령문이 위에서 아래로 순차적으로 실행됩니다.

먼저 id=2인 명령문을 살펴보겠습니다. 두 번째 문장과 첫 번째 문장의 id=1 명령문은 모두 usergroup 테이블의 데이터를 필터링하고 동일한 결과 세트 A를 얻을 수 있습니다.

모두 동일한 결과 집합을 기반으로 동작하고 그 다음에는 차이가 발생합니다.

먼저 첫 번째 문장을 보고 결과 집합 A를 기준으로 왼쪽 조인 테이블 사용자로 이동하여 필터링합니다. 최종 데이터를 클라이언트에 반환합니다.

두 번째 문장은 위의 A를 기반으로 데이터를 다시 필터링하여 필요한 데이터를 얻은 다음 이 데이터를 사용하여 사용자 테이블과 Left Join을 통해 가져옵니다.

위의 관점에서 실행 계획 중 두 번째 실행 계획이 더 효율적입니다.

하위 쿼리를 통해 쿼리 범위를 크게 줄일 수 있다면, 하위 쿼리 문 사용을 고려해 보세요.

위 내용은 MySQL 성능 최적화 하위 쿼리에 대한 자세한 소개의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!