다른 열에서 WHERE를 선택하는 테이블에 대한 최상의 인덱싱 기법

다른 열에서 WHERE를 선택하는 테이블에 대한 최상의 인덱싱 기법

다음과 같은 로그 테이블의 효율적인 인덱싱 기술을 찾고 있습니다.

MariaDB [Webapp]> explain logs;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id        | int(11)      | YES  | MUL | NULL    |                |
| activity_name  | varchar(20)  | NO   |     | NULL    |                |
| activity_key   | varchar(255) | NO   |     | NULL    |                |
| activity_value | varchar(255) | NO   |     | NULL    |                |
| activity_date  | datetime     | NO   | MUL | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

---

다음과 같이 검색합니다.

SELECT *
FROM logs
WHERE user_id IN (1, 3)
  AND activity_name IN ('login', 'logout')
  AND activity_date >= '2020-02-01'
  AND activity_date <= '2020-06-01'

열 위치user_id,activity_name그리고.activity_date관련된

---

그리고 가끔은 이런 식입니다.

SELECT *
FROM logs
WHERE user_id IN (1, 3)
  AND activity_name IN ('login', 'logout')

둘 다 있는 곳user_id그리고.activity_name관련은 있지만 날짜는 없습니다.

---

And like this too:

SELECT *
FROM logs
WHERE user_id IN (1, 3)
  AND activity_date >= '2020-02-01'
  AND activity_date <= '2020-06-01'

---

SELECT *
FROM logs
WHERE activity_name IN ('login', 'logout')
  AND activity_date >= '2020-02-01'
  AND activity_date <= '2020-06-01'

복합 인덱스에 대해 읽었고 내 검색이 주문되면 좋을 것이라고 했지만, 보시다시피 적합하지 않아요.

그리고 단일 인덱스는 한 번에 하나의 열에만 사용할 수 있다는 것도 읽었는데, 제 경우에는 좋지 않을 것 같습니다.

어떤 아이디어든 부탁드립니다, 저는 MySQL에 익숙하지 않습니다.어떻게 하면 쿼리를 최적화할 수 있습니까?

참고: 와일드카드(*)는 읽는 속도가 느려지기 때문에 사용하지 않지만 더 쉽게 이해하기 위해 쿼리를 단축하기 위해 입력했습니다.

각 쿼리에 대해 기본 아이디어는 다음을 포함하는 열을 가진 인덱스를 갖는 것입니다.where절네 개의 쿼리에 대해 단일 인덱스를 사용하여 이 작업을 수행할 수 없습니다. 세 개의 인덱스가 필요하다고 생각합니다.

먼저 다음 인덱스를 고려합니다.

logs(user_id, activity_name, activity_date)

에 일치합니다.where첫 번째 쿼리의 절:

WHERE 
    user_id IN (1, 3) 
    AND activity_name IN ('login', 'logout') 
    AND activity_date >= '2020-02-01' 
    AND activity_date <= '2020-06-01'

또한 두 번째 쿼리(여기서는 세 번째 인덱스 열은 무시됨)에 대해서도 다음을 수행합니다.

WHERE 
    user_id IN (1, 3) 
    AND activity_name IN ('login', 'logout') 

다른 두 쿼리의 경우 두 개의 별도 인덱스가 필요합니다.

WHERE 
    user_id IN (1, 3) 
    AND activity_date >= '2020-02-01' 
    AND activity_date <= '2020-06-01'

요구 사항:

logs(user_id, activity_date)

그리고:

WHERE 
    activity_name IN ('login', 'logout') 
    AND activity_date >= '2020-02-01' 
    AND activity_date <= '2020-06-01'

요구 사항:

logs(activity_name, activity_date)

참고: 일반적으로 맹목적으로 하지 마십시오.select *대신 결과 집합에 원하는 열을 열거합니다. 특히 모든 열을 원하지 않는 경우에는 더욱 그렇습니다.두 개 또는 세 개의 열만 필요한 경우 인덱스 끝에 열을 추가하여 피복 인덱스로 전환하는 것이 좋습니다.

언급URL : https://stackoverflow.com/questions/63442478/best-indexing-technique-for-a-table-that-does-select-where-on-different-columns