KnifeFly
0%

MySQL 索引失效

Posted on Edited on

MySQL 索引失效

为了测试索引失效，首先创建一个用于测试的数据表

1
2
3
4
5
6
7
8
9
10
create table 
	student(id int not null primary key, name varchar(10) not null, 
	age int, 
	address varchar(200), 
	hobby varchar(200), 
	key(name, age, address)
);

insert into student(id, name, age, address, hobby) 
	values(1, 'a', 20, 'a', 'a'),(2, 'b', 30, 'b', 'b'),(3, 'c', 30, 'c', 'c');

索引失效的几种常见场景

  1. select语句中有is null或者is not null查询判断。索引是一棵B+树，节点中不会存储NULL值。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
MariaDB [test]> explain select * from student where age is null \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)


MariaDB [test]> explain select * from student where age is not null \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.01 sec)
  1. 前导模糊查询不能利用索引，例如查询字段使用like '%XX'或者like '%XX%'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
MariaDB [test]> explain select * from student where name like '%a' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)


MariaDB [test]> explain select * from student where name like '%a%' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)


// like 'a%' 场景是可以使用到索引
MariaDB [test]> explain select * from student where name like 'a%' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: range
possible_keys: name
          key: name
      key_len: 12
          ref: NULL
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)
  1. 查询条件有or
1
2
3
4
5
6
7
8
9
10
11
12
13
MariaDB [test]> explain select * from student where name='a' or name='b' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)
  1. 不符合左前缀法则的查询，跳过多列索引的第一列
1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [test]> explain select * from student where age=10 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)

  1. 范围查询 > < between
1
2
3
4
5
6
7
8
9
10
11
12
13
MariaDB [test]> explain select * from student where name>='a' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)
  1. 查询条件使用函数在索引列上，或者对索引列进行运算
1
2
3
4
5
6
7
8
9
10
11
12
13
MariaDB [test]> explain select * from student where left(name, 1)='a' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)
  1. not in/not exists
1
2
3
4
5
6
7
8
9
10
11
12
13
MariaDB [test]> explain select * from student where name not in ('c', 'b') \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)
  1. 隐式转换导致索引失效，name是一个字符串字段，select查询时传入数字，mysql会把数字转换为字符串，然后做全表扫描
1
2
3
4
5
6
7
8
9
10
11
12
13
MariaDB [test]> explain select * from student where name = 11 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)