索引

在MySQL中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样

而即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同

由于InnoDB存储引擎在MySQL数据库中使用最为广泛，所以就以InnoDB为例，分析一下其中的索引模型

InnoDB的索引模型

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表

又因为InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。每一个索引在InnoDB里面对应一棵B+树

假设，我们有一个主键列为ID的表，表中有字段k，并且在k上有索引


create table T(
    id int primary key, 
    k int not null, 
    name varchar(16),
    index (k)
)engine=InnoDB;

表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)，两棵树的示例示意图如下

从图中不难看出，根据叶子节点的内容，索引类型分为主键索引和非主键索引

主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）

非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）

如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索ID这棵B+树

如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询

索引维护

B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面这个图为例，如果插入新的行ID值为700，则只需要在R5的记录后面插入一个新记录

如果新插入的ID值为400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置

而更糟的情况是，如果R5所在的数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响

除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约50%

当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程

所以大部分场景使用自增主键，从性能和存储空间方面来说都是更合理的

  • 每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂
  • 主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小

当然也有一些特殊的场景，可以不使用自增主键

  • 只有一个索引
  • 该索引必须是唯一索引

其实这就是典型的KV场景。由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题

这时候我们可以直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树

案例分析


create table T (
    ID int primary key,
    k int NOT NULL DEFAULT 0, 
    s varchar(16) NOT NULL DEFAULT '',
    index k(k)
)engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

执行select * from T where k between 3 and 5需要执行几次树的搜索操作

  1. 在k索引树上找到k=3的记录，取得ID=300
  2. 再到ID索引树查到ID=300对应的R3
  3. 在k索引树取下一个值k=5，取得ID=500
  4. 再回到ID索引树查到ID=500对应的R4
  5. 在k索引树取下一个值k=6，不满足条件，循环结束

可以看到，这个查询过程读了k索引树的3条记录（步骤1、3和5），回表了两次（步骤2和4）

那么执行select * from T where k in (3,4,5)与上面的between语句哪个好呢？

between更好，因为in搜索里面有多少个就会搜索多少次索引树

">"、"<"、">="、"<="与between类似

覆盖索引

如果执行的语句是select ID from T where k between 3 and 5，这时只需要查ID的值，而ID的值已经在k索引树上了

因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引k已经"覆盖了"我们的查询需求，我们称为覆盖索引

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段

联合索引

基于上面覆盖索引的说明，我们来讨论一个问题：在一个市民信息表上，是否有必要将身份证号和名字建立联合索引


CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card_name` (`id_card`,`name`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

我们知道，身份证号是市民的唯一标识。也就是说，如果有根据身份证号查询市民信息的需求，我们只要在身份证号字段上建立索引就够了

而再建立一个（身份证号、姓名）的联合索引，是不是浪费空间

如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了

它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间

当然，索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要实际业务场景权衡考虑了

最左前缀原则

以上面的name_age联合索引为例

SELECT * FROM tuser WHERE name='张三';，可以命中索引

SELECT * FROM tuser WHERE age=10;，不能命中索引

如果age需要命中索引，需要再增加一个age索引

所以联合索引最左边的那个字段一定是最多被使用的字段

如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的

索引下推

还是以上面的name_age联合索引为例

select * from tuser where name like '张%' and age=10 and ismale=1

在MySQL5.6之前，只能找到name索引后开始一个个回表。到主键索引上找出数据行，再对比age和ismale的值

而MySQL5.6引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

也就是说，在命中name索引后会优先比较age值，满足条件后再进行回表。以此来减少回表次数

这样的话，如果name和age一直是成对出现的话，只要建立name_age索引就行了，不用单独再为age建立索引了

重建索引

索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间

那么，通过以下语句重建索引和重建主键索引是否合理


alter table T drop index k;
alter table T add index(k);

alter table T drop primary key;
alter table T add primary key(id);

重建索引k的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建

所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，可以使用alter table T engine=InnoDB代替

主键也能用联合索引



CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

results matching ""

    No results matching ""