MySQL索引创建于是否失效的情况

基础概念

索引： 索引是对表中的一列或者多列的数据进行排序的物理结构。

联合索引： 两个或更多个列上的索引被称作联合索引，联合索引又叫复合索引。

测试前准备

MySql数据库版本

5.7.29 MySQL Community Server (GPL)

建表语句

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

插入数据

INSERT INTO t_user (name,age,msg) values ('Andy',18,'a'),('jerry',20,'j'),('kelly',25,'k');

创建联合索引

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

表内容

id	name	age	msg
1	Andy	18	a
2	jerry	20	j
3	kelly	25	k

type说明:

index:虽然all和index都是扫描全表，但index从索引中读取的，all是从硬盘中读取的。
ref：非唯一性索引扫描，返回匹配某个单独值的所有行，
本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而它可能会找到多个符合条件的行，所以属于查找和扫描的混合体。

测试联合索引生效情况

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

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

ALTER TABLE t_user ADD column location VARCHAR(255);

现在表中的内容如下：

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 t_user where name = ‘andy’\G;	ref	name_age_msg
2	explain select * from t_user where age = 20\G;	ALL	NULL
3	explain select * from t_user where msg = ‘a’\G;	ALL	NULL
4	explain select * from t_user where name=‘andy’ and age=15\G;	ref	name_age_msg
5	explain select * from t_user where name=‘andy’ and age=15 and msg=‘a’\G;	ref	name_age_msg
6	explain select * from t_user where name=‘andy’ and msg=‘a’\G;	ref	name_age_msg
7	explain select * from t_user where age=15 and msg=‘a’\G;	ALL	NULL
8	explain select * from t_user where age=15 and msg=‘a’ and name=‘andy’\G;	ref	name_age_msg
9	explain select * from t_user where age=15 or msg=‘a’ or name=‘andy’\G;	ALL	NULL
10	explain select * from t_user where age=15 or msg=‘a’\G;	ALL	NULL
11	explain select * from t_user where name=‘andy’ or age=15\G;	ALL	NULL
12	explain select * from t_user where name=‘andy’ or location=‘text’\G;	ALL	NULL
13	explain select * from t_user where name=‘andy’ and msg=‘a’ and location = ‘text’\G;	ref	name_age_msg
14	explain select * from t_user where name=‘andy’ and msg=‘a’ or location = ‘text’\G;	ALL	NULL
15	explain select * from t_user where name=NULL\G	NULL	NULL
16	explain select * from t_user where name=’’\G	ref	name_age_msg
17	explain select * from t_user where name is null\G	ref	name_age_msg
18	explain select * from t_user where name is null and msg is null\G	ref	name_age_msg

结论和解释

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

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

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

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

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

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

1，col=null无法使用索引，并且也查询不出数据。

2，是否使用索引只与查询条件是否使用col=null有关，而与列的数据是否存在null值的行无关。

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

如果name列存在一行值为空字符串，则需要使用name=''条件来查询。并且可以使用索引。

索引不生效的情况

1，询条件中包含 != 或 <> 。

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

3，like查询以 % 开头

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

5，如果查询条件里面包含 col=null 则此列的索引不生效。并且也查询不出数据，必须使用 col is null 语句才能使索引生效。

6，如果mysql查询优化器估计使用全表扫描要比使用索引快，则不使用索引。