2022年3月30日记:Linux服务器开发,MySQL索引原理以及SQL优化(2)
────────────────────────────────────
┌————————————┐
│▉▉♥♥♥♥♥♥♥♥ 99% │ ♥❤ 鱼沈雁杳天涯路,始信人间别离苦。
└————————————┘
对你的感情正在充电中,请稍侯…
────────────────────────────────────
推荐一个 零声学院 免费公开课程,个人觉得老师讲得不错,分享给大家:[Linux,Nginx,ZeroMQ,MySQL,Redis,fastdfs,MongoDB,ZK,流媒体,CDN,P2P,K8S,Docker,TCP/IP,协程,DPDK等技术内容,立即学习]
https
MySQL索引原理以及SQL优化
- 前言
- 一、索引
- 二、B+树数据结构
-
- mysql为什们性能强这与缓存设计有很大的关系。
- LRU算法
- 三、最左匹配原则
- 四、索引失效
- 五、创建索引原则
- 六、定位解决问题
- 总结
前言
今天学习的目的,主要是加强对mysql内部的理解。所为温故而知新,可以为师矣。希望通过本节的学习,能达到深入理解B+树这种数据结构,mysql运行机制的效果。
一、索引
索引是一个物理的概念,而约束是一个逻辑的概念。
主键(primary key)和唯一(unique key)的区别在于唯一索引允许有一个null值,主键不能为空且是唯一的。
create table parent(id int not null,parimary key(id)) engine =innodb;create table child(id int,parent_id int,foreign key(parent_id) references parent(id) ON DELETE CASCADE ON UPDATE CASADE) engine =innodb;
- CASADE 父子表同样行为
- SET NULL 更新子表设置为空
- NO ACTION 父表相同行为报错
- RESTRICT 同上
**外键有联动效果,容易产生bug,所以不建议使用外键。
二、B+树数据结构
通过比较key来保证有序的。
B+树全称为多路平衡搜索树,换句话说,按照中序遍历是一个有序的结构。平衡的是树的高度,提供一个稳定的搜索时间复杂度。
叶子节点的高度一样,每条链路的高度一致。
非叶子节点只存储比较信息。
相邻的叶子节点间记录上下次磁盘物理地址,防止回溯发生。
查找时不是主键要走辅助索引,找到主键ID,通过B+树再去找聚集索引,这就叫做回表查询。
对数据修改时,修改唯一的,先写日志再异步刷盘。
mysql为什们性能强这与缓存设计有很大的关系。
LRU算法
三、最左匹配原则
对于组合索引,从左向右匹配,遇到between,,like就停止匹配。
四、索引失效
- A and B中,右一个条件不满足就回索引失效。
- where中key做了判断,B+树的判断条件发生改变索引失效。
- 隐式转换,'1’被转成了1。
- like 'Mark%'这种是可以进行比较的,不能比较就失效。
- 不等于非常特殊,改为>0 or <0,这样就可以避免啦。
- in不会失效,in + or 和 not in 肯定失效。
- 组合索引没有使用第一列索引导致索引失效。
五、创建索引原则
- 查询频次高的建立索引。
- 使用短索引。
- 很长的动态字符串,考虑使用前缀索引。
六、定位解决问题
- 添加扩展索引,在现有的基础上添加复合索引,最多6个索引。
- 尽量不要使用select *,只检索使用的列。
- 索引列尽量使用非空。
- 可选:开启自适应hash或者调整changebuf。
总结
阿里的规定:过500万行就要分表分库,客户可以去拓展一下知识面。
经过今天的学习,感觉对B+树有了一定的了解。尽管mark老师讲的热火朝天,可是我还是仍然感觉自己需要一个长时间基础知识的积累。画龙点睛的效果自然是舒服美妙,但是我目前的状况是龙还没画好,故决定先充实自己,有条不紊的推进零声课程就好。
心静如水,快步如飞。