PM 상위 ID(vBulletin 데이터베이스)로 그룹화할 때 MySQL 쿼리 속도가 느림

PM 상위 ID(vBulletin 데이터베이스)로 그룹화할 때 MySQL 쿼리 속도가 느림

IM과 같은 대화처럼 vBulletin에서 모든 PM을 가져오려고 하는데, 이 PM을 내에서 사용해야 합니다.Dapper를 사용하는 NET Core 라이브러리.이것은 다음을 의미합니다.A가 B에게 메시지를 보내면, B는 두 개의 메시지를 가지고 한 번의 대화를 하는 것입니다.이로 인해 성능 문제가 발생하기 때문에 Dapper 쿼리를 직접 실행하여 DBever를 이용하여 파악하고자 하였습니다.

받은 편지함에서 페이지의 대화를 가져오기 위해 다음과 같은 쿼리를 작성했습니다.

SELECT pm.pmid
FROM pm, pmtext AS txt
WHERE pm.pmtextid = txt.pmtextid 
AND (pm.userid = 123 OR txt.fromuserid = 123)
AND pm.folderid != -1
GROUP BY IF(pm.parentpmid != 0, pm.parentpmid, pm.pmid)
LIMIT 0, 50

이것은 나에게 #123 사용자를 위한 첫 50개의 대화 ID를 주었습니다.작동하지만 실행하는 데는 440ms가 걸렸습니다.관련된 모든 필드에 인덱스를 추가하려고 했습니다.

ALTER TABLE pmtext ADD INDEX fromuserid_only(fromuserid);
ALTER TABLE pm ADD INDEX userid_only(userid);
ALTER TABLE pm ADD INDEX parentpmid(parentpmid);

하지만 아직은 느립니다.그것은 그 때문에 생긴 것 같습니다.GROUP BY. 내가 그냥 할때도GROUP BY pm.parentpmid(오류 데이터를 생성할 수 있지만 성능 테스트를 위해서만 해당) 쿼리 실행 시간은 더 좋지 않습니다.제거할 때.GROUP BY, 꽤 빠릅니다(~12ms).

대화의 전체 페이지를 세는 내 쿼리는 조인과 빠른(< 20ms) 없이 유사합니다.

// DbConnection db = ...
string sqlTotalPages = @"
    SELECT CEIL(COUNT(*)/ 50) AS pages
   FROM pm, pmtext AS txt
    WHERE pm.pmtextid = txt.pmtextid 
    AND (pm.userid = 18 OR txt.fromuserid = 18)";
int totalPages = db.QueryFirstOrDefault<int>(sqlTotalPages);

왜 그럴까요?GROUP BY질문 속도를 그렇게 많이 늦춘다고요?어떻게 하면 성능을 향상시킬 수 있을까요?

vB에서 테이블 구조

CREATE TABLE `pm` (
  `pmid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pmtextid` int(10) unsigned NOT NULL DEFAULT '0',
  `userid` int(10) unsigned NOT NULL DEFAULT '0',
  `folderid` smallint(6) NOT NULL DEFAULT '0',
  `messageread` smallint(5) unsigned NOT NULL DEFAULT '0',
  `parentpmid` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`pmid`),
  KEY `pmtextid` (`pmtextid`),
  KEY `userid` (`userid`,`folderid`),
  KEY `userid_only` (`userid`),
  KEY `parentpmid` (`parentpmid`)
) ENGINE=MyISAM AUTO_INCREMENT=221965 DEFAULT CHARSET=latin1

CREATE TABLE `pmtext` (
  `pmtextid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `fromuserid` int(10) unsigned NOT NULL DEFAULT '0',
  `fromusername` varchar(100) NOT NULL DEFAULT '',
  `title` varchar(250) NOT NULL DEFAULT '',
  `message` mediumtext,
  `touserarray` mediumtext,
  `iconid` smallint(5) unsigned NOT NULL DEFAULT '0',
  `dateline` int(10) unsigned NOT NULL DEFAULT '0',
  `showsignature` smallint(5) unsigned NOT NULL DEFAULT '0',
  `allowsmilie` smallint(5) unsigned NOT NULL DEFAULT '1',
  `reportthreadid` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`pmtextid`),
  KEY `fromuserid` (`fromuserid`,`dateline`),
  KEY `fromuserid_only` (`fromuserid`),
  KEY `fromuserid_only2` (`fromuserid`)
) ENGINE=MyISAM AUTO_INCREMENT=118470 DEFAULT CHARSET=latin1

GROUP BY가 처리 시간 증가 등의 원인이 되는 것은 LIMIT 때문이라고 생각합니다.사용자의 기준과 일치하는 50개가 발견되면 DB 엔진은 쿼리의 행 처리를 중지할 수 있습니다.전체 테이블을 처리해야 하지만 GROUP BY 절과 함께 그룹화한 후 50개의 첫 번째 결과가 반환됩니다.해결책으로, GROUP BY를 제거하고 WHERE-clause에 "그리고 pm.parent pmid = 0"을 추가하면 정확한 결과를 얻을 수 있습니까?GROUP BY 절은 WHERE를 사용하여 보다 효율적으로 수행되는 결과에서 부모가 있는 행을 제거하기 위해 있는 것 같습니다(부모가 있는 모든 행에도 결과 중 부모가 있다고 가정).

당신의 쿼리를 최적화하기 위해서, 나는 당신이 그룹으로 무엇을 달성하고 싶은지 조항별로 알아야 합니다.당신이 예상한 결과를 표에 적은 예시를 들어주실 수 있습니까?

만약 당신이 부모 메일만 보여주기를 원한다면, 나는 Erik H가 다음 쿼리를 사용하는 것이 낫다는 것에 동의합니다.

SELECT pm.pmid
FROM pm, pmtext AS txt
WHERE pm.pmtextid = txt.pmtextid 
AND (pm.userid = 123 OR txt.fromuserid = 123)
AND pm.folderid != -1
AND pm.parentpmid = 0
LIMIT 0, 50;

하지만 그것은 당신의 질문과는 다른 결과를 줍니다.

당신의 그룹 BY의 효과는 제가 보기에는 상당히 자의적으로 보입니다.pmid는 Aggregate 함수의 일부가 아니며 그룹화되어 있지 않기 때문에 MySQL/mariaDB는 동일한 그룹화에 적용되는 첫 번째 값을 반환합니다.

데이터베이스에 다음 값을 추가하면 다음과 같습니다.

INSERT INTO pmtext (`fromuserid`, `fromusername`,`title`,`message`,`touserarray`,`iconid`,`dateline`,`showsignature`,`allowsmilie`,`reportthreadid`)
VALUES 
    (123, 'Pete',  'Titlel',            'Hello1', '', 0, 0, 0, 1, 0),
    (123, 'Pete',  'Title2',            'Hello2', '', 0, 0, 0, 1, 0),
    (2,   'Hank',  'Re: Title1',        'Hello3', '', 0, 0, 0, 1, 0),
    (2,   'Hank',  'Re: Title2',        'Hello4', '', 0, 0, 0, 1, 0),
    (3,   'Chris', 'Re: Title2(a)',     'Hello5', '', 0, 0, 0, 1, 0),
    (2,   'Hank',  'Re: Re: Title2(a)', 'Hello6', '', 0, 0, 0, 1, 0),
    (123, 'Pete',  'Title3',            'Hello7', '', 0, 0, 0, 1, 0),
    (123, 'Pete',  'Re: Re: Title1',    'Hello8', '', 0, 0, 0, 1, 0),
    (123, 'Pete',  'Title4',            'Hello9', '', 0, 0, 0, 1, 0);

INSERT INTO pm ( `pmtextid`, `userid`, `folderid`, `messageread`, `parentpmid`)
VALUES
  (118470 , 123, 0, 0, 0),
  (118471 , 123, 0, 0, 0), 
  (118472 , 123, 0, 0, 221965),
  (118473 , 123, 0, 0, 221966), 
  (118474 , 123, 0, 0, 221966),
  (118475 , 123, 0, 0, 221969), 
  (118476 , 123, 0, 0, 0),
  (118477 , 123, 0, 0, 221967),
  (118478 , 123, 0, 0, 0);

그러면 쿼리가 반환됩니다.

• 221965, 그것이 "Title1"입니다.
• 221966, 그것은 "Title2
• 221972, 그것은 "Re: Re: Title1"입니다.
• 22 1970, 그것은 "Re: Re: Title2(a)"입니다.
• 221971, 그것은 "Title3"입니다.
• 221973, 그것은 "Title4"입니다.

쿼리를 최적화하기 전에 이것이 당신이 기대하는 결과인지 알아야 합니다.

언급URL : https://stackoverflow.com/questions/62370321/mysql-query-slow-when-grouping-by-pm-parent-id-vbulletin-database