数据库总结

都是纸老虎
375 阅读12分钟

三大范式

第一范式(1NF)：属性不可分割，即每个属性都是不可分割的原子项。(实体的属性即表中的列)
第二范式(2NF)：满足第一范式；且不存在部分依赖，即非主属性必须完全依赖于主属性。(主属性即主键；完全依赖是针对于联合主键的情况，非主键列不能只依赖于主键的一部分)
第三范式(3NF)：满足第二范式；且不存在传递依赖，即非主属性不能与非主属性之间有依赖关系，非主属性必须直接依赖于主属性，不能间接依赖主属性。（A -> B, B ->C, A -> C）

Mysql

innodb索引类型是什么，b+树。为什么用b+树做索引

主要的目的是减小磁盘IO次数.

  1. mysql有大量范围,排序如果命中索引可以按索引进行查找;未命中索引可以按叶子节间的双向指针进行查找.
  2. B+ 树只有叶子节点存储数据B树内部也存储数据。在查询相同数据量的情况下，B树高度更高，IO次数更多
  3. 从根结点到叶子结点的路。所有关键字查询的路径长度相同，每一个数据的查询效率相当

主要考虑的是IO影响吧。因为B+ 树只有叶子节点存储数据，B树内部也存储数据。在查询相同数据量的情况下，B树高度更高，IO次数更多，然后只能一点点加载数据页。 B树的话，所有的节点都是数据地址。需要在内部节点和叶子之间去查询数据。b树的分支节点也有数据。 b树范围查询只能中序遍历。

B+树只有叶子节点有数据，而且叶子节点之间由双向指针链接，在叶子节点顺序查询会比较快。b+树的数据都集中在叶子节点。非叶子节点只负责索引。b+树的层高会小于B树 平均的IO次数会远小于B树（因为B+树是顺序查找）b+树更擅长范围查询。叶子节点 数据是按顺序放置的双向链表。 b+树可以把索引完全加载至内存中。支持多路，多路的好处：可以每次只加载一个节点的数据进去，因为内存的容量是有限的。【这个就是多路的好处了

B+树只有叶子节点有数据，而且叶子节点之间由双向指针链接，在叶子节点顺序查询会比较快。b+树的数据都集中在叶子节点。非叶子节点只负责索引。**** 平均的IO次数会远小于B树（因为B+树是顺序查找）b+树更擅长范围查询。叶子节点 数据是按顺序放置的双向链表。 b+树可以把索引完全加载至内存中。支持多路，多路的好处：可以每次只加载一个节点的数据进去，因为内存的容量是有限的。【这个就是多路的好处了

www.cnblogs.com/nullzx/p/87… www.cnblogs.com/makai/p/108…

创建表式例

CREATE TABLE `rw_riddle_mission_record` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键唯一识别',
    `user_id` int(10) NOT NULL COMMENT '用户id',
    `mission_id` int(10) unsigned NOT NULL DEFAULT 0  COMMENT '任务id',
    `select_id` tinyint(10) unsigned NOT NULL DEFAULT 0  COMMENT '选项id',
    `is_reward` tinyint(10) unsigned NOT NULL DEFAULT 1  COMMENT '是否发放奖励 1-未发 2-已发',
    `status` tinyint(4) unsigned NOT NULL DEFAULT 0  COMMENT '状态 1 - 未开始 2 - 成功 3- 失败',
     PRIMARY KEY (`id`),
     UNIQUE KEY `mission_user_idx` (`mission_id`,`user_id`),
     KEY `idx_mission_info` (`user_id`,`status`,`is_reward`)
)  ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

添加索引实例

 CREATE UNIQUE INDEX `mission_user_idx` ON `rw_riddle_mission_record`(`mission_id`,`user_id`);

插入数据

INSERT INTO rw_riddle_mission_record(`user_id`, `mission_id`, `select_id`, `is_reward`, `status`) VALUES(90005638, 1,1, 0, 2);

MySql版本与特点

MySQL4.0版本 增加了子查询的支持，字符集增加UTF-8，GROUP BY 语句增加了ROLLUP，mysql.user表采用了更好的加密算法，InnoDB开始 支持单独的表空间。
MySQL5.0版本 增加了Stored procedures、Views、Cursors、Triggers、 XA transactions的支持，增加了INFORATION_SCHEMA系统数据库
MySQL5.5版本 默认存储引擎更改为InnoDB，提高性能和可扩展性， 增加半同步复制。
MySQL5.6版本 提高InnoDB性能，支持延迟复制。
MySQL5.7版本 提升数据库性能和存储引擎，更健壮的复制功能，增 加sys系统库存放数据库管理信息。

Mysql数据库不能设置null

  • 布尔值逻辑中的NULL：NULL既不为1也不为0，它代表“未知”或“不可知”。

  • 运算结果为NULL：任何值与NULL进行比较运算或逻辑运算（如>, <, IN, NOT IN等），结果都是NULL，即“未知”。

  • IN和NULL比较问题：当使用IN来比较时，NULL值无法正常匹配，因此无法查询出NULL的记录。

  • NOT IN的特殊性：如果NOT IN后面包含NULL，则查询结果为“空集”，即无论任何情况下都无法查询到结果。

  • 判断NULL的方式：必须使用IS NULL或IS NOT NULL来判断字段是否为NULL，普通的比较运算无效。

  • COUNT函数差异：COUNT(字段)无法统计NULL值，但COUNT(*)可以统计包括NULL在内的所有行数。

  • 主键与NULL的关系：主键列自动设置为NOT NULL，不允许NULL值出现。

  • 避免使用NULL的建议：由于NULL带来的复杂性和潜在错误，强烈建议在设计字段时禁止使用NULL，并为字段设置默认值。

  1. null与''不一样查询时会造成困扰
  2. 索引效率降低
  3. count数据丢失

连接数

数据库数目状态
mysql1000-1500健康数目
mysql2000最高
redis3000 pipeline 16w写
redis20w读

事务

事务通常是由一个或一组SQL组成的，组成一个事务的SQL一般都是一个业务操作满足ACID原则

ACID

Atomicity原子性 ： 事务中的一组SQL会被看成一个不可分割的整体，当成一个操作看待。要么全部执行成功，要么全部执行失败。基于undo-log来实现的，因为在该日志中会生成相应的反SQL，执行失败时会利用该日志来回滚所有写入操作
Consistency一致性 一致性状态变化为另一个一致性状态基于redo-log实现的
Isolation：隔离性 事务之间都是独立的过锁机制和MVCC机制实现
Durability：持久性 一个事务被提交之后。它对数据库中数据的改变是持久的

隔离级别

  • Read uncommitted/RU：读未提交
  • Read committed/RC：读已提交
  • Repeatable read/RR：可重复读
  • Serializable：序列化/串行化

脏读 ： A事务的修改在未提交时被B事务读到了-UPDATE
不可重复读 ： A事务多次使用同一sql多次读取数据，但是由于B事务在其间做了修改导致A事务读取数据的结果不同-UPDATE
幻读 ： 它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。-INSERT

隔离级别脏读不可重复读幻读
READ UNCOMMITTEDPossiblePossiblePossible
READ COMMITTEDNot PossiblePossiblePossible
REPEATABLE READNot PossibleNot PossiblePossible
SERIALIZABLENot PossibleNot PossibleNot Possible

MySQL却选择可重复读（Repeatable-Read） 作默认隔离级别

隔离级别实现机制
READ UNCOMMITTED写操作加排它锁，读操作不加锁
READ COMMITTED写操作加排它锁，读操作mvcc
REPEATABLE READ写操作加排它锁，读操作mvcc仅在第一次select时生成readview
SERIALIZABLE写操作加临键锁，读操作加共享锁

死锁解决

也就是在业务允许的情况下，尽量缩短一个事务持有锁的时间、减小锁的粒度以及锁的数量。

MVCC

多版本并发控制，MVCC是一种并发控制的方法，减少事务中需要锁定的行数。

MVCC实现

基于TRX_ID(transaction_id),Undo-log,ReadView实现 mvcc机制 主要是在开启时候后,执行select操作时生成一个ReadView结构 ReadView有以下四部分 creator_trx_id：代表创建当前这个ReadView的事务ID。 trx_ids：表示在生成当前ReadView时，系统内活跃的事务ID列表。 up_limit_id：活跃的事务列表中，最小的事务ID。 low_limit_id：表示在生成当前ReadView时，系统中要给下一个事务分配的ID值。

判断行数据中的隐藏列trx_id与ReadView.creator_trx_id是否相同：

相同：代表创建ReadView和修改行数据的事务是同一个，自然可以读取最新版数据。 不相同：代表目前要查询的数据，是被其他事务修改过的，继续往下执行。

③判断隐藏列trx_id是否小于ReadView.up_limit_id最小活跃事务ID：

小于：代表改动行数据的事务在创建快照前就已结束，可以读取最新版本的数据。 不小于：则代表改动行数据的事务还在执行，因此需要继续往下判断。

④判断隐藏列trx_id是否小于ReadView.low_limit_id这个值：

大于或等于：代表改动行数据的事务是生成快照后才开启的，因此不能访问最新版数据。 小于：表示改动行数据的事务ID在up_limit_id、low_limit_id之间，需要进一步判断。

⑤如果隐藏列trx_id小于low_limit_id，继续判断trx_id是否在trx_ids中：

在：表示改动行数据的事务目前依旧在执行，不能访问最新版数据。 不在：表示改动行数据的事务已经结束，可以访问最新版的数据。

经过上面判断就能知道是否返回最新数据，如果不能则去Undo-log日志中获取旧版本的数据返回。

Mysql主从复制的过程

  1. Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容。
  2. Master接收到来自Slave的IO进程的请求后，负责复制的IO进程会根据请求信息读取日志指定位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，还包返回的bin-log文件名称以及bin-log的位置。
  3. Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master从何处开始读取日志。
  4. Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行。

日志类型

Undo-log : 用来给MySQL撤销SQL操作的
Redo-log ： Redo-log是一种预写式日志，即在向内存写入数据前，会先写日志，当后续数据未被刷写到磁盘、MySQL崩溃时，就可以通过日志来恢复数据，确保所有提交的事务都会被持久化 Bin-log ： 记录所有对数据库表结构变更和表数据修改的操作，对于select、show这类读操作并不会记录。

Redo-log 恢复过程https://ost.51cto.com/posts/11047

MySQL查询执行路径

  1. 客户端发送一条查询给服务器；

  2. 服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；

  3. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；

  4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；

  5. 将结果返回给客户端。

MySQL5.7中读操作的执行流程

  • ①读取数据之前首先会对B+Tree加一个共享锁。

  • ②在基于树检索数据的过程中，对于所有走过的叶节点会加一个共享锁。

  • ③找到需要读取的目标叶子节点后，先加一个共享锁，释放步骤②上加的所有共享锁。

  • ④读取最终的目标叶子节点中的数据，读取完成后释放对应叶子节点上的共享锁。

MySQL5.7中乐观写入的执行流程

  • ①乐观写入之前首先会对B+Tree加一个共享锁。

  • ②在基于树检索修改位置的过程中，对于所有走过的叶节点会加一个共享锁。

  • ③找到需要写入数据的目标叶子节点后，先加一个排他锁，释放步骤②上加的所有共享锁。

  • ④修改目标叶子节点中的数据后，释放对应叶子节点上的排他锁。

MySQL5.7中悲观写入的执行流程
  • ①悲观更新之前首先会对B+Tree加一个共享排他锁。
  • ②由于①上已经加了SX锁，因此当前事务执行过程中会阻塞其他尝试更改树结构的事务。
  • ③遍历查找需要写入数据的目标叶子节点，找到后对其分支加上排他锁，释放①中加的SX锁。
  • ④执行SMO操作，也就是执行悲观写入操作，完成后释放步骤③中在分支上加的排他锁。

如果需要修改多个数据时，会在遍历查找的过程中，记录下所有要修改的目标节点。

索引失效场景

heapdump.cn/article/396…

avatar
文章
阅读
粉丝