当前位置：首页 > news >正文

MySQL复合索引

news 文章来源：https://blog.csdn.net/qq_43012298/article/details/135852142 2024/11/28 23:59:15

复合索引是指在数据库表上同时包含两个或更多列的索引。它们对于优化涉及这些列的查询非常有效，特别是当这些列常常在查询条件（如WHERE子句）、排序（ORDER BY子句）和连接（JOIN条件）中使用时。

复合索引生效的场景

最左前缀原则（Leftmost Prefix Rule）:
如果查询条件使用了复合索引的最左边的列，则索引生效。例如，如果有一个复合索引(A, B, C)，那么对于查询条件包含A、A和B、或A、B和C的查询，该索引都会被考虑使用。
索引覆盖查询（Covering Index）:
如果一个查询的选择列完全由复合索引中的列组成，那么这个查询就是一个索引覆盖查询，复合索引将被用于直接返回结果，而不需要回表查询数据。
排序和分组操作:
如果ORDER BY或GROUP BY子句中的列与复合索引匹配，并且遵循索引的列顺序，那么这个索引可以用于优化排序和分组操作。

复合索引失效的场景

中间列跳跃:
如果查询条件跳过了复合索引中的中间列，索引可能不会被完全利用。例如，对于复合索引(A, B, C)，如果查询条件只有A和C，而没有B，则这个索引只有部分生效。
不遵循索引顺序:
如果WHERE、ORDER BY或GROUP BY子句中的列顺序与复合索引中的列顺序不一致，索引可能不会被充分利用。
索引列用于计算:
和单列索引一样，如果索引列被用于计算或函数中，索引可能不会被使用。
使用范围查询:
对复合索引中的第一个列使用范围查询（如>、<、BETWEEN、LIKE 'prefix%'）后，该列之后的索引列将不会被用于优化。

源码解析

在MySQL中，复合索引的使用由优化器根据查询的结构和统计信息来决定。这是在sql/optimizer/路径下的range_optimizer和sql_optimizer.cc文件中处理的。优化器会根据成本基准模型选择是否使用复合索引。

Java代码演示

以下是一个简单的Java代码示例，展示如何创建复合索引，以及如何编写一个可能触发索引失效的查询：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;public class CompositeIndexExample {public static void main(String[] args) {String jdbcUrl = "jdbc:mysql://localhost:3306/yourdatabase";String username = "yourusername";String password = "yourpassword";try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password);Statement statement = connection.createStatement()) {// 创建复合索引String createIndexSQL = "CREATE INDEX idx_column1_column2 ON yourtable (column1, column2)";statement.execute(createIndexSQL);// 查询使用复合索引的最左前缀String queryWithLeftmostPrefix = "SELECT * FROM yourtable WHERE column1 = ?";// 查询跳过复合索引中的中间列String querySkippingIntermediateColumn = "SELECT * FROM yourtable WHERE column1 = ? AND column3 = ?";// 查询不遵循索引列顺序String queryNotFollowingIndexOrder = "SELECT * FROM yourtable WHERE column2 = ? AND column1 = ? ORDER BY column2, column1";// 上述查询中的PreparedStatement和ResultSet处理逻辑省略...} catch (Exception e) {e.printStackTrace();System.out.println("Error during database operation: " + e.getMessage());}}
}

在这个代码中，createIndexSQL创建了一个复合索引。接着，我们有三个查询示例：第一个是利用复合索引的最左前缀条件，这个查询可以高效利用索引；第二个示例查询跳过了中间列，可能导致索引的效率降低；第三个示例查询不遵循索引列的顺序，可能导致索引不被使用。

注意事项

使用EXPLAIN语句或相应的数据库工具来分析查询是否利用了索引及其效率。
确保查询中使用的列与复合索引定义的顺序相匹配。
在设计复合索引时，要考虑查询模式和数据的选择性。
定期审查和调整索引，以适应数据和查询模式的变化。

通过理解索引的工作原理和生效场景，可以更好地设计和优化数据库索引，以提升应用程序的性能。

MySQL联合索引复合索引（abc）如何索引命中规则实测

本地 MySQL版本 8.0.28

MySQL创建一张表，表名：‘test’

CREATE TABLE test (
id INT(11) NOT NULL AUTO_INCREMENT,
a INT(11) DEFAULT NULL,
b INT(11) DEFAULT NULL,
c INT(11) DEFAULT NULL,
d INT(11) DEFAULT NULL,
e INT(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY index_abc (a,b,c)
);
我用代码往表中写入100万条数据
其中每行数据取值

a 范围 1 - 100000 随机整数
b 范围 1 - 100000 随机整数
c 范围 1 - 100000 随机整数
d 范围 1 - 100000 随机整数
e 范围 1 - 100000 随机整数
开始测试联合索引（a,b,c）
使用 ‘EXPLAIN’ sql语句查看执行详情
EXPLAIN SELECT * FROM test WHERE a = 1 AND b = 1 AND c = 1;

结论（下面结论全部都实际测试过）
只要用到了最左侧a列，和顺序无关都会使用索引
a = 1 AND b = 2 AND c = 3 ; 使用索引
c = 1 AND b = 2 AND a = 3 ; 使用索引
a = 1 AND b = 2 ; 使用索引
a = 1 AND c = 3 ; 使用索引
c = 1 AND a = 2 ; 使用索引

不包含最左侧的 a 的不使用索引
c = 3 ; 未使用索引
b = 2 ; 未使用索引
b = 2 AND c = 3 ; 未使用索引
c = 1 AND b = 2 ; 未使用索引

OR 不使用索引
a = 1 AND b = 2 OR c = 3 未使用索引
a = 1 OR b = 2 AND c = 3 未使用索引
a = 1 OR b = 2 OR c = 3 未使用索引

最左侧的‘a’列被大于，小于，不等于比较的，不一定使用索引.
看比较后结果集是否足够小.

测试时发现有时会使用索引,有时不会使用索引
我猜测这种情况和MySQL现在对索引的优化有关（有懂得大佬指导下哈）
数据a列取值是1-100000,似乎当a列被极小范围或足够小范围查询时是会使用索引的,当a列被比较小查询或取值范围较大时,MySQL就放弃了使用索引

a > 1时,确实不会使用索引,这里2-100000范围太大了
a < 2时,这里会使用索引,这里a取值是1-100000,<2范围就极小了,就是1了
a逐渐增大 a < 1000 ,a<2000,a<10000,a<14000;时都会使用索引.
但当 a < 15000时,就不再使用索引了.

同时a 大于某数时情况是同样的.
a > 1 这里没有使用索引 ( a > 1,等于2-100000)
a > 85000时,会开始使用索引

最左侧a=1，后面列大于小于无所谓，都使用索引（但后面必须 and and ）
a = 1 AND b < 2 AND c = 3 使用索引
a = 1 AND c = 2 AND b < 3 使用索引
a = 1 AND b < 2 使用索引
a = 1 AND b <> 2 AND c = 3 使用索引
OR不使用索引
a = 1 AND b < 2 OR c = 2 未使用索引