mysql sql索引命中问题

Question

有这样一条sql 查询

SELECT
    a.*,
    b.NAME AS store_name 
FROM
     order a
    LEFT JOIN  store b ON a.store_id = b.id 
WHERE
    a.wid IN (
        100021219, 100021076, 100020963, 100020890, 100020792, 100020773, 100020770,
        100020644, 100020387, 100020513, 100020511, 100020496,
        100020479, 100020467, 100020435, 100020385, 100020421, 100020378,
        100020370, 100020374, 100020375, 100020354, 100020349, 100020376,
        100020376, 100020327, 100020200, 100020198, 100020173 
    ) 
    AND a.channel < 2 AND a.channel > 0 
    AND a.created_at >= '2019-07-02' 
    AND a.created_at < '2019-07-03' 
    AND a.is_pay = 1 
    AND a.type IN ( 2, 3 ) 
ORDER BY
    a.id DESC 
    LIMIT 0,
    20

耗时 和 解析如下

疑问

我尝试将 a.channel < 2 AND a.channel > 0 修改为 a.channel = 1 索引命不中
查询了两个数据表编码都是 utf8
created_at、channel、type、 wid 都建有索引 一点效果都没有
去掉 a.channel < 2 AND a.channel > 0 查询就很快

你期待的结果是什么？

求大神给个解决办法，也尝试过使用 left jion 代替 wid in 的部分 都没有效果

不使用 wid 的方式

SELECT
    a.*,
    b.NAME AS store_name 
FROM
     order a
    LEFT JOIN  store b ON a.store_id = b.id
    LEFT JOIN `tree` c ON a.wid = c.wid 
WHERE
    c.deleted_at IS NULL 
    AND c.first_agent_id = 309 
    AND a.channel < 2 AND a.channel > 0 
    AND a.created_at >= '2019-07-02' 
    AND a.created_at < '2019-07-03' 
    AND a.is_pay = 1 
    AND a.type IN ( 2, 3 ) 
ORDER BY
    a.id DESC 
    LIMIT 0,
    20

Answer 1

调个顺序？

SELECT
    a.*,
    b.NAME AS store_name 
FROM
     order a
    LEFT JOIN  store b ON a.store_id = b.id 
WHERE
    a.channel =1
    AND a.wid IN (
        100021219, 100021076, 100020963, 100020890, 100020792, 100020773, 100020770,
        100020644, 100020387, 100020513, 100020511, 100020496,
        100020479, 100020467, 100020435, 100020385, 100020421, 100020378,
        100020370, 100020374, 100020375, 100020354, 100020349, 100020376,
        100020376, 100020327, 100020200, 100020198, 100020173 
    ) 
    AND a.created_at >= '2019-07-02' 
    AND a.created_at < '2019-07-03' 
    AND a.is_pay = 1 
    AND a.type IN ( 2, 3 ) 
ORDER BY
    a.id DESC 
    LIMIT 0,
    20

Answer 2

你用了组合索引吗？组合索引遵循从左到右原则， 如果不是完整的条件使用，不是最左的是不生效的

Answer 3

老铁，这种问题，一般要结合表结构，表上有哪些索引，以及谓词条件和关联条件的数据分布来分析的；
你说的索引命不中，我都不知道你说的是哪个索引，很难帮你啊。