索引：索引使用、索引失效

关于

关于索引的基础知识（索引结构：B+树，聚集索引与辅助索引，复合索引，全文索引，索引前缀，回表，排序索引构建：索引的批量加载 等），笔记中已有深入了解。

以下记录对“索引使用”、“索引失效”的情况进行说明：

1. 什么情况下使用索引？
2. 范围查询是否使用索引？
3. 什么情况下索引失效？
4. 如何进行索引优化？

补：覆盖索引

覆盖索引（covering index）：索引包含查询检索到的所有列（查询字段、条件列）。【即：索引覆盖了所有需要的列】

即：非主键索引包含所需列，而无需回表。

• 覆盖索引其实是数据库查询优化器的一种机制，而非索引算法或索引实现。
• 覆盖索引是建立在已有索引基础之上的；而要利用覆盖索引进行优化，常常通过复合索引。
• 即从非主键索引中就能得到所需的数据列，而不需要查询主键索引中的记录，避免了回表产生的树搜索所带来的I/O操作，从而提升性能。
• 使用覆盖索引 InnoDB 比 MyISAM 效果更好：InnoDB 使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。【？】

注意：以下情况，执行计划不会选择覆盖查询：

1. select 选择的字段中含有不在索引中的字段 ，即索引没有覆盖全部的列。【索引未全面覆盖】
2. where 条件中不能含有对索引进行 like 的操作。【？不使用索引？】

示例

有表如下：

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '名称',
  `age` int(3) unsigned NOT NULL DEFAULT '1' COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `I_name` (`name`)
) ENGINE=InnoDB;

INSERT INTO student (name, age) VALUES("小赵", 10),("小王", 11),("小李", 12),("小陈", 13);

对应索引结构如下：

对于如下查询：

SELECT age FROM student WHERE name = '小李'；

1. 通过其普通索引，查询如下：【未使用“覆盖索引”优化】
   1. 在“name”索引树上找到“小李”对应的节点，获取其“id”为“03”；
   2. 从主索引树上找到“id”为“03”的叶子节点；【回表】
   3. 在叶子节的数据中，获取字段命为“age”的值“12”；

   以上查询，未使用“覆盖索引”，需通过回表在主索引上才能得到“age”值。

2. 删除其“name”索引，并以“name”和“age”两个字段建立联合索引：【建立复合索引，以使用“覆盖索引”优化】

   ALTER TABLE student DROP INDEX I_name;
   ALTER TABLE student ADD INDEX I_name_age(name, age);
   

   其复合索引结构如下：

   

   通过复合索引，查询流程如下：【使用复合索引】

   1. 在“name，age”索引树上找到“小李”对应的节点，并在该节点的索引值中获取其“age”为“12”；

   以上查询，直接从索引中得到“age”值，而并未回表。

   【“name，age”索引，包括了条件中的“name”字段和查询结果中的“age”字段】

判断“覆盖优化”，通过查询执行计划：

如上，在 explain 的 extra 列可以看到 using index 的信息。