关系型数据库主要模块
关系型数据库主要知识点:
索引模块常见问题
什么样的信息能成为索引
为了更快的查询数据
索引的数据结构
密集索引和稀疏索引的区别
索引的数据结构
二叉查找树
特点:对半搜索,查询较快,也就是常说的二分法查询
缺点:单边数据的增加,可能会变成线性二叉树,将发生多次IO,降低了查询效率
B-Tree结构
B-Tree 定义(平衡多路查找树)
>根节点至少包括两个孩子
>树中每个节点最多含有m个孩子(m>=2)
> 除根节点和叶节点外,其他每个节点至少有ceil(m/2)个孩子
/>所有叶子节点都位于同一层(让每个索引块存储更多信息)
三阶B树如下:
与二叉树相比,比二叉树矮的多,加快了数据的查询
B+-Tree结构
B+树是B树的变体,其定义基本与B树相同,除了∶
> 非叶子节点的子树指针与关键字个数相同(能存储更多的关键字)
> 非叶子节点的子树指针P[i],指向关键字值(K[i],K[i+1])的子树
> 非叶子节点仅用来索引,数据都保存在叶子节点中(比B树更矮)
|>所有叶子节点均有一个链指针指向下一个叶子结点(方便在叶子结点做范围统计!,可以横向跨子树,做统计)
结论:
B+Tree更适合用来做存储索引
B+树的磁盘读写代价更低(B树只存放索引信息,不存放数据)
B+树的査询效率更加稳定(所有关键字查询的长度相同,也就是效率是相同的)
B+树更有利于对数据库的扫描(遍历用指针链接的叶子结点,方便范围查询)
建立Hash结构
缺点
仅仅能满足"=","IN",不能使用范围查询
无法被用来避免数据的排序操作 hash运算无排序概念!
不能利用部分索引键查询 是组合合并后的哈希值,不是单独计算索引得哈希值
不能避免表扫描 无序就不能范围查询!!
遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高
bitmap(Oracle使用,mysql不支持)
只适用于某个字段的值是固定的几个的情况。
缺陷,锁的密度特别大。修改数据的时候影响较大
适合并发较少的系统
密集索引和稀疏索引的区别
- 密集索引文件中的每个搜索码值都对应一个索引值
- 稀疏索引只为索引码的某些值建立索引项
因为密集索引决定了表的排列顺序,所以1个表只能有1个物理排列顺序,所以一个表只能有1个密集索引
InnoDB(含一个密集索引,其他索引依赖这个密集索引)
若一个主键被定义,该主键则作为密集索引
若没有主键被定义,该表的第一个唯一非空索引则作为密集索引
若不满足以上条件,innodb内部会生成一个隐藏主键(密集索引)
非主键索引存储相关键位和其对应的主键值,包含两次查找
MyISAM(稀疏索引,所有索引等价)
查看数据库表的文件
sudo ls -lrth data/database_demo
每个数据库的表结构信息存储在.frm里面
InnoDB 索引和数据都存储在.ibd文件内
myisam索引和数据是分开的 数据在.myd里面
慢查询处理思路
如何定位并优化慢查询Sql?
根据慢日志定位慢查询sql
1.开启慢查询
show variables like '%quer%'
set slow_query_log = on:
set slow_query_time = 1://需要重新链接客户端
最好在配置文件中修改,不然重启服务后会读取配置文件配置,也就是初始化了
其中
long_query_time 就是定义多久是慢日志,这里是10s
slow_query_log 就是是否开启慢日志记录 OFF关闭
slow_query_log_file 就是慢日志文件的存储位置
这里修改值后,有的是直接起效了,有的则是需要重新链接客户端
show status like '%slow_queries%' 查看慢查询sql记录次数
在终端中
sudo vim slow_query_log_file的地址 打开慢查询记录的日志
这里的query_time 才是查询的真实时间,客户端显示的是不正确的。也可以看到查询的sql
使用explain等工具分析sql
一般放在select前面
一般看type和extea两个字段
type:出现index和all全表查询一般是需要优化的
extra:
sql调试技巧
使用force index (primary) 强制使用什么索引,可以看到区别
联合索引的最左匹配原则的成因
如图所示∶
1.先根据Alice索引查找到所有Alice的叶子节点
2.然后根据clo2进行排序
因此查找的时候,想走clo3和clo2的联合索引就得有clo3,单单依靠clo2是没有办法去走联合索引的。
最左匹配原则的成因∶
1.Mysql创建联合索引是首先会对最左边,也就是第一个索引字段进行排序
2.在第一个排序的基础上,再对第二个索引字段进行排序,其实就像是实现了Order by字段1,再Order by字段2这样—种排序规则
3.所以第一个字段是绝对有序的,而第二个字段就是无序的了
4.因此通常情况下,直接使用第二个字段进行条件判断是用不到索引的。这就是为什么mysgl要强调最左匹配原则的成因。
总结下就是如果没有最左匹配的话,数据就是失序的,失序的话就要全表查
索引是建立得越多越好吗
-
数据量小的表不需要建立索引,建立会增加额外的索引开销
-
数据变更需要维护索引,因此更多的索引意味着更多的维护成本
-
更多的索引意味着也需要更多的空间
好比100页的书却有50页的目录!!!