+ ----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+----------------------------------+| 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编程开发与优化课》返回搜狐,查看更多