MySQL索引:InnoDB 的索引机制
索引的种类
- 数据结构:B+树、Hash、Full-text
- 物理存储:聚簇索引、非聚簇索引
- 字段特性:Key、Unique、Index、Fulltext
- 字段个数:单列索引、联合索引
索引数据结构
索引就是搜索,将同一类事物 / 字段进行数据结构排列,方便查找
比如:B树的二分查找、hash的桶位路由查找等等
索引数据结构有:BST、AVLT、红黑树、B树、B+树
- 是否二分 BST
- 是否平衡
- 时刻平衡 AVL
- 惰性平衡 红黑树
索引的优缺点
优点:
- 条件下推到索引,可以提高数据检索效率
- 唯一性索引可以保证字段不重复
- 加快数据的分组和排序
缺点: - 存索引,需要占硬盘空间
- 创建索引开销
- 增删改操作,需要维护索引,维护开销
- 数据量越多,维护开销越大
- 普通索引/辅助索引(index类型索引)会导致频繁回表
- 左模糊查询,或者一些条件查询,会导致索引失效
- 虽然增加了查询开销,但是维护/增删改的性能都会下降
表空间和B+树索引空间
在innodb中的表数据文件是.idb文件,用来描述表空间
表空间是由多个B+树索引空间组成
每建立一个索引就会在表空间中建立一个辅助索引,
每个辅助索引都会对当前字段在表空间中建立一个对应列的B+树索引数据结构,
非聚簇索引就是辅助索引
非聚簇索引就是辅助索引,它不包含完整的表字段,只包含索引字段和主键
辅助索引又叫二级索引,分为普通索引 Index 和唯一索引 Unique
聚簇索引就是主键
在innodb中,主键索引和所有行字段,都是放在一起的
而这种索引数据结构是一种索引和行数据混合的存储模式
这个就是innodb的模式
不要使用隐藏的主键
在innodb引擎创建的表中,如果不设计主键列,还是会有主键
而且是自动递增主键,只不过这个主键是系统自动分配的,叫做:row_id
但是这个主键不会提高检索效率
而且因为是自动递增,导致删除之后会留下外存碎片,降低外存空间使用率
Index 和 Unique
辅助索引分为普通索引 Index 和唯一索引 unique,他们一个是按照平衡二分排序,一个是在平衡二分的基础上保证唯一不重复
它们除了在唯一性上不一样,在性能上也有差异:
查效率对比
查询首先是层序遍历B+树的树节点,路由到叶子节点,磁盘读取对应页到内存,之后维护内存指针读取记录,之后:
- Index在读内存页的字段记录中,如果读到了目标记录,还会继续移动指针读页内下一个记录;
如果恰好指针移动到了当页的最后一条记录,则会磁盘IO加载下一页;
这个过程直到读到第一个值不一样的记录,才会停止 - Unique在读内存页的字段记录中,如果读到目标记录,就直接返回一个主键id,结束读取,不会读下一个记录
所以在查效率上,唯一索引Unique效率更高
增效率对比
增加也是先层序遍历到叶子节点,之后读取一页到内存,之后遍历页内记录:
第二步,看当前内存中是否有对应页,如果缓存了对应页:
- 内存中有缓存页:
- Index:会找到合适的位置插入数据;
- Unique:会找到合适的位置,然后判断是否有冲突,如果无冲突再插入;
- 内存无缓存的目标页:e
- Index:将待插入的数据放入 change buffer
- Unique:将数据页加载到内存作为缓存页,然后按照有缓存页的方式进行插入;
其他效率
其他操作上都是差不多的,都是修改B+树结构的模式
综上,Unique在查询上的效率高于Index,而且唯一性也意味着唯一次数的回表;
但是Unique在插入的效率会差一些;
Innodb不支持的索引
不支持虚拟列索引和表达式索引,因为表达式和虚拟列需要计算,对于B+树这种结构来说,维护的计算代价过大
联合索引
联合索引又称组合索引、复合索引
优缺点
优点:
- 条件过滤的高效性
可以用联合索引一次性过滤多个条件,避免多个二级索引树的查找开销 - 索引覆盖,联合索引实现索引覆盖可以不用回表
缺点: - 联合的列数过多,会导致维护开销、增删改的成本增大
- 如果查询条件是 ‘或’ 则不会走联合索引,而是部分走索引
- 索引失效问题
最左前缀和最左匹配原则
联合索引是有顺序的,默认是按联合的第一列进行排序,之后在相同第一列的情况下,才会对之后的列进行排序,也就是,联合索引按列从左到右的顺序分别进行排序。
最左匹配原则是说,根据where条件去匹配单列或联合索引过程中,如果是可以走联合索引,是从最左开始匹配选择最合适的索引(索引覆盖率最高的索引进行匹配)
联合索引失效
- or
- LIKE '%···'
- where条件,跳过联合索引的最左前缀
- 最左前缀列的 范围查询(不等于、大于、小于)
- 最左前缀列的 IN 列表值过多 (通常小于10个)
失效的原因:
如果范围过多,就算走索引找到的记录也不是完整的记录,还是需要回表再查询,当范围过大,要访问索引之后再频繁回表,执行器会认为全表扫描的执行计划比走索引的效率更高
回表与辅助索引
索引下推(ICP)
索引下推(Index Condition Pushdown,ICP)是MySQL5.6的优化,改进了二级索引的扫描策略,可以减少服务层访问存储引擎的次数,以及减少存储引擎访问基表的次数。
ICP适用于 InnoDB 和 MyISAM ,可以减少回表次数
MySQL分层架构
- 服务层,也叫Server层,是用来解析SQL的语法语义,以及生成查询计划、接管从存储引擎层上推的数据进行二次过滤的分层
- 存储引擎层是按照Server层的请求,通过索引或者全表扫描的方式获取并返回数据的分层
配置索引下推
-- 查看索引下推是否开启
select @@optimizer_switch
-- 开启索引下推
set optimizer_switch="index_condition_pushdown=on"
-- 关闭索引下推
set optimizer_switch="index_condition_pushdown=off"
流程:无索引下推
无索引下推时,优化器不会将and条件连接成联合索引,比如现在有索引:
idx_age_name(age, name),查询条件是 age=18 and name="罗一"
此时执行器会根据最左前缀匹配原则,匹配到age字段,但是不会将name字段联合,导致在age_name索引树上匹配到合适的age,会忽略掉name字段,导致回表匹配name字段,最后返回的结果在服务层按照name="罗一"进行过滤
这会导致明明当前索引树有name字段,却还要回表查询name值,最后还在服务层进行条件过滤
流程:索引下推
会合并and条件,判断是否可以走联合索引,防止回表
索引下推失效
- 引用子查询条件,子查询不是存储引擎层执行的内容;
- 引用表达式条件,比如:
idx(col1+col2)
,因为表达式和函数,也不是存储引擎层Innodb所可以维护的索引,所以会导致服务层执行器制定执行计划时,会放弃对应字段的索引; - 虚拟列上的索引,比如函数索引
idx(dosomething(col))
回表
对应字段的索引找到了记录,但是因为辅助索引只能查到对应字段
所以如果待查询的字段存在一个或以上,不是辅助索引对应的字段,那么需要根据查到的主键进行回表
所谓回表就是回到聚簇索引(主键索引)中查询回表的主键对应的其他需要的字段
当然MyISAM、Memory之类的存储引擎必须要回表,因为他们的表数据跟索引不聚簇;
索引覆盖
当然,通过回表的定义可知,其实如果定义的辅助索引或者联合索引包含了所有需求的字段,那么就不需要回表,这种情况就是索引覆盖
自适应Hash索引
用户无法手动创建hash索引;
如果一个加了Hash索引的字段的某一项被查询若干次后,那么这条Hash索引的记录就可以被加载到缓存中的散列表,当再次访问这个条件时,就会从缓存中直接返回索引对应的查询到的主键
ORDER BY
Order By是mysql中的排序方法,是对存储引擎返回的结果集进行排序的方法
在使用Order By时,如果排序的顺序跟联合索引顺序不一致,就会导致只能部分走索引
现在假设索引是:idx_age_name(age, name)
:
1.部分走或失效
- 非最左匹配:
如果排序条件是:ORDER BY name, age
,此时只能部分走索引,走的是idx_age_name索引中age字段的部分索引; - DESC序:
如果顺序是DESC序,会导致对应列的索引失效
2.完全走联合索引
如果排序条件是:ORDER BY age, name
,此时可以完全走索引。
索引失效问题
普通索引失效
- 大范围查询 或 过多值的 IN 条件
- 范围查询返回结果占总行数的比例在 20% 左右可以认为还能走索引
- LIKE % 左模糊查询
- 无最左前缀条件的联合索引
- 排序操作 走部分索引和完全失效(desc序、非最左匹配)
- 子查询结果列作为 IN 列表
具体走不走索引可以通过 explain 查看执行计划判断
唯一性索引失效
- IN 条件中有重复值,违反唯一性索引
- 跟普通索引一样的失效条件
不建议加索引的情况
- 查询条件中,很少使用的字段可以不加索引
- 如果当前字段是枚举值、或者值的类型很少,并且分布很均匀,那么没必要加索引;但是如果某种值很少,那么也可以加索引
- 举个例子,假如某个字段只有1、2、3
如果1、2、3分布很均匀,那么可以不加索引;
如果某个字段1几乎没有,但是2、3很常见,那么建议加索引
- 举个例子,假如某个字段只有1、2、3