金三银四--数据库

本文主要是介绍金三银四--数据库，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

数据库考点

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

程序实例模块，对存储进行实例的管理
数据的逻辑关系，转化为物理关系的，组织数据，存储管理模块
所以处理数据不可能放在磁盘上做，应该方法内存上做，机型硬盘是靠磁头转动去找，固态硬盘是靠调用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会锁住一个范围，并且锁定记录本身，使得其他事务不能操作锁定范围内的记录，也就杜绝了出现“幻影”。（因为其他事务根本无法在此范围里插入数据）

日志级别需要自行复习

语法部分

数据库一些其他问题

offset 和Limit 有什么问题 以及代替方案是什么？

正如前面段落所说的那样，OFFSET 和 LIMIT 对于数据量少的项目来说是没有问题的。
但是，当数据库里的数据量超过服务器内存能够存储的能力，并且需要对所有数据进行分页，问题就会出现。
为了实现分页，每次收到分页请求时，数据库都需要进行低效的全表扫描。

这意味着，如果你有 1 亿个用户，OFFSET 是 5 千万，那么它需要获取所有这些记录 (包括那么多根本不需要的数据)，将它们放入内存，然后获取 LIMIT 指定的 20 条结果

也就是说，为了获取一页的数据：10万行中的第5万行到第5万零20行

需要先获取 5 万行。这么做是多么低效？

这种分页查询方式会从数据库第一条记录开始扫描，所以越往后，查询速度越慢，而且查询的数据越多，也会拖慢总查询速度。

你应该这样做：

1 限制ID

这是一种基于指针的分页。

你要在本地保存上一次接收到的主键 (通常是一个 ID) 和 LIMIT，而不是 OFFSET 和 LIMIT，那么每一次的查询可能都与此类似。

为什么？因为通过显式告知数据库最新行，数据库就确切地知道从哪里开始搜索（基于有效的索引），而不需要考虑目标范围之外的记录。

要使用这种基于游标的分页，需要有一个惟一的序列字段 (或多个)，比如惟一的整数 ID 或时间戳，但在某些特定情况下可能无法满足这个条件。

我的建议是，不管怎样都要考虑每种解决方案的优缺点，以及需要执行哪种查询。

关于数据表的id说明
一般情况下，在数据库中建立表的时候，强制为每一张表添加 id 递增字段，这样方便查询。

如果像是订单库等数据量非常庞大，一般会进行分库分表。这个时候不建议使用数据库的 id 作为唯一标识，而应该使用分布式的高并发唯一 id 生成器来生成，并在数据表中使用另外的字段来存储这个唯一标识。

使用先使用范围查询****定位 id （或者索引），然后再使用索引进行定位数据，能够提高好几倍查询速度。即先 select id，然后再 select *；

2 使用临时表 优化
这种方式已经不属于查询优化，这儿附带提一下。

对于使用 id 限定优化中的问题，需要 id 是连续递增的，但是在一些场景下，比如使用历史表的时候，或者出现过数据缺失问题时，可以考虑使用临时存储的表来记录分页的id，使用分页的id来进行 in 查询。这样能够极大的提高传统的分页查询速度，尤其是数据量上千万的时候。

MySql千万数据10秒批量插入

互联网项目中MySql 应该选用什么事务隔离级别

为什么使用读已提交？
要从主从复制讲起,基于binlog 复制的，binlog是一个记录数据库更改的文件。
binlog 有三种格式

为什么 项目中不适用读未提交 和串行化？

互联网的分布式方案，多采用最终一致性的事务解决方案!

RR 和RC 该怎么选取？

MySql 两个日志系统

1 redo log
MySql 如果每次更新操作都需要写进磁盘，然后磁盘也要先找到对应的那条数据，然后更新，整个过程IO成本，查找成本很高，为了解决这个问题，MySql在设计的时候就用了类似饭店记账的思路来更新效率。

其实就是MySQL里常说的WAL技术，WAL的全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写小黑板，等不忙的时候再写账本。

需要注意的是，先写日志的写日志，其实也是写磁盘，只是写日志是顺序磁盘，速度非常快。

具体的情况就是，当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里面，并更新内存，这个时候更新就算完成了，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往在系统比较空闲的时候做，就像打烊了以后写账本一样。

同时，将赊账记录在小黑板上，如果赊账的不多，可以等打烊了以后再记录账本，如果赊账的特别多，小黑板写满了，这个时候掌柜就要放下手上的活，先把黑板上的部分赊账记录更新到账本上，然后将记录好的信息从小黑板上擦掉，为记录新的赊账腾出地方。

MySQL于这个也是类似的，InnoDB的redo log是固定大小的，比如我们可以分配一组4个文件，每个文件的大小都是1GB，那么总共就可以记录4GB的操作，从头开始写，写到末尾就又从开头循环写，write pos是当前记录的位置，一边写一边后移，写到3号文件末尾后就回到0号文件开头，checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据库中。

write pos和checkpoint之间的是“小黑板”上还空着的部分，可以用来记录更新的操作，如果write pos追上了checkpoint，表示“黑板“写满了，这个时候不能再执行新的更新，得停下先擦掉一些记录，把check point推进一下。

redo log是InnoDB引擎所特有的，所以我们在使用InnoDB引擎创建表时，如果数据库发生异常重启，之前提交的记录都不会丢失，InnoDB就是因为有了redo log才有了crash-safe的能力。

crash-safe简单来讲，就好比饭店掌柜的把赊账记录在小黑板上或者账本上，之后饭店突然停业了几天，重新开业后，依然可以通过小黑板和账本上的数据核算赊账账目，

2 binlog
MySql 整体看来有两部分
一部分是Server 层，主要做的就是MySql功能层面的事情， Server 层的日志成为binlog。
还有一部分是引擎层，负责存储相关的具体事情。redo log 是innoDB 引擎特有的日志。

1 redo log 是 innoDB 引擎特有的；binlog是MySql的Server层实现的，所有引擎都可以使用。

2 redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是罗技日志，记录的是这个语句的原始逻辑，比如“給ID=2 这一行的 c 字段加1”

3 redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。追加写是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

一条Sql 在MySql 中是怎么执行的

MySql 大体上来看可以分为Server 层和存储引擎两部分
server层：包含 连接器，查询缓存，分析器，优化器执行器等，覆盖了MySql的大部分核心服务功能，以及所有的内置函数，所有的跨存储引擎的功能都在这一层实现，比如存储过程，触发器，视图等

存储引擎层：负责数据的存储和提取，其架构模式是插件式的，支持InnoDB，MyISAM，Memory等多个存储引擎。

关键字：长连接以及短连接，内存溢出，发生异常重启 ， 解决办法。

关键字：缓存如何存放，怎么过期，如何使用缓存，版本过期问题

关键字： 执行方案也就定下来了

关键字：对表进行权限校验，而连接器验证的是用户的身份。

这篇关于金三银四--数据库的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！