两年

V1

2022/09/19阅读:21主题:橙心

mysql进阶知识-索引1

”种一棵树最好是十年前,其次是现在“,结合个人十多年IT基础架构领域摸爬滚打的经验来看,数据库领域潜力无限,大有可为。运维领域知识面需要广,更需要专,数据库是我选择做专做深的方向。

本篇文章以结构化的方式呈现mysql进阶知识的体系框架,并且通过通俗易懂的语言,方便大家更好的阅读和理解。

索引知识概览

索引
索引

概述

索引是帮助mysql高效获取数据的一种数据结构

结构

mysql的索引在存储引擎层实现,不同的引擎支持不同的索引结构

  • B+Tree

常见的索引结构,大部分引擎都支持

  • Hash

底层使用hash算法实现,只有精确匹配索引列的查询才有效,不支持范围查询

  • B-Tree

是MyISAM使用的一种特殊索引结构类型,主要用于地理空间数据类型,使用较少

  • Full-text

通过建立倒排索引,快速匹配文档的方式

索引结构
索引结构
  • 二叉树
二叉树
二叉树
红黑树
红黑树
  • B-Tree

为了解决上述问题,可以使用 B-Tree 结构。

B-Tree (多路平衡查找树) 以一棵最大度数(max-degree,指一个节点的子节点个数)为5(5阶)的 b-tree 为例(每个节点最多存储4个key,5个指针)

B-Tree
B-Tree

演示地址:https://www.cs.usfca.edu/~galles/visualization/BTree.html

  • B+Tree
B+Tree
B+Tree

演示地址:https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

与B-Tree的区别:

  • 所有数据都存放在叶子节点
  • 叶子节点形成一个单向链表

MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的 B+Tree,提高区间访问的性能。

Mysql优化后的B+Tree
Mysql优化后的B+Tree
  • Hash

哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在hash表中。 如果两个(或多个)键值,映射到一个相同的槽位上,他们就产生了hash冲突(也称为hash碰撞),可以通过链表来解决。

Hash
Hash

特点:

  • Hash索引只能用于对等比较(=、in),不支持范围查询(betwwn、>、<、…)

  • 无法利用索引完成排序操作

  • 查询效率高,通常只需要一次检索就可以了,效率通常要高于 B+Tree 索引

面试题

  1. 为什么Innodb选择使用B+Tree索引结构

    • 相对于B-Tree,层级更少,搜索效率更高

    • 对于B-Tree,无论是叶子还是非叶子节点,都要存储指针和数据。每页存放的键值和指针都比B+Tree少,相同数据量情况下,必将增加数的高度,从而降低了性能

    • 对于Hash索引,B+Tree支持范围匹配和排序操作

分类

索引分类
索引分类

在Innodb存储引擎中,根据索引的存储形式,可以分为两种:

索引存储形式分类
索引存储形式分类

聚集索引&&二级索引演示

聚集索引存放行数据,二级索引只存放主键ID

聚集索引&&二级索引
聚集索引&&二级索引

查询案例原理剖析

select * from user where name = 'Arm';
查询原理演示
查询原理演示
  1. 先通过二级索引查询到ID,再通过回表查询聚集索引查询到该ID对应的行数据

聚集索引选取规则:

  • 如果有主键,则选取主键索引作为聚集索引
  • 如果没有主键,则选择第一个唯一索引(unique)作为聚集索引
  • 如果两个都没有,则Innodb会生成一个rowid作为隐藏的聚集索引

语法

#创建索引:

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name, ...);

#如果不加 CREATE 后面不加索引类型参数,则创建的是常规索引

#查看索引:

SHOW INDEX FROM table_name;

#删除索引:

DROP INDEX index_name ON table_name;   

需求案例:


-- name字段为姓名字段,该字段的值可能会重复,为该字段创建索引
create index idx_user_name on tb_user(name);

-- phone手机号字段的值非空,且唯一,为该字段创建唯一索引
create unique index idx_user_phone on tb_user (phone);

-- 为profession, age, status创建联合索引
create index idx_user_pro_age_stat on tb_user(profession, age, status);

-- 为email建立合适的索引来提升查询效率
create index idx_user_email on tb_user(email);

-- 删除索引
drop index idx_user_email on tb_user;

作者:谷会于(转载请获本人授权,并注明作者与出处)

分类:

后端

标签:

数据库

作者介绍

两年
V1