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

+ ----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+----------------------------------+| 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| Usingwhere; Backward index scan |+ ----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+----------------------------------+

可以看到 用到了 Backward index scan

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

猜测下 Extra里的 是不是只能用一种 ？那我把desc 去掉怎样呢 ？

select*from t12where emp_no = 10001andsalary 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的倒叙索引

所以我进行如下实验

createindexix_t2 ont12(emp_no,from_date desc,salary);select*fromt12 forceindex(ix_t2)whereemp_no = 10001andsalary in( 30000, 4000)orderbyfrom_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编程开发与优化

课程即将本周五开课~~

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

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

欢迎加入 知数堂大家庭。

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

点击下方小程序加入松华老师《深入SQL编程开发与优化课》返回搜狐，查看更多