从历史记录表中思考索引的使用

[liudaac]

问题围绕下面历史记录表结构展开

CREATE TABLE user_data (
userid INT,
event_date DATE,
KEY userid_index (userid),
KEY userid_event_date_index (userid, event_date)
);

概览表结构

• 这个表结构其实是项目中比较常见的，比如用户事件的记录、订单对账记录等。这一类表标准的写多读少，且数据量大，在项目中的操作以Insert为主，没有主键甚至没有唯一索引。
• 表中声明的两个索引均不做唯一性约束，那么它的聚簇索引是什么呢？有人会疑虑似乎聚簇索引要么是主键，要么可能是第一个不允许为 null 的唯一索引，显然这两者都不存在。InnoDB 会选择内置 6 字节长的 ROWID 作为隐含的聚集索引，来构成其他索引回表使用的B+树聚簇索引。

写入分析

• 锁的使用。Innodb在事务控制上只有两种选择，自动提交和手动提交。也就是在写入操作即便没有显式的声明开启事务，Innodb也会自动开启事务，并在完成完成单条写操作后自动提交事务。事务的控制就需要考虑到锁的占用，常用的事务隔离级别主要是RR（可重复读）和RC（读已提交）。

RR为了保证可重复读，会在读过程增加排它锁，对于唯一索引会增加Gap锁，普通索引会开启临键锁，在索引未查询到的情况下，会降级成Gap锁，这些都会影响表的写入性能。RC在Mysql 5.15之后已经通过binlog的statement到statement、row、mixed的扩展，使用row解决了statement格式下主从不一致的问题，在Insert过程不会额外增加排它锁。所以在大数据表中，会建议调整成RC，可以保证写入性能，同时我们认为读已提交造成的不可重复读并不是问题。

• 自增主键。如果存在自增主键，会增加Auto_Inc锁。另外就是会造成唯一索引，影响见后面。
• 唯一索引。为何此处声明为普通索引，没有主键没有唯一性约束。由于在写入过程中，如果是普通索引，可以应用ChangeBuffer机制，内存执行后即返回，等下次查询该数据页时，先从数据盘load回内存再进行与ChangeBuffer的Merge。这种在写多读少的场景下对写入性能会有极大的提升。而如果增加了唯一性约束，那么就无法使用ChangeBuffer，而是要从数据盘拉取到索引命中的数据页，先做唯一性校验。

综上，在此处的场景中，只声明了两个普通索引。在保证写入性能的条件下，同时可以保证读性能。

查询分析

• 读过程。需要先分析Mysql中执行过程

连接器。负责连接鉴权。

查询缓存。相对鸡肋，数据有变更，都会造成缓存成片失效，mysql8已经关闭该功能。

分析器。词法分析和语法分析。

优化器。执行计划，索引选择。选择索引参考两个方面，①扫描行数，估算区分度，即一个索引上不同的值越多区分度越高，从N个数据页中取平均*总页数，当超过1/M变更则重新统计。有两种估算方式，持久化存储N20,M10和内存存储N8,M16。②回表的代价。索引查询后如果无法覆盖查询字段，则需要从聚簇索引中查询详细的字段信息，也就是回表。这两个因素会决定执行层选择哪个索引。

执行器。操作存储引擎，返回结果。（如果是写入会有些不同，涉及到内存加载、redo_log两阶段提交和binlog。）

存储引擎。

• B+树索引中普通索引的结构。联合索引有几条规则要遵守

①覆盖索引，对于高频请求将结果和查询字段建立联合索引，空间换时间

②排序。索引项的顺序是按字段的先后顺序排序的

③最左前缀。比如(b,a)可以用b或ba但不能用a查询，需要单独建立a索引

④索引下推。Innodb默认提供的，复杂条件下，先进行索引过滤，然后再对剩余项扫描

⑤联合索引。最终数据页会按主键排序，影响按序查询。

那么基本可以得出结果，理想状态下
select event_date from user_data where userid=x，select * from user_data where userid=x 命中userid_event_date_index联合索引，此时不用回表。
select userid from user_data where userid=x order by event_date desc 命中userid_event_date_index联合索引，由于联合索引的排序。
select userid from user_data_test where userid>1 and userid<3命中userid_index。
select event_date from user_data where event_date=y无法命中索引。