数据库笔记
Carbda Lv3

2021.12.9

存储引擎

查看存储引擎:

1
mysql> show engines;

MySQL 当前默认的存储引擎是 InnoDB,并且在 5.7 版本所有的存储引擎中只有 InnoDB 是事务性存储引擎,也就是说只有 InnoDB 支持事务。

MyISAM 和 InnoDB 的区别

1.是否支持行级锁(?)
MyISAM 只有表级锁(table-level locking),而 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
也就说,MyISAM 一锁就是锁住了整张表,这在并发写的情况下是多么滴憨憨啊!这也是为什么 InnoDB 在并发写的时候,性能更牛皮了!
2.是否支持事务
MyISAM 不提供事务支持。
InnoDB 提供事务支持,具有提交(commit)和回滚(rollback)事务的能力。
3.是否支持外键
MyISAM 不支持,而 InnoDB 支持。
4.是否支持数据库异常崩溃后的安全恢复
MyISAM 不支持,而 InnoDB 支持。
使用 InnoDB 的数据库在异常崩溃后,数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log (重做日志)。
5.是否支持 MVCC(?)
MyISAM 不支持,而 InnoDB 支持。
讲真,这个对比有点废话,毕竟 MyISAM 连行级锁都不支持。
MVCC 可以看作是行级锁的一个升级,可以有效减少加锁操作,提供性能。

并发事务带来哪些问题?

脏读(Dirty read): 当一个事务正在访问数据并且对数据进行了修改,而这种修改还没有提交到数据库中,这时另外一个事务也访问了这个数据,然后使用了这个数据。因为这个数据是还没有提交的数据,那么另外一个事务读到的这个数据是“脏数据”,依据“脏数据”所做的操作可能是不正确的。
丢失修改(Lost to modify): 指在一个事务读取一个数据时,另外一个事务也访问了该数据,那么在第一个事务中修改了这个数据后,第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失,因此称为丢失修改。 例如:事务 1 读取某表中的数据 A=20,事务 2 也读取 A=20,事务 1 修改 A=A-1,事务 2 也修改 A=A-1,最终结果 A=19,事务 1 的修改被丢失。
不可重复读(Unrepeatable read): 指在一个事务内多次读同一数据。在这个事务还没有结束时,另一个事务也访问该数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况,因此称为不可重复读。
幻读(Phantom read): 幻读与不可重复读类似。它发生在一个事务(T1)读取了几行数据,接着另一个并发事务(T2)插入了一些数据时。在随后的查询中,第一个事务(T1)就会发现多了一些原本不存在的记录,就好像发生了幻觉一样,所以称为幻读。

事务隔离级别有哪些?

SQL 标准定义了四个隔离级别:

  • READ-UNCOMMITTED(读取未提交): 最低的隔离级别,允许读取尚未提交的数据变更,可能会导致脏读、幻读或不可重复读。
  • READ-COMMITTED(读取已提交): 允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生。
  • REPEATABLE-READ(可重复读): 对同一字段的多次读取结果都是一致的,除非数据是被本身事务自己所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生。
  • SERIALIZABLE(可串行化): 最高的隔离级别,完全服从 ACID 的隔离级别。所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,也就是说,该级别可以防止脏读、不可重复读以及幻读。

**mysql **的 默认隔离级别:可重复读

2021.12.13

MySQL有几种锁

按锁粒度分类

行级锁&表级锁&页级锁

  1. 行级锁:
    描述:行级锁是mysql中锁定粒度最细的一种锁。表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突,其加锁粒度最小,但加锁的开销也最大。行级锁分为共享锁和排他锁
    特点:开销大,加锁慢,会出现死锁。发生锁冲突的概率最低,并发度也最高。
  2. 表级锁:
    描述:表级锁是mysql中锁定粒度最大的一种锁,表示对当前操作的整张表加锁,它实现简单,资源消耗较少,被大部分mysql引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。表级锁定分为表共享读锁(共享锁)与表独占写锁(排他锁)
    特点:开销小,加锁快,不会出现死锁。发生锁冲突的概率最高,并发度也最低。
  3. 页级锁:
    描述:页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快,但冲突多,行级冲突少,但速度慢。因此,采取了折中的页级锁,一次锁定相邻的一组记录。BDB 支持页级锁。
    特点:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

按照锁的共享策略分类

共享锁、排他锁、意向共享锁、意向排他锁

  1. 共享锁:
    共享锁又称读锁,是读取操作创建的锁。其他用户可以并发读取数据,但任何事务都不能对数据进行修改(获取数据上的排他锁),直到已释放所有共享锁。
    如果事务T对数据A加上共享锁后,则其他事务只能对A再加共享锁,不能加排他锁。获准共享锁的事务只能读数据,不能修改数据。

  2. 排他锁:
    排他锁又称写锁、独占锁,如果事务T对数据A加上排他锁后,则其他事务不能再对A加任何类型的封锁。获准排他锁的事务既能读数据,又能修改数据。

  3. 意向锁:
    意向锁是表级锁,其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型。InnoDB 中的两个表锁:

    • 意向共享锁(IS):表示事务准备给数据行加入共享锁,也就是说一个数据行加共享锁前必须先取得该表的IS锁;
    • 意向排他锁(IX):类似上面,表示事务准备给数据行加入排他锁,说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

    意向锁是 InnoDB 自动加的,不需要用户干预。

从加锁策略上分

从加锁策略上分:乐观锁和悲观锁
悲观锁认为对于同一个数据的并发操作,一定是会发生修改的(或者增删改多,查少),哪怕没有修改,也会认为修改。因此对于同一个数据的并发操作,悲观锁采取加锁的形式。悲观的认为,不加锁的并发操作一定会出问题。
乐观锁则认为对于同一个数据的并发操作,是不会发生修改的(或者增删改少,查多)。在更新数据的时候,会采用不断尝试更新的方式来修改数据。也就是先不管资源有没有被别的线程占用,直接取申请操作,如果没有产生冲突,那就操作成功,如果产生冲突,有其他线程已经在使用了,那么就不断地轮询。乐观的认为,不加锁的并发操作是没有事情的。就是通过记录一个数据历史记录的多个版本,如果修改完之后发现有冲突再将版本返回到没修改的样子,乐观锁就是不加锁。好处就是减少上下文切换,坏处是浪费CPU时间。

2022.1.5

索引

Hash表

哈希表是键值对的集合,通过键(key)即可快速取出对应的值(value),因此哈希表可以快速检索数据

为什么MySQL 没有使用其作为索引的数据结构呢?

  1. Hash 冲突问题 :多个不同的 key 最后得到的 index 相同。
  2. Hash 索引不支持顺序和范围查询: 假如我们要对表中的数据进行排序或者进行范围查询,那 Hash 索引可就不行了。

B 树& B+树

  1. B 树的所有节点既存放键(key) 也存放 数据(data),而 B+树只有叶子节点存放 key 和 data,其他内节点只存放 key。
  2. B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
  3. B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找,可能还没有到达叶子节点,检索就结束了。而 B+树的检索效率就很稳定了,任何查找都是从根节点到叶子节点的过程,叶子节点的顺序检索很明显。

索引类型

主键索引

数据表的主键列使用的就是主键索引。

一张数据表有只能有一个主键,并且主键不能为 null,不能重复。

在 MySQL 的 InnoDB 的表中,当没有显示的指定表的主键时,InnoDB 会自动先检查表中是否有唯一索引且不允许存在null值的字段,如果有,则选择该字段为默认的主键,否则 InnoDB 将会自动创建一个自增主键。

二级索引(辅助索引)

二级索引又称为辅助索引,是因为二级索引的叶子节点存储的数据是主键。也就是说,通过二级索引,可以定位主键的位置。

唯一索引,普通索引,前缀索引等索引属于二级索引。

  1. 唯一索引(Unique Key) :唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据,但是允许数据为 NULL,一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性,而不是为了查询效率。
  2. 普通索引(Index)普通索引的唯一作用就是为了快速查询数据,一张表允许创建多个普通索引,并允许数据重复和 NULL。
  3. 前缀索引(Prefix) :前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引,相比普通索引建立的数据更小, 因为只取前几个字符。(在这个点面试吃过亏)

聚集索引与非聚集索引

聚集索引

聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。

优点:
聚集索引的查询速度非常的快,因为整个 B+树本身就是一颗多叉平衡树,叶子节点也都是有序的,定位到索引的节点,就相当于定位到了数据。

缺点:

  1. 依赖于有序的数据 :因为 B+树是多路平衡树,如果索引的数据不是有序的,那么就需要在插入时排序,如果数据是整型还好,否则类似于字符串或 UUID 这种又长又难比较的数据,插入或查找的速度肯定比较慢。
  2. 更新代价大 : 如果对索引列的数据被修改时,那么对应的索引也将会被修改,而且聚集索引的叶子节点还存放着数据,修改代价肯定是较大的,所以对于主键索引来说,主键一般都是不可被修改的。

非聚集索引

非聚集索引即索引结构和数据分开存放的索引。二级索引属于非聚集索引。

优点:

更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了,非聚集索引的叶子节点是不存放数据的

缺点:

  1. 跟聚集索引一样,非聚集索引也依赖于有序的数据
  2. 可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后,可能还需要根据指针或主键再到数据文件或表中查询。

覆盖索引

如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为“覆盖索引”。

覆盖索引即需要查询的字段正好是索引的字段,那么直接根据该索引,就可以查到数据了, 而无需回表查询。

如主键索引,如果一条 SQL 需要查询主键,那么正好根据主键索引就可以查到主键。

再如普通索引,如果一条 SQL 需要查询 name,name 字段正好有索引, 那么直接根据这个索引就可以查到数据,也无需回表。

注意:不是所有索引都可以成为覆盖索引,覆盖索引需要存储索引列的值,而哈希索引、全文索引不存储索引列的值,所以只有B+树索引可以用作覆盖索引

最左匹配原则

如果SQL语句中用到了组合索引中的最左边的索引,那么这条SQL语句就可以利用这个组合索引进行匹配。

遇到范围查询(如 > 、< 、between 、like)就会停止匹配,后面的字段不会用到索引。

举个例子,例如:对于 (a,b,c) 建立索引,查询条件用 a/ab/abc 会走索引,bc 则不会走索引。

再比如:如果查询条件为 a = 1 AND b > 2 AND c = 3,那么 a 和 b 能用到索引,c 则不能。

前缀索引

前缀索引是指对文本或者字符串的前几个字符建立索引,这样索引的长度更短,查询速度更快。

有时候需要再很长的字符列上创建索引,这会造成索引特别大且慢,使用前缀索引可以避免这个问题。

创建前缀索引的关键在于选择足够长的前缀以保证比较高的索引选择性。

索引选择性越高,查询效率就越高,因为选择性高的索引可以让MySQL再寻找时过滤掉高多的数据行。

索引的设计原则

1、使用区分度高的字段作为索引列,索引的效果更好。

2、尽量使用短索引,对于较长的字符串进行索引时应该指定一个较短的前缀长度,因为较小的索引涉及到的磁盘 I/O 较少,查询速度更快。

3、索引不是越多越好,每个索引都需要额外的物理空间,维护也需要时间。

4、利用最左前缀原则。

索引失效的情况

1、对于组合索引,不是使用组合索引最左边的字段,则不会使用索引

2、以 % 开头的 like 查询如 %abc,无法使用索引;非 % 开头的 like 查询如 abc%,相当于规范查询,会使用索引

3、查询条件用 or 连接

4、查询条件中列类型时字符串,没有使用引号,可能会因为类型不同发生隐式转换,使索引失效

5、判断索引是否不等于某个值时(???)

6、对索引进行运算(索引列上参与计算会导致索引失效)

2022.8.24

可重复读的实现原理

使用的是一种叫 多版本并发控制 的方式,类似于乐观锁的一种实现方式。

InnoDB在每行记录后面保存两个隐藏的列,分别保存了这个行的创建时间(版本号)和行的删除时间(版本号)。这里存储的并不是实际的时间值,而是系统版本号,当数据被修改时,版本号加1。
在读取事务开始时,系统会给当前读事务一个版本号,然后,事务会读取版本号<=当前版本号的数据。
此时如果其他写事务修改了这条数据(增删改),那么这条数据的版本号就会加1,从而比当前事务的版本号高,所以,当前事务读取的并不是更新后的数据,而是当前事务对应版本下的数据。

基本知识点(以前学的网课)

2021.10.17 网课

顺序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
select

...

from

...

where

...

group by

...

having

...

order by

...

limit ... ;

分组查询:

如果有group by字段,select后面只能跟参加分组的字段以及分组函数

having:having不能单独使用,不能代替where,必须和group by联合使用

外连接:

相比于内连接,可以查出没有匹配上的实例

外连接的查询条数 >= 内连接的查询条数

union:

合并查询结果

limit:

用于分页查询。

例:limit 5; 或 limit 0,5;

limit startIndex(起始下标,默认0), length(长度);

limit写在order by之后。

limit (pageNo-1)*pageSize , pageSize ;

将查询数据创建表:

1
creat table emp2 as select * from emp where job = 'manager';

物理删除(不能rollback):

truncate table … ;

字段联合唯一:

unique(字段a, 字段b);

在mysql中一个字段同时被unique和not null约束时自动变成主键

自动维护主键值:

auto_increment(自增)

事务:

DML语句:insert、delete、update

DML语句才有事务一说

提交事务:commit

回滚事务:rollback

开启事务:start transaction(关闭自动提交功能)

事务隔离:

read uncommited 读未提交(能读到未提交的事务)

read commited 读已提交(不能读到未提交的事务,只能读已提交的)

repeatable read 可重复读(读到“幻象”)

serializable 序列化/串行化

设置隔离级别:set global transaction isolation level … ;

索引:

失效:

  1. %开头

  2. 用or

MySQL三大日志

MySQL的日志中比较重要的还要属二进制日志 binlog(归档日志)和事务日志 redo log(重做日志)和 undo log(回滚日志)。

redo log

redo log(重做日志)是 InnoDB 存储引擎独有的,它让MySQL拥有了崩溃恢复能力。

比如 MySQL 实例挂了或宕机了,重启时,InnoDB 存储引擎会使用 redo log 恢复数据,保证数据的持久性与完整性。

MySQL 中数据是以页为单位,你查询一条记录,会从硬盘把一页的数据加载出来,加载出来的数据叫数据页,会放入到 Buffer Pool 中。

后续的查询都是先从 Buffer Pool 中找,没有命中再去硬盘加载,减少硬盘 IO 开销,提升性能。

更新表数据的时候,也是如此,发现 Buffer Pool 里存在要更新的数据,就直接在 Buffer Pool 里更新。

然后会把“在某个数据页上做了什么修改”记录到重做日志缓存(redo log buffer)里,接着刷盘到 redo log 文件里。

理想情况,事务一提交就会进行刷盘操作,但实际上,刷盘的时机是根据策略来进行的。

刷盘时机:

策略:

  • 0 :设置为 0 的时候,表示每次事务提交时不进行刷盘操作
  • 1 :设置为 1 的时候,表示每次事务提交时都将进行刷盘操作(默认值)
  • 2 :设置为 2 的时候,表示每次事务提交时都只把 redo log buffer 内容写入 page cache

另外,InnoDB 存储引擎有一个后台线程,每隔1 秒,就会把 redo log buffer 中的内容写到文件系统缓存(page cache),然后调用 fsync 刷盘。

因此:

  • 0 :设置为 0 的时候,如果MySQL挂了或宕机可能会有1秒数据的丢失。
  • 1 :设置为 1 的时候,只要事务提交成功,redo log记录就一定在硬盘里,不会有任何数据丢失。
  • 2 :设置为 2 的时候,如果仅仅只是MySQL挂了不会有任何数据丢失,但是宕机可能会有1秒数据的丢失。

binlog

binlog 是逻辑日志,记录内容是语句的原始逻辑,类似于“给 ID=2 这一行的 c 字段加 1”,属于MySQL Server 层。

可以说 MySQL 数据库的数据备份、主备、主主、主从都离不开 binlog ,需要依靠 binlog 来同步数据,保证数据一致性。

写入机制:

binlog 的写入时机也非常简单,事务执行过程中,先把日志写到binlog cache,事务提交的时候,再把binlog cache写到binlog文件中。

undo log

在异常发生时,对已经执行的操作进行回滚,在 MySQL 中,恢复机制是通过 回滚日志(undo log) 实现的。

所有事务进行的修改都会先记录到这个回滚日志中,然后再执行相关的操作。

如果执行过程中遇到异常的话,我们直接利用 回滚日志 中的信息将数据回滚到修改之前的样子即可!并且,回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况,当用户再次启动数据库的时候,数据库还能够通过查询回滚日志来回滚将之前未完成的事务。

分表

分表策略

范围分表:

Hash分表:

应用场景

单库单表:原始方案

单库分表:有效缩小磁盘扫描范围(例如要找第 3亿零一 个数据,通过范围分表和Hash分表都可以判断要去哪个表里面检索)

多库分表:提高数据库并行处理能力(每个数据库部署在不同的服务器,数据库可以在各个服务器上的磁盘上进行IO,相比于单库只在一台服务器上跑效率肯定会提升)

分表可能产生的问题

一、分布式事务问题

引入 XA、TCC、SAGA 等分布式事务解决方案(用于进行事务协调)

二、跨库 JOIN 关联查询

  1. 程序先查 A 表,再循环查询 B 表
  2. MyCat、ShardingSphere 支持两表跨库 join

三、跨界点分页查询问题

单节点各取 n 条,之后在程序合并运算取 top n(???)

四、全局主键 ID 问题

采用分布式主键生成器,推特SnowFlake、美团Leaf (回头要看一下美团的实现)

五、扩容问题

范围分表易扩容,但存在尾部热点问题(尾部热点问题意思是最新的一个表由于新创建出来会经常访问,最后一个读写压力会比较大)

Hash 分表极难扩容,建议改为一致性 Hash,但迁移难度大(什么是一致性Hash?)

雪花算法(分布式唯一ID)

直接上图:

  • 第一位是表示正负数,而分布式唯一ID是整数,所以应恒为0
  • 后41位是时间戳
  • 后10位是机器ID,即最多可以容纳1024个机器
  • 最后12位是序列号,即最多4096个ID,从0~4095

那么也就是说一个时间戳的时间范围内,雪花算法可以生成1024*4096个ID,大约是四百万+个,可以看出来效率是非常高的

一致性Hash算法

这个算法是为了解决Hash分表的扩容问题

一致性hash算法会建立一个有2^32个槽点(0 - 2^32-1)的hash环,假设现在有A、B、C三台服务器,以A为栗,会进行hash(A)%2^32,得到一个0 - 2^32-1之间的数,然后映射到hash环上

以csdn.jpg为例,我们照样算出hash(csdn.jpg)%2^32的值,然后映射到hash环上,然后以该点出发,顺时针遇到的第一个服务器,即为数据即将存储的服务器:

什么是hash偏斜?

A、B、C服务节点,如果像上图那样接近于将hash环平均分配那固然理想,但是如果他们hash值十分相近,会发生什么呢?

怎么办呢?

引入了虚拟节点的概念,以A节点为例,虚拟构造出(A0,A1,A2….AN),只要是落在这些虚拟节点上的数据,都存入A节点。读取时也相同,顺时针获取的是A0虚拟节点,就到A节点上获取数据,这样就能解决数据分布不均的问题。如图所示:

数据库三大范式

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

提高SQL效率

  1. 避免全表扫描,首先考虑在where及order by的列上建立索引。
  2. 尽量避免在where子句中使用!=或<>操作符, MySQL只有对以下操作符才使用索引:<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE。
  3. 尽量避免在where子句中使用or来连接条件, 否则将导致引擎放弃使用索引而进行全表扫描, 可以使用UNION合并查询。
  4. in和not in也要慎用,否则会导致全表扫描,对于连续的数值,能用between就不要用in了。
  5. 如果在where子句中使用参数,也会导致全表扫描。
  6. 尽量避免在where子句中对字段进行表达式操作,应尽量避免在where子句中对字段进行函数操作。
  7. 索引虽然可以提高相应的select的效率,但同时也降低了insert及update的效率,因为insert或update时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。
  8. 尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。
  9. 尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。
  10. 常见的简化规则如下:不要有超过5个以上的表连接(JOIN),考虑使用临时表或表变量存放中间结果。少用子查询,视图嵌套不要过深,一般视图嵌套不要超过2个为宜。
  11. 用OR的字句可以分解成多个查询,并且通过UNION 连接多个查询。他们的速度只同是否使用索引有关,如果查询需要用到联合索引,用UNION all执行的效率更高.多个OR的字句没有用到索引,改写成UNION的形式再试图与索引匹配。一个关键的问题是否用到索引。
  12. 尽量使用“>=”,不要使用“>”。
  13. 尽量使用exists代替select count(1)来判断是否存在记录,count函数只有在统计表中所有行数时使用,而且count(1)比count(*)更有效率。
  14. sql语句用大写,因为oracle 总是先解析sql语句,把小写的字母转换成大写的再执行。
  15. 别名的使用,别名是大型数据库的应用技巧,就是表名、列名在查询中以一个字母为别名,查询速度要比建连接表快1.5倍。
  16. 避免死锁,在你的存储过程和触发器中访问同一个表时总是以相同的顺序;事务应经可能地缩短,在一个事务中应尽可能减少涉及到的数据量;永远不要在事务中等待用户输入。
  17. MySQL查询可以启用高速查询缓存。这是提高数据库性能的有效Mysql优化方法之一。当同一个查询被执行多次时,从缓存中提取数据和直接从数据库中返回数据快很多。
  18. 任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边。

可参考:如何提高sql执行效率?