索引

目录

• 数据结构
  • B 树
  • B+ 树
• 索引
  • 聚集索引
  • 辅助索引
  • 使用
  • 普通索引和唯一索引
  • 联合索引
  • 优化器索引选择

数据结构

B 树

B 树每个节点都包含 key 值和 data 值，因此如果 data 比较大时，每一页存储的 key 会比较少；当数据比较多时，有“要经历多层节点才能查询在叶子节点的数据”的问题。

B+ 树

• 所有叶子节点中包含了全部关键字的信息
• 各叶子节点用指针进行连接
• 非叶子节点上只存储 key 的信息，这样相对 B 树，可以增加每一页中存储 key 的数量。
• B 树是纵向扩展，最终变成一个“瘦高个”，而 B+ 树是横向扩展的，最终会变成一个“矮胖子”（

索引

聚集索引

InnoDB 的数据是按照主键顺序存放的，而聚集索引就是按照每张表的主键构造一颗 B+ 树，它的叶子节点存放的是整行数据。

辅助索引

InnoDB 存储引擎辅助索引的叶子节点存放的是键值和主键 ID。

当通过辅助索引来寻找数据时，InnoDB 存储引擎会查找到对应记录的主键，然后通过主键索引来找到对应的行数据。

使用

• 数据检索
• 聚合函数（max/count）
• 排序
• 避免回表（覆盖索引）
• 关联查询

普通索引和唯一索引

Insert Buffer：对于非聚集索引的插入时，先判断插入的非聚集索引页是否在缓冲池（Buffer Pool）中。如果在，则直接插入；如果不在，则先放
入 Insert Buffer 中，然后再以一定频率和情况进行 Insert Buffer 和辅助索引页子节点的 merge 操作。要求不是唯一索引

意义：将多个插入合并到一个操作中，大大提高了非聚集索引的插入性能。

Change Buffer：Insert Buffer 的升级，，InnoDB 存储引擎可以对 insert、delete、update 都进行缓存。要求不是唯一索引

参数：

• innodb_change_buffering：确定哪些场景使用 Change Buffer，它的值包含：none、inserts、deletes、
  changes、purges、all。默认为 all，表示启用所有。

• innodb_change_buffer_max_size：控制 Change Buffer 最大使用内存占总 buffer pool 的百分比。默认25，表示
  最多可以使用 buffer pool 的 25%，最大值50。

原因：唯一索引必须要将数据页读入内存才能判断是否违反唯一性约束。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 Change Buffer 了。

区别

1、有普通索引的字段可以写入重复的值，而有唯一索引的字段不可以写入重复的值。

2、如果对数据有修改操作，则普通索引可以用 Change Buffer，而唯一索引不行。

3、数据修改时，唯一索引在 RR 隔离级别下，更容易出现死锁。

4、查询数据时，普通索引查到满足条件的第一条记录还需要继续查找下一个记录，而唯一索引查找到第一个记录就可以直接返回结果了，但是普通索引多出的查找次数所消耗的资源多数情况可以忽略不计。

选择

1、如果业务要求某个字段唯一，但是代码不能完全保证写入唯一值，则添加唯一索引

2、如果代码确定某个字段不会有重复的数据写入，则可以选择添加普通索引。

联合索引

对表上的多个列进行索引。适合 where 条件中的多列组合，在某些场景可以避免回表。

使用：

• where 条件中，经常同时出现的列放在联合索引中。
• 把选择性最大的列放在联合索引的最左边。

联合索引应用：

/*使用完整联合索引*/
select * from t11 where a=1 and b=1 and c=1;
select * from t11 where c=1 and b=1 and a=1;
select * from t11 where a=2 and b in (1,2) and c=2;
select * from t11 where a=1 and b=2 order by c;
select * from t11 where a=1 order by b,c;
select a,b,c from t11 order by a,b,c;
/*使用部分联合索引idx_a_b_c*/
select * from t11 where a=1 and b=1;
select * from t11 where a=1 and c=1;//索引a
select * from t11 where a=2 and b in (3,4) order by c; //索引ab
/*覆盖索引,不需要回表查询聚集索引中的记录*/
select b,c from t11 where a=3;
select c from t11 where a=1 and b=1 ;
select id from t11 where a=1 and b=1 and c=1;
/*不能使用联合索引*/
select * from t11 where b=1; //联合索引最左匹配
select * from t11 order by b;

优化器索引选择

show index可以看到索引的Cardinality，即索引中不重复记录数量的预估值。

Cardinality 统计信息的更新时机：

• 表中 1/16 的数据已经发生过变化
• 表中数据发生变化次数超过 2000000000

统计方法

随机取出 B+ 树索引中的 8 个叶子节点，统计每个页中不同记录的个数，计算得到每页的平均数后，乘以叶子节点总数

问题

通过统计信息来预估扫描行数，Cardinality不精准可能导致选错了索引。

应对

analyze table t13;//更新统计信息

问题

如果单次选取的数据量过大，可能也会导致“选错”索引

select a from t13 where a>70000 limit 1000;//走了主键索引

应对

force index 来强制走索引

select a from t13 force index(idx_a) where a>70000 limit 1000;