MySQL8.0 desc Index 사용사례

안녕하세요 어늘 웹서칭하다가 카페에서 아래와 같은 내용을 읽게되였습니다

 

SELECT
t2.column1 ,
t3.column1
FROM tb_test1 t1
INNER JOIN tb_test2 t2 ON t2.column1 = t1.column1
INNER JOIN tb_test3 ON t3.column1 = t1.column1
WHERE
  t1.coulmn1 = #{idNo}
  AND t1.column2 = 'N'
  AND t1.column3 in ( 'A','B','C')
ORDER BY t1.column4 DESC
LIMIT #{offset}, #{limit};

여기서 idx1 ( column1,column2,column4,column3)  아래 인덱스를 생성하고 

언하는것은 index condition pushdown 입니다 

 

근데 결과는 

+----+-------------+-------+--------+---------------+---------+---------------------------------------+
| id | select_type | table | type   | key           | key_len | Extra                                 |
+----+-------------+-------+--------+---------------+---------+---------------------------------------+
|  1 | SIMPLE      | t1    | range  | idx1_tb_test1 | 406     | Using where; Backward index scan      |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | 8       | NULL                                  |
|  1 | SIMPLE      | t3    | eq_ref | PRIMARY       | 8       | NULL                                  |
+----+-------------+-------+--------+---------------+---------+---------------------------------------+

원하는결과는 아니였습니다  

 

저도 여기까지 읽고 궁금쯩이 생겨서  테스트를 진행했습니다.

 

create table t12 ( 
id bigint not null auto_increment  primary key ,
emp_no int ,
salary int ,
from_date date ,
to_date date 
)

insert into t12 (
emp_no   
,salary   
,from_date
,to_date  
)
select 
emp_no   
,salary   
,from_date
,to_date
from salaries limit 10000;


create index ix_t1 on t12(emp_no,from_date,salary);

 

SQL 실행결과 

select 
*
from t12 
where emp_no = 10001
and salary in (30000,4000)
order by from_date desc 

+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra                            |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+----------------------------------+
|  1 | SIMPLE      | t12   | NULL       | ref  | ix_t1         | ix_t1 | 5       | const |    1 |    20.00 | Using where; Backward index scan |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+----------------------------------+

여기서 보시다 싶이 Backward index scan 였습니다 

여기서 질문 혹시 MySQL 은 인덱스 사용관련 ICP 혹은 Backward index scan 하나만 사용가능? 

혹시 여기서 desc 를 삭제하면 어떻게 될까  ?

select 
*
from t12 
where emp_no = 10001
and salary in (30000,4000)
order by from_date  
;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t12   | NULL       | ref  | ix_t1         | ix_t1 | 5       | const |    1 |    20.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+

역시 제생각과 동일한 결과값이 추출되네요  

그럼 여기서 또하나 문제는 발견했는데 해결책은  ?

MySQL8.0 부터 지원되는 DESC Index 

create index ix_t2 on t12(emp_no,from_date desc ,salary);
select 
*
from t12 force index(ix_t2)
where emp_no = 10001
and salary in (30000,4000)
order by from_date desc 
;

+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t12   | NULL       | ref  | ix_t2         | ix_t2 | 5       | const |    1 |    20.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+

 

ㅋㅋ 문제해결~~~~