数据库知识-索引模块

最新推荐文章于 2022-06-12 23:08:55 发布

gefeng1209

最新推荐文章于 2022-06-12 23:08:55 发布

阅读量260

点赞数

分类专栏： java

本文链接：https://blog.csdn.net/gefeng1209/article/details/100983448

版权

java 专栏收录该内容

33 篇文章 13 订阅

订阅专栏

关系型数据库考点

1.数据库架构

如何设计一个关系型数据库

将数据库划分为存储部分与程序部分。存储部分负责存储数据库中的数据，程序部分负责对数据的逻辑管理。

存储管理模块：将数据的逻辑关系转化为物理存储关系
缓存机制模块：优化执行效率
SQL解析模块：解析sql语句
日志管理模块：记录数据库操作
权限划分模块：进行多用户管理
容灾机制模块：灾难恢复
索引管理模块：优化查询效率
锁模块：使得数据库支持并发操作

1.索引模块

为什么需要索引：为了避免对数据库查询时，进行全表扫描，扫描所有存储行记录的块和页，索引帮助定位数据。

索引的数据结构

二叉查找树-平衡二叉树

IO次数:h-1,复杂度：O(h)

缺点：普通二叉查找树容易退化成链表，且二叉树每个节点只有两个子节点，当数据量较多时，树的深度过高，IO次数过多；

B树（平衡多路查找树）

为了减少IO次数，即树的高度，让每个节点存储更多的信息。

关键字最左边节点中的关键字都小于本关键字；关键字最右边节点中的关键字都大于本关键字；其他孩子节点中的关键都介于该节点两侧关键字大小之间。

B+树

存储更多关键字：节点的子节点指针个数等于关键字个数

关系型数据库一般使用B+树作为存储引擎

Hash表

缺点：

1.不支持范围查询，因为经过Hash算法得到的Hash值顺序并不能保证和之前的顺序完全一样

2.数据库无法进行排序

3.不能利用部分索引键进行查询，因为对于组合键，Hash索引先拼接组合键之后计算Hash值

4.不能避免表扫描：对于Hash值冲突的数据，还是要扫描bucket

5.当有大量Hash值发生冲突时，查询效率比较低

对某个取值只有固定几个的字段，可以还使用位图索引。

不适用于OLTP类系统，而适用于OLAP类系统

OLTP：联机事务处理，高并发

OLAP：并发度低，统计操作多

聚簇索引、非聚簇索引、主键

在《数据库原理》一书中是这么解释聚簇索引和非聚簇索引的区别的：

聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

怎么理解呢？

聚簇索引的顺序，就是数据在硬盘上的物理顺序。一般情况下主键就是默认的聚簇索引。

一张表只允许存在一个聚簇索引，因为真实数据的物理顺序只能有一种。如果一张表上还没有聚簇索引，为它新创建聚簇索引时，就需要对已有数据重新进行排序，所以对表进行修改速度较慢是聚簇索引的缺点，对于经常更新的列不宜建立聚簇索引。

聚簇索引性能最好，因为一旦具有第一个索引值的记录被找到，具有连续索引值的记录也一定物理地紧跟其后。一张表只能有一个聚簇索引，所以非常珍贵，必须慎重设置，一般要根据这个表最常用的SQL查询方式选择某个（或多个）字段作为聚簇索引（或复合聚簇索引）。

聚簇索引默认是主键，如果表中没有定义主键，InnoDB[1]会选择一个唯一的非空索引代替（“唯一的非空索引”是指列不能出现null值的唯一索引，跟主键性质一样）。如果没有这样的索引，InnoDB会隐式地定义一个主键来作为聚簇索引。

聚簇索引与唯一索引

严格来说，聚簇索引不一定是唯一索引，聚簇索引的索引值并不要求是唯一的，唯一聚簇索引才是！在一个有聚簇索引的列上是可以插入两个或多个相同值的，这些相同值在硬盘上的物理排序与聚簇索引的排序相同，仅此而已。

如何定位并优化慢sql

根据慢日志定位慢查询sql
慢日志就是用来记录我们查询比较慢的sql

show variables like %quer%;

11：slow_query_log off ：慢日志关闭

2：slow_query_log_file：慢日志文件

3：long_query_time sql：sql执行多长时间会记录到慢日志文件中

show status like '%slow_queries%'

slow_queries：慢查询的数量即有多少个sql执行的比较慢

打开慢查询：

set global slow_query_log = on;

设置慢查询的时间：

set global long_query_time = 1;（重新连接之后才能看到修改的状态）

(也可以通过配置文件来修改这些配置my.ini，修改配置是永久修改的)

使用explian等分析工具分析sql
explain一般放在关键之的前面，用来描述mysql如何执行查询操作，以及mysql成功返回结果集需要执行的行数。explian可以帮助我们分析select语句，让我们知道查询效率低下的原因，从而改进我们的查询，让查询优化器更好的工作

id：标明sql的执行顺序，越大，越先执行
explian关键字段
type：mysql需要找到数据行的方式
性能是从最优到最差。index/all标明本次的查询走的是全表扫描
extra：辅助我们了解语句的执行方式
Using filesort ：没有使用表中的索引去排序，而是使用表外部的一个索引
修改sql让查询的sql走索引

执行上面的sql并没有走主键索引（密集索引）。为什么不用id而是用account（索引）？

之所以走这个索引，是查询优化器做的决定，mysql查询优化器的最终目标是走索引，并且使用最严格的索引来消除尽可能多的数据行，最红是提交select查询数据行，而不是排除数据行，优化器试图排除数据行是原因排除数据行的速度越快，那么找到与条件匹配的数据行就越快，因袭查询优化器会根据它的分析和判断的标准决定走哪个索引。没有选择走主键索引大致原因：应该是因为密集索引的叶子结点把其他数据也存储到叶子节点当中，所以这里就变成他的一个缺点，这样查询的效率，因为我们的数据都放在一起，所以他是效率要比稀疏索引要低，因为稀疏索引存储关键字和以及主键的值，这样我们在内存里就能加载更多的关键字和主键的值，来进行count。我们也可以强制让sql查询走主键索引，如下：

最左匹配原则的成因

联合索引
多列组成的索引

最左匹配原则
假设我们有两列a,b，a和b是联合索引，他的顺序是a,b，我们在where语句中调用a=? and b=?的时候就会走联合索引，如果调用where a = ?的时候也会走索引，但是当我们使用where b = ?的时候就不会走这个联合索引

成因：

mysql创建复合索引的规则是首先会对复合索引的最左边，也就是索引中的第一个字段进行排序，在第一个字段排序的基础上，在对索引上第二个字段进行排序，其实就像是实现类似order by 字段1，字段2这样的排序规则，那么第一个字段是绝对有序的，而第二个字段就是无序的了，因此一般情况下直接只用第二个字段判断是用不到索引的，这就是为什么mysql要强调联合索引最左匹配原则的原因。