面试官请不要再问我mysql联合索引失效、有效情况了

- 日理万妓 2023-06-10 04:27 2阅读 0赞

## 版本：5.5.18.1 ##

![在这里插入图片描述][20191025173848908.png]

## 联合索引建立情况： ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70]

## 查询条件顺序和联合索引一样 ##

EXPLAIN SELECT * FROM t_cmp_mission WHERE companyID = "" AND isFinish ="" AND missionType ="" AND creTm = "";

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 1]

## 查询条件顺序和联合索引顺序完全相反 ##

EXPLAIN SELECT * FROM t_cmp_mission WHERE creTm = "" AND missionType ="" AND isFinish =""  AND companyID = "" ;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 2]

## 查询条件中不包含索引的第一个 ##

EXPLAIN SELECT * FROM t_cmp_mission WHERE isFinish ="4" and missionType ="4" AND creTm ="";

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 3]

## 查询条件只有联合索引的第一个索引 ##

EXPLAIN SELECT * FROM t_cmp_mission WHERE companyID = "44";

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 4]

## 只有联合索引的非第一个索引 ##

EXPLAIN SELECT * FROM t_cmp_mission WHERE isFinish = "44";

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 5]

## 非第一个索引列like ##

EXPLAIN SELECT * FROM t_cmp_mission WHERE companyID = "44" AND isFinish ="4" and missionType ="4" and creTm LIKE '%000%';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 6]

## 第一个索引列like 且双边模糊匹配 ##

EXPLAIN SELECT * FROM t_cmp_mission WHERE companyID LIKE '%000%' AND isFinish ="4" and missionType ="4" and creTm ="";

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 7]

## 非第一个索引列like 且双边模糊匹配 ##

EXPLAIN SELECT * FROM t_cmp_mission WHERE  companyID ="" AND isFinish LIKE '%aa%' AND missionType ="4" and creTm ="";

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 8]

## 第一个索引列like 且右边模糊匹配 ##

EXPLAIN SELECT * FROM t_cmp_mission WHERE companyID LIKE '000%' AND isFinish ="4" and missionType ="4" and creTm ="";

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 9]

## 第一个索引列like 且左边模糊匹配 ##

EXPLAIN SELECT * FROM t_cmp_mission WHERE companyID LIKE '%000' AND isFinish ="4" and missionType ="4" and creTm ="";

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 10]

## 查询条件中包含 or ##

EXPLAIN SELECT * FROM t_cmp_mission WHERE companyID ="someID" OR isFinish ="4" and missionType ="4" and creTm ="";

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 11]

## 联合索引遇上 where 1=1 ##

EXPLAIN SELECT * FROM t_cmp_mission WHERE 1=1 AND companyID ="someID" AND isFinish ="4" and missionType ="4" and creTm ="";

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 12]

## 总结 ##

1.  联合索引所有成员均以and 出现时与顺序无关，都能生效
2.  条件语句中出现or 则联合索引无效
3.  条件语句中缺少排在第一位的索引字段，整个联合索引失效
4.  欲使联合索引要生效，排在第一位的索引为必须出现，位置不限
5.  使用like 不一定会导致索引失效，只有like 使用在**第一位的索引为且左边做了模糊匹配**才会使索引失效
6.  或许是因为mysql内部做了优化，where 1=1并不影响索引的使用，不会扫描全表

[20191025173848908.png]: /images/20230531/c734aa210391441b89569411c42624f9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70]: /images/20230531/8afe11f5882444e688a203a6e97e84c1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 1]: /images/20230531/93ba188c11db40a69c74f87fe20c43e9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 2]: /images/20230531/93e691a5cc85480bbd72e27a8c498ba6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 3]: /images/20230531/65d16fe5ede9465f9fb4edaaa68b04df.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 4]: /images/20230531/defae3a353d74c639e740f96a067d850.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 5]: /images/20230531/2a03099d2e704b429197b34e7fe89bb5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 6]: /images/20230531/715e4cb0bd3e4707b4f14fdf57a45728.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 7]: /images/20230531/fc20ba58c0b54da6be71f48a48ec160a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 8]: /images/20230531/63c77ce1e5d34c699c3b83bf14afee5c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 9]: /images/20230531/ecba5d89b6444dafa5ab61c1418a53bb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 10]: /images/20230531/ca998ec7fe1a4154b6795aada6d002f9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 11]: /images/20230531/51965840125a4f44865377db6e4097fa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaXN1cmVfbGlmZQ_size_16_color_FFFFFF_t_70 12]: /images/20230531/3e2de31c49a24631a84e9417d9a29dfb.png