”种一棵树最好是十年前,其次是现在“,结合个人十多年IT基础架构领域摸爬滚打的经验来看,数据库领域潜力无限,大有可为。运维领域知识面需要广,更需要专,数据库是我选择做专做深的方向。
本篇文章以结构化的方式呈现mysql进阶知识的体系框架,并且通过通俗易懂的语言,方便大家更好的阅读和理解。
索引知识概览
![索引](https://i-blog.csdnimg.cn/blog_migrate/a3452851676e7716b811ac4434b717f7.png)
概述
索引是帮助mysql高效获取数据的一种数据结构
结构
mysql的索引在存储引擎层实现,不同的引擎支持不同的索引结构
-
B+Tree
常见的索引结构,大部分引擎都支持
-
Hash
底层使用hash算法实现,只有精确匹配索引列的查询才有效,不支持范围查询
-
B-Tree
是MyISAM使用的一种特殊索引结构类型,主要用于地理空间数据类型,使用较少
-
Full-text
通过建立倒排索引,快速匹配文档的方式
![索引结构](https://i-blog.csdnimg.cn/blog_migrate/c443c44bfcb4f315df5cff165fbcf72c.png)
-
二叉树
![二叉树](https://i-blog.csdnimg.cn/blog_migrate/c5cfe74048e1e2e9545b6b8e0afc4a54.png)
![红黑树](https://i-blog.csdnimg.cn/blog_migrate/6ba2e21be90d3bb1cee20c694c88e0fb.png)
-
B-Tree
为了解决上述问题,可以使用 B-Tree 结构。
B-Tree (多路平衡查找树) 以一棵最大度数(max-degree,指一个节点的子节点个数)为5(5阶)的 b-tree 为例(每个节点最多存储4个key,5个指针)
![B-Tree](https://i-blog.csdnimg.cn/blog_migrate/e907432f48358f868faaa536b36f11ba.png)
演示地址:https://www.cs.usfca.edu/~galles/visualization/BTree.html
-
B+Tree
![B+Tree](https://i-blog.csdnimg.cn/blog_migrate/0dad4a6e0614de8c1fc0f9be7f743c9b.png)
演示地址:https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
与B-Tree的区别:
-
所有数据都存放在叶子节点 -
叶子节点形成一个单向链表
MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的 B+Tree,提高区间访问的性能。
![Mysql优化后的B+Tree](https://i-blog.csdnimg.cn/blog_migrate/ba208e856267ab50f4b4e0149aae9cb2.png)
-
Hash
哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在hash表中。 如果两个(或多个)键值,映射到一个相同的槽位上,他们就产生了hash冲突(也称为hash碰撞),可以通过链表来解决。
![Hash](https://i-blog.csdnimg.cn/blog_migrate/8bc220fab9de2de6a5625bbcb4e8aa39.png)
特点:
-
Hash索引只能用于对等比较(=、in),不支持范围查询(betwwn、>、<、…)
-
无法利用索引完成排序操作
-
查询效率高,通常只需要一次检索就可以了,效率通常要高于 B+Tree 索引
面试题
-
为什么Innodb选择使用B+Tree索引结构
-
相对于B-Tree,层级更少,搜索效率更高
-
对于B-Tree,无论是叶子还是非叶子节点,都要存储指针和数据。每页存放的键值和指针都比B+Tree少,相同数据量情况下,必将增加数的高度,从而降低了性能
-
对于Hash索引,B+Tree支持范围匹配和排序操作
-
分类
![索引分类](https://i-blog.csdnimg.cn/blog_migrate/2e835b8b403bc9c30ff7e7293ebf880e.png)
在Innodb存储引擎中,根据索引的存储形式,可以分为两种:
![索引存储形式分类](https://i-blog.csdnimg.cn/blog_migrate/96a055fd665ef3f69e11d12187404950.png)
聚集索引&&二级索引演示
聚集索引存放行数据,二级索引只存放主键ID
![聚集索引&&二级索引](https://i-blog.csdnimg.cn/blog_migrate/8831faeaec63d840e167060feb896944.png)
查询案例原理剖析
select * from user where name = 'Arm';
![查询原理演示](https://i-blog.csdnimg.cn/blog_migrate/01a32215a4e716b7b823286a155c8beb.png)
-
先通过二级索引查询到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;
作者:谷会于(转载请获本人授权,并注明作者与出处)
本文由 mdnice 多平台发布