这篇具有很好参考价值的文章主要介绍了MySQL中IN的取值范围较大时会导致索引失效。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。
一:分析MySQL In查询为什么所有不生效
结论:IN肯定会走索引,但是当IN的取值范围较大时会导致索引失效,走全表扫描
navicat可视化工具使用explain函数查看sql执行信息
1.1 场景1:当IN中的取值只有一个主键时
我们只需要注意一个最重要的type 的信息很明显的提现是否用到索引:
type结果值从好到坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
all:全表扫描
index:另一种形式的全表扫描,只不过他的扫描方式是按照索引的顺序
range:有范围的索引扫描,相对于index的全表扫描,他有范围限制,因此要优于index
ref: 查找条件列使用了索引而且不为主键和unique。其实,意思就是虽然使用了索引,但该索引列的值并不唯一,有重复。这样即使使用索引快速查找到了第一条数据,仍然不能停止,要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表,因为索引是有序的,即便有重复值,也是在一个非常小的范围内扫描。
const:通常情况下,如果将一个主键放置到where后面作为条件查询,mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化,这个取决于优化器
一般来说,得保证查询至少达到range级别,最好能达到ref,type出现index和all时,表示走的是全表扫描没有走索引,效率低下,这时需要对sql进行调优。
当extra出现Using filesor或Using temproary时,表示无法使用索引,必须尽快做优化。
possible_keys:sql所用到的索引
key:显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL
rows: 显示MySQL认为它执行查询时必须检查的行数。
1.2 场景2:扩大IN中的取值范围
此时仍然走了索引,但是效率降低了
1.3 场景3:继续扩大IN的取值范围
看上面的图,发现此时已经没有走索引了,而是全表扫描。
在说一下结论
结论:IN肯定会走索引,但是当IN的取值范围较大时会导致索引失效,走全表扫描。
By the way:如果使用了 not in,则不走索引。
二:MySQL 需要 IN查询但是很慢怎么办 ?
从上文得知我们的IN查询索引不生效,以及不生效的原因。文章来源:https://www.toymoban.com/news/detail-618560.html
2.1 这是一个常用的IN查询文章来源地址https://www.toymoban.com/news/detail-618560.html
SELECT id, order_index, data_order_start, update_time, create_time, gov_frame_id
FROM gov_price_category_detail
WHERE
gov_frame_id IN (
SELECT id FROM gov_price_frame WHERE deleted=1 AND is_spider=0 AND city IN ( '长沙市' ) GROUP BY id
)
AND deleted=1
AND data_order_start < 51
2.2 我们把IN查询 改造成 inner 查询
SELECT gcd.id, gcd.order_index, gcd.data_order_start, gcd.update_time, gcd.create_time, gcd.gov_frame_id
FROM gov_price_category_detail gcd , ( SELECT gp.id FROM gov_price_frame gp WHERE gp.deleted=1 AND gp.is_spider=0 AND gp.city IN ( '长沙市' ) GROUP BY gp.id ) gpf
WHERE
gpf.id = gcd.gov_frame_id
AND gcd.deleted=1
AND gcd.data_order_start < 51
3.3 优化后速度对比
原始SQL速度信息:
优化后SQL速度信息:
一:分析MySQL In查询为什么所有不生效
结论:IN肯定会走索引,但是当IN的取值范围较大时会导致索引失效,走全表扫描
navicat可视化工具使用explain函数查看sql执行信息
1.1 场景1:当IN中的取值只有一个主键时
我们只需要注意一个最重要的type 的信息很明显的提现是否用到索引:
type结果值从好到坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
all:全表扫描
index:另一种形式的全表扫描,只不过他的扫描方式是按照索引的顺序
range:有范围的索引扫描,相对于index的全表扫描,他有范围限制,因此要优于index
ref: 查找条件列使用了索引而且不为主键和unique。其实,意思就是虽然使用了索引,但该索引列的值并不唯一,有重复。这样即使使用索引快速查找到了第一条数据,仍然不能停止,要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表,因为索引是有序的,即便有重复值,也是在一个非常小的范围内扫描。
const:通常情况下,如果将一个主键放置到where后面作为条件查询,mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化,这个取决于优化器
一般来说,得保证查询至少达到range级别,最好能达到ref,type出现index和all时,表示走的是全表扫描没有走索引,效率低下,这时需要对sql进行调优。
当extra出现Using filesor或Using temproary时,表示无法使用索引,必须尽快做优化。
possible_keys:sql所用到的索引
key:显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL
rows: 显示MySQL认为它执行查询时必须检查的行数。
1.2 场景2:扩大IN中的取值范围
此时仍然走了索引,但是效率降低了
1.3 场景3:继续扩大IN的取值范围
看上面的图,发现此时已经没有走索引了,而是全表扫描。
在说一下结论
结论:IN肯定会走索引,但是当IN的取值范围较大时会导致索引失效,走全表扫描。
By the way:如果使用了 not in,则不走索引。
二:MySQL 需要 IN查询但是很慢怎么办 ?
从上文得知我们的IN查询索引不生效,以及不生效的原因。
2.1 这是一个常用的IN查询
到了这里,关于MySQL中IN的取值范围较大时会导致索引失效的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!