中国开源软件网

当前位置: 首页 > 创业投资 >

MySQL,8.0,MySQL8.0的倒叙索引

时间:2020-08-12 14:45来源:互联网 作者:小狐

+ ----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+----------------------------------+ 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优化

欢迎加入 知数堂大家庭。

网友评论

相关文章