重看聚簇索引,非聚簇索引,索引覆盖,索引下推

1. 聚簇索引和非聚簇索引

聚簇索引, 叶子节点上就是数据行. 非聚簇索引, 叶子节点仍然是索引, 但是有指针指向数据

	聚簇索引	非聚簇索引
叶子节点	直接放了数据行	数据块的指针
插入顺序	数据行在物理存储空间的顺序和插入的主键顺序一致	数据行存储无序
存储引擎	适用于innodb	适用于myisam
每个表几个	因为和数据行的物理存储顺序相关, 所以只能有一个	可以多个
优点	1. 范围查询, 排序查询速度快. 因为本身数据行在物理存储就是有序的. 2. 由于主键索引的叶子节点和数据行在一起, 在查询到内存中时, 会以页为最小单位把页放到内存中, 再次访问同一页的相邻数据行时直接在内存中可以取到. 3. 在移动行的时候只需要维护主键索引, 辅助索引的叶子节点不用变
缺点	修改和维护索引昂贵, 一旦因为数据增删导致页分裂, 叶子节点调整变化, 物理存储地址也需要迁移

聚簇索引生成:

1. 有主键, 根据主键创建
2. 表没有主键, 根据一个唯一非空索引创建
3. 都没有, 创建一个隐式聚簇索引, 使用的是一个全局共享的row_id, 类型是bigint unsigned, 大小是6字节, 当溢出后会变成0

自增id在用完以后就不再变化,报主键冲突的错误

聚簇索引上建立的其他索引: 是辅助索引, 或者叫二级索引, 叶子节点保存的是主键

主键Id类型和二级索引的影响

1. 推荐主键id采用int类型自增, 因为它通常相比varchar, char等其他类型空间更小. (int4个字节, utf-8mb4 1个字符4个字节), 数字计算也比字符串计算简单. 具体看哪一种占用空间更小
2. 主键小一些的话,因为二级索引中保存主键, 也会减少二级索引的空间大小. 索引页越小, 加载到内存中的页也就越多. 在内存找到缓存的可能性就越大
3. 我们应该选择最小的数据范围，因为这样可以大大减少磁盘空间及磁盘I/0读写开销

uuid和自增id

由于聚簇索引的数据行存储和主键排序有关, 所以无序的uuid在插入的时候会导致更多的页分裂和数据行迁移, 从而导致io开销, 并且在排序和区间查询的时候性能远远不如自增排序的id

但是在业务中由于自增id可能会导致泄露数据量, 以及分布式系统中保证id唯一, 方便后期分库分表

所以如果需要用uuid, 可以将其作为业务id, 但是主键ID还是推荐用自增id

2. 覆盖索引和下推索引

索引覆盖

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表. 使用联合索引

回表

指: 在辅助索引找到主键后, 再回头拿主键去主键索引中查询对应的数据行, 多进行一遍io读取

前提

1. select的列和where列都必须在一棵索引树上
2. where条件的索引要符合最左前缀原则的条件, 如有个a-b-c的联合索引, 必须是a, 或a,b, 或a,b,c作为条件查询. 顺序不关心, mysql会优化

如果使用到了索引覆盖, explain执行计划中 extra的字段会返回Using index. 具体用sql演示如下

-- 对一个表 id,name,sex,age , 在name和sex建立联合索引. 这里有顺序的, 相当于是建立了name和name-sex两个索引.

-- select和where覆盖
explain select name from spring_demo.user where name = '2'; -- 使用了
-- select覆盖, where查第二个索引列
explain select name from spring_demo.user where sex = 1; -- 没有使用覆盖索引
-- select覆盖, where查第一个索引列
explain select sex from spring_demo.user where name = '2'; -- 使用了
-- select覆盖, where 按联合索引顺序查询,包含第一个索引列
explain select sex ,name from spring_demo.user where name = '2' and sex = 1; -- 使用了
-- select覆盖, where 不按联合索引顺序查询,但是包含第一个索引列
explain select sex ,name from spring_demo.user where sex = 1 and name = '2'  ; -- 使用了
-- select没有完全覆盖
explain select sex ,name,age from spring_demo.user where sex = 1 and name = '2'  ; -- 没有
-- where没有完全覆盖
explain select sex ,name from spring_demo.user where sex = 1 and name = '2' and age = 1  ; -- 没有

索引下推

索引下推是mysql5.6以后增加的优化

5.5以前. 如果要where要查询多个条件, 即便建立了联合索引, 也需要先根据其中一个条件回表查询主键索引, 再捞取出记录行来判断其他条件是否符合.

而索引下推, 会对一棵索引树中包含的where字段先筛选, 过滤掉不符合的数据, 减少回表查询的记录