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.column1WHERE t1.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 )select emp_no ,salary ,from_date,to_datefrom salaries limit 10000;create index ix_t1 on t12(emp_no,from_date,salary);然后运行了如下SQL
结果跟提问者一样的结果
select *from t12 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_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 = 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/1346083saleToken=2150272&from=pclink
课程即将本周五开课~~
我是知数堂SQL 优化班老师~ ^^
如有关于SQL优化方面疑问和一起交流的请加 并且 @兔子@知数堂SQL优化
高性能MySQL,SQL优化群 有叶金荣，吴炳锡 两位大神坐镇 ：579036588
欢迎加入 知数堂大家庭。
我的微信公众号：SQL开发与优化(sqlturning)
点击下方小程序加入松华老师《深入SQL编程开发与优化课》