이 SQL 쿼리를 최적화할 수 있습니까?

이 SQL 쿼리를 최적화할 수 있습니까?

MariaDB(MySQL)에서 Postfix 테이블 조회(smtpd_sender_login_maps)에 대한 쿼리입니다.전자 메일 주소를 지정하면 해당 주소를 사용할 수 있는 사용자가 반환됩니다.검색해야 할 계정과 별칭을 저장하기 위해 두 개의 SQL 테이블을 사용하고 있습니다.사후 수정에는 단일 결과 집합을 반환하는 단일 쿼리가 필요하므로UNION SELECT.난 있는 걸 안다.unionmap:{}포스트픽스에서 하지만 저는 그 경로를 가고 싶지 않고 연합 선택을 선호합니다.그emails.email열은 Postfix SASL 인증에 대해 반환되는 사용자 이름입니다.그%s쿼리에서 Postfix가 검색할 전자 메일 주소를 삽입합니다.모든 것을 다시 일치시키는 이유는emails.postfixPath이는 실제 받은 편지함이기 때문입니다. 두 계정이 동일한 받은 편지함을 공유하는 경우 두 계정 모두 별칭을 포함하여 동일한 모든 전자 메일을 사용할 수 있는 액세스 권한을 참조하십시오.

Table: emails
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| email       | varchar(100) | NO   | PRI | NULL    |       |
| postfixPath | varchar(100) | NO   | MUL | NULL    |       |
| password    | varchar(50)  | YES  |     | NULL    |       |
| acceptMail  | tinyint(1)   | NO   |     | 1       |       |
| allowLogin  | tinyint(1)   | NO   |     | 1       |       |
| mgrLogin    | tinyint(1)   | NO   |     | 0       |       |
+-------------+--------------+------+-----+---------+-------+

.

Table: aliases
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| email      | varchar(100) | NO   | PRI | NULL    |       |
| forwardTo  | varchar(100) | NO   |     | NULL    |       |
| acceptMail | tinyint(1)   | NO   |     | 1       |       |
+------------+--------------+------+-----+---------+-------+

.

SELECT email
    FROM emails
    WHERE postfixPath=(
        SELECT postfixPath
            FROM emails
            WHERE email='%s'
            AND acceptMail=1
            LIMIT 1)
    AND password IS NOT NULL
    AND allowLogin=1
UNION SELECT email
    FROM emails
    WHERE postfixPath=(
        SELECT postfixPath
            FROM emails
            WHERE email=(
                SELECT forwardTo 
                    FROM aliases 
                    WHERE email='%s' 
                    AND acceptMail=1)
            LIMIT 1)
    AND password IS NOT NULL
    AND allowLogin=1
    AND acceptMail=1

이 쿼리는 작동합니다. 제가 보기에는 무겁고 효율적이어야 한다고 생각합니다.이 글을 쓸 수 있는 더 좋은 방법이 있는 사람이 있습니까? 아니면 이 글이 그렇게 좋은 것입니까?

• 추가했습니다.CREATE INDEX index_postfixPath ON emails (postfixPath)@임팔러의 제안에 따라.

@여기 Rick James가 추가 표 정보를 제공합니다.

Table: emails
Create Table: CREATE TABLE `emails` (
`email` varchar(100) NOT NULL,
`postfixPath` varchar(100) NOT NULL,
`password` varchar(50) DEFAULT NULL,
`acceptMail` tinyint(1) NOT NULL DEFAULT 1,
`allowLogin` tinyint(1) NOT NULL DEFAULT 1,
`mgrLogin` tinyint(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`email`),
KEY `index_postfixPath` (`postfixPath`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Table: aliases
Create Table: CREATE TABLE `aliases` (
`email` varchar(100) NOT NULL,
`forwardTo` varchar(100) NOT NULL,
`acceptMail` tinyint(1) NOT NULL DEFAULT 1,
PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

.

1부:

SELECT  email
    FROM  emails
    WHERE  postfixPath=
    (
        SELECT  postfixPath
            FROM  emails
            WHERE  email='%s'
              AND  acceptMail = 1
            LIMIT  1
    )
      AND  password IS NOT NULL
      AND  allowLogin = 1

인덱스 포함:

emails: (email, acceptMail, password)

아마acceptMail값이 2개뿐입니까?옵티마이저는 그것을 알 수 없으므로, 그것은 그것을 봅니다.AND acceptMail범위 테스트로. AND acceptMail = 1고칩니다. (아닙니다.> 0,!= 0등을 최적화할 수 없습니다.)

2부:

여기에는 3개의 계층이 있으며, 여기서 비효율성이 발생할 수 있습니다.

SELECT e.email
    FROM ( SELECT forwardTo ... ) AS c
    JOIN ( SELECT postfixPath ... ) AS d ON ...
    JOIN emails AS e  ON e.postfixPath = d.postfixPath

이렇게 하면 Optimizer가 버전을 최적화할 수 있습니다.하지만 저는 그것이 그렇게 되었는지 확신할 수 없어서 그렇게 하도록 격려하기 위해 그것을 바꿨습니다.

다시, 사용=1"참"을 테스트할 때.그런 다음 다음 인덱스를 사용합니다.

aliases:  (email, acceptMail, forwardTo)
emails:   (email, postfixPath)
emails:   (postfixPath, allowLogin, acceptMail, password, email)

마지막으로,UNION:

( SELECT ... part 1 ... )
UNION ALL 
( SELECT ... part 2 ... )

나는 선택 대 연합에 속하는 조항에 대한 모호성을 피하기 위해 괄호를 추가했습니다.

UNION ALL보다 빠름UNION(즉,UNION DISTINCT), 하지만 같은 이메일을 두 번 받을 수도 있습니다.하지만, 그것은 말도 안 되는 소리일 수도 있습니다. 자신에게 이메일을 전달하는 것입니까?

각 인덱스의 열 순서는 중요합니다.(그러나 일부 변형 모델은 동일합니다.)

제가 제공한 모든 인덱스가 "적용"되어 추가적인 성능 향상을 제공한다고 생각합니다.

이용해주세요SHOW CREATE TABLE그것은 보다 더 묘사적입니다.DESCRIBE"MUL"은 특히 모호합니다.

(동굴:저는 다소 성급하게 이 코드를 조합했습니다. 그것이 정확하지 않을 수도 있지만, 원칙이 도움이 될 것입니다.)

추가 최적화를 위해 제가 3단계로 나누었던 것처럼 부탁드립니다.각각의 성능을 확인합니다.

다음 세 가지 인덱스를 사용하면 쿼리 속도가 빨라집니다.

create index ix1 on emails (allowLogin, postfixPath, acceptMail, password, email);

create index ix2 on emails (email, acceptMail);

create index ix3 on aliases (email, acceptMail);

언급URL : https://stackoverflow.com/questions/66461970/can-this-sql-query-be-optimized