优享资讯 | 遇到索引自认为应该使用时未被使用的问题

掘金后端 ( ) • 2024-03-04 09:52

产品提了个需求，需要在后台展示不同等级的用户情况，功能本身很简单，但是在功能上线前跑一段 sql 先进行打标签时，在测试环境用 explain 语句，发现这段 sql 并没有用到我认为会使用的索引。

这段 sql 是这样的：

insert into  new_table(user_id,flag) 
select user_id,1 as flag from  table where coins >= xxx and recharge_flag = xxx

所用的 explain 语句：

explain select user_id,1 as flag from  table where coins >= xxx and recharge_flag = xxx

已经在 coins 上建立了索引，而且 recharge_flag 是 0 或者 1 的值，标识度很低，这个问题让我挺疑惑的，然后我尝试在查询语句的后面加了 limit 100，发现这时候就走到了 index_coins 索引了。

我突然意识到是不是 coins 的标识度也是挺低的，于是用了以下语句进行查询：

SELECT count(DISTINCT(coins))/count(*) AS Selectivity FROM table ;

发现 Selectivity 的值才只有 0.0034。这实在让我没想到。那不走 coins 索引的原因已经找到了，那为什么加了 limit 100 后，就走了 coins 索引呢。

翻看了一下 mysql 8 的文档，发现 explain 还有一个更加详细的语句，即 explain annalyze。

分别查看了一下不加 limit 和加上 limit 后的输出。

不加 limit：

-> Limit: 100 row(s)  (cost=144673 rows=100) (actual time=0.0284..0.312 rows=100 loops=1)
    -> Filter: (table.recharge_flag= 0)  (cost=144673 rows=32150) (actual time=0.0278..0.308 rows=100 loops=1)
        -> Index range scan on table using index_coins over (xxxx <= coins), with index condition: (index_coins.coins>= xxx)  (cost=144673 rows=321495) (actual time=0.0184..0.299 rows=146 loops=1)

加上 limit：

-> Filter: ((table.recharge_flag = xxx) and (table.coins>= xxx))  (cost=65861 rows=32150) (actual time=0.0665..497 rows=11874 loops=1)
    -> Table scan on table (cost=65861 rows=642990) (actual time=0.0315..469 rows=649083 loops=1)

首先用我的理解讲一下它们之间分别的含义。

不加 limit:

首先进行了索引范围查询，使用了 index_coins 索引。
然后对数据进行了过滤，过滤的条件是 where 里面的第二个条件，即 ( table.recharge_flag= 0 )。
最后，将满足过滤的条件进行限制操作，只返回前 100 行。

加 limit

首先进行了全表扫表。
然后进行过滤操作，过滤条件是 ( table.recharge_flag = xxx ) and ( table.coins>= xxx )，预计会有 32150 条满足条件。

这时候就清晰了知道原因了，当不加 limit 条件时，index_coins 索引因为 coins 的标识度太低了，所以优化器并没有选择走 index_coins 索引，当加上 limit 条件时，由于只需要返回前 100 条，走了 index_coins 查出满足条件的行即可，然后再进行第二个 where 条件的过滤操作。

这让我收获了下次加索引时，可以先查看下这个索引的标识度多少，即前面的 SELECT count(DISTINCT(coins))/count(*) AS Selectivity FROM table 的语句，比如当我们需要频繁地按照员工的姓名去搜索员工，我们可以先用这个语句，查看一下 first_name 或 fist_name, last_name 的索引选择性，选一个标识度高的作为索引，另外如果我们所有用到前缀索引时，比如一个字段的值太长了，我们可以用这个语句查看这个字段的前几位标识度高的作为索引。