使用覆盖索引优化like查询

什么叫覆盖索引？
解释一：就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

解释二：索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。

解释三：是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。

总之，不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引

explain里type字段注解:

system

const的特例，仅返回一条数据的时候。

const 查找主键索引，返回的数据至多一条（0或者1条）。属于精确查找

eq_ref 查找唯一性索引，返回的数据至多一条。属于精确查找 ref 查找非唯一性索引，返回匹配某一条件的多条数据。属于精确查找、数据返回可能是多条

range 查找某个索引的部分索引，一般在where子句中使用 < 、>、in、between等关键词。只检索给定范围的行，属于范围查找

index 查找所有的索引树，比ALL要快的多，因为索引文件要比数据文件小的多。

ALL 不使用任何索引，进行全表扫描，性能最差。

explain里extra字段注解:

using index ：使用覆盖索引的时候就会出现
using where：在查找使用索引的情况下，需要回表去查询所需的数据
using index condition：查找使用了索引，但是需要回表查询数据
using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
有实验证明using index & using where要优于using index condition。

1.回表

上面讲过如果索引的列在select子句中就不需要回表，索引列也是表，如果索引中包括你需要的列，查询结果就找到了，如果没有你需要的列，索引列中有指针指向表记录的行位置，从表中查询列值。

2.索引篇

聚簇索引：每个InnoDB表具有一个特殊的索引称为聚集索引。如果您的表上定义有主键，该主键索引是聚集索引。如果你不定义为您的表的主键时，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚集索引。如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

二级索引：又称辅助索引、非聚集索引(no-clustered index)。b＋tree树结构，然而二级索引的叶子节点不保存记录中的所有列，其叶子节点保存的是<健值，(记录)地址>，非叶子节点存放的记录格式为<键值，主键值，地址>。而聚集索引叶子节点保存保存记录中的所有列，非叶子节点保存的是下一层节点地址。

做个测试吧:

建表:

CREATE TABLE `t_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

生成测试数据:

DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `proc_batch_insert`()
BEGIN
DECLARE pre_name BIGINT;
DECLARE ageVal INT;
DECLARE i INT;
SET pre_name=187635267;
SET ageVal=100;
SET i=1;
WHILE i < 1000000 DO
        INSERT INTO t_user(`name`,age,create_time,update_time) VALUES(CONCAT(pre_name,'@qq.com'),(ageVal+i)%30,NOW(),NOW());
SET pre_name=pre_name+100;
SET i=i+1;
END WHILE;
END;;
DELIMITER ;