MySQL时间字段(Timestamp)不走索引的问题解析与对策

小插曲:同事说,建立了索引的时间戳字段,竟然不走索引,而且经过各种尝试,发现某些情况下有效,有些情况下无效!太神奇,为什么索引还能这样?

结论

因为MySQL优化器认为检索条件不及全表扫描更高效,所以他会选择全表扫描

应对方法:

  • 推荐:优化字段,保证索引字段重复率低,最好是唯一索引
  • 增加FORCE INDEX (create_time)
  • 执行分析表SQL(ANALYZE TABLE),更新索引状态
  • 调整系统优化敏感参数:max_seeks_for_key

原理分析:

由于MySQL具有索引优化分析能力,不同情况下,索引可能生效,也可能不生效,具体原因如下(参考):

  • 表太小,执行全表扫描更快,比如10条一下记录的表
  • 在ON或WHERE条件中,已索引列中没有可用匹配
  • 在比较已索引列和全表扫描时,优化器认为全表扫描更快
  • 使用的键比其他列具备更低的候选性,不及执行全表扫描更快

MySQL优化器是如何认为全表扫描更快的?

索引的本质

索引是通过B+树实现,通过索引检索记录,如果不是索引全覆盖,那么MySQL还需要评估是否值得采用索引。索引执行的是数据库行存储位置,如果MySQL优化器认为,把索引查询一遍,再到数据库活动记录,还不如全表扫描(全表扫描时顺序读,而索引是随机读

说明:取出的字段不能直接返回,假设字段是create_time,那么select count(distinct(create_time)) from order 就是索引全覆盖,这样的SQL一定会采用索引的,另一种,比如select * from order where create_time > ‘2022-5-15’,这种就查完索引后,还需要查表。

所以MySQL优化会考虑是否需要采用全表查询还是索引查询。

怎么任务全表扫描更快呢?

max_seeks_for_key这个参数定义了在什么情况下采用索引,具体怎么计算,我暂时没有找到文档和源代码。
基于现有文档:
SHOW INDEX中的Cardinality字段表名这个说明的候选节点分布,不是严格的准确,该值越大,越有机会使用索引。

经过实际测试发现修改参数效果不明显,也发现其他兄弟尝试效果也不行。

如果明确需要使用索引,就直接告诉MySQL引擎吧

FORCE INDEX (gmt_create)
语法参考

索引唯一性好处

索引的效果好不好,通弄SHOW INDEX,查看Cardinality就可以看出,如果和记录数量相差太远,那么这个索引就不是一个好索引,需要考虑是不是新增字段,把字段进行拼合,构建新索引效果更好。

例如把时间戳+特定编号等,构建符合的数值字段(不是字符串,数值效率更好),作为主键,或建立索引,必然可以获得较好效果。(之前在数千万级别表值就是这么设计的)

  • 2
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值