4.2 KiB
| layout | title | categories | tags | |
|---|---|---|---|---|
| pages | MySQL InnoDB 中的索引覆盖 | 杂谈 |
|
什么是索引覆盖
新建一张表的时候，必须有一个主键。为什么必须呢？因为 InnoDB 存储引擎中，表里的数据就存在主键索引这颗B+树的叶子节点里。
而其他索引的叶子节点，存储的主要是主键id，是不包括这行的全量数据的。
使用普通索引查询数据的时候，走到叶子节点，也只能拿到 主键id 和 索引字段。还需要根据主键id再走一遍主键索引，才能拿到整行的数据。
什么是索引覆盖呢？ 就是 select 的时候，如果只 select 主键id 和 当前查询使用的索引树对应的索引字段。就不需要回到主键索引里拿整行数据了。
错误的应用
经常在维护的代码里见到不用 select *，只 select 具体字段 的情况。 那些人是出于什么考虑，一个简单的星号不写，费劲去把字段一个个列出来呢？
也许是为了省带宽？
也许是觉得少几个字段能减少反序列化的开销？
也许最大的原因，是学到了 索引覆盖 这个知识点。就开始滥用了。。。
我是推荐尽量使用 select * 的。dao 层对外返回的数据，应该尽量是全量的。整体的模型，应该是越底层，越接近数据层，提供的数据字段越全面。越上层，越接近逻辑层、业务层，取用的字段越精细。
我们的业务在不断变化，需求在不停的提出。假如某天要给表增加一个字段。不用 select * 就得挨个 sql 改一遍。
更严重的情况不是给表增加字段。而是这个字段在表里原本就有，业务层原本没有使用。现在要用了，必然会出现直接在业务层代码里直接取用的操作，然而 dao 层根本没给返回值对象里设置这个字段的值。只能拿到null，等到自测、测试、甚至是上线后才发现。
在这个案例里，可维护性才是最重要的。真的不在乎那点计算资源的损耗。
恰当应用的例子
使用 offset 关键字在 mysql 里执行分页操作。页数越大，性能越差。
什么原因呢？
跳出 mysql 的范畴，我们设计一个数据库的粗浅架构。可以分为 计算层 和 数据层。offset 的操作被划分在了计算层，数据层在执行 where 条件 筛选数据的时候并不知道哪些数据是该 offset 跳过的。
于是在查询 每页十条，第十页的数据 的时候，使用了 select *。数据层就只能查出一百条数据，并且一百条数据都需要回到主键索引去查出全量数据。走到主键索引的叶子节点这个步骤，大概率是要走磁盘IO的。一百条完整数据交给计算层后，才开始去做跳过前九页的操作。
mysql 不去做针对 offset 的优化。只能是我们使用者换个使用方式。把 sql 分成两条，第一条与原 sql 几乎一致，只把 select * 改成 select 主键id。第二条 sql 再根据 主键id 查全量数据。分两条 sql 操作，就避免了前九页数据的回主键索引走磁盘IO了。
如果要严守 sql 只 select * 的规范，可以把两条 sql 用子查询、join 的方式合并成一条。（这里是为了实现技术目的使用的join，并且join的驱动集非常小。尽量不要为了业务目的使用join。）
针对 offset，推荐另一个方案
另一个处理 offset 问题的方案，是不用 sql 自带的 offset。由使用者增加一个 where 条件：（升序时） where sort_field > 上一页最后一个 sort_field 的值。
这也是变相的 “计算向数据转移”。把 offset 的计算转换成了 where 条件，where 条件是在数据层执行的。这个思想在大数据中常见。
说到大数据，假设 mysql 将来要平滑替换到 分布式的 TiDB。数据层肯定是分片的。类似于 mysql 中分表了，又没有分页规则，只能每张表都执行一遍。分页的场景下，要取 每页十条，第十页的数据，每个分片必须返回前一百条数据，才能保证拿到完整的第十页数据。把 offset 转换成 where 条件，每个分片就只需要各自的第十页数据就能拼出整体的第十页。
- 文章目录 {:toc}