数据库学习总结

啊噗、

已于 2024-07-14 21:33:20 修改

阅读量121

点赞数 6

文章标签：数据库学习

于 2024-07-14 00:40:13 首次发布

本文链接：https://blog.csdn.net/qq_44369975/article/details/140409007

版权

数据库学习

架构

索引

为什么要使用索引

索引类似一个字典，可以让我们避免全面扫描，提高查询的效率

什么情况下使用索引

主键，唯一键，只要是能够定义的字段都可以成为索引

索引的数据结构

主流为B+树还有B树 hash bitmap索引

密集索引和稀疏索引的含义和区别

密集索引是每一个索引都对应唯一一个数据，系数索引表示每一个索引都对应这个数据集合，在Innondb中只有一个密集索引就是主键索引。先查询B+树索引在查询主键索引从主键索引中找到数据。

Myism和Innondb

Myism都是系数索引，Innondb 有一个密集索引
Myism索引和数据是分开存储的 Innondb 索引和数据存放在一个文件中ibd

如何定位优化慢SQL

根据慢日志查询出现比较慢的SQL
show variables like ‘%quer%’
查询出是否启用慢日志监控 slow_query_log=on （set global slow_query_log = on ）
slow_query_log_file = XXXXX慢日志的记录位置
long_query_time = 10 sql 超过多少时间会被记录
show status like ‘%slow_queryies%’ 查看有多少慢sql
EXPLINE 分析SQL
分析修改让SQL尽量走索引

什么时联合索引的最左匹配

加入数据库建立了索引a,b,c的联合索引那么 mysql会给a字段建立一个B+树的索引在相同叶子节点上会对b和c进行排序，因此加入不走最左匹配原则，那么b就没法去走a的B+数索引查找数据。

索引应该建立多少合适

索引的建立和维护也是需要花费资源和时间的
数据量比较小的表没必要建立索引
索引太多会导致更新数据变慢，因此写多读少的字段和数据表不适合建立索引

锁

MyIsam和Innodb加锁方式

MyIsam 的锁为表锁，导致其他会话在进行操作的时候 不管是否操作同一条数据都会加锁
MyIsam 每次更新数据都会加上排他锁，使得其他会话在进行更新和修改数据的时候会阻塞
MyIsam 每次查询数据会加上共享锁，共享锁不会锁定数据的查询 但是会锁定数据的更新操作
Innodb 的锁为行锁，其他会话在进行操作的时候，当修改数据为同一行才会进行锁定，由于Innodb数据和索引是放在一起的，查询使用索引找到主键id在使用主键索引进行数据查找，当其在没有索引的字段上进行查询或者更新 会导致锁升级 变成表锁（Innodb的意向锁 IS,IX）
Innodb每次更新数据会加上排他锁，但是加锁范围只会是本条数据，会使得其他会话更新相同数据被阻塞
Innodb 每次查询都会加上共享锁，共享锁会锁定当前行的更新操作，但这里数据库引擎会做一些优化。

数据库事务的四大特性

Innodb支持事务，事务有四个特性（ACID）
A(Atomic)，原子性：当前事务是一个原子，要么同时成功，要么同时失败。
C(Consistency),一致性：事物确保数据库状态从一个一致状态转变成另一个一致状态。一致性状态表示数据库字段满足完整性约束。（转账总金额2000 转账后金额总和还是2000）
I(Isolation)隔离性,表示多个事务之间不受影响，一个事务的执行不影响其他事务的执行
D(Durability)持久性,事务一但提交对数据库的修改是永久保留的，这里也对数据库的容灾有一定的要求，一但一个事务被提交 DBMS 一定要保证一定的冗余，使其可以保证发生故障时的容灾处理。如Innodb如果事务一但提交，会将操作记录写入redolog中，确保在数据库重启时从此文件中进行恢复操作。

事务隔离级别

读未提交：可能发生脏读

事务1进行写入操作将某余额从1000变为900但未提交
事务2可直接读到900 可能会在900的基础上进行操作假设存入200 提交为余额1100
事务1这时候因为某些原因回滚了数据
从上帝视角可以看到总数应该为1200 但变成了1100

读已提交：避免了脏读，可能发生重复读

事务1进行余额存入200元未提交事务
事务2先进行查询余额为1000
事务1提交数据
事务2在读取余额变为1200元但这就会使得事务2在读取时不知道那次数据才是对的，导致数据的不确定性。

可重复读：避免了可重复读，但会发生幻读（Innodb默认隔离级别，但Innodb可以在当前隔离级别避免幻读）

事务1进行读取可能读取到2条数据
事务2对某个数据进行删除
事务1对读取到的数据进行统一更新，但只成功了1条，这时候就出现了幻读

注：Innodb可以在当前隔离级别避免幻读（Innodb会在读取数据时候加行锁和gap锁也就是我们熟知的next-key锁）

串行化

事务每次查询和更新都会添加读写锁
事务和事务是串行的方式进行

RR和RC事务隔离级别下的非阻塞读如何实现

每个数据行里面都会有DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID字段
undo日志 undolog会根据数据更新版本存储不同版本的数据。3. read view 在RC级别下每次快照读都是从最新的数据进行快照因此可以看到其他事务最新提交的数据，在RR级别下mysql会根据可见性算法从数据中取出DB_TRX_ID 在从undolog中获取到大于等于当前id的版本，及查询到的数据是一个稳定的版本，这就要求我们在RR级别下，读取数据的时机很关键，在数据插入之后读取，即可获得当前数据最新的快照读版本。