like索引查询--mysql索引

like '%%'如何命中索引

相信各位老铁在开发的过程中肯定都遇到过like全匹配的需求。在数据量很小的时候，select * from user where name like '%hello%'。这种查询问题也不大，但是久而久之，会发现输入框再输入这种简单文字的时候页面就会loading很久。当数据量大起来，like双百分号匹配不走索引的时候就很忧伤，但是需求不会因为这个而改变。那么，mysql是如何解决这个问题的呢？或许可以试试explain select name from user where name like '%hello%'。你会意外的发现key字段竟然不是null。W-H-Y？

覆盖索引

这里引入一个概念。索引包含所有满足查询需要的数据的索引，称为覆盖索引(Covering Index)。百度百科来的定义、是不是抽象的一脸懵逼？可以简单的理解为select的数据列只用从索引中就能够取得，不需要查数据就能拿到结果。拿这个栗子来说，name字段自身是建立了索引的（innodb引擎下索引自身是保存了数据的），select查询name字段直接就可以获取到值，就不需要再根据索引去数据表里面查对应的值。

B+Tree数据结构.jpg

B+Tree数据结构简介

这里有必要介绍一下mysql -- innodb引擎下B+Tree索引的数据结构。

1. 浅蓝色的块是磁盘块，对应硬件上的磁盘空间。其中深蓝色的块表示数据项（17，35），黄色的表是指针（P1,P2,P3）
2. 真实的数据存在于叶子节点中，非叶子节点只储存数据项和指引方向的指针，这里17，和35并不实际存在于数据表中。
3. 如果我们要在上图中搜索数据75，磁盘仅需要3次IO即可。第一次，与17和35比较，发现数据大于35，根据指针P3指示继续往下走。第二次，发现数据介于65和87中间，指针P2指向数据块10，那么第三次就能直接查找到数据。
4. 显然如果没有建立索引，直接挨个查找75的IO次数远不止3次。

关于like的索引问题

索引失效的几种情况大家可以先预习一下，这是百度上搜到的http://blog.csdn.net/zmx729618/article/details/52701370，如果觉得没必要，可以直接跳过。下面我们来模拟数据。

CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(125) NOT NULL DEFAULT '' COMMENT '名称',
  `age` int(3) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
  `address` varchar(50) NOT NULL DEFAULT '' COMMENT '住址',
  `deleted` tinyint(4) unsigned DEFAULT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_name_age_address` (`name`,`age`,`address`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COMMENT='用户表测试';

INSERT INTO `user` (`id`, `name`, `age`, `address`, `deleted`, `created`)
VALUES
    (1, '5', 1000, '花果山', 0, '2017-12-10 21:48:03'),
    (2, '8', 800, '高老庄', 0, '2017-12-10 21:48:16'),
    (3, '3', 25, '长安', 0, '2017-12-10 21:48:49'),
    (4, '白龙马', 700, '龙宫', 0, '2017-12-10 21:49:16');

执行上面的建表语句，初始化数据之后，我们来看一下下面的两个sql

select * from user 
where name = '3' and age = 20 and address like '长安%' 

select * from user 
where name like 3 and age > 20 and address like '长安%'  

然后分析一波下面的问题

1. 第二个查询语句有没有命中索引？
2. 如果没有命中索引，如何让它命中索引
3. 它能命中索引的哪些字段（name,age,address）
   .
   .
   .
   .
   .
   .

好了，思考一个省略号的时间之后我们来回答这个问题。

1. 第二个sql命中了索引。
2. 这里name字段是varchar类型，字符串未加单引号索引会失效，但是like特殊，不受限制，这里加不加单引号并没有影响。
3. 命中了索引的字段是，name、age。 address并未命中。这里name字段like查询为范围查询，按照联合索引返回查询之后全失效的规则，这里age本不应该命中索引，但是like例外。

下面是分析过程
我们分析的依据是第一个sql的执行结果，用explain来分析查看，可以看到3个字段都命中索引之后 key_len值为708。

sql1.jpg

再看第二个sql，发现key_len为506。可以证明真正命中了索引的字段是name和age。

sql2.jpg

今天就分析到这里，希望这些例子能帮助大家少踩一些坑！！！