作者回复: 因为order_id索引不能覆盖我们要查询的信息,所以在对order_id查询之后还需要一次回表来查找到整行数据,虽然order_id索引是顺序存放的,但是相对于主键id存放的数据顺序是不一致的,所以存在每次回表都是随机获取整行数据,如果在获取大量数据时,通过这种方式获取数据性能肯定是不理想的。
所以mysql一般判断在查询超过整个表20%的数据时,就会考虑使用聚族索引来查找数据,这种方式顺序读取数据的可能性要大于使用辅助索引的随机读。
在查询少量数据的情况下,使用辅助索引性能更加,而查询大量数据时,就未必了。
如果我们发现在查询一定量数据使用辅助索引要比主键索引快,而数据库又没有按照我们期望的去使用辅助索引,则我们可以通过子查询或force index来强制使用辅助索引。
作者回复: 对的,点赞补充
作者回复: 回表就相当于两次索引树扫描操作了,而主键查询只有一次。
作者回复: 区别在于聚簇索引存储了其他数据,而辅助索引只保存了索引列和主键,所以通过查询辅助索引统计行检索的数据量会更少,I/O操作会更少
作者回复: 这个跟具体场景有关系,在数据量非常大的情况下,可能使用辅助索引会效率更高些。
作者回复: 分析到位,答案正确。
作者回复: 👍
作者回复: mysql查询优化是基于检索成本考虑,而不是基于时间成本考虑,假设我们读取的是随机行的数据,在磁盘中存储是无序的,有可能在扫描少数行的情况下,所需时间更长,这种情况下会出现误选择索引。
通常在一些in操作时,在数据量比较小的情况下,会使用我们建立的索引,当数据量超过一定量时,会改走主键索引。我们一般是通过慢日志来排查这些问题,一旦发现不是走的我们想要的索引,可以使用force index来强制走期望的索引。
作者回复: 我们这里讨论的是索引失效
作者回复: 如果与id索引的排序是一致的,会走索引,可以动手实践一下
作者回复: 官网给出的是不支持自创建hash数据结构的索引,但是它是自适应的,也就是我们不能人为的干预使用hash索引。具体的可以参考官网:https://dev.mysql.com/doc/refman/8.0/en/create-index.html
作者回复: 多熟悉一门数据库也是好的,知己知彼
作者回复: 思路是对的,这种方式可以解决。
作者回复: 对的
作者回复: 正解!