什么情况下索引会失效？（查询时不使用索引）

一、在创建数据表时创建索引

属性值的含义

INDEX 和 KEY 参数用于指定字段为索引的，用户在选择时，只需要选择其中的一种即可
UNIQUE: 可选参数，表示索引为唯一索引
FULLTEXT: 可选参数，表示索引为全文索引
SPATIAL: 可选参数，表示索引为空间索引

1.创建普通索引

不添加任何参数。
例如：在该表的name字段上建立索引

index(name)

CREATE TABLE `user2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) CHARACTER SET latin1 DEFAULT '',
  `age` tinyint(4) DEFAULT '0',
  `address` varchar(32) CHARACTER SET latin1 DEFAULT '',
  PRIMARY KEY (`id`),
  index(name)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

2.创建唯一索引

使用UNIQUE进行约束
例如：在该表的id字段上创建唯一索引

UNIQUE index user3(id ASC)

CREATE TABLE `user3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) CHARACTER SET latin1 DEFAULT '',
  `age` tinyint(4) DEFAULT '0',
  `address` varchar(32) CHARACTER SET latin1 DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE index user3(id ASC)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

3.创建全文索引

全文索引只能作用在char,varchar,text类型的字段上，创建全文索引需要FULLTEXT参数进行约束。
例如：在该表的name字段上建立全文索引

CREATE TABLE `user4` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) CHARACTER SET latin1 DEFAULT '',
  `age` tinyint(4) DEFAULT '0',
  `address` varchar(32) CHARACTER SET latin1 DEFAULT '',
  PRIMARY KEY (`id`),
  FULLTEXT KEY user4(name)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

4.创建单列索引

创建单列索引，即在数据表的单个字段上创建索引。创建该类型索引不需要引入约束参数，用户在建立时只需要指定单列字段名，即可创建单列索引。
例如：在该表指定字段age上建立名称为age_num的单列索引

CREATE TABLE `user5` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) CHARACTER SET latin1 DEFAULT '',
  `age` tinyint(4) DEFAULT '0',
  `address` varchar(32) CHARACTER SET latin1 DEFAULT '',
  PRIMARY KEY (`id`),
  index age_num(age)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

5.创建多列索引

创建多列索引即指定表的多个字段即可实现。
例如：在该表中指定name和age为多列索引

CREATE TABLE `user6` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) CHARACTER SET latin1 DEFAULT '',
  `age` tinyint(4) DEFAULT '0',
  `address` varchar(32) CHARACTER SET latin1 DEFAULT '',
  PRIMARY KEY (`id`),
  index info(name,age)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

6.创建空间索引

需要设置设置SPATIAL参数，只有MyISAM类型存储引擎支持该类型索引。而且索引字段必须有非空约束。
例如：创建一个名为userinfo的空间索引在goods字段上

CREATE TABLE `user7` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
	`goods` geometry not null,
  PRIMARY KEY (`id`),
  SPATIAL INDEX userinfo(goods)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

注意：goods字段不能为空，且数据类型是GEOMETRY（空间数据类型）。空间类型不能用其他类型代替，否则在生成空间索引时会产生错误。

二、判断查询中是否用到了索引

以下例子中用到的表：
表结构是：

创建的索引有：
可以使用explain关键字进行判断。
explain的用法：

EXPLAIN SELECT * FROM goods WHERE id=3

id：选择标识符；
select_type：表示查询的类型；
table：输出结果集的表；
partitions：匹配的分区；
type：表示表的连接类型；
possible_keys：表示查询时，可能使用的索引；
key：表示实际使用的索引；
key_len：索引字段的长度；
ref：列与索引的比较；
rows：扫描出的行数(估算的行数)；
filtered：按表条件过滤的行百分比；
extra：执行情况的描述和说明

索引失效的几种情况：

1.模糊查询中 %放在开头

like ‘%XXX’ 或者 like ‘%XXX%’ 索引会失效
like ‘XXX%’ 索引不会失效

例如：
like ‘%XXX’

EXPLAIN SELECT * FROM user_info3 WHERE name like '%a'

like ‘%XXX%’

EXPLAIN SELECT * FROM user_info3 WHERE name like '%a%'

like ‘XXX%’

EXPLAIN SELECT * FROM user_info3 WHERE name like 'a%'

2.查询条件中有or

查询条件中有or，即使其中有条件带索引也不会使用。(所以很少使用)
不加or时：

EXPLAIN SELECT * FROM user_info3 WHERE id=3

加上or后：

EXPLAIN SELECT * FROM user_info3 WHERE id=3 or age=6

要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

3.字符串不加’ ’

在查询时，没有对字符串加单引号，mysql的查询优化器会自动进行类型转换，造成索引失效

4.索引字段做运算

EXPLAIN SELECT * FROM user_info3 WHERE age*2>6

5.使用 != 和<>

EXPLAIN SELECT * FROM user_info3 WHERE name != 'a'

EXPLAIN SELECT * FROM user_info3 WHERE name <> 'a'

6.正则表达式不使用索引

7.全表扫描比使用索引快

mysql内部优化器会对sql语句进行优化，如果优化器使用全表全表扫描比使用索引快，则不使用索引。
在进行范围查询（比如>、< 、>=、<=、in等条件），会根据实际查询数据来判断，如果全盘扫描速度比索引速度要快则不走索引 。
在字段存在null 和 not null的情况下，如果表中该索引列数据大部分是非空值，则在is not null时使用索引，在is null时不使用索引（全表扫描），反之亦然。

注意：

避免使用select

因为它会进行全表扫描，不能有效利用索引，增大了数据库服务器的负担，以及它与应用程序客户端之间的网络IO开销。
例如：

EXPLAIN SELECT * FROM user_info3 WHERE name ='a' '

这里虽然结果显示用到了索引，但是为了获取非索引项字段，我们需要回表去查询数据，效率较低。
Extra为null表示没有用覆盖索引。

尽量使用覆盖索引(索引列完全包含查询列)

什么是覆盖索引？

select的数据列只用从索引中就能够获得，不必读取数据行，不用回表。

怎么判断有没有用到覆盖索引？

EXPLAIN SELECT age FROM user_info3 WHERE name ='a'

Extra输出Using Index 表示用到了覆盖索引。