복붙노트

[SQL] 별칭을 허용 성능에 미치는 영향은 HAVING 절에 사용되는

SQL

별칭을 허용 성능에 미치는 영향은 HAVING 절에 사용되는

이 질문에 오늘 아침 자신의 바보의 비트를했다. 문제는 SQL Server를 사용하였고, 정답은 HAVING 절을 추가하고있었습니다. 내가 만든 초기 실수는 SELECT 문에서 별칭이 SQL 서버에서 허용되지 않습니다 HAVING 절에 사용될 수 있다고 생각했다. 나는 SQL 서버 별칭이 HAVING 절에서 사용할 수 있도록 않는, MySQL은 같은 규칙을 가지고 있다고 가정하기 때문에이 오류가 발생했다.

이 날 호기심을 가지고, 나는이 규칙은이 각각의 RDBMS에 적용됩니다 이유를 설명 재료의 무리를 발견, 다른 스택 오버플로 및 주위 찌르고. 그러나 아무 나는 성능에 영향이 / 수 HAVING 절에 별칭을 허용하지 될 것입니다 무엇에 대한 설명을 찾을하지 않았다.

구체적인 예를 제공하기 위해, 위 언급 한 문제의 발생 쿼리를 복제합니다 :

SELECT students.camID, campus.camName, COUNT(students.stuID) as studentCount
FROM students
JOIN campus
    ON campus.camID = students.camID
GROUP BY students.camID, campus.camName
HAVING COUNT(students.stuID) > 3
ORDER BY studentCount

무엇 HAVING 절에 대신 COUNT를 지정 재 별칭을 사용하는 성능에 영향 있을까요? 이 질문은 MySQL을 직접 대답 할 수 있으며,이 HAVING 절에 별칭을 지원한다면 희망 누군가가 SQL에 무슨 일이 일어날 지에 대한 통찰력을 줄 수 있습니다.

이것은 OK 그래서 태양이 순간을 즐기고, MySQL과 SQL 서버 모두와 SQL 질문에 태그를 할 수있는 드문 예이다.

해결법

  1. ==============================

    1.간신히 그냥 특정 쿼리에 초점을 맞추고, 샘플 데이터 아래에로드. 이것은 다른 사람에 의해 언급 된 수 (별개의 ...)로 주소를 다른 쿼리를 수행합니다.

    간신히 그냥 특정 쿼리에 초점을 맞추고, 샘플 데이터 아래에로드. 이것은 다른 사람에 의해 언급 된 수 (별개의 ...)로 주소를 다른 쿼리를 수행합니다.

    에 HAVING 나타납니다의 별명 중 하나를 약간 능가하거나 아주 약간은 대안 (쿼리에 따라)을 능가.

    이 3 분에서 5 분 정도 소요됩니다 광산의이 답변을 통해 신속하게 생성에이 만 5에 대한 행이 기존의 테이블을 사용합니다.

    결과적인 구조 :

    CREATE TABLE `ratings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `thing` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5046214 DEFAULT CHARSET=utf8;

    하지만 그 대신 INNODB를 사용하여. 때문에 범위 예약 삽입에 이상 예상 INNODB 격차를 작성합니다. 그냥 말을하지만, 차이가 없습니다. 4.7 백만 행.

    팀의 가정 된 스키마 근처에서 얻을 수있는 테이블을 수정합니다.

    rename table ratings to students; -- not exactly instanteous (a COPY)
alter table students add column camId int; -- get it near Tim's schema
-- don't add the `camId` index yet

    다음은 시간이 걸릴 것입니다. 덩어리 다시하고 다시 실행하거나 다른 연결은 시간이 초과 될 수 있습니다. 제한 시간은 업데이트 문에서 LIMIT 절하지 않고 인해 500 만 행입니다. 참고, 우리는 LIMIT 절을해야합니까.

    그래서 우리는 50 만 행 반복에 그 일을하고 있습니다. A와 열을 설정합니다 1과 20 사이의 난수

    update students set camId=floor(rand()*20+1) where camId is null limit 500000; -- well that took a while (no surprise)

    더 camId가 null가 없을 때까지 위의 실행하십시오.

    나는 (모든 일이 7 ~ 10 분 소요) 10 번처럼 실행

    select camId,count(*) from students
group by camId order by 1 ;

1   235641
2   236060
3   236249
4   235736
5   236333
6   235540
7   235870
8   236815
9   235950
10  235594
11  236504
12  236483
13  235656
14  236264
15  236050
16  236176
17  236097
18  235239
19  235556
20  234779

select count(*) from students;
-- 4.7 Million rows

    (물론 삽입 후) 유용한 인덱스를 작성합니다.

    create index `ix_stu_cam` on students(camId); -- takes 45 seconds

ANALYZE TABLE students; -- update the stats: http://dev.mysql.com/doc/refman/5.7/en/analyze-table.html
-- the above is fine, takes 1 second

    캠퍼스 테이블을 만듭니다.

    create table campus
(   camID int auto_increment primary key,
    camName varchar(100) not null
);
insert campus(camName) values
('one'),('2'),('3'),('4'),('5'),
('6'),('7'),('8'),('9'),('ten'),
('etc'),('etc'),('etc'),('etc'),('etc'),
('etc'),('etc'),('etc'),('etc'),('twenty');
-- ok 20 of them

    두 쿼리를 실행합니다 :

    SELECT students.camID, campus.camName, COUNT(students.id) as studentCount 
FROM students 
JOIN campus 
    ON campus.camID = students.camID 
GROUP BY students.camID, campus.camName 
HAVING COUNT(students.id) > 3 
ORDER BY studentCount; 
-- run it many many times, back to back, 5.50 seconds, 20 rows of output

    과

    SELECT students.camID, campus.camName, COUNT(students.id) as studentCount 
FROM students 
JOIN campus 
    ON campus.camID = students.camID 
GROUP BY students.camID, campus.camName 
HAVING studentCount > 3 
ORDER BY studentCount; 
-- run it many many times, back to back, 5.50 seconds, 20 rows of output

    그래서 시간은 동일하다. 각을 다스 시간을 달렸다.

    출력이 모두 동일한 것이라고 설명

    +----+-------------+----------+------+---------------+------------+---------+----------------------+--------+---------------------------------+
| id | select_type | table    | type | possible_keys | key        | key_len | ref                  | rows   | Extra                           |
+----+-------------+----------+------+---------------+------------+---------+----------------------+--------+---------------------------------+
|  1 | SIMPLE      | campus   | ALL  | PRIMARY       | NULL       | NULL    | NULL                 |     20 | Using temporary; Using filesort |
|  1 | SIMPLE      | students | ref  | ix_stu_cam    | ix_stu_cam | 5       | bigtest.campus.camID | 123766 | Using index                     |
+----+-------------+----------+------+---------------+------------+---------+----------------------+--------+---------------------------------+

    AVG () 함수를 사용하여, 나는이의 별명과 성능이 12 % 증가를 얻고에서 다음 두 개의 쿼리를 갖는 (EXPLAIN 동일한 출력).

    SELECT students.camID, campus.camName, avg(students.id) as studentAvg 
FROM students 
JOIN campus 
    ON campus.camID = students.camID 
GROUP BY students.camID, campus.camName 
HAVING avg(students.id) > 2200000 
ORDER BY students.camID; 
-- avg time 7.5

explain 

SELECT students.camID, campus.camName, avg(students.id) as studentAvg 
FROM students 
JOIN campus 
    ON campus.camID = students.camID 
GROUP BY students.camID, campus.camName 
HAVING studentAvg > 2200000
ORDER BY students.camID;
-- avg time 6.5

    그리고 마지막으로, DISTINCT :

    SELECT students.camID, count(distinct students.id) as studentDistinct 
FROM students 
JOIN campus 
    ON campus.camID = students.camID 
GROUP BY students.camID 
HAVING count(distinct students.id) > 1000000 
ORDER BY students.camID; -- 10.6   10.84   12.1   11.49   10.1   9.97   10.27   11.53   9.84 9.98
-- 9.9

 SELECT students.camID, count(distinct students.id) as studentDistinct 
 FROM students 
 JOIN campus 
    ON campus.camID = students.camID 
 GROUP BY students.camID 
 HAVING studentDistinct > 1000000 
 ORDER BY students.camID; -- 6.81    6.55   6.75   6.31   7.11 6.36   6.55
-- 6.45

    가진의 별명은 지속적으로 EXPLAIN 같은 출력으로 더 빠른 35 %를 실행합니다. 아래 본. Explain 스 같은 출력이 표시되었습니다 그래서 두 번 같은 성능이 있지만, 일반적인 단서로하지합니다.

    +----+-------------+----------+-------+---------------+------------+---------+----------------------+--------+----------------------------------------------+
| id | select_type | table    | type  | possible_keys | key        | key_len | ref                  | rows   | Extra                                        |
+----+-------------+----------+-------+---------------+------------+---------+----------------------+--------+----------------------------------------------+
|  1 | SIMPLE      | campus   | index | PRIMARY       | PRIMARY    | 4       | NULL                 |     20 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | students | ref   | ix_stu_cam    | ix_stu_cam | 5       | bigtest.campus.camID | 123766 | Using index                                  |
+----+-------------+----------+-------+---------------+------------+---------+----------------------+--------+----------------------------------------------+

    옵티마이 저는 특히 DISTINCT를 들어, 지금 가지고있는 별명을 선호하는 나타납니다.

  2. ==============================

    2.이것은 너무 오래 코멘트입니다.

    이것은 너무 오래 코멘트입니다.

    나는 HAVING 절에서 식을 처리하는 복잡한 포함하지 않는 정말 어떤 성능에 영향 거기 생각하지 않는다 (예를 들어, 수 (별개의) 또는 긴 문자열에 문자열 처리와 같은 복잡한 기능).

    나는 MySQL은이 쿼리에 두 번 언급 한 두 번 경우 집계 함수를 수행 할 것이라고 거의 확신합니다. 나는 확실히 SQL Server가 두 번째 참조를 멀리 최적화하고 있지 않다 경우,하지만 난하지 추측 것 (SQL Server는 좋은 최적화를 가지고 있지만 그렇게 좋은 일반적인 표현 제거되지 않습니다).

    문제는 다음 식의 복잡성이다. 같은 수가 같은 간단한 표현 ()과 합계 () 정말로이 발생할 많이 추가 오버 헤드 할 - 집계가 이미 수행되고 나면. 복잡한 식 비싼 얻을 시작할 수 있습니다.

    당신은 SQL Server의 복잡한 표현이있는 경우, 당신은 하위 쿼리를 사용하여 한 번만 평가되는 것을 보장 할 수 있어야한다.

  3. ==============================

    3.나는, GROUP BY는, HAVING, SELECT, FROM의 순서, ORDER BY 진행하기 위해 SQL을 기다리고 있었다

    나는, GROUP BY는, HAVING, SELECT, FROM의 순서, ORDER BY 진행하기 위해 SQL을 기다리고 있었다

    나는 MYSQL 전문가가 아니지만, 합법적 인 이유에 MYSQL 문서에서이 밖으로 이유를 발견했다.

    선택 목록이 GROUP BY 절에 이름이 없습니다 집계되지 열을 참조 할 수 있도록 MySQL은 GROUP BY의 표준 SQL의 사용을 확장합니다. 위의 쿼리는 MySQL의 법적 않습니다 의미합니다. 당신은 불필요한 열 정렬을 피하고 그룹화하여 더 나은 성능을 얻기 위해이 기능을 사용할 수 있습니다. 그룹에서의 각각 명명되지 집계되지 열의 모든 값은 각 군에서 동일 주로 때, 유용하다. 서버는이 같은 아니라면, 선택 값이 불확정 있으며, 각 그룹의 모든 값을 자유롭게 선택할 수있다. 또한, 각 그룹의 값의 선택은 ORDER BY 절 첨가에 의해 영향을받을 수 없다. 결과 세트의 정렬 값이 선택 된 후에 발생하고 ORDER BY 각 그룹 내에서 어떤 값을 서버 선택한다면 영향을주지 않습니다.

    비슷한 MySQL의 확장은 HAVING 절에 적용됩니다. 표준 SQL에서 쿼리는 GROUP BY 절에서 명명되지 않은 HAVING 절에 집계되지 열을 참조 할 수 없습니다. 단순화 계산에, MySQL의 확장은 열에 대한 참조를 허용한다. 이 확장은 nongrouped 열이 같은 그룹 현명한 값이 있다고 가정합니다. 그렇지 않으면, 결과는 부정이다.

    성능에 미치는 영향에, 나는 그 가정하고, 별칭을 갖는 것이다 느린 필터가 모든 실행 후 적용해야하기 때문에 갖는 unaliased보다. 나는 의견에 전문가를 기다립니다.

    4. from https://stackoverflow.com/questions/38187913/performance-implications-of-allowing-alias-to-be-used-in-having-clause by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 어떻게 분할 문자열에 문자로 SQL 서버에서 별도의 컬럼에  (0) 2020.07.11
[SQL] 연속 날짜 범위를 결합  (0) 2020.07.11
[SQL] 어떻게 동시 쿼리는 MySQL 데이터베이스에서 처리?  (0) 2020.07.11
[SQL] 무엇 SQL 데이터베이스는 CHECK 제약 조건에 서브 쿼리를 지원합니까?  (0) 2020.07.11
[SQL] GetSchemaTable ()를 사용하여에만 열 이름을 검색하는  (0) 2020.07.11
,

티스토리툴바