MySQL进阶(三)-索引篇

本文最后更新于:2024年4月22日 下午

MySQL索引概念,常见索引,聚簇索引,辅助索引,组合索引,唯一性索引

索引是数据库 高效获取数据数据结构,加快查询速度,索引一般存储在表空间中,也就是磁盘里

优势与劣势

优势:两降一升,降低磁盘IO频次、降低数据排序的成本,提高数据检索效率

劣势:占用更多磁盘空间(空间换时间),降低更新效率

索引操作

删除索引

DROP INDEX index_name ON table

查看索引

SHOW INDEX FROM table_name

常见可以创建 主键索引、唯一索引、普通索引、全文索引、前缀索引、组合索引

索引的数据结构

使用索引的基本需求

等值查询:根据某个值查找数据

范围查询:根据某个范围区间查找数据

排序Order By

分组Group By

可选的数据结构

Hash表,二叉树,平衡二叉查找树(红黑树是一个近似平衡二叉树),B树,B+树

Hash表

Hash表存储表数据Key可以存储索引列,Value可以存储行记录或者行磁盘地址。Hash表在等值查询时效率很高,时间复杂度为O(1)

二叉查找树

每个节点最多有2个分叉,左子树和右子树数据顺序左小右大

检索复杂度和树高相关:理想状态下效率可以达到O(logn)

红黑树

平衡二叉树是采用二分法思维,平衡二叉查找树除了具备二叉树的特点,最主要的特征是树的左右两个子树的层级最多相差1。在插入删除数据时通过左旋**/**右旋操作保持二叉树的平衡,不会出现左子树很高、右子树很矮的情况

缺点:

数据量大时候,时间更长

不支持范围查找

数据量大的时候,索引磁盘占用较大

B树改进二叉树,为多叉树

减少IO次数,减少树的高度。在每个节点尽可能多的存储数据。每个节点可以存储1000个索引(16k/16=1000),这样就将二叉树改造成了多叉树,通过增加树的叉树,将树从高瘦变为矮胖

特点:

  • B树的节点中存储着多个元素,每个节点内有多个分叉
  • 节点中的元素包含键值和数据,节点中的键值从大到小排列。也就是说,在所有的节点都储存数据
  • 父节点当中的元素不会出现在子节点中
  • 所有的叶子结点都位于同一层,叶节点具有相同的深度,叶节点之间没有指针连接

B+树改进B树,非叶子节点不存储数据

B+树和B树最主要的区别在于非叶子节点是否存储数据的问题

B+树只有叶子节点才会存储数据非叶子节点只存储键值。叶子节点之间使用双向指针连接,最底层的叶子节点形成了一个双向有序链表

特点:

  • 继承了B树的优点【多叉树的优点】

  • 保证等值和范围查询的快速查找

  • MySQL的索引就采用了B+树的数据结构。

存储引擎索引实现

MyISAM索引

MyISAM数据文件和索引文件分开存储,索引B+Tree数据结构,其中叶子节点Key为索引列值数据为所在行的磁盘地址,表索引存储在索引文件tablename.MYI中,数据文件存储在数据文tablename.MYD

主键索引

MyISAM查询时会将索引节点缓存在MySQL缓存中,而数据的缓存依赖于操作OS Cache

辅助索引

  • 主键索引必须唯一,辅助索引可以重复
  • 由于辅助索引重复了,所以即便是等值查询,也需要按照范围查询的方式在辅助索引树上查询数据

InnoDB索引

每个InnoDB表都有一个聚簇索引 ,也叫聚集索引。除了聚簇索引外的其他索引都叫辅助索引,聚簇索引是B+Tree数据结构,叶子节点存储数据行,非叶子节点存储主键值

一般情况下主键索引就是聚簇索引,但也存在没有主键的情况,没有主键会采用ROWID构建聚簇索引

InnoDB的表数据和索引默认存储在一个文件tablename.ibd

主键索引

  • InnoDB要求表必须有主键索引
  • 主键索引叶子节点存储数据行辅助索引只会存储主键值
  • 底层叶子节点按照顺序排序

辅助索引:

  • InnoDB的辅助索引只会存储主键值而非磁盘地址(重点:MyISAM存储的就是磁盘地址)
  • 除聚簇索引之外的所有索引都称为辅助索引
  • 辅助索引查询记录必然经过主键索引:首先查辅助索引获取主键,根据主键在主键索引查询获得记录(回表操作)
  • 叶子节点按顺序排序

组合索引

​ 表t_multiple_index,id为主键列,创建了一个联合索引idx_abc(a,b,c),构建的B+树索引结构如图所示。索引树中节点中的索引项按照(a,b,c)的顺序从大到小排列,先按照a列排序,a列相同时按照b列排序,b列相同按照c列排序。在最底层的叶子节点中,如果两个索引项的a,b,c三列都相同,索引项按照主键id排序

最左前缀匹配原则

组合索引的最左前缀匹配原则:使用组合索引查询时,mysql会一直向右匹配直至遇到范围查询(><betweenlike)就停止匹配

  • 在组合索引树中,最底层的叶子节点按照第一列a列从左到右递增排列,但是b列和c列是无序的,b列只有在a列值相等的情况下小范围内递增有序,而c列只能在a,b两列相等的情况下小范围内递增有序

能使用索引的情况

select * from t_multiple_index where a=13;
select * from t_multiple_index where a=13 and b=16;
select * from t_multiple_index where a=13 and b=16 and c=4;
select * from t_multiple_index where a=13 and b>13;
select * from t_multiple_index where a>11 and b=14;
select * from t_multiple_index where a=16 and c=4;

没有用到索引的情况

select * from t_multiple_index where b=16 and c=4;
select * from t_multiple_index where c=4;

总结:创建的**idx_abc(a,b,c)**索引,相当于创建了(a)(a,b)(a,b,c)三个索引

注意事项:

书写SQL条件的顺序,不一定是执行时候的where条件顺序。优化器会帮助我们优化成索引可以识别的形式

select * from t_multiple_index where b=16 and c=4 and a=13;
#等价于下面的sql,优化器会按照索引的顺序优化
select * from t_multiple_index where a=13 and b=16 and c=4;

覆盖索引:

select中列数据如果可以直接在辅助索引树上全部获取,也就是说索引树已经“覆盖”了我们的查询需求,这时MySQL就不会白费力气的回表查询,这中现象就是覆盖索引

使用explain工具查看执行计划,可以看到extra中“Using index”,代表使用了覆盖索引

索引条件下推ICP

是MySQL5.6对使用索引从表中检索行的一种优化。ICP可以减少存储引擎必须访问基表的次数以及MySQL服务器必须访问存储引擎的次数。可用于 InnoDB 和 MyISAM 表,对于InnoDB表ICP仅用于辅助索引

#optimizer_switch优化相关参数开关
mysql> show VARIABLES like 'optimizer_switch';
#关闭ICP
SET optimizer_switch = 'index_condition_pushdown=off';
#开启ICP
SET optimizer_switch = 'index_condition_pushdown=on';

作用

  • 不使用ICP,不满足最左前缀的索引条件的比较是在Server层进行的,非索引条件的比较是在Server层进行的
  • 使用ICP,所有的索引条件的比较是在存储引擎层进行的,非索引条件的比较是在Server层进行的

减少回表次数及减少IO次数

举例说明

select * from t_multiple_index where a=13 and b>15 and c='5' and d='pdf';

abc组合索引

这里b>15阻断组合索引使用

没使用索引下推情况

从索引找出满足a=13,b>15的数据,然后再通过id 回表找出这部分数据,再到MySQL的server层进行数据过滤

使用索引下推情况

从索引找出满足a=13,b>15的数据,发现c其实也在组合索引中,这个时候会找出c=’5’的数据主键Id,进行回表,再到MySQL的server层进行数据过滤

创建索引的原则

  • 频繁出现在where 条件字段,order排序,group by分组字段

  • select 频繁查询的列,考虑是否需要创建联合索引(覆盖索引,不回表)

  • 多表join关联查询,on字段两边的字段都要创建索引

索引优化建议

  1. 表记录很少不需创建索引
  2. 一个表的索引个数不能过多
  3. 频繁更新的字段不建议作为索引
  4. 区分度低的字段,不建议建索引
  5. 在InnoDB存储引擎中,主键索引建议使用自增的长整型,避免使用很长的字段
  6. 不建议用无序的值作为索引
  7. 尽量创建组合索引,而不是单列索引

MySQL进阶(三)-索引篇
https://hyq965672903.gitee.io/posts/31dcd61.html
作者
灼华
发布于
2023年4月2日
许可协议