敖丙思维导图-Mysql数据库_敖丙 csdn

敖丙思维导图系列目录

这些知识整理都是自己查阅帅丙资料加以总结滴~ 每周都会更新知识进去。
如有不全或错误还请大家在评论中指出~

---

1. 敖丙思维导图-集合
2. 敖丙思维导图-多线程之synchronized\ThreadLocal\Lock\Volatitle\线程池
3. 敖丙思维导图-JVM知识整理
4. 敖丙思维导图-Spring
5. 敖丙思维导图-Redis
6. 敖丙思维导图-RocketMQ+Zookeeper
7. 敖丙思维导图-Mysql数据库
8. 敖丙思维导图-网络基础

数据库三范式

高级别范式的依赖于低级别的范式
一：确保每列的原子性。属性不可分。
二：非主键列不存在对主键的部分依赖 （要求每个表只描述一件事情。比如学生选课表Sname, Sdept 和 Mname 都部分依赖于键码，当一个学生选修了多门课时，这些数据就会出现多次，造成大量冗余数据。）
三：满足第二范式，并且表中的列不存在对非主键列的传递依赖 （Sno -> Sdept -> Mname，改成Sno -> Sdept ，Sdept -> Mname）

分库分表

1、单表记录条数达到百万或千万级别时
2、解决表锁的问题
水平分表：表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询次数
垂直分表：把主键和一些列放在一个表，然后把主键和另外的列放在另一个表中。
Sharding 策略：哈希取模：hash(key)%N。范围：可以是 ID 范围也可以是时间范围。映射表：使用单独的一个数据库来存储映射关系

设定网站用户数量在千万级，但是活跃用户数量只有1%，如何通过优化数据库提高活跃用户访问速度？
可以使用MySQL的分区，把活跃用户分在一个区，不活跃用户分在另外一个区，本身活跃用户区数据量比较少，因此可以提高活跃用户访问速度。
还可以水平分表，把活跃用户分在一张表，不活跃用户分在另一张表，可以提高活跃用户访问速度。

分区 （垂直分区/水平分区）

分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。

在下面的场景中，分区可以起到非常大的作用：

1. 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他都是历史数据
2. 分区表的数据更容易维护，如：想批量删除大量数据可以使用清除整个分区的方式。另外，还可以对一个独立分区进行优化、检查、修复等操作
3. 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备
4. 可以使用分区表来避免某些特殊的瓶颈，如：innodb的单个索引的互斥访问，ext3文件系统的inode锁竞争等
5. 如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好
6. 优化查询，在where字句中包含分区列时，可以只使用必要的分区来提高查询效率，同时在涉及sum()和count()这类聚合函数的查询时，可以在每个分区上面并行处理，最终只需要汇总所有分区得到的结果。

事务隔离级别

Read uncommitted 、Read committed 、Repeatable read 、Serializable
​​​​

1.脏读：
脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
2.不可重复读：
是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。（即不能读到相同的数据内容）
例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。如果只有在作者全部完成编写后编辑人员才可以读取文档，则可以避免该问题。
3.幻读:
是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象
发生了幻觉一样。
例如，一个编辑人员更改作者提交的文档，但当生产部门将其更改内容合并到该文档的主复本时，发现作者已将未编辑的新材料添加到该文档中。如果在编辑人员和生产部门完成对原始文档的处理之前，任何人都不能将新材料添加到文档中，则可以避免该问题。

索引

索引与全表扫描主要区别就是扫描数据量大小以及IO的操作，全表扫描是顺序IO，索引扫描是随机IO，MySQL对此做了优化，增加了change buffer特性来提高IO性能。

索引设计->降低接口响应时间->降低服务器配置->降低成本
程序局部性:一个程序在访问了一条数据之后，在之后会有极大的可能再次访问这条数据和访问这条数据的相邻数据。在操作系统的概念中，当我们往磁盘中取数据，假设要取出的数据的大小是1KB，但是操作系统并会取出4KB（一个页表项）的数据。在MySQL的InnoDb引擎中，页的大小是16KB，是操作系统的4倍。
二叉树插入有序数列会变成链表，而平衡二叉查找树，左右子树高度差不得超过1，时间复杂度O(logn)，会产生非常多IO次数。（红黑树也是为了保证树的平衡性，降低树的高度）
B树是一种多路搜索树，每个节点可以拥有多于2个孩子的节点。（不限制路数会退化成有序数组）它常用于文件系统的索引，因为一棵树无法一次性加载进入内存，涉及磁盘操作。可以每次加载B树的一个节点，一步步往下找。

索引的优点

1. 大大减少了服务器需要扫描的数据行数。

2. 帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。

3. 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

B+ Tree

B+树的数据都在叶子节点，同时叶子节点之间还加了指针形成链表。查找起始于根节点，自顶向下遍历树，选择其分离值在要查找值的任意一边的子指针。在节点内部使用二分查找确定位置。

树的常见特性

1. AVL 树
   平衡二叉树，左右子树树高差不超过1，那么和红黑树比较它是严格的平衡二叉树，平衡条件非常严格（树高差只有1），只要插入或删除不满足上面的条件就要通过旋转来保持平衡。由于旋转是非常耗费时间的。所以 AVL 树适用于插入/删除次数比较少，但查找多的场景。
2. 红黑树
   通过对从根节点到叶子节点路径上各个节点的颜色进行约束，确保没有一条路径会比其他路径长2倍，因而是近似平衡的。所以相对于严格要求平衡的AVL树来说，它的旋转保持平衡次数较少。适合，查找少，插入/删除次数多的场景。（现在部分场景使用跳表来替换红黑树，redis 使用跳表(skiplist)而不是使用 red-black，因为简单且排序集通常是许多Zrange或Zrevrange操作的目标，即作为链表遍历跳过列表）
3. B/B+ 树
   多路查找树，出度高，磁盘IO低，一般用于数据库系统中。

索引选择为什么是B +

B+Tree由三部分组成：根root、枝branch以及Leaf叶子，其中root和branch不存储数据，只存储指针地址，数据全部存储在Leaf Node，同时Leaf Node之间用双向链表链接

1. 为啥不用hash索引呢

• 取多条数据时，B+树由于所有数据都在叶子结点，不用跨层，同时由于有链表结构，只需要找到首尾，通过链表就能把所有数据取出来了。
• 数据库中的索引一般是在磁盘上，数据量大的情况可能无法一次装入内存，B+树的设计可以允许数据分批加载，同时树的高度较低，提高查找效率。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

2. B + 树与红黑树

• 磁盘 IO 次数：B+ 树一个节点可以存储多个元素，相对于红黑树的树高更低。
• 磁盘预读特性：为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道。每次会读取页的整数倍。
  操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。

3. B + 树与 B 树

• B+ 树的磁盘 IO 更低 B+ 树的内部节点并没有指向关键字具体信息的指针。因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低。
• B+ 树的查询效率更加稳定 由于非叶子结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。
• B+ 树元素遍历效率高 B+树只要遍历叶子节点就可以实现整棵树的遍历。

InnoDB一棵B+树可以存放多少行数据

1. InnoDB存储引擎的最小存储单元是页，页可以用于存放数据也可以用于存放键值+指针，在B+树中叶子节点存放数据，非叶子节点存放键值+指针。
2. 索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而在去数据页中查找到需要的数据；

先假设B+树高为2，即存在一个根节点和若干个叶子节点，那么这棵B+树的存放总记录数为：根节点指针数*单个叶子节点记录行数。

上文我们已经说明单个叶子节点（页）中的记录数=16K/1K=16。（这里假设一行记录的数据大小为1k）。

假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即（2的14次方）16384/14（类型大小+指针大小）=1170。那么可以算出一棵高度为2的B+树，能存放1170*16=18720条这样的数据记录。

一个高度为3的B+树可以存放：1170117016=21902400条这样的记录。所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

聚集索引与非聚集索引

InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

辅助索引也称为二级索引，索引中除了存储索引列外，还存储了主键id。因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找，这个过程也被称作回表。

1. InnoDB(聚集索引， 主键索引与行记录是存储在一起，必须要有且只有一个)，其中叶子中存放的就是数据行

普通索引，其中叶子节点存储