上一篇博客已经简单从广的方面介绍了一下 MySQL 整体架构以及物理结构的内容.
本篇博客的内容是 MySQL 的索引, 索引无论是在面试还是我们日常工作中都是非常的重要一环.
索引是什么?
官方介绍索引是帮助 MySQL 高效获取数据的数据结构. 打个比方来说的话相当于我们生活中字典.
索引的优势和劣势
优势:
可以加快数据的检索速度, 降低磁盘的 IO, 提高查询效率.
索引列可以对数据进行排序, 减低 CPU 的消耗
劣势:
索引是需要占用磁盘空间的.
索引只是针对查询会提升性能. 对增删改反而会降低. 原因是因为要维护索引, 会产生磁盘 IO.
索引的分类
单列索引
普通索引: MySQL 中的基本索引类型, 只是为了查询快一些.
主键索引: MySQL 主键列上添加索引. 不允许有 null 和空值
唯一索引: 唯一列上添加索引, 允许有 null 和空值
组合索引
在同一张表里多个列上添加索引
需要遵循最左前缀原则
建议使用组合索引替代单列索引, 主键索引分情况.
索引的使用
索引的创建
首先说明我们有一张 user 表, 字段分别为主键 id,name,age.
单列普通索引
create index idx_name on user(name(10));
这里想说明一下, 我们在工作中对某个字段添加索引时, 目标字段由于是 varchar 类型, 可能比较长, 为了更好的维护索引和减少索引占用磁盘空间的大小, 我们可以在列后面加上索引的长度.
唯一索引
create unique index idx_id on user(id);
主键索引是唯一索引的特殊类型, 建议主键索引使用整数, 整数占用空间比较小. 同样可以为索引指定长度, 如果是 int 类型就不需要指定了.
组合索引
create index idx_id_name_age on user(id,name(10),age);
最左前缀原则:
说明一点我们创建了以上组合索引的时候, 相当于创建了是三个索引:
- id,name,age
- id,name
- id
- select * from user where age = 13 and id =1 and name = 'VN';
此时是否使用到了组合索引?
这种情况下是违反了最左前缀原则, 由于我们创建的索引的顺序是 id,name,age. 我们在使用组合索引的时候应该也要遵循这个顺序, 如果打乱顺序那么就会导致索引失效. 正确使用组合索引应该是以下 sql 语句:
select * from user where id = 1 and name = 'VN' and age = 13;
还有以下情况, 是否使用到了索引.
- select * from user where id =1 and name = 'VN';
- select * from user where id =1;
以上两条 sql 语句是正确使用了索引的, 因为组合索引也可以拆开使用, 但一定是有顺序的, 不能打乱, 从打乱索引顺序的时候开始, 往后的索引就是失效了.
切记: 如果索引顺序是以上情况, 直接拿 name, 或者 age 来用, 索引是失效的. 因为违背的最左前缀原则, 即使把组合索引拆开来用, 也一定是有序的.
删除索引
drop index idx_id_name_age on user;
查看索引
show index from user \G;
索引的数据结构
在开始具体说索引的数据结构前, 要说明一下, 因为索引是 MySQL 引擎中实现的, 所以不同的存储引擎有不同的实现. 由于现在 MySQL 中 InnoDB 是默认的数据库引擎, 并且我们大部分场景下使用的也是 InnoDB 引擎, 所以在索引的数据结构这里, 我们只针对于 InnoDB 引擎来说.
索引的数据结构是什么, 相信我们大家都知道是 B+tree, 可具体什么是 B+TREE 呢? B+TREE 长什么样子呢? B-TREE 和 B+TREE 的区别是什么? 这些问题大家就不一定都能回答上来了吧?
B-tree:(也叫做多路平衡树)
B+TREE:
以上两种是 b-tree 和 B+tree 的样子
b+tree 在 MyISAM 的实现:
MyISAM 非聚集索引. 非叶子节点只存放着指向具体的数据的地址值.
b+tree 在 InnoDB 的实现:
InnoDB 聚集索引, 非叶子节点存放有具体的数据.
b-tree 和 b+tree 的区别
b-tree 叶子节点也是存放数据的, 而 b+tree 只有非叶子节点存放数据, 叶子节点存放的都是指向下一个节点的指针.
b+tree 非叶子节点使用链表结构相邻的两个非叶子节点相连.
索引失效
在说索引失效之前, 不得不先说一下 explain 查看执行计划.
执行计划
MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句的执行计划进行分析, 并输出 SELECT 执行的
详细信息, 以供开发人员针对性优化.
使用 explain 这个命令来查看一个这些 SQL 语句的执行计划, 查看该 SQL 语句有没有使用上了索引, 有没
有做全表扫描, 这都可以通过 explain 命令来查看.
可以通过 explain 命令深入了解 MySQL 的基于开销的优化器, 还可以获得很多可能被优化器考虑到的访
问策略的细节, 以及当运行 SQL 语句时哪种策略预计会被优化器采用.
用法如下:
其中各列的含义如下:
id:select 查询的标识符, 每一个 select 有一个唯一的标识符. 标识查询的执行顺序.
id 相同, 执行顺序从上往下
id 不同, 如果是子查询, id 越大, 优先级越高
select_type:select 查询的类型.
simple: 简单的 select 查询
parmary: 一个 union 或者子查询的操作, 最外层的就是 parmary
union: 连接的两个都是查询, 第一个是派生表 dervied, 往后的都是 union
dependent union: 出现在连接查询中, 受外部查询影响
subquery: 除了 from 中的子查询, 其他地方的子查询
derived:from 中出现的子查询, 和之前提到的一样派生表
table: 查询的那张表.
如果使用了别名, 这里显示别名
如果出现了尖括号, 那说明是临时表
如果不涉及表的操作, 这里显示为 null
partitions: 匹配的分区.
type: 连接类型
性能从好到差排序
system: 只有一行数据或者是空表
const: 使用唯一索引或者主键
eq_ref: 出现在多表关联查询, 对于前表的每一个结果抖只能匹配到一条结果
ref: 非唯一索引, 使用了组合索引符合最左前缀
fulltext: 全文索引检索
ref_or_null:ref 类似
unique_subquery:where 中的 in 的子查询
index_subquery: 子查询 in 形式子查询使用到了辅助索引
range: 索引范围扫描
index_merge: 使用了两个以上的索引
index: 结果列中使用到了索引
index
ALL: 全表扫描
只有 all 没有使用到索引, 其他都使用到了索引
建议使用到 range 级别
possible_keys: 此次查询中可能选用的索引
key: 此次查询中确切使用到的索引.
ref: 哪个字段或常数与 key 一起被使用
rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
filtered: 表示此查询条件所过滤的数据的百分比
extra: 额外的信息
using index: 索引覆盖, 不需要回表扫描
using where: 对 storage engine 提取的结果进行过滤, 改字段没有索引
using filesort: 排序中没有使用到索引
using temporary: 使用了临时表存结果
索引下推
大家都知道 MySQL 架构分为了 server 层和引擎层. 索引下推也叫做 ICP.
如何处理 where 条件
index_key: 确定索引中的连续范围, 根据索引来确定范围
index_filter:index_key 确定了索引范围之后, 还有一部分不符合条件, 通过 index_filter 筛选
table_filter: 索引不能过滤的交给 table_filter, 也就是回表过滤
torage 层:
首先将 index key 条件满足的索引记录区间确定, 然后在索引上使用 index filter 进行过滤
将满足的 index filter 条件的索引记录才去回表取出整行记录返回 server 层
不满足 index filter 条件的索引记录丢弃, 不回表, 也不会返回 server 层
server 层:
对返回的数据, 使用 table filter 条件做最后的过滤.
使用 ICP 的好处:
直接去掉了不满足 index_filter 的记录, 避免了回表和传到 server 层
索引失效
违反了最左前缀原则会导致索引失效
索引上不要做计算, 会导致索引失效
范围条件右边的列索引失效
索引字段不要使用不等, 会导致索引失效
索引字段使用 is null 或者 not null 会导致索引失效
索引字段使用 or 会导致索引失效
关于索引的数据结构推荐大家一片博文, 讲解的会更全面, 本篇博客中一部分图片取自于该博客.
地址是: https://www.cnblogs.com/aligege/p/11589398.html
来源: https://www.cnblogs.com/sx-wuyj/p/12525064.html