乐趣区

关于数据库:MySQL-覆盖索引详解

jiezi
  1. 什么是索引?
    索引（在 MySQL 中也叫“键 key”）是存储引擎疾速找到记录的一种数据结构，艰深来说相似书本的目录，这个比如尽管被用的最多然而也是最恰如其当的，在查问书本中的某个知识点不借助目录的状况下，往往都找的够呛，那么索引相较于数据库的重要性也可见一斑。
  2. 索引的有哪些品种？
    索引的品种这里只列举出 InnoDB 反对的索引：主键索引(PRIMARY)，一般索引(INDEX)，惟一索引(UNIQUE)，组合索引，总体划分为两类，主键索引也被称为聚簇索引（clustered index），其余都称说为非主键索引也被称为二级索引（secondary index）。
  3. InnoDB 的不同的索引组织构造是怎么的呢？
    家喻户晓在 InnoDB 援用的是 B + 树索引模型，这里对 B + 树结构临时不做过多论述，很多文章都有形容，在第二问中咱们对索引的品种划分为两大类主键索引和非主键索引，那么问题就在于比拟两种索引的区别了，咱们这里建设一张学生表，其中蕴含字段 id 设置主键索引、name 设置一般索引、age(无解决)，并向数据库中插入 4 条数据：（” 小赵 ”, 10）（” 小王 ”, 11）（” 小李 ”, 12）（” 小陈 ”, 13）
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);

这里咱们设置了主键为自增，那么此时数据库里数据为

每一个索引在 InnoDB 外面对应一棵 B + 树，那么此时就存着两棵 B + 树。

能够发现区别在与叶子节点中，主键索引存储了整行数据，而非主键索引中存储的值为主键 id, 在咱们执行如下 sql 后

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

流程为：

在 name 索引树上找到名称为小李的节点 id 为 03
从 id 索引树上找到 id 为 03 的节点 获取所有数据
从数据中获取字段命为 age 的值返回 12
在流程中从非主键索引树搜寻回到主键索引树搜寻的过程称为：回表，在本次查问中因为查问后果只存在主键索引树中，咱们必须回表能力查问到后果，那么如何优化这个过程呢？引入注释笼罩索引

  1. 什么是笼罩索引？
    笼罩索引（covering index，或称为索引笼罩）即从非主键索引中就能查到的记录，而不须要查问主键索引中的记录，防止了回表的产生缩小了树的搜寻次数，显著晋升性能。
  2. 如何应用是笼罩索引？
    之前咱们曾经建设了表 student，那么当初呈现的业务需要中要求依据名称获取学生的年龄，并且该搜寻场景十分频繁，那么先在咱们删除掉之前以字段 name 建设的一般索引，以 name 和 age 两个字段建设联结索引，sql 命令与建设后的索引树结构如下
ALTER TABLE student DROP INDEX I_name;
ALTER TABLE student ADD INDEX I_name_age(name, age);

那在咱们再次执行如下 sql 后

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

流程为：

在 name,age 联结索引树上找到名称为小李的节点
此时节点索引里蕴含信息 age 间接返回 12

  1. 如何确定数据库胜利应用了笼罩索引呢？
    当发动一个索引笼罩查问时，在 explain 的 extra 列能够看到 using index 的信息

这里咱们很分明的看到 Extra 中 Using index 表明咱们胜利应用了笼罩索引

总结：笼罩索引防止了回体现象的产生，从而缩小树的搜寻次数，显著晋升查问性能，所以应用笼罩索引是性能优化的一种伎俩，文章有不当之处，欢送斧正，以上环境都是部署在 3A 服务器上的，感兴趣的敌人能够本人部署尝试下

退出移动版