MySQL联合索引和索引不生效的情况研究

联合索引

测试前准备

mysql版本5.7.22

创建表结构：

CREATE TABLE tmp_table (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    age INT,
    msg VARCHAR(255)
    );

插入临时数据:

INSERT INTO tmp_table (name,age,msg) values ('andy',15,'a'),('jerry',20,'j'),('kelly',25,'k');

表内容：

id	name	age	msg
1	andy	15	a
2	jerry	20	j
3	kelly	25	k

创建联合索引：

ALTER TABLE tmp_table add INDEX name_age_msg(name,age,msg);

测试联合索引的生效情况

语句	type	key
explain select * from tmp_table where name = 'andy'\G;	ref	name_age_msg
explain select * from tmp_table where age = 20\G;	index	name_age_msg
explain select * from tmp_table where msg = 'a'\G;	index	name_age_msg
explain select * from tmp_table where name='andy' and age=15\G;	ref	name_age_msg
explain select * from tmp_table where name='andy' and age=15 and msg='a'\G;	ref	name_age_msg
explain select * from tmp_table where name='andy' and msg='a'\G;	ref	name_age_msg
explain select * from tmp_table where age=15 and msg='a'\G;	index	name_age_msg
explain select * from tmp_table where age=15 and msg='a' and name='andy'\G;	ref	name_age_msg
explain select * from tmp_table where age=15 or msg='a' or name='andy'\G;	index	name_age_msg
explain select * from tmp_table where age=15 or msg='a'\G;	index	name_age_msg
explain select * from tmp_table where name='andy' or age=15\G;	index	name_age_msg

接下来创建新的一列，并且此列没有索引

ALTER TABLE tmp_table ADD column location VARCHAR(255);

更新location的数据，不让其为NULL（避免值为NULL的列影响索引的使用，实际上值是否为NULL并不会对索引是否生效造成影响。这个下文会解释）

UPDATE tmp_table set location = 'text';

现在表中的内容如下：

id	name	age	msg	location
1	andy	15	a	text
2	jerry	20	j	text
3	kelly	25	k	text

重复上面的语句，查看联合索引使用情况：

序号	语句	type	key
1	explain select * from tmp_table where name = 'andy'\G;	ref	name_age_msg
2	explain select * from tmp_table where age = 20\G;	ALL	NULL
3	explain select * from tmp_table where msg = 'a'\G;	ALL	NULL
4	explain select * from tmp_table where name='andy' and age=15\G;	ref	name_age_msg
5	explain select * from tmp_table where name='andy' and age=15 and msg='a'\G;	ref	name_age_msg
6	explain select * from tmp_table where name='andy' and msg='a'\G;	ref	name_age_msg
7	explain select * from tmp_table where age=15 and msg='a'\G;	ALL	NULL
8	explain select * from tmp_table where age=15 and msg='a' and name='andy'\G;	ref	name_age_msg
9	explain select * from tmp_table where age=15 or msg='a' or name='andy'\G;	ALL	NULL
10	explain select * from tmp_table where age=15 or msg='a'\G;	ALL	NULL
11	explain select * from tmp_table where name='andy' or age=15\G;	ALL	NULL
12	explain select * from tmp_table where name='andy' or location='text'\G;	ALL	NULL
13	explain select * from tmp_table where name='andy' and msg='a' and location = 'text'\G;	ref	name_age_msg
14	explain select * from tmp_table where name='andy' and msg='a' or location = 'text'\G;	ALL	NULL
15	explain select * from tmp_table where name=NULL\G	NULL	NULL
16	explain select * from tmp_table where name=''\G	ref	name_age_msg
17	explain select * from tmp_table where name is null\G	ref	name_age_msg
18	explain select * from tmp_table where name is null and msg is null\G	ref	name_age_msg

结论和解释

如上例子，联合索引会创建(name),(name,age),(name,age,msg)这三个索引。

虽然看上去没有创建(name, msg)索引，但是通过explain select * from tmp_table where name='andy' and msg='a'\G;（表格中的第6条）语句可以发现还是使用到了联合索引。

联合索引遵循最左匹配原则，在where...and语句中必须包含name这个“最左字段”，不然的话联合索引不生效。

但是where...and语句中的联合索引这三个字段的顺序可以不一致，只要包含最左的字段就可以使索引生效，因为mysql的sql优化器会优化这些代码。

联合索引对or关系不起作用，必须要使用and作为条件。

上面的实验结果存不存在location字段对explain出来的结果不一致的情况，原因是在没有加上location字段的时候全表都是有索引的，id是主键，剩余三个字段是联合索引，所以语句explain出来都是使用了index。而后面的实验添加了无索引的额外字段之后就使的测试结果恢复正常的预期了。

使用and作为条件查询，即使存在无索引的条件字段，只要存在有索引的列，explain的结果也会使用到索引，但是如果使用or作为条件查询，那么只要其中一个字段没有索引，就不会使用索引，而是全表扫描（例如表格中的第14条）。

col=null和col is null查询的区别

1. col=null无法使用索引，并且也查询不出数据（对比上面15，和17条查询语句）
2. 是否使用索引只与查询条件是否使用col=null有关，而与列的数据是否存在null值的行无关。

比如上面的name列，不管这一列是否存在null值的行，只要是使用col=null就不会使用索引，并且查询不到值，而使用col is null即会使用索引，也会查询出具体的行。

3. 空字符串与null并不相等，且不可替换，针对这两个值的查询语句和结果都不一样

如果name列存在一行值为空字符串，则需要使用name=''条件来查询。并且可以使用索引，参考上面的第16条语句的explain结果。而不能使用name=null这样的查询。

至于有些文章里面提到的“联合索引中如果列的值包含NULL则此列对于联合索引就是无效的”，这一结论并不准确。

还是使用上面的表，我将age列中的其中一个数据改为NULL之后，使用语句explain select * from tmp_table where name='jerry' and age=20\G依然是使用到了索引。并且将name字段中其中一个值改为null，执行语句explain select * from tmp_table where name='jerry'\G同样使用到了索引，证明此结论并不准确。只有当查询条件中使用到了where name=null时此列的索引才不会生效，即使name字段中没有值为null。

索引不生效的情况

1. 查询条件中包含“!=”或”<>”。

这种的情况针对普通索引，主键的情况下type为range，使用了主键作为key。

2. 条件中有or，但是有其中一个or没有建立index

此时要使用index，需要把所有or查询字段都加上索引

3. like查询以“%”开头

例如explain select * from tmp_table where name like '%sdf'\G;就没有使用索引，而like 'sdf%'就使用了

4. 如果列类型是字符串，那要在条件中将数据用引号引用起来，即使查询的数据是一个字符串整型

例如explain select * from tmp_table where name='123'\G;使用到了索引，而where name=123则是全表扫描。

5. 如果查询条件里面包含col=null则此列的索引不生效。并且也查询不出数据，必须使用col is null语句才能使索引生效。
6. 如果mysql查询优化器估计使用全表扫描要比使用索引快，则不使用索引

参考

mysql 联合索引匹配原则
mysql联合索引