2022-10-27 490
但是在我们的日常开发当中，「并不是所有的表一定要满足三大范式」，有时候冗余几个字段可以少关联几张表，带来的查询效率的提升有可能是质变的
1.普通索引的 B+ 树上存放的是主键索引的值，如果该值较大，会「导致普通索引的存储空间较大」
2.使用自增 id 做主键索引新插入数据只要放在该页的最尾端就可以，直接「按照顺序插入」，不用刻意维护
3.页分裂容易维护，当插入数据的当前页快满时，会发生页分裂的现象，如果主键索引不为自增 id，那么数据就可能从页的中间插入，页的数据会频繁的变动，「导致页分裂维护成本较高」
1.通过连接器跟客户端「建立连接」
2.通过查询「缓存查询」之前是否有查询过该 sql
有则直接返回结果
没有则执行第三步
3.通过分析器「分析该 sql 的语义」是否正确，包括格式，表等等
4.通过优化器「优化该语句」，比如选择索引，join 表的连接顺序
5.「验证权限」，验证是否有该表的查询权限
6.通过执行器调用存储引擎执行该 sql，然后返回「执行结果」
用以下语句来举例，c 字段无索引，id 为主键索引
updateTsetc=c+1whereid=2;
1.执行器先找引擎取 id=2 这一行。id 是主键，引擎直接用树搜索找到这一行
2.执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口「写入这行新数据」
3.引擎将这行新数据更新到内存中，同时将这个更新操作「记录到 redo log 里面」，此时 redo log 处于 「prepare」 状态。然后告知执行器执行完成了，随时可以提交事务
4.执行器「生成这个操作的 binlog」，并把 binlog 「写入磁盘」
5.执行器调用引擎的「提交事务」接口，引擎把刚刚写入的 redo log 改成提交(commit)状态，「更新完成」
可以看到，「如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致」。
相信大家小时候学习汉字的时候都会查字典，想想你查字典的步骤，我们是通过汉字的首字母 a～z 一个一个在字典目录中查找，最终找到该字的页数。想想，如果没有目录会怎么样，最差的结果是你有可能翻到字典的最后一页才找到你想要找的字。
索引就「相当于我们字典中的目录」，可以极大的提高我们在数据库的查询效率。
以下随便列举几个，不同版本的 mysql 场景不一
1.最左前缀法则(带头索引不能死，中间索引不能断
2.不要在索引上做任何操作(计算、函数、自动/手动类型转换)，不然会导致索引失效而转向全表扫描
3.不能继续使用索引中范围条件(bettween、<、>、in等)右边的列，如：
selectafromuserwherec>5andb=4；
4.索引字段上使用(!= 或者 < >)判断时，会导致索引失效而转向全表扫描
5.索引字段上使用 is null / is not null 判断时，会导致索引失效而转向全表扫描。
6.索引字段使用like以通配符开头(‘%字符串’)时，会导致索引失效而转向全表扫描，也是最左前缀原则。
7.索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全表扫描
8.索引字段使用 or 时，会导致索引失效而转向全表扫描
B+ 树只在叶子结点储存数据，非叶子结点不存具体数据，只存 key，查询更稳定，增大了广度，而一个节点就是磁盘一个内存页，内存页大小固定，那么相比 B 树，B- 树这些「可以存更多的索引结点」，宽度更大，树高矮，节点小，拉取一次数据的磁盘 IO 次数少，并且 B+ 树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，效率更高。
WAL 就是 Write-Ahead Logging，其实就是「所有的修改都先被写入到日志中，然后再写磁盘」，用于保证数据操作的原子性和持久性。
好处:
1.「读和写可以完全地并发执行」，不会互相阻塞
2.先写入 log 中，磁盘写入从「随机写变为顺序写」，降低了 client 端的延迟就。并且，由于顺序写入大概率是在一个磁盘块内，这样产生的 io 次数也大大降低
3.写入日志当数据库崩溃的时候「可以使用日志来恢复磁盘数据」
回表就是先通过数据库索引扫描出该索引树中数据所在的行，取到主键 id，再通过主键 id 取出主键索引数中的数据，即基于非主键索引的查询需要多扫描一棵索引树.
如果存在某些被索引的列的判断条件时，MySQL 将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合 MySQL 服务器传递的条件，「只有当索引符合条件时才会将数据检索出来返回给 MySQL 服务器」 。
覆盖索引(covering index)指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取，可以减少回表的次数。比如:
selectidfromtwhereage=1;
id 为主键索引，age 为普通索引，age 这个索引树存储的就是逐渐信息，可以直接返回
最左前缀其实说的是，在 where 条件中出现的字段，「如果只有组合索引中的部分列，则这部分列的触发索引顺序」，是按照定义索引的时候的顺序从前到后触发，最左面一个列触发不了，之后的所有列索引都无法触发。
比如「有一个 (a,b,c) 的组合索引」
wherea=1andb=1
此时 a,b 会命中该组合索引
wherea=1andc=1
此时 a 会命中该组合索引, c 不会
whereb=1andc=1
此时不会命中该组合索引
查询
更新
所以「唯一索引更加适合查询的场景,普通索引更适合插入的场景」
事务是指是程序中一系列操作必须全部成功完成，有一个失败则全部失败。
特性
「1.原子性(Atomicity)」：要么全部执行成功，要么全部不执行。
「2.一致性(Consistency)」：事务前后数据的完整性必须保持一致。
「3.隔离性(Isolation)」：隔离性是当多个事务同事触发时，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
「4.持久性(Durability)」：事务完成之后的改变是永久的。
1.「读提交」:即能够「读取到那些已经提交」的数据
2.「读未提交」:即能够「读取到没有被提交」的数据
3.「可重复读」:可重复读指的是在一个事务内，最开始读到的数据和事务结束前的「任意时刻读到的同一批数据都是一致的」
4.「可串行化」:最高事务隔离级别，不管多少事务，都是「依次按序一个一个执行」
「脏读」
「不可重复读」
「幻读」
binlog 是归档日志，属于 Server 层的日志，是一个二进制格式的文件，用于「记录用户对数据库更新的SQL语句信息」。
主要作用
undolog 是 InnoDB 存储引擎的日志，用于保证数据的原子性，「保存了事务发生之前的数据的一个版本，也就是说记录的是数据是修改之前的数据，可以用于回滚」，同时可以提供多版本并发控制下的读(MVCC)。
主要作用
relaylog 是中继日志，「在主从同步的时候使用到」，它是一个中介临时的日志文件，用于存储从master节点同步过来的binlog日志内容。
master 主节点的 binlog 传到 slave 从节点后，被写入 relay log 里，从节点的 slave sql 线程从 relaylog 里读取日志然后应用到 slave 从节点本地。从服务器 I/O 线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后 SQL 线程会读取 relay-log 日志的内容并应用到从服务器，从而「使从服务器和主服务器的数据保持一致」。
redolog 是 「InnoDB 存储引擎所特有的一种日志」，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。
可以做「数据恢复并且提供 crash-safe 能力」
当有增删改相关的操作时，会先记录到 Innodb 中，并修改缓存页中的数据，「等到 mysql 闲下来的时候才会真正的将 redolog 中的数据写入到磁盘当中」。
InnoDB 的 redo log 是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么总共就可以记录4GB的操作。「从头开始写，写到末尾就又回到开头循环写」。
所以，如果数据写满了但是还没有来得及将数据真正的刷入磁盘当中，那么就会发生「内存抖动」现象，从肉眼的角度来观察会发现 mysql 会宕机一会儿，此时就是正在刷盘了。
1.「redolog」 是 「Innodb」 独有的日志，而 「binlog」 是 「server」 层的，所有的存储引擎都有使用到
2.「redolog」 记录了「具体的数值」，对某个页做了什么修改，「binlog」 记录的「操作内容」
3.「binlog」 大小达到上限或者 flush log 「会生成一个新的文件」，而 「redolog」 有固定大小「只能循环利用」
4.「binlog 日志没有 crash-safe 的能力」，只能用于归档。而 redo log 有 crash-safe 能力。
MVCC:多版本并发控制，是现代数据库(包括 MySQL、Oracle、PostgreSQL 等)引擎实现中常用的处理读写冲突的手段，目的在于「提高数据库高并发场景下的吞吐性能」。
在 MVCC 协议下，每个读操作会看到一个一致性的快照，「这个快照是基于整个库的」，并且可以实现非阻塞的读，用于「支持读提交和可重复读隔离级别的实现」。
MVCC 允许数据具有多个版本，这个版本可以是时间戳或者是全局递增的事务 ID，在同一个时间点，不同的事务看到的数据是不同的，这个修改的数据是「记录在 undolog 中」的。
「1.没有用到索引」
比如函数导致的索引失效，或者本身就没有加索引
「2.表数据量太大」
考虑分库分表吧
「3.优化器选错了索引」
「考虑使用」 force index 强制走索引
「1. 数据库在刷新脏页」
比如 「redolog 写满了」，「内存不够用了」释放内存如果是脏页也需要刷，mysql 「正常空闲状态刷脏页」
「2. 没有拿到锁」
1.master 主库将此次更新的事件类型「写入到主库的 binlog 文件」中
2.master 「创建 log dump 线程通知 slave」 需要更新数据
3.「slave」 向 master 节点发送请求，「将该 binlog 文件内容存到本地的 relaylog 中」
4.「slave 开启 sql 线程」读取 relaylog 中的内容，「将其中的内容在本地重新执行一遍」，完成主从数据同步
「同步策略」：
1.「全同步复制」：主库强制同步日志到从库，等全部从库执行完才返回客户端，性能差
2.「半同步复制」：主库收到至少一个从库确认就认为操作成功，从库写入日志成功返回ack确认
1.MySQL 5.6 版本以后，提供了一种「并行复制」的方式，通过将 SQL 线程转换为多个 work 线程来进行重放
2.「提高机器配置」(王道)
3.在业务初期就选择合适的分库、分表策略，「避免单表单库过大」带来额外的复制压力
4.「避免长事务」
5.「避免让数据库进行各种大量运算」
6.对于一些对延迟很敏感的业务「直接使用主库读」
在使用 delete 删除数据时，其实对应的数据行并不是真正的删除，是「逻辑删除」，InnoDB 仅仅是将其「标记成可复用的状态」，所以表空间不会变小
当定义varchar长度小于等于255时，长度标识位需要一个字节(utf-8编码)
当大于255时，长度标识位需要两个字节，并且建立的「索引也会失效」
1.「本地消息表」
2.「消息事务」
3.「二阶段提交」
4.「三阶段提交」
5.「TCC」
6.「最大努力通知」
7.「Seata 框架」
七种分布式事务的解决方案，一次讲给你听
以下并不全，主要理解下锁的意义即可
1.并发情况下，数据库「连接池容易被撑爆」
2.「容易造成大量的阻塞和锁超时」
长事务还占用锁资源，也可能拖垮整个库，
3.执行时间长，容易造成「主从延迟」
4.「回滚所需要的时间比较长」
事务越长整个时间段内的事务也就越多
5.「undolog 日志越来越大」
长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。
buffer pool 是一块内存区域，为了「提高数据库的性能」，当数据库操作数据的时候，把硬盘上的数据加载到 buffer pool，不直接和硬盘打交道，操作的是 buffer pool 里面的数据，数据库的增删改查都是在 buffer pool 上进行
buffer pool 里面缓存的数据内容也是一个个数据页
其中「有三大双向链表」:
「free 链表」
「flush 链表」
「lru 链表」
预读机制:
Buffer Pool 有一项特技叫预读，存储引擎的接口在被 Server 层调用时，会在响应的同时进行预判，将下次可能用到的数据和索引加载到 Buffer Pool
1.「表结构优化」
1.1拆分字段
1.2字段类型的选择
1.3字段类型大小的限制
1.4合理的增加冗余字段
1.5新建字段一定要有默认值
2.「索引方面」
2.1索引字段的选择
2.2利用好mysql支持的索引下推，覆盖索引等功能
2.3唯一索引和普通索引的选择
3.「查询语句方面」
3.1避免索引失效
3.2合理的书写where条件字段顺序
3.3小表驱动大表
3.4可以使用force index()防止优化器选错索引
4.「分库分表」
原文链接：https://77isp.com/post/10507.html
=========================================
https://77isp.com/ 为 “云服务器技术网” 唯一官方服务平台，请勿相信其他任何渠道。
数据库技术 2022-03-28
网站技术 2022-11-26
网站技术 2023-01-07
网站技术 2022-11-17
Windows相关 2022-02-23
网站技术 2023-01-14
Windows相关 2022-02-16
Windows相关 2022-02-16
Linux相关 2022-02-27
数据库技术 2022-02-20
抠敌 2023年10月23日
嚼餐 2023年10月23日
男忌 2023年10月22日
瓮仆 2023年10月22日
簿偌 2023年10月22日
扫码二维码
获取最新动态
