【数据库】

瑞新

发布于 2020-07-07 18:13:22

1.1K0

文章被收录于专栏：用户3288143的专栏用户3288143的专栏

文章目录

数据库架构
- 如何设计一个数据库架构
- 索引
  - 为什么使用索引
  - 什么样信息能成为索引
  - 索引的数据结构
优化你的索引-运用二叉查找树
优化你的索引-运用B树
优化你的索引-运用B+树
优化你的索引-运用Hash以及BitMap
密集索引和稀疏索引的区别
索引额外的问题之如何调优Sql
- 1，根据慢日志定位慢查询Sql。
- 2，使用explain等工具分析Sql
- 3，修改Sql或者尽量让sql走索引
索引额外问题之最左匹配原则的成因
索引额外问题之索引是建立越多越好吗
锁模块之MyISAM与InooDB关于锁方面的区别
- MyISAM与InnoDB关于锁方面的区别是什么
  - MyISAM事务模拟-读锁
  - 显式-读锁
  - 读锁-读（共享锁）
  - 写锁-读（排它锁）
  - 写锁-写
  - 读->写锁（上排它锁）
  - InnoDB锁
  - mysql事务自动提交-更新
  - 事务默认自动提交-关闭测试的两个session自动
  - 行级锁-共享锁，无法加排它锁
  - 行级锁（不同行）-（读）共享锁，（写）可以加排它锁
  - 行级锁（同行）-（读）（读）
  - 测试不走索引的字段锁
  - 互斥锁和排它锁的兼容性
  - MyISAM,InnoDB使用场景
  - 数据库锁的分类
数据库事务的四大特性
事务隔离级别以及各级别下的并发访问问题
- 数据丢失
- 脏读
- 不可重复读
InnoDB可重复读隔离级别~下如何避免幻读
- 当前读，快照读
- 场景模拟：快照读，2rc2rr
RC、RR级别下的InnoDB的非阻塞读（快照）如何实现
- 快照读（非阻塞读）
- **undo主要分为两种 insert、update**
- **日志的实现方式-减法的演示（事务对行记录的更新过程）**
锁模块之RR如何避免幻读
- 对主键索引或唯一键索引会用Gap锁吗
  - 普通加锁
  - 测试-不会加gap锁的（删9，不锁10）
  - 测试-会加gap锁的（删掉不存在的记录78-全都不命中）
  - 部分命中-加gap锁
  - 全部命中-不会上gap锁
- Gap锁会用在非唯一索引或不走索引的当前读中
  - 简单理解
  - 演示-rr级别下，非唯一索引
  - 不走索引
- 思路扩展
语法sql
- 关键语法
- 表关系
- group by
- Having
- 拼接混合使用

数据库架构

如何设计一个数据库架构

加载到内存，为减少频繁io，存储不是逐行，而是块/页更快优化内存，数据缓存，块数据，里面其他没用的行，根据某行访问，其附近的行也可能被访问的原则外界指令操作数据库sql，sql解析成机器可以识别的指令日志管理，做灾难恢复

索引

为什么使用索引

快速查询，避免全表扫描（全表扫描适用数据量小的情况）像字典

什么样信息能成为索引

主键、唯一键、普通键等

索引的数据结构

优化你的索引-运用二叉查找树

左<根<右平衡二叉树（根部左子树和右子树比不超过1）二分查找缺点：如果圈中节点删除复杂度变成Log(N ) 利用树的旋转的特性维持在O(N),但是会增加频繁的io 改进方向：树矮一些，每个节点的io少一些，产生B-tree

优化你的索引-运用B树

复杂度O(N) 合并、分裂、上移、下移节点，保持，使其不会变成线性的，且矮的多限定孩子数，和深度 ceil(向上取整)

非叶子节点的关键字信息：简单理解为下方，对应的2、3范围区间

每个节点有m个孩子，就是m阶B树

优化你的索引-运用B+树

关键字不存储信息，一次读取数据更多，减少磁盘io 解决范围查询

和B数差不多，是B树变体注意：关键字k不一定是最小值，只是在当前指向指针里的其中一个值（不硬性），但是必须小于ki+1的值（硬性），形成范围。

优化你的索引-运用Hash以及BitMap

基于mysql的InnoDB和MyISAM不支持hash 一次计算，减小了逐级访问io，理论上比B+tree好

为什么不用做数据库索引？

BitMap位图索引

mysql不支持像B+Tree一样，叶子位存储位图只存是否因此特别快速度特别快位图锁力度很大，一处修改整个位图都锁不适合多并发联机事务处理的系统，适合少并发，统计多的系统

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

密集索引：键值对+其他列稀疏索引：主键对应行

MyISAM：默认索引，主键、唯一、普通（均稀疏索引）索引和数据是分开存储的 InnoDB：有且只有一个密集索引为什么要有一个密集索引？稀疏索引的叶子节点，并不存储行数据的物理地址。而是存储的该行的主键值，所以非主键索引（稀疏索引）包含了两次查找（查找次级索引自身+查找主键）

非聚集索引，也是一颗平衡树。所有节点都会存储索引列的值，比如这里就是name列的值，叶子节点还会多存储该name值所对应的的聚集索引的值，即主键的值。不同于聚集索引，真实的数据行不会按照非聚集索引排序存储，但索引项的内容是按顺序存储的。一个表可以有多个非主键索引，因此会建立多个非聚集索引，每建立一个非聚集索引，都会将该非聚集索引关联的字段数据复制出来一份，用于生成以该列为基础的平衡树。这样的操作会增加表的体积，占用磁盘空间，所以不是索引越多越好。通过非聚集索引查询数据时，查询到叶子节点上的主键值后，再利用这个主键值查询聚集索引，从而查询到具体的行记录，这个需要遍历两次树。

索引额外的问题之如何调优Sql

【数据库】MySql性能监控

如何定位并优化慢查询Sql？

具体场景具体分析，只提出大致思路。

1，根据慢日志定位慢查询Sql。

MySQL有很多自带的系统变量，咱们可以通过查询一些变量来得知有哪些配置信息：

这里我们是要查询跟慢日志相关的一些信息，

show VARIABLES LIKE '%query%'

然后查询一下慢SQL的数量（注意一下，这里只是记录本次会话的慢SQL的条数，一旦我们关闭了MySQL的客户端，再重启之后，这个慢SQL的条数就会被清零）

show status like '%slow_queries%';

然后我们将慢查询日志打开，sql如下：

set global slow_query_log = on;

然后查看一下，发现慢查询日志已经打开了。

然后我们需要设置慢查询时间为1s，sql如下：

set global long_query_time = 1;

然后重新连接客户端（为什么要重新连接客户端呢？因为有些系统配置修改后可以立即看到结果，有些则不行，设置慢查询时间属于后者），再执行“show VARIABLES LIKE ‘%query%’“，发现时间已经变化：

我们也可以到MySQL数据库的配置文件my.ini文件中去做修改，这样的修改将是永久性的。我们在这里做的修改，如果重启了数据库的服务，这些配置将会被还原成默认的配置。

接下来我们来制造慢查询：

我已经提前在一张表里灌入了10万多条数据

现在我们写一个慢查询Sql，执行看下时间：

然后执行：show status like ‘%slow_queries%’;结果如下：

打开慢查询日志：

可以看到这里已经记录了慢查询SQL和查询所用的时间。

2，使用explain等工具分析Sql

然后我们用explain对慢sql进行分析

先了解下Explain关键字段：

（1）type：表示的是MySQL找到需要的数据行的方式。

从左到右性能越来越差。当我们看到type为最后两个的时候，表明sql走的是全表扫描。

（2）extra

3，修改Sql或者尽量让sql走索引

走索引，调优程序，整体效率提升33%

增加索引

索引额外问题之最左匹配原则的成因

组合索引最左匹配原则第一个字段必须等值按照规则排列的字段顺序乱了没问题，mysql会优化顺序

数据库创建联合索引

全字段走联合索引

最左匹配

非最左

索引额外问题之索引是建立越多越好吗

锁模块之MyISAM与InooDB关于锁方面的区别

MyISAM与InnoDB关于锁方面的区别是什么

共享锁，排它锁MyISAM和InnoDB都适用 MyISAM不支持事务 InnoDB支持事务

场景模拟：两种数据引擎数据量大致相同，两百万

MyISAM事务模拟-读锁

MyISAM不支持事务，对表级锁不容易模仿再现表级锁：增加操作延时，查找时并发更新

这样在查找前一个等待时，可以并发访问另一个表数据请求，从而触发MyISAM表级锁

等待执行完（读锁释放），才更新

显式-读锁

读锁未释放，无法更新

释放当前读锁

锁释放，更新成功执行

读锁-读（共享锁）

产生读锁

（读锁未释放）再读，可以读取

写锁-读（排它锁）

创造写锁

写锁未释放，无法读

写锁-写

写锁未释放，无法写

读->写锁（上排它锁）

写锁，无法读

InnoDB锁

利用其支持事务的特点，利用session获取锁，暂时不自动提交 mysql是自动提交事务的用的二段锁：

mysql事务自动提交-更新

两条语句都无需等待，直接更新的，看起来和不支持事务的MyISAM一样。

更新

更新

事务默认自动提交-关闭测试的两个session自动

session关闭

另一个session也关闭

如果不愿意像这样做测试，可以范围查询这样复杂，因为表默认支持了事务二段提交

直接对单行，加共享锁。对同一个session另一行加共享读

行级锁-共享锁，无法加排它锁

查询

读锁，

需要将读锁(共享锁释放)commit

行级锁（不同行）-（读）共享锁，（写）可以加排它锁

共享锁

行级锁（同行）-（读）（读）

第一个session

第二个session

测试不走索引的字段锁

加读锁motto1

更新motto2（两条根据不同的motto，但被锁住了）

释放读锁commit；

因此不走索引时，就是表级别锁 InnoDB在没有用到索引的时候用的是表级锁意向锁（表级锁） IS读IX写锁（和MyISAM的共享锁排它锁很像，避免行轮询，形成一个表锁） sql锁行级锁 gep锁走普通非唯一索引的

互斥锁和排它锁的兼容性

MyISAM,InnoDB使用场景

MyISAM

InnoDB

数据库锁的分类

自动锁：InnoDB意向锁（非索引字段引起的表级锁）、MyISAM表锁、insert、update、delete【mysql自动上的】显式锁：select、等显式加的锁 DML锁：数据操作 DDL锁：对表结构进行变更乐观锁（不用数据的自带的锁）两种实现方式：版本号，时间戳

体会乐观锁-版本号

模拟场景

版本过期，交给用户按照业务逻辑处理

数据库事务的四大特性

数据库恢复文件InnoDB redofile

事务隔离级别以及各级别下的并发访问问题

数据丢失

主流数据库会自动避免假象场景

脏读

一个事务读到另一个事务未提交的数据避免脏读的事务隔离级别： read uncommit（不可以） read commit（可以），规定事务只能读别人已经提交的数据，避免脏读不需要关闭自动提交，降低事务级别，准备好表和数据降低事务级别：调整当前session事务隔离级别到最低，方便模拟场景未提交读：事务允许其他事务读取未提交数据

查询数据隔离级别

表和数据的准备

拿第一条数据做测试

开启各个session事务

session1从账户取走100元

查余额-变900，但是当前session1没提交

脏读：session2这时候查，竟然是900

session1，还未提交（网络原因失败）回滚，变为1000

session2存入两百，提交：并不知道此时脏读的900回滚1000

上帝视角：脏读的恶果 1000取100（失败）存入200，应该是1200，但现在是1100。少了100

解决方法：

提高事务隔离级别，事务已提交读（read uncommit->read commit）设置事务隔离级别

验证

提升事务隔离级别后的脏读测试

session1：1100-100，未提交

session2：读到1100。没有读到session1未提交前的修改。避免了脏读

session1：回滚1000->1100

session2：存入200。1100+200=1300

成功避免脏读

不可重复读

理解: 事务a多次读取数据，事务b在a的读取过程中。修改了数据。导致数据a多次读取数据时值不一致确保环境：事务a回滚正常，b也都提交commit。两者隔离级别read commit

a，b开启事务

a：1300

b：存入300，未提交：1300+300=1600

a：1300，因为此事务级别，避免了脏读

b：提交修改

a：重新读取1600

第一次读1300，第二次读1600 上帝视角没问题但如果此时a没有读到新数据，在旧数据进行操作后果很坏。取出的值并不可靠。。。和git的冲突很像

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

rr（可重复读级）别下是如何避免幻读的为什么只实现伪MVCC，因为没有实现多版本共存，undoLog只是串行化的后果

当前读，快照读

当前读：当前加锁快照读：不加锁的条件，在事务隔离级别不为Serializable可串行化，的前提下，才成立当Serializable可串行化时，快照读退化成当前读强制的进行排序，在每个读读数据行上添加共享锁。会导致大量超时现象和锁竞争。快照读是为了，提升并发性能的考虑。多版本并发控制，及MVCC（是行级锁的变种，很多情况下避免了加锁的操作，因此开销更低）由于是多版本并发，快照读很可能读到的不是最新版本，可能是之前的历史版本