筛网怎么做网站,免费社区建站系统,分销商城网站开发,怎么查询网站是否收录复合索引是指在数据库表上同时包含两个或更多列的索引。它们对于优化涉及这些列的查询非常有效#xff0c;特别是当这些列常常在查询条件#xff08;如WHERE子句#xff09;、排序#xff08;ORDER BY子句#xff09;和连接#xff08;JOIN条件#xff09;中使用时。
复…复合索引是指在数据库表上同时包含两个或更多列的索引。它们对于优化涉及这些列的查询非常有效特别是当这些列常常在查询条件如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 ,a2000,a10000,a14000;时都会使用索引. 但当 a 15000时,就不再使用索引了.
同时a 大于某数时情况是同样的. a 1 这里没有使用索引 ( a 1,等于2-100000) a 85000时,会开始使用索引
最左侧a1后面列大于小于无所谓都使用索引但后面必须 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 未使用索引
