SQL中索引失效列子及解决方案

一 in 主键 和 非主键特殊查询索引是否失效问题

①索引失效

height 为int（非主键）  使用in 会导致索引失效   还有 ！=  ,<>,not in 导致索引失效

EXPLAIN SELECT id, name ,age FROM vtiger_knowledgess WHERE height IN('122','11');

EXPLAIN SELECT id, name ,age FROM vtiger_knowledgess WHERE height IN(122,11);

EXPLAIN SELECT id, name ,age FROM vtiger_knowledgess WHERE height NOT IN(122,11);

EXPLAIN SELECT id, name ,age FROM vtiger_knowledgess WHERE height !=122;

EXPLAIN SELECT id, name ,age FROM vtiger_knowledgess WHERE height <>122;

② 与上面作对比索引不失效情况

salesorderworkflowstagesid 为int(主键) 使用in 不会会导致索引失效   还有 ！=  ,<>,not in 不会导致索引失效

EXPLAIN SELECT * FROM vtiger_salesorderworkflowstages WHERE salesorderworkflowstagesid IN('9137','9138');

EXPLAIN SELECT * FROM vtiger_salesorderworkflowstages WHERE salesorderworkflowstagesid IN(9137,9138);

EXPLAIN SELECT * FROM vtiger_salesorderworkflowstages WHERE salesorderworkflowstagesid NOT IN(9137,9138);

EXPLAIN SELECT * FROM vtiger_salesorderworkflowstages WHERE salesorderworkflowstagesid !=9137;

EXPLAIN SELECT * FROM vtiger_salesorderworkflowstages WHERE salesorderworkflowstagesid <>9137;

二 varchar 索引失效问题

-- 如果是varchar类型的如果查询数据类型和数据库类型不一致会导致索引失效
EXPLAIN SELECT * FROM `vtiger_salesorderworkflowstages` WHERE `auditorid` = 1 LIMIT 0,10;-- 如果 auditorid 是varchar 则错误
EXPLAIN SELECT * FROM `vtiger_salesorderworkflowstages` WHERE `auditorid` = '1' LIMIT 0,10;-- 如果 auditorid 是varchar 则正确

三 > 或者 < 号导致索引失效问题

-- 如果是包含> 或者 < 的 想解决 其他索引不失效 就把其他条件放到前面
EXPLAIN SELECT * FROM vtiger_salesorderworkflowstages     WHERE     workflowstagesid > 361031 AND smcreatorid=1  ; -- 会导致smcreatorid 索引失效
EXPLAIN SELECT * FROM vtiger_salesorderworkflowstages     WHERE    smcreatorid=1 AND workflowstagesid < 361031 ;-- 正确
EXPLAIN SELECT * FROM vtiger_salesorderworkflowstages     WHERE    smcreatorid=1 AND (workflowstagesid < 361031 OR workflowstagesid > 361031);-- 正确

四  or 索引失效问题

--  or 会导致复合索引失效 解决方式是 or 前的条件建复合索引 or 后的建复合索引 如果id 一样就用in（用in会可能会导致索引失效 下面有讲到嘿嘿）
 EXPLAIN SELECT * FROM vtiger_salesorderworkflowstages     WHERE    smcreatorid=1 or workflowstagesid = 361031;  --  如果是建的复合索引smcreatorid,workflowstagesid 回导致索引失效 分别建一般索引不会失效

五 in 索引失效问题

-- in 里包含一个数据能使用到索引
EXPLAIN  SELECT * FROM vtiger_salesorderworkflowstages WHERE  workflowstagesid IN (361031) ; -- 正确
-- in 里包含两个以上会导致索引失效
EXPLAIN  SELECT * FROM vtiger_salesorderworkflowstages WHERE  workflowstagesid IN (361031,361035); -- 索引失效
-- 强制索引（in） 如果单独强制使用索引会导致其他索引失效
SELECT * FROM vtiger_salesorderworkflowstages  FORCE INDEX(workflowstagesid) WHERE  workflowstagesid IN (361031,361035) AND smcreatorid=1; -- 强制索引workflowstagesid生效 导致smcreatorid 索引失效
-- 强制索引（in） 和 其 and 条件可以建立复合索引  可以解决强制索引导致其他and 索引失效的问题
SELECT * FROM vtiger_salesorderworkflowstages  FORCE INDEX(common) WHERE  (workflowstagesid IN (361031,361035) AND smcreatorid=1); -- 建立复合索引 强制使用索引生效 正确

六  in 和 or  一起使用  索引失效问题解决方案

 -- 有强制索引的复合索引（in） 有or 的情况下索引优化使用 使用union可以解决强制索引 和  or 同时使用导致索引失败的问题
EXPLAIN  SELECT * FROM vtiger_salesorderworkflowstages  FORCE INDEX(common) WHERE  (workflowstagesid IN (361031,361035) AND smcreatorid=1) -- 强制使用索引的复合索引 正确
 UNION
SELECT * FROM vtiger_salesorderworkflowstages  WHERE  sequence=3 ; -- 把 或独立处理union正确

七  like %***% 索引失效解决办法 使用索引覆盖

EXPLAIN SELECT * FROM `vtiger_salesorderworkflowstages`  WHERE `accountname` LIKE '%上海赣帛%';-- 错误

EXPLAIN SELECT accountname FROM `vtiger_salesorderworkflowstages`  WHERE `accountname` LIKE '%上海赣帛%'; -- 正确

八   分组中多个分组 会导致索引失效、

select DATE_FORMAT(FROM_UNIXTIME(reg_time),'%m') show_data, count(*) sum_count, `reg_source` from `member_analysis` where `reg_time` between 1609430400 and 1640966399 and `member_analysis`.`deleted_at` is null group by DATE_FORMAT(FROM_UNIXTIME(reg_time),'%m'),reg_source