数据库一些理解

数据库考点

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

程序实例模块，对存储进行实例的管理
数据的逻辑关系，转化为物理关系的，组织数据，存储管理模块
所以处理数据不可能放在磁盘上做，应该方法内存上做，机型硬盘是靠磁头转动去找，固态硬盘是靠调用SATA信号量去找，
为了提升IO，如果以行去找并返回，那么IO将慢如蜗牛，因为一次IO读多条和单条没有任何区别，所以行失去了意义，所以数据库把数据存储单元用块or页来表示，里面存放多行数据。

优化执行效率的缓存模块
优化数据查询效率的索引模块
支持数据库进行并发操作的锁模块

索引模块

常见问题：

为什么要用索引？

加快查找效率，避免全表扫描

什么样的信息能成为索引？

能有区分度的键，都可以
索引的数据结构？

MySql 主要通过B+ Tree 来实现的

这样不但有时间复杂度的问题，还会有IO问题，比如找6，将5读到内存中，在发生IO读到7，在发生IO读到6，深度增加就会发生一次IO，IO就会很多，就跟全表扫描一样。



由于有这些约束，所以B树不会变为链表，查找效率和二叉树一样

所有叶子节点均有一个链指针指向下一个叶子结点，是为了方便有一个范围统计，比如大于等于10 ，不用再回到非叶子结点进行搜索。

B+树 所有数据都在叶子结点里面
B+树都要走到叶子结点，每次查询
B+树只要遍历叶子结点，就完成了对于关机字全部信息的扫描

Hash索引也可以考虑下

理论上高于B+树
因为进行运算后的hash值，不一定能保证和运算前的大小关系一样。

找到hash的bucket之后，还是要去扫描表去获取数据

Oracle 支持，类似B+树， 适用于 索引值是基本固定的
缺点锁的力度非常大，在进行新增的时候，在同一个位图的数据都会被锁住。因为所在位图顺序，会因为数据的添加和删除会发生改变

bitMap的索引 就是用位图表示的索引，对列的每个键值建立一个位图，所以相对与B-Tree索引，占用空间存储非常小，创建和使用非常快，因为小，减少了磁盘IO，
缺点就是修改操作锁粒度大，不适合频繁更新。

bitmap索引适用场景
建在值重复度高的列上，GP手册建议在100到100,000之间，如：职业、地市等。重复度过高则对比其他类型索引没有明显优势；重复度过低，则空间效率和性能会大大降低。

特定类型的查询例如count、or、and等逻辑操作因为只需要进行位运算。如：通过多个条件组合查询，select count(*) from table where city = ’南京市’ and job = ’医生’ and phonetype = ‘iphone’ and gender =’男’。类似这种场景，如果在每个查询条件列上都建立了bitmap索引，则数据库可以进行高效的bit运算，精确定位到需要的数据，减少磁盘IO。并且筛选出的结果集越小，bitmap索引的优势越明显。

适用于即席查询、多维分析等OLAP场景。如果有一张表有100列，用户会使用其中的20 个列作为查询条件（任意使用这20个列上的N的列），几乎没有办法创建合适的 b-tree 索引。但是在这些列上创建 20 个 bitmap 索引，那么所有的查询都可以应用到索引。

bitmap索引不适用场景
值重复度低的列，如：身份证号、手机号码等。

重复度过低的列，如：性别，可以建立bitmap索引，但不建议单独作为查询条件使用，建议与其他条件共同过滤。

经常需要更新修改的列。

不适用于OLTP场景。

位图索引 使用举例:
　　此外，位图索引适合静态数据，而不适合索引频繁更新的列。举个例子，有这样一个字段busy，记录各个机器的繁忙与否，当机器忙碌时，busy为1，当机器不忙碌时，busy为0。

这个时候有人会说使用位图索引，因为busy只有两个值。好，我们使用位图索引索引busy字段！假设用户A使用update更新某个机器的busy值，比如update table set table.busy=1 where rowid=100;，但还没有commit，而用户B也使用update更新另一个机器的busy值，update table set table.busy=1 where rowid=12; 这个时候用户B怎么也更新不了，需要等待用户A commit。

原因：用户A更新了某个机器的busy值为1，会导致所有busy为1的机器的位图向量发生改变，因此数据库会将busy＝1的所有行锁定，只有commit之后才解锁。

密集（聚集）索引和稀疏（非聚集）索引区别

ISAM 引擎：
都是稀疏索引

InnoDB引擎：
有且仅有一个密集索引

索引模块衍生问题：


超过1s就比较慢的了，看慢查询的数量


重新连接客户端就能看到，但不是永久改变。重启后就会变化。

index， all 表示是走的全表扫描

这两个出现会非常慢的

这个是索引查询优化器决定的，优化器是要尽可能快，排除数据行，优化器用哪个就是哪个

可以用force index 强制 修改 走哪个索引

最左匹配原则

索引是越多越好吗？

锁模块

常见面试题

MyISAM 有一个表级别的读锁（共享锁），写锁（独占锁）
InnoDB 引擎：
使用的是二段锁，加锁和解锁是两个步骤，innoDB 是 自动提交的。

这里面的语句都是关闭了autoCommit的，需要手动commit

select语句默认不加锁，不管哪种情况，普通select读都可以读到

可以更新成功，这个是非阻塞select，并未对行上锁

显示 添加共享锁

InnoDB引擎默认对update，delete，insert加排它锁， 更新不成功，需要上个步骤commit，才能update成功

两个共享锁是可以读出数据的，即便是同一行

表级锁和索引无关
行级锁和索引有关
在Mysql中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，Mysql就会锁定这条主键索引；如果一条语句操作了非主键索引，Mysql会先锁定该非主键索引，再锁定相关的主键索引。

InnoDB行锁是通过给索引加锁实现的，如果没有索引，也就是说：如果不通过索引条件检索数据，那么InnoDB将对表中所有数据加锁，实际效果跟表锁一样。因为没有了索引，找到记录就得扫描全表，要扫描全表，就得锁定表。

InnoDB 还支持表级别的共享读锁（IS）和排他写锁（IX），和ISAM的锁差不多

各个引擎使用场景

而 MyISAM 内部维持了一个计数器，预存了结果，所以直接返回即可

锁的分类

ACID

一致性是指在事务开始之前和事务结束以后，业务逻辑上的一致性 and 数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。
如A给B转账，不论转账的事务操作是否成功，其两者的存款总额不变

多个事务并发执行，不应该影响其他事务执行
一旦一个事物提交，永久变更（redolog file 数据库每次从启都会从这个文件读取数据）

查看事务隔离级别：

设置 读为提交

可重复读 理论上避免不了幻读，而是通过巧妙的方式避免了幻读

不可重复读侧重于对于同一个数据的修改
幻读侧重于 新增or删除，范围数据，幻读发生在当两个完全相同的查询执行时，第二次查询所返回的结果集跟第一个查询不相同。
处于性能考虑才会选择不同的隔离级别。
MySql 默认为可重复读， Oracle 默认为 已提交读

当前读和快照读

InnoDB 可重复读隔离级别下如何避免幻读


只有lock in share mode 添加了 共享锁，其他语句都是添加了排他锁

current read 就是当前读，获取最新版本（以上就是当前读流程）

不加锁的条件，是隔离级别部位seriziable的条件下才成立的，在seriziable条件下，都是穿行读，快照读也会变为当前读，都变成select in share mode 模式
快照读是为了提升性能，基于MVCC 多版本并发控制，是行级锁的一个变种，所以快照读可能会读到历史版本

rc 隔离界别下，当前读和快照读的结果是一样的
rr 隔离界别下，当前读和快照读 有可能不一样。创建快照的时机，决定了读取数据的版本

RC，RR 级别下的InnoDB 的非阻塞读（快照读）如何实现

DB_TRX_ID：最后一次对本行数据进行修改的事务标识符号
DB_ROLL_PTR：回滚指针，
DB_ROW_ID：行号，有个单调递增的行ID ，这个就是隐藏主键

undo 日志：当我们对记录进行变更的时候，就会生成undo日志，里面存储的是老版本数据
主要分为两种： insert undo log 和 update undo log

修改过程就是先使用排它锁，锁住改行，然后拷贝日志，然后修改当前值，最后填写TRX_ID

如果又有 事务修改，就又多了一条 undo log，由 Roll_PTR 连接起来了

read view 进行可见性判断，当进行快照读的时候，会根据您可见性数据创造出一个readview，来决定当前事务能看到的是哪个版本的数据

遵循一个可见性算法：将要修改数据的DB_TRX_ID 取出来，与系统其他活跃事务ID做对比，如果大于or等于这些ID，就通过PTR指针去undo log取去出上一层的数据，直到小于这些活跃ID 为止，这样就保证了 看到的版本是最稳定的版本。

每次新开启事务的时候，事务ID都会递增，每次新开的事务ID 都会越大。 再往下就深刻了

正是因为如上的特点，
RR级别下，事务第一条快照读，会创建第一条readview，此后再调用快照读，用的是同一个readview
RC 级别下，每次select 都会创建一条快照读，这就是为什么每次都能看到别的事务提交的增删改查

这就是为什么RR 级别下，别的事务数据提交了但是可重复读的原因，对于RR级别下，首次事务快照读的时机是非常重要的。

真是如上原因，才使得InnoDB 在RR RC 使用非阻塞读（快照读），而读取数据的非阻塞 就是所谓的MVCC，这个是伪MVCC ，undolog 里面的东西只是串行化的结果。记录了多个版本的结果

MVCC 是多版本并发控制，读不加锁，读写不冲突 读多写少应用。

InnoDB RR 级别下 如何 避免幻读

内在是next-key锁机制

只有在RR， 其他级别下有，其他事物隔离级别下是没有的。

delete 要对唯一索引和主键索引都添加排他锁，为的是让其他并发事务，感受到主键索引的存在

在非唯一索引（普通索引）or不走索引的当前读中，除了加record lock ，自己加，给主键加，还要添加间隙锁

gap锁如何划分，只会对要修改数据的周边进行上锁
不走索引，都会添加gap锁，就相当于锁住表，这种操作比上表锁代价更大，通常需要避免

当前读是通过next-key lock实现，next-key lock会锁住一个范围，并且锁定记录本身，使得其他事务不能操作锁定范围内的记录，也就杜绝了出现“幻影”。（因为其他事务根本无法在此范围里插入数据）

日志级别需要自行复习

语法部分