数据库学习

什么是数据库事务

 

事务（transaction）是作为一个单元的一组有序的数据库操作 要么全部成功，要么全部失败。

事务的特性：

1.原子性 事务要么全部执行，要么全部不执行

2.一致性 事务的执行会使数据库从一种一致性状态进入另一种一致性状态

3.隔离性：并发执行时，一个事务的执行不影响其他事务的执行

4.持久性：事务一旦执行就是永久的不论断电或者机器故障

拿转账来说：一致性就是 A和B的账号一共有2000元 无论怎么转正他们的账户都有2000元。

脏读 ，不可重复读，幻读

1.脏读就是读取到其他事务未提交的数据

2.不可重复读，事务两次读取结果不一致，也就是读取到其他事务提交的数据

3.幻读，事务两次读取的总量不一致，比如其他事务使用insert。

四大隔离级别

1.读未提交

能够读取到未提交的数据 没有解决脏读，不可重复读，幻读。

2.读已提交

能够读取到已提交的数据 解决了脏读

3.重复读取

解决了不可重复读和脏读

4.序列化

解决了幻读

并不是隔离级别越高越好，隔离级别越高，代表所耗费的资源越多，性能越差，需要作一个平衡。

mysql的默认存储引擎是innodb 默认隔离级别是可重复读。

索引

索引就像一本书的目录能够快速的定位，加快数据库的查询，如果没有索引的话就会进行顺序遍历进行查找，索引能够降低IO次数，

索引并不是越多越好，建立索引需要空间，而索引的底层结构一把是B+树，更新，删除，插入还需要维护索引。而且数据量小的话不一定需要索引。

memory的默认存储引擎是 hash索引

memory表存放在内存中，断电就会消失，适用于临时表

hash索引，当查找某条记录时很快，每个键对应一个值，以散列的方式进行分布，不支持排序和范围查询

Inodb的默认存储引擎是b+树

1.b+树非叶子节点不存数据，所以一个结点能够存储的数据更多会更加的矮胖，减少读取磁盘的次数

2.b+树每次读取都需要到叶子节点，所以更加稳定。

3.b+树叶子节点以链表形式管理起来，并且有序，更加适合范围查询。

Innodb 支持事务支持行锁 外键。Myisam不支持

Innodb索引和数据是在一起的，每个文件一个表，而MyIsam每个表两个文件，索引文件和数据文件

锁有几种：

按粒度划分：

行锁：加锁粒度小，开销大

读锁   select自动不会加锁。

写锁    innodb会自动为update/select/insert自动加入写锁

有间隙锁：左开右闭（1，4]；

Next-key：左闭右闭  [1,4];

表锁：加锁粒度大，开销比较小

读锁

写锁

当对某行加锁的时候会对整个表加意向锁，意向锁之间并不冲突，然后如果有意向锁，再对整个表加锁就会被阻塞。

全局锁：整个数据库实例处于只读状态，一般用于全局数据库备份

读已提交隔离级别：

只对查出来的数据加行锁 不论什么索引。

可重复读 会加间隙锁并且解决幻读

快照读用mvcc来解决（读取快照），但幻读是当前读，读取的最新的数据，所以用间隙锁。

innodb 默认以主键作为聚簇索引，如果没有主键那么以非空不重复的键作为聚簇索引，如果都没有就默认建row_id（每一行都包含一个rowid)

建议一定要建立主键：因为如果不建立主键系统会自动找不重复的列作为主键，或者默认建row_id浪费时间

使用整形自增的主键：使用整形较小，普通索引叶子节点存的是主键，主键越小，普通索引越小。

自增：会把相邻主键的数据安放在相邻的物理存储上。如果主键不是自增，而是随机的，那么频繁的插入会使 innodb 频繁地移动磁盘块，而影响写入性能。

 聚簇索引与非聚簇索引的最大区别就是列的物理存储顺序与索引的顺序一致。聚簇索引叶子节点存的是数据，而非聚簇索引叶子节点存的是主键

一个表只有一个聚簇索引，可以有多个非聚簇索引

如何实现索引覆盖：

索引覆盖的意思是只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

如果是普通索引的话进行一次查询找到主键后还需要回到聚簇索引中去找数据，也就是两次树查找（回标）速度较慢。

常见的方法是：将被查询的字段，建立到联合索引里去。

索引的设计原则：

1.选择唯一性索引

2删除不再使用或者很少使用的索引

3对于查询频率高的建立索引

4.尽量使用数据量小的索引

5.索引的数目不宜过多

6频繁增删改的不建立索引。

如何处理慢查询：

最左前缀：

联合索引中关键字的比较是先比较前面的列再比较后面的列，在前面字段排序基础上对后面字段进行排序，

前面字段确定的情况下后面的字段是有序的，而前面字段没有确定的情况下后面的字段是无序的，

如果不使用最左前缀匹配就用不到索引。

为什么索引失效：

1.联合索引没有遵从最左前缀原则。

2.在索引列上做了任何操作（计算、函数、类型转换）

3.使用了！=作为条件

4.使用or会失效，如果使用or则两边的列需要建立了索引

5.模糊查询以 "%"开头索引失效，因为不确定 % 的值

内连接 左连接（左外连接） 右连接（右外连接） 全连接（全外连接）：

1.内连接,显示两个表中有联系的所有数据;（求交集）

2.左连接,以左表为参照,显示所有数据;（将左表和右表相等的全部显示出来，再显示左表有的右表没有的并用Null表示)

3.右连接,以右表为参照显示数据;(将左表和右表相等的全部显示出来，再显示右表有的左表没有的并用Null表示)

4.全外连接，将左表和右表相等的全部显示出来，再显示左表有的右表没有的并用Null表示，再显示右表有的左表没有的并用Null表示

数据库范式：

1.第一范式：如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

2.第二范式：第二范式要求实体中每一行的所有非主属性都必须完全依赖于主键；即：非主属性必须完全依赖于主键，而不是主键的一部分。

3.第三范式：首先满足第二范式，非主键列必须直接依赖于主键，任何非主属性不依赖于其它非主属性，不能存在间接依赖。

怎么实现事务的：

• 原子性：使用 undo log ，从而达到回滚

• 持久性：使用 redo log，从而达到故障后恢复

• 隔离性：使用锁以及MVCC,

分库分表：

当表中的数据量过大时，整个查询效率就会变得很低，就需要将一个表中的数据分到多个表中的多个数据

分片分为垂直分片和水平分片。垂直分片将不同的表拆分到不同的库中能够解决数据库文件过大问题，但不能解决查询问题

水平分片将一个表中的数据拆分到不同的表中，解决了数据量过大造成的查询问题，有按时间分片，按范围分片，取模分片。

数据量超过500W或者大小超过2G需要考虑分库分表

rollback 回滚到事务执行之前 可以savepoint t123  然后 rollback to t123 回滚到回滚点。

MVVC

读取数据通过一种快照的方式将数据保存起来

已提交读下每次select都会生成readview 表示当前活跃的事务id。

而可重复读只有第一次读生成readview。

每行记录都有两个隐藏的列：1事务Id,2回滚指针 roll_point  roll_point,对记录修改时会将老版本写入undo日志，roll_point存的是一个指针，指向这条聚簇索引的上一个版本的位置，通过它获得上一个版本的信息，形成一个版本链。mvcc就是版本链和readview的组合。

已提交读和可重复读的区别就是生成readview的策略不一样

readview 会生成 min_tr   max_tr  m_id 

如果得到的版本中trx_id <min_tr  表面事务已经提交可以见

如果trx_id>max_tr  表面事务是新生成的不可见。

如果min_tr<trx_id<max_tr   如果trx_id 在  m_id 中说明事务还在活跃， 不可见

否则说明事务已经不在了 可见。