Mysql5.7

两类索引

聚簇索引：

  • 如果表设置了主键，主键就是聚簇索引
  • 如果没有主键，默认第一个 NOT NULL，且 UNIQUE 的列作为聚簇索引
  • 都没有，默认创建一个隐藏的 row_id 作为聚簇索引

InnoDB聚簇索引的叶子结点存储的是行记录（页结构，一个页包含多行数据）
InnoDB 有且仅有一个聚簇索引

普通索引：

  • 也叫二级索引，非聚簇索引
  • InnoDB普通索引叶子结点存储的是主键值(聚簇索引)，MyISAM 普通索引存储的是记录指针

查找过程

聚簇索引

如果查询条件为主键（聚簇索引），只需扫描一次 B+树，即可定位到行数据

select * from user where id=1;

普通索引

如果查询条件为普通索引（非聚簇索引），并且查询数据 不只有 主键和索引列，则需要扫描两次 B+树
第一次扫描定位到聚簇索引的值，
第二次扫描通过聚簇索引找到行记录，获取到其他列数据。

select * from user where age=30;

这两次扫描就是回表查询

如果 只查找 主键与普通索引列，就直接从索引中取数据，不需要回表查询其他列数据

select id, age from user where age=30;

此时 explain 的 Extra 会显示 Using Index。

防止回表查询

建立联合索引

将被查询的字段，与主键建立联合索引

如实现：select id, age, name from user where age=10;
创建联合索引：
create index idx_age_name on user (age, name);

修改主键格式

比如用户帖子，主键格式可以是 用户唯一标识+压缩后的秒级时间戳+用户设备序号

这里压缩秒级时间戳可以设计为：

  • 帖子发布时间减去系统初次发布时间，再进行36进制编码

这样设计可以利用数据库的 范围查询

查询某个用户的帖子的 SQL：
select * from post where postId between postId1 and postId2;

或者

select * from post where postId like “用户唯一标识%”

这样直接查询主键就可以避免回表，
随着帖子数量增加，还可以根据 postId 中的 userid 水平拆分