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索引的数据结构。
- 浅蓝色的块是磁盘块，对应硬件上的磁盘空间。其中深蓝色的块表示数据项（17，35），黄色的表是指针（P1,P2,P3）
- 真实的数据存在于叶子节点中，非叶子节点只储存数据项和指引方向的指针，这里17，和35并不实际存在于数据表中。
- 如果我们要在上图中搜索数据75，磁盘仅需要3次IO即可。第一次，与17和35比较，发现数据大于35，根据指针P3指示继续往下走。第二次，发现数据介于65和87中间，指针P2指向数据块10，那么第三次就能直接查找到数据。
- 显然如果没有建立索引，直接挨个查找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 '长安%'
然后分析一波下面的问题
- 第二个查询语句有没有命中索引？
- 如果没有命中索引，如何让它命中索引
- 它能命中索引的哪些字段（name,age,address）
.
.
.
.
.
.
好了，思考一个省略号的时间之后我们来回答这个问题。
- 第二个sql命中了索引。
- 这里name字段是varchar类型，字符串未加单引号索引会失效，但是like特殊，不受限制，这里加不加单引号并没有影响。
- 命中了索引的字段是，name、age。 address并未命中。这里name字段like查询为范围查询，按照联合索引返回查询之后全失效的规则，这里age本不应该命中索引，但是like例外。
下面是分析过程
我们分析的依据是第一个sql的执行结果，用explain来分析查看，可以看到3个字段都命中索引之后 key_len值为708。
sql1.jpg
再看第二个sql，发现key_len为506。可以证明真正命中了索引的字段是name和age。
sql2.jpg
今天就分析到这里，希望这些例子能帮助大家少踩一些坑！！！
网友评论