admin 管理员组文章数量: 1088139
2024年3月19日发(作者：成套个人简历模板免费下载)
MySQL联合索引失效原理详解
1. 索引的基本概念
索引是数据库中用于提高查询效率的一种数据结构。它类似于书籍的目录，可以通
过索引快速定位到特定数据，避免了全表扫描的开销，提高了查询性能。
在MySQL中，常见的索引类型包括B-Tree索引、哈希索引、全文索引等。其中，
B-Tree索引是最常用的一种。
2. 联合索引
联合索引（Composite Index）是指将多个列组合在一起创建的一个索引。它可以
在多个列上同时建立索引，从而提供更加灵活和高效的查询方式。
例如，我们有一个用户表（user），包含id、name和age三个字段。如果我们需
要根据name和age进行查询，那么可以创建一个联合索引(name, age)，这样可以
加快该条件下的查询速度。
3. 联合索引失效原理
虽然联合索引可以提高查询性能，但在某些情况下可能会失效。下面我们将详细介
绍几种导致联合索引失效的原因，并解释其基本原理。
3.1 不满足最左前缀原则
最左前缀原则是指，在使用联合索引进行查询时，必须从联合索引的最左边的列开
始查询。也就是说，如果一个联合索引是由(name, age)组成，那么查询条件中必
须包含name这个列。
例如，我们有一个联合索引(name, age)，如果查询条件只有age，那么该联合索
引将不会被使用。因为在B-Tree索引中，数据是按照索引的顺序进行存储的，如
果查询条件没有包含最左边的列，那么就无法通过索引快速定位到特定数据。
3.2 范围查询后面的列无法使用索引
当我们在联合索引上进行范围查询时，例如使用
BETWEEN
、
>
、
<
等操作符时，后面
的列将无法使用索引。
例如，我们有一个联合索引(name, age)，如果查询条件是
name='Alice' AND age >
20
，那么该联合索引将无法被使用。因为age是在范围查询之后才进行判断的，在
B-Tree索引中需要连续地比较每个节点才能确定具体位置。
3.3 使用了不等于操作符
当我们在联合索引上使用不等于操作符（例如
<>
）时，该联合索引将无法被使用。
例如，我们有一个联合索引(name, age)，如果查询条件是
name='Alice' AND age <>
20
，那么该联合索引将无法被使用。因为在B-Tree索引中，不等于操作符需要对
每个节点进行比较，无法通过索引快速定位到特定数据。
3.4 使用函数或表达式进行查询
当我们在联合索引上使用函数或表达式进行查询时，该联合索引将无法被使用。
例如，我们有一个联合索引(name, age)，如果查询条件是
SUBSTR(name, 1,
3)='Ali'
，那么该联合索引将无法被使用。因为函数SUBSTR不是简单的列名，而
是对列进行了处理，在B-Tree索引中无法通过索引快速定位到特定数据。
3.5 字符串类型的列使用了前缀索引
当我们在联合索引中的字符串类型的列上使用了前缀索引时，该联合索引可能会失
效。
例如，我们有一个联合索引(name(10), age)，如果查询条件是
name='Alice'
，那
么该联合索引将会被使用。但如果查询条件是
name='Alic'
，由于前缀长度不满足
要求，该联合索引将无法被使用。
4. 如何避免联合索引失效
虽然存在一些情况下会导致联合索引失效，但我们可以采取一些措施来避免这种情
况发生。
4.1 创建合适的联合索引
在创建联合索引时，需要根据实际的查询需求来选择合适的列进行组合。遵循最左
前缀原则，将最常用、最具有区分度的列放在联合索引的最左边。
例如，如果我们经常需要根据name和age进行查询，那么可以创建一个联合索引
(name, age)。如果还有其他经常使用的查询条件，也可以根据具体情况创建相应
的联合索引。
4.2 避免使用范围查询
尽量避免在联合索引上进行范围查询（例如
BETWEEN
、
>
、
<
等操作符）。如果确实
需要进行范围查询，可以考虑将范围查询放到联合索引之后。
例如，我们有一个联合索引(name, age)，如果查询条件是
name='Alice' AND age >
20
，可以改写为
age > 20 AND name='Alice'
。这样可以保证age仍然位于联合索引
的最左边。
4.3 避免使用不等于操作符
尽量避免在联合索引上使用不等于操作符（例如
<>
）。如果确实需要使用不等于操
作符，可以考虑将不等于条件拆分成多个等于条件，并使用UNION或者IN来组合
结果。
例如，我们有一个联合索引(name, age)，如果查询条件是
name='Alice' AND age <>
20
，可以改写为
name='Alice' AND age IN (10, 15, 25, ...)
。
4.4 避免使用函数或表达式进行查询
尽量避免在联合索引上使用函数或表达式进行查询。如果确实需要使用函数或表达
式，可以考虑将结果存储到一个新的列中，并在该列上创建索引。
例如，我们有一个联合索引(name, age)，如果查询条件是
SUBSTR(name, 1,
3)='Ali'
，可以考虑创建一个新的列name_prefix，将SUBSTR(name, 1, 3)的结果
存储到该列，并在该列上创建索引。
4.5 字符串类型的列不要使用前缀索引
尽量避免在联合索引中的字符串类型的列上使用前缀索引。如果确实需要使用前缀
索引，需要根据实际情况选择合适的前缀长度。
例如，我们有一个联合索引(name(10), age)，如果查询条件是
name='Alic'
，由于
前缀长度不满足要求，该联合索引将无法被使用。可以考虑增加前缀长度或者不使
用前缀索引。
总结
本文详细介绍了MySQL联合索引失效的几种原因，并解释了其基本原理。为了避免
联合索引失效，我们可以根据实际需求创建合适的联合索引，并避免使用范围查询、
不等于操作符、函数或表达式以及前缀索引。通过合理使用索引，可以提高查询性
能，加快数据检索的速度。
版权声明：本文标题：mysql联合索引失效原理 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://roclinux.cn/b/1710835745a575506.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论