4.2 MySql索引的深入了解
2021-04-22 21:35:40 0 举报AI智能生成
🔸自己整理的老师PPT
作者其他创作
大纲/内容
索引的基础知识
索引可以加快数据库的检索速度
表经常进行INSERT/UPDATE/DELETE操作就不要建立索引了
换言之
索引会降低插入、删除、修改等维护任务的速度
索引需要占物理和数据空间
了解过索引的最左匹配原则
索引
物理空间
索引空间
聚集索引和非聚集索引
索引
聚类索引
非聚类索引
MySQL支持这两种索引
Hash索引
哈希索引
B+树索引
B+树索引
各个数据页组成一个双向链表
而每个数据页中的记录又组成一个单向链表
每个数据页都会为存储在它里面的记录生成一个页目录
在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽
然后再遍历该槽对应分组中的记录即可快速找到指定的记录
以其他列(非主键)作为搜索条件
只能从最小记录开始依次遍历单链表中的每条记录
select * from user where username = 'Java3y'
定位到记录所在的页
需要遍历双向链表，找到所在的页
从所在的页内中查找相应的记录
由于不是根据主键查询
只能遍历所在页的单链表了
在数据量很大的情况下这样查找会很慢！
深入了解
使用索引为什么可以加快数据库的检索速度？
为什么说索引会降低插入、删除、修改等维护任务的速度
索引的最左匹配原则指的是什么？
Hash索引和B+树索引有什么区别？主流的使用哪一个比较多？InnoDB存储都支持吗？
聚集索引和非聚集索引有什么区别？
索引提高检索速度
将无序的数据变成有序(相对)
要找到id为8的记录简要步骤
没有用索引我们是需要遍历双向链表来定位对应的页
现在通过“目录”就可以很快地定位到对应的页上了
索引降低增删改的速度
B+树是一种平衡树
平衡树
它是一棵空树
或它的左右两个子树的高度差的绝对值不超过1
并且左右两个子树都是一棵平衡二叉树
如果一棵普通的树在极端的情况下
是能退化成链表的
(树的优点就不复存在了)
B+树是平衡树的一种
不会退化成链表
树的高度都是相对比较低的
(基本符合矮矮胖胖(均衡)的结构)
【这样一来我们检索的时间复杂度就是O(logn)】
建立索引实际上就是建立一颗B+树
B+树是一颗平衡树
如果我们对这颗树增删改的话
那肯定会破坏它的原有结构
要维持平衡树
就必须做额外的工作
正因为这些额外的工作开销
导致索引会降低增删改
Hash索引
除了B+树之外
还有一种常见的是哈希索引
哈希索引就是采用一定的哈希算法
作法
把键值换算成新的哈希值
检索时不需要类似B+树那样从根节点到叶子节点逐级查找
只需一次哈希算法即可立刻定位到相应的位置
速度非常快
本质上就是把键值换算成新的哈希值
根据这个哈希值来定位
哈希索引有好几个局限
哈希索引也没办法利用索引完成排序
不支持最左匹配原则
在有大量重复键值情况下
哈希索引的效率也是极低的
---->哈希碰撞问题
不支持范围查询
https://www.cnblogs.com/zengkefu/p/5647279.html
InnoDB引擎支持hash索引吗？
从上面的图中可以得知
MySQL 是支持Hash索引的
但支持和不支持又和具体的存储引擎有关系
从图中，看到InnoDB是支持BTree索引
InnoDB存储引擎是支持Hash索引的
不过，我们必须启用
Hash索引的创建由InnoDB存储引擎引擎自动优化创建
我们干预不了
MySQL技术内幕InnoDB存储引擎
聚集和非聚集索引
简单概括
聚集索引就是以主键创建的索引
非聚集索引就是以非主键创建的索引
【二级索引】
区别
聚集索引在叶子节点存储的是表中的数据
非聚集索引在叶子节点存储的是主键和索引列
使用非聚集索引查询出数据时
拿到叶子上的主键再去查到想要查找的数据
(拿到主键再查找这个过程叫做回表)
聚集索引
数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同
一个表中只能拥有一个聚集索引
第一列的地址表示该行数据在磁盘中的物理地址
后面三列才是我们SQL里面用的表里的列
其中id是主键，建立了聚集索引
结合左边的表格就能理解
数据行的物理顺序与列值的顺序相同
如果我们查询id比较靠后的数据
那么这行数据的地址在磁盘中的物理地址也会比较靠后
而且由于物理排列方式与聚集索引的顺序相同
所以只能建立一个聚集索引
从左图可以看出聚集索引的好处
索引的叶子节点就是对应的数据节点
可以直接获取到对应的全部列的数据
而非聚集索引在索引没有覆盖到对应的列的时候需要进行二次查询
因此在查询方面
聚集索引的速度往往会更占优势
如果不创建索引
系统会自动创建一个隐含列作为表的聚集索引
非聚集索引
该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同
一个表中可以拥有多个非聚集索引
除了聚集索引以外的索引都是非聚集索引
非聚集索引就像新华字典的偏旁字典
他结构顺序与实际存放顺序不一定一致
非聚集索引的二次查询问题
非聚集索引叶节点仍然是索引节点
只是有一个指针指向对应的数据块
如果使用非聚集索引查询
而查询列中包含了其他该索引没有覆盖的列
那么他还要进行第二次的查询
查询节点上对应的数据行的数据
这就是回表
其中有 聚集索引clustered index(id), 非聚集索引index(username)
使用以下语句进行查询
不需要进行二次查询
直接就可以从非聚集索引的节点里面就可以获取到查询列的数据
但是使用以下语句进行查询
就需要二次的查询去获取原数据行的score
聚集和非聚集索引
索引是通过二叉树的形式进行描述的
我们可以这样区分聚集与非聚集索引的区别
聚集索引的叶节点就是最终的数据节点
而非聚集索引的叶节点仍然是索引节点
但它有一个指向最终数据的指针
覆盖索引
前面已经知道
如果不是聚集索引
叶子节点存储的是主键+列值
最终还是要“回表”
也就是要通过主键再查找一次
这样就会比较慢
覆盖索引就是把要查询出的列和索引是对应的
不做回表操作！
最左匹配原则
索引可以简单如一个列(a)
也可以复杂如多个列(a, b, c, d)
即联合索引
如果是联合索引
那么key也由多个列组成
同时
索引只能用于查找key是否存在（相等）
遇到范围查询(>、<、between、like左匹配)等
就不能进一步匹配了
后续退化为线性查找
列的排列顺序决定了可命中索引的列数
如有索引(a, b, c, d)
查询条件a = 1 and b = 2 and c > 3 and d = 4
则会在每个节点依次命中a、b、c
无法命中d
(很简单：索引命中只能是相等的情况，不能是范围匹配)
=、in自动优化顺序
索引总结
最左前缀匹配原则
非常重要的原则
MySQL会一直向右匹配直到遇到范围查询（>,<,BETWEEN,LIKE）
就停止匹配
尽量选择区分度高的列作为索引
区分度的公式是 COUNT(DISTINCT col) / COUNT(*)
表示字段不重复的比率
比率越大我们扫描的记录数就越少
索引列不能参与计算
尽量保持列“干净”
比如
FROM_UNIXTIME(create_time) = '2016-06-06'
就不能使用索引
原因很简单
B+树中存储的都是数据表中的字段值
但是进行检索时
需要把所有元素都应用函数才能比较
显然这样的代价太大
所以语句要写成：create_time = UNIX_TIMESTAMP('2016-06-06')
尽可能的扩展索引
不要新建立索引
比如表中已经有了a的索引
现在要加（a,b）的索引
那么只需要修改原来的索引即可
单个多列组合索引
和多个单列索引的检索
查询效果不同
因为在执行SQL时
MySQL只能使用一个索引
会从多个单列索引中
选择一个限制最为严格的索引
0 条评论
下一页
