MySQL 8.0 倒序索引的应用

最新推荐文章于 2024-06-14 21:22:58 发布

老叶茶馆_

最新推荐文章于 2024-06-14 21:22:58 发布

阅读量1.7k

点赞数

文章标签： mysql weex 数据库编程语言 ai

大家好，我是知数堂SQL 优化班老师网名：骑龟的兔子

已经很久没写文章了，今天在浏览网站的时候，看到有人提了以下问题

为什么下面的SQL 没有用到

index condition pushdown

提问者判断到下面的SQL 中 column4 的代价更高

所以创建了 idx1 ( column1,column2,column4,column3)

这样的索引，他认为应该走icp 但是实际上，没有走

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};


+----+-------------+-------+--------+---------------+---------+---------------------------------------+
| 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

如果想达到提问者的要求 ICP 和 Backward index scan 都得用到

猜测下 Extra里的是不是只能用一种？那我把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 |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+

结果果然用到了 ICP

那么现在的猜测是Extra里不能同时用到一个以上的索引方法

既然这样，我们又有什么方法来解决这个问题？

这里我用到了 MySQL8.0的倒叙索引

所以我进行如下实验

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 |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+

结果发现既用到了ICP 也达到了倒叙的效果

我的新一轮的