MySQL 8.0 倒序索引的应用

た入场券 2022-11-26 10:56 145阅读 0赞

大家好，我是知数堂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 也达到了倒叙的效果

我的新一轮的

# 深入SQL编程开发与优化 #

https://ke.qq.com/course/1346083?saleToken=2150272&from=pclink

课程即将本周五开课~~

我是知数堂SQL 优化班老师~ ^^

如有关于SQL优化方面疑问和一起交流的请加 并且 @兔子@知数堂SQL优化

高性能MySQL,SQL优化群 有叶金荣，吴炳锡 两位大神坐镇 ：579036588

欢迎加入 知数堂大家庭。

我的微信公众号：SQL开发与优化(sqlturning)

点击下方小程序加入松华老师《深入SQL编程开发与优化课》