mysql 反转索引_MySQL 8.0 倒序索引的应用

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

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

为什么 下面的SQL 没有用到

index condition pushdown

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

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

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

SELECTt2.column1 ,t3.column1FROM tb_test1 t1INNER JOIN tb_test2 t2 ON t2.column1 = t1.column1INNER JOIN tb_test3 ON t3.column1 = t1.column1WHEREt1.coulmn1 = #{idNo}AND t1.column2 = 'N'AND t1.column3 in ( 'A','B','C')ORDER BY t1.column4 DESCLIMIT #{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)selectemp_no,salary,from_date,to_datefrom salaries limit 10000;create index ix_t1 on t12(emp_no,from_date,salary);

然后运行了如下SQL

结果跟提问者一样的结果select*from t12where emp_no = 10001and 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 t12where emp_no = 10001and 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 = 10001and 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编程开发与优化课》