Home
img of docs

举例说明 MySQL 在哪些条件下才会使用到索引,触发索引的条件是什么

chou403

/ Database

/ c:

/ u:

/ 6 min read

mysql 条件哪些会用到索引

创建表:

   CREATE TABLE `test_models` (
`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`)
);

  1. AND 只要用到了最左侧a列,和顺序无关 都会使用 索引

       // 使用索引 abc
EXPLAIN SELECT * FROM test_models WHERE a = 1 AND b = 2 AND c = 3;
// 使用索引 abc
EXPLAIN SELECT * FROM test_models WHERE c = 1 AND b = 2 AND a = 3;
// 使用索引 ab
EXPLAIN SELECT * FROM test_models WHERE a = 1 AND b = 2;
// 使用索引 a
EXPLAIN SELECT * FROM test_models WHERE a = 1 AND c = 3;
// 使用索引 a
EXPLAIN SELECT * FROM test_models WHERE c = 1 AND a = 2;

  2. 不包含最左侧 a 列 不使用索引

       // 不使用索引
EXPLAIN SELECT * FROM test_models WHERE c = 3;
// 不使用索引
EXPLAIN SELECT * FROM test_models WHERE b = 2;
// 不使用索引
EXPLAIN SELECT * FROM test_models WHERE b = 2 AND c = 3;
// 不使用索引
EXPLAIN SELECT * FROM test_models WHERE c = 1 AND b = 2;

  3. OR 不使用索引 这个条件中也包含了AND和OR逻辑操作符。在大多数数据库系统中,OR操作符往往难以使用联合索引,因为它需要组合多个条件的结果。在这种情况下,数据库可能会选择使用最适合的单列索引,或者执行多个单列索引的合并操作,或者在没有适用的单列索引的情况下执行全表扫描

       // 不使用索引
EXPLAIN SELECT * FROM test_models WHERE a = 1 AND b = 2 OR c = 3;
// 不使用索引
EXPLAIN SELECT * FROM test_models WHERE a = 1 OR b = 2 AND c = 3;
// 不使用索引
EXPLAIN SELECT * FROM test_models WHERE a = 1 OR b = 2 OR c = 3;

  4. 最左侧的’a’列 被大于,小于比较的 ,使用range索引

       // 使用索引 a
EXPLAIN SELECT * FROM test_models WHERE a > 1 AND b = 2 AND c = 3;
// 使用索引 a
EXPLAIN SELECT * FROM test_models WHERE a < 1 AND b =  2 AND c = 3;
// 使用索引 a
EXPLAIN SELECT * FROM test_models WHERE a > 1;
// 使用索引 a
EXPLAIN SELECT * FROM test_models WHERE a <> 1 AND b = 2 AND c = 3;

  5. 最左侧a=某某,后面列大于小于无所谓,都使用索引(但后面必须 and and )

       // 使用索引 ab
EXPLAIN SELECT * FROM test_models WHERE a = 1 AND b < 2 AND c = 3;
// 使用索引 ab
EXPLAIN SELECT * FROM test_models WHERE a = 1 AND c = 2 AND b < 3;
// 使用索引 ab
EXPLAIN SELECT * FROM test_models WHERE a = 1 AND b < 2;
// 使用索引 ab
EXPLAIN SELECT * FROM test_models WHERE a = 1 AND b <> 2 AND c = 3;
// 不使用索引
EXPLAIN SELECT * FROM test_models WHERE a = 1 AND b < 2 OR c = 2;

  6. ORDER BY a = 某,后面order 无所谓 都 使用索引 (和最上面的最左匹配一样)

       // 使用索引 abc
EXPLAIN SELECT * FROM test_models WHERE a = 1 AND b = 2 AND c = 3 ORDER BY a;
// 使用索引 a
EXPLAIN SELECT * FROM test_models WHERE a = 1 ORDER BY a;
// 使用索引 a
EXPLAIN SELECT * FROM test_models WHERE a = 1 ORDER BY b;
// 使用索引 a
EXPLAIN SELECT * FROM test_models WHERE a = 1 ORDER BY c;
// 不使用索引
EXPLAIN SELECT * FROM test_models WHERE b = 1 order by a;

  7. MySQL联合索引遵循最左前缀匹配规则,即从联合索引的最左列开始向右匹配,直到遇到匹配终止条件。例如联合索引(col1, col2, col3), where条件为col1=a AND col2=b可命中该联合索引的(col1,col2)前缀部分, where条件为col2=b AND col3=c不符合最左前缀匹配,不能命中该联合索引。

  8. 匹配终止条件为范围操作符(如>, <, between, like等)或函数等不能应用索引的情况。例如联合索引(col1, col2, col3), where条件为col1=a AND col2>1 AND col3=c, 在col2列上为范围查询,匹配即终止,只会匹配到(col1, col2),不能匹配到(col1, col2, col3).

  9. where条件中的顺序不影响索引命中。例如联合索引(col1, col2, col3), where条件为col3=c AND col2=b AND col1=a, MySQL优化器会自行进行优化,可命中联合索引(col1, col2, col3).

#java #mysql #database