本文正在参与「技能专题19期 闲谈数据库技能」活动

为什么这么规划索引

  • 假如你仔细阅读了上一部分，那么你一定知道为什么数据库索引选用的是B+Tree， 说白了便是为了提高查询功率。由于只要B+Tree 在满足平衡特性的状况下可以存储更多的索引内容从未维持这颗树在一定高度上。至于为什么不必Hash表那就更不必说了。一个是它发生冲突概率太大另外一个便是他实际便是个链表，链表和树比较必定是树查询快。

有哪些索引

  • 在 mysql 中默认主键索引名是经过 pk_ 开头进行标识的。
索引类型 关键词
主键索引 pk_
唯一索引 uk_
一般索引 idx_

一般索引

CREATE INDEX 索引名 ON 表名(字段名)

  • 一般索引常常是用来进行条件快速查找的，除此之外对字段自身并无要求。

唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名(字段名)

主键索引

alter table 表名 add primary key(字段名)

组合索引

CREATE INDEX 索引名 ON 表名(字段名1,字段名2);

  • 组合索引咱们可以理解成多列组成的一般索引，所以和一般索引创立的语法相同。关于组合索引往往也是面试高频题。其他索引都是单列索引射中与否也很好判别。可是组合索引涉及到最左匹配准则往往是他人忘掉的一点。
  • 关于最左匹配准则呢？这里先简略阐述下，比方你创立了一个联合索引 “create index idx_abc on t (b,c,d) 。 此刻有个sql select * from t where e=1 。这个sql是不会走索引的，最左匹配准则便是联合索引需求从左开端匹配。

全文索引

全文索引首先是 MySQL 的一种索引类型，也是查找引擎的关键技能。

试想在1M大小的文件中查找一个词，或许需求几秒，在100M的文件中或许需求几十秒，假如在更大的文件中查找那么就需求更大的系统开支，这样的开支是不现实的。

所以在这样的矛盾下呈现了全文索引技能，有时分有人叫倒排文档技能。

  • 关于全文索引其实这里和 es 的分词倒是有点像。将分词与数据挂钩，这样经过分词条件查找的时分就会十分的方便，具体讲解可以参考下这篇文章

怎么射中索引

回表&索引下推

回表

  • 回表和索引下推都是基于B+Tree发生的必要场景。由于 mysql 中只要主键索引是聚簇索引也便是只要主键索引叶子节点存储的是实在的行数据，其他的一般索引叶子节点都存储的是主键。当咱们经过一般索引查询时就极有或许需求在经过主键进行查找实在数据，这便是回表。

避免回表，引入索引下推|提高索引命中率 | 提前下班啦

  • 但不是所有的一般索引查询的 sql 都会发生回表的。下面咱们来怎么防止发生回表的且经过一般索引查询的 sql 。
  • 想要防止回表咱们就要做到 索引掩盖 . 掩盖索引并不是指索引结构上有啥不同，而是指咱们的sql 尽量不要用一般索引以外的信息。比方现在咱们又一个索引 idx_name(username) ， 针对这个一般索引咱们运用select username from t where username='xxx' 或许运用select id from t where username='xxx' ， 这两种都是完成了索引掩盖。由于上述两种查询 sql 条件和查询别离用到了 username 和 id ，恰巧这两个字段都在 idx_name 这个索引中。所以这种状况时不需求回表的。
  • 可是这种状况下咱们为了方便或许运用的类似 mybatis-Plus 这种结构都会发生类似这种sql select * from t where username='xxx' 。针对这种sql 关于数据库来说是很不友爱的。由于在事务上你或许只需求经过 username 查找，而字段你或许只需求id或许username 乃至是你不需求查询字段内容。可是sql却去查询了，何况为了其他类似于age 字段 ，mysql 乃至得花一次IO去回表查询，这样关于高性能服务来说是一种IO浪费。

索引下推

  • 5.6前存储引擎经过回表进行判别其他索引是否契合
  • 5.6后存储引擎持续判别其他索引是否契合之后在回表 这叫索引下推。
  • 好像还不是很理解什么是索引下推。 现在咱们创立一个索引idx_uas(username,age,sex) 这个联合索引。 开端索引下推之前咱们先了解下联合索引怎么射中这个问题。
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `user_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `uk_ua` (`user_name`,`age`,`sex`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

insert into t(id,user_name,age,sex) values(1,'zxhtom',11,1);
insert into t(id,user_name,age,sex) values(2,'sun',15,2);
insert into t(id,user_name,age,sex) values(3,'wukong',21,1);
insert into t(id,user_name,age,sex) values(4,'zhu',11,3);
insert into t(id,user_name,age,sex) values(5,'baijie',45,1);
insert into t(id,user_name,age,sex) values(6,'sha',101,2);
insert into t(id,user_name,age,sex) values(7,'heshang',1,1);
insert into t(id,user_name,age,sex) values(8,'zxz',121,1);

避免回表，引入索引下推|提高索引命中率 | 提前下班啦

sql1

select * from t where user_name > ‘zxhtom’ and age<2;

  • 在 explain 之前我一度以为该sql 只能全表扫描了。虽然他是依照最左匹配准则书写的条件匹配，可是上来就遇到了规模查询。而索引匹配在遇到规模查询时就会失效，这是我从书本上背来的，可是事实却并不是我以为的那样。

避免回表，引入索引下推|提高索引命中率 | 提前下班啦

  • 很明显可以看到 Using index 阐明运用了索引，并且或许是 uk_ua 这个索引。这是由于 mysql 会先将 user_name>'zxhtom' 这个条件到 uk_ua 构建的BTree中查找到具体的页节点上。由于最左匹配准则 ，咱们可以知道 user_name>'zxhtom' 的都会在 user_name='zxhtom' 的右侧。在第一层查询到的规模之后再去查找 age<2 的数据。此刻age 这块可以持续上面定位到的节点持续往下查找。

避免回表，引入索引下推|提高索引命中率 | 提前下班啦

sql2

select * from t where age<2;

避免回表，引入索引下推|提高索引命中率 | 提前下班啦

  • 这个sql 就更让我疑惑了，最左匹配的 user_name 都没有，为什么还能走 uk_ua 这颗索引树呢？可是仔细想想也可以理解，究竟age就存在这颗树中。只不过查询的规模就比较广。查了8条记录这就相当于时全表扫描了。而且还规划回表，这便是引擎的不智能。有的时分走索引不比全表扫描快。
  • 两个 sql 比照可以发现 最左匹配准则 遇到非准确查询时会结束索引，而不是不走索引。

下推

  • 好了简略了解最左匹配之后咱们再来看看 索引下推 是什么意思吧。

避免回表，引入索引下推|提高索引命中率 | 提前下班啦

  • 上面联合索引中当咱们经过最左匹配准则 匹配到节点时，咱们知道其他字段尤其是下一字段age 在该内部是部分递增排列的。这个时分假如咱们在依据age 进行定位查找，功率是十分快的。这个过程咱们称之为 索引下推。
select * from t where user_name='zxhtom' and age=2
  • 在5.7之后的 mysql 服务器中会先依据 user_name 定位到节点，然后在节点周边去定位age 从而最终确认到所需节点。然后在依据叶子节点的主键数据回表查询完好数据。
  • 而在 5.7之前并不支撑 索引下推 。 在 5.7之前是先经过 user_name 定位到节点。然后将定位到的节点进行回表查询完好数据，在依据回表查到的数据进行挑选age属性。这样做必定不如 索引下推 来的功率高。究竟准确认位好数据之后在回表这样可以防止不必要的数据查询。此刻假如经过 explain 去分析的话你会发现即使运用的最左匹配 查询 运用的也是 where 查找，而不是像5.7之后运用的是 index condition 。

哪些场景会索引失效

  • 这里就仅仅罗列失效的场景，关于失效的原因以后慢慢单独出篇 explain 详解一下。
  1. 未遵从最左匹配准则
  2. 索引列参与核算 ： select * from t where id+1=2
  3. 对索引列存在包装 ： select * from t where concat(id,'')='1'
  4. 含糊查询中呈现左侧含糊的状况 ： select * from t where id like '%test' 或许 select * from t where id like '%test%'
  5. 发生类型转化 ： select * from t where id ='2'
  6. sql 中呈现 OR 句子 ； not null 句子 ； not in. not exist 等句子
  7. 呈现两列比较，不管比较列在不在索引中 select * from t where id!=name
  8. 非准确查询即 不等于查找 select * from t where id !=1。

本文正在参与「技能专题19期 闲谈数据库技能」活动