内容总结于慕课网剑指offer内容
关系型数据库:
- 架构;
- 索引;
- 锁;
- 语法;
- 理论范式;(第一范式:列不可再分;第二范式:第二主键;第三范式:传递依赖;)
索引模块:
为什么要使用索引?
避免全表扫描,快速查询数据
有哪些信息能成为索引?
主键、唯一键以及普通键等;
索引的数据结构:
1.生成索引,建立二叉查找数、平衡二叉树及红黑树;
2.生成索引,建立B-Tree结构进行查找;
3.生成索引,建立B+数结构进行查找;
4.生成索引,建立Hash结构进行查找;
二叉树:
查找时间复杂度:O(logn);查找快
但是插入慢;
无论是二叉查找树还是平衡二叉树亦或是红黑树,都存在一个缺陷,就是IO问题。当查找的时候,每一次树节点的比较都会触发一次IO。而且随着数据数量的增加,树的高度每增加1,IO就会增加1,但是IO存在着难以解决的性能瓶颈;这就会大大降低树的效率,甚至会差于全表扫描;
B-Tree:
1.根结点至少包括两个孩子;
2.树中每个节点最多含有m个孩子(m>=2);
3.除根结点和叶节点外,其他每个节点至少有ceil(m/2)个孩子;
4.所有叶子节点都处在同一高度;
B+树:
B+是更适合用来做存储索引:
1.B+树的磁盘读写代价更低;
2.B+树的查询效率更加稳定;
3.B+树有利于所数据库的扫描;
Hash索引:
Hash桶的实现
缺点:
1、仅仅能满足“=”,“IN”,不能使用范围查询
2、无法被用来避免数据的排序操作
3、不能利用部分索引键查询
4、不能避免表扫描
5、如果存在大量Hash冲突时,效率就不高了
BitMap:
类似于B+树;
只适用于某个字段是固定几个值的时候;
在进行增删改操作时,会发生强烈的锁机制,比使用与高并发系统;
密集索引和稀疏索引:
密集索引:密集索引文件中的每个搜索码值都对应一个索引值;
稀疏索引:稀疏索引文件只为索引码的某些值建立索引项;
InnoDB:
若一个主键被定义,该主键则作为密集索引
若没有主键被定义,该表的第一个唯一非空索引则作为密集索引
若不满足上述条件,innodb内部会生成一个隐藏主键(密集索引)
如何定位并优化慢查询Sql
1.根据慢日志定位慢查询sql
2.使用explain等工具分析sql | Oracle的explain plan
3.使用sql或者尽量让sql走索引
最左匹配原则,即当组合索引存在时,需要使用完整的组合筛选条件,否则会全表扫描
索引是建立的越多越好吗?
1.数据量小的表不需要建立索引,建立会增加额外的索引开销;
2.数据表更需要维护索引,因此更多的索引意味着更多的维护成本;
3.更多的索引意味着也需要更多的空间;
锁模块
常见问题:
1.MyISAM与InnoDB关于锁方面的区别是什么?
2.数据库事务的四大特性?
3.事务隔离级别以及各级别下的并发访问问题?
4.InnoDB可重复读隔离级别下如何避免幻读?
5.RC,RR级别下的InnoDB的非阻塞读如何实现
MyISAM和InnoDB关于锁方面的区别:
MyISAM默认用的是表级锁,不支持行级锁
InnoDB默认用的是行级锁,也支持表级锁
悲观锁:
共享锁和排他锁:共享锁对读共享,对写加锁;排他锁对当前所操作的行或表加锁,执行完排他锁的操作之前,任何操作不得进行;
MyISAM:
MyISAM在进行select读取数据的时候,会为表加上一个表级的读锁;
在进行增删改操作的时候,则会为表加上一个标记的写锁;
上锁操作:lock tables <表明> read/write;//读锁~共享锁、写锁~排他锁
开锁操作:unlock tables;
当有写操作时,表级锁会触发;读操作是不会相互阻塞的;
那能不能给读操作进行排它锁呢?如何操作?
就是在select语句的最后,加上for update;
show variables like 'autocommit';
set autocommit = 1/0;#1就是开启,0就是关闭;
InnoDB
InnoDB默认的是乐观锁机制,即不存在排他锁和共享锁;
那如何对InnoDB进行悲观上锁呢?
在DML语句后面加上 lock in share mode;
当操作不使用索引,使用的是表级锁;
当操作使用索引时,使用的是行级锁和gap锁;gap锁是在使用普通非唯一索引的时候用到的;
IS,IX(排他写锁);
MyISAM适合的场景:
1.频繁执行全表count语句;
2.适合对数据进行增删改的频率不高,查询非常频繁的业务;
3.没有事务的业务;
InnoDB适合的场景:
1.数据增删改查都相当频繁的业务;
2.适合可靠性要求比较高,要求支持事务的业务处理;
数据库所的分类:
1.按锁的粒度划分,可分为表级锁、行级锁、页级锁;
2.按锁级别划分,可分为共享锁、排他锁;
3.按加锁方式划分,可分为自动锁,显示锁;
4.按操作划分,可分为DML锁,DDL锁;
5.按使用方式划分,可分为乐观锁,悲观锁;
乐观锁机制:表中添加version字段,用于记录行数据的版本数,每次更新修改,都会修改version的值;
数据库事务的四大特性:
ACID:
原子性(Atomic)
一致性(Consistency)
隔离性(Isolation)
持久性(Durability)
事务隔离级别以及各级别瞎的并发访问问题:
事务并发访问引起的问题以及如何避免:
查看事务隔离级别:select @@tx_isolation;
设置最低事务隔离级别: set session transaction isolation level read uncommitted;
//read uncommitted :最低的隔离级别了;
1.更新丢失————read uncommitted mysql所有事务隔离级别在数据库层面上均可避免;
数据更新丢失,例如转账问题;
2.脏读————READ-COMMITED 事务隔离级别以上可避免;
读取到事务未提交的数据;解决办法 设置级别为:read committed;
3.不可重复读————REPEATABLE-READ 事务隔离级别以上可避免;
在重复读取数据时,其他事务对数据进行了修改操作,怎会导致前后读取同一条数据时,数据不统一的情况;
设置REPEATABLE-READ,则不会实时更新读取的数据,但是操作时会使用新数据;
4.幻读————使用SERIALIZABLE 事务隔离级别可避免
Oracle默认为RC,Mysql默认为RR
InnoDB RR级别下,为什么可以避免幻读?如何避免幻读的?
表象:快照读(非阻塞读) --伪MVCC
内在:next-key锁(行锁+gap锁)
当前读和快照读:
当前读:select ... lock in share mode | for update
当前读:update,delete,insert
快照读:不加锁的非阻塞读,select;读到可能是历史版本,而不是最新版本;在serializable级别下退化为当前读; 在RR级别下,可能会读到数据的历史版本,也可能取到最新版本;
RC,RR级别下的InnoDB非阻塞读如何实现?
1.数据行里的DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID字段;
DB_TRX_ID:标识最近一次对当前数据进行修改的事务
DB_ROLL_PTR:回滚指针
DB_ROW_ID:单调自增的行ID;
2.undo日志;
回滚和快照需要使用的历史日志;
3.read view;
用于可见版本控制;根据当前数据中的回滚指针和read view
的阈值,在undo日志中找到要显示的数据;
InnoDB可重复读隔离级别下如何避免幻读?
表象:快照读(非阻塞读) --伪MVCC
内在:next-key锁(行锁+gap锁)
next-key锁(行锁+gap锁)
gap锁:
如果where条件全部命中,则不会用Gap锁,只会加记录锁; 当加上lock in share mode,即采用当前锁,就会加上Gap锁;
如果where条件部分命中或者全部不命中,则会加Gap锁;
gap锁会用在非唯一索引或者不走索引的当前读中
非唯一索引:此时会在左开右闭的去年内上锁,且会根据主键的排序进行区间划分;
不走索引:则会锁住所有gap;相当于表级锁,尽量要避免这个情况;